--- a/db.c	Thu Jun 10 10:39:21 2021 +0200
+++ b/db.c	Mon Jun 14 22:08:24 2021 +0200
@@ -1,3 +1,4 @@
+#include <sys/queue.h>
 #include <assert.h>
 #include <stdlib.h>
 #include <string.h>
@@ -5,34 +6,117 @@
 #include <sqlite3.h>
 
 #include "db.h"
-#include "job.h"
 #include "log.h"
-#include "project.h"
-#include "worker.h"
+#include "types.h"
+#include "util.h"
 
 #include "sql/init.h"
-#include "sql/job-queue.h"
-#include "sql/job-result-todo.h"
-#include "sql/job-save.h"
-#include "sql/project-insert.h"
-#include "sql/project-get.h"
+#include "sql/job-add.h"
+#include "sql/job-todo.h"
+#include "sql/jobresult-add.h"
+#include "sql/project-add.h"
 #include "sql/project-find.h"
-#include "sql/worker-get.h"
+#include "sql/project-find-id.h"
+#include "sql/project-list.h"
+#include "sql/worker-add.h"
 #include "sql/worker-find.h"
-#include "sql/worker-insert.h"
+#include "sql/worker-find-id.h"
+#include "sql/worker-list.h"
 
 #define CHAR(v) (const char *)(v)
 
 static sqlite3 *db;
 
-static inline void
-convert_project(struct project *project, sqlite3_stmt *stmt)
+struct str {
+	char *str;
+	SLIST_ENTRY(str) link;
+};
+
+SLIST_HEAD(strlist, str);
+
+static struct strlist *
+strlist_new(void)
+{
+	struct strlist *l;
+
+	l = util_calloc(1, sizeof (*l));
+	SLIST_INIT(l);
+
+	return l;
+}
+
+static const char *
+strlist_add(struct strlist *l, const char *text)
+{
+	struct str *s;
+
+	s = util_calloc(1, sizeof (*s));
+	s->str = util_strdup(text);
+
+	SLIST_INSERT_HEAD(l, s, link);
+
+	return s->str;
+}
+
+static void
+strlist_free(struct strlist *l)
 {
-	project->id = sqlite3_column_int64(stmt, 0);
-	strlcpy(project->name, CHAR(sqlite3_column_text(stmt, 1)), sizeof (project->name));
-	strlcpy(project->desc, CHAR(sqlite3_column_text(stmt, 2)), sizeof (project->desc));
-	strlcpy(project->url, CHAR(sqlite3_column_text(stmt, 3)), sizeof (project->url));
-	strlcpy(project->script, CHAR(sqlite3_column_text(stmt, 4)), sizeof (project->script));
+	struct str *s, *tmp;
+
+	SLIST_FOREACH_SAFE(s, l, link, tmp) {
+		free(s->str);
+		free(s);
+	}
+
+	SLIST_INIT(l);
+}
+
+static inline void
+convert_project(struct db_ctx *ctx, struct project *project, sqlite3_stmt *stmt)
+{
+	project->id = sqlite3_column_int(stmt, 0);
+	project->name = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 1)));
+	project->desc = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 2)));
+	project->url = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 3)));
+	project->script = strlist_add(ctx->handle, CHAR(sqlite3_column_text(stmt, 4)));
+}
+
+static int
+insert(const char *sql, const char *fmt, ...)
+{
+	assert(sql);
+	assert(fmt);
+
+	sqlite3_stmt *stmt = NULL;
+	va_list ap;
+
+	if (sqlite3_prepare(db, sql, -1, &stmt, NULL) != SQLITE_OK)
+		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;
+		}
+	}
+
+	va_end(ap);
+
+	if (sqlite3_step(stmt) != SQLITE_DONE) {
+		log_warn("db: %s", sqlite3_errmsg(db));
+		sqlite3_finalize(stmt);
+		return -1;
+	}
+
+	return sqlite3_last_insert_rowid(db);
 }
 
 int
@@ -64,7 +148,7 @@
 	sqlite3_stmt *stmt = NULL;
 	int ret = -1;
 
-	if (sqlite3_prepare(db, CHAR(sql_project_insert), -1, &stmt, NULL) != SQLITE_OK)
+	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);
@@ -86,7 +170,7 @@
 }
 
 ssize_t
