BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

stateDb.js 4.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);
29`;
30
31export class StateDatabase
32{
33 constructor (filePath)
34 {
35 this.db = openDatabase(filePath);
36 this.db.exec(STATE_SCHEMA);
37 }
38
39 getAccountBySubject (authSubject)
40 {
41 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
42 }
43
44 createAccount (authSubject, isAdmin, email = null)
45 {
46 this.db.prepare("INSERT INTO accounts (auth_subject, email, is_admin) VALUES (?, ?, ?)").run(
47 authSubject,
48 email,
49 isAdmin ? 1 : 0
50 );
51 return this.getAccountBySubject(authSubject);
52 }
53
54 ensureAccount (authSubject, isAdmin, email = null)
55 {
56 const existing = this.getAccountBySubject(authSubject);
57 if (existing)
58 { return existing; }
59
60 return this.createAccount(authSubject, isAdmin, email);
61 }
62
63 listCharacters (accountId)
64 {
65 return this.db.prepare(`
66 SELECT * FROM characters
67 WHERE account_id = ?
68 ORDER BY character_id ASC
69 `).all(accountId);
70 }
71
72 getCharacter (characterId)
73 {
74 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
75 }
76
77 findSurnameClaim (surnameNormalized)
78 {
79 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
80 }
81
82 createCharacter (accountId, firstName, lastName, sex, tileX, tileY)
83 {
84 const firstNormalized = normalizeName(firstName);
85 const lastNormalized = normalizeName(lastName);
86 this.db.exec("BEGIN");
87 try
88 {
89 this.db.prepare(`
90 INSERT INTO characters (
91 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y
92 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
93 `).run(accountId, firstName, lastName, firstNormalized, lastNormalized, sex, tileX, tileY);
94 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
95
96 const claim = this.findSurnameClaim(lastNormalized);
97 if (!claim)
98 {
99 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
100 lastNormalized,
101 accountId
102 );
103 }
104
105 this.db.exec("COMMIT");
106 return this.getCharacter(characterId);
107 }
108 catch (error)
109 {
110 this.db.exec("ROLLBACK");
111 throw error;
112 }
113 }
114
115 scheduleDeletion (characterId, at)
116 {
117 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
118 }
119
120 cancelDeletion (characterId)
121 {
122 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
123 }
124
125 deleteCharacter (characterId)
126 {
127 const character = this.getCharacter(characterId);
128 if (!character)
129 { return; }
130
131 this.db.exec("BEGIN");
132 try
133 {
134 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
135 const remaining = this.db.prepare(`
136 SELECT COUNT(*) AS count FROM characters
137 WHERE account_id = ? AND last_normalized = ?
138 `).get(character.account_id, character.last_normalized);
139 if (!remaining.count)
140 {
141 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
142 character.last_normalized,
143 character.account_id
144 );
145 }
146 this.db.exec("COMMIT");
147 }
148 catch (error)
149 {
150 this.db.exec("ROLLBACK");
151 throw error;
152 }
153 }
154
155 saveCharacterPosition (characterId, tileX, tileY, gait)
156 {
157 this.db.prepare("UPDATE characters SET tile_x = ?, tile_y = ?, gait = ? WHERE character_id = ?").run(
158 tileX,
159 tileY,
160 gait,
161 characterId
162 );
163 }
164
165 listDueDeletions (now)
166 {
167 return this.db.prepare(`
168 SELECT * FROM characters
169 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
170 `).all(now);
171 }
172
173 close ()
174 {
175 this.db.close();
176 }
177}
178
179export function normalizeName (value)
180{
181 return String(value ?? "").trim().toLocaleLowerCase();
182}