Mercurial > sci
changeset 4:9c4fea43803c
scid: some refactoring in db
author | David Demelier <markand@malikania.fr> |
---|---|
date | Tue, 15 Jun 2021 20:42:55 +0200 |
parents | 215c0c3b3609 |
children | 566bc028cdcb |
files | db.c scictl.c scid.c sql/job-todo.sql sql/jobresult-add.sql |
diffstat | 5 files changed, 160 insertions(+), 267 deletions(-) [+] |
line wrap: on
line diff
--- a/db.c Mon Jun 14 22:08:24 2021 +0200 +++ b/db.c Tue Jun 15 20:42:55 2021 +0200 @@ -27,11 +27,21 @@ static sqlite3 *db; +typedef void (*unpacker)(sqlite3_stmt *, struct db_ctx *, void *); + struct str { char *str; SLIST_ENTRY(str) link; }; +struct list { + unpacker unpack; + void *data; + size_t datasz; + size_t elemwidth; + struct db_ctx *ctx; +}; + SLIST_HEAD(strlist, str); static struct strlist * @@ -71,8 +81,8 @@ SLIST_INIT(l); } -static inline void -convert_project(struct db_ctx *ctx, struct project *project, sqlite3_stmt *stmt) +static void +project_unpacker(sqlite3_stmt *stmt, struct db_ctx *ctx, struct project *project) { project->id = sqlite3_column_int(stmt, 0); project->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1))); @@ -81,6 +91,42 @@ project->script = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 4))); } +static void +worker_unpacker(sqlite3_stmt *stmt, struct db_ctx *ctx, struct worker *w) +{ + w->id = sqlite3_column_int(stmt, 0); + w->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1))); + w->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2))); +} + +static void +job_unpacker(sqlite3_stmt *stmt, struct db_ctx *ctx, struct job *job) +{ + job->id = sqlite3_column_int(stmt, 0); + job->tag = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1))); + job->project_id = sqlite3_column_int(stmt, 2); +} + +static void +vbind(sqlite3_stmt *stmt, const char *fmt, va_list ap) +{ + for (int index = 1; *fmt; ++fmt) { + switch (*fmt) { + case 'i': + sqlite3_bind_int(stmt, index++, va_arg(ap, int)); + break; + case 's': + sqlite3_bind_text(stmt, index++, va_arg(ap, const char *), -1, SQLITE_STATIC); + break; + case 'z': + sqlite3_bind_int64(stmt, index++, va_arg(ap, size_t)); + break; + default: + break; + } + } +} + static int insert(const char *sql, const char *fmt, ...) { @@ -94,20 +140,7 @@ return log_warn("db: %s", sqlite3_errmsg(db)), -1; va_start(ap, fmt); - - for (int index = 1; *fmt; ++fmt) { - switch (*fmt) { - case 'i': - sqlite3_bind_int(stmt, index++, va_arg(ap, int)); - break; - case 's': - sqlite3_bind_text(stmt, index++, va_arg(ap, const char *), -1, SQLITE_STATIC); - break; - default: - break; - } - } - + vbind(stmt, fmt, ap); va_end(ap); if (sqlite3_step(stmt) != SQLITE_DONE) { @@ -119,6 +152,43 @@ return sqlite3_last_insert_rowid(db); } +static ssize_t +list(struct list *sel, const char *sql, const char *args, ...) +{ + sqlite3_stmt *stmt = NULL; + + va_list ap; + int step; + ssize_t ret = -1; + size_t tot = 0; + + sel->ctx->handle = NULL; + + if (sqlite3_prepare(db, sql, -1, &stmt, NULL) != SQLITE_OK) + return log_warn("db: %s", sqlite3_errmsg(db)), -1; + + va_start(ap, args); + vbind(stmt, args, ap); + va_end(ap); + + sel->ctx->handle = strlist_new(); + + while (tot < sel->datasz && (step = sqlite3_step(stmt)) == SQLITE_ROW) + sel->unpack(stmt, sel->ctx, (unsigned char *)sel->data + (tot++ * sel->elemwidth)); + + if (step == SQLITE_OK || step == SQLITE_DONE || step == SQLITE_ROW) + ret = tot; + else { + memset(sel->data, 0, sel->datasz * sel->elemwidth); + strlist_free(sel->ctx->handle); + sel->ctx->handle = NULL; + } + + sqlite3_finalize(stmt); + + return ret; +} + int db_open(const char *path) { @@ -141,130 +211,52 @@ } int -db_project_add(struct project *pj) +db_project_add(struct project *p) { - assert(pj); - - sqlite3_stmt *stmt = NULL; - int ret = -1; - - if (sqlite3_prepare(db, CHAR(sql_project_add), -1, &stmt, NULL) != SQLITE_OK) - goto sqlite3_err; - - sqlite3_bind_text(stmt, 1, pj->name, -1, SQLITE_STATIC); - sqlite3_bind_text(stmt, 2, pj->desc, -1, SQLITE_STATIC); - sqlite3_bind_text(stmt, 3, pj->url, -1, SQLITE_STATIC); - sqlite3_bind_text(stmt, 4, pj->script, -1, SQLITE_STATIC); - - if (sqlite3_step(stmt) != SQLITE_DONE) - goto sqlite3_err; - - pj->id = sqlite3_last_insert_rowid(db); - ret = 0; - -sqlite3_err: - if (stmt) - sqlite3_finalize(stmt); - - return ret; + return (p->id = insert(CHAR(sql_project_add), "ssss", p->name, p->desc, + p->url, p->script)) < 0 ? -1 : 0; } ssize_t db_project_list(struct db_ctx *ctx, struct project *projects, size_t projectsz) { - assert(projects); - - sqlite3_stmt *stmt = NULL; - struct project *p = projects; - ssize_t ret = 0; - - if (sqlite3_prepare(db, CHAR(sql_project_list), -1, &stmt, NULL) != SQLITE_OK) { - log_warn("db: %s", sqlite3_errmsg(db)); - return -1; - } + struct list sel = { + .unpack = (unpacker)project_unpacker, + .data = projects, + .datasz = projectsz, + .elemwidth = sizeof (*projects), + .ctx = ctx + }; - sqlite3_bind_int(stmt, 1, projectsz); - ctx->handle = strlist_new(); - - for (; sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret < projectsz; ++ret, ++p) - convert_project(ctx, p, stmt); - - if (stmt) - sqlite3_finalize(stmt); - - return ret; + return list(&sel, CHAR(sql_project_list), "z", projectsz); } int db_project_find(struct db_ctx *ctx, struct project *project) { - assert(ctx); - assert(project); - - sqlite3_stmt *stmt = NULL; - int ret = -1; - - ctx->handle = NULL; - - if (sqlite3_prepare(db, CHAR(sql_project_find), -1, &stmt, NULL) != SQLITE_OK) - goto sqlite3_err; - - sqlite3_bind_text(stmt, 1, project->name, -1, SQLITE_STATIC); - - if (sqlite3_step(stmt) != SQLITE_ROW) - goto sqlite3_err; + struct list sel = { + .unpack = (unpacker)project_unpacker, + .data = project, + .datasz = 1, + .elemwidth = sizeof (*project), + .ctx = ctx + }; - ret = 0; - ctx->handle = strlist_new(); - convert_project(ctx, project, stmt); - -sqlite3_err: - if (ret < 0) { - if (ctx->handle) - db_ctx_finish(ctx); - - log_warn("db: %s", sqlite3_errmsg(db)); - } - if (stmt) - sqlite3_finalize(stmt); - - return ret; + return list(&sel, CHAR(sql_project_find), "s", project->name) == 1 ? 0 : -1; } int db_project_find_id(struct db_ctx *ctx, struct project *project) { - assert(ctx); - assert(project); - - sqlite3_stmt *stmt = NULL; - int ret = -1; - - ctx->handle = NULL; - - if (sqlite3_prepare(db, CHAR(sql_project_find_id), -1, &stmt, NULL) != SQLITE_OK) - goto sqlite3_err; - - sqlite3_bind_int(stmt, 1, project->id); - - if (sqlite3_step(stmt) != SQLITE_ROW) - goto sqlite3_err; + struct list sel = { + .unpack = (unpacker)project_unpacker, + .data = project, + .datasz = 1, + .elemwidth = sizeof (*project), + .ctx = ctx + }; - ret = 0; - ctx->handle = strlist_new(); - convert_project(ctx, project, stmt); - -sqlite3_err: - if (ret < 0) { - if (ctx->handle) - db_ctx_finish(ctx); - - log_warn("db: %s", sqlite3_errmsg(db)); - } - if (stmt) - sqlite3_finalize(stmt); - - return ret; + return list(&sel, CHAR(sql_project_find_id), "i", project->id) == 1 ? 0 : -1; } int @@ -272,26 +264,7 @@ { assert(wk); - sqlite3_stmt *stmt = NULL; - int ret = -1; - - if (sqlite3_prepare(db, CHAR(sql_worker_add), -1, &stmt, NULL) != SQLITE_OK) - goto sqlite3_err; - - sqlite3_bind_text(stmt, 1, wk->name, -1, SQLITE_STATIC); - sqlite3_bind_text(stmt, 2, wk->desc, -1, SQLITE_STATIC); - - if (sqlite3_step(stmt) != SQLITE_DONE) - goto sqlite3_err; - - wk->id = sqlite3_last_insert_rowid(db); - ret = 0; - -sqlite3_err: - if (stmt) - sqlite3_finalize(stmt); - - return ret; + return (wk->id = insert(CHAR(sql_worker_add), "ss", wk->name, wk->desc)) < 0 ? -1 : 0; } ssize_t @@ -300,111 +273,43 @@ assert(ctx); assert(wk); - sqlite3_stmt *stmt = NULL; - struct worker *w = wk; - ssize_t ret = -1; - - ctx->handle = NULL; - - if (sqlite3_prepare(db, CHAR(sql_worker_list), -1, &stmt, NULL) != SQLITE_OK) - goto sqlite3_err; - - sqlite3_bind_int(stmt, 1, wksz); - ctx->handle = strlist_new(); + struct list sel = { + .unpack = (unpacker)worker_unpacker, + .data = wk, + .datasz = wksz, + .elemwidth = sizeof (*wk), + .ctx = ctx + }; - for (ret = 0; sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret < wksz; ++ret, ++w) { - w->id = sqlite3_column_int(stmt, 0); - w->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1))); - w->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2))); - } - -sqlite3_err: - if (ret < 0) { - if (ctx->handle) - db_ctx_finish(ctx); - - log_warn("db: %s", sqlite3_errmsg(db)); - } - if (stmt) - sqlite3_finalize(stmt); - - return ret; + return list(&sel, CHAR(sql_worker_list), "z", wksz); } int -db_worker_find(struct db_ctx *ctx, struct worker *w) +db_worker_find(struct db_ctx *ctx, struct worker *wk) { - assert(ctx); - assert(w); - - sqlite3_stmt *stmt = NULL; - int ret = -1; - - ctx->handle = NULL; - - if (sqlite3_prepare(db, CHAR(sql_worker_find), -1, &stmt, NULL) != SQLITE_OK) - goto sqlite3_err; - - sqlite3_bind_text(stmt, 1, w->name, -1, SQLITE_STATIC); - - if (sqlite3_step(stmt) != SQLITE_ROW) - goto sqlite3_err; + struct list sel = { + .unpack = (unpacker)worker_unpacker, + .data = wk, + .datasz = 1, + .elemwidth = sizeof (*wk), + .ctx = ctx + }; - ret = 0; - ctx->handle = strlist_new(); - w->id = sqlite3_column_int(stmt, 0); - w->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1))); - w->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2))); - -sqlite3_err: - if (ret < 0) { - if (ctx->handle) - db_ctx_finish(ctx); - - log_warn("db: %s", sqlite3_errmsg(db)); - } - if (stmt) - sqlite3_finalize(stmt); - - return ret; + return list(&sel, CHAR(sql_worker_find), "s", wk->name); } int -db_worker_find_id(struct db_ctx *ctx, struct worker *w) +db_worker_find_id(struct db_ctx *ctx, struct worker *wk) { - assert(ctx); - assert(w); - - sqlite3_stmt *stmt = NULL; - int ret = -1; - - ctx->handle = NULL; - - if (sqlite3_prepare(db, CHAR(sql_worker_find_id), -1, &stmt, NULL) != SQLITE_OK) - goto sqlite3_err; - - sqlite3_bind_int(stmt, 1, w->id); - - if (sqlite3_step(stmt) != SQLITE_ROW) - goto sqlite3_err; + struct list sel = { + .unpack = (unpacker)worker_unpacker, + .data = wk, + .datasz = 1, + .elemwidth = sizeof (*wk), + .ctx = ctx + }; - ret = 0; - ctx->handle = strlist_new(); - w->id = sqlite3_column_int(stmt, 0); - w->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1))); - w->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2))); - -sqlite3_err: - if (ret < 0) { - if (ctx->handle) - db_ctx_finish(ctx); - - log_warn("db: %s", sqlite3_errmsg(db)); - } - if (stmt) - sqlite3_finalize(stmt); - - return ret; + return list(&sel, CHAR(sql_worker_find_id), "i", wk->id); } int @@ -412,9 +317,8 @@ { assert(job); - job->id = insert(CHAR(sql_job_add), "si", job->tag, job->project_id); - - return job->id < 0 ? -1 : 0; + return (job->id = insert(CHAR(sql_job_add), + "si", job->tag, job->project_id)) < 0 ? -1 : 0; } ssize_t @@ -423,27 +327,15 @@ assert(ctx); assert(jobs); - sqlite3_stmt *stmt = NULL; - ssize_t ret = 0; - - if (sqlite3_prepare(db, CHAR(sql_job_todo), -1, &stmt, NULL) != SQLITE_OK) { - log_warn("db: %s", sqlite3_errmsg(db)); - return -1; - } - - sqlite3_bind_int(stmt, 1, worker_id); - sqlite3_bind_int(stmt, 2, jobsz); - ctx->handle = strlist_new(); - - while (sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret++ < jobsz) { - jobs->id = sqlite3_column_int(stmt, 0); - jobs->tag = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1))); - jobs++->project_id = sqlite3_column_int(stmt, 2); + struct list sel = { + .unpack = (unpacker)job_unpacker, + .data = jobs, + .datasz = jobsz, + .elemwidth = sizeof (*jobs), + .ctx = ctx }; - sqlite3_finalize(stmt); - - return ret; + return list(&sel, CHAR(sql_job_todo), "iz", worker_id, jobsz); } int @@ -451,10 +343,8 @@ { assert(r); - r->id = insert(CHAR(sql_jobresult_add), "iiis", r->job_id, - r->worker_id, r->exitcode, r->log); - - return r->id < 0 ? -1 : 0; + return (r->id = insert(CHAR(sql_jobresult_add), "iiis", r->job_id, + r->worker_id, r->exitcode, r->log)) < 0 ? -1 : 0; } void
--- a/scictl.c Mon Jun 14 22:08:24 2021 +0200 +++ b/scictl.c Tue Jun 15 20:42:55 2021 +0200 @@ -186,6 +186,7 @@ if ((req = req_project_find(&project, argv[0])).status) return req; + printf("%-16s%d\n", "id:", project.id); printf("%-16s%s\n", "name:", project.name); printf("%-16s%s\n", "desc:", project.desc); printf("%-16s%s\n", "url:", project.url); @@ -209,6 +210,7 @@ return req; for (size_t i = 0; i < projectsz; ++i) { + printf("%-16s%d\n", "id:", projects[i].id); printf("%-16s%s\n", "name:", projects[i].name); printf("%-16s%s\n", "desc:", projects[i].desc); printf("%-16s%s\n", "url:", projects[i].url); @@ -248,6 +250,7 @@ return req; for (size_t i = 0; i < wksz; ++i) { + printf("%-16s%d\n", "id:", wk[i].id); printf("%-16s%s\n", "name:", wk[i].name); printf("%-16s%s\n", "desc:", wk[i].desc);
--- a/scid.c Mon Jun 14 22:08:24 2021 +0200 +++ b/scid.c Tue Jun 15 20:42:55 2021 +0200 @@ -83,7 +83,7 @@ str = json_dumps(json, JSON_COMPACT); - if (send(fd, str, strlen(str), 0) < 0 || send(fd, "\r\n", 2, 0) < 0) + if (send(fd, str, strlen(str), MSG_NOSIGNAL) < 0 || send(fd, "\r\n", 2, 0) < 0) ret = -1; free(str); @@ -367,7 +367,7 @@ fp = util_fmemopen((msg = util_calloc(1, SCI_MSG_MAX)), SCI_MSG_MAX, "w"); setbuf(fp, NULL); - while (!strstr(msg, "\r\n") && (nr = recv(client, buf, sizeof (buf), 0)) > 0) + while (!strstr(msg, "\r\n") && (nr = recv(client, buf, sizeof (buf), MSG_NOSIGNAL)) > 0) if (fwrite(buf, 1, nr, fp) != (size_t)nr) warn("fwrite");
--- a/sql/job-todo.sql Mon Jun 14 22:08:24 2021 +0200 +++ b/sql/job-todo.sql Tue Jun 15 20:42:55 2021 +0200 @@ -4,8 +4,8 @@ FROM job WHERE job.id NOT IN ( - SELECT job_result.job_id - FROM job_result - WHERE job_result.worker_id = ? + SELECT jobresult.job_id + FROM jobresult + WHERE jobresult.worker_id = ? ) LIMIT ?