Mercurial > sci

changeset 4:9c4fea43803c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 ?
--- a/sql/jobresult-add.sql	Mon Jun 14 22:08:24 2021 +0200
+++ b/sql/jobresult-add.sql	Tue Jun 15 20:42:55 2021 +0200
@@ -1,4 +1,4 @@
-INSERT INTO job_result(
+INSERT INTO jobresult(
 	job_id,
 	worker_id,
 	status,