editor/apps/tauri/src/lib/schema.test.ts

import { describe, it, expect, beforeAll } from "vitest"
import Database from "better-sqlite3"
import { SCHEMA_SQL } from "./schema"

let db: Database.Database

beforeAll(() => {
  db = new Database(":memory:")
  db.exec(SCHEMA_SQL)
})

describe("schema integrity", () => {
  it("creates pages table", () => {
    const result = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='pages'",
    ).get() as { name: string } | undefined
    expect(result?.name).toBe("pages")
  })

  it("creates blocks table", () => {
    const result = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='blocks'",
    ).get() as { name: string } | undefined
    expect(result?.name).toBe("blocks")
  })

  it("creates links table", () => {
    const result = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='links'",
    ).get() as { name: string } | undefined
    expect(result?.name).toBe("links")
  })

  it("creates blocks_fts virtual table", () => {
    const result = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='blocks_fts'",
    ).get() as { name: string } | undefined
    expect(result?.name).toBe("blocks_fts")
  })

  it("enforces pages.path primary key", () => {
    db.prepare("INSERT INTO pages (path, title, modified_at) VALUES ('a.md', 'A', 1000)").run()
    expect(() => {
      db.prepare("INSERT INTO pages (path, title, modified_at) VALUES ('a.md', 'A2', 2000)").run()
    }).toThrow()
  })

  it("creates all expected indices", () => {
    const indices = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%'",
    ).all() as { name: string }[]
    const names = indices.map((i) => i.name).sort()
    expect(names).toContain("idx_blocks_page")
    expect(names).toContain("idx_links_target")
    expect(names).toContain("idx_links_source")
    expect(names).toContain("idx_links_tag")
  })

  it("enforces link_type CHECK constraint", () => {
    db.prepare(
      "INSERT INTO pages (path, title, modified_at) VALUES ('p.md', 'P', 1000)",
    ).run()
    db.prepare(
      "INSERT INTO blocks (id, page_path, content, modified_at) VALUES ('b1', 'p.md', 'c', 1000)",
    ).run()
    expect(() => {
      db.prepare(
        "INSERT INTO links (source_block_id, source_page_path, target, link_type) VALUES ('b1', 'p.md', 'x', 'invalid-type')",
      ).run()
    }).toThrow()
  })

  it("enforces blocks.block_type CHECK constraint", () => {
    expect(() => {
      db.prepare(
        "INSERT INTO blocks (id, page_path, block_type, modified_at) VALUES ('b2', 'p.md', 'invalid-type', 1000)",
      ).run()
    }).toThrow()
  })
})