303 lines
12 KiB
JavaScript
303 lines
12 KiB
JavaScript
'use strict';
|
|
|
|
const test = require('node:test');
|
|
const assert = require('node:assert/strict');
|
|
const crypto = require('node:crypto');
|
|
const fs = require('node:fs');
|
|
const os = require('node:os');
|
|
const path = require('node:path');
|
|
const initSqlJs = require('sql.js');
|
|
const database = require('../../src/database');
|
|
|
|
const MIGRATION_DIR = path.join(__dirname, '../../src/database/migrations');
|
|
const EXPECTED_VERSIONS = Array.from({ length: 20 }, (_, index) => index + 1);
|
|
const quietLogger = { info() {}, warn() {}, error() {} };
|
|
|
|
function resultRows(result) {
|
|
if (!result?.length) return [];
|
|
const [{ columns, values }] = result;
|
|
return values.map((valuesRow) => Object.fromEntries(columns.map((column, index) => [column, valuesRow[index]])));
|
|
}
|
|
|
|
function scalar(db, sql) {
|
|
return db.exec(sql)?.[0]?.values?.[0]?.[0] ?? null;
|
|
}
|
|
|
|
function migrationEntries(throughVersion) {
|
|
return fs.readdirSync(MIGRATION_DIR)
|
|
.filter((name) => /^\d{4}-.+\.js$/.test(name))
|
|
.sort()
|
|
.map((name) => ({
|
|
name,
|
|
path: path.join(MIGRATION_DIR, name),
|
|
migration: require(path.join(MIGRATION_DIR, name)),
|
|
}))
|
|
.filter(({ migration }) => migration.version <= throughVersion);
|
|
}
|
|
|
|
function recordAppliedMigrations(db, entries) {
|
|
const columns = resultRows(db.exec('PRAGMA table_info(schema_migrations)')).map((column) => column.name);
|
|
for (const entry of entries) {
|
|
const valuesByColumn = {
|
|
version: entry.migration.version,
|
|
checksum: crypto.createHash('sha256').update(fs.readFileSync(entry.path)).digest('hex'),
|
|
description: entry.migration.description,
|
|
status: 'applied',
|
|
applied_at: new Date(1700000000000 + entry.migration.version * 1000).toISOString(),
|
|
error: null,
|
|
snapshot_path: null,
|
|
recovery_instructions: null,
|
|
row_count_delta_json: '{}',
|
|
};
|
|
const selected = columns.filter((column) => Object.hasOwn(valuesByColumn, column));
|
|
db.run(
|
|
`INSERT OR REPLACE INTO schema_migrations (${selected.join(', ')}) VALUES (${selected.map(() => '?').join(', ')})`,
|
|
selected.map((column) => valuesByColumn[column]),
|
|
);
|
|
}
|
|
}
|
|
|
|
function seedLegacyGame(db, { gameId, title, version = '1.00', backupPath = '/srv/backups/game.pkg' }) {
|
|
db.run(`
|
|
INSERT INTO games (game_id, title, content_id, size, version, installed, backed_up, ftp_path)
|
|
VALUES (?, ?, ?, 4096, ?, 1, 1, ?)
|
|
`, [gameId, title, gameId, version, `/mnt/console/${gameId}`]);
|
|
db.run('INSERT INTO backups (game_id, backup_path) VALUES (?, ?)', [gameId, backupPath]);
|
|
}
|
|
|
|
function insertAdaptiveTrophyEvidence(db) {
|
|
const columns = resultRows(db.exec('PRAGMA table_info(trophy_psn_snapshots)'));
|
|
const overrides = {
|
|
snapshot_id: 'fixture-psn-snapshot',
|
|
snapshot_key: 'fixture-psn-snapshot',
|
|
account_id: 'fixture-account',
|
|
account_ref: 'fixture-account',
|
|
online_id: 'fixture-user',
|
|
source: 'psn-read-only',
|
|
source_type: 'psn-read-only',
|
|
source_ref: 'fixture-export',
|
|
captured_at: 1700000000000,
|
|
observed_at: 1700000000000,
|
|
imported_at: 1700000000000,
|
|
evidence_hash: 'a'.repeat(64),
|
|
payload_hash: 'b'.repeat(64),
|
|
canonical_sha256: 'c'.repeat(64),
|
|
payload_json: '{"titles":[]}',
|
|
snapshot_json: '{"titles":[]}',
|
|
trophy_count: 1,
|
|
title_count: 1,
|
|
confidence: 'high',
|
|
provenance: 'migration-rehearsal',
|
|
status: 'verified',
|
|
};
|
|
const selected = [];
|
|
const values = [];
|
|
for (const column of columns) {
|
|
if (column.pk && /INT/i.test(column.type || '')) continue;
|
|
if (Object.hasOwn(overrides, column.name)) {
|
|
selected.push(column.name);
|
|
values.push(overrides[column.name]);
|
|
continue;
|
|
}
|
|
if (column.notnull && column.dflt_value === null) {
|
|
selected.push(column.name);
|
|
values.push(/INT|REAL|NUM/i.test(column.type || '') ? 1 : `fixture-${column.name}`);
|
|
}
|
|
}
|
|
db.run(
|
|
`INSERT INTO trophy_psn_snapshots (${selected.join(', ')}) VALUES (${selected.map(() => '?').join(', ')})`,
|
|
values,
|
|
);
|
|
}
|
|
|
|
async function buildVersionedDatabase(root, throughVersion, { beforeVersion, afterMigrations } = {}) {
|
|
const SQL = await initSqlJs();
|
|
const db = new SQL.Database();
|
|
db.run('PRAGMA foreign_keys = ON');
|
|
const entries = migrationEntries(throughVersion);
|
|
for (const entry of entries) {
|
|
try {
|
|
beforeVersion?.(entry.migration.version, db);
|
|
db.run('BEGIN IMMEDIATE');
|
|
entry.migration.up(db);
|
|
db.run('COMMIT');
|
|
} catch (error) {
|
|
try { db.run('ROLLBACK'); } catch {}
|
|
throw new Error(`Direct migration fixture failed at version ${entry.migration.version}: ${error.message || error}`, { cause: error });
|
|
}
|
|
}
|
|
try {
|
|
afterMigrations?.(db);
|
|
} catch (error) {
|
|
throw new Error(`Post-migration fixture failed at version ${throughVersion}: ${error.message || error}`, { cause: error });
|
|
}
|
|
recordAppliedMigrations(db, entries);
|
|
fs.writeFileSync(path.join(root, 'chimera.db'), Buffer.from(db.export()));
|
|
db.close();
|
|
}
|
|
|
|
async function buildAmbiguousLegacyDatabase(root) {
|
|
const SQL = await initSqlJs();
|
|
const db = new SQL.Database();
|
|
db.run(`
|
|
CREATE TABLE games (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
game_id TEXT NOT NULL,
|
|
content_id TEXT DEFAULT '',
|
|
size INTEGER DEFAULT 0,
|
|
version TEXT DEFAULT '',
|
|
installed INTEGER DEFAULT 0,
|
|
backed_up INTEGER DEFAULT 0,
|
|
ftp_path TEXT DEFAULT '',
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
)
|
|
`);
|
|
db.run('CREATE TABLE backups (id INTEGER PRIMARY KEY AUTOINCREMENT, game_id TEXT NOT NULL, backup_path TEXT NOT NULL UNIQUE)');
|
|
db.run(`
|
|
INSERT INTO games (game_id, title, content_id, size, version, installed)
|
|
VALUES ('CUSA10002', 'Ambiguous Fixture', 'CUSA10002', 1, '1.00', 0),
|
|
('CUSA10002', 'Ambiguous Fixture', 'CUSA10002', 1, '1.01', 0)
|
|
`);
|
|
fs.writeFileSync(path.join(root, 'chimera.db'), Buffer.from(db.export()));
|
|
db.close();
|
|
}
|
|
|
|
async function openRoot(root) {
|
|
database.configure({ dataDir: root, logger: quietLogger });
|
|
return database.initialize();
|
|
}
|
|
|
|
async function closeAndRemove(root, { closeDatabase = true } = {}) {
|
|
database.setTestMigrationFailureVersion(null);
|
|
if (closeDatabase) {
|
|
try {
|
|
await database.close();
|
|
} catch (error) {
|
|
throw new Error(`Cleanup database close failed: ${error.message || error}`, { cause: error });
|
|
}
|
|
}
|
|
try {
|
|
fs.rmSync(root, { recursive: true, force: true });
|
|
} catch (error) {
|
|
throw new Error(`Cleanup fixture removal failed: ${error.message || error}`, { cause: error });
|
|
}
|
|
}
|
|
|
|
function assertAllMigrationsApplied() {
|
|
const rows = database.runQuery('SELECT version, status FROM schema_migrations ORDER BY version');
|
|
assert.deepEqual(rows.map((row) => row.version), EXPECTED_VERSIONS);
|
|
assert.ok(rows.every((row) => row.status === 'applied'));
|
|
}
|
|
|
|
function assertForeignKeysClean() {
|
|
assert.deepEqual(database.checkForeignKeys(), []);
|
|
}
|
|
|
|
test('CH-1002 rehearses legacy, v11 and v16 migration and recovery paths', async (t) => {
|
|
await t.test('ambiguous legacy schema migrates without inventing version certainty', async () => {
|
|
const root = fs.mkdtempSync(path.join(os.tmpdir(), 'chimera-ch1002-legacy-'));
|
|
try {
|
|
await buildAmbiguousLegacyDatabase(root);
|
|
await openRoot(root);
|
|
assertAllMigrationsApplied();
|
|
const report = JSON.parse(database.runQuery("SELECT report_json FROM migration_reports WHERE migration_name = 'legacy-to-v3'")[0].report_json);
|
|
assert.deepEqual(report.ambiguousVersions, [{ game_id: 'CUSA10002', versions: ['1.00', '1.01'] }]);
|
|
assert.equal(database.runQuery("SELECT COUNT(*) AS count FROM titles WHERE title_id = 'CUSA10002'")[0].count, 1);
|
|
assertForeignKeysClean();
|
|
} finally {
|
|
await closeAndRemove(root);
|
|
}
|
|
});
|
|
|
|
await t.test('v11 install, update and backup evidence survives through v20', async () => {
|
|
const root = fs.mkdtempSync(path.join(os.tmpdir(), 'chimera-ch1002-v11-'));
|
|
try {
|
|
await buildVersionedDatabase(root, 11, {
|
|
beforeVersion(version, db) {
|
|
if (version === 4) seedLegacyGame(db, {
|
|
gameId: 'CUSA10011',
|
|
title: 'Version Eleven Fixture',
|
|
backupPath: '/srv/backups/CUSA10011/game.pkg',
|
|
});
|
|
},
|
|
afterMigrations(db) {
|
|
const titleId = scalar(db, "SELECT id FROM titles WHERE title_id = 'CUSA10011'");
|
|
db.run(`
|
|
INSERT INTO version_evidence (subject_type, subject_id, semantic_version, raw_version, version_source, observed_at, confidence)
|
|
VALUES ('title', ?, '1.02', '01.020.000', 'fixture-update-manifest', 1700000000000, 'high')
|
|
`, [titleId]);
|
|
},
|
|
});
|
|
await openRoot(root);
|
|
assertAllMigrationsApplied();
|
|
assert.equal(database.runQuery("SELECT COUNT(*) AS count FROM console_installs ci JOIN titles t ON t.id = ci.title_id WHERE t.title_id = 'CUSA10011'")[0].count, 1);
|
|
assert.equal(database.runQuery("SELECT COUNT(*) AS count FROM local_copies lc JOIN titles t ON t.id = lc.title_id WHERE t.title_id = 'CUSA10011'")[0].count, 1);
|
|
assert.equal(database.runQuery("SELECT semantic_version FROM version_evidence ve JOIN titles t ON t.id = ve.subject_id WHERE ve.subject_type = 'title' AND t.title_id = 'CUSA10011'")[0].semantic_version, '1.02');
|
|
assertForeignKeysClean();
|
|
} finally {
|
|
await closeAndRemove(root);
|
|
}
|
|
});
|
|
|
|
await t.test('v16 trophy evidence survives forced failure, snapshot rollback and idempotent rerun', async () => {
|
|
const root = fs.mkdtempSync(path.join(os.tmpdir(), 'chimera-ch1002-v16-'));
|
|
let stage = 'build-v16-copy';
|
|
try {
|
|
await buildVersionedDatabase(root, 16, {
|
|
beforeVersion(version, db) {
|
|
if (version === 4) seedLegacyGame(db, {
|
|
gameId: 'PPSA10016',
|
|
title: 'Version Sixteen Fixture',
|
|
backupPath: '/srv/backups/PPSA10016/game.pkg',
|
|
});
|
|
},
|
|
afterMigrations(db) {
|
|
insertAdaptiveTrophyEvidence(db);
|
|
},
|
|
});
|
|
|
|
stage = 'forced-version-18-failure';
|
|
database.setTestMigrationFailureVersion(18);
|
|
await assert.rejects(() => openRoot(root), /Test-gedwongen migratiefout voor versie 18/);
|
|
assert.equal(database.runQuery('SELECT COUNT(*) AS count FROM trophy_psn_snapshots')[0].count, 1);
|
|
assert.equal(database.runQuery("SELECT COUNT(*) AS count FROM local_copies lc JOIN titles t ON t.id = lc.title_id WHERE t.title_id = 'PPSA10016'")[0].count, 1);
|
|
const failed = database.runQuery('SELECT status, snapshot_path, recovery_instructions FROM schema_migrations WHERE version = 18')[0];
|
|
assert.equal(failed.status, 'failed');
|
|
assert.ok(failed.snapshot_path);
|
|
assert.ok(failed.recovery_instructions);
|
|
assertForeignKeysClean();
|
|
|
|
stage = 'recovery-retry';
|
|
database.setTestMigrationFailureVersion(null);
|
|
await database.initialize();
|
|
assertAllMigrationsApplied();
|
|
const semanticBefore = database.runQuery(`
|
|
SELECT
|
|
(SELECT COUNT(*) FROM trophy_psn_snapshots) AS trophies,
|
|
(SELECT COUNT(*) FROM local_copies) AS backups,
|
|
(SELECT COUNT(*) FROM schema_migrations WHERE status = 'applied') AS migrations
|
|
`)[0];
|
|
assert.deepEqual(semanticBefore, { trophies: 1, backups: 1, migrations: 20 });
|
|
assertForeignKeysClean();
|
|
|
|
stage = 'idempotent-reopen';
|
|
await database.close();
|
|
await openRoot(root);
|
|
const semanticAfter = database.runQuery(`
|
|
SELECT
|
|
(SELECT COUNT(*) FROM trophy_psn_snapshots) AS trophies,
|
|
(SELECT COUNT(*) FROM local_copies) AS backups,
|
|
(SELECT COUNT(*) FROM schema_migrations WHERE status = 'applied') AS migrations
|
|
`)[0];
|
|
assert.deepEqual(semanticAfter, semanticBefore);
|
|
assertAllMigrationsApplied();
|
|
assertForeignKeysClean();
|
|
} catch (error) {
|
|
throw new Error(`CH-1002 v16 stage ${stage} failed: ${error.message || error}`, { cause: error });
|
|
} finally {
|
|
await closeAndRemove(root, { closeDatabase: stage !== 'build-v16-copy' });
|
|
}
|
|
});
|
|
});
|