Mercurial > sci

comparison db.c @ 3:215c0c3b3609

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
misc: use JSON everywhere (scictl/sciwebd)
author David Demelier <markand@malikania.fr>
date Mon, 14 Jun 2021 22:08:24 +0200
parents 5fa3d2f479b2
children 9c4fea43803c
comparison
equal deleted inserted replaced
2:5fa3d2f479b2 3:215c0c3b3609
1 #include <sys/queue.h>
1 #include <assert.h> 2 #include <assert.h>
2 #include <stdlib.h> 3 #include <stdlib.h>
3 #include <string.h> 4 #include <string.h>
4 5
5 #include <sqlite3.h> 6 #include <sqlite3.h>
6 7
7 #include "db.h" 8 #include "db.h"
8 #include "job.h"
9 #include "log.h" 9 #include "log.h"
10 #include "project.h" 10 #include "types.h"
11 #include "worker.h" 11 #include "util.h"
12 12
13 #include "sql/init.h" 13 #include "sql/init.h"
14 #include "sql/job-queue.h" 14 #include "sql/job-add.h"
15 #include "sql/job-result-todo.h" 15 #include "sql/job-todo.h"
16 #include "sql/job-save.h" 16 #include "sql/jobresult-add.h"
17 #include "sql/project-insert.h" 17 #include "sql/project-add.h"
18 #include "sql/project-get.h"
19 #include "sql/project-find.h" 18 #include "sql/project-find.h"
20 #include "sql/worker-get.h" 19 #include "sql/project-find-id.h"
20 #include "sql/project-list.h"
21 #include "sql/worker-add.h"
21 #include "sql/worker-find.h" 22 #include "sql/worker-find.h"
22 #include "sql/worker-insert.h" 23 #include "sql/worker-find-id.h"
24 #include "sql/worker-list.h"
23 25
24 #define CHAR(v) (const char *)(v) 26 #define CHAR(v) (const char *)(v)
25 27
26 static sqlite3 *db; 28 static sqlite3 *db;
27 29
30 struct str {
31 char *str;
32 SLIST_ENTRY(str) link;
33 };
34
35 SLIST_HEAD(strlist, str);
36
37 static struct strlist *
38 strlist_new(void)
39 {
40 struct strlist *l;
41
42 l = util_calloc(1, sizeof (*l));
43 SLIST_INIT(l);
44
45 return l;
46 }
47
48 static const char *
49 strlist_add(struct strlist *l, const char *text)
50 {
51 struct str *s;
52
53 s = util_calloc(1, sizeof (*s));
54 s->str = util_strdup(text);
55
56 SLIST_INSERT_HEAD(l, s, link);
57
58 return s->str;
59 }
60
61 static void
62 strlist_free(struct strlist *l)
63 {
64 struct str *s, *tmp;
65
66 SLIST_FOREACH_SAFE(s, l, link, tmp) {
67 free(s->str);
68 free(s);
69 }
70
71 SLIST_INIT(l);
72 }
73
28 static inline void 74 static inline void
29 convert_project(struct project *project, sqlite3_stmt *stmt) 75 convert_project(struct db_ctx *ctx, struct project *project, sqlite3_stmt *stmt)
30 { 76 {
31 project->id = sqlite3_column_int64(stmt, 0); 77 project->id = sqlite3_column_int(stmt, 0);
32 strlcpy(project->name, CHAR(sqlite3_column_text(stmt, 1)), sizeof (project->name)); 78 project->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1)));
33 strlcpy(project->desc, CHAR(sqlite3_column_text(stmt, 2)), sizeof (project->desc)); 79 project->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2)));
34 strlcpy(project->url, CHAR(sqlite3_column_text(stmt, 3)), sizeof (project->url)); 80 project->url = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 3)));
35 strlcpy(project->script, CHAR(sqlite3_column_text(stmt, 4)), sizeof (project->script)); 81 project->script = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 4)));
82 }
83
84 static int
85 insert(const char *sql, const char *fmt, ...)
86 {
87 assert(sql);
88 assert(fmt);
89
90 sqlite3_stmt *stmt = NULL;
91 va_list ap;
92
93 if (sqlite3_prepare(db, sql, -1, &stmt, NULL) != SQLITE_OK)
94 return log_warn("db: %s", sqlite3_errmsg(db)), -1;
95
96 va_start(ap, fmt);
97
98 for (int index = 1; *fmt; ++fmt) {
99 switch (*fmt) {
100 case 'i':
101 sqlite3_bind_int(stmt, index++, va_arg(ap, int));
102 break;
103 case 's':
104 sqlite3_bind_text(stmt, index++, va_arg(ap, const char *), -1, SQLITE_STATIC);
105 break;
106 default:
107 break;
108 }
109 }
110
111 va_end(ap);
112
113 if (sqlite3_step(stmt) != SQLITE_DONE) {
114 log_warn("db: %s", sqlite3_errmsg(db));
115 sqlite3_finalize(stmt);
116 return -1;
117 }
118
119 return sqlite3_last_insert_rowid(db);
36 } 120 }
37 121
38 int 122 int
39 db_open(const char *path) 123 db_open(const char *path)
40 { 124 {
62 assert(pj); 146 assert(pj);
63 147
64 sqlite3_stmt *stmt = NULL; 148 sqlite3_stmt *stmt = NULL;
65 int ret = -1; 149 int ret = -1;
66 150
67 if (sqlite3_prepare(db, CHAR(sql_project_insert), -1, &stmt, NULL) != SQLITE_OK) 151 if (sqlite3_prepare(db, CHAR(sql_project_add), -1, &stmt, NULL) != SQLITE_OK)
68 goto sqlite3_err; 152 goto sqlite3_err;
69 153
70 sqlite3_bind_text(stmt, 1, pj->name, -1, SQLITE_STATIC); 154 sqlite3_bind_text(stmt, 1, pj->name, -1, SQLITE_STATIC);
71 sqlite3_bind_text(stmt, 2, pj->desc, -1, SQLITE_STATIC); 155 sqlite3_bind_text(stmt, 2, pj->desc, -1, SQLITE_STATIC);
72 sqlite3_bind_text(stmt, 3, pj->url, -1, SQLITE_STATIC); 156 sqlite3_bind_text(stmt, 3, pj->url, -1, SQLITE_STATIC);
84 168
85 return ret; 169 return ret;
86 } 170 }
87 171
88 ssize_t 172 ssize_t
89 db_project_get(struct project *projects, size_t projectsz) 173 db_project_list(struct db_ctx *ctx, struct project *projects, size_t projectsz)
90 { 174 {
91 assert(projects); 175 assert(projects);
92 176
93 sqlite3_stmt *stmt = NULL; 177 sqlite3_stmt *stmt = NULL;
94 struct project *p = projects; 178 struct project *p = projects;
95 ssize_t ret = 0; 179 ssize_t ret = 0;
96 180
97 if (sqlite3_prepare(db, CHAR(sql_project_get), -1, &stmt, NULL) != SQLITE_OK) { 181 if (sqlite3_prepare(db, CHAR(sql_project_list), -1, &stmt, NULL) != SQLITE_OK) {
98 log_warn("db: %s", sqlite3_errmsg(db)); 182 log_warn("db: %s", sqlite3_errmsg(db));
99 return -1; 183 return -1;
100 } 184 }
101 185
102 sqlite3_bind_int64(stmt, 1, projectsz); 186 sqlite3_bind_int(stmt, 1, projectsz);
187 ctx->handle = strlist_new();
103 188
104 for (; sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret < projectsz; ++ret, ++p) 189 for (; sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret < projectsz; ++ret, ++p)
105 convert_project(p, stmt); 190 convert_project(ctx, p, stmt);
106 191
107 if (stmt) 192 if (stmt)
108 sqlite3_finalize(stmt); 193 sqlite3_finalize(stmt);
109 194
110 return ret; 195 return ret;
111 } 196 }
112 197
113 int 198 int
114 db_project_find(struct project *project) 199 db_project_find(struct db_ctx *ctx, struct project *project)
115 { 200 {
201 assert(ctx);
116 assert(project); 202 assert(project);
117 203
118 sqlite3_stmt *stmt = NULL; 204 sqlite3_stmt *stmt = NULL;
119 int ret = -1; 205 int ret = -1;
120 206
207 ctx->handle = NULL;
208
121 if (sqlite3_prepare(db, CHAR(sql_project_find), -1, &stmt, NULL) != SQLITE_OK) 209 if (sqlite3_prepare(db, CHAR(sql_project_find), -1, &stmt, NULL) != SQLITE_OK)
122 goto sqlite3_err; 210 goto sqlite3_err;
123 211
124 sqlite3_bind_text(stmt, 1, project->name, -1, SQLITE_STATIC); 212 sqlite3_bind_text(stmt, 1, project->name, -1, SQLITE_STATIC);
125 213
126 if (sqlite3_step(stmt) != SQLITE_ROW) 214 if (sqlite3_step(stmt) != SQLITE_ROW)
127 goto sqlite3_err; 215 goto sqlite3_err;
128 216
129 ret = 0; 217 ret = 0;
130 convert_project(project, stmt); 218 ctx->handle = strlist_new();
131 219 convert_project(ctx, project, stmt);
132 sqlite3_err: 220
133 if (ret < 0) 221 sqlite3_err:
134 log_warn("db: %s", sqlite3_errmsg(db)); 222 if (ret < 0) {
223 if (ctx->handle)
224 db_ctx_finish(ctx);
225
226 log_warn("db: %s", sqlite3_errmsg(db));
227 }
228 if (stmt)
229 sqlite3_finalize(stmt);
230
231 return ret;
232 }
233
234 int
235 db_project_find_id(struct db_ctx *ctx, struct project *project)
236 {
237 assert(ctx);
238 assert(project);
239
240 sqlite3_stmt *stmt = NULL;
241 int ret = -1;
242
243 ctx->handle = NULL;
244
245 if (sqlite3_prepare(db, CHAR(sql_project_find_id), -1, &stmt, NULL) != SQLITE_OK)
246 goto sqlite3_err;
247
248 sqlite3_bind_int(stmt, 1, project->id);
249
250 if (sqlite3_step(stmt) != SQLITE_ROW)
251 goto sqlite3_err;
252
253 ret = 0;
254 ctx->handle = strlist_new();
255 convert_project(ctx, project, stmt);
256
257 sqlite3_err:
258 if (ret < 0) {
259 if (ctx->handle)
260 db_ctx_finish(ctx);
261
262 log_warn("db: %s", sqlite3_errmsg(db));
263 }
135 if (stmt) 264 if (stmt)
136 sqlite3_finalize(stmt); 265 sqlite3_finalize(stmt);
137 266
138 return ret; 267 return ret;
139 } 268 }
144 assert(wk); 273 assert(wk);
145 274
146 sqlite3_stmt *stmt = NULL; 275 sqlite3_stmt *stmt = NULL;
147 int ret = -1; 276 int ret = -1;
148 277
149 if (sqlite3_prepare(db, CHAR(sql_worker_insert), -1, &stmt, NULL) != SQLITE_OK) 278 if (sqlite3_prepare(db, CHAR(sql_worker_add), -1, &stmt, NULL) != SQLITE_OK)
150 goto sqlite3_err; 279 goto sqlite3_err;
151 280
152 sqlite3_bind_text(stmt, 1, wk->name, -1, SQLITE_STATIC); 281 sqlite3_bind_text(stmt, 1, wk->name, -1, SQLITE_STATIC);
153 sqlite3_bind_text(stmt, 2, wk->desc, -1, SQLITE_STATIC); 282 sqlite3_bind_text(stmt, 2, wk->desc, -1, SQLITE_STATIC);
154 283
164 293
165 return ret; 294 return ret;
166 } 295 }
167 296
168 ssize_t 297 ssize_t
169 db_worker_get(struct worker *wk, size_t wksz) 298 db_worker_list(struct db_ctx *ctx, struct worker *wk, size_t wksz)
170 { 299 {
300 assert(ctx);
171 assert(wk); 301 assert(wk);
172 302
173 sqlite3_stmt *stmt = NULL; 303 sqlite3_stmt *stmt = NULL;
174 struct worker *w = wk; 304 struct worker *w = wk;
175 ssize_t ret = -1; 305 ssize_t ret = -1;
176 306
177 if (sqlite3_prepare(db, CHAR(sql_worker_get), -1, &stmt, NULL) != SQLITE_OK) 307 ctx->handle = NULL;
178 goto sqlite3_err; 308
179 309 if (sqlite3_prepare(db, CHAR(sql_worker_list), -1, &stmt, NULL) != SQLITE_OK)
180 sqlite3_bind_int64(stmt, 1, wksz); 310 goto sqlite3_err;
311
312 sqlite3_bind_int(stmt, 1, wksz);
313 ctx->handle = strlist_new();
181 314
182 for (ret = 0; sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret < wksz; ++ret, ++w) { 315 for (ret = 0; sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret < wksz; ++ret, ++w) {
183 w->id = sqlite3_column_int64(stmt, 0); 316 w->id = sqlite3_column_int(stmt, 0);
184 strlcpy(w->name, CHAR(sqlite3_column_text(stmt, 1)), sizeof (w->name)); 317 w->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1)));
185 strlcpy(w->desc, CHAR(sqlite3_column_text(stmt, 2)), sizeof (w->desc)); 318 w->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2)));
186 } 319 }
187 320
188 sqlite3_err: 321 sqlite3_err:
189 if (ret < 0) 322 if (ret < 0) {
190 log_warn("db: %s", sqlite3_errmsg(db)); 323 if (ctx->handle)
191 if (stmt) 324 db_ctx_finish(ctx);
192 sqlite3_finalize(stmt); 325
193 326 log_warn("db: %s", sqlite3_errmsg(db));
194 return ret; 327 }
195 } 328 if (stmt)
196 329 sqlite3_finalize(stmt);
197 int 330
198 db_worker_find(struct worker *w) 331 return ret;
199 { 332 }
333
334 int
335 db_worker_find(struct db_ctx *ctx, struct worker *w)
336 {
337 assert(ctx);
200 assert(w); 338 assert(w);
201 339
202 sqlite3_stmt *stmt = NULL; 340 sqlite3_stmt *stmt = NULL;
203 int ret = -1; 341 int ret = -1;
204 342
343 ctx->handle = NULL;
344
205 if (sqlite3_prepare(db, CHAR(sql_worker_find), -1, &stmt, NULL) != SQLITE_OK) 345 if (sqlite3_prepare(db, CHAR(sql_worker_find), -1, &stmt, NULL) != SQLITE_OK)
206 goto sqlite3_err; 346 goto sqlite3_err;
207 347
208 sqlite3_bind_text(stmt, 1, w->name, -1, SQLITE_STATIC); 348 sqlite3_bind_text(stmt, 1, w->name, -1, SQLITE_STATIC);
209 349
210 if (sqlite3_step(stmt) != SQLITE_ROW) 350 if (sqlite3_step(stmt) != SQLITE_ROW)
211 goto sqlite3_err; 351 goto sqlite3_err;
212 352
213 ret = 0; 353 ret = 0;
214 w->id = sqlite3_column_int64(stmt, 0); 354 ctx->handle = strlist_new();
215 strlcpy(w->name, CHAR(sqlite3_column_text(stmt, 1)), sizeof (w->name)); 355 w->id = sqlite3_column_int(stmt, 0);
216 strlcpy(w->desc, CHAR(sqlite3_column_text(stmt, 2)), sizeof (w->desc)); 356 w->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1)));
217 357 w->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2)));
218 sqlite3_err: 358
219 if (ret < 0) 359 sqlite3_err:
220 log_warn("db: %s", sqlite3_errmsg(db)); 360 if (ret < 0) {
221 if (stmt) 361 if (ctx->handle)
222 sqlite3_finalize(stmt); 362 db_ctx_finish(ctx);
223 363
224 return ret; 364 log_warn("db: %s", sqlite3_errmsg(db));
225 } 365 }
226 366 if (stmt)
227 int 367 sqlite3_finalize(stmt);
228 db_job_queue(struct job *job) 368
369 return ret;
370 }
371
372 int
373 db_worker_find_id(struct db_ctx *ctx, struct worker *w)
374 {
375 assert(ctx);
376 assert(w);
377
378 sqlite3_stmt *stmt = NULL;
379 int ret = -1;
380
381 ctx->handle = NULL;
382
383 if (sqlite3_prepare(db, CHAR(sql_worker_find_id), -1, &stmt, NULL) != SQLITE_OK)
384 goto sqlite3_err;
385
386 sqlite3_bind_int(stmt, 1, w->id);
387
388 if (sqlite3_step(stmt) != SQLITE_ROW)
389 goto sqlite3_err;
390
391 ret = 0;
392 ctx->handle = strlist_new();
393 w->id = sqlite3_column_int(stmt, 0);
394 w->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1)));
395 w->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2)));
396
397 sqlite3_err:
398 if (ret < 0) {
399 if (ctx->handle)
400 db_ctx_finish(ctx);
401
402 log_warn("db: %s", sqlite3_errmsg(db));
403 }
404 if (stmt)
405 sqlite3_finalize(stmt);
406
407 return ret;
408 }
409
410 int
411 db_job_add(struct job *job)
229 { 412 {
230 assert(job); 413 assert(job);
231 414
232 sqlite3_stmt *stmt = NULL; 415 job->id = insert(CHAR(sql_job_add), "si", job->tag, job->project_id);
233 int ret = -1; 416
234 417 return job->id < 0 ? -1 : 0;
235 if (sqlite3_prepare(db, CHAR(sql_job_queue), -1, &stmt, NULL) != SQLITE_OK)
236 goto sqlite3_err;
237
238 sqlite3_bind_text(stmt, 1, job->tag, -1, SQLITE_STATIC);
239 sqlite3_bind_int64(stmt, 2, job->project.id);
240
241 if (sqlite3_step(stmt) != SQLITE_DONE)
242 goto sqlite3_err;
243
244 job->id = sqlite3_last_insert_rowid(db);
245 ret = 0;
246
247 sqlite3_err:
248 if (ret < 0)
249 log_warn("db: %s", sqlite3_errmsg(db));
250 if (stmt)
251 sqlite3_finalize(stmt);
252
253 return ret;
254 } 418 }
255 419
256 ssize_t 420 ssize_t
257 db_job_result_todo(struct job_result *re, size_t resz, int64_t worker_id) 421 db_job_todo(struct db_ctx *ctx, struct job *jobs, size_t jobsz, int worker_id)
258 { 422 {
259 assert(re); 423 assert(ctx);
424 assert(jobs);
260 425
261 sqlite3_stmt *stmt = NULL; 426 sqlite3_stmt *stmt = NULL;
262 ssize_t ret = 0; 427 ssize_t ret = 0;
263 428
264 if (sqlite3_prepare(db, CHAR(sql_job_result_todo), -1, &stmt, NULL) != SQLITE_OK) { 429 if (sqlite3_prepare(db, CHAR(sql_job_todo), -1, &stmt, NULL) != SQLITE_OK) {
265 log_warn("db: %s", sqlite3_errmsg(db)); 430 log_warn("db: %s", sqlite3_errmsg(db));
266 return -1; 431 return -1;
267 } 432 }
268 433
269 sqlite3_bind_int64(stmt, 1, worker_id); 434 sqlite3_bind_int(stmt, 1, worker_id);
270 sqlite3_bind_int64(stmt, 2, resz); 435 sqlite3_bind_int(stmt, 2, jobsz);
271 436 ctx->handle = strlist_new();
272 while (sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret++ < resz) { 437
273 memset(re, 0, sizeof (*re)); 438 while (sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret++ < jobsz) {
274 re->job.id = sqlite3_column_int64(stmt, 0); 439 jobs->id = sqlite3_column_int(stmt, 0);
275 strlcpy(re->job.tag, CHAR(sqlite3_column_text(stmt, 1)), 440 jobs->tag = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1)));
276 sizeof (re->job.tag)); 441 jobs++->project_id = sqlite3_column_int(stmt, 2);
277 strlcpy(re->job.project.name, CHAR(sqlite3_column_text(stmt, 2)),
278 sizeof (re->job.project.name));
279
280 ++re;
281 }; 442 };
282 443
283 if (stmt) 444 sqlite3_finalize(stmt);
284 sqlite3_finalize(stmt); 445
285 446 return ret;
286 return ret; 447 }
287 } 448
288 449 int
289 int 450 db_jobresult_add(struct jobresult *r)
290 db_job_save(struct job_result *r)
291 { 451 {
292 assert(r); 452 assert(r);
293 453
294 sqlite3_stmt *stmt = NULL; 454 r->id = insert(CHAR(sql_jobresult_add), "iiis", r->job_id,
295 int ret = -1; 455 r->worker_id, r->exitcode, r->log);
296 456
297 if (sqlite3_prepare(db, CHAR(sql_job_save), -1, &stmt, NULL) != SQLITE_OK) 457 return r->id < 0 ? -1 : 0;
298 goto sqlite3_err;
299
300 sqlite3_bind_int64(stmt, 1, r->job.id);
301 sqlite3_bind_int64(stmt, 2, r->worker.id);
302 sqlite3_bind_int(stmt, 3, r->status);
303 sqlite3_bind_int(stmt, 4, r->retcode);
304 sqlite3_bind_text(stmt, 5, r->console, -1, SQLITE_STATIC);
305
306 if (sqlite3_step(stmt) != SQLITE_DONE)
307 goto sqlite3_err;
308
309 ret = 0;
310 r->id = sqlite3_last_insert_rowid(db);
311
312 sqlite3_err:
313 if (ret < 0)
314 log_warn("db: %s", sqlite3_errmsg(db));
315 if (stmt)
316 sqlite3_finalize(stmt);
317
318 return ret;
319 } 458 }
320 459
321 void 460 void
322 db_finish(void) 461 db_finish(void)
323 { 462 {
324 if (db) { 463 if (db) {
325 sqlite3_close(db); 464 sqlite3_close(db);
326 db = NULL; 465 db = NULL;
327 } 466 }
328 } 467 }
468
469 void
470 db_ctx_finish(struct db_ctx *ctx)
471 {
472 if (ctx->handle) {
473 strlist_free(ctx->handle);
474 ctx->handle = NULL;
475 }
476 }