BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

stateDb.js 14.5 KiB Download Raw
1import { openDatabase } from "./database.js";
2import { emptyEquipment, emptySlots, insertStack, publicSlots } from "../../../shared/items/inventory.js";
3import { STARTER_CURRENCY, STARTER_ITEMS, itemDefinition } from "../../../shared/definitions/items.js";
4
5const STATE_SCHEMA = `
6CREATE TABLE IF NOT EXISTS accounts (
7 account_id INTEGER PRIMARY KEY AUTOINCREMENT,
8 auth_subject TEXT NOT NULL UNIQUE,
9 email TEXT,
10 is_admin INTEGER NOT NULL DEFAULT 0
11);
12
13CREATE TABLE IF NOT EXISTS surname_claims (
14 surname_normalized TEXT PRIMARY KEY,
15 account_id INTEGER NOT NULL
16);
17
18CREATE TABLE IF NOT EXISTS characters (
19 character_id INTEGER PRIMARY KEY AUTOINCREMENT,
20 account_id INTEGER NOT NULL,
21 first_name TEXT NOT NULL,
22 last_name TEXT NOT NULL,
23 first_normalized TEXT NOT NULL,
24 last_normalized TEXT NOT NULL,
25 sex TEXT NOT NULL,
26 tile_x INTEGER NOT NULL DEFAULT 8,
27 tile_y INTEGER NOT NULL DEFAULT 8,
28 gait TEXT NOT NULL DEFAULT 'walk',
29 deletion_scheduled_at INTEGER
30);
31CREATE TABLE IF NOT EXISTS friend_requests (
32 request_id INTEGER PRIMARY KEY AUTOINCREMENT,
33 from_character_id INTEGER NOT NULL,
34 to_character_id INTEGER NOT NULL,
35 created_at INTEGER NOT NULL,
36 UNIQUE (from_character_id, to_character_id)
37);
38
39CREATE TABLE IF NOT EXISTS friendships (
40 character_id_a INTEGER NOT NULL,
41 character_id_b INTEGER NOT NULL,
42 created_at INTEGER NOT NULL,
43 PRIMARY KEY (character_id_a, character_id_b)
44);
45
46CREATE TABLE IF NOT EXISTS persistent_entities (
47 entity_id INTEGER PRIMARY KEY,
48 tile_x INTEGER NOT NULL,
49 tile_y INTEGER NOT NULL,
50 components_blob TEXT NOT NULL
51);
52
53CREATE TABLE IF NOT EXISTS character_bags (
54 character_id INTEGER PRIMARY KEY,
55 inventory_blob TEXT NOT NULL,
56 equipment_blob TEXT NOT NULL,
57 currency INTEGER NOT NULL DEFAULT 0,
58 vault_blob TEXT NOT NULL,
59 vault_currency INTEGER NOT NULL DEFAULT 0
60);
61
62CREATE TABLE IF NOT EXISTS account_inheritance (
63 account_id INTEGER PRIMARY KEY,
64 slots_blob TEXT NOT NULL,
65 currency INTEGER NOT NULL DEFAULT 0
66);
67
68CREATE TABLE IF NOT EXISTS ground_items (
69 ground_item_id INTEGER PRIMARY KEY,
70 tile_x INTEGER NOT NULL,
71 tile_y INTEGER NOT NULL,
72 stack_blob TEXT NOT NULL,
73 dropped_at INTEGER NOT NULL,
74 expires_at INTEGER NOT NULL,
75 source TEXT NOT NULL,
76 dropped_by_character_id INTEGER
77);
78`;
79
80export class StateDatabase
81{
82 constructor (filePath)
83 {
84 this.db = openDatabase(filePath);
85 this.db.exec(STATE_SCHEMA);
86 }
87
88 getAccountBySubject (authSubject)
89 {
90 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
91 }
92
93 createAccount (authSubject, isAdmin, email = null)
94 {
95 this.db.prepare("INSERT INTO accounts (auth_subject, email, is_admin) VALUES (?, ?, ?)").run(
96 authSubject,
97 email,
98 isAdmin ? 1 : 0
99 );
100 return this.getAccountBySubject(authSubject);
101 }
102
103 ensureAccount (authSubject, isAdmin, email = null)
104 {
105 const existing = this.getAccountBySubject(authSubject);
106 if (existing)
107 { return existing; }
108
109 return this.createAccount(authSubject, isAdmin, email);
110 }
111
112 listCharacters (accountId)
113 {
114 return this.db.prepare(`
115 SELECT * FROM characters
116 WHERE account_id = ?
117 ORDER BY character_id ASC
118 `).all(accountId);
119 }
120
121 getCharacter (characterId)
122 {
123 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
124 }
125
126 findSurnameClaim (surnameNormalized)
127 {
128 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
129 }
130
131 createCharacter (accountId, firstName, lastName, sex, tileX, tileY)
132 {
133 const firstNormalized = normalizeName(firstName);
134 const lastNormalized = normalizeName(lastName);
135 this.db.exec("BEGIN");
136 try
137 {
138 this.db.prepare(`
139 INSERT INTO characters (
140 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y
141 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
142 `).run(accountId, firstName, lastName, firstNormalized, lastNormalized, sex, tileX, tileY);
143 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
144
145 const claim = this.findSurnameClaim(lastNormalized);
146 if (!claim)
147 {
148 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
149 lastNormalized,
150 accountId
151 );
152 }
153
154 this.db.exec("COMMIT");
155 this.#ensureBag(characterId);
156 return this.getCharacter(characterId);
157 }
158 catch (error)
159 {
160 this.db.exec("ROLLBACK");
161 throw error;
162 }
163 }
164
165 scheduleDeletion (characterId, at)
166 {
167 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
168 }
169
170 cancelDeletion (characterId)
171 {
172 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
173 }
174
175 deleteCharacter (characterId)
176 {
177 const character = this.getCharacter(characterId);
178 if (!character)
179 { return; }
180
181 this.db.exec("BEGIN");
182 try
183 {
184 this.#moveGoodsToInheritance(character);
185 this.db.prepare("DELETE FROM character_bags WHERE character_id = ?").run(characterId);
186 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
187 this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run(
188 characterId,
189 characterId
190 );
191 this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run(
192 characterId,
193 characterId
194 );
195 const remaining = this.db.prepare(`
196 SELECT COUNT(*) AS count FROM characters
197 WHERE account_id = ? AND last_normalized = ?
198 `).get(character.account_id, character.last_normalized);
199 if (!remaining.count)
200 {
201 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
202 character.last_normalized,
203 character.account_id
204 );
205 }
206 this.db.exec("COMMIT");
207 }
208 catch (error)
209 {
210 this.db.exec("ROLLBACK");
211 throw error;
212 }
213 }
214
215 saveCharacterPosition (characterId, tileX, tileY, gait)
216 {
217 this.db.prepare("UPDATE characters SET tile_x = ?, tile_y = ?, gait = ? WHERE character_id = ?").run(
218 tileX,
219 tileY,
220 gait,
221 characterId
222 );
223 }
224
225 listDueDeletions (now)
226 {
227 return this.db.prepare(`
228 SELECT * FROM characters
229 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
230 `).all(now);
231 }
232
233 getCharacterByName (firstName, lastName)
234 {
235 return this.db.prepare(`
236 SELECT * FROM characters
237 WHERE first_normalized = ? AND last_normalized = ?
238 `).get(normalizeName(firstName), normalizeName(lastName)) ?? null;
239 }
240
241 listFriendRecords (characterId)
242 {
243 return this.db.prepare(`
244 SELECT
245 CASE WHEN character_id_a = ? THEN character_id_b ELSE character_id_a END AS character_id,
246 created_at
247 FROM friendships
248 WHERE character_id_a = ? OR character_id_b = ?
249 ORDER BY created_at ASC
250 `).all(characterId, characterId, characterId);
251 }
252
253 areFriends (characterId, otherId)
254 {
255 const [a, b] = pairIds(characterId, otherId);
256 return !!this.db.prepare(`
257 SELECT 1 FROM friendships WHERE character_id_a = ? AND character_id_b = ?
258 `).get(a, b);
259 }
260
261 insertFriendship (characterId, otherId)
262 {
263 const [a, b] = pairIds(characterId, otherId);
264 this.db.prepare(`
265 INSERT INTO friendships (character_id_a, character_id_b, created_at)
266 VALUES (?, ?, ?)
267 `).run(a, b, Date.now());
268 }
269
270 deleteFriendship (characterId, otherId)
271 {
272 const [a, b] = pairIds(characterId, otherId);
273 this.db.prepare(`
274 DELETE FROM friendships WHERE character_id_a = ? AND character_id_b = ?
275 `).run(a, b);
276 }
277
278 listIncomingRequests (characterId)
279 {
280 return this.db.prepare(`
281 SELECT request_id, from_character_id, to_character_id, created_at
282 FROM friend_requests
283 WHERE to_character_id = ?
284 ORDER BY created_at ASC
285 `).all(characterId);
286 }
287
288 listOutgoingRequests (characterId)
289 {
290 return this.db.prepare(`
291 SELECT request_id, from_character_id, to_character_id, created_at
292 FROM friend_requests
293 WHERE from_character_id = ?
294 ORDER BY created_at ASC
295 `).all(characterId);
296 }
297
298 getFriendRequest (fromId, toId)
299 {
300 return this.db.prepare(`
301 SELECT * FROM friend_requests
302 WHERE from_character_id = ? AND to_character_id = ?
303 `).get(fromId, toId) ?? null;
304 }
305
306 insertFriendRequest (fromId, toId)
307 {
308 this.db.prepare(`
309 INSERT INTO friend_requests (from_character_id, to_character_id, created_at)
310 VALUES (?, ?, ?)
311 `).run(fromId, toId, Date.now());
312 }
313
314 deleteFriendRequest (fromId, toId)
315 {
316 this.db.prepare(`
317 DELETE FROM friend_requests
318 WHERE from_character_id = ? AND to_character_id = ?
319 `).run(fromId, toId);
320 }
321
322 listPersistentInRegion (minX, minY, maxX, maxY)
323 {
324 return this.db.prepare(`
325 SELECT entity_id, tile_x, tile_y, components_blob
326 FROM persistent_entities
327 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
328 `).all(minX, maxX, minY, maxY);
329 }
330
331 replacePersistentInRegion (minX, minY, maxX, maxY, entities)
332 {
333 this.db.exec("BEGIN");
334 try
335 {
336 this.db.prepare(`
337 DELETE FROM persistent_entities
338 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
339 `).run(minX, maxX, minY, maxY);
340 const insert = this.db.prepare(`
341 INSERT INTO persistent_entities (entity_id, tile_x, tile_y, components_blob)
342 VALUES (?, ?, ?, ?)
343 `);
344 for (const entity of entities)
345 {
346 insert.run(
347 entity.entityId,
348 entity.tileX,
349 entity.tileY,
350 JSON.stringify(entity.payload?.components ?? entity.components)
351 );
352 }
353 this.db.exec("COMMIT");
354 }
355 catch (error)
356 {
357 this.db.exec("ROLLBACK");
358 throw error;
359 }
360 }
361
362 #ensureBag (characterId)
363 {
364 const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
365 if (existing)
366 { return parseBag(existing, characterId); }
367 const slots = emptySlots();
368 for (const starter of STARTER_ITEMS)
369 {
370 const result = insertStack(slots, { ...starter }, itemDefinition(starter.itemId));
371 if (result.ok)
372 { slots.splice(0, slots.length, ...result.slots); }
373 }
374 this.db.prepare(`
375 INSERT INTO character_bags (
376 character_id, inventory_blob, equipment_blob, currency, vault_blob, vault_currency
377 ) VALUES (?, ?, ?, ?, ?, 0)
378 `).run(
379 characterId,
380 JSON.stringify(slots),
381 JSON.stringify(emptyEquipment()),
382 STARTER_CURRENCY,
383 JSON.stringify(emptySlots())
384 );
385 return this.getBag(characterId);
386 }
387
388 getBag (characterId)
389 {
390 const row = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
391 if (row)
392 { return parseBag(row, characterId); }
393 if (this.getCharacter(characterId))
394 { return this.#ensureBag(characterId); }
395 return null;
396 }
397
398 saveBag (characterId, bag)
399 {
400 this.#ensureBag(characterId);
401 this.db.prepare(`
402 UPDATE character_bags
403 SET inventory_blob = ?, equipment_blob = ?, currency = ?, vault_blob = ?, vault_currency = ?
404 WHERE character_id = ?
405 `).run(
406 JSON.stringify(bag.inventory ?? emptySlots()),
407 JSON.stringify(bag.equipment ?? emptyEquipment()),
408 bag.currency ?? 0,
409 JSON.stringify(bag.vault ?? emptySlots()),
410 bag.vaultCurrency ?? 0,
411 characterId
412 );
413 }
414
415 getInheritance (accountId)
416 {
417 const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId);
418 if (!row)
419 { return { accountId, slots: emptySlots(), currency: 0 }; }
420 return {
421 accountId,
422 slots: JSON.parse(row.slots_blob),
423 currency: row.currency
424 };
425 }
426
427 saveInheritance (accountId, inheritance)
428 {
429 this.db.prepare(`
430 INSERT INTO account_inheritance (account_id, slots_blob, currency)
431 VALUES (?, ?, ?)
432 ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = excluded.currency
433 `).run(accountId, JSON.stringify(inheritance.slots ?? emptySlots()), inheritance.currency ?? 0);
434 }
435
436 #moveGoodsToInheritance (character)
437 {
438 const bag = this.getBag(character.character_id) ?? {
439 inventory: emptySlots(),
440 equipment: emptyEquipment(),
441 currency: 0,
442 vault: emptySlots(),
443 vaultCurrency: 0
444 };
445 const inheritance = this.getInheritance(character.account_id);
446 const incoming = [
447 ...(bag.inventory ?? []),
448 ...Object.values(bag.equipment?.slots ?? {}),
449 ...(bag.vault ?? [])
450 ].filter(Boolean);
451 let slots = inheritance.slots ?? emptySlots();
452 for (const stack of incoming)
453 {
454 const def = itemDefinition(stack.itemId);
455 if (!def)
456 { continue; }
457 const result = insertStack(slots, stack, def);
458 if (result.ok)
459 { slots = result.slots; }
460 else
461 {
462 const extra = result.slots;
463 for (const leftover of extra)
464 {
465 if (leftover)
466 { slots.push(leftover); }
467 }
468 slots.push({ ...stack, quantity: result.remainder });
469 }
470 }
471 this.saveInheritance(character.account_id, {
472 slots: publicSlots(slots),
473 currency: (inheritance.currency ?? 0) + (bag.currency ?? 0) + (bag.vaultCurrency ?? 0)
474 });
475 }
476
477 nextGroundItemId ()
478 {
479 const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get();
480 return (row?.id ?? 0) + 1;
481 }
482
483 listGroundItemsInRegion (minX, minY, maxX, maxY)
484 {
485 return this.db.prepare(`
486 SELECT * FROM ground_items
487 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
488 `).all(minX, maxX, minY, maxY).map(parseGroundItem);
489 }
490
491 upsertGroundItem (item)
492 {
493 this.db.prepare(`
494 INSERT INTO ground_items (
495 ground_item_id, tile_x, tile_y, stack_blob, dropped_at, expires_at, source, dropped_by_character_id
496 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
497 ON CONFLICT(ground_item_id) DO UPDATE SET
498 tile_x = excluded.tile_x,
499 tile_y = excluded.tile_y,
500 stack_blob = excluded.stack_blob,
501 dropped_at = excluded.dropped_at,
502 expires_at = excluded.expires_at,
503 source = excluded.source,
504 dropped_by_character_id = excluded.dropped_by_character_id
505 `).run(
506 item.groundItemId,
507 item.tileX,
508 item.tileY,
509 JSON.stringify(item.stack),
510 item.droppedAt,
511 item.expiresAt,
512 item.source,
513 item.droppedByCharacterId ?? null
514 );
515 }
516
517 deleteGroundItem (groundItemId)
518 {
519 this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId);
520 }
521
522 close ()
523 {
524 this.db.close();
525 }
526}
527
528export function normalizeName (value)
529{
530 return String(value ?? "").trim().toLocaleLowerCase();
531}
532
533function pairIds (left, right)
534{
535 return left < right ? [left, right] : [right, left];
536}
537
538function parseGroundItem (row)
539{
540 return {
541 groundItemId: row.ground_item_id,
542 tileX: row.tile_x,
543 tileY: row.tile_y,
544 stack: JSON.parse(row.stack_blob),
545 droppedAt: row.dropped_at,
546 expiresAt: row.expires_at,
547 source: row.source,
548 droppedByCharacterId: row.dropped_by_character_id
549 };
550}
551
552function parseBag (row, characterId)
553{
554 return {
555 characterId,
556 inventory: JSON.parse(row.inventory_blob),
557 equipment: JSON.parse(row.equipment_blob),
558 currency: row.currency,
559 vault: JSON.parse(row.vault_blob),
560 vaultCurrency: row.vault_currency
561 };
562}