|
0
|
1 /* |
|
|
2 * database.c -- sqlite storage |
|
|
3 * |
|
|
4 * Copyright (c) 2020 David Demelier <markand@malikania.fr> |
|
|
5 * |
|
|
6 * Permission to use, copy, modify, and/or distribute this software for any |
|
|
7 * purpose with or without fee is hereby granted, provided that the above |
|
|
8 * copyright notice and this permission notice appear in all copies. |
|
|
9 * |
|
|
10 * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES |
|
|
11 * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF |
|
|
12 * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR |
|
|
13 * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES |
|
|
14 * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN |
|
|
15 * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF |
|
|
16 * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE. |
|
|
17 */ |
|
|
18 |
|
|
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 "image.h" |
|
|
28 #include "log.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 image(\n" |
|
|
37 " id TEXT PRIMARY KEY,\n" |
|
|
38 " title TEXT,\n" |
|
|
39 " author TEXT,\n" |
|
|
40 " data BLOB,\n" |
|
|
41 " filename TEXT,\n" |
|
|
42 " date INT DEFAULT CURRENT_TIMESTAMP,\n" |
|
|
43 " visible INTEGER DEFAULT 0,\n" |
|
|
44 " duration INT\n" |
|
|
45 ");\n" |
|
|
46 "\n" |
|
|
47 "END TRANSACTION"; |
|
|
48 |
|
|
49 static const char *sql_get = |
|
|
50 "SELECT id\n" |
|
|
51 " , title\n" |
|
|
52 " , author\n" |
|
|
53 " , data\n" |
|
|
54 " , LENGTH(data) AS length\n" |
|
|
55 " , filename\n" |
|
|
56 " , strftime('%s', date) AS date\n" |
|
|
57 " , visible\n" |
|
|
58 " , duration\n" |
|
|
59 " FROM image\n" |
|
|
60 " WHERE id = ?"; |
|
|
61 |
|
|
62 static const char *sql_insert = |
|
|
63 "INSERT INTO image(\n" |
|
|
64 " id,\n" |
|
|
65 " title,\n" |
|
|
66 " author,\n" |
|
|
67 " data,\n" |
|
|
68 " filename,\n" |
|
|
69 " visible,\n" |
|
|
70 " duration\n" |
|
|
71 ") VALUES (?, ?, ?, ?, ?, ?, ?)"; |
|
|
72 |
|
|
73 static const char *sql_recents = |
|
|
74 "SELECT id\n" |
|
|
75 " , title\n" |
|
|
76 " , author\n" |
|
|
77 " , data\n" |
|
|
78 " , LENGTH(data) AS length\n" |
|
|
79 " , filename\n" |
|
|
80 " , strftime('%s', date) AS date\n" |
|
|
81 " , visible\n" |
|
|
82 " , duration\n" |
|
|
83 " FROM image\n" |
|
|
84 " WHERE visible = 1\n" |
|
|
85 " ORDER BY date DESC\n" |
|
|
86 " LIMIT ?\n"; |
|
|
87 |
|
|
88 static const char *sql_clear = |
|
|
89 "BEGIN EXCLUSIVE TRANSACTION;\n" |
|
|
90 "\n" |
|
|
91 "DELETE\n" |
|
|
92 " FROM image\n" |
|
|
93 " WHERE strftime('%s', 'now') - strftime('%s', date) >= duration;" |
|
|
94 "\n" |
|
|
95 "END TRANSACTION"; |
|
|
96 |
|
|
97 static const char *sql_search = |
|
|
98 "SELECT id\n" |
|
|
99 " , title\n" |
|
|
100 " , author\n" |
|
|
101 " , data\n" |
|
|
102 " , LENGTH(data)\n" |
|
|
103 " , filename\n" |
|
|
104 " , strftime('%s', date) AS date\n" |
|
|
105 " , visible\n" |
|
|
106 " , duration\n" |
|
|
107 " FROM image\n" |
|
|
108 " WHERE title LIKE ?\n" |
|
|
109 " AND author LIKE ?\n" |
|
|
110 " AND visible = 1\n" |
|
|
111 " ORDER BY date DESC\n" |
|
|
112 " LIMIT ?\n"; |
|
|
113 |
|
|
114 /* sqlite3 use const unsigned char *. */ |
|
|
115 static char * |
|
|
116 dup(const unsigned char *s) |
|
|
117 { |
|
|
118 return estrdup(s ? (const char *)(s) : ""); |
|
|
119 } |
|
|
120 |
|
|
121 static void |
|
|
122 convert(sqlite3_stmt *stmt, struct image *image) |
|
|
123 { |
|
|
124 image->id = dup(sqlite3_column_text(stmt, 0)); |
|
|
125 image->title = dup(sqlite3_column_text(stmt, 1)); |
|
|
126 image->author = dup(sqlite3_column_text(stmt, 2)); |
|
|
127 image->datasz = sqlite3_column_int64(stmt, 4); |
|
|
128 image->data = ememdup(sqlite3_column_blob(stmt, 3), image->datasz); |
|
|
129 image->filename = dup(sqlite3_column_text(stmt, 5)); |
|
|
130 image->timestamp = sqlite3_column_int64(stmt, 6); |
|
|
131 image->visible = sqlite3_column_int(stmt, 7); |
|
|
132 image->duration = sqlite3_column_int64(stmt, 8); |
|
|
133 } |
|
|
134 |
|
|
135 static bool |
|
|
136 exists(const char *id) |
|
|
137 { |
|
|
138 assert(id); |
|
|
139 |
|
|
140 sqlite3_stmt *stmt = NULL; |
|
|
141 bool ret = true; |
|
|
142 |
|
|
143 if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) == SQLITE_OK) { |
|
|
144 sqlite3_bind_text(stmt, 1, id, -1, NULL); |
|
|
145 ret = sqlite3_step(stmt) == SQLITE_ROW; |
|
|
146 sqlite3_finalize(stmt); |
|
|
147 } |
|
|
148 |
|
|
149 return ret; |
|
|
150 } |
|
|
151 |
|
|
152 static const char * |
|
|
153 create_id(void) |
|
|
154 { |
|
|
155 static const char table[] = "abcdefghijklmnopqrstuvwxyz1234567890"; |
|
|
156 static char id[12]; |
|
|
157 |
|
|
158 for (size_t i = 0; i < sizeof (id); ++i) |
|
|
159 id[i] = table[rand() % (sizeof (table) - 1)]; |
|
|
160 |
|
|
161 return id; |
|
|
162 } |
|
|
163 |
|
|
164 static bool |
|
|
165 set_id(struct image *image) |
|
|
166 { |
|
|
167 assert(image); |
|
|
168 |
|
|
169 image->id = NULL; |
|
|
170 |
|
|
171 /* |
|
|
172 * Avoid infinite loop, we only try to create a new id in 30 steps. |
|
|
173 * |
|
|
174 * On error, the function `exist` returns true to indicate we should |
|
|
175 * not try to save with that id. |
|
|
176 */ |
|
|
177 int tries = 0; |
|
|
178 |
|
|
179 do { |
|
|
180 free(image->id); |
|
|
181 image->id = estrdup(create_id()); |
|
|
182 } while (++tries < 30 && exists(image->id)); |
|
|
183 |
|
|
184 return tries < 30; |
|
|
185 } |
|
|
186 |
|
|
187 bool |
|
|
188 database_open(const char *path) |
|
|
189 { |
|
|
190 assert(path); |
|
|
191 |
|
|
192 log_info("database: opening %s", path); |
|
|
193 |
|
|
194 if (sqlite3_open(path, &db) != SQLITE_OK) { |
|
|
195 log_warn("database: unable to open %s: %s", path, sqlite3_errmsg(db)); |
|
|
196 return false; |
|
|
197 } |
|
|
198 |
|
|
199 /* Wait for 30 seconds to lock the database. */ |
|
|
200 sqlite3_busy_timeout(db, 30000); |
|
|
201 |
|
|
202 if (sqlite3_exec(db, sql_init, NULL, NULL, NULL) != SQLITE_OK) { |
|
|
203 log_warn("database: unable to initialize %s: %s", path, sqlite3_errmsg(db)); |
|
|
204 return false; |
|
|
205 } |
|
|
206 |
|
|
207 return true; |
|
|
208 } |
|
|
209 |
|
|
210 bool |
|
|
211 database_recents(struct image *images, size_t *max) |
|
|
212 { |
|
|
213 assert(images); |
|
|
214 assert(max); |
|
|
215 |
|
|
216 sqlite3_stmt *stmt = NULL; |
|
|
217 |
|
|
218 memset(images, 0, *max * sizeof (*images)); |
|
|
219 log_debug("database: accessing most recents"); |
|
|
220 |
|
|
221 if (sqlite3_prepare(db, sql_recents, -1, &stmt, NULL) != SQLITE_OK || |
|
|
222 sqlite3_bind_int64(stmt, 1, *max) != SQLITE_OK) |
|
|
223 goto sqlite_err; |
|
|
224 |
|
|
225 size_t i = 0; |
|
|
226 |
|
|
227 for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i) |
|
|
228 convert(stmt, &images[i]); |
|
|
229 |
|
|
230 log_debug("database: found %zu images", i); |
|
|
231 sqlite3_finalize(stmt); |
|
|
232 *max = i; |
|
|
233 |
|
|
234 return true; |
|
|
235 |
|
|
236 sqlite_err: |
|
|
237 log_warn("database: error (recents): %s\n", sqlite3_errmsg(db)); |
|
|
238 |
|
|
239 if (stmt) |
|
|
240 sqlite3_finalize(stmt); |
|
|
241 |
|
|
242 return (*max = 0); |
|
|
243 } |
|
|
244 |
|
|
245 bool |
|
|
246 database_get(struct image *image, const char *id) |
|
|
247 { |
|
|
248 assert(image); |
|
|
249 assert(id); |
|
|
250 |
|
|
251 sqlite3_stmt* stmt = NULL; |
|
|
252 bool found = false; |
|
|
253 |
|
|
254 memset(image, 0, sizeof (*image)); |
|
|
255 log_debug("database: accessing image with id: %s", id); |
|
|
256 |
|
|
257 if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) != SQLITE_OK || |
|
|
258 sqlite3_bind_text(stmt, 1, id, -1, NULL) != SQLITE_OK) |
|
|
259 goto sqlite_err; |
|
|
260 |
|
|
261 switch (sqlite3_step(stmt)) { |
|
|
262 case SQLITE_ROW: |
|
|
263 convert(stmt, image); |
|
|
264 found = true; |
|
|
265 break; |
|
|
266 case SQLITE_MISUSE: |
|
|
267 case SQLITE_ERROR: |
|
|
268 goto sqlite_err; |
|
|
269 default: |
|
|
270 break; |
|
|
271 } |
|
|
272 |
|
|
273 sqlite3_finalize(stmt); |
|
|
274 |
|
|
275 return found; |
|
|
276 |
|
|
277 sqlite_err: |
|
|
278 if (stmt) |
|
|
279 sqlite3_finalize(stmt); |
|
|
280 |
|
|
281 log_warn("database: error (get): %s", sqlite3_errmsg(db)); |
|
|
282 |
|
|
283 return false; |
|
|
284 } |
|
|
285 |
|
|
286 bool |
|
|
287 database_insert(struct image *image) |
|
|
288 { |
|
|
289 assert(image); |
|
|
290 |
|
|
291 sqlite3_stmt *stmt = NULL; |
|
|
292 |
|
|
293 log_debug("database: creating new image"); |
|
|
294 |
|
|
295 if (sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL, NULL) != SQLITE_OK) { |
|
|
296 log_warn("database: could not lock database: %s", sqlite3_errmsg(db)); |
|
|
297 return false; |
|
|
298 } |
|
|
299 |
|
|
300 if (!set_id(image)) { |
|
|
301 log_warn("database: unable to randomize unique identifier"); |
|
|
302 sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL); |
|
|
303 return false; |
|
|
304 } |
|
|
305 |
|
|
306 if (sqlite3_prepare(db, sql_insert, -1, &stmt, NULL) != SQLITE_OK) |
|
|
307 goto sqlite_err; |
|
|
308 |
|
|
309 sqlite3_bind_text(stmt, 1, image->id, -1, SQLITE_STATIC); |
|
|
310 sqlite3_bind_text(stmt, 2, image->title, -1, SQLITE_STATIC); |
|
|
311 sqlite3_bind_text(stmt, 3, image->author, -1, SQLITE_STATIC); |
|
|
312 sqlite3_bind_blob(stmt, 4, image->data, image->datasz, NULL); |
|
|
313 sqlite3_bind_text(stmt, 5, image->filename, -1, SQLITE_STATIC); |
|
|
314 sqlite3_bind_int(stmt, 6, image->visible); |
|
|
315 sqlite3_bind_int64(stmt, 7, image->duration); |
|
|
316 |
|
|
317 if (sqlite3_step(stmt) != SQLITE_DONE) |
|
|
318 goto sqlite_err; |
|
|
319 |
|
|
320 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); |
|
|
321 sqlite3_finalize(stmt); |
|
|
322 |
|
|
323 log_info("database: new image (%s) from %s expires in one %lld seconds", |
|
|
324 image->id, image->author, image->duration); |
|
|
325 |
|
|
326 return true; |
|
|
327 |
|
|
328 sqlite_err: |
|
|
329 log_warn("database: error (insert): %s", sqlite3_errmsg(db)); |
|
|
330 sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL); |
|
|
331 |
|
|
332 if (stmt) |
|
|
333 sqlite3_finalize(stmt); |
|
|
334 |
|
|
335 free(image->id); |
|
|
336 image->id = NULL; |
|
|
337 |
|
|
338 return false; |
|
|
339 } |
|
|
340 |
|
|
341 bool |
|
|
342 database_search(struct image *images, |
|
|
343 size_t *max, |
|
|
344 const char *title, |
|
|
345 const char *author) |
|
|
346 { |
|
|
347 assert(images); |
|
|
348 assert(max); |
|
|
349 |
|
|
350 sqlite3_stmt *stmt = NULL; |
|
|
351 size_t i; |
|
|
352 |
|
|
353 memset(images, 0, *max * sizeof (*images)); |
|
|
354 |
|
|
355 /* Select everything if not specified. */ |
|
|
356 title = title ? title : "%"; |
|
|
357 author = author ? author : "%"; |
|
|
358 |
|
|
359 if (sqlite3_prepare(db, sql_search, -1, &stmt, NULL) != SQLITE_OK) |
|
|
360 goto sqlite_err; |
|
|
361 if (sqlite3_bind_text(stmt, 1, title, -1, NULL) != SQLITE_OK) |
|
|
362 goto sqlite_err; |
|
|
363 if (sqlite3_bind_text(stmt, 2, author, -1, NULL) != SQLITE_OK) |
|
|
364 goto sqlite_err; |
|
|
365 if (sqlite3_bind_int64(stmt, 3, *max) != SQLITE_OK) |
|
|
366 goto sqlite_err; |
|
|
367 |
|
|
368 for (i = 0; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i) |
|
|
369 convert(stmt, &images[i]); |
|
|
370 |
|
|
371 log_debug("database: found %zu images", i); |
|
|
372 sqlite3_finalize(stmt); |
|
|
373 *max = i; |
|
|
374 |
|
|
375 return true; |
|
|
376 |
|
|
377 sqlite_err: |
|
|
378 log_warn("database: error (search): %s\n", sqlite3_errmsg(db)); |
|
|
379 |
|
|
380 if (stmt) |
|
|
381 sqlite3_finalize(stmt); |
|
|
382 |
|
|
383 return (*max = 0); |
|
|
384 } |
|
|
385 |
|
|
386 void |
|
|
387 database_clear(void) |
|
|
388 { |
|
|
389 log_debug("database: clearing deprecated images"); |
|
|
390 |
|
|
391 if (sqlite3_exec(db, sql_clear, NULL, NULL, NULL) != SQLITE_OK) |
|
|
392 log_warn("database: error (clear): %s\n", sqlite3_errmsg(db)); |
|
|
393 } |
|
|
394 |
|
|
395 void |
|
|
396 database_finish(void) |
|
|
397 { |
|
|
398 log_debug("database: closing"); |
|
|
399 |
|
|
400 if (db) { |
|
|
401 sqlite3_close(db); |
|
|
402 db = NULL; |
|
|
403 } |
|
|
404 } |
