1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
/*
* Created by yuuta on 11/28/21.
*/
#include "db.h"
#include "log.h"
#include "botd.h"
#include <stdio.h>
#include <sqlite3.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)"
};
sqlite3 *db = NULL;
sqlite3_stmt *stmt_global_stat = NULL;
void db_init(void) {
int r;
r = sqlite3_open(cmd.db_path, &db);
if (r) {
LOGFEV("Cannot open SQLite3 database: %s", r, sqlite3_errstr(r));
}
char *errmsg = NULL;
/* Enable foreign key support */
if ((r = sqlite3_exec(db, "PRAGMA foreign_keys = ON", NULL, NULL, &errmsg))) {
goto sql_err;
sql_err:
db_close();
LOGFEV("%s", r, errmsg);
}
/* Get user_version */
sqlite3_stmt *stmt;
if ((r = sqlite3_prepare_v2(db, "PRAGMA user_version", -1, &stmt, NULL))) {
goto sql_err;
}
if ((r = sqlite3_step(stmt)) != SQLITE_ROW) {
errmsg = (char *) sqlite3_errstr(r);
sqlite3_finalize(stmt);
goto sql_err;
}
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;
}
char sql[60];
sprintf(sql, "PRAGMA user_version = %d;", to);
if ((r = sqlite3_exec(db, sql, NULL, NULL, &errmsg))) {
goto sql_err;
}
}
/* Prepare statements */
if ((r = sqlite3_prepare_v2(db, "SELECT * FROM stats_global;", -1, &stmt_global_stat, NULL))) {
goto sql_err;
}
}
void db_close(void) {
if (db == NULL)
return;
if (stmt_global_stat) {
sqlite3_finalize(stmt_global_stat);
stmt_global_stat = NULL;
}
sqlite3_close(db);
db = NULL;
}
|
