actually create some tables in SQLite database

6 files changed, 545 insertions, 20 deletions

diff --git a/src/CMakeLists.txt b/src/CMakeLists.txt
index da1f7b1..d8ded6d 100644
--- a/src/CMakeLists.txt
+++ b/src/CMakeLists.txt
@@ -18,15 +18,33 @@ function(add_my_executable name)
     install(TARGETS "${name}" RUNTIME DESTINATION bin)
 endfunction()
 
+find_package(Python3 REQUIRED COMPONENTS Interpreter)
+
+function(generate_sql_header engine)
+    file(GLOB sql_files "${CMAKE_CURRENT_SOURCE_DIR}/${engine}/*.sql")
+    add_custom_command(
+        OUTPUT "${CMAKE_CURRENT_BINARY_DIR}/${engine}_sql.h"
+        COMMAND Python3::Interpreter
+            "${CMAKE_CURRENT_SOURCE_DIR}/generate-sql-header.py"
+            "${CMAKE_CURRENT_SOURCE_DIR}/${engine}/"
+            -o "${CMAKE_CURRENT_BINARY_DIR}/${engine}_sql.h"
+        DEPENDS ${sql_files})
+endfunction()
+
+generate_sql_header(sqlite)
+
 add_my_executable(server server_main.c server.c
     ci_queue.c
     msg.c
     net.c
     signal.c
+    sqlite.c
+    sqlite_sql.h
     storage.c
     storage_sqlite.c
     tcp_server.c)
 target_link_libraries(server PRIVATE pthread sqlite3)
