Hankan/test/db/migration-6.test.ts

/* Migration 6 — cards move to stable lemma ids.

   A database as the previous build left it: positional lemma ids, a custom
   word at the old reserved offset, a tombstone naming a card by its old id.
   After the migration every reference names the same word under its hashed
   id, and nothing has been stamped — the rows are the same rows, renamed. */

import { describe, it, expect, beforeEach, afterEach } from "vitest";
import { SqliteWasmDb } from "@app/db/sqlite-wasm-core.js";
import { migrate } from "@app/db/migrate.js";
import { lemmaId } from "@shared/lemma-id.mjs";
import type { Db } from "@app/db/types.js";

let db: Db;
beforeEach(async () => {
  db = await SqliteWasmDb.open({ memory: true });
  await migrate(db, 5);
  await db.exec(`
    INSERT INTO lemma (id, headword, pos, gloss_en, unit_band, source) VALUES
      (1, '물', 'noun', 'water', 0, 'curated'),
      (2, '밥', 'noun', 'rice', 0, 'curated'),
      (3, '책', 'noun', 'book', 0, 'curated'),
      (10000000, '던전', 'noun', 'dungeon', 0, 'custom');
    INSERT INTO surface (form, lemma_id, analysis) VALUES
      ('밥', 2, 'headword, noun'),
      ('던전', 10000000, 'headword, custom');
    INSERT INTO card (lemma_id, state, interval, due, reps, updated_at) VALUES
      (2, 2, 21, 100, 5, 111),
      (10000000, 1, 1, 90, 1, 222),
      (424242, 1, 1, 90, 1, 250);
    INSERT INTO tombstone (tbl, pk, updated_at) VALUES ('card', '3', 333);
    INSERT INTO meta (k, v, updated_at) VALUES ('dict.loadedBands', '[0]', 0);
  `);
  await migrate(db);
});
afterEach(async () => {
  await db.close();
});

describe("migration 6", () => {
  it("moves each card to its word's stable id, keeping its schedule and stamp", async () => {
    const rice = await db.get<{ interval: number; reps: number; updated_at: number }>(
      "SELECT interval, reps, updated_at FROM card WHERE lemma_id = ?",
      [lemmaId("밥", "noun")],
    );
    expect(rice).toEqual({ interval: 21, reps: 5, updated_at: 111 });
    expect(await db.get("SELECT 1 FROM card WHERE lemma_id = 2")).toBeUndefined();
  });

  it("leaves a card it cannot name exactly as it was", async () => {
    expect(await db.get<{ updated_at: number }>("SELECT updated_at FROM card WHERE lemma_id = 424242")).toEqual({
      updated_at: 250,
    });
  });

  it("renames a card's tombstone with it", async () => {
    const graves = await db.all<{ pk: string; updated_at: number }>(
      "SELECT pk, updated_at FROM tombstone WHERE tbl = 'card'",
    );
    // As JSON, once migration 9 has written keys in protocol 2's form.
    expect(graves).toEqual([{ pk: JSON.stringify([lemmaId("책", "noun")]), updated_at: 333 }]);
  });

  it("turns a custom lemma into a custom_word, stamped when its card was made", async () => {
    expect(await db.all("SELECT headword, pos, gloss, updated_at FROM custom_word")).toEqual([
      { headword: "던전", pos: "noun", gloss: "dungeon", updated_at: 222 },
    ]);
    const id = lemmaId("던전", "noun");
    expect(await db.get("SELECT headword, source FROM lemma WHERE id = ?", [id])).toEqual({
      headword: "던전",
      source: "custom",
    });
    expect(await db.get("SELECT form FROM surface WHERE lemma_id = ?", [id])).toEqual({ form: "던전" });
    expect(await db.get<{ updated_at: number }>("SELECT updated_at FROM card WHERE lemma_id = ?", [id])).toEqual({
      updated_at: 222,
    });
  });

  it("drops the shipped dictionary, so the bands reload under the new ids", async () => {
    expect(await db.all("SELECT headword FROM lemma WHERE source <> 'custom'")).toEqual([]);
    expect(await db.get("SELECT v FROM meta WHERE k = 'dict.loadedBands'")).toBeUndefined();
  });

  it("stamps nothing", async () => {
    for (const tbl of ["card", "custom_word", "tombstone", "meta"]) {
      const row = await db.get<{ top: number | null }>(`SELECT max(updated_at) AS top FROM ${tbl}`);
      expect(row?.top ?? 0, tbl).toBeLessThanOrEqual(333);
    }
  });
});