BlueGit

timfa questward

Files

questward / server / src / persistence / worldDb.js

worldDb.js

worldDb.js 8.3 KiB Download Raw
1import { DEFAULT_WORLD_SETTINGS } from "../../../shared/protocol/constants.js";
2import { normalizeStarterLoadout } from "../../../shared/items/starterLoadout.js";
3import { openDatabase, withTransaction } from "./database.js";
4
5const WORLD_SCHEMA = `
6CREATE TABLE IF NOT EXISTS world_revisions (
7 revision_id INTEGER PRIMARY KEY AUTOINCREMENT,
8 created_at INTEGER NOT NULL,
9 created_by TEXT NOT NULL,
10 status TEXT NOT NULL
11);
12
13CREATE TABLE IF NOT EXISTS world_chunk_versions (
14 chunk_x INTEGER NOT NULL,
15 chunk_y INTEGER NOT NULL,
16 revision_id INTEGER NOT NULL,
17 open INTEGER NOT NULL,
18 data_blob TEXT NOT NULL,
19 PRIMARY KEY (chunk_x, chunk_y, revision_id)
20);
21
22CREATE TABLE IF NOT EXISTS world_meta (
23 key TEXT PRIMARY KEY,
24 value INTEGER
25);
26
27CREATE TABLE IF NOT EXISTS world_settings (
28 revision_id INTEGER PRIMARY KEY,
29 settings_blob TEXT NOT NULL
30);
31
32CREATE TABLE IF NOT EXISTS world_chunk_edit_log (
33 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
34 chunk_x INTEGER NOT NULL,
35 chunk_y INTEGER NOT NULL,
36 revision_id INTEGER NOT NULL,
37 editor_admin_id TEXT NOT NULL,
38 edited_at INTEGER NOT NULL,
39 diff_entries TEXT NOT NULL
40);
41
42CREATE TABLE IF NOT EXISTS world_live_settings (
43 id INTEGER PRIMARY KEY CHECK (id = 1),
44 settings_blob TEXT NOT NULL,
45 updated_at INTEGER NOT NULL,
46 updated_by TEXT NOT NULL
47);
48
49CREATE TABLE IF NOT EXISTS world_settings_edit_log (
50 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
51 editor_admin_id TEXT NOT NULL,
52 edited_at INTEGER NOT NULL,
53 diff_entries TEXT NOT NULL
54);
55`;
56
57export class WorldDatabase
58{
59 constructor (filePath)
60 {
61 this.db = openDatabase(filePath);
62 this.db.exec(WORLD_SCHEMA);
63 this.#ensureInitialRevision();
64 this.#ensureLiveSettings();
65 }
66
67 #ensureInitialRevision ()
68 {
69 const meta = this.db.prepare("SELECT key, value FROM world_meta").all();
70 if (meta.length > 0)
71 { return; }
72
73 withTransaction(this.db, () =>
74 {
75 this.db.prepare(
76 "INSERT INTO world_revisions (created_at, created_by, status) VALUES (?, ?, ?)"
77 ).run(Date.now(), "system", "active");
78 const revisionId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
79 this.db.prepare("INSERT INTO world_meta (key, value) VALUES ('active_revision', ?)").run(revisionId);
80 this.db.exec("INSERT INTO world_meta (key, value) VALUES ('pending_revision', NULL)");
81 this.db.prepare("INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?)").run(
82 revisionId,
83 JSON.stringify(DEFAULT_WORLD_SETTINGS)
84 );
85 });
86 }
87
88 #ensureLiveSettings ()
89 {
90 const existing = this.db.prepare("SELECT id FROM world_live_settings WHERE id = 1").get();
91 if (existing)
92 { return; }
93
94 const settings = this.getSettings(this.getActiveRevisionId());
95 this.db.prepare(
96 "INSERT INTO world_live_settings (id, settings_blob, updated_at, updated_by) VALUES (1, ?, ?, ?)"
97 ).run(JSON.stringify(settings), Date.now(), "system");
98 }
99
100 getLiveSettings ()
101 {
102 this.#ensureLiveSettings();
103 const row = this.db.prepare("SELECT settings_blob FROM world_live_settings WHERE id = 1").get();
104 const merged = row
105 ? { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(row.settings_blob) }
106 : { ...DEFAULT_WORLD_SETTINGS };
107 const loadout = normalizeStarterLoadout(merged);
108 merged.starterEquipment = loadout.equipment;
109 merged.starterInventory = loadout.inventory;
110 return merged;
111 }
112
113 saveLiveSettings (settings, updatedBy)
114 {
115 this.db.prepare(`
116 INSERT INTO world_live_settings (id, settings_blob, updated_at, updated_by)
117 VALUES (1, ?, ?, ?)
118 ON CONFLICT(id) DO UPDATE SET
119 settings_blob = excluded.settings_blob,
120 updated_at = excluded.updated_at,
121 updated_by = excluded.updated_by
122 `).run(JSON.stringify(settings), Date.now(), updatedBy);
123 }
124
125 appendSettingsChangelog (editorAdminId, diffEntries)
126 {
127 this.db.prepare(`
128 INSERT INTO world_settings_edit_log (editor_admin_id, edited_at, diff_entries)
129 VALUES (?, ?, ?)
130 `).run(editorAdminId, Date.now(), JSON.stringify(diffEntries));
131 }
132
133 listSettingsChangelog ()
134 {
135 return this.db.prepare(`
136 SELECT log_id, editor_admin_id, edited_at, diff_entries
137 FROM world_settings_edit_log
138 ORDER BY edited_at ASC, log_id ASC
139 `).all().map((row) => ({
140 ...row,
141 diffEntries: JSON.parse(row.diff_entries)
142 }));
143 }
144
145 getActiveRevisionId ()
146 {
147 return this.db.prepare("SELECT value FROM world_meta WHERE key = 'active_revision'").get()?.value ?? null;
148 }
149
150 getPendingRevisionId ()
151 {
152 return this.db.prepare("SELECT value FROM world_meta WHERE key = 'pending_revision'").get()?.value ?? null;
153 }
154
155 getEditorRevisionId ()
156 {
157 return this.getPendingRevisionId() ?? this.getActiveRevisionId();
158 }
159
160 getSettings (revisionId)
161 {
162 const row = this.db.prepare("SELECT settings_blob FROM world_settings WHERE revision_id = ?").get(revisionId);
163 if (row)
164 { return { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(row.settings_blob) }; }
165
166 const fallback = this.db.prepare(
167 "SELECT settings_blob FROM world_settings WHERE revision_id <= ? ORDER BY revision_id DESC LIMIT 1"
168 ).get(revisionId);
169 return fallback
170 ? { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(fallback.settings_blob) }
171 : { ...DEFAULT_WORLD_SETTINGS };
172 }
173
174 getChunkVersion (chunkX, chunkY, revisionId)
175 {
176 return this.db.prepare(`
177 SELECT chunk_x, chunk_y, revision_id, open, data_blob
178 FROM world_chunk_versions
179 WHERE chunk_x = ? AND chunk_y = ? AND revision_id <= ?
180 ORDER BY revision_id DESC
181 LIMIT 1
182 `).get(chunkX, chunkY, revisionId) ?? null;
183 }
184
185 listChunkCoords (revisionId)
186 {
187 return this.db.prepare(`
188 SELECT chunk_x, chunk_y, MAX(revision_id) AS revision_id
189 FROM world_chunk_versions
190 WHERE revision_id <= ?
191 GROUP BY chunk_x, chunk_y
192 `).all(revisionId);
193 }
194
195 ensurePendingRevision (createdBy)
196 {
197 const existing = this.getPendingRevisionId();
198 if (existing)
199 { return existing; }
200
201 return withTransaction(this.db, () =>
202 {
203 const activeId = this.getActiveRevisionId();
204 this.db.prepare(
205 "INSERT INTO world_revisions (created_at, created_by, status) VALUES (?, ?, ?)"
206 ).run(Date.now(), createdBy, "pending");
207 const revisionId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
208 this.db.prepare("UPDATE world_meta SET value = ? WHERE key = 'pending_revision'").run(revisionId);
209
210 const settings = this.getSettings(activeId);
211 this.db.prepare("INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?)").run(
212 revisionId,
213 JSON.stringify(settings)
214 );
215 return revisionId;
216 });
217 }
218
219 saveChunkVersion (chunkX, chunkY, revisionId, open, dataBlob)
220 {
221 this.db.prepare(`
222 INSERT INTO world_chunk_versions (chunk_x, chunk_y, revision_id, open, data_blob)
223 VALUES (?, ?, ?, ?, ?)
224 ON CONFLICT(chunk_x, chunk_y, revision_id) DO UPDATE SET
225 open = excluded.open,
226 data_blob = excluded.data_blob
227 `).run(chunkX, chunkY, revisionId, open ? 1 : 0, dataBlob);
228 }
229
230 saveSettings (revisionId, settings)
231 {
232 this.db.prepare(`
233 INSERT INTO world_settings (revision_id, settings_blob)
234 VALUES (?, ?)
235 ON CONFLICT(revision_id) DO UPDATE SET settings_blob = excluded.settings_blob
236 `).run(revisionId, JSON.stringify(settings));
237 }
238
239 appendChangelog (chunkX, chunkY, revisionId, editorAdminId, diffEntries)
240 {
241 this.db.prepare(`
242 INSERT INTO world_chunk_edit_log
243 (chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries)
244 VALUES (?, ?, ?, ?, ?, ?)
245 `).run(chunkX, chunkY, revisionId, editorAdminId, Date.now(), JSON.stringify(diffEntries));
246 }
247
248 listChangelog (chunkX, chunkY)
249 {
250 return this.db.prepare(`
251 SELECT log_id, chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries
252 FROM world_chunk_edit_log
253 WHERE chunk_x = ? AND chunk_y = ?
254 ORDER BY edited_at ASC, log_id ASC
255 `).all(chunkX, chunkY).map((row) => ({
256 ...row,
257 diffEntries: JSON.parse(row.diff_entries)
258 }));
259 }
260
261 promotePendingRevision ()
262 {
263 const pendingId = this.getPendingRevisionId();
264 if (!pendingId)
265 { return this.getActiveRevisionId(); }
266
267 return withTransaction(this.db, () =>
268 {
269 const activeId = this.getActiveRevisionId();
270 this.db.prepare("UPDATE world_revisions SET status = 'superseded' WHERE revision_id = ?").run(activeId);
271 this.db.prepare("UPDATE world_revisions SET status = 'active' WHERE revision_id = ?").run(pendingId);
272 this.db.prepare("UPDATE world_meta SET value = ? WHERE key = 'active_revision'").run(pendingId);
273 this.db.exec("UPDATE world_meta SET value = NULL WHERE key = 'pending_revision'");
274 return pendingId;
275 });
276 }
277
278 close ()
279 {
280 this.db.close();
281 }
282}