// Upgrade path for local DBs written by pre-scope executor versions.//// These tests exercise both halves://   1. The detector correctly identifies DBs missing the `scope_id`//      column on `source`.//   2. The move-aside helper renames the file (plus WAL/SHM siblings)//      so a subsequent fresh `migrate()` can create the new shape. import { afterEach, beforeEach, describe, expect, it } from "@effect/vitest";import { Database } from "bun:sqlite";import { mkdtempSync, rmSync, existsSync, writeFileSync } from "node:fs";import { join } from "node:path";import { tmpdir } from "node:os";import { drizzle } from "drizzle-orm/bun-sqlite";import { migrate } from "drizzle-orm/bun-sqlite/migrator"; import {  importLegacySecrets,  isPreScopeSchema,  moveAsidePreScopeDb,  readLegacySecrets,} from "./db-upgrade"; const PRE_SCOPE_SCHEMA = `  CREATE TABLE source (    id TEXT PRIMARY KEY NOT NULL,    plugin_id TEXT NOT NULL,    kind TEXT NOT NULL,    name TEXT NOT NULL,    can_remove INTEGER DEFAULT 1 NOT NULL,    can_refresh INTEGER DEFAULT 0 NOT NULL,    can_edit INTEGER DEFAULT 0 NOT NULL,    created_at INTEGER NOT NULL,    updated_at INTEGER NOT NULL  );  CREATE TABLE tool (    id TEXT PRIMARY KEY NOT NULL,    source_id TEXT NOT NULL,    plugin_id TEXT NOT NULL,    name TEXT NOT NULL,    description TEXT NOT NULL,    created_at INTEGER NOT NULL,    updated_at INTEGER NOT NULL  );  CREATE TABLE secret (    id TEXT PRIMARY KEY NOT NULL,    name TEXT NOT NULL,    provider TEXT NOT NULL,    created_at INTEGER NOT NULL  );  CREATE TABLE blob (    namespace TEXT NOT NULL,    key TEXT NOT NULL,    value TEXT NOT NULL,    PRIMARY KEY (namespace, key)  );`; const SCOPED_SCHEMA = `  CREATE TABLE source (    id TEXT NOT NULL,    scope_id TEXT NOT NULL,    plugin_id TEXT NOT NULL,    kind TEXT NOT NULL,    name TEXT NOT NULL,    can_remove INTEGER DEFAULT 1 NOT NULL,    can_refresh INTEGER DEFAULT 0 NOT NULL,    can_edit INTEGER DEFAULT 0 NOT NULL,    created_at INTEGER NOT NULL,    updated_at INTEGER NOT NULL,    PRIMARY KEY (scope_id, id)  );`; const seed = (path: string, sql: string) => {  const db = new Database(path);  db.exec(sql);  db.close();}; let workDir: string; beforeEach(() => {  workDir = mkdtempSync(join(tmpdir(), "exec-dbup-"));}); afterEach(() => {  rmSync(workDir, { recursive: true, force: true });}); describe("isPreScopeSchema", () => {  it("returns true for a DB with a source table missing scope_id", () => {    const path = join(workDir, "data.db");    seed(path, PRE_SCOPE_SCHEMA);    expect(isPreScopeSchema(path)).toBe(true);  });   it("returns false for a DB whose source table already has scope_id", () => {    const path = join(workDir, "data.db");    seed(path, SCOPED_SCHEMA);    expect(isPreScopeSchema(path)).toBe(false);  });   it("returns false for a DB with no source table", () => {    const path = join(workDir, "data.db");    seed(path, "CREATE TABLE unrelated (x TEXT);");    expect(isPreScopeSchema(path)).toBe(false);  });   it("returns false when the DB file doesn't exist", () => {    expect(isPreScopeSchema(join(workDir, "missing.db"))).toBe(false);  });}); describe("moveAsidePreScopeDb", () => {  it("renames data.db + wal/shm siblings and returns the backup path", () => {    const path = join(workDir, "data.db");    seed(path, PRE_SCOPE_SCHEMA);    writeFileSync(`${path}-wal`, "wal-bytes");    writeFileSync(`${path}-shm`, "shm-bytes");     const backup = moveAsidePreScopeDb(path);    expect(backup).toMatch(/data\.db\.pre-scopes-\d+-[0-9a-f]{8}$/);    expect(existsSync(path)).toBe(false);    expect(existsSync(`${path}-wal`)).toBe(false);    expect(existsSync(`${path}-shm`)).toBe(false);    expect(existsSync(backup!)).toBe(true);    expect(existsSync(`${backup}-wal`)).toBe(true);    expect(existsSync(`${backup}-shm`)).toBe(true);  });   it("is a no-op when the DB already has the scoped schema", () => {    const path = join(workDir, "data.db");    seed(path, SCOPED_SCHEMA);    expect(moveAsidePreScopeDb(path)).toBeNull();    expect(existsSync(path)).toBe(true);  });   it("is a no-op when the DB doesn't exist yet (fresh install)", () => {    expect(moveAsidePreScopeDb(join(workDir, "missing.db"))).toBeNull();  });}); // Integration: the whole reason this helper exists — a pre-scope DB// must be recoverable via fresh drizzle migrations after the move.describe("move-aside + fresh migrate end-to-end", () => {  it("lets migrations run cleanly after an old DB is moved aside", () => {    const path = join(workDir, "data.db");    seed(path, PRE_SCOPE_SCHEMA);     const backup = moveAsidePreScopeDb(path);    expect(backup).not.toBeNull();     const db = new Database(path);    migrate(drizzle(db), {      migrationsFolder: join(__dirname, "../../drizzle"),    });    // migrate() should have produced the new schema — source now has scope_id.    const cols = db.prepare("PRAGMA table_info('source')").all() as ReadonlyArray<{      readonly name: string;    }>;    expect(cols.some((c) => c.name === "scope_id")).toBe(true);    db.close();  });}); describe("readLegacySecrets", () => {  it("returns all rows from a pre-scope DB's secret table", () => {    const path = join(workDir, "data.db");    seed(path, PRE_SCOPE_SCHEMA);    const db = new Database(path);    db.prepare("INSERT INTO secret (id, name, provider, created_at) VALUES (?, ?, ?, ?)").run(      "sec_1",      "GitHub Token",      "onepassword",      1700000000,    );    db.prepare("INSERT INTO secret (id, name, provider, created_at) VALUES (?, ?, ?, ?)").run(      "sec_2",      "Stripe",      "keychain",      1700000001,    );    db.close();     const rows = readLegacySecrets(path);    expect(rows).toHaveLength(2);    expect(rows[0]).toEqual({      id: "sec_1",      name: "GitHub Token",      provider: "onepassword",      createdAt: 1700000000,    });  });   it("returns [] when the DB has no secret table", () => {    const path = join(workDir, "data.db");    seed(path, "CREATE TABLE unrelated (x TEXT);");    expect(readLegacySecrets(path)).toEqual([]);  });   it("returns [] when the DB file doesn't exist", () => {    expect(readLegacySecrets(join(workDir, "missing.db"))).toEqual([]);  });}); describe("importLegacySecrets", () => {  // Set up a fresh DB with the new (scoped) `secret` shape to import into.  const createScopedDb = (path: string): Database => {    const db = new Database(path);    db.exec(`      CREATE TABLE secret (        id TEXT NOT NULL,        scope_id TEXT NOT NULL,        name TEXT NOT NULL,        provider TEXT NOT NULL,        created_at INTEGER NOT NULL,        PRIMARY KEY (scope_id, id)      );    `);    return db;  };   it("inserts rows stamped with the given scope id", () => {    const path = join(workDir, "data.db");    const db = createScopedDb(path);    importLegacySecrets(db, "scope_a", [      { id: "sec_1", name: "GH", provider: "onepassword", createdAt: 1 },      { id: "sec_2", name: "St", provider: "keychain", createdAt: 2 },    ]);    const rows = db      .prepare("SELECT id, scope_id, name, provider FROM secret ORDER BY id")      .all() as ReadonlyArray<{ id: string; scope_id: string; name: string; provider: string }>;    db.close();    expect(rows).toHaveLength(2);    expect(rows[0]).toEqual({      id: "sec_1",      scope_id: "scope_a",      name: "GH",      provider: "onepassword",    });    expect(rows[1].scope_id).toBe("scope_a");  });   it("is a no-op with an empty list", () => {    const path = join(workDir, "data.db");    const db = createScopedDb(path);    importLegacySecrets(db, "scope_a", []);    const count = (db.prepare("SELECT COUNT(*) as n FROM secret").get() as { n: number }).n;    db.close();    expect(count).toBe(0);  });   it("uses INSERT OR IGNORE so a second import of the same ids is a no-op", () => {    const path = join(workDir, "data.db");    const db = createScopedDb(path);    const rows = [{ id: "sec_1", name: "GH", provider: "onepassword", createdAt: 1 }];    importLegacySecrets(db, "scope_a", rows);    // If the user's already re-registered the secret via a different    // provider, the legacy row must NOT clobber it.    db.prepare(      "UPDATE secret SET provider = 'file' WHERE id = 'sec_1' AND scope_id = 'scope_a'",    ).run();    importLegacySecrets(db, "scope_a", rows);    const provider = (      db        .prepare("SELECT provider FROM secret WHERE id = ? AND scope_id = ?")        .get("sec_1", "scope_a") as { provider: string }    ).provider;    db.close();    expect(provider).toBe("file");  });});