Stardream
299 lines
12 KiB
JavaScript
299 lines
12 KiB
JavaScript
'use strict';
|
|
|
|
async function createStore(databaseUrl) {
|
|
if (!databaseUrl) {
|
|
throw new Error('DATABASE_URL is required. Run with docker compose so the PostgreSQL container is available.');
|
|
}
|
|
const { Pool } = require('pg');
|
|
const pool = new Pool({ connectionString: databaseUrl });
|
|
|
|
// Bumped on every write that changes app_state.json, so the route layer can cache /api/state
|
|
// and serve 304s until the data actually changes.
|
|
let stateVersion = 1;
|
|
const bumpState = () => { stateVersion++; };
|
|
await pool.query(`
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS app_state (
|
|
id INTEGER PRIMARY KEY CHECK (id = 1),
|
|
schema INTEGER,
|
|
json JSONB NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS collections (
|
|
subject_id TEXT PRIMARY KEY,
|
|
json JSONB NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS runs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
json JSONB NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
subject_id TEXT,
|
|
json JSONB NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS history_subject_idx ON history(subject_id);
|
|
`);
|
|
|
|
// One-time migration: move the (huge) history array out of app_state.json into the history table,
|
|
// so the main state row stays small and GET /api/state never pays to strip a 60+MB history blob.
|
|
// Atomic: either history is fully moved and stripped, or nothing changes (no data loss).
|
|
{
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
const probe = await client.query(
|
|
`SELECT jsonb_typeof(json->'history') AS t, COALESCE(jsonb_array_length(json->'history'), 0) AS n
|
|
FROM app_state WHERE id = 1 FOR UPDATE`
|
|
);
|
|
const row = probe.rows[0];
|
|
if (row && row.t === 'array' && Number(row.n) > 0) {
|
|
await client.query(
|
|
`INSERT INTO history(subject_id, json, created_at)
|
|
SELECT e->>'subject_id', e, COALESCE(NULLIF(e->>'created_at', '')::timestamptz, NOW())
|
|
FROM app_state, jsonb_array_elements(json->'history') e
|
|
WHERE app_state.id = 1`
|
|
);
|
|
await client.query(`UPDATE app_state SET json = json - 'history' WHERE id = 1`);
|
|
console.log(`Migrated ${row.n} history entries from app_state.json into the history table.`);
|
|
}
|
|
await client.query('COMMIT');
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
throw err;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
|
|
async function refreshCollectionsTable(client, collections) {
|
|
await client.query('DELETE FROM collections');
|
|
for (const [id, row] of Object.entries(collections || {})) {
|
|
await client.query(
|
|
'INSERT INTO collections(subject_id, json, updated_at) VALUES ($1, $2::jsonb, NOW()) ON CONFLICT(subject_id) DO UPDATE SET json = EXCLUDED.json, updated_at = EXCLUDED.updated_at',
|
|
[String(id), JSON.stringify(row)]
|
|
);
|
|
}
|
|
}
|
|
|
|
async function refreshRunsTable(client, runs) {
|
|
await client.query('DELETE FROM runs');
|
|
for (const run of runs || []) {
|
|
await client.query('INSERT INTO runs(json, created_at) VALUES ($1::jsonb, $2)', [
|
|
JSON.stringify(run),
|
|
run.finished_at || run.started_at || new Date().toISOString()
|
|
]);
|
|
}
|
|
}
|
|
|
|
return {
|
|
kind: 'postgres',
|
|
|
|
getStateVersion() { return stateVersion; },
|
|
|
|
async getSetting(key) {
|
|
const result = await pool.query('SELECT value FROM settings WHERE key = $1', [key]);
|
|
return result.rows[0]?.value || '';
|
|
},
|
|
|
|
async setSetting(key, value) {
|
|
await pool.query(
|
|
'INSERT INTO settings(key, value, updated_at) VALUES ($1, $2, NOW()) ON CONFLICT(key) DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at',
|
|
[key, String(value)]
|
|
);
|
|
},
|
|
|
|
async getState() {
|
|
const result = await pool.query('SELECT json FROM app_state WHERE id = 1');
|
|
return result.rows[0]?.json || null;
|
|
},
|
|
|
|
// Client-facing state: history is in its own table (lazy-loaded per subject), and the heavy
|
|
// per-collection blobs the frontend never reads (`subject_raw`, `infobox`) are stripped here in
|
|
// Postgres so they never get serialized/gzipped/transferred on load.
|
|
async getClientState() {
|
|
// Cheap in Postgres: just drop history. Strip the unused heavy per-collection blobs in JS
|
|
// (fast key deletes) — doing it via jsonb_each/jsonb_object_agg in SQL is far more CPU.
|
|
const result = await pool.query(`SELECT json - 'history' AS json FROM app_state WHERE id = 1`);
|
|
const j = result.rows[0]?.json;
|
|
if (!j) return null;
|
|
const cols = j.collections;
|
|
if (cols && typeof cols === 'object') {
|
|
for (const k in cols) {
|
|
const c = cols[k];
|
|
if (c) { delete c.subject_raw; delete c.infobox; }
|
|
}
|
|
}
|
|
return j;
|
|
},
|
|
|
|
// History entries for one subject (detail drawer).
|
|
async getSubjectHistory(subjectId) {
|
|
const result = await pool.query(
|
|
`SELECT coalesce(jsonb_agg(json ORDER BY id), '[]'::jsonb) AS history FROM history WHERE subject_id = $1`,
|
|
[String(subjectId)]
|
|
);
|
|
return result.rows[0]?.history || [];
|
|
},
|
|
|
|
// Subject infobox for one collection (game type/engine/developer…). Stripped from /api/state to
|
|
// keep that payload small, so the detail drawer lazy-loads it per subject like history does.
|
|
async getSubjectInfobox(subjectId) {
|
|
const result = await pool.query(
|
|
`SELECT json -> 'collections' -> $1 -> 'infobox' AS infobox FROM app_state WHERE id = 1`,
|
|
[String(subjectId)]
|
|
);
|
|
const ib = result.rows[0]?.infobox;
|
|
return Array.isArray(ib) ? ib : [];
|
|
},
|
|
|
|
// All infoboxes as a { subject_id: infobox } map (one row de-TOAST). Lets the client prefetch every
|
|
// infobox in the background after load, so opening any drawer shows 条目资料 instantly from cache.
|
|
async getAllInfoboxes() {
|
|
const result = await pool.query(
|
|
`SELECT coalesce(jsonb_object_agg(e.key, e.value -> 'infobox')
|
|
FILTER (WHERE jsonb_typeof(e.value -> 'infobox') = 'array' AND e.value -> 'infobox' <> '[]'::jsonb),
|
|
'{}'::jsonb) AS infoboxes
|
|
FROM app_state, jsonb_each(app_state.json -> 'collections') AS e
|
|
WHERE app_state.id = 1`
|
|
);
|
|
return result.rows[0]?.infoboxes || {};
|
|
},
|
|
|
|
// Full history array (backup export).
|
|
async getAllHistory() {
|
|
const result = await pool.query(`SELECT coalesce(jsonb_agg(json ORDER BY id), '[]'::jsonb) AS history FROM history`);
|
|
return result.rows[0]?.history || [];
|
|
},
|
|
|
|
// Full replace — backup import and in-app full saves. History now lives in its own table and is
|
|
// NOT stored in app_state.json. The browser no longer holds history, so a historyless payload must
|
|
// NOT wipe server history: only replace the history table when the payload carries a non-empty
|
|
// history (backup import); otherwise leave the existing history table untouched.
|
|
async persistState(state) {
|
|
const incomingHasHistory = Array.isArray(state.history) && state.history.length > 0;
|
|
const jsonNoHistory = { ...state };
|
|
delete jsonNoHistory.history;
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
await client.query(
|
|
'INSERT INTO app_state(id, schema, json, updated_at) VALUES (1, $1, $2::jsonb, NOW()) ON CONFLICT(id) DO UPDATE SET schema = EXCLUDED.schema, json = EXCLUDED.json, updated_at = EXCLUDED.updated_at',
|
|
[Number(state.schema || 0), JSON.stringify(jsonNoHistory)]
|
|
);
|
|
if (incomingHasHistory) {
|
|
await client.query('DELETE FROM history');
|
|
await client.query(
|
|
`INSERT INTO history(subject_id, json, created_at)
|
|
SELECT e->>'subject_id', e, COALESCE(NULLIF(e->>'created_at', '')::timestamptz, NOW())
|
|
FROM jsonb_array_elements($1::jsonb) e`,
|
|
[JSON.stringify(state.history)]
|
|
);
|
|
}
|
|
await refreshCollectionsTable(client, state.collections);
|
|
await refreshRunsTable(client, state.runs);
|
|
await client.query('COMMIT');
|
|
bumpState();
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
throw err;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
},
|
|
|
|
// Merge a partial settings object into app_state.json.settings without touching collections.
|
|
async setStateSettings(partial) {
|
|
await pool.query(
|
|
`INSERT INTO app_state(id, schema, json, updated_at)
|
|
VALUES (1, 9, jsonb_build_object('schema', 9, 'settings', $1::jsonb, 'collections', '{}'::jsonb, 'runs', '[]'::jsonb), NOW())
|
|
ON CONFLICT(id) DO UPDATE SET
|
|
json = jsonb_set(coalesce(app_state.json, '{}'::jsonb), '{settings}',
|
|
coalesce(app_state.json->'settings', '{}'::jsonb) || $1::jsonb, true),
|
|
updated_at = NOW()`,
|
|
[JSON.stringify(partial || {})]
|
|
);
|
|
bumpState();
|
|
},
|
|
|
|
// Update a single collection's fields (safety-box actions). Reflects into both app_state.json and the collections table.
|
|
async patchCollection(subjectId, patch) {
|
|
const id = String(subjectId);
|
|
const cur = await pool.query(`SELECT json->'collections'->$1 AS row FROM app_state WHERE id = 1`, [id]);
|
|
const existing = cur.rows[0]?.row;
|
|
if (!existing) return null;
|
|
const merged = { ...existing, ...(patch || {}) };
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
await client.query(
|
|
`UPDATE app_state SET json = jsonb_set(json, ARRAY['collections', $1], $2::jsonb, true), updated_at = NOW() WHERE id = 1`,
|
|
[id, JSON.stringify(merged)]
|
|
);
|
|
await client.query(
|
|
'INSERT INTO collections(subject_id, json, updated_at) VALUES ($1, $2::jsonb, NOW()) ON CONFLICT(subject_id) DO UPDATE SET json = EXCLUDED.json, updated_at = EXCLUDED.updated_at',
|
|
[id, JSON.stringify(merged)]
|
|
);
|
|
await client.query('COMMIT');
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
throw err;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
bumpState();
|
|
return merged;
|
|
},
|
|
|
|
// Sync end: replace collections/runs and settings.autoSyncLastRunDate in app_state.json (history is
|
|
// NOT in json anymore), and APPEND the run's new history entries to the history table.
|
|
// `history` here is the new entries produced by this run (runSync starts from an empty history).
|
|
async persistSyncResult({ collections, history, runs, lastRunDate }) {
|
|
const client = await pool.connect();
|
|
try {
|
|
await client.query('BEGIN');
|
|
await client.query(
|
|
`INSERT INTO app_state(id, schema, json, updated_at)
|
|
VALUES (1, 9, jsonb_build_object('schema', 9, 'settings', jsonb_build_object('autoSyncLastRunDate', $3::text),
|
|
'collections', $1::jsonb, 'runs', $2::jsonb), NOW())
|
|
ON CONFLICT(id) DO UPDATE SET
|
|
json = jsonb_set(jsonb_set(jsonb_set(
|
|
app_state.json,
|
|
'{collections}', $1::jsonb),
|
|
'{runs}', $2::jsonb),
|
|
'{settings,autoSyncLastRunDate}', to_jsonb($3::text), true),
|
|
updated_at = NOW()`,
|
|
[JSON.stringify(collections || {}), JSON.stringify(runs || []), String(lastRunDate || '')]
|
|
);
|
|
const newHistory = Array.isArray(history) ? history : [];
|
|
if (newHistory.length) {
|
|
await client.query(
|
|
`INSERT INTO history(subject_id, json, created_at)
|
|
SELECT e->>'subject_id', e, COALESCE(NULLIF(e->>'created_at', '')::timestamptz, NOW())
|
|
FROM jsonb_array_elements($1::jsonb) e`,
|
|
[JSON.stringify(newHistory)]
|
|
);
|
|
}
|
|
await refreshCollectionsTable(client, collections);
|
|
await refreshRunsTable(client, runs);
|
|
await client.query('COMMIT');
|
|
bumpState();
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
throw err;
|
|
} finally {
|
|
client.release();
|
|
}
|
|
}
|
|
};
|
|
}
|
|
|
|
module.exports = { createStore };
|