BlueGit

timfa questward

Files

questward / server / src / persistence / worldDb.js

worldDb.js

worldDb.js 6.1 KiB Download Raw
1import { DEFAULT_WORLD_SETTINGS } from "../../../shared/protocol/constants.js";
2import { openDatabase, withTransaction } from "./database.js";
3
4const WORLD_SCHEMA = `
5CREATE TABLE IF NOT EXISTS world_revisions (
6 revision_id INTEGER PRIMARY KEY AUTOINCREMENT,
7 created_at INTEGER NOT NULL,
8 created_by TEXT NOT NULL,
9 status TEXT NOT NULL
10);
11
12CREATE TABLE IF NOT EXISTS world_chunk_versions (
13 chunk_x INTEGER NOT NULL,
14 chunk_y INTEGER NOT NULL,
15 revision_id INTEGER NOT NULL,
16 open INTEGER NOT NULL,
17 data_blob TEXT NOT NULL,
18 PRIMARY KEY (chunk_x, chunk_y, revision_id)
19);
20
21CREATE TABLE IF NOT EXISTS world_meta (
22 key TEXT PRIMARY KEY,
23 value INTEGER
24);
25
26CREATE TABLE IF NOT EXISTS world_settings (
27 revision_id INTEGER PRIMARY KEY,
28 settings_blob TEXT NOT NULL
29);
30
31CREATE TABLE IF NOT EXISTS world_chunk_edit_log (
32 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
33 chunk_x INTEGER NOT NULL,
34 chunk_y INTEGER NOT NULL,
35 revision_id INTEGER NOT NULL,
36 editor_admin_id TEXT NOT NULL,
37 edited_at INTEGER NOT NULL,
38 diff_entries TEXT NOT NULL
39);
40`;
41
42export class WorldDatabase
43{
44 constructor (filePath)
45 {
46 this.db = openDatabase(filePath);
47 this.db.exec(WORLD_SCHEMA);
48 this.#ensureInitialRevision();
49 }
50
51 #ensureInitialRevision ()
52 {
53 const meta = this.db.prepare("SELECT key, value FROM world_meta").all();
54 if (meta.length > 0)
55 { return; }
56
57 withTransaction(this.db, () =>
58 {
59 this.db.prepare(
60 "INSERT INTO world_revisions (created_at, created_by, status) VALUES (?, ?, ?)"
61 ).run(Date.now(), "system", "active");
62 const revisionId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
63 this.db.prepare("INSERT INTO world_meta (key, value) VALUES ('active_revision', ?)").run(revisionId);
64 this.db.exec("INSERT INTO world_meta (key, value) VALUES ('pending_revision', NULL)");
65 this.db.prepare("INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?)").run(
66 revisionId,
67 JSON.stringify(DEFAULT_WORLD_SETTINGS)
68 );
69 });
70 }
71
72 getActiveRevisionId ()
73 {
74 return this.db.prepare("SELECT value FROM world_meta WHERE key = 'active_revision'").get()?.value ?? null;
75 }
76
77 getPendingRevisionId ()
78 {
79 return this.db.prepare("SELECT value FROM world_meta WHERE key = 'pending_revision'").get()?.value ?? null;
80 }
81
82 getEditorRevisionId ()
83 {
84 return this.getPendingRevisionId() ?? this.getActiveRevisionId();
85 }
86
87 getSettings (revisionId)
88 {
89 const row = this.db.prepare("SELECT settings_blob FROM world_settings WHERE revision_id = ?").get(revisionId);
90 if (row)
91 { return { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(row.settings_blob) }; }
92
93 const fallback = this.db.prepare(
94 "SELECT settings_blob FROM world_settings WHERE revision_id <= ? ORDER BY revision_id DESC LIMIT 1"
95 ).get(revisionId);
96 return fallback
97 ? { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(fallback.settings_blob) }
98 : { ...DEFAULT_WORLD_SETTINGS };
99 }
100
101 getChunkVersion (chunkX, chunkY, revisionId)
102 {
103 return this.db.prepare(`
104 SELECT chunk_x, chunk_y, revision_id, open, data_blob
105 FROM world_chunk_versions
106 WHERE chunk_x = ? AND chunk_y = ? AND revision_id <= ?
107 ORDER BY revision_id DESC
108 LIMIT 1
109 `).get(chunkX, chunkY, revisionId) ?? null;
110 }
111
112 listChunkCoords (revisionId)
113 {
114 return this.db.prepare(`
115 SELECT chunk_x, chunk_y, MAX(revision_id) AS revision_id
116 FROM world_chunk_versions
117 WHERE revision_id <= ?
118 GROUP BY chunk_x, chunk_y
119 `).all(revisionId);
120 }
121
122 ensurePendingRevision (createdBy)
123 {
124 const existing = this.getPendingRevisionId();
125 if (existing)
126 { return existing; }
127
128 return withTransaction(this.db, () =>
129 {
130 const activeId = this.getActiveRevisionId();
131 this.db.prepare(
132 "INSERT INTO world_revisions (created_at, created_by, status) VALUES (?, ?, ?)"
133 ).run(Date.now(), createdBy, "pending");
134 const revisionId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
135 this.db.prepare("UPDATE world_meta SET value = ? WHERE key = 'pending_revision'").run(revisionId);
136
137 const settings = this.getSettings(activeId);
138 this.db.prepare("INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?)").run(
139 revisionId,
140 JSON.stringify(settings)
141 );
142 return revisionId;
143 });
144 }
145
146 saveChunkVersion (chunkX, chunkY, revisionId, open, dataBlob)
147 {
148 this.db.prepare(`
149 INSERT INTO world_chunk_versions (chunk_x, chunk_y, revision_id, open, data_blob)
150 VALUES (?, ?, ?, ?, ?)
151 ON CONFLICT(chunk_x, chunk_y, revision_id) DO UPDATE SET
152 open = excluded.open,
153 data_blob = excluded.data_blob
154 `).run(chunkX, chunkY, revisionId, open ? 1 : 0, dataBlob);
155 }
156
157 saveSettings (revisionId, settings)
158 {
159 this.db.prepare(`
160 INSERT INTO world_settings (revision_id, settings_blob)
161 VALUES (?, ?)
162 ON CONFLICT(revision_id) DO UPDATE SET settings_blob = excluded.settings_blob
163 `).run(revisionId, JSON.stringify(settings));
164 }
165
166 appendChangelog (chunkX, chunkY, revisionId, editorAdminId, diffEntries)
167 {
168 this.db.prepare(`
169 INSERT INTO world_chunk_edit_log
170 (chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries)
171 VALUES (?, ?, ?, ?, ?, ?)
172 `).run(chunkX, chunkY, revisionId, editorAdminId, Date.now(), JSON.stringify(diffEntries));
173 }
174
175 listChangelog (chunkX, chunkY)
176 {
177 return this.db.prepare(`
178 SELECT log_id, chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries
179 FROM world_chunk_edit_log
180 WHERE chunk_x = ? AND chunk_y = ?
181 ORDER BY edited_at ASC, log_id ASC
182 `).all(chunkX, chunkY).map((row) => ({
183 ...row,
184 diffEntries: JSON.parse(row.diff_entries)
185 }));
186 }
187
188 promotePendingRevision ()
189 {
190 const pendingId = this.getPendingRevisionId();
191 if (!pendingId)
192 { return this.getActiveRevisionId(); }
193
194 return withTransaction(this.db, () =>
195 {
196 const activeId = this.getActiveRevisionId();
197 this.db.prepare("UPDATE world_revisions SET status = 'superseded' WHERE revision_id = ?").run(activeId);
198 this.db.prepare("UPDATE world_revisions SET status = 'active' WHERE revision_id = ?").run(pendingId);
199 this.db.prepare("UPDATE world_meta SET value = ? WHERE key = 'active_revision'").run(pendingId);
200 this.db.exec("UPDATE world_meta SET value = NULL WHERE key = 'pending_revision'");
201 return pendingId;
202 });
203 }
204
205 close ()
206 {
207 this.db.close();
208 }
209}