-db_project_get(struct project *projects, size_t projectsz)
+db_project_list(struct db_ctx *ctx, struct project *projects, size_t projectsz)
 {
 	assert(projects);
 
@@ -94,15 +178,16 @@
 	struct project *p = projects;
 	ssize_t ret = 0;
 
-	if (sqlite3_prepare(db, CHAR(sql_project_get), -1, &stmt, NULL) != SQLITE_OK) {
+	if (sqlite3_prepare(db, CHAR(sql_project_list), -1, &stmt, NULL) != SQLITE_OK) {
 		log_warn("db: %s", sqlite3_errmsg(db));
 		return -1;
 	}
 
-	sqlite3_bind_int64(stmt, 1, projectsz);
+	sqlite3_bind_int(stmt, 1, projectsz);
+	ctx->handle = strlist_new();
 
 	for (; sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret < projectsz; ++ret, ++p)
-		convert_project(p, stmt);
+		convert_project(ctx, p, stmt);
 
 	if (stmt)
 		sqlite3_finalize(stmt);
@@ -111,13 +196,16 @@
 }
 
 int
-db_project_find(struct project *project)
+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;
 
@@ -127,11 +215,52 @@
 		goto sqlite3_err;
 
 	ret = 0;
-	convert_project(project, stmt);
+	ctx->handle = strlist_new();
+	convert_project(ctx, project, stmt);
 
 sqlite3_err:
-	if (ret < 0)
+	if (ret < 0) {
+		if (ctx->handle)
+			db_ctx_finish(ctx);
+
 		log_warn("db: %s", sqlite3_errmsg(db));
+	}
+	if (stmt)
+		sqlite3_finalize(stmt);
+
+	return ret;
+}
+
+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;
+
+	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);
 
@@ -146,7 +275,7 @@
 	sqlite3_stmt *stmt = NULL;
 	int ret = -1;
 
-	if (sqlite3_prepare(db, CHAR(sql_worker_insert), -1, &stmt, NULL) != SQLITE_OK)
+	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);
@@ -166,28 +295,36 @@
 }
 
 ssize_t
