blob: 511b519796fc66b902e0e2431b7a446cf9307442 (
plain)
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
/* SQLite3 persistence layer replacing previous PG / Slonik implementation.
*
* Features:
* - Uses better-sqlite3 (synchronous, high-performance, safe for WAL mode)
* - Separate "write" connection (single) and a small pool of readonly connections for parallel reads
* - Applies requested PRAGMAs on every connection
* - Uses BEGIN IMMEDIATE transactions for write operations
* - STRICT tables (requires SQLite 3.37+)
*
* Environment:
* SQLITE_PATH (optional) - path to DB file. Defaults to ./data/hommabot.db
*
* Schema (mirrors previous PG schema):
* active_chats(id INTEGER PRIMARY KEY)
* permitted_users(id INTEGER PRIMARY KEY)
*/
import fs from "fs";
import path from "path";
import Database, { Database as BetterSqliteDb } from "better-sqlite3";
interface ActiveChat {
id: number;
}
interface PermittedUser {
id: number;
}
const DB_FILE =
process.env.SQLITE_PATH || path.join(process.cwd(), "data", "hommabot.db");
ensurePath();
const db = new Database(DB_FILE, {
fileMustExist: false,
readonly: false,
});
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("synchronous = NORMAL");
db.pragma("cache_size = 1000000000");
db.pragma("foreign_keys = ON");
db.pragma("temp_store = MEMORY");
const ddl = `
BEGIN IMMEDIATE;
CREATE TABLE IF NOT EXISTS active_chats (
id INTEGER PRIMARY KEY
) STRICT;
CREATE TABLE IF NOT EXISTS permitted_users (
id INTEGER PRIMARY KEY
) STRICT;
COMMIT;
`;
db.exec(ddl);
/**
* Initialize directory for DB file if required.
*/
function ensurePath() {
const dir = path.dirname(DB_FILE);
if (!fs.existsSync(dir)) {
fs.mkdirSync(dir, { recursive: true });
}
}
/**
* Wrap a write operation in a BEGIN IMMEDIATE transaction.
*/
function withWriteTx<T>(fn: (db: BetterSqliteDb) => T): T {
const begin = db.prepare("BEGIN IMMEDIATE");
const commit = db.prepare("COMMIT");
const rollback = db.prepare("ROLLBACK");
begin.run();
try {
const result = fn(db);
commit.run();
return result;
} catch (err) {
try {
rollback.run();
} catch {
/* ignore */
}
throw err;
}
}
/* Public API (mirrors old Slonik-based version) */
export const getActiveChats = async (): Promise<readonly ActiveChat[]> => {
const stmt = db.prepare("SELECT id FROM active_chats");
return stmt.all() as ActiveChat[];
};
export const addActiveChat = async (id: number): Promise<void> => {
withWriteTx((db) => {
const stmt = db.prepare(
"INSERT OR IGNORE INTO active_chats (id) VALUES (?)",
);
stmt.run(id);
});
};
export const removeActiveChat = async (id: number): Promise<void> => {
withWriteTx((db) => {
const stmt = db.prepare("DELETE FROM active_chats WHERE id = ?");
stmt.run(id);
});
};
export const getPermittedUsers = async (): Promise<
readonly PermittedUser[]
> => {
const stmt = db.prepare("SELECT id FROM permitted_users");
return stmt.all() as PermittedUser[];
};
|
