BlueGit

timfa questward

Files

questward / server / src / persistence / worldDb.js

worldDb.js

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