--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/scid/db.c	Wed Aug 03 15:18:09 2022 +0200
@@ -0,0 +1,413 @@
+/*
+ * db.c -- scid database access
+ *
+ * Copyright (c) 2021 David Demelier <markand@malikania.fr>
+ *
+ * Permission to use, copy, modify, and/or distribute this software for any
+ * purpose with or without fee is hereby granted, provided that the above
+ * copyright notice and this permission notice appear in all copies.
+ *
+ * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
+ * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
+ * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
+ * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
+ * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
+ * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
+ * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
+ */
+
+#include <assert.h>
+#include <stdlib.h>
+#include <string.h>
+
+#include <sqlite3.h>
+
+#include <utlist.h>
+
+#include "db.h"
+#include "log.h"
+#include "util.h"
+
+#include "sql/init.h"
+#include "sql/job-add.h"
+#include "sql/job-list.h"
+#include "sql/job-todo.h"
+#include "sql/jobresult-add.h"
+#include "sql/jobresult-list-by-job.h"
+#include "sql/jobresult-list-by-job-group.h"
+#include "sql/jobresult-list-by-worker.h"
+#include "sql/project-find.h"
+#include "sql/project-list.h"
+#include "sql/project-save.h"
+#include "sql/worker-find.h"
+#include "sql/worker-list.h"
+#include "sql/worker-save.h"
+
+#define CHAR(v) (const char *)(v)
+
+static sqlite3 *db;
+
+static json_t *
+project_packer(sqlite3_stmt *stmt)
+{
+	return json_pack("{ss ss ss ss sI}",
+		"name",         sqlite3_column_text(stmt, 0),
+		"desc",         sqlite3_column_text(stmt, 1),
+		"url",          sqlite3_column_text(stmt, 2),
+		"script",       sqlite3_column_text(stmt, 3),
+		"date",         (json_int_t)sqlite3_column_int64(stmt, 4)
+	);
+}
+
+static int
+project_binder(json_t *doc, sqlite3_stmt *stmt)
+{
+	const char *name, *desc, *url, *script;
+	int ret;
+
+	ret = json_unpack(doc, "{ss ss ss ss}",
+		"name",         &name,
+		"desc",         &desc,
+		"url",          &url,
+		"script",       &script
+	);
+
+	if (ret < 0)
+		return -1;
+
+	sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 2, desc, -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 3, url, -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 4, script, -1, SQLITE_STATIC);
+
+	return 0;
+}
+
+static json_t *
+worker_packer(sqlite3_stmt *stmt)
+{
+	return json_pack("{ss ss}",
+		"name",         sqlite3_column_text(stmt, 0),
+		"desc",         sqlite3_column_text(stmt, 1)
+	);
+}
+
+static int
+worker_binder(json_t *doc, sqlite3_stmt *stmt)
+{
+	const char *name, *desc;
+	int ret;
+
+	ret = json_unpack(doc, "{ss ss}",
+		"name",         &name,
+		"desc",         &desc
+	);
+
+	if (ret < 0)
+		return -1;
+
+	sqlite3_bind_text(stmt, 1, name, -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 2, desc, -1, SQLITE_STATIC);
+
+	return 0;
+}
+
+static json_t *
+job_packer(sqlite3_stmt *stmt)
+{
+	return json_pack("{sI ss ss sI}",
+		"id",           (json_int_t)sqlite3_column_int64(stmt, 0),
+		"tag",          sqlite3_column_text(stmt, 1),
+		"project_name", sqlite3_column_text(stmt, 2),
+		"date",         (json_int_t)sqlite3_column_int64(stmt, 3)
+	);
+}
+
+static int
+job_binder(json_t *doc, sqlite3_stmt *stmt)
+{
+	const char *tag, *project_name;
+	int ret;
+
+	ret = json_unpack(doc, "{ss ss}",
+		"tag",          &tag,
+		"project_name", &project_name
+	);
+
+	if (ret)
+		return -1;
+
+	sqlite3_bind_text(stmt, 1, tag, -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 2, project_name, -1, SQLITE_STATIC);
+
+	return 0;
+}
+
+static json_t *
+jobresult_packer(sqlite3_stmt *stmt)
+{
+	return json_pack("{sI sI ss ss si si sI}",
+		"id",           (json_int_t)sqlite3_column_int64(stmt, 0),
+		"job_id",       (json_int_t)sqlite3_column_int64(stmt, 1),
+		"worker_name",  sqlite3_column_text(stmt, 2),
+		"console",      sqlite3_column_text(stmt, 3),
+		"exitcode",     sqlite3_column_int(stmt, 4),
+		"sigcode",      sqlite3_column_int(stmt, 5),
+		"date",         (json_int_t)sqlite3_column_int64(stmt, 6)
+	);
+}
+
+static int
+jobresult_binder(json_t *doc, sqlite3_stmt *stmt)
+{
+	json_int_t job_id;
+	int exitcode, sigcode, ret;
+	const char *worker_name, *console;
+
+	ret = json_unpack(doc, "{sI ss ss si si}",
+		"job_id",       &job_id,
+		"worker_name",  &worker_name,
+		"console",      &console,
+		"exitcode",     &exitcode,
+		"sigcode",      &sigcode
+	);
+
+	if (ret < 0)
+		return -1;
+
+	sqlite3_bind_int64(stmt, 1, job_id);
+	sqlite3_bind_text(stmt, 2, worker_name, -1, SQLITE_STATIC);
+	sqlite3_bind_text(stmt, 3, console, -1, SQLITE_STATIC);
+	sqlite3_bind_int(stmt, 4, exitcode);
+	sqlite3_bind_int(stmt, 5, sigcode);
+
+	return 0;
+}
+
+static void
+bindva(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 'j':
+			sqlite3_bind_int64(stmt, index++, va_arg(ap, intmax_t));
+			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(int (*binder)(json_t *, sqlite3_stmt *), json_t *obj, const char *sql)
+{
+	assert(binder);
+	assert(obj);
+	assert(sql);
+
+	sqlite3_stmt *stmt = NULL;
+
+	if (sqlite3_prepare(db, sql, -1, &stmt, NULL) != SQLITE_OK)
+		return log_warn("db: %s", sqlite3_errmsg(db)), -1;
+
+	if (binder(obj, stmt))
+		log_warn("db: unable to bind parameter");
+	else {
+		if (sqlite3_step(stmt) != SQLITE_DONE)
+			log_warn("db: %s", sqlite3_errmsg(db));
+		else
+			json_object_set(obj, "id", json_integer(sqlite3_last_insert_rowid(db)));
+	}
+
+	sqlite3_finalize(stmt);
+
+	return 0;
+}
+
+static json_t *
+listva(json_t * (*unpacker)(sqlite3_stmt *), const char *sql, const char *args, va_list ap)
+{
+	sqlite3_stmt *stmt = NULL;
+	json_t *array, *obj;
+
+	if (sqlite3_prepare(db, sql, -1, &stmt, NULL) != SQLITE_OK)
+		return log_warn("db: %s", sqlite3_errmsg(db)), NULL;
+
+	bindva(stmt, args, ap);
+	array = json_array();
+
+	while (sqlite3_step(stmt) == SQLITE_ROW)
+		if ((obj = unpacker(stmt)))
+			json_array_append(array, obj);
+
+	sqlite3_finalize(stmt);
+
+	return array;
+}
+
+static json_t *
+list(json_t * (*unpacker)(sqlite3_stmt *), const char *sql, const char *args, ...)
+{
+	va_list ap;
+	json_t *ret;
+
+	va_start(ap, args);
+	ret = listva(unpacker, sql, args, ap);
+	va_end(ap);
+
+	return ret;
+}
+
+/*
+ * Same as list but the array should have only one element that we extract for
+ * convenience.
+ */
+static json_t *
+get(json_t * (*unpacker)(sqlite3_stmt *), const char *sql, const char *args, ...)
+{
+	va_list ap;
+	json_t *ret, *obj = NULL;
+
+	va_start(ap, args);
+	ret = listva(unpacker, sql, args, ap);
+	va_end(ap);
+
+	if (json_array_size(ret) == 1) {
+		obj = json_array_get(ret, 0);
+		json_incref(obj);
+	}
+
+	if (ret)
+		json_decref(ret);
+
+	return obj;
+}
+
+int
+db_open(const char *path)
+{
+	assert(path);
+
+	if (sqlite3_open(path, &db) != SQLITE_OK)
+		return log_warn("db: open error: %s", sqlite3_errmsg(db)), -1;
+
+	/* Wait for 30 seconds to lock the database. */
+	sqlite3_busy_timeout(db, 30000);
+
+	if (sqlite3_exec(db, CHAR(sql_init), NULL, NULL, NULL) != SQLITE_OK)
+		return log_warn("db: initialization error: %s", sqlite3_errmsg(db)), -1;
+
+	return 0;
+}
+
+int
+db_job_add(json_t *job)
+{
+	assert(job);
+
+	return insert(job_binder, job, CHAR(sql_job_add));
+}
+
+json_t *
+db_job_todo(const char *worker)
+{
+	assert(worker);
+
+	return list(job_packer, CHAR(sql_job_todo), "ss", worker, worker);
+}
+
+json_t *
+db_job_list(const char *project)
+{
+	assert(project);
+
+	return list(job_packer, CHAR(sql_job_list), "s", project);
+}
+
+int
+db_jobresult_add(json_t *res)
+{
+	assert(res);
+
+	return insert(jobresult_binder, res, CHAR(sql_jobresult_add));
+}
+
+json_t *
+db_jobresult_list_by_job(intmax_t job_id)
+{
+	return list(jobresult_packer, CHAR(sql_jobresult_list_by_job), "j", job_id);
+}
+
+json_t *
+db_jobresult_list_by_job_group(intmax_t job_id)
+{
+	return list(jobresult_packer, CHAR(sql_jobresult_list_by_job_group), "j", job_id);
+}
+
+json_t *
+db_jobresult_list_by_worker(const char *worker)
+{
+	assert(worker);
+
+	return list(jobresult_packer, CHAR(sql_jobresult_list_by_worker), "s", worker);
+}
+
+int
+db_project_save(json_t *p)
+{
+	assert(p);
+
+	return insert(project_binder, p, CHAR(sql_project_save));
+}
+
+json_t *
+db_project_list(void)
+{
+	return list(project_packer, CHAR(sql_project_list), "");
+}
+
+json_t *
+db_project_find(const char *name)
+{
+	return get(project_packer, CHAR(sql_project_find), "s", name);
+}
+
+int
+db_worker_save(json_t *wk)
+{
+	assert(wk);
+
+	return insert(worker_binder, wk, CHAR(sql_worker_save));
+}
+
+json_t *
+db_worker_list(void)
+{
+	return list(worker_packer, CHAR(sql_worker_list), "");
+}
+
+json_t *
+db_worker_find(const char *name)
+{
+	assert(name);
+
+	return get(worker_packer, CHAR(sql_worker_find), "s", name);
+}
+
+void
+db_finish(void)
+{
+	if (db) {
+		sqlite3_close(db);
+		db = NULL;
+	}
+}