summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorTrumeet <yuuta@yuuta.moe>2022-04-08 12:37:00 -0700
committerTrumeet <yuuta@yuuta.moe>2022-04-08 12:37:00 -0700
commit22f6aeabe8a366cdbce96d419d8c1588e377587f (patch)
treef8d7c65f3aeff20ec0c392d49c2be6a9fd9cbb7c
parent94fc2b12ded9d1492e5b70f84cf051f22ca9ddf0 (diff)
downloadksyxbot-22f6aeabe8a366cdbce96d419d8c1588e377587f.tar
ksyxbot-22f6aeabe8a366cdbce96d419d8c1588e377587f.tar.gz
ksyxbot-22f6aeabe8a366cdbce96d419d8c1588e377587f.tar.bz2
ksyxbot-22f6aeabe8a366cdbce96d419d8c1588e377587f.zip
Embed SQL upgrade statements
-rw-r--r--db.c109
-rw-r--r--sql/0_1.sql10
-rw-r--r--sql/1_2.sql9
3 files changed, 30 insertions, 98 deletions
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