BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

stateDb.js 17.2 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
79CREATE TABLE IF NOT EXISTS courier_transits (
80 courier_id INTEGER PRIMARY KEY AUTOINCREMENT,
81 sender_character_id INTEGER NOT NULL,
82 destination_kind TEXT NOT NULL,
83 destination_character_id INTEGER,
84 cargo_blob TEXT NOT NULL,
85 origin_x REAL NOT NULL,
86 origin_y REAL NOT NULL,
87 dest_x REAL NOT NULL,
88 dest_y REAL NOT NULL,
89 traveled REAL NOT NULL DEFAULT 0,
90 status TEXT NOT NULL,
91 entity_id INTEGER,
92 updated_at INTEGER NOT NULL,
93 created_at INTEGER NOT NULL
94);
95`;
96
97export class StateDatabase
98{
99 constructor (filePath)
100 {
101 this.db = openDatabase(filePath);
102 this.db.exec(STATE_SCHEMA);
103 }
104
105 getAccountBySubject (authSubject)
106 {
107 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
108 }
109
110 createAccount (authSubject, isAdmin, email = null)
111 {
112 this.db.prepare("INSERT INTO accounts (auth_subject, email, is_admin) VALUES (?, ?, ?)").run(
113 authSubject,
114 email,
115 isAdmin ? 1 : 0
116 );
117 return this.getAccountBySubject(authSubject);
118 }
119
120 ensureAccount (authSubject, isAdmin, email = null)
121 {
122 const existing = this.getAccountBySubject(authSubject);
123 if (existing)
124 { return existing; }
125
126 return this.createAccount(authSubject, isAdmin, email);
127 }
128
129 listCharacters (accountId)
130 {
131 return this.db.prepare(`
132 SELECT * FROM characters
133 WHERE account_id = ?
134 ORDER BY character_id ASC
135 `).all(accountId);
136 }
137
138 getCharacter (characterId)
139 {
140 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
141 }
142
143 findSurnameClaim (surnameNormalized)
144 {
145 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
146 }
147
148 createCharacter (accountId, firstName, lastName, sex, tileX, tileY)
149 {
150 const firstNormalized = normalizeName(firstName);
151 const lastNormalized = normalizeName(lastName);
152 this.db.exec("BEGIN");
153 try
154 {
155 this.db.prepare(`
156 INSERT INTO characters (
157 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y
158 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
159 `).run(accountId, firstName, lastName, firstNormalized, lastNormalized, sex, tileX, tileY);
160 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
161
162 const claim = this.findSurnameClaim(lastNormalized);
163 if (!claim)
164 {
165 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
166 lastNormalized,
167 accountId
168 );
169 }
170
171 this.db.exec("COMMIT");
172 this.#ensureBag(characterId);
173 return this.getCharacter(characterId);
174 }
175 catch (error)
176 {
177 this.db.exec("ROLLBACK");
178 throw error;
179 }
180 }
181
182 scheduleDeletion (characterId, at)
183 {
184 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
185 }
186
187 cancelDeletion (characterId)
188 {
189 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
190 }
191
192 deleteCharacter (characterId)
193 {
194 const character = this.getCharacter(characterId);
195 if (!character)
196 { return; }
197
198 this.db.exec("BEGIN");
199 try
200 {
201 this.#moveGoodsToInheritance(character);
202 this.db.prepare("DELETE FROM character_bags WHERE character_id = ?").run(characterId);
203 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
204 this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run(
205 characterId,
206 characterId
207 );
208 this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run(
209 characterId,
210 characterId
211 );
212 const remaining = this.db.prepare(`
213 SELECT COUNT(*) AS count FROM characters
214 WHERE account_id = ? AND last_normalized = ?
215 `).get(character.account_id, character.last_normalized);
216 if (!remaining.count)
217 {
218 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
219 character.last_normalized,
220 character.account_id
221 );
222 }
223 this.db.exec("COMMIT");
224 }
225 catch (error)
226 {
227 this.db.exec("ROLLBACK");
228 throw error;
229 }
230 }
231
232 saveCharacterPosition (characterId, tileX, tileY, gait)
233 {
234 this.db.prepare("UPDATE characters SET tile_x = ?, tile_y = ?, gait = ? WHERE character_id = ?").run(
235 tileX,
236 tileY,
237 gait,
238 characterId
239 );
240 }
241
242 listDueDeletions (now)
243 {
244 return this.db.prepare(`
245 SELECT * FROM characters
246 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
247 `).all(now);
248 }
249
250 getCharacterByName (firstName, lastName)
251 {
252 return this.db.prepare(`
253 SELECT * FROM characters
254 WHERE first_normalized = ? AND last_normalized = ?
255 `).get(normalizeName(firstName), normalizeName(lastName)) ?? null;
256 }
257
258 listFriendRecords (characterId)
259 {
260 return this.db.prepare(`
261 SELECT
262 CASE WHEN character_id_a = ? THEN character_id_b ELSE character_id_a END AS character_id,
263 created_at
264 FROM friendships
265 WHERE character_id_a = ? OR character_id_b = ?
266 ORDER BY created_at ASC
267 `).all(characterId, characterId, characterId);
268 }
269
270 areFriends (characterId, otherId)
271 {
272 const [a, b] = pairIds(characterId, otherId);
273 return !!this.db.prepare(`
274 SELECT 1 FROM friendships WHERE character_id_a = ? AND character_id_b = ?
275 `).get(a, b);
276 }
277
278 insertFriendship (characterId, otherId)
279 {
280 const [a, b] = pairIds(characterId, otherId);
281 this.db.prepare(`
282 INSERT INTO friendships (character_id_a, character_id_b, created_at)
283 VALUES (?, ?, ?)
284 `).run(a, b, Date.now());
285 }
286
287 deleteFriendship (characterId, otherId)
288 {
289 const [a, b] = pairIds(characterId, otherId);
290 this.db.prepare(`
291 DELETE FROM friendships WHERE character_id_a = ? AND character_id_b = ?
292 `).run(a, b);
293 }
294
295 listIncomingRequests (characterId)
296 {
297 return this.db.prepare(`
298 SELECT request_id, from_character_id, to_character_id, created_at
299 FROM friend_requests
300 WHERE to_character_id = ?
301 ORDER BY created_at ASC
302 `).all(characterId);
303 }
304
305 listOutgoingRequests (characterId)
306 {
307 return this.db.prepare(`
308 SELECT request_id, from_character_id, to_character_id, created_at
309 FROM friend_requests
310 WHERE from_character_id = ?
311 ORDER BY created_at ASC
312 `).all(characterId);
313 }
314
315 getFriendRequest (fromId, toId)
316 {
317 return this.db.prepare(`
318 SELECT * FROM friend_requests
319 WHERE from_character_id = ? AND to_character_id = ?
320 `).get(fromId, toId) ?? null;
321 }
322
323 insertFriendRequest (fromId, toId)
324 {
325 this.db.prepare(`
326 INSERT INTO friend_requests (from_character_id, to_character_id, created_at)
327 VALUES (?, ?, ?)
328 `).run(fromId, toId, Date.now());
329 }
330
331 deleteFriendRequest (fromId, toId)
332 {
333 this.db.prepare(`
334 DELETE FROM friend_requests
335 WHERE from_character_id = ? AND to_character_id = ?
336 `).run(fromId, toId);
337 }
338
339 listPersistentInRegion (minX, minY, maxX, maxY)
340 {
341 return this.db.prepare(`
342 SELECT entity_id, tile_x, tile_y, components_blob
343 FROM persistent_entities
344 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
345 `).all(minX, maxX, minY, maxY);
346 }
347
348 replacePersistentInRegion (minX, minY, maxX, maxY, entities)
349 {
350 this.db.exec("BEGIN");
351 try
352 {
353 this.db.prepare(`
354 DELETE FROM persistent_entities
355 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
356 `).run(minX, maxX, minY, maxY);
357 const insert = this.db.prepare(`
358 INSERT INTO persistent_entities (entity_id, tile_x, tile_y, components_blob)
359 VALUES (?, ?, ?, ?)
360 `);
361 for (const entity of entities)
362 {
363 insert.run(
364 entity.entityId,
365 entity.tileX,
366 entity.tileY,
367 JSON.stringify(entity.payload?.components ?? entity.components)
368 );
369 }
370 this.db.exec("COMMIT");
371 }
372 catch (error)
373 {
374 this.db.exec("ROLLBACK");
375 throw error;
376 }
377 }
378
379 #ensureBag (characterId)
380 {
381 const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
382 if (existing)
383 { return parseBag(existing, characterId); }
384 const slots = emptySlots();
385 for (const starter of STARTER_ITEMS)
386 {
387 const result = insertStack(slots, { ...starter }, itemDefinition(starter.itemId));
388 if (result.ok)
389 { slots.splice(0, slots.length, ...result.slots); }
390 }
391 this.db.prepare(`
392 INSERT INTO character_bags (
393 character_id, inventory_blob, equipment_blob, currency, vault_blob, vault_currency
394 ) VALUES (?, ?, ?, ?, ?, 0)
395 `).run(
396 characterId,
397 JSON.stringify(slots),
398 JSON.stringify(emptyEquipment()),
399 STARTER_CURRENCY,
400 JSON.stringify(emptySlots())
401 );
402 return this.getBag(characterId);
403 }
404
405 getBag (characterId)
406 {
407 const row = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
408 if (row)
409 { return parseBag(row, characterId); }
410 if (this.getCharacter(characterId))
411 { return this.#ensureBag(characterId); }
412 return null;
413 }
414
415 saveBag (characterId, bag)
416 {
417 this.#ensureBag(characterId);
418 this.db.prepare(`
419 UPDATE character_bags
420 SET inventory_blob = ?, equipment_blob = ?, currency = ?, vault_blob = ?, vault_currency = ?
421 WHERE character_id = ?
422 `).run(
423 JSON.stringify(bag.inventory ?? emptySlots()),
424 JSON.stringify(bag.equipment ?? emptyEquipment()),
425 bag.currency ?? 0,
426 JSON.stringify(bag.vault ?? emptySlots()),
427 bag.vaultCurrency ?? 0,
428 characterId
429 );
430 }
431
432 getInheritance (accountId)
433 {
434 const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId);
435 if (!row)
436 { return { accountId, slots: emptySlots(), currency: 0 }; }
437 return {
438 accountId,
439 slots: JSON.parse(row.slots_blob),
440 currency: row.currency
441 };
442 }
443
444 saveInheritance (accountId, inheritance)
445 {
446 this.db.prepare(`
447 INSERT INTO account_inheritance (account_id, slots_blob, currency)
448 VALUES (?, ?, ?)
449 ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = excluded.currency
450 `).run(accountId, JSON.stringify(inheritance.slots ?? emptySlots()), inheritance.currency ?? 0);
451 }
452
453 #moveGoodsToInheritance (character)
454 {
455 const bag = this.getBag(character.character_id) ?? {
456 inventory: emptySlots(),
457 equipment: emptyEquipment(),
458 currency: 0,
459 vault: emptySlots(),
460 vaultCurrency: 0
461 };
462 const inheritance = this.getInheritance(character.account_id);
463 const incoming = [
464 ...(bag.inventory ?? []),
465 ...Object.values(bag.equipment?.slots ?? {}),
466 ...(bag.vault ?? [])
467 ].filter(Boolean);
468 let slots = inheritance.slots ?? emptySlots();
469 for (const stack of incoming)
470 {
471 const def = itemDefinition(stack.itemId);
472 if (!def)
473 { continue; }
474 const result = insertStack(slots, stack, def);
475 if (result.ok)
476 { slots = result.slots; }
477 else
478 {
479 const extra = result.slots;
480 for (const leftover of extra)
481 {
482 if (leftover)
483 { slots.push(leftover); }
484 }
485 slots.push({ ...stack, quantity: result.remainder });
486 }
487 }
488 this.saveInheritance(character.account_id, {
489 slots: publicSlots(slots),
490 currency: (inheritance.currency ?? 0) + (bag.currency ?? 0) + (bag.vaultCurrency ?? 0)
491 });
492 }
493
494 nextGroundItemId ()
495 {
496 const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get();
497 return (row?.id ?? 0) + 1;
498 }
499
500 listGroundItemsInRegion (minX, minY, maxX, maxY)
501 {
502 return this.db.prepare(`
503 SELECT * FROM ground_items
504 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
505 `).all(minX, maxX, minY, maxY).map(parseGroundItem);
506 }
507
508 upsertGroundItem (item)
509 {
510 this.db.prepare(`
511 INSERT INTO ground_items (
512 ground_item_id, tile_x, tile_y, stack_blob, dropped_at, expires_at, source, dropped_by_character_id
513 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
514 ON CONFLICT(ground_item_id) DO UPDATE SET
515 tile_x = excluded.tile_x,
516 tile_y = excluded.tile_y,
517 stack_blob = excluded.stack_blob,
518 dropped_at = excluded.dropped_at,
519 expires_at = excluded.expires_at,
520 source = excluded.source,
521 dropped_by_character_id = excluded.dropped_by_character_id
522 `).run(
523 item.groundItemId,
524 item.tileX,
525 item.tileY,
526 JSON.stringify(item.stack),
527 item.droppedAt,
528 item.expiresAt,
529 item.source,
530 item.droppedByCharacterId ?? null
531 );
532 }
533
534 deleteGroundItem (groundItemId)
535 {
536 this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId);
537 }
538
539 insertCourierTransit (transit)
540 {
541 const now = transit.updatedAt ?? Date.now();
542 const result = this.db.prepare(`
543 INSERT INTO courier_transits (
544 sender_character_id, destination_kind, destination_character_id, cargo_blob,
545 origin_x, origin_y, dest_x, dest_y, traveled, status, entity_id, updated_at, created_at
546 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
547 `).run(
548 transit.senderCharacterId,
549 transit.destinationKind,
550 transit.destinationCharacterId ?? null,
551 JSON.stringify(transit.cargo),
552 transit.originX,
553 transit.originY,
554 transit.destX,
555 transit.destY,
556 transit.traveled ?? 0,
557 transit.status ?? "in_transit",
558 transit.entityId ?? null,
559 now,
560 transit.createdAt ?? now
561 );
562 return this.getCourierTransit(Number(result.lastInsertRowid));
563 }
564
565 getCourierTransit (courierId)
566 {
567 const row = this.db.prepare("SELECT * FROM courier_transits WHERE courier_id = ?").get(courierId);
568 return row ? parseCourierTransit(row) : null;
569 }
570
571 listActiveCourierTransits ()
572 {
573 return this.db.prepare(`
574 SELECT * FROM courier_transits WHERE status = 'in_transit' ORDER BY courier_id ASC
575 `).all().map(parseCourierTransit);
576 }
577
578 updateCourierTransit (transit)
579 {
580 this.db.prepare(`
581 UPDATE courier_transits
582 SET traveled = ?, status = ?, entity_id = ?, updated_at = ?,
583 origin_x = ?, origin_y = ?, dest_x = ?, dest_y = ?,
584 destination_kind = ?, destination_character_id = ?
585 WHERE courier_id = ?
586 `).run(
587 transit.traveled ?? 0,
588 transit.status,
589 transit.entityId ?? null,
590 transit.updatedAt ?? Date.now(),
591 transit.originX,
592 transit.originY,
593 transit.destX,
594 transit.destY,
595 transit.destinationKind,
596 transit.destinationCharacterId ?? null,
597 transit.courierId
598 );
599 }
600
601 close ()
602 {
603 this.db.close();
604 }
605}
606
607export function normalizeName (value)
608{
609 return String(value ?? "").trim().toLocaleLowerCase();
610}
611
612function pairIds (left, right)
613{
614 return left < right ? [left, right] : [right, left];
615}
616
617function parseGroundItem (row)
618{
619 return {
620 groundItemId: row.ground_item_id,
621 tileX: row.tile_x,
622 tileY: row.tile_y,
623 stack: JSON.parse(row.stack_blob),
624 droppedAt: row.dropped_at,
625 expiresAt: row.expires_at,
626 source: row.source,
627 droppedByCharacterId: row.dropped_by_character_id
628 };
629}
630
631function parseBag (row, characterId)
632{
633 return {
634 characterId,
635 inventory: JSON.parse(row.inventory_blob),
636 equipment: JSON.parse(row.equipment_blob),
637 currency: row.currency,
638 vault: JSON.parse(row.vault_blob),
639 vaultCurrency: row.vault_currency
640 };
641}
642
643function parseCourierTransit (row)
644{
645 return {
646 courierId: row.courier_id,
647 senderCharacterId: row.sender_character_id,
648 destinationKind: row.destination_kind,
649 destinationCharacterId: row.destination_character_id,
650 cargo: JSON.parse(row.cargo_blob),
651 originX: row.origin_x,
652 originY: row.origin_y,
653 destX: row.dest_x,
654 destY: row.dest_y,
655 traveled: row.traveled,
656 status: row.status,
657 entityId: row.entity_id,
658 updatedAt: row.updated_at,
659 createdAt: row.created_at
660 };
661}