-db_worker_get(struct worker *wk, size_t wksz)
+db_worker_list(struct db_ctx *ctx, struct worker *wk, size_t wksz)
 {
+	assert(ctx);
 	assert(wk);
 
 	sqlite3_stmt *stmt = NULL;
 	struct worker *w = wk;
 	ssize_t ret = -1;
 
-	if (sqlite3_prepare(db, CHAR(sql_worker_get), -1, &stmt, NULL) != SQLITE_OK)
+	ctx->handle = NULL;
+
+	if (sqlite3_prepare(db, CHAR(sql_worker_list), -1, &stmt, NULL) != SQLITE_OK)
 		goto sqlite3_err;
 
-	sqlite3_bind_int64(stmt, 1, wksz);
+	sqlite3_bind_int(stmt, 1, wksz);
+	ctx->handle = strlist_new();
 
 	for (ret = 0; sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret < wksz; ++ret, ++w) {
-		w->id = sqlite3_column_int64(stmt, 0);
-		strlcpy(w->name, CHAR(sqlite3_column_text(stmt, 1)), sizeof (w->name));
-		strlcpy(w->desc, CHAR(sqlite3_column_text(stmt, 2)), sizeof (w->desc));
+		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 (ret < 0) {
+		if (ctx->handle)
+			db_ctx_finish(ctx);
+
 		log_warn("db: %s", sqlite3_errmsg(db));
+	}
 	if (stmt)
 		sqlite3_finalize(stmt);
 
@@ -195,13 +332,16 @@
 }
 
 int
-db_worker_find(struct worker *w)
+db_worker_find(struct db_ctx *ctx, struct worker *w)
 {
+	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;
 
@@ -211,13 +351,18 @@
 		goto sqlite3_err;
 
 	ret = 0;
-	w->id = sqlite3_column_int64(stmt, 0);
-	strlcpy(w->name, CHAR(sqlite3_column_text(stmt, 1)), sizeof (w->name));
-	strlcpy(w->desc, CHAR(sqlite3_column_text(stmt, 2)), sizeof (w->desc));
+	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 (ret < 0) {
+		if (ctx->handle)
+			db_ctx_finish(ctx);
+
 		log_warn("db: %s", sqlite3_errmsg(db));
+	}
 	if (stmt)
 		sqlite3_finalize(stmt);
 
@@ -225,61 +370,37 @@
 }
 
 int
-db_job_queue(struct job *job)
+db_worker_find_id(struct db_ctx *ctx, struct worker *w)
 {
-	assert(job);
+	assert(ctx);
+	assert(w);
 
 	sqlite3_stmt *stmt = NULL;
 	int ret = -1;
 
-	if (sqlite3_prepare(db, CHAR(sql_job_queue), -1, &stmt, NULL) != SQLITE_OK)
+	ctx->handle = NULL;
+
+	if (sqlite3_prepare(db, CHAR(sql_worker_find_id), -1, &stmt, NULL) != SQLITE_OK)
 		goto sqlite3_err;
 
-	sqlite3_bind_text(stmt, 1, job->tag, -1, SQLITE_STATIC);
-	sqlite3_bind_int64(stmt, 2, job->project.id);
+	sqlite3_bind_int(stmt, 1, w->id);
 
-	if (sqlite3_step(stmt) != SQLITE_DONE)
+	if (sqlite3_step(stmt) != SQLITE_ROW)
 		goto sqlite3_err;
 
-	job->id = sqlite3_last_insert_rowid(db);
 	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)
-		log_warn("db: %s", sqlite3_errmsg(db));
-	if (stmt)
-		sqlite3_finalize(stmt);
-
-	return ret;
-}
+	if (ret < 0) {
+		if (ctx->handle)
+			db_ctx_finish(ctx);
 
-ssize_t
-db_job_result_todo(struct job_result *re, size_t resz, int64_t worker_id)
-{
-	assert(re);
-
-	sqlite3_stmt *stmt = NULL;
-	ssize_t ret = 0;
-
-	if (sqlite3_prepare(db, CHAR(sql_job_result_todo), -1, &stmt, NULL) != SQLITE_OK) {
 		log_warn("db: %s", sqlite3_errmsg(db));
-		return -1;
 	}
-
-	sqlite3_bind_int64(stmt, 1, worker_id);
-	sqlite3_bind_int64(stmt, 2, resz);
-
-	while (sqlite3_step(stmt) == SQLITE_ROW && (size_t)ret++ < resz) {
-		memset(re, 0, sizeof (*re));
-		re->job.id = sqlite3_column_int64(stmt, 0);
-		strlcpy(re->job.tag, CHAR(sqlite3_column_text(stmt, 1)),
-		    sizeof (re->job.tag));
-		strlcpy(re->job.project.name, CHAR(sqlite3_column_text(stmt, 2)),
-		    sizeof (re->job.project.name));
-
-		++re;
-	};
-
 	if (stmt)
 		sqlite3_finalize(stmt);
 
@@ -287,35 +408,53 @@
 }
 
 int
-db_job_save(struct job_result *r)
+db_job_add(struct job *job)
+{
+	assert(job);
+
+	job->id = insert(CHAR(sql_job_add), "si", job->tag, job->project_id);
+
+	return job->id < 0 ? -1 : 0;
+}
+
+ssize_t
+db_job_todo(struct db_ctx *ctx, struct job *jobs, size_t jobsz, int worker_id)
+{
+	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);
+	};
+
+	sqlite3_finalize(stmt);
+
+	return ret;
+}
+
+int
+db_jobresult_add(struct jobresult *r)
 {
 	assert(r);
 
-	sqlite3_stmt *stmt = NULL;
-	int ret = -1;
-
-	if (sqlite3_prepare(db, CHAR(sql_job_save), -1, &stmt, NULL) != SQLITE_OK)
-		goto sqlite3_err;
-
-	sqlite3_bind_int64(stmt, 1, r->job.id);
-	sqlite3_bind_int64(stmt, 2, r->worker.id);
-	sqlite3_bind_int(stmt, 3, r->status);
-	sqlite3_bind_int(stmt, 4, r->retcode);
-	sqlite3_bind_text(stmt, 5, r->console, -1, SQLITE_STATIC);
+	r->id = insert(CHAR(sql_jobresult_add), "iiis", r->job_id,
+	    r->worker_id, r->exitcode, r->log);
 
-	if (sqlite3_step(stmt) != SQLITE_DONE)
-		goto sqlite3_err;
-
-	ret = 0;
-	r->id = sqlite3_last_insert_rowid(db);
-
-sqlite3_err:
-	if (ret < 0)
-		log_warn("db: %s", sqlite3_errmsg(db));
-	if (stmt)
-		sqlite3_finalize(stmt);
-
-	return ret;
+	return r->id < 0 ? -1 : 0;
 }
 
 void
@@ -326,3 +465,12 @@
 		db = NULL;
 	}
 }
+
+void
+db_ctx_finish(struct db_ctx *ctx)
+{
+	if (ctx->handle) {
+		strlist_free(ctx->handle);
+		ctx->handle = NULL;
+	}
+}