Mercurial > paster
annotate database.c @ 21:21c103c33ac9
pasterd: improve verbosity, closes #2472
author | David Demelier <markand@malikania.fr> |
---|---|
date | Thu, 06 Feb 2020 20:10:00 +0100 |
parents | 98ef2713eb0d |
children | f4e8a7920b94 |
rev | line source |
---|---|
6
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
1 /* |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
2 * database.c -- sqlite storage |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
3 * |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
4 * Copyright (c) 2020 David Demelier <markand@malikania.fr> |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
5 * |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
6 * Permission to use, copy, modify, and/or distribute this software for any |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
7 * purpose with or without fee is hereby granted, provided that the above |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
8 * copyright notice and this permission notice appear in all copies. |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
9 * |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
10 * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
11 * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
12 * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
13 * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
14 * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
15 * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
16 * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
17 */ |
98ef2713eb0d
pasterd: add licenses in database
David Demelier <markand@malikania.fr>
parents:
5
diff
changeset
|
18 |
0 | 19 #include <assert.h> |
20 #include <stdlib.h> | |
21 #include <stdio.h> | |
22 #include <string.h> | |
23 | |
24 #include <sqlite3.h> | |
25 | |
26 #include "database.h" | |
27 #include "log.h" | |
28 #include "paste.h" | |
29 #include "util.h" | |
30 | |
31 static sqlite3 *db; | |
32 | |
33 static const char *sql_init = | |
34 "BEGIN EXCLUSIVE TRANSACTION;\n" | |
35 "\n" | |
36 "CREATE TABLE IF NOT EXISTS paste(\n" | |
37 " uuid TEXT PRIMARY KEY,\n" | |
38 " title TEXT,\n" | |
39 " author TEXT,\n" | |
40 " language TEXT,\n" | |
41 " code TEXT,\n" | |
42 " date INT DEFAULT CURRENT_TIMESTAMP,\n" | |
43 " visible INTEGER DEFAULT 1,\n" | |
44 " duration INT\n" | |
45 ");\n" | |
46 "\n" | |
47 "END TRANSACTION"; | |
48 | |
49 static const char *sql_get = | |
50 "SELECT uuid\n" | |
51 " , title\n" | |
52 " , author\n" | |
53 " , language\n" | |
54 " , code\n" | |
55 " , strftime('%s', date)\n" | |
56 " , visible\n" | |
57 " , duration\n" | |
58 " FROM paste\n" | |
1
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
59 " WHERE uuid = ?"; |
0 | 60 |
61 static const char *sql_insert = | |
62 "INSERT INTO paste(\n" | |
63 " uuid,\n" | |
64 " title,\n" | |
65 " author,\n" | |
66 " language,\n" | |
67 " code,\n" | |
68 " visible,\n" | |
69 " duration\n" | |
70 ") VALUES (?, ?, ?, ?, ?, ?, ?)"; | |
71 | |
72 static const char *sql_recents = | |
73 "SELECT uuid\n" | |
74 " , title\n" | |
75 " , author\n" | |
76 " , language\n" | |
77 " , code\n" | |
78 " , strftime('%s', date) AS date\n" | |
79 " , visible\n" | |
80 " , duration\n" | |
81 " FROM paste\n" | |
2
65607ae124b1
pasterd: implement /new
David Demelier <markand@malikania.fr>
parents:
1
diff
changeset
|
82 " WHERE visible = 1\n" |
5
f455893bf0b0
pasterd: show dates in /
David Demelier <markand@malikania.fr>
parents:
2
diff
changeset
|
83 " ORDER BY date DESC\n" |
0 | 84 " LIMIT ?\n"; |
85 | |
86 static const char *sql_clear = | |
87 "BEGIN EXCLUSIVE TRANSACTION;\n" | |
88 "\n" | |
89 "DELETE\n" | |
90 " FROM paste\n" | |
91 " WHERE strftime('%s', 'now') - strftime('%s', date) >= duration;" | |
92 "\n" | |
93 "END TRANSACTION"; | |
94 | |
1
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
95 /* sqlite3 use const unsigned char *. */ |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
96 static char * |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
97 dup(const unsigned char *s) |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
98 { |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
99 return estrdup(s ? (const char *)(s) : ""); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
100 } |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
101 |
0 | 102 static const char * |
103 create_id(void) | |
104 { | |
105 static char uuid[256]; | |
106 | |
107 /* | |
108 * Not a very strong generation but does not require to link against | |
109 * util-linux. | |
110 * | |
111 * See https://stackoverflow.com/questions/2174768/generating-random-uuids-in-linux | |
112 */ | |
113 sprintf(uuid, "%x%x-%x-%x-%x-%x%x%x", | |
114 rand(), rand(), | |
115 rand(), | |
116 ((rand() & 0x0fff) | 0x4000), | |
117 rand() % 0x3fff + 0x8000, | |
118 rand(), rand(), rand()); | |
119 | |
120 return uuid; | |
121 } | |
122 | |
123 static void | |
124 convert(sqlite3_stmt *stmt, struct paste *paste) | |
125 { | |
1
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
126 paste->uuid = dup(sqlite3_column_text(stmt, 0)); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
127 paste->title = dup(sqlite3_column_text(stmt, 1)); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
128 paste->author = dup(sqlite3_column_text(stmt, 2)); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
129 paste->language = dup(sqlite3_column_text(stmt, 3)); |
836a698946f8
pasterd: add basic routes
David Demelier <markand@malikania.fr>
parents:
0
diff
changeset
|
130 paste->code = dup(sqlite3_column_text(stmt, 4)); |
0 | 131 paste->timestamp = sqlite3_column_int64(stmt, 5); |
132 paste->visible = sqlite3_column_int(stmt, 6); | |
133 paste->duration = sqlite3_column_int64(stmt, 7); | |
134 } | |
135 | |
136 bool | |
137 database_open(const char *path) | |
138 { | |
139 assert(path); | |
140 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
141 log_info("database: opening %s", path); |
0 | 142 |
143 if (sqlite3_open(path, &db) != SQLITE_OK) { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
144 log_warn("database: unable to open %s: %s", path, sqlite3_errmsg(db)); |
0 | 145 return false; |
146 } | |
147 | |
148 if (sqlite3_exec(db, sql_init, NULL, NULL, NULL) != SQLITE_OK) { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
149 log_warn("database: unable to initialize %s: %s", path, sqlite3_errmsg(db)); |
0 | 150 return false; |
151 } | |
152 | |
153 return true; | |
154 } | |
155 | |
156 bool | |
157 database_recents(struct paste *pastes, size_t *max) | |
158 { | |
159 assert(pastes); | |
160 assert(max); | |
161 | |
162 sqlite3_stmt *stmt = NULL; | |
163 | |
164 memset(pastes, 0, *max * sizeof (struct paste)); | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
165 log_debug("database: accessing most recents"); |
0 | 166 |
167 if (sqlite3_prepare(db, sql_recents, -1, &stmt, NULL) != SQLITE_OK || | |
168 sqlite3_bind_int64(stmt, 1, *max) != SQLITE_OK) | |
169 goto sqlite_err; | |
170 | |
171 size_t i = 0; | |
172 | |
173 for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i) | |
174 convert(stmt, &pastes[i]); | |
175 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
176 log_debug("database: found %zu pastes", i); |
0 | 177 sqlite3_finalize(stmt); |
178 *max = i; | |
179 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
180 |
0 | 181 return true; |
182 | |
183 sqlite_err: | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
184 log_warn("database: error (recents): %s\n", sqlite3_errmsg(db)); |
0 | 185 |
186 if (stmt) | |
187 sqlite3_finalize(stmt); | |
188 | |
189 return (*max = 0); | |
190 } | |
191 | |
192 bool | |
193 database_get(struct paste *paste, const char *uuid) | |
194 { | |
195 assert(paste); | |
196 assert(uuid); | |
197 | |
198 memset(paste, 0, sizeof (struct paste)); | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
199 log_debug("database: accessing paste with uuid: %s", uuid); |
0 | 200 |
201 sqlite3_stmt* stmt = NULL; | |
202 | |
203 if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) != SQLITE_OK || | |
204 sqlite3_bind_text(stmt, 1, uuid, -1, NULL) != SQLITE_OK) | |
205 goto sqlite_err; | |
206 | |
207 switch (sqlite3_step(stmt)) { | |
208 case SQLITE_ROW: | |
209 convert(stmt, paste); | |
210 break; | |
211 case SQLITE_MISUSE: | |
212 case SQLITE_ERROR: | |
213 goto sqlite_err; | |
214 default: | |
215 break; | |
216 } | |
217 | |
218 sqlite3_finalize(stmt); | |
219 | |
220 return true; | |
221 | |
222 sqlite_err: | |
223 if (stmt) | |
224 sqlite3_finalize(stmt); | |
225 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
226 log_warn("database: error (get): %s", sqlite3_errmsg(db)); |
0 | 227 |
228 return false; | |
229 } | |
230 | |
231 bool | |
232 database_insert(struct paste *paste) | |
233 { | |
234 assert(paste); | |
235 | |
236 sqlite3_stmt* stmt = NULL; | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
237 log_debug("database: creating new paste"); |
0 | 238 |
239 if (sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL, NULL) != SQLITE_OK) { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
240 log_warn("database: could not lock database: %s", sqlite3_errmsg(db)); |
0 | 241 return false; |
242 } | |
243 | |
244 if (sqlite3_prepare(db, sql_insert, -1, &stmt, NULL) != SQLITE_OK) | |
245 goto sqlite_err; | |
246 | |
247 /* Create a new uuid first. */ | |
248 paste->uuid = estrdup(create_id()); | |
249 | |
250 sqlite3_bind_text(stmt, 1, paste->uuid, -1, SQLITE_STATIC); | |
251 sqlite3_bind_text(stmt, 2, paste->title, -1, SQLITE_STATIC); | |
252 sqlite3_bind_text(stmt, 3, paste->author, -1, SQLITE_STATIC); | |
253 sqlite3_bind_text(stmt, 4, paste->language, -1, SQLITE_STATIC); | |
254 sqlite3_bind_text(stmt, 5, paste->code, -1, SQLITE_STATIC); | |
255 sqlite3_bind_int(stmt, 6, paste->visible); | |
256 sqlite3_bind_int64(stmt, 7, paste->duration); | |
257 | |
258 if (sqlite3_step(stmt) != SQLITE_DONE) | |
259 goto sqlite_err; | |
260 | |
261 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); | |
262 sqlite3_finalize(stmt); | |
263 | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
264 log_info("database: new paste (%s) from %s expires in one %lld seconds", |
0 | 265 paste->uuid, paste->author, paste->duration); |
266 | |
267 return true; | |
268 | |
269 sqlite_err: | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
270 log_warn("database: error (insert): %s", sqlite3_errmsg(db)); |
0 | 271 sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL); |
272 | |
273 if (stmt) | |
274 sqlite3_finalize(stmt); | |
275 | |
276 free(paste->uuid); | |
277 paste->uuid = NULL; | |
278 | |
279 return false; | |
280 } | |
281 | |
282 void | |
283 database_clear(void) | |
284 { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
285 log_debug("database: clearing deprecated pastes"); |
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
286 |
0 | 287 if (sqlite3_exec(db, sql_clear, NULL, NULL, NULL) != SQLITE_OK) |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
288 log_warn("database: error (clear): %s\n", sqlite3_errmsg(db)); |
0 | 289 } |
290 | |
291 void | |
292 database_finish(void) | |
293 { | |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
294 log_debug("database: closing"); |
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
295 |
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
296 if (db) { |
0 | 297 sqlite3_close(db); |
21
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
298 db = NULL; |
21c103c33ac9
pasterd: improve verbosity, closes #2472
David Demelier <markand@malikania.fr>
parents:
6
diff
changeset
|
299 } |
0 | 300 } |