Mercurial > sci

comparison lib/db.c @ 19:de4bf839b565

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
misc: revamp SQL
author David Demelier <markand@malikania.fr>
date Fri, 15 Jul 2022 11:11:48 +0200
parents
children f98ea578b1ef
comparison
equal deleted inserted replaced
18:600204c31bf0 19:de4bf839b565
1 /*
2 * db.c -- scid database access
3 *
4 * Copyright (c) 2021 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 <string.h>
22
23 #include <sqlite3.h>
24
25 #include <utlist.h>
26
27 #include "db.h"
28 #include "log.h"
29 #include "types.h"
30 #include "util.h"
31
32 #include "sql/init.h"
33 #include "sql/job-add.h"
34 #include "sql/job-todo.h"
35 #include "sql/jobresult-add.h"
36 #include "sql/project-add.h"
37 #include "sql/project-update.h"
38 #include "sql/project-find.h"
39 #include "sql/project-find-id.h"
40 #include "sql/project-list.h"
41 #include "sql/worker-add.h"
42 #include "sql/worker-find.h"
43 #include "sql/worker-find-id.h"
44 #include "sql/worker-list.h"
45
46 #define CHAR(v) (const char *)(v)
47
48 static sqlite3 *db;
49
50 typedef void (*unpacker)(sqlite3_stmt *, struct db_ctx *, void *);
51
52 struct str {
53 char *str;
54 struct str *next;
55 };
56
57 struct list {
58 unpacker unpack;
59 void *data;
60 size_t datasz;
61 size_t elemwidth;
62 struct db_ctx *ctx;
63 };
64
65 static const char *
66 strlist_add(struct db_ctx *ctx, const char *text)
67 {
68 struct str *s, *list = ctx->handle;
69
70 s = util_calloc(1, sizeof (*s));
71 s->str = util_strdup(text);
72 LL_APPEND(list, s);
73
74 return s->str;
75 }
76
77 static void
78 strlist_free(struct db_ctx *ctx)
79 {
80 struct str *s, *tmp, *list = ctx->handle;
81
82 LL_FOREACH_SAFE(list, s, tmp) {
83 free(s->str);
84 free(s);
85 }
86
87 ctx->handle = NULL;
88 }
89
90 static void
91 project_unpacker(sqlite3_stmt *stmt, struct db_ctx *ctx, struct project *project)
92 {
93 project->id = sqlite3_column_int(stmt, 0);
94 project->name = strlist_add(ctx, CHAR(sqlite3_column_text(stmt, 1)));
95 project->desc = strlist_add(ctx, CHAR(sqlite3_column_text(stmt, 2)));
96 project->url = strlist_add(ctx, CHAR(sqlite3_column_text(stmt, 3)));
97 project->script = strlist_add(ctx, CHAR(sqlite3_column_text(stmt, 4)));
98 }
99
100 static void
101 worker_unpacker(sqlite3_stmt *stmt, struct db_ctx *ctx, struct worker *w)
102 {
103 w->id = sqlite3_column_int(stmt, 0);
104 w->name = strlist_add(ctx, CHAR(sqlite3_column_text(stmt, 1)));
105 w->desc = strlist_add(ctx, CHAR(sqlite3_column_text(stmt, 2)));
106 }
107
108 static void
109 job_unpacker(sqlite3_stmt *stmt, struct db_ctx *ctx, struct job *job)
110 {
111 job->id = sqlite3_column_int(stmt, 0);
112 job->tag = strlist_add(ctx, CHAR(sqlite3_column_text(stmt, 1)));
113 job->project_id = sqlite3_column_int(stmt, 2);
114 }
115
116 static void
117 vbind(sqlite3_stmt *stmt, const char *fmt, va_list ap)
118 {
119 for (int index = 1; *fmt; ++fmt) {
120 switch (*fmt) {
121 case 'i':
122 sqlite3_bind_int(stmt, index++, va_arg(ap, int));
123 break;
124 case 's':
125 sqlite3_bind_text(stmt, index++, va_arg(ap, const char *), -1, SQLITE_STATIC);
126 break;
127 case 'z':
128 sqlite3_bind_int64(stmt, index++, va_arg(ap, size_t));
129 break;
130 default:
131 break;
132 }
133 }
134 }
135
136 static int
137 insert(const char *sql, const char *fmt, ...)
138 {
139 assert(sql);
140 assert(fmt);
141
142 sqlite3_stmt *stmt = NULL;
143 va_list ap;
144 int ret = -1;
145
146 if (sqlite3_prepare(db, sql, -1, &stmt, NULL) != SQLITE_OK)
147 return log_warn("db: %s", sqlite3_errmsg(db)), -1;
148
149 va_start(ap, fmt);
150 vbind(stmt, fmt, ap);
151 va_end(ap);
152
153 if (sqlite3_step(stmt) != SQLITE_DONE)
154 log_warn("db: %s", sqlite3_errmsg(db));
155 else
156 ret = sqlite3_last_insert_rowid(db);
157
158 sqlite3_finalize(stmt);
159
160 return ret;
161 }
162
163 static int
164 update(const char *sql, const char *fmt, ...)
165 {
166 assert(sql);
167 assert(fmt);
168
169 sqlite3_stmt *stmt = NULL;
170 va_list ap;
171 int ret = 1;
172
173 if (sqlite3_prepare(db, sql, -1, &stmt, NULL) != SQLITE_OK)
174 return log_warn("db: %s", sqlite3_errmsg(db)), -1;
175
176 va_start(ap, fmt);
177 vbind(stmt, fmt, ap);
178 va_end(ap);
179
180 if (sqlite3_step(stmt) != SQLITE_DONE)
181 log_warn("db: %s", sqlite3_errmsg(db));
182 else
183 ret = 0;
184
185 sqlite3_finalize(stmt);
186
187 return ret;
188 }
189
190 static ssize_t
191 list(struct list *sel, const char *sql, const char *args, ...)
192 {
193 sqlite3_stmt *stmt = NULL;
194
195 va_list ap;
196 int step;
197 ssize_t ret = -1;
198 size_t tot = 0;
199
200 sel->ctx->handle = NULL;
201
202 if (sqlite3_prepare(db, sql, -1, &stmt, NULL) != SQLITE_OK)
203 return log_warn("db: %s", sqlite3_errmsg(db)), -1;
204
205 va_start(ap, args);
206 vbind(stmt, args, ap);
207 va_end(ap);
208
209 while (tot < sel->datasz && (step = sqlite3_step(stmt)) == SQLITE_ROW)
210 sel->unpack(stmt, sel->ctx, (unsigned char *)sel->data + (tot++ * sel->elemwidth));
211
212 if (step == SQLITE_OK || step == SQLITE_DONE || step == SQLITE_ROW)
213 ret = tot;
214 else {
215 memset(sel->data, 0, sel->datasz * sel->elemwidth);
216 strlist_free(sel->ctx->handle);
217 sel->ctx->handle = NULL;
218 }
219
220 sqlite3_finalize(stmt);
221
222 return ret;
223 }
224
225 int
226 db_open(const char *path)
227 {
228 assert(path);
229
230 if (sqlite3_open(path, &db) != SQLITE_OK)
231 return log_warn("db: open error: %s", sqlite3_errmsg(db)), -1;
232
233 /* Wait for 30 seconds to lock the database. */
234 sqlite3_busy_timeout(db, 30000);
235
236 if (sqlite3_exec(db, CHAR(sql_init), NULL, NULL, NULL) != SQLITE_OK)
237 return log_warn("db: initialization error: %s", sqlite3_errmsg(db)), -1;
238
239 return 0;
240 }
241
242 int
243 db_project_add(struct project *p)
244 {
245 return (p->id = insert(CHAR(sql_project_add), "ssss", p->name, p->desc,
246 p->url, p->script)) < 0 ? -1 : 0;
247 }
248
249 int
250 db_project_update(const struct project *p)
251 {
252 assert(p);
253
254 return update(CHAR(sql_project_update), "ssssi", p->name, p->desc,
255 p->url, p->script, p->id);
256 }
257
258 ssize_t
259 db_project_list(struct db_ctx *ctx, struct project *projects, size_t projectsz)
260 {
261 struct list sel = {
262 .unpack = (unpacker)project_unpacker,
263 .data = projects,
264 .datasz = projectsz,
265 .elemwidth = sizeof (*projects),
266 .ctx = ctx
267 };
268
269 return list(&sel, CHAR(sql_project_list), "z", projectsz);
270 }
271
272 int
273 db_project_find(struct db_ctx *ctx, struct project *project)
274 {
275 struct list sel = {
276 .unpack = (unpacker)project_unpacker,
277 .data = project,
278 .datasz = 1,
279 .elemwidth = sizeof (*project),
280 .ctx = ctx
281 };
282
283 return list(&sel, CHAR(sql_project_find), "s", project->name) == 1 ? 0 : -1;
284 }
285
286 int
287 db_project_find_id(struct db_ctx *ctx, struct project *project)
288 {
289 struct list sel = {
290 .unpack = (unpacker)project_unpacker,
291 .data = project,
292 .datasz = 1,
293 .elemwidth = sizeof (*project),
294 .ctx = ctx
295 };
296
297 return list(&sel, CHAR(sql_project_find_id), "i", project->id) == 1 ? 0 : -1;
298 }
299
300 int
301 db_worker_add(struct worker *wk)
302 {
303 assert(wk);
304
305 return (wk->id = insert(CHAR(sql_worker_add), "ss", wk->name, wk->desc)) < 0 ? -1 : 0;
306 }
307
308 ssize_t
309 db_worker_list(struct db_ctx *ctx, struct worker *wk, size_t wksz)
310 {
311 assert(ctx);
312 assert(wk);
313
314 struct list sel = {
315 .unpack = (unpacker)worker_unpacker,
316 .data = wk,
317 .datasz = wksz,
318 .elemwidth = sizeof (*wk),
319 .ctx = ctx
320 };
321
322 return list(&sel, CHAR(sql_worker_list), "z", wksz);
323 }
324
325 int
326 db_worker_find(struct db_ctx *ctx, struct worker *wk)
327 {
328 struct list sel = {
329 .unpack = (unpacker)worker_unpacker,
330 .data = wk,
331 .datasz = 1,
332 .elemwidth = sizeof (*wk),
333 .ctx = ctx
334 };
335
336 return list(&sel, CHAR(sql_worker_find), "s", wk->name) == 1 ? 0 : -1;
337 }
338
339 int
340 db_worker_find_id(struct db_ctx *ctx, struct worker *wk)
341 {
342 struct list sel = {
343 .unpack = (unpacker)worker_unpacker,
344 .data = wk,
345 .datasz = 1,
346 .elemwidth = sizeof (*wk),
347 .ctx = ctx
348 };
349
350 return list(&sel, CHAR(sql_worker_find_id), "i", wk->id) == 1 ? 0 : -1;
351 }
352
353 int
354 db_job_add(struct job *job)
355 {
356 assert(job);
357
358 return (job->id = insert(CHAR(sql_job_add),
359 "si", job->tag, job->project_id)) < 0 ? -1 : 0;
360 }
361
362 ssize_t
363 db_job_todo(struct db_ctx *ctx, struct job *jobs, size_t jobsz, int worker_id)
364 {
365 assert(ctx);
366 assert(jobs);
367
368 struct list sel = {
369 .unpack = (unpacker)job_unpacker,
370 .data = jobs,
371 .datasz = jobsz,
372 .elemwidth = sizeof (*jobs),
373 .ctx = ctx
374 };
375
376 return list(&sel, CHAR(sql_job_todo), "iiz", worker_id, worker_id, jobsz);
377 }
378
379 int
380 db_jobresult_add(struct jobresult *r)
381 {
382 assert(r);
383
384 return (r->id = insert(CHAR(sql_jobresult_add), "iiis", r->job_id,
385 r->worker_id, r->exitcode, r->log)) < 0 ? -1 : 0;
386 }
387
388 void
389 db_finish(void)
390 {
391 if (db) {
392 sqlite3_close(db);
393 db = NULL;
394 }
395 }
396
397 void
398 db_ctx_finish(struct db_ctx *ctx)
399 {
400 if (ctx->handle) {
401 strlist_free(ctx->handle);
402 ctx->handle = NULL;
403 }
404 }