Use SQLite3 view to do global stats

2 files changed, 18 insertions, 14 deletions

diff --git a/db.c b/db.c
index 6c49f3d..98b35a0 100644
--- a/db.c
+++ b/db.c
@@ -20,7 +20,10 @@ static const char *sql_upgrades[] = {
         "'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)"
 };
 
 sqlite3 *db = NULL;
diff --git a/stat.c b/stat.c
index 2c1323e..143a98d 100644
--- a/stat.c
+++ b/stat.c
@@ -12,15 +12,15 @@
 #include <string.h>
 
 static char *praises[] = {
-        "普天之下，莫非 K 土。TGCN 共有 %d 位 K 教授的虔诚学生卖了 %d 句菜，其中卖菜最多的人共卖了 %d 句菜，平均每人卖了 %d 句菜。",
-        "率土之滨，莫非 K 臣。TGCN 共有 %d 位 K 教授的虔诚学生卖了 %d 句菜，其中卖菜最多的人共卖了 %d 句菜，平均每人卖了 %d 句菜。",
+        "普天之下，莫非 K 土。",
+        "率土之滨，莫非 K 臣。",
 };
 
 void stat(struct TdMessage *msg) {
     int r;
     char m[1024];
     sqlite3_stmt *stmt = NULL;
-    if ((r = sqlite3_prepare_v2(db, "SELECT COUNT(user), SUM(i), MAX(i), AVG(i) FROM stats;", -1, &stmt, NULL))) {
+    if ((r = sqlite3_prepare_v2(db, "SELECT * FROM stats_global;", -1, &stmt, NULL))) {
         goto sql_err;
         sql_err:
         {
@@ -31,19 +31,20 @@ void stat(struct TdMessage *msg) {
         }
     }
     if ((r = sqlite3_step(stmt)) != SQLITE_ROW) goto sql_err;
-    snprintf(m, 1024, praises[rand() % (sizeof(praises) / sizeof(char *))],
+
+    snprintf(m, sizeof(m), "%s"
+                                  "TGCN 共有 %d 位 K 教授的虔诚学生卖了 %d 句菜，"
+                                  "其中卖菜最多的人共卖了 %d 句菜，"
+                                  "平均每人卖了 %d 句菜。"
+                                  "\n\n"
+                                  "共有 %d 句教授语录。\n"
+                                  "Bot 已运行 %ld 秒。",
+             praises[rand() % (sizeof(praises) / sizeof(char *))],
              sqlite3_column_int(stmt, 0),
              sqlite3_column_int(stmt, 1),
              sqlite3_column_int(stmt, 2),
-             sqlite3_column_int(stmt, 3));
-    sqlite3_finalize(stmt);
-    stmt = NULL;
-
-    if ((r = sqlite3_prepare_v2(db, "SELECT COUNT(t) FROM says;", -1, &stmt, NULL))) goto sql_err;
-    if ((r = sqlite3_step(stmt)) != SQLITE_ROW) goto sql_err;
-    size_t len = strlen(m);
-    snprintf(&m[len], 1024 - len, "\n\n共有 %d 句教授语录。\nBot 已运行 %ld 秒。",
-             sqlite3_column_int(stmt, 0),
+             sqlite3_column_int(stmt, 3),
+             sqlite3_column_int(stmt, 4),
              time(NULL) - start_time);
     sqlite3_finalize(stmt);
     goto send;