Use message index instead of URL to save space

Refresh code is not maintained.

6 files changed, 157 insertions, 31 deletions

diff --git a/CMakeLists.txt b/CMakeLists.txt
index 380cb42..6179bfe 100644
--- a/CMakeLists.txt
+++ b/CMakeLists.txt
@@ -17,6 +17,6 @@ set(CMAKE_EXE_LINKER_FLAGS_DEBUG
         "${CMAKE_EXE_LINKER_FLAGS_DEBUG} -fsanitize=address")
 add_definitions(-D_POSIX_C_SOURCE=200809L)
 
-add_executable(ksyxbotd main.c tdutils.c tdutils.h log.c log.h botd.h db.c db.h cmdline.c store.c logic.h query.c refresh.c stat.c)
+add_executable(ksyxbotd main.c tdutils.c tdutils.h log.c log.h botd.h db.c db.h cmdline.c store.c logic.h query.c refresh.c stat.c utils.c utils.h)
 target_include_directories(ksyxbotd PUBLIC "${PROJECT_BINARY_DIR}")
 target_link_libraries(ksyxbotd PRIVATE tdc tdsqlite m)
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))) {
diff --git a/query.c b/query.c
index 1423d65..ff05ff2 100644
--- a/query.c
+++ b/query.c
@@ -21,9 +21,9 @@ static void cb_answer(bool successful, struct TdObject *result, struct TdError *
 }
 
 static struct TdInputInlineQueryResultArticle *create_inline_result(char *id,
-    char *title,
-    char *descr,
-    char *text) {
+                                                                    char *title,
+                                                                    char *descr,
+                                                                    char *text) {
     struct TdInputInlineQueryResultArticle *r = TdCreateObjectInputInlineQueryResultArticle(
             id,
             NULL,
@@ -63,7 +63,7 @@ int handle_inline(struct TdUpdateNewInlineQuery *update) {
         goto sql_err;
         sql_err:
         LOGEV("Cannot query: %s.", sqlite3_errstr(r));
-        for (int i = 0; i < effective_items_count; i ++) {
+        for (int i = 0; i < effective_items_count; i++) {
             TdDestroyObjectInputInlineQueryResultArticle(results_inline[i]);
         }
         goto cleanup;
@@ -85,7 +85,7 @@ int handle_inline(struct TdUpdateNewInlineQuery *update) {
     snprintf(msg_stat, sizeof(msg_stat), "我已经卖了 %d 句菜", personal_stat);
     snprintf(id_temp, sizeof(id_temp), "s%lld", update->sender_user_id_);
     results_inline[0] = create_inline_result(id_temp, "卖菜统计", "点击发送卖菜统计", msg_stat);
-    effective_items_count ++;
+    effective_items_count++;
 
     if (query) {
         const size_t raw_query_len = strlen(update->query_);
@@ -101,7 +101,7 @@ int handle_inline(struct TdUpdateNewInlineQuery *update) {
     }
 
     for (; effective_items_count < sizeof(results_inline) / sizeof(struct TdInputInlineQueryResultArticle *);
-    effective_items_count ++) {
+           effective_items_count++) {
         r = sqlite3_step(stmt_query);
         if (r == SQLITE_DONE) {
             break;
@@ -112,13 +112,16 @@ int handle_inline(struct TdUpdateNewInlineQuery *update) {
 
         int id = sqlite3_column_int(stmt_query, 0);
         char *t = (char *) sqlite3_column_text(stmt_query, 1);
-        char *url = (char *) sqlite3_column_text(stmt_query, 2);
+        int index = sqlite3_column_int(stmt_query, 2);
+
+        char descr_temp[32];
+        snprintf(descr_temp, sizeof(descr_temp), "#%d", index);
 
         snprintf(id_temp, 32, "x_%d", id);
         results_inline[effective_items_count] =
                 create_inline_result(id_temp,
                                      t,
-                                     url,
+                                     descr_temp,
                                      t);
     }
 
diff --git a/store.c b/store.c
index cda3f7b..f6294ef 100644
--- a/store.c
+++ b/store.c
@@ -6,12 +6,14 @@
 #include "logic.h"
 #include "db.h"
 #include "botd.h"
+#include "utils.h"
 
 #include <stdbool.h>
 #include <stdlib.h>
 #include <string.h>
 #include <errno.h>
 #include <assert.h>
+#include <stdint.h>
 
 /* Temp object between message callback and link callback */
 struct say {
@@ -33,9 +35,10 @@ static void cb_link(bool successful, struct TdObject *result, struct TdError *er
     LOGDV("%s", s->text);
     struct TdMessageLink *link = (struct TdMessageLink *) result;
     int r;
+    uint32_t msg_index = tg_url_get_index(link->link_);
     sqlite3_stmt *stmt = NULL;
     if ((r = sqlite3_prepare_v2(db,
-                                "INSERT INTO says(t, url, msg) VALUES(?, ?, ?)",
+                                "INSERT INTO says(t, msg_index, msg) VALUES(?, ?, ?)",
                                 -1,
                                 &stmt,
                                 NULL))) {
@@ -48,8 +51,14 @@ static void cb_link(bool successful, struct TdObject *result, struct TdError *er
     if ((r = sqlite3_bind_text(stmt, 1, s->text, (int) strlen(s->text), NULL))) {
         goto sql_err;
     }
-    if ((r = sqlite3_bind_text(stmt, 2, link->link_, (int) strlen(link->link_), NULL))) {
-        goto sql_err;
+    if (msg_index) {
+        if ((r = sqlite3_bind_int64(stmt, 2, msg_index))) {
+            goto sql_err;
+        }
+    } else {
+        if ((r = sqlite3_bind_null(stmt, 2))) {
+            goto sql_err;
+        }
     }
     if ((r = sqlite3_bind_int64(stmt, 3, s->msg_id))) {
         goto sql_err;
diff --git a/utils.c b/utils.c
new file mode 100644
index 0000000..8215174
--- /dev/null
+++ b/utils.c
@@ -0,0 +1,28 @@
+/*
+ * Created by yuuta on 3/19/23.
+ */
+
+#include "utils.h"
+
+#include <inttypes.h>
+#include <string.h>
+#include <errno.h>
+#include <stdint.h>
+
+uint32_t tg_url_get_index(const char *url) {
+    char *tok = strrchr(url, '/');
+
+    if (!tok) {
+        return 0;
+    }
+
+    char *endptr;
+    uintmax_t unum = strtoumax(tok + 1, &endptr, 10);
+    if (strcmp(endptr, "") != 0 || (unum == UINTMAX_MAX && errno == ERANGE)) {
+        return 0;
+    }
+    if (unum > UINT32_MAX) {
+        return 0;
+    }
+    return (uint32_t) unum;
+}
diff --git a/utils.h b/utils.h
new file mode 100644
index 0000000..3a853f6
--- /dev/null
+++ b/utils.h
@@ -0,0 +1,12 @@
+/*
+ * Created by yuuta on 3/19/23.
+ */
+
+#ifndef KSYXBOT_UTILS_H
+#define KSYXBOT_UTILS_H
+
+#include <stdint.h>
+
+uint32_t tg_url_get_index(const char *url);
+
+#endif /* KSYXBOT_UTILS_H */