electric-cherry/tests/unit/database-indexes.test.ts

import { afterEach, beforeEach, expect, it, vi } from 'vitest';
import Database from 'better-sqlite3';
import { newFixture, type Fixture } from '../helpers/fixture.js';

let fixture: Fixture;
beforeEach(async () => {
    vi.resetModules();
    fixture = await newFixture([]);
});
afterEach(async () => {
    await fixture.cleanup();
});

function expectModerationReferenceIndexes(handle: Database.Database) {
    for (const [query, index] of [
        [
            "SELECT id FROM moderation_cases WHERE review_sanction_id='restriction'",
            'moderation_case_review_sanction'
        ],
        [
            "SELECT id FROM moderation_case_relationships WHERE case_id='source'",
            'moderation_case_relationship_source'
        ],
        [
            "SELECT id FROM moderation_case_notifications WHERE case_id='source'",
            'moderation_case_notifications_parent'
        ]
    ]) {
        const plan = handle.prepare(`EXPLAIN QUERY PLAN ${query}`).all() as { detail: string }[];
        expect(
            plan.some((row) => row.detail.includes(index)),
            query
        ).toBe(true);
    }
}

it('indexes campaign, beta and moderation references on fresh and reopened databases', async () => {
    const { db, closeDb } = await import('../../src/lib/server/db/index.js');
    for (let pass = 0; pass < 2; pass++) {
        const handle = db();
        for (const [query, index] of [
            [
                'SELECT id FROM staged_file_changes WHERE deployment_file_id = 1',
                'idx_staged_files_deployment_file'
            ],
            [
                'SELECT id FROM beta_test_runs WHERE beta_id = 1 AND revision = 2',
                'idx_beta_test_runs_beta_revision'
            ]
        ]) {
            const plan = handle.prepare(`EXPLAIN QUERY PLAN ${query}`).all() as {
                detail: string;
            }[];
            expect(plan.some((row) => row.detail.includes(index))).toBe(true);
        }
        expectModerationReferenceIndexes(handle);
        expect(handle.pragma('foreign_key_check')).toEqual([]);
        closeDb();
    }
});

it('adds complete moderation reference indexes when upgrading version 34 without changing records', async () => {
    const { migrations } = await import('../../src/lib/server/db/migrations.js');
    const { runMigrations } = await import('../../src/lib/server/db/migration-runner.js');
    const legacy = new Database(':memory:');
    legacy.pragma('foreign_keys = ON');
    try {
        runMigrations(legacy, migrations.slice(0, 34));
        legacy.exec(`
            INSERT INTO auth_users(id,username,display_name,password_hash,role,created_at,updated_at)
                VALUES('moderator','moderator','Moderator','unused','moderator',0,0);
            INSERT INTO moderation_cases(id,game,guid,title,status,actor_id,actor,created_at,updated_at)
                VALUES('source','t5','123','Source case','open','moderator','Moderator','2026-10-17','2026-10-17'),
                    ('target','t5','123','Target case','open','moderator','Moderator','2026-10-17','2026-10-17');
            INSERT INTO moderation_sanctions(id,game,guid,kind,scope,reason,note,actor_id,actor,created_at)
                VALUES('restriction','t5','123','mute','game','Recorded restriction','','moderator','Moderator','2026-10-17');
            UPDATE moderation_cases SET review_sanction_id='restriction' WHERE id='source';
            INSERT INTO moderation_case_relationships VALUES('relationship','source','target','same_incident','moderator','Moderator','2026-10-17','2026-10-18');
            INSERT INTO moderation_case_notifications(user_id,case_id,kind,created_at)
                VALUES('moderator','source','evidence','2026-10-17');
        `);
        const records = () => ({
            cases: legacy.prepare('SELECT * FROM moderation_cases ORDER BY id').all(),
            relationships: legacy.prepare('SELECT * FROM moderation_case_relationships').all(),
            notifications: legacy.prepare('SELECT * FROM moderation_case_notifications').all()
        });
        const before = records();
        runMigrations(legacy, migrations);
        runMigrations(legacy, migrations);
        expectModerationReferenceIndexes(legacy);
        expect(records()).toEqual(before);
        expect(legacy.pragma('foreign_key_check')).toEqual([]);
    } finally {
        legacy.close();
    }
});

it('covers every foreign key child prefix, including host and library schemas', async () => {
    const { db } = await import('../../src/lib/server/db/index.js');
    const handle = db();
    const tables = handle.prepare("SELECT name FROM sqlite_master WHERE type='table'").all() as {
        name: string;
    }[];
    const missing: string[] = [];
    for (const { name } of tables) {
        const foreignKeys = handle.pragma(`foreign_key_list(${name})`) as {
            from: string;
            seq: number;
        }[];
        const indexes = handle.pragma(`index_list(${name})`) as { name: string; partial: number }[];
        const prefixes = indexes
            .filter((index) => !index.partial)
            .map(
                (index) =>
                    (handle.pragma(`index_info(${index.name})`) as { name: string }[])[0]?.name
            );
        const columns = handle.pragma(`table_info(${name})`) as {
            name: string;
            type: string;
            pk: number;
        }[];
        if (columns.filter((column) => column.pk).length === 1)
            prefixes.push(columns.find((column) => column.pk)!.name);
        for (const key of foreignKeys.filter((key) => key.seq === 0)) {
            if (!prefixes.includes(key.from)) missing.push(`${name}.${key.from}`);
        }
    }
    expect(missing).toEqual([]);
});