Mercurial > paster

comparison database.c @ 79:52029a52a385

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
pasterd: revert using ktemplate
author David Demelier <markand@malikania.fr>
date Fri, 17 Mar 2023 07:43:20 +0100
parents 9bfe5ce3cc45
children 94dcca86e5cc
comparison
equal deleted inserted replaced
78:9bfe5ce3cc45 79:52029a52a385
22 #include <string.h> 22 #include <string.h>
23 23
24 #include <sqlite3.h> 24 #include <sqlite3.h>
25 25
26 #include "database.h" 26 #include "database.h"
27 #include "json-util.h"
28 #include "log.h" 27 #include "log.h"
28 #include "paste.h"
29 #include "util.h" 29 #include "util.h"
30 30
31 #include "sql/clear.h" 31 #include "sql/clear.h"
32 #include "sql/get.h" 32 #include "sql/get.h"
33 #include "sql/init.h" 33 #include "sql/init.h"
34 #include "sql/insert.h" 34 #include "sql/insert.h"
35 #include "sql/recents.h" 35 #include "sql/recents.h"
36 #include "sql/search.h" 36 #include "sql/search.h"
37 37
38 #define ID_MAX (12 + 1) 38 #define CHAR(sql) (const char *)(sql)
39 39
40 static sqlite3 *db; 40 static sqlite3 *db;
41 41
42 static inline json_t * 42 static char *
43 convert(sqlite3_stmt *stmt) 43 dup(const unsigned char *s)
44 { 44 {
45 return json_pack("{ss ss ss ss ss sI si si}", 45 return estrdup(s ? (const char *)(s) : "");
46 "id", sqlite3_column_text(stmt, 0), 46 }
47 "title", sqlite3_column_text(stmt, 1), 47
48 "author", sqlite3_column_text(stmt, 2), 48 static void
49 "language", sqlite3_column_text(stmt, 3), 49 convert(sqlite3_stmt *stmt, struct paste *paste)
50 "code", sqlite3_column_text(stmt, 4), 50 {
51 "timestamp", (json_int_t)sqlite3_column_int64(stmt, 5), 51 paste->id = dup(sqlite3_column_text(stmt, 0));
52 "visible", sqlite3_column_int(stmt, 6), 52 paste->title = dup(sqlite3_column_text(stmt, 1));
53 "duration", sqlite3_column_int(stmt, 7) 53 paste->author = dup(sqlite3_column_text(stmt, 2));
54 ); 54 paste->language = dup(sqlite3_column_text(stmt, 3));
55 paste->code = dup(sqlite3_column_text(stmt, 4));
56 paste->timestamp = sqlite3_column_int64(stmt, 5);
57 paste->visible = sqlite3_column_int(stmt, 6);
58 paste->duration = sqlite3_column_int64(stmt, 7);
55 } 59 }
56 60
57 static int 61 static int
58 exists(const char *id) 62 exists(const char *id)
59 { 63 {
60 assert(id); 64 assert(id);
61 65
62 sqlite3_stmt *stmt = NULL; 66 sqlite3_stmt *stmt = NULL;
63 int ret = 1; 67 int ret = 1;
64 68
65 if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) == SQLITE_OK) { 69 if (sqlite3_prepare(db, CHAR(sql_get), -1, &stmt, NULL) == SQLITE_OK) {
66 sqlite3_bind_text(stmt, 1, id, -1, NULL); 70 sqlite3_bind_text(stmt, 1, id, -1, NULL);
67 ret = sqlite3_step(stmt) == SQLITE_ROW; 71 ret = sqlite3_step(stmt) == SQLITE_ROW;
68 sqlite3_finalize(stmt); 72 sqlite3_finalize(stmt);
69 } 73 }
70 74
71 return ret; 75 return ret;
72 } 76 }
73 77
74 static const char * 78 static const char *
75 create_id(char *id) 79 create_id(void)
76 { 80 {
77 static const char table[] = "abcdefghijklmnopqrstuvwxyz1234567890"; 81 static const char table[] = "abcdefghijklmnopqrstuvwxyz1234567890";
78 82 static char id[12];
79 for (int i = 0; i < ID_MAX; ++i) 83
84 for (size_t i = 0; i < sizeof (id); ++i)
80 id[i] = table[rand() % (sizeof (table) - 1)]; 85 id[i] = table[rand() % (sizeof (table) - 1)];
81 86
82 id[ID_MAX - 1] = 0; 87 return id;
83 } 88 }
84 89
85 static int 90 static int
86 set_id(json_t *paste) 91 set_id(struct paste *paste)
87 { 92 {
93 assert(paste);
94
95 paste->id = NULL;
96
88 /* 97 /*
89 * Avoid infinite loop, we only try to create a new id in 30 steps. 98 * Avoid infinite loop, we only try to create a new id in 30 steps.
90 * 99 *
91 * On error, the function `exist` returns true to indicate we should 100 * On error, the function `exist` returns true to indicate we should
92 * not try to save with that id. 101 * not try to save with that id.
93 */ 102 */
94 int tries = 0; 103 int tries = 0;
95 char id[ID_MAX];
96 104
97 do { 105 do {
98 create_id(id); 106 free(paste->id);
99 } while (++tries < 30 && exists(id)); 107 paste->id = estrdup(create_id());
100 108 } while (++tries < 30 && exists(paste->id));
101 if (tries >= 30) 109
102 return -1; 110 return tries < 30 ? 0 : -1;
103
104 json_object_set_new(paste, "id", json_string(id));
105
106 return 0;
107 } 111 }
108 112
109 int 113 int
110 database_open(const char *path) 114 database_open(const char *path)
111 { 115 {
119 } 123 }
120 124
121 /* Wait for 30 seconds to lock the database. */ 125 /* Wait for 30 seconds to lock the database. */
122 sqlite3_busy_timeout(db, 30000); 126 sqlite3_busy_timeout(db, 30000);
123 127
124 if (sqlite3_exec(db, sql_init, NULL, NULL, NULL) != SQLITE_OK) { 128 if (sqlite3_exec(db, CHAR(sql_init), NULL, NULL, NULL) != SQLITE_OK) {
125 log_warn("database: unable to initialize %s: %s", path, sqlite3_errmsg(db)); 129 log_warn("database: unable to initialize %s: %s", path, sqlite3_errmsg(db));
126 return -1; 130 return -1;
127 } 131 }
128 132
129 return 0; 133 return 0;
130 } 134 }
131 135
132 json_t * 136 int
133 database_recents(size_t limit) 137 database_recents(struct paste *pastes, size_t *max)
134 { 138 {
135 json_t *array = NULL; 139 assert(pastes);
140 assert(max);
141
136 sqlite3_stmt *stmt = NULL; 142 sqlite3_stmt *stmt = NULL;
137 size_t i = 0; 143 size_t i = 0;
138 144
145 memset(pastes, 0, *max * sizeof (struct paste));
139 log_debug("database: accessing most recents"); 146 log_debug("database: accessing most recents");
140 147
141 if (sqlite3_prepare(db, sql_recents, -1, &stmt, NULL) != SQLITE_OK || 148 if (sqlite3_prepare(db, CHAR(sql_recents), -1, &stmt, NULL) != SQLITE_OK ||
142 sqlite3_bind_int64(stmt, 1, limit) != SQLITE_OK) 149 sqlite3_bind_int64(stmt, 1, *max) != SQLITE_OK)
143 goto sqlite_err; 150 goto sqlite_err;
144 151
145 array = json_array(); 152 for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i)
146 153 convert(stmt, &pastes[i]);
147 for (; i < limit && sqlite3_step(stmt) == SQLITE_ROW; ++i)
148 json_array_append_new(array, convert(stmt));
149 154
150 log_debug("database: found %zu pastes", i); 155 log_debug("database: found %zu pastes", i);
151 sqlite3_finalize(stmt); 156 sqlite3_finalize(stmt);
152 157 *max = i;
153 return array; 158
159 return 0;
154 160
155 sqlite_err: 161 sqlite_err:
156 log_warn("database: error (recents): %s\n", sqlite3_errmsg(db)); 162 log_warn("database: error (recents): %s\n", sqlite3_errmsg(db));
157 163
158 if (stmt) 164 if (stmt)
159 sqlite3_finalize(stmt); 165 sqlite3_finalize(stmt);
160 166
161 return NULL; 167 *max = 0;
162 } 168
163 169 return -1;
164 json_t * 170 }
165 database_get(const char *id) 171
166 { 172 int
173 database_get(struct paste *paste, const char *id)
174 {
175 assert(paste);
167 assert(id); 176 assert(id);
168 177
169 json_t *object = NULL;
170 sqlite3_stmt* stmt = NULL; 178 sqlite3_stmt* stmt = NULL;
171 179 int found = -1;
172 log_debug("database: accessing paste with uuid: %s", id); 180
173 181 memset(paste, 0, sizeof (struct paste));
174 if (sqlite3_prepare(db, sql_get, -1, &stmt, NULL) != SQLITE_OK || 182 log_debug("database: accessing paste with id: %s", id);
183
184 if (sqlite3_prepare(db, CHAR(sql_get), -1, &stmt, NULL) != SQLITE_OK ||
175 sqlite3_bind_text(stmt, 1, id, -1, NULL) != SQLITE_OK) 185 sqlite3_bind_text(stmt, 1, id, -1, NULL) != SQLITE_OK)
176 goto sqlite_err; 186 goto sqlite_err;
177 187
178 switch (sqlite3_step(stmt)) { 188 switch (sqlite3_step(stmt)) {
179 case SQLITE_ROW: 189 case SQLITE_ROW:
180 object = convert(stmt); 190 convert(stmt, paste);
191 found = 0;
181 break; 192 break;
182 case SQLITE_MISUSE: 193 case SQLITE_MISUSE:
183 case SQLITE_ERROR: 194 case SQLITE_ERROR:
184 goto sqlite_err; 195 goto sqlite_err;
185 default: 196 default:
186 break; 197 break;
187 } 198 }
188 199
189 sqlite3_finalize(stmt); 200 sqlite3_finalize(stmt);
190 201
191 return object; 202 return found;
192 203
193 sqlite_err: 204 sqlite_err:
194 if (stmt) 205 if (stmt)
195 sqlite3_finalize(stmt); 206 sqlite3_finalize(stmt);
196 207
197 log_warn("database: error (get): %s", sqlite3_errmsg(db)); 208 log_warn("database: error (get): %s", sqlite3_errmsg(db));
198 209
199 return NULL; 210 return -1;
200 } 211 }
201 212
202 int 213 int
203 database_insert(json_t *paste) 214 database_insert(struct paste *paste)
204 { 215 {
205 assert(paste); 216 assert(paste);
206 217
207 sqlite3_stmt *stmt = NULL; 218 sqlite3_stmt *stmt = NULL;
208 219
210 221
211 if (sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL, NULL) != SQLITE_OK) { 222 if (sqlite3_exec(db, "BEGIN EXCLUSIVE TRANSACTION", NULL, NULL, NULL) != SQLITE_OK) {
212 log_warn("database: could not lock database: %s", sqlite3_errmsg(db)); 223 log_warn("database: could not lock database: %s", sqlite3_errmsg(db));
213 return -1; 224 return -1;
214 } 225 }
215
216 if (set_id(paste) < 0) { 226 if (set_id(paste) < 0) {
217 log_warn("database: unable to randomize unique identifier"); 227 log_warn("database: unable to randomize unique identifier");
218 sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL); 228 sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL);
219 return -1; 229 return -1;
220 } 230 }
221 231 if (sqlite3_prepare(db, CHAR(sql_insert), -1, &stmt, NULL) != SQLITE_OK)
222 if (sqlite3_prepare(db, sql_insert, -1, &stmt, NULL) != SQLITE_OK) 232 goto sqlite_err;
223 goto sqlite_err; 233
224 234 sqlite3_bind_text(stmt, 1, paste->id, -1, SQLITE_STATIC);
225 sqlite3_bind_text(stmt, 1, ju_get_string(paste, "id"), -1, SQLITE_STATIC); 235 sqlite3_bind_text(stmt, 2, paste->title, -1, SQLITE_STATIC);
226 sqlite3_bind_text(stmt, 2, ju_get_string(paste, "title"), -1, SQLITE_STATIC); 236 sqlite3_bind_text(stmt, 3, paste->author, -1, SQLITE_STATIC);
227 sqlite3_bind_text(stmt, 3, ju_get_string(paste, "author"), -1, SQLITE_STATIC); 237 sqlite3_bind_text(stmt, 4, paste->language, -1, SQLITE_STATIC);
228 sqlite3_bind_text(stmt, 4, ju_get_string(paste, "language"), -1, SQLITE_STATIC); 238 sqlite3_bind_text(stmt, 5, paste->code, -1, SQLITE_STATIC);
229 sqlite3_bind_text(stmt, 5, ju_get_string(paste, "code"), -1, SQLITE_STATIC); 239 sqlite3_bind_int(stmt, 6, paste->visible);
230 sqlite3_bind_int(stmt, 6, ju_get_bool(paste, "visible")); 240 sqlite3_bind_int64(stmt, 7, paste->duration);
231 sqlite3_bind_int64(stmt, 7, ju_get_int(paste, "duration"));
232 241
233 if (sqlite3_step(stmt) != SQLITE_DONE) 242 if (sqlite3_step(stmt) != SQLITE_DONE)
234 goto sqlite_err; 243 goto sqlite_err;
235 244
236 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); 245 sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
237 sqlite3_finalize(stmt); 246 sqlite3_finalize(stmt);
238 247
239 log_info("database: new paste (%s) from %s expires in one %lld seconds", 248 log_info("database: new paste (%s) from %s expires in one %lld seconds",
240 ju_get_string(paste, "id"), 249 paste->id, paste->author, paste->duration);
241 ju_get_string(paste, "author"),
242 ju_get_int(paste, "duration")
243 );
244 250
245 return 0; 251 return 0;
246 252
247 sqlite_err: 253 sqlite_err:
248 log_warn("database: error (insert): %s", sqlite3_errmsg(db)); 254 log_warn("database: error (insert): %s", sqlite3_errmsg(db));
249 sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL); 255 sqlite3_exec(db, "ROLLBACK", NULL, NULL, NULL);
250 256
251 if (stmt) 257 if (stmt)
252 sqlite3_finalize(stmt); 258 sqlite3_finalize(stmt);
253 259
254 /* Make sure it is not used anymore. */ 260 free(paste->id);
255 json_object_del(paste, "id"); 261 paste->id = NULL;
256 262
257 return 0; 263 return -1;
258 } 264 }
259 265
260 json_t * 266 int
261 database_search(size_t limit, 267 database_search(struct paste *pastes,
268 size_t *max,
262 const char *title, 269 const char *title,
263 const char *author, 270 const char *author,
264 const char *language) 271 const char *language)
265 { 272 {
266 json_t *array = NULL; 273 assert(pastes);
274 assert(max);
275
267 sqlite3_stmt *stmt = NULL; 276 sqlite3_stmt *stmt = NULL;
268 size_t i = 0; 277 size_t i = 0;
269 278
270 log_debug("database: searching title=%s, author=%s, language=%s", 279 log_debug("database: searching title=%s, author=%s, language=%s",
271 title ? title : "", 280 title ? title : "",
272 author ? author : "", 281 author ? author : "",
273 language ? language : ""); 282 language ? language : "");
274 283
284 memset(pastes, 0, *max * sizeof (struct paste));
285
275 /* Select everything if not specified. */ 286 /* Select everything if not specified. */
276 title = title ? title : "%"; 287 title = title ? title : "%";
277 author = author ? author : "%"; 288 author = author ? author : "%";
278 language = language ? language : "%"; 289 language = language ? language : "%";
279 290
280 if (sqlite3_prepare(db, sql_search, -1, &stmt, NULL) != SQLITE_OK) 291 if (sqlite3_prepare(db, CHAR(sql_search), -1, &stmt, NULL) != SQLITE_OK)
281 goto sqlite_err; 292 goto sqlite_err;
282 if (sqlite3_bind_text(stmt, 1, title, -1, NULL) != SQLITE_OK) 293 if (sqlite3_bind_text(stmt, 1, title, -1, NULL) != SQLITE_OK)
283 goto sqlite_err; 294 goto sqlite_err;
284 if (sqlite3_bind_text(stmt, 2, author, -1, NULL) != SQLITE_OK) 295 if (sqlite3_bind_text(stmt, 2, author, -1, NULL) != SQLITE_OK)
285 goto sqlite_err; 296 goto sqlite_err;
286 if (sqlite3_bind_text(stmt, 3, language, -1, NULL) != SQLITE_OK) 297 if (sqlite3_bind_text(stmt, 3, language, -1, NULL) != SQLITE_OK)
287 goto sqlite_err; 298 goto sqlite_err;
288 if (sqlite3_bind_int64(stmt, 4, limit) != SQLITE_OK) 299 if (sqlite3_bind_int64(stmt, 4, *max) != SQLITE_OK)
289 goto sqlite_err; 300 goto sqlite_err;
290 301
291 array = json_array(); 302 for (; i < *max && sqlite3_step(stmt) == SQLITE_ROW; ++i)
292 303 convert(stmt, &pastes[i]);
293 for (; i < limit && sqlite3_step(stmt) == SQLITE_ROW; ++i)
294 json_array_append_new(array, convert(stmt));
295 304
296 log_debug("database: found %zu pastes", i); 305 log_debug("database: found %zu pastes", i);
297 sqlite3_finalize(stmt); 306 sqlite3_finalize(stmt);
298 307 *max = i;
299 return array; 308
309 return 0;
300 310
301 sqlite_err: 311 sqlite_err:
302 log_warn("database: error (search): %s\n", sqlite3_errmsg(db)); 312 log_warn("database: error (search): %s\n", sqlite3_errmsg(db));
303 313
304 if (stmt) 314 if (stmt)
305 sqlite3_finalize(stmt); 315 sqlite3_finalize(stmt);
306 316
307 return NULL; 317 *max = 0;
318
319 return -1;
308 } 320 }
309 321
310 void 322 void
311 database_clear(void) 323 database_clear(void)
312 { 324 {
313 log_debug("database: clearing deprecated pastes"); 325 log_debug("database: clearing deprecated pastes");
314 326
315 if (sqlite3_exec(db, sql_clear, NULL, NULL, NULL) != SQLITE_OK) 327 if (sqlite3_exec(db, CHAR(sql_clear), NULL, NULL, NULL) != SQLITE_OK)
316 log_warn("database: error (clear): %s\n", sqlite3_errmsg(db)); 328 log_warn("database: error (clear): %s\n", sqlite3_errmsg(db));
317 } 329 }
318 330
319 void 331 void
320 database_finish(void) 332 database_finish(void)