BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

stateDb.js 7.4 KiB Download Raw
1import { openDatabase } from "./database.js";
2
3const STATE_SCHEMA = `
4CREATE TABLE IF NOT EXISTS accounts (
5 account_id INTEGER PRIMARY KEY AUTOINCREMENT,
6 auth_subject TEXT NOT NULL UNIQUE,
7 email TEXT,
8 is_admin INTEGER NOT NULL DEFAULT 0
9);
10
11CREATE TABLE IF NOT EXISTS surname_claims (
12 surname_normalized TEXT PRIMARY KEY,
13 account_id INTEGER NOT NULL
14);
15
16CREATE TABLE IF NOT EXISTS characters (
17 character_id INTEGER PRIMARY KEY AUTOINCREMENT,
18 account_id INTEGER NOT NULL,
19 first_name TEXT NOT NULL,
20 last_name TEXT NOT NULL,
21 first_normalized TEXT NOT NULL,
22 last_normalized TEXT NOT NULL,
23 sex TEXT NOT NULL,
24 tile_x INTEGER NOT NULL DEFAULT 8,
25 tile_y INTEGER NOT NULL DEFAULT 8,
26 gait TEXT NOT NULL DEFAULT 'walk',
27 deletion_scheduled_at INTEGER
28);
29CREATE TABLE IF NOT EXISTS friend_requests (
30 request_id INTEGER PRIMARY KEY AUTOINCREMENT,
31 from_character_id INTEGER NOT NULL,
32 to_character_id INTEGER NOT NULL,
33 created_at INTEGER NOT NULL,
34 UNIQUE (from_character_id, to_character_id)
35);
36
37CREATE TABLE IF NOT EXISTS friendships (
38 character_id_a INTEGER NOT NULL,
39 character_id_b INTEGER NOT NULL,
40 created_at INTEGER NOT NULL,
41 PRIMARY KEY (character_id_a, character_id_b)
42);
43`;
44
45export class StateDatabase
46{
47 constructor (filePath)
48 {
49 this.db = openDatabase(filePath);
50 this.db.exec(STATE_SCHEMA);
51 }
52
53 getAccountBySubject (authSubject)
54 {
55 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
56 }
57
58 createAccount (authSubject, isAdmin, email = null)
59 {
60 this.db.prepare("INSERT INTO accounts (auth_subject, email, is_admin) VALUES (?, ?, ?)").run(
61 authSubject,
62 email,
63 isAdmin ? 1 : 0
64 );
65 return this.getAccountBySubject(authSubject);
66 }
67
68 ensureAccount (authSubject, isAdmin, email = null)
69 {
70 const existing = this.getAccountBySubject(authSubject);
71 if (existing)
72 { return existing; }
73
74 return this.createAccount(authSubject, isAdmin, email);
75 }
76
77 listCharacters (accountId)
78 {
79 return this.db.prepare(`
80 SELECT * FROM characters
81 WHERE account_id = ?
82 ORDER BY character_id ASC
83 `).all(accountId);
84 }
85
86 getCharacter (characterId)
87 {
88 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
89 }
90
91 findSurnameClaim (surnameNormalized)
92 {
93 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
94 }
95
96 createCharacter (accountId, firstName, lastName, sex, tileX, tileY)
97 {
98 const firstNormalized = normalizeName(firstName);
99 const lastNormalized = normalizeName(lastName);
100 this.db.exec("BEGIN");
101 try
102 {
103 this.db.prepare(`
104 INSERT INTO characters (
105 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y
106 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
107 `).run(accountId, firstName, lastName, firstNormalized, lastNormalized, sex, tileX, tileY);
108 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
109
110 const claim = this.findSurnameClaim(lastNormalized);
111 if (!claim)
112 {
113 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
114 lastNormalized,
115 accountId
116 );
117 }
118
119 this.db.exec("COMMIT");
120 return this.getCharacter(characterId);
121 }
122 catch (error)
123 {
124 this.db.exec("ROLLBACK");
125 throw error;
126 }
127 }
128
129 scheduleDeletion (characterId, at)
130 {
131 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
132 }
133
134 cancelDeletion (characterId)
135 {
136 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
137 }
138
139 deleteCharacter (characterId)
140 {
141 const character = this.getCharacter(characterId);
142 if (!character)
143 { return; }
144
145 this.db.exec("BEGIN");
146 try
147 {
148 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
149 this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run(
150 characterId,
151 characterId
152 );
153 this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run(
154 characterId,
155 characterId
156 );
157 const remaining = this.db.prepare(`
158 SELECT COUNT(*) AS count FROM characters
159 WHERE account_id = ? AND last_normalized = ?
160 `).get(character.account_id, character.last_normalized);
161 if (!remaining.count)
162 {
163 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
164 character.last_normalized,
165 character.account_id
166 );
167 }
168 this.db.exec("COMMIT");
169 }
170 catch (error)
171 {
172 this.db.exec("ROLLBACK");
173 throw error;
174 }
175 }
176
177 saveCharacterPosition (characterId, tileX, tileY, gait)
178 {
179 this.db.prepare("UPDATE characters SET tile_x = ?, tile_y = ?, gait = ? WHERE character_id = ?").run(
180 tileX,
181 tileY,
182 gait,
183 characterId
184 );
185 }
186
187 listDueDeletions (now)
188 {
189 return this.db.prepare(`
190 SELECT * FROM characters
191 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
192 `).all(now);
193 }
194
195 getCharacterByName (firstName, lastName)
196 {
197 return this.db.prepare(`
198 SELECT * FROM characters
199 WHERE first_normalized = ? AND last_normalized = ?
200 `).get(normalizeName(firstName), normalizeName(lastName)) ?? null;
201 }
202
203 listFriendRecords (characterId)
204 {
205 return this.db.prepare(`
206 SELECT
207 CASE WHEN character_id_a = ? THEN character_id_b ELSE character_id_a END AS character_id,
208 created_at
209 FROM friendships
210 WHERE character_id_a = ? OR character_id_b = ?
211 ORDER BY created_at ASC
212 `).all(characterId, characterId, characterId);
213 }
214
215 areFriends (characterId, otherId)
216 {
217 const [a, b] = pairIds(characterId, otherId);
218 return !!this.db.prepare(`
219 SELECT 1 FROM friendships WHERE character_id_a = ? AND character_id_b = ?
220 `).get(a, b);
221 }
222
223 insertFriendship (characterId, otherId)
224 {
225 const [a, b] = pairIds(characterId, otherId);
226 this.db.prepare(`
227 INSERT INTO friendships (character_id_a, character_id_b, created_at)
228 VALUES (?, ?, ?)
229 `).run(a, b, Date.now());
230 }
231
232 deleteFriendship (characterId, otherId)
233 {
234 const [a, b] = pairIds(characterId, otherId);
235 this.db.prepare(`
236 DELETE FROM friendships WHERE character_id_a = ? AND character_id_b = ?
237 `).run(a, b);
238 }
239
240 listIncomingRequests (characterId)
241 {
242 return this.db.prepare(`
243 SELECT request_id, from_character_id, to_character_id, created_at
244 FROM friend_requests
245 WHERE to_character_id = ?
246 ORDER BY created_at ASC
247 `).all(characterId);
248 }
249
250 listOutgoingRequests (characterId)
251 {
252 return this.db.prepare(`
253 SELECT request_id, from_character_id, to_character_id, created_at
254 FROM friend_requests
255 WHERE from_character_id = ?
256 ORDER BY created_at ASC
257 `).all(characterId);
258 }
259
260 getFriendRequest (fromId, toId)
261 {
262 return this.db.prepare(`
263 SELECT * FROM friend_requests
264 WHERE from_character_id = ? AND to_character_id = ?
265 `).get(fromId, toId) ?? null;
266 }
267
268 insertFriendRequest (fromId, toId)
269 {
270 this.db.prepare(`
271 INSERT INTO friend_requests (from_character_id, to_character_id, created_at)
272 VALUES (?, ?, ?)
273 `).run(fromId, toId, Date.now());
274 }
275
276 deleteFriendRequest (fromId, toId)
277 {
278 this.db.prepare(`
279 DELETE FROM friend_requests
280 WHERE from_character_id = ? AND to_character_id = ?
281 `).run(fromId, toId);
282 }
283
284 close ()
285 {
286 this.db.close();
287 }
288}
289
290export function normalizeName (value)
291{
292 return String(value ?? "").trim().toLocaleLowerCase();
293}
294
295function pairIds (left, right)
296{
297 return left < right ? [left, right] : [right, left];
298}