From 22f6aeabe8a366cdbce96d419d8c1588e377587f Mon Sep 17 00:00:00 2001
From: Trumeet <yuuta@yuuta.moe>
Date: Fri, 8 Apr 2022 12:37:00 -0700
Subject: Embed SQL upgrade statements

---
 db.c        | 109 +++++++++++++++++-------------------------------------------
 sql/0_1.sql |  10 ------
 sql/1_2.sql |   9 -----
 3 files changed, 30 insertions(+), 98 deletions(-)
 delete mode 100644 sql/0_1.sql
 delete mode 100644 sql/1_2.sql

diff --git a/db.c b/db.c
index cc44489..6c49f3d 100644
--- a/db.c
+++ b/db.c
@@ -8,14 +8,20 @@
 
 #include <stdio.h>
 #include <sqlite3.h>
-#include <stdlib.h>
-#include <string.h>
-#include <errno.h>
-#include <stdbool.h>
 
-#ifndef SQL_PATH
-#define SQL_PATH "../sql/"
-#endif
+static const char *sql_upgrades[] = {
+        "CREATE TABLE 'says' ("
+        "'id' INTEGER PRIMARY KEY,"
+        "'t' TEXT NOT NULL UNIQUE,"
+        "'url' TEXT,"
+        "'msg' INTEGER NOT NULL"
+        ");",
+        "CREATE TABLE 'stats' ("
+        "'user' INTEGER UNIQUE,"
+        "'i' INTEGER NOT NULL,"
+        "PRIMARY KEY('user')"
+        ");"
+};
 
 sqlite3 *db = NULL;
 
@@ -27,8 +33,7 @@ void db_init(void) {
     }
     char *errmsg = NULL;
     /* Enable foreign key support */
-    r = sqlite3_exec(db, "PRAGMA foreign_keys = ON;", NULL, NULL, &errmsg);
-    if (r) {
+    if ((r = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &errmsg))) {
         goto sql_err;
         sql_err:
         db_close();
@@ -36,84 +41,30 @@ void db_init(void) {
     }
     /* Get user_version */
     sqlite3_stmt *stmt;
-    r = sqlite3_prepare_v2(db, "PRAGMA user_version", -1, &stmt, 0);
-    if (r) {
+    if ((r = sqlite3_prepare_v2(db, "PRAGMA user_version", -1, &stmt, NULL))) {
         goto sql_err;
     }
-    r = sqlite3_step(stmt);
-    if (r != SQLITE_ROW) {
+    if ((r = sqlite3_step(stmt)) != SQLITE_ROW) {
+        errmsg = (char *) sqlite3_errstr(r);
         sqlite3_finalize(stmt);
         goto sql_err;
     }
-    const int current_var = sqlite3_column_int(stmt, 0);
-    r = sqlite3_finalize(stmt);
-    /* Upgrade one by one */
-    FILE *fd_sql = NULL;
-    char *path_sql = NULL;
-    const unsigned int path_len = strlen(SQL_PATH) + 20;
-    path_sql = calloc(path_len, sizeof(char));
-    if (path_sql == NULL) {
-        r = errno;
-        db_close();
-        LOGFEV("Cannot allocate memory: %s", r, strerror(r));
-    }
-    for (int i = current_var; true; i++) {
-        snprintf(path_sql, path_len - 1, "%s/%d_%d.sql", SQL_PATH, i, i + 1);
-        fd_sql = fopen(path_sql, "r");
-        if (fd_sql == NULL) {
-            if (errno != ENOENT) {
-                r = errno;
-                free(path_sql);
-                db_close();
-                LOGFEV("Cannot upgrade database to %d: %s", r, i + 1, strerror(r));
-            }
-            break;
-        }
-        char *sql_str = NULL;
-        char *line = NULL;
-        size_t len = 0;
-        ssize_t tread = 0;
-        ssize_t nread;
-        while ((nread = getline(&line, &len, fd_sql)) != -1) {
-            tread += nread;
-            if (sql_str == NULL) {
-                sql_str = calloc(nread + 1, sizeof(char));
-                if (sql_str == NULL) {
-                    r = errno;
-                    fclose(fd_sql);
-                    free(line);
-                    free(path_sql);
-                    db_close();
-                    LOGFEV("Cannot allocate memory: %s", r, strerror(r));
-                }
-                strcpy(sql_str, line);
-            } else {
-                char *newptr = realloc(sql_str, (tread + 1) * sizeof(char));
-                if (newptr == NULL) {
-                    r = errno;
-                    fclose(fd_sql);
-                    free(line);
-                    free(sql_str);
-                    free(path_sql);
-                    db_close();
-                    LOGFEV("Cannot allocate memory: %s", r, strerror(r));
-                }
-                sql_str = newptr;
-                strcat(sql_str, line);
-            }
+    int current_var = sqlite3_column_int(stmt, 0);
+    sqlite3_finalize(stmt);
+    for (; current_var < sizeof(sql_upgrades) / sizeof(char *); current_var ++) {
+        const int to = current_var + 1;
+        LOGIV("Upgrading SQL schema from %d to %d.",
+              current_var,
+              to);
+        if ((r = sqlite3_exec(db, sql_upgrades[current_var], NULL, NULL, &errmsg))) {
+            goto sql_err;
         }
-        fclose(fd_sql);
-        free(line);
-        r = sqlite3_exec(db, sql_str, NULL, NULL, &errmsg);
-        if (r) {
-            free(sql_str);
-            db_close();
-            LOGFV("%s", errmsg);
-            break;
+        char sql[60];
+        sprintf(sql, "PRAGMA user_version = %d;", to);
+        if ((r = sqlite3_exec(db, sql, NULL, NULL, &errmsg))) {
+            goto sql_err;
         }
-        free(sql_str);
     }
-    free(path_sql);
 }
 
 void db_close(void) {
diff --git a/sql/0_1.sql b/sql/0_1.sql
deleted file mode 100644
index 847b16d..0000000
--- a/sql/0_1.sql
+++ /dev/null
@@ -1,10 +0,0 @@
--- Init
-CREATE TABLE "says"
-(
-    "id"       INTEGER PRIMARY KEY,
-    "t"        TEXT NOT NULL UNIQUE,
-    "url"      TEXT,
-    "msg"      INTEGER NOT NULL
-);
-
-PRAGMA user_version = 1;
\ No newline at end of file
diff --git a/sql/1_2.sql b/sql/1_2.sql
deleted file mode 100644
index 37d2168..0000000
--- a/sql/1_2.sql
+++ /dev/null
@@ -1,9 +0,0 @@
--- Add stats
-CREATE TABLE "stats"
-(
-    "user"     INTEGER UNIQUE,
-    "i"        INTEGER NOT NULL,
-    PRIMARY KEY("user")
-);
-
-PRAGMA user_version = 2;
\ No newline at end of file
-- 
cgit v1.2.3