1// The D1 migrations (worker/migrations/) are applied by name, in order, and
2// each only once per database. So the numbers must run 0001, 0002, … with no
3// gap and no two alike: two branches that each add a 0002 fail here when
4// they meet, rather than one of them silently never applying where the other
5// already has. A migration that has deployed is never edited (the database
6// has already run it); a change is a new file.
7import { readFileSync } from 'node:fs';
8import { DatabaseSync } from 'node:sqlite';
9import { describe, expect, it } from 'vitest';
10import { migrations, MIGRATIONS_DIR, testD1 } from '../test/d1';
11
12describe('the D1 migrations', () => {
13  it('are numbered 0001, 0002, … with no gap or repeat', () => {
14    const files = migrations();
15    expect(files.length).toBeGreaterThan(0);
16    for (const [i, file] of files.entries()) {
17      expect(file).toMatch(/^\d{4}_[a-z0-9_]+\.sql$/);
18      expect(Number(file.slice(0, 4)), file).toBe(i + 1);
19    }
20  });
21
22  it('every one applies, in order, to an empty database', () => {
23    expect(() => testD1()).not.toThrow();
24  });
25
26  it('create STRICT tables', () => {
27    for (const file of migrations()) {
28      const sql = readFileSync(new URL(file, MIGRATIONS_DIR), 'utf8').replace(/--.*$/gm, '');
29      for (const table of sql.match(/CREATE TABLE[\s\S]*?\)\s*[^;]*;/gi) ?? []) {
30        expect(table, file).toMatch(/\bSTRICT\b/i);
31      }
32    }
33  });
34
35  it('0006 dates the takes stored before it at noon UTC of their day, with no player, and every later one too', () => {
36    const db = new DatabaseSync(':memory:');
37    const apply = (file: string) => db.exec(readFileSync(new URL(file, MIGRATIONS_DIR), 'utf8'));
38    const [before, after] = [migrations().filter((f) => f < '0006'), migrations().filter((f) => f >= '0006')];
39    before.forEach(apply);
40    const insert = (id: string) =>
41      db
42        .prepare(`INSERT INTO performances (id, song, code_hash, model, every, ended, jevs, day) VALUES (?, 'jev/a', ?, 'm', 8, 'finished', 1, '2026-09-20')`)
43        .run(id, 'h'.repeat(64));
44    insert('a'.repeat(22));
45    after.forEach(apply);
46    const noon = Date.parse('2026-09-20T12:00:00Z');
47    expect(db.prepare('SELECT recorded_at, user_id FROM performances').get()).toEqual({ recorded_at: noon, user_id: null });
48    // the Worker that was live during the deploy writes no time: the trigger dates it
49    insert('b'.repeat(22));
50    expect(db.prepare('SELECT recorded_at FROM performances WHERE id = ?').get('b'.repeat(22))).toEqual({ recorded_at: noon });
51    // a player is an account, shaped as one
52    expect(() => db.prepare('UPDATE performances SET user_id = ?').run('short')).toThrow(/CHECK/);
53    expect(() => db.prepare('UPDATE performances SET user_id = ?').run('N'.repeat(22))).toThrow(/FOREIGN KEY/);
54    expect(() => db.prepare('UPDATE performances SET recorded_at = 0').run()).toThrow(/CHECK/);
55  });
56});