BlueGit

timfa questward

Files

questward / server / src / persistence / worldDb.js

worldDb.js

feat: add sprite average colours and an editor minimap
timfa project_85689633_bot_760517ee9267c86ff1d6c3a0237abf7d · pushed by timfa · 6 weeks ago
8a24eb51 History
worldDb.js 25.2 KiB Download Raw
1import { DEFAULT_WORLD_SETTINGS } from "../../../shared/protocol/constants.js";
2import { normalizeStarterLoadout } from "../../../shared/items/starterLoadout.js";
3import { normalizeEquipmentStatBonuses } from "../../../shared/definitions/equipmentStatCategories.js";
4import { normalizeInitialQuests } from "../../../shared/quests/initialQuests.js";
5import {
6 OVERWORLD_ID,
7 DIMENSION_PURGE_MS,
8 defaultDimensionRecord,
9 normalizeDimensionRecord,
10 normalizeDimensionId,
11 isOverworld
12} from "../../../shared/world/dimensions.js";
13import { openDatabase, withTransaction } from "./database.js";
14
15const WORLD_SCHEMA = `
16CREATE TABLE IF NOT EXISTS world_revisions (
17 revision_id INTEGER PRIMARY KEY AUTOINCREMENT,
18 created_at INTEGER NOT NULL,
19 created_by TEXT NOT NULL,
20 status TEXT NOT NULL
21);
22
23CREATE TABLE IF NOT EXISTS world_chunk_versions (
24 dimension_id TEXT NOT NULL DEFAULT 'overworld',
25 chunk_x INTEGER NOT NULL,
26 chunk_y INTEGER NOT NULL,
27 revision_id INTEGER NOT NULL,
28 open INTEGER NOT NULL,
29 data_blob TEXT NOT NULL,
30 PRIMARY KEY (dimension_id, chunk_x, chunk_y, revision_id)
31);
32
33CREATE TABLE IF NOT EXISTS world_meta (
34 key TEXT PRIMARY KEY,
35 value INTEGER
36);
37
38CREATE TABLE IF NOT EXISTS world_settings (
39 revision_id INTEGER PRIMARY KEY,
40 settings_blob TEXT NOT NULL
41);
42
43CREATE TABLE IF NOT EXISTS world_chunk_edit_log (
44 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
45 dimension_id TEXT NOT NULL DEFAULT 'overworld',
46 chunk_x INTEGER NOT NULL,
47 chunk_y INTEGER NOT NULL,
48 revision_id INTEGER NOT NULL,
49 editor_admin_id TEXT NOT NULL,
50 edited_at INTEGER NOT NULL,
51 diff_entries TEXT NOT NULL
52);
53
54CREATE TABLE IF NOT EXISTS world_live_settings (
55 id INTEGER PRIMARY KEY CHECK (id = 1),
56 settings_blob TEXT NOT NULL,
57 updated_at INTEGER NOT NULL,
58 updated_by TEXT NOT NULL
59);
60
61CREATE TABLE IF NOT EXISTS world_settings_edit_log (
62 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
63 editor_admin_id TEXT NOT NULL,
64 edited_at INTEGER NOT NULL,
65 diff_entries TEXT NOT NULL
66);
67
68CREATE TABLE IF NOT EXISTS world_quest_defs (
69 revision_id INTEGER NOT NULL,
70 quest_id TEXT NOT NULL,
71 def_blob TEXT NOT NULL,
72 PRIMARY KEY (revision_id, quest_id)
73);
74
75CREATE TABLE IF NOT EXISTS world_dialogue_trees (
76 revision_id INTEGER NOT NULL,
77 dialogue_id TEXT NOT NULL,
78 tree_blob TEXT NOT NULL,
79 PRIMARY KEY (revision_id, dialogue_id)
80);
81
82CREATE TABLE IF NOT EXISTS world_quest_edit_log (
83 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
84 quest_id TEXT NOT NULL,
85 revision_id INTEGER NOT NULL,
86 editor_admin_id TEXT NOT NULL,
87 edited_at INTEGER NOT NULL,
88 diff_entries TEXT NOT NULL
89);
90
91CREATE TABLE IF NOT EXISTS world_dialogue_edit_log (
92 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
93 dialogue_id TEXT NOT NULL,
94 revision_id INTEGER NOT NULL,
95 editor_admin_id TEXT NOT NULL,
96 edited_at INTEGER NOT NULL,
97 diff_entries TEXT NOT NULL
98);
99
100CREATE TABLE IF NOT EXISTS world_dimensions (
101 revision_id INTEGER NOT NULL,
102 dimension_id TEXT NOT NULL,
103 display_name TEXT NOT NULL,
104 lighting_mode TEXT NOT NULL,
105 season_mode TEXT NOT NULL,
106 default_ground_type_id INTEGER NOT NULL,
107 deleted_at INTEGER,
108 purge_after INTEGER,
109 PRIMARY KEY (revision_id, dimension_id)
110);
111`;
112
113export class WorldDatabase
114{
115 constructor (filePath)
116 {
117 this.db = openDatabase(filePath);
118 this.db.exec(WORLD_SCHEMA);
119 this.#migrateChunkDimensionColumn();
120 this.#ensureInitialRevision();
121 this.#ensureLiveSettings();
122 this.#ensureOverworldDimension(this.getActiveRevisionId());
123 }
124
125 #migrateChunkDimensionColumn ()
126 {
127 const cols = this.db.prepare("PRAGMA table_info(world_chunk_versions)").all();
128 const hasDim = cols.some((col) => col.name === "dimension_id");
129 if (hasDim)
130 {
131 this.#migrateEditLogDimensionColumn();
132 return;
133 }
134
135 withTransaction(this.db, () =>
136 {
137 this.db.exec(`
138 CREATE TABLE world_chunk_versions_v2 (
139 dimension_id TEXT NOT NULL DEFAULT 'overworld',
140 chunk_x INTEGER NOT NULL,
141 chunk_y INTEGER NOT NULL,
142 revision_id INTEGER NOT NULL,
143 open INTEGER NOT NULL,
144 data_blob TEXT NOT NULL,
145 PRIMARY KEY (dimension_id, chunk_x, chunk_y, revision_id)
146 );
147 INSERT INTO world_chunk_versions_v2
148 (dimension_id, chunk_x, chunk_y, revision_id, open, data_blob)
149 SELECT 'overworld', chunk_x, chunk_y, revision_id, open, data_blob
150 FROM world_chunk_versions;
151 DROP TABLE world_chunk_versions;
152 ALTER TABLE world_chunk_versions_v2 RENAME TO world_chunk_versions;
153 `);
154 });
155 this.#migrateEditLogDimensionColumn();
156 }
157
158 #migrateEditLogDimensionColumn ()
159 {
160 const cols = this.db.prepare("PRAGMA table_info(world_chunk_edit_log)").all();
161 const hasDim = cols.some((col) => col.name === "dimension_id");
162 if (hasDim)
163 { return; }
164 withTransaction(this.db, () =>
165 {
166 this.db.exec(`
167 CREATE TABLE world_chunk_edit_log_v2 (
168 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
169 dimension_id TEXT NOT NULL DEFAULT 'overworld',
170 chunk_x INTEGER NOT NULL,
171 chunk_y INTEGER NOT NULL,
172 revision_id INTEGER NOT NULL,
173 editor_admin_id TEXT NOT NULL,
174 edited_at INTEGER NOT NULL,
175 diff_entries TEXT NOT NULL
176 );
177 INSERT INTO world_chunk_edit_log_v2
178 (log_id, dimension_id, chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries)
179 SELECT log_id, 'overworld', chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries
180 FROM world_chunk_edit_log;
181 DROP TABLE world_chunk_edit_log;
182 ALTER TABLE world_chunk_edit_log_v2 RENAME TO world_chunk_edit_log;
183 `);
184 });
185 }
186
187 #ensureInitialRevision ()
188 {
189 const meta = this.db.prepare("SELECT key, value FROM world_meta").all();
190 if (meta.length > 0)
191 { return; }
192
193 withTransaction(this.db, () =>
194 {
195 this.db.prepare(
196 "INSERT INTO world_revisions (created_at, created_by, status) VALUES (?, ?, ?)"
197 ).run(Date.now(), "system", "active");
198 const revisionId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
199 this.db.prepare("INSERT INTO world_meta (key, value) VALUES ('active_revision', ?)").run(revisionId);
200 this.db.exec("INSERT INTO world_meta (key, value) VALUES ('pending_revision', NULL)");
201 this.db.prepare("INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?)").run(
202 revisionId,
203 JSON.stringify(DEFAULT_WORLD_SETTINGS)
204 );
205 this.#upsertDimensionRow(revisionId, defaultDimensionRecord(OVERWORLD_ID, {
206 defaultGroundTypeId: DEFAULT_WORLD_SETTINGS.defaultGroundTypeId
207 }));
208 });
209 }
210
211 #ensureOverworldDimension (revisionId)
212 {
213 if (revisionId == null)
214 { return; }
215 const existing = this.getDimension(revisionId, OVERWORLD_ID);
216 if (existing)
217 { return; }
218 const settings = this.getSettings(revisionId);
219 this.#upsertDimensionRow(revisionId, defaultDimensionRecord(OVERWORLD_ID, {
220 defaultGroundTypeId: settings.defaultGroundTypeId
221 }));
222 }
223
224 #upsertDimensionRow (revisionId, record)
225 {
226 const dim = normalizeDimensionRecord(record);
227 this.db.prepare(`
228 INSERT INTO world_dimensions (
229 revision_id, dimension_id, display_name, lighting_mode, season_mode,
230 default_ground_type_id, deleted_at, purge_after
231 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
232 ON CONFLICT(revision_id, dimension_id) DO UPDATE SET
233 display_name = excluded.display_name,
234 lighting_mode = excluded.lighting_mode,
235 season_mode = excluded.season_mode,
236 default_ground_type_id = excluded.default_ground_type_id,
237 deleted_at = excluded.deleted_at,
238 purge_after = excluded.purge_after
239 `).run(
240 revisionId,
241 dim.dimensionId,
242 dim.displayName,
243 dim.lightingMode,
244 dim.seasonMode,
245 dim.defaultGroundTypeId,
246 dim.deletedAt,
247 dim.purgeAfter
248 );
249 }
250
251 #rowToDimension (row)
252 {
253 if (!row)
254 { return null; }
255 return normalizeDimensionRecord({
256 dimensionId: row.dimension_id,
257 displayName: row.display_name,
258 lightingMode: row.lighting_mode,
259 seasonMode: row.season_mode,
260 defaultGroundTypeId: row.default_ground_type_id,
261 deletedAt: row.deleted_at,
262 purgeAfter: row.purge_after
263 });
264 }
265
266 #ensureLiveSettings ()
267 {
268 const existing = this.db.prepare("SELECT id FROM world_live_settings WHERE id = 1").get();
269 if (existing)
270 { return; }
271
272 const settings = this.getSettings(this.getActiveRevisionId());
273 this.db.prepare(
274 "INSERT INTO world_live_settings (id, settings_blob, updated_at, updated_by) VALUES (1, ?, ?, ?)"
275 ).run(JSON.stringify(settings), Date.now(), "system");
276 }
277
278 getLiveSettings ()
279 {
280 this.#ensureLiveSettings();
281 const row = this.db.prepare("SELECT settings_blob FROM world_live_settings WHERE id = 1").get();
282 const merged = row
283 ? { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(row.settings_blob) }
284 : { ...DEFAULT_WORLD_SETTINGS };
285 const loadout = normalizeStarterLoadout(merged);
286 merged.starterEquipment = loadout.equipment;
287 merged.starterInventory = loadout.inventory;
288 merged.equipmentStatBonuses = normalizeEquipmentStatBonuses(merged.equipmentStatBonuses);
289 merged.initialQuests = normalizeInitialQuests(merged.initialQuests);
290 return merged;
291 }
292
293 saveLiveSettings (settings, updatedBy)
294 {
295 this.db.prepare(`
296 INSERT INTO world_live_settings (id, settings_blob, updated_at, updated_by)
297 VALUES (1, ?, ?, ?)
298 ON CONFLICT(id) DO UPDATE SET
299 settings_blob = excluded.settings_blob,
300 updated_at = excluded.updated_at,
301 updated_by = excluded.updated_by
302 `).run(JSON.stringify(settings), Date.now(), updatedBy);
303 }
304
305 appendSettingsChangelog (editorAdminId, diffEntries)
306 {
307 this.db.prepare(`
308 INSERT INTO world_settings_edit_log (editor_admin_id, edited_at, diff_entries)
309 VALUES (?, ?, ?)
310 `).run(editorAdminId, Date.now(), JSON.stringify(diffEntries));
311 }
312
313 listSettingsChangelog ()
314 {
315 return this.db.prepare(`
316 SELECT log_id, editor_admin_id, edited_at, diff_entries
317 FROM world_settings_edit_log
318 ORDER BY edited_at ASC, log_id ASC
319 `).all().map((row) => ({
320 ...row,
321 diffEntries: JSON.parse(row.diff_entries)
322 }));
323 }
324
325 appendQuestChangelog (questId, revisionId, editorAdminId, diffEntries)
326 {
327 this.db.prepare(`
328 INSERT INTO world_quest_edit_log
329 (quest_id, revision_id, editor_admin_id, edited_at, diff_entries)
330 VALUES (?, ?, ?, ?, ?)
331 `).run(String(questId), revisionId, editorAdminId, Date.now(), JSON.stringify(diffEntries));
332 }
333
334 listQuestChangelog (questId = null)
335 {
336 if (questId != null)
337 {
338 return this.db.prepare(`
339 SELECT log_id, quest_id, revision_id, editor_admin_id, edited_at, diff_entries
340 FROM world_quest_edit_log
341 WHERE quest_id = ?
342 ORDER BY edited_at ASC, log_id ASC
343 `).all(String(questId)).map(mapQuestLogRow);
344 }
345 return this.db.prepare(`
346 SELECT log_id, quest_id, revision_id, editor_admin_id, edited_at, diff_entries
347 FROM world_quest_edit_log
348 ORDER BY edited_at ASC, log_id ASC
349 `).all().map(mapQuestLogRow);
350 }
351
352 appendDialogueChangelog (dialogueId, revisionId, editorAdminId, diffEntries)
353 {
354 this.db.prepare(`
355 INSERT INTO world_dialogue_edit_log
356 (dialogue_id, revision_id, editor_admin_id, edited_at, diff_entries)
357 VALUES (?, ?, ?, ?, ?)
358 `).run(String(dialogueId), revisionId, editorAdminId, Date.now(), JSON.stringify(diffEntries));
359 }
360
361 listDialogueChangelog (dialogueId = null)
362 {
363 if (dialogueId != null)
364 {
365 return this.db.prepare(`
366 SELECT log_id, dialogue_id, revision_id, editor_admin_id, edited_at, diff_entries
367 FROM world_dialogue_edit_log
368 WHERE dialogue_id = ?
369 ORDER BY edited_at ASC, log_id ASC
370 `).all(String(dialogueId)).map(mapDialogueLogRow);
371 }
372 return this.db.prepare(`
373 SELECT log_id, dialogue_id, revision_id, editor_admin_id, edited_at, diff_entries
374 FROM world_dialogue_edit_log
375 ORDER BY edited_at ASC, log_id ASC
376 `).all().map(mapDialogueLogRow);
377 }
378
379 /**
380 * Recent edits across chunks, settings, quests, and dialogues (newest first).
381 * @param {number} [limit]
382 */
383 listGlobalChangelog (limit = 250)
384 {
385 const cap = Math.max(1, Math.min(1000, limit | 0 || 250));
386 const chunkRows = this.db.prepare(`
387 SELECT log_id, dimension_id, chunk_x, chunk_y, editor_admin_id, edited_at, diff_entries
388 FROM world_chunk_edit_log
389 ORDER BY edited_at DESC, log_id DESC
390 LIMIT ?
391 `).all(cap).map((row) => ({
392 kind: "chunk",
393 scope: `${row.dimension_id} ${row.chunk_x},${row.chunk_y}`,
394 editor_admin_id: row.editor_admin_id,
395 edited_at: row.edited_at,
396 diffEntries: JSON.parse(row.diff_entries),
397 _key: row.log_id
398 }));
399 const settingsRows = this.db.prepare(`
400 SELECT log_id, editor_admin_id, edited_at, diff_entries
401 FROM world_settings_edit_log
402 ORDER BY edited_at DESC, log_id DESC
403 LIMIT ?
404 `).all(cap).map((row) => ({
405 kind: "settings",
406 scope: "settings",
407 editor_admin_id: row.editor_admin_id,
408 edited_at: row.edited_at,
409 diffEntries: JSON.parse(row.diff_entries),
410 _key: row.log_id
411 }));
412 const questRows = this.db.prepare(`
413 SELECT log_id, quest_id, editor_admin_id, edited_at, diff_entries
414 FROM world_quest_edit_log
415 ORDER BY edited_at DESC, log_id DESC
416 LIMIT ?
417 `).all(cap).map((row) => ({
418 kind: "quest",
419 scope: row.quest_id,
420 editor_admin_id: row.editor_admin_id,
421 edited_at: row.edited_at,
422 diffEntries: JSON.parse(row.diff_entries),
423 _key: row.log_id
424 }));
425 const dialogueRows = this.db.prepare(`
426 SELECT log_id, dialogue_id, editor_admin_id, edited_at, diff_entries
427 FROM world_dialogue_edit_log
428 ORDER BY edited_at DESC, log_id DESC
429 LIMIT ?
430 `).all(cap).map((row) => ({
431 kind: "dialogue",
432 scope: row.dialogue_id,
433 editor_admin_id: row.editor_admin_id,
434 edited_at: row.edited_at,
435 diffEntries: JSON.parse(row.diff_entries),
436 _key: row.log_id
437 }));
438 return [...chunkRows, ...settingsRows, ...questRows, ...dialogueRows]
439 .sort((a, b) => (b.edited_at - a.edited_at) || (b._key - a._key))
440 .slice(0, cap)
441 .map(({ _key, ...entry }) => entry);
442 }
443
444 getActiveRevisionId ()
445 {
446 return this.db.prepare("SELECT value FROM world_meta WHERE key = 'active_revision'").get()?.value ?? null;
447 }
448
449 getPendingRevisionId ()
450 {
451 return this.db.prepare("SELECT value FROM world_meta WHERE key = 'pending_revision'").get()?.value ?? null;
452 }
453
454 getEditorRevisionId ()
455 {
456 return this.getPendingRevisionId() ?? this.getActiveRevisionId();
457 }
458
459 getSettings (revisionId)
460 {
461 const row = this.db.prepare("SELECT settings_blob FROM world_settings WHERE revision_id = ?").get(revisionId);
462 if (row)
463 { return { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(row.settings_blob) }; }
464
465 const fallback = this.db.prepare(
466 "SELECT settings_blob FROM world_settings WHERE revision_id <= ? ORDER BY revision_id DESC LIMIT 1"
467 ).get(revisionId);
468 return fallback
469 ? { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(fallback.settings_blob) }
470 : { ...DEFAULT_WORLD_SETTINGS };
471 }
472
473 listDimensions (revisionId)
474 {
475 this.#ensureOverworldDimension(revisionId);
476 return this.db.prepare(`
477 SELECT dimension_id, display_name, lighting_mode, season_mode,
478 default_ground_type_id, deleted_at, purge_after
479 FROM world_dimensions
480 WHERE revision_id = ?
481 ORDER BY dimension_id ASC
482 `).all(revisionId).map((row) => this.#rowToDimension(row));
483 }
484
485 getDimension (revisionId, dimensionId)
486 {
487 const id = normalizeDimensionId(dimensionId);
488 const row = this.db.prepare(`
489 SELECT dimension_id, display_name, lighting_mode, season_mode,
490 default_ground_type_id, deleted_at, purge_after
491 FROM world_dimensions
492 WHERE revision_id = ? AND dimension_id = ?
493 `).get(revisionId, id);
494 return this.#rowToDimension(row);
495 }
496
497 /** Latest dimension row at or before revision (for live fallback). */
498 getDimensionAtOrBefore (revisionId, dimensionId)
499 {
500 const id = normalizeDimensionId(dimensionId);
501 const row = this.db.prepare(`
502 SELECT dimension_id, display_name, lighting_mode, season_mode,
503 default_ground_type_id, deleted_at, purge_after
504 FROM world_dimensions
505 WHERE dimension_id = ? AND revision_id <= ?
506 ORDER BY revision_id DESC
507 LIMIT 1
508 `).get(id, revisionId);
509 return this.#rowToDimension(row);
510 }
511
512 saveDimension (revisionId, record)
513 {
514 const dim = normalizeDimensionRecord(record);
515 if (isOverworld(dim.dimensionId))
516 {
517 dim.deletedAt = null;
518 dim.purgeAfter = null;
519 }
520 this.#upsertDimensionRow(revisionId, dim);
521 return dim;
522 }
523
524 softDeleteDimension (revisionId, dimensionId, now = Date.now())
525 {
526 const id = normalizeDimensionId(dimensionId);
527 if (isOverworld(id))
528 { return { ok: false, error: "Cannot delete overworld." }; }
529 const existing = this.getDimension(revisionId, id) ?? defaultDimensionRecord(id);
530 existing.deletedAt = now;
531 existing.purgeAfter = now + DIMENSION_PURGE_MS;
532 this.#upsertDimensionRow(revisionId, existing);
533 return { ok: true, dimension: existing };
534 }
535
536 cancelDimensionDeletion (revisionId, dimensionId)
537 {
538 const id = normalizeDimensionId(dimensionId);
539 const existing = this.getDimension(revisionId, id);
540 if (!existing)
541 { return { ok: false, error: "Unknown dimension." }; }
542 existing.deletedAt = null;
543 existing.purgeAfter = null;
544 this.#upsertDimensionRow(revisionId, existing);
545 return { ok: true, dimension: existing };
546 }
547
548 /** Permanently remove dimensions past purge_after and all their chunk versions. */
549 purgeExpiredDimensions (now = Date.now())
550 {
551 const rows = this.db.prepare(`
552 SELECT DISTINCT dimension_id FROM world_dimensions
553 WHERE purge_after IS NOT NULL AND purge_after <= ? AND dimension_id != ?
554 `).all(now, OVERWORLD_ID);
555 let purged = 0;
556 const purgedIds = [];
557 for (const row of rows)
558 {
559 const id = row.dimension_id;
560 withTransaction(this.db, () =>
561 {
562 this.db.prepare("DELETE FROM world_chunk_versions WHERE dimension_id = ?").run(id);
563 this.db.prepare("DELETE FROM world_chunk_edit_log WHERE dimension_id = ?").run(id);
564 this.db.prepare("DELETE FROM world_dimensions WHERE dimension_id = ?").run(id);
565 });
566 purgedIds.push(id);
567 purged += 1;
568 }
569 if (purgedIds.length > 0 && typeof this.onDimensionsPurged === "function")
570 {
571 try
572 { this.onDimensionsPurged(purgedIds); }
573 catch (error)
574 { console.error("onDimensionsPurged failed:", error?.message ?? error); }
575 }
576 return purged;
577 }
578
579 hasChunkVersion (chunkX, chunkY, revisionId, dimensionId = OVERWORLD_ID)
580 {
581 const dim = normalizeDimensionId(dimensionId);
582 const row = this.db.prepare(`
583 SELECT revision_id
584 FROM world_chunk_versions
585 WHERE dimension_id = ? AND chunk_x = ? AND chunk_y = ? AND revision_id <= ?
586 ORDER BY revision_id DESC
587 LIMIT 1
588 `).get(dim, chunkX, chunkY, revisionId);
589 return !!row;
590 }
591
592 getChunkVersion (chunkX, chunkY, revisionId, dimensionId = OVERWORLD_ID)
593 {
594 const dim = normalizeDimensionId(dimensionId);
595 return this.db.prepare(`
596 SELECT dimension_id, chunk_x, chunk_y, revision_id, open, data_blob
597 FROM world_chunk_versions
598 WHERE dimension_id = ? AND chunk_x = ? AND chunk_y = ? AND revision_id <= ?
599 ORDER BY revision_id DESC
600 LIMIT 1
601 `).get(dim, chunkX, chunkY, revisionId) ?? null;
602 }
603
604 listChunkCoords (revisionId, dimensionId = null)
605 {
606 if (dimensionId != null)
607 {
608 const dim = normalizeDimensionId(dimensionId);
609 return this.db.prepare(`
610 SELECT dimension_id, chunk_x, chunk_y, MAX(revision_id) AS revision_id
611 FROM world_chunk_versions
612 WHERE revision_id <= ? AND dimension_id = ?
613 GROUP BY dimension_id, chunk_x, chunk_y
614 `).all(revisionId, dim);
615 }
616 return this.db.prepare(`
617 SELECT dimension_id, chunk_x, chunk_y, MAX(revision_id) AS revision_id
618 FROM world_chunk_versions
619 WHERE revision_id <= ?
620 GROUP BY dimension_id, chunk_x, chunk_y
621 `).all(revisionId);
622 }
623
624 ensurePendingRevision (createdBy)
625 {
626 const existing = this.getPendingRevisionId();
627 if (existing)
628 { return existing; }
629
630 return withTransaction(this.db, () =>
631 {
632 const activeId = this.getActiveRevisionId();
633 this.db.prepare(
634 "INSERT INTO world_revisions (created_at, created_by, status) VALUES (?, ?, ?)"
635 ).run(Date.now(), createdBy, "pending");
636 const revisionId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
637 this.db.prepare("UPDATE world_meta SET value = ? WHERE key = 'pending_revision'").run(revisionId);
638
639 const settings = this.getSettings(activeId);
640 this.db.prepare("INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?)").run(
641 revisionId,
642 JSON.stringify(settings)
643 );
644 this.#copyQuestDefs(activeId, revisionId);
645 this.#copyDialogueTrees(activeId, revisionId);
646 this.#copyDimensions(activeId, revisionId);
647 return revisionId;
648 });
649 }
650
651 #copyDimensions (fromRevisionId, toRevisionId)
652 {
653 const rows = this.listDimensions(fromRevisionId);
654 for (const dim of rows)
655 { this.#upsertDimensionRow(toRevisionId, dim); }
656 this.#ensureOverworldDimension(toRevisionId);
657 }
658
659 #copyQuestDefs (fromRevisionId, toRevisionId)
660 {
661 const rows = this.listQuestDefs(fromRevisionId);
662 const insert = this.db.prepare(`
663 INSERT INTO world_quest_defs (revision_id, quest_id, def_blob)
664 VALUES (?, ?, ?)
665 `);
666 for (const def of rows)
667 { insert.run(toRevisionId, def.questId, JSON.stringify(def)); }
668 }
669
670 #copyDialogueTrees (fromRevisionId, toRevisionId)
671 {
672 const rows = this.listDialogueTrees(fromRevisionId);
673 const insert = this.db.prepare(`
674 INSERT INTO world_dialogue_trees (revision_id, dialogue_id, tree_blob)
675 VALUES (?, ?, ?)
676 `);
677 for (const tree of rows)
678 { insert.run(toRevisionId, tree.dialogueId, JSON.stringify(tree)); }
679 }
680
681 listQuestDefs (revisionId)
682 {
683 return this.db.prepare(`
684 SELECT quest_id, def_blob FROM world_quest_defs WHERE revision_id = ?
685 `).all(revisionId).map((row) => JSON.parse(row.def_blob));
686 }
687
688 getQuestDef (revisionId, questId)
689 {
690 const row = this.db.prepare(`
691 SELECT def_blob FROM world_quest_defs WHERE revision_id = ? AND quest_id = ?
692 `).get(revisionId, questId);
693 return row ? JSON.parse(row.def_blob) : null;
694 }
695
696 saveQuestDef (revisionId, def)
697 {
698 this.db.prepare(`
699 INSERT INTO world_quest_defs (revision_id, quest_id, def_blob)
700 VALUES (?, ?, ?)
701 ON CONFLICT(revision_id, quest_id) DO UPDATE SET def_blob = excluded.def_blob
702 `).run(revisionId, def.questId, JSON.stringify(def));
703 }
704
705 deleteQuestDef (revisionId, questId)
706 {
707 this.db.prepare(`
708 DELETE FROM world_quest_defs WHERE revision_id = ? AND quest_id = ?
709 `).run(revisionId, questId);
710 }
711
712 listDialogueTrees (revisionId)
713 {
714 return this.db.prepare(`
715 SELECT dialogue_id, tree_blob FROM world_dialogue_trees WHERE revision_id = ?
716 `).all(revisionId).map((row) => JSON.parse(row.tree_blob));
717 }
718
719 getDialogueTree (revisionId, dialogueId)
720 {
721 const row = this.db.prepare(`
722 SELECT tree_blob FROM world_dialogue_trees WHERE revision_id = ? AND dialogue_id = ?
723 `).get(revisionId, dialogueId);
724 return row ? JSON.parse(row.tree_blob) : null;
725 }
726
727 saveDialogueTree (revisionId, tree)
728 {
729 this.db.prepare(`
730 INSERT INTO world_dialogue_trees (revision_id, dialogue_id, tree_blob)
731 VALUES (?, ?, ?)
732 ON CONFLICT(revision_id, dialogue_id) DO UPDATE SET tree_blob = excluded.tree_blob
733 `).run(revisionId, tree.dialogueId, JSON.stringify(tree));
734 }
735
736 deleteDialogueTree (revisionId, dialogueId)
737 {
738 this.db.prepare(`
739 DELETE FROM world_dialogue_trees WHERE revision_id = ? AND dialogue_id = ?
740 `).run(revisionId, dialogueId);
741 }
742
743 saveChunkVersion (chunkX, chunkY, revisionId, open, dataBlob, dimensionId = OVERWORLD_ID)
744 {
745 const dim = normalizeDimensionId(dimensionId);
746 this.db.prepare(`
747 INSERT INTO world_chunk_versions (dimension_id, chunk_x, chunk_y, revision_id, open, data_blob)
748 VALUES (?, ?, ?, ?, ?, ?)
749 ON CONFLICT(dimension_id, chunk_x, chunk_y, revision_id) DO UPDATE SET
750 open = excluded.open,
751 data_blob = excluded.data_blob
752 `).run(dim, chunkX, chunkY, revisionId, open ? 1 : 0, dataBlob);
753 }
754
755 saveSettings (revisionId, settings)
756 {
757 this.db.prepare(`
758 INSERT INTO world_settings (revision_id, settings_blob)
759 VALUES (?, ?)
760 ON CONFLICT(revision_id) DO UPDATE SET settings_blob = excluded.settings_blob
761 `).run(revisionId, JSON.stringify(settings));
762 }
763
764 appendChangelog (chunkX, chunkY, revisionId, editorAdminId, diffEntries, dimensionId = OVERWORLD_ID)
765 {
766 const dim = normalizeDimensionId(dimensionId);
767 this.db.prepare(`
768 INSERT INTO world_chunk_edit_log
769 (dimension_id, chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries)
770 VALUES (?, ?, ?, ?, ?, ?, ?)
771 `).run(dim, chunkX, chunkY, revisionId, editorAdminId, Date.now(), JSON.stringify(diffEntries));
772 }
773
774 listChangelog (chunkX, chunkY, dimensionId = OVERWORLD_ID)
775 {
776 const dim = normalizeDimensionId(dimensionId);
777 return this.db.prepare(`
778 SELECT log_id, dimension_id, chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries
779 FROM world_chunk_edit_log
780 WHERE dimension_id = ? AND chunk_x = ? AND chunk_y = ?
781 ORDER BY edited_at ASC, log_id ASC
782 `).all(dim, chunkX, chunkY).map((row) => ({
783 ...row,
784 dimensionId: row.dimension_id,
785 diffEntries: JSON.parse(row.diff_entries)
786 }));
787 }
788
789 promotePendingRevision ()
790 {
791 const pendingId = this.getPendingRevisionId();
792 if (!pendingId)
793 { return this.getActiveRevisionId(); }
794
795 return withTransaction(this.db, () =>
796 {
797 const activeId = this.getActiveRevisionId();
798 this.db.prepare("UPDATE world_revisions SET status = 'superseded' WHERE revision_id = ?").run(activeId);
799 this.db.prepare("UPDATE world_revisions SET status = 'active' WHERE revision_id = ?").run(pendingId);
800 this.db.prepare("UPDATE world_meta SET value = ? WHERE key = 'active_revision'").run(pendingId);
801 this.db.exec("UPDATE world_meta SET value = NULL WHERE key = 'pending_revision'");
802 return pendingId;
803 });
804 }
805
806 close ()
807 {
808 this.db.close();
809 }
810}
811
812function mapQuestLogRow (row)
813{
814 return {
815 ...row,
816 questId: row.quest_id,
817 diffEntries: JSON.parse(row.diff_entries)
818 };
819}
820
821function mapDialogueLogRow (row)
822{
823 return {
824 ...row,
825 dialogueId: row.dialogue_id,
826 diffEntries: JSON.parse(row.diff_entries)
827 };
828}