Use message index instead of URL to save space

Refresh code is not maintained.

1 files changed, 93 insertions, 19 deletions

diff --git a/db.c b/db.c
index 4400341..a72cbc9 100644
--- a/db.c
+++ b/db.c
@@ -5,25 +5,41 @@
 #include "db.h"
 #include "log.h"
 #include "botd.h"
+#include "utils.h"
 
 #include <stdio.h>
 #include <sqlite3.h>
+#include <stdint.h>
 
-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')"
-        ");",
-        "CREATE VIEW 'stat_global'"
-        "AS "
-        "SELECT * FROM (SELECT COUNT(user), SUM(i), MAX(i), AVG(i) FROM stats), (SELECT COUNT(msg) FROM says);"
+struct upgrade {
+    const char *sql;
+
+    int (*pre_func)(void);
+
+    int (*post_func)(void);
+};
+
+static int upgrade_migrate_msg_index(void);
+
+static const struct upgrade sql_upgrades[] = {
+        {"CREATE TABLE 'says' ("
+         "'id' INTEGER PRIMARY KEY,"
+         "'t' TEXT NOT NULL UNIQUE,"
+         "'url' TEXT,"
+         "'msg' INTEGER NOT NULL"
+         ");", NULL, NULL},
+        {"CREATE TABLE 'stats' ("
+         "'user' INTEGER UNIQUE,"
+         "'i' INTEGER NOT NULL,"
+         "PRIMARY KEY('user')"
+         ");", NULL, NULL},
+        {"CREATE VIEW 'stat_global'"
+         "AS "
+         "SELECT * FROM (SELECT COUNT(user), SUM(i), MAX(i), AVG(i) FROM stats), (SELECT COUNT(msg) FROM says);",
+         NULL, NULL},
+        {"ALTER TABLE says ADD msg_index INTEGER;", NULL, upgrade_migrate_msg_index},
+        /* TDSQLite is 3.31, while ALTER TABLE DROP support begins from 3.35. */
+        /* {"ALTER TABLE says DROP COLUMN url;", NULL, NULL} */
 };
 
 sqlite3 *db = NULL;
@@ -36,6 +52,56 @@ sqlite3_stmt *stmt_search = NULL;
 
 sqlite3_stmt *stmt_random = NULL;
 
+static int upgrade_migrate_msg_index(void) {
+    int r;
+    sqlite3_stmt *stmt = NULL;
+    sqlite3_stmt *stmt_set = NULL;
+
+    /* This is currently not atomic.
+     * To retry, delete the column msg_index and set user_version to 3 manually. */
+
+    if ((r = sqlite3_prepare_v2(db, "SELECT id,url FROM says;", -1, &stmt, NULL))) {
+        LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
+    }
+    if ((r = sqlite3_prepare_v2(db, "UPDATE says SET url = null, msg_index = ? WHERE id = ?;", -1, &stmt_set, NULL))) {
+        LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
+    }
+
+    while ((r = sqlite3_step(stmt)) == SQLITE_ROW) {
+        int id = sqlite3_column_int(stmt, 0);
+        char *url = (char *) sqlite3_column_text(stmt, 1);
+        uint32_t index = tg_url_get_index(url);
+
+        if (!index) {
+            LOGFEV("Unknown URL: %s", 1, url);
+        }
+        if ((r = sqlite3_bind_int64(stmt_set, 1, index))) {
+            LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
+        }
+        if ((r = sqlite3_bind_int(stmt_set, 2, id))) {
+            LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
+        }
+
+        if ((r = sqlite3_step(stmt_set)) != SQLITE_DONE) {
+            LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
+        }
+        if (sqlite3_changes(db) != 1) {
+            LOGFEV("Cannot upgrade DB: Missing %d.", 1, id);
+        }
+        if ((r = sqlite3_reset(stmt_set))) {
+            LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
+        }
+    }
+
+    if (r != SQLITE_DONE) {
+        LOGFEV("Cannot upgrade DB: %s", r, sqlite3_errstr(r));
+    }
+
+    sqlite3_finalize(stmt);
+    sqlite3_finalize(stmt_set);
+    return 0;
+}
+
 void db_init(void) {
     int r;
     r = sqlite3_open(cmd.db_path, &db);
@@ -62,14 +128,22 @@ void db_init(void) {
     }
     int current_var = sqlite3_column_int(stmt, 0);
     sqlite3_finalize(stmt);
-    for (; current_var < sizeof(sql_upgrades) / sizeof(char *); current_var ++) {
+    for (; current_var < sizeof(sql_upgrades) / sizeof(struct upgrade); current_var++) {
         const int to = current_var + 1;
+        const struct upgrade *upg = &sql_upgrades[current_var];
         LOGIV("Upgrading SQL schema from %d to %d.",
               current_var,
               to);
-        if ((r = sqlite3_exec(db, sql_upgrades[current_var], NULL, NULL, &errmsg))) {
+        /* Pre / post functions return values are currently unused. */
+        if (upg->pre_func) {
+            upg->pre_func();
+        }
+        if ((r = sqlite3_exec(db, upg->sql, NULL, NULL, &errmsg))) {
             goto sql_err;
         }
+        if (upg->post_func) {
+            upg->post_func();
+        }
         char sql[60];
         sprintf(sql, "PRAGMA user_version = %d;", to);
         if ((r = sqlite3_exec(db, sql, NULL, NULL, &errmsg))) {
@@ -88,7 +162,7 @@ void db_init(void) {
         goto sql_err;
     }
 
-    if ((r = sqlite3_prepare_v2(db, "SELECT id, t, url FROM says WHERE t LIKE ? LIMIT 10;",
+    if ((r = sqlite3_prepare_v2(db, "SELECT id, t, msg_index FROM says WHERE t LIKE ? LIMIT 10;",
                                 -1,
                                 &stmt_search,
                                 NULL))) {
@@ -96,7 +170,7 @@ void db_init(void) {
         goto sql_err;
     }
 
-    if ((r = sqlite3_prepare_v2(db, "SELECT id, t, url FROM says ORDER BY RANDOM() LIMIT 10;",
+    if ((r = sqlite3_prepare_v2(db, "SELECT id, t, msg_index FROM says ORDER BY RANDOM() LIMIT 10;",
                                 -1,
                                 &stmt_random,
                                 NULL))) {