| 1 | import { DEFAULT_WORLD_SETTINGS } from "../../../shared/protocol/constants.js"; |
| 2 | import { openDatabase, withTransaction } from "./database.js"; |
| 3 | |
| 4 | const WORLD_SCHEMA = ` |
| 5 | CREATE 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 | |
| 12 | CREATE 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 | |
| 21 | CREATE TABLE IF NOT EXISTS world_meta ( |
| 22 | key TEXT PRIMARY KEY, |
| 23 | value INTEGER |
| 24 | ); |
| 25 | |
| 26 | CREATE TABLE IF NOT EXISTS world_settings ( |
| 27 | revision_id INTEGER PRIMARY KEY, |
| 28 | settings_blob TEXT NOT NULL |
| 29 | ); |
| 30 | |
| 31 | CREATE 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 | CREATE 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 | |
| 48 | CREATE 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 | |
| 56 | export 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 | } |