+target_include_directories(server PRIVATE "${CMAKE_CURRENT_BINARY_DIR}")
 
 add_my_executable(client client_main.c client.c
     msg.c
diff --git a/src/generate-sql-header.py b/src/generate-sql-header.py
new file mode 100755
index 0000000..2cb2862
--- /dev/null
+++ b/src/generate-sql-header.py
@@ -0,0 +1,101 @@
+#!/usr/bin/env python3
+
+# Copyright (c) 2023 Egor Tensin <Egor.Tensin@gmail.com>
+# This file is part of the "cimple" project.
+# For details, see https://github.com/egor-tensin/cimple.
+# Distributed under the MIT License.
+
+import argparse
+from contextlib import contextmanager
+from glob import glob
+import os
+import sys
+
+
+class Generator:
+    def __init__(self, fd, dir):
+        self.fd = fd
+        if not os.path.isdir(dir):
+            raise RuntimeError('must be a directory: ' + dir)
+        self.dir = os.path.abspath(dir)
+        self.name = os.path.basename(self.dir)
+
+    def write(self, line):
+        self.fd.write(f'{line}\n')
+
+    def do(self):
+        self.include_guard_start()
+        self.include_sql_files()
+        self.include_guard_end()
+
+    def include_guard_start(self):
+        self.write(f'#ifndef __{self.name.upper()}_SQL_H__')
+        self.write(f'#define __{self.name.upper()}_SQL_H__')
+        self.write('')
+
+    def include_guard_end(self):
+        self.write('')
+        self.write(f'#endif')
+
+    def enum_sql_files(self):
+        return [os.path.join(self.dir, path) for path in sorted(glob('*.sql', root_dir=self.dir))]
+
+    @property
+    def var_name_prefix(self):
+        return f'sql_{self.name}'
+
+    def sql_file_to_var_name(self, path):
+        name = os.path.splitext(os.path.basename(path))[0]
+        return f'{self.var_name_prefix}_{name}'
+
+    @staticmethod
+    def sql_file_to_string_literal(path):
+        with open(path) as fd:
+            sql = fd.read()
+        sql = sql.encode().hex().upper()
+        sql = ''.join((f'\\x{sql[i:i + 2]}' for i in range(0, len(sql), 2)))
+        return sql
+
+    def include_sql_files(self):
+        vars = []
+        for path in self.enum_sql_files():
+            name = self.sql_file_to_var_name(path)
+            vars.append(name)
+            value = self.sql_file_to_string_literal(path)
+            self.write(f'static const char *const {name} = "{value}";')
+        self.write('')
+        self.write(f'static const char *const {self.var_name_prefix}_files[] = {{')
+        for var in vars:
+            self.write(f'\t{var},')
+        self.write('};')
+
+
+@contextmanager
+def open_output(path):
+    if path is None:
+        yield sys.stdout
+    else:
+        with open(path, 'w') as fd:
+            yield fd
+
+
+def parse_args(argv=None):
+    if argv is None:
+        argv = sys.argv[1:]
+    parser = argparse.ArgumentParser()
+    parser.add_argument('-o', '--output', metavar='PATH',
+                        help='set output file path')
+    parser.add_argument('dir', metavar='INPUT_DIR',
+                        help='input directory')
+    return parser.parse_args()
+
+
+def main(argv=None):
+    args = parse_args(argv)
+    with open_output(args.output) as fd:
+        generator = Generator(fd, args.dir)
+        generator.do()
+
+
+if __name__ == '__main__':
+    main()
diff --git a/src/sqlite.c b/src/sqlite.c
new file mode 100644
index 0000000..168ab9a
--- /dev/null
+++ b/src/sqlite.c
@@ -0,0 +1,261 @@
+/*
+ * Copyright (c) 2022 Egor Tensin <Egor.Tensin@gmail.com>
+ * This file is part of the "cimple" project.
+ * For details, see https://github.com/egor-tensin/cimple.
+ * Distributed under the MIT License.
+ */
+
+#include "sqlite.h"
+#include "compiler.h"
+#include "log.h"
+
+#include <sqlite3.h>
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+
+#define sqlite_errno(var, fn)                                                                      \
+	do {                                                                                       \
+		log_err("%s: %s\n", fn, sqlite3_errstr(var));                                      \
+		var = -var;                                                                        \
+	} while (0)
+
+#define sqlite_errno_if(expr, fn)                                                                  \
+	do {                                                                                       \
+		int CONCAT(ret, __LINE__) = expr;                                                  \
+		if (CONCAT(ret, __LINE__))                                                         \
+			sqlite_errno(CONCAT(ret, __LINE__), fn);                                   \
+	} while (0)
+
+int sqlite_init()
+{
+	int ret = 0;
+
+	ret = sqlite3_initialize();
+	if (ret) {
+		sqlite_errno(ret, "sqlite3_initialize");
+		return ret;
+	}
+
+	return ret;
+}
+
+void sqlite_destroy()
+{
+	sqlite_errno_if(sqlite3_shutdown(), "sqlite3_shutdown");
+}
+
+int sqlite_open(const char *path, sqlite3 **db, int flags)
+{
+	int ret = 0;
+
+	ret = sqlite3_open_v2(path, db, flags, NULL);
+	if (ret) {
+		sqlite_errno(ret, "sqlite3_open_v2");
+		return ret;
+	}
+
+	return ret;
+}
+
+int sqlite_open_rw(const char *path, sqlite3 **db)
+{
+	static const int flags = SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE;
+	return sqlite_open(path, db, flags);
+}
+
+int sqlite_open_ro(const char *path, sqlite3 **db)
+{
+	static const int flags = SQLITE_OPEN_READONLY;
+	return sqlite_open(path, db, flags);
+}
+
+void sqlite_close(sqlite3 *db)
+{
+	sqlite_errno_if(sqlite3_close(db), "sqlite3_close");
+}
+
+int sqlite_exec(sqlite3 *db, const char *stmt, sqlite3_callback callback)
+{
+	int ret = 0;
+
+	ret = sqlite3_exec(db, stmt, callback, NULL, NULL);
+	if (ret) {
+		sqlite_errno(ret, "sqlite3_exec");
+		return ret;
+	}
+
+	return ret;
+}
+
+int sqlite_log_result(UNUSED void *arg, int numof_columns, char **values, char **column_names)
+{
+	log("Row:\n");
+	for (int i = 0; i < numof_columns; ++i) {
+		log("\t%s: %s\n", column_names[i], values[i]);
+	}
+	return 0;
+}
+
+int sqlite_prepare(sqlite3 *db, const char *stmt, sqlite3_stmt **result)
+{
+	int ret = 0;
+
+	ret = sqlite3_prepare_v2(db, stmt, -1, result, NULL);
+	if (ret) {
+		sqlite_errno(ret, "sqlite3_prepare_v2");
+		return ret;
+	}
+
+	return ret;
+}
+
+void sqlite_finalize(sqlite3_stmt *stmt)
+{
+	sqlite_errno_if(sqlite3_finalize(stmt), "sqlite3_finalize");
+}
+
+int sqlite_step(sqlite3_stmt *stmt)
+{
+	int ret = 0;
+
+	ret = sqlite3_step(stmt);
+
+	switch (ret) {
+	case SQLITE_ROW:
+	case SQLITE_DONE:
+		return 0;
+
+	default:
+		sqlite_errno(ret, "sqlite3_step");
+		return ret;
+	}
+}
+
+int sqlite_column_int(sqlite3_stmt *stmt, int index)
+{
+	return sqlite3_column_int(stmt, index);
+}
+
+int sqlite_column_text(sqlite3_stmt *stmt, int index, char **result)
+{
+	const unsigned char *value;
+	size_t nb;
+	int ret = 0;
+
+	value = sqlite3_column_text(stmt, index);
+	if (!value) {
+		ret = sqlite3_errcode(sqlite3_db_handle(stmt));
+		if (ret) {
+			sqlite_errno(ret, "sqlite3_column_text");
+			return ret;
+		}
+
+		*result = NULL;
+		return 0;
+	}
+
+	ret = sqlite3_column_bytes(stmt, index);
+	nb = (size_t)ret;
+
+	*result = calloc(nb + 1, 1);
+	if (!*result) {
+		log_errno("calloc");
+		return -1;
+	}
+
+	memcpy(*result, value, nb);
+	return 0;
+}
+
+int sqlite_column_blob(sqlite3_stmt *stmt, int index, unsigned char **result)
+{
+	const unsigned char *value;
+	size_t nb;
+	int ret = 0;
+
+	value = sqlite3_column_blob(stmt, index);
+	if (!value) {
+		ret = sqlite3_errcode(sqlite3_db_handle(stmt));
+		if (ret) {
+			sqlite_errno(ret, "sqlite3_column_text");
+			return ret;
+		}
+
+		*result = NULL;
+		return 0;
+	}
+
+	ret = sqlite3_column_bytes(stmt, index);
+	nb = (size_t)ret;
+
+	*result = malloc(nb);
+	if (!*result) {
+		log_errno("malloc");
+		return -1;
+	}
+
+	memcpy(*result, value, nb);
+	return 0;
+}
+
+int sqlite_exec_as_transaction(sqlite3 *db, const char *stmt)
+{
+	static const char *const FMT = "BEGIN; %s COMMIT;";
+
+	char *full_stmt;
+	size_t nb;
+	int ret = 0;
+
+	ret = snprintf(NULL, 0, FMT, stmt);
+	nb = (size_t)ret + 1;
+	ret = 0;
+
+	full_stmt = malloc(nb);
+	if (!full_stmt) {
+		log_errno("malloc");
+		return -1;
+	}
+	snprintf(full_stmt, nb, FMT, stmt);
+
+	ret = sqlite_exec(db, stmt, NULL);
+	goto free;
+
+free:
+	free(full_stmt);
+
+	return ret;
+}
+
+int sqlite_get_user_version(sqlite3 *db, unsigned int *version)
+{
+	sqlite3_stmt *stmt;
+	int result, ret = 0;
+
+	ret = sqlite_prepare(db, "PRAGMA user_version;", &stmt);
+	if (ret < 0)
+		return ret;
+	ret = sqlite_step(stmt);
+	if (ret < 0)
+		goto finalize;
+
+	result = sqlite_column_int(stmt, 0);
+	if (result < 0) {
+		log_err("Invalid database version: %d\n", result);
+		return -1;
+	}
+	*version = (unsigned int)result;
+
+	goto finalize;
+
+finalize:
+	sqlite_finalize(stmt);
+
+	return ret;
+}
+
+int sqlite_set_foreign_keys(sqlite3 *db)
+{
+	return sqlite_exec(db, "PRAGMA foreign_keys = ON;", NULL);
+}
diff --git a/src/sqlite.h b/src/sqlite.h
new file mode 100644
index 0000000..bb46288
--- /dev/null
+++ b/src/sqlite.h
@@ -0,0 +1,37 @@
+/*
+ * Copyright (c) 2023 Egor Tensin <Egor.Tensin@gmail.com>
+ * This file is part of the "cimple" project.
+ * For details, see https://github.com/egor-tensin/cimple.
+ * Distributed under the MIT License.
+ */
+
+#ifndef __SQLITE_H__
+#define __SQLITE_H__
+
+#include <sqlite3.h>
+
+int sqlite_init();
+void sqlite_destroy();
+
+int sqlite_open(const char *path, sqlite3 **db, int flags);
+int sqlite_open_rw(const char *path, sqlite3 **db);
+int sqlite_open_ro(const char *path, sqlite3 **db);
+void sqlite_close(sqlite3 *db);
+
+int sqlite_exec(sqlite3 *db, const char *stmt, sqlite3_callback callback);
+int sqlite_log_result(void *, int, char **, char **);
+
+int sqlite_prepare(sqlite3 *db, const char *stmt, sqlite3_stmt **result);
+void sqlite_finalize(sqlite3_stmt *);
+int sqlite_step(sqlite3_stmt *);
+
+int sqlite_column_int(sqlite3_stmt *, int column_index);
+int sqlite_column_text(sqlite3_stmt *, int column_index, char **result);
+int sqlite_column_blob(sqlite3_stmt *, int column_index, unsigned char **result);
+
+int sqlite_exec_as_transaction(sqlite3 *db, const char *stmt);
+
+int sqlite_get_user_version(sqlite3 *db, unsigned int *version);
+int sqlite_set_foreign_keys(sqlite3 *db);
+
+#endif
diff --git a/src/sqlite/v01.sql b/src/sqlite/v01.sql
new file mode 100644
index 0000000..26869b6
--- /dev/null
+++ b/src/sqlite/v01.sql
@@ -0,0 +1,13 @@
+CREATE TABLE cimple_repositories (
+	id INTEGER PRIMARY KEY,
+	url TEXT NOT NULL UNIQUE
+) STRICT;
+
+CREATE TABLE cimple_runs (
+	id INTEGER PRIMARY KEY,
+	result INTEGER NOT NULL,
+	output BLOB NOT NULL,
+	repo_id INTEGER NOT NULL,
+	FOREIGN KEY (repo_id) REFERENCES cimple_repositories(id)
+		ON DELETE CASCADE ON UPDATE CASCADE
+) STRICT;
diff --git a/src/storage_sqlite.c b/src/storage_sqlite.c
index b170e73..3f2c5c5 100644
--- a/src/storage_sqlite.c
+++ b/src/storage_sqlite.c
@@ -7,26 +7,16 @@
 
 #include "storage_sqlite.h"
 #include "log.h"
+#include "sqlite.h"
+#include "sqlite_sql.h"
 #include "storage.h"
 
 #include <sqlite3.h>
 
+#include <stdio.h>
 #include <stdlib.h>
 #include <string.h>
 
-#define sqlite_errno(var, fn)                                                                      \
-	do {                                                                                       \
-		log_err("%s: %s\n", fn, sqlite3_errstr(var));                                      \
-		var = -var;                                                                        \
-	} while (0)
-
-#define sqlite_errno_if(expr, fn)                                                                  \
-	do {                                                                                       \
-		int CONCAT(ret, __LINE__) = expr;                                                  \
-		if (CONCAT(ret, __LINE__))                                                         \
-			sqlite_errno(CONCAT(ret, __LINE__), fn);                                   \
-	} while (0)
-
 int storage_settings_create_sqlite(struct storage_settings *settings, const char *path)
 {
 	settings->sqlite.path = strdup(path);
@@ -48,27 +38,132 @@ struct storage_sqlite {
 	sqlite3 *db;
 };
 
+static int storage_upgrade_sqlite_to(struct storage_sqlite *storage, size_t version)
+{
+	static const char *const FMT = "%s PRAGMA user_version = %zu;";
+
+	const char *script;
+	char *full_script;
+	size_t nb;
+	int ret = 0;
+
+	script = sql_sqlite_files[version];
+
+	ret = snprintf(NULL, 0, FMT, script, version + 1);
+	nb = (size_t)ret + 1;
+	ret = 0;
+
+	full_script = malloc(nb);
+	if (!full_script) {
+		log_errno("malloc");
+		return -1;
+	}
+	snprintf(full_script, nb, FMT, script, version + 1);
+
+	ret = sqlite_exec_as_transaction(storage->db, full_script);
+	goto free;
+
+free:
+	free(full_script);
+
+	return ret;
+}
+
+static int storage_upgrade_sqlite_from_to(struct storage_sqlite *storage, size_t from, size_t to)
+{
+	int ret = 0;
+
+	for (size_t i = from; i < to; ++i) {
+		log("Upgrading SQLite database from version %zu to version %zu\n", i, i + 1);
+		ret = storage_upgrade_sqlite_to(storage, i);
+		if (ret < 0) {
+			log_err("Failed to upgrade to version %zu\n", i + 1);
+			return ret;
+		}
+	}
+
+	return ret;
+}
+
+static int storage_upgrade_sqlite(struct storage_sqlite *storage)
+{
+	size_t newest_version;
+	unsigned int current_version;
+	int ret = 0;
+
+	ret = sqlite_get_user_version(storage->db, &current_version);
+	if (ret < 0)
+		return ret;
+	log("SQLite database version: %u\n", current_version);
+
+	newest_version = sizeof(sql_sqlite_files) / sizeof(sql_sqlite_files[0]);
+	log("Newest database version: %zu\n", newest_version);
+
+	if (current_version > newest_version) {
+		log_err("Unknown database version: %u\n", current_version);
+		return -1;
+	}
+
+	if (current_version == newest_version) {
+		log("SQLite database already at the newest version\n");
+		return 0;
+	}
+
+	return storage_upgrade_sqlite_from_to(storage, current_version, newest_version);
+}
+
+static int storage_prepare_sqlite(struct storage_sqlite *storage)
+{
+	int ret = 0;
+
+	ret = sqlite_set_foreign_keys(storage->db);
+	if (ret < 0)
+		return ret;
+
+	ret = storage_upgrade_sqlite(storage);
+	if (ret < 0)
+		return ret;
+
+	return ret;
+}
+
 int storage_create_sqlite(struct storage *storage, const struct storage_settings *settings)
 {
 	int ret = 0;
 
+	log("Using SQLite database at %s\n", settings->sqlite.path);
+
 	storage->sqlite = malloc(sizeof(storage->sqlite));
 	if (!storage->sqlite) {
 		log_errno("malloc");
 		return -1;
 	}
 
-	ret = sqlite3_open(settings->sqlite.path, &storage->sqlite->db);
-	if (ret) {
-		sqlite_errno(ret, "sqlite3_open");
-		return ret;
-	}
+	ret = sqlite_init();
+	if (ret < 0)
+		goto free;
+	ret = sqlite_open_rw(settings->sqlite.path, &storage->sqlite->db);
+	if (ret < 0)
+		goto destroy;
+	ret = storage_prepare_sqlite(storage->sqlite);
+	if (ret < 0)
+		goto close;
 
-	return 0;
+	return ret;
+
+close:
+	sqlite_close(storage->sqlite->db);
+destroy:
+	sqlite_destroy();
+free:
+	free(storage->sqlite);
+
+	return ret;
 }
 
 void storage_destroy_sqlite(struct storage *storage)
 {
-	sqlite_errno_if(sqlite3_close(storage->sqlite->db), "sqlite3_close");
+	sqlite_close(storage->sqlite->db);
+	sqlite_destroy();
 	free(storage->sqlite);
 }