Migrate Val Town SQLite

import { sqlite } from "https://esm.town/v/std/sqlite/main.ts";

// The app's tables, parent tables first, and any views it uses.
const TABLES = ["links"];
const VIEWS: string[] = [];
const PAGE_SIZE = 500;

// Scoped databases return rows as objects, account-wide ones as arrays.
// Every query here selects one column, so take the first value either way.
async function column(sql: string, args: unknown[] = []): Promise<unknown[]> {
  const result = await sqlite.execute({ sql, args });
  return result.rows.map((row: unknown) =>
    Array.isArray(row) ? row[0] : Object.values(row as object)[0],
  );
}

const quoteName = (name: string) => `"${name.replaceAll('"', '""')}"`;

async function* exportSql(): AsyncGenerator<string> {
  yield "PRAGMA foreign_keys=OFF;\nBEGIN;\n";
  for (const table of TABLES) {
    const [create] = await column(
      "SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = ?",
      [table],
    );
    if (typeof create !== "string") throw new Error(`Table not found: ${table}`);
    if (/^CREATE VIRTUAL/i.test(create)) throw new Error(`${table} is a virtual table`);
    yield `${create};\n`;

    // Generated columns aren't in table_info, so they're rebuilt, not copied.
    const columns = (await column(
      "SELECT name FROM pragma_table_info(?) ORDER BY cid",
      [table],
    )) as string[];
    const names = columns.map(quoteName).join(",");
    const values = columns.map((name) => `quote(${quoteName(name)})`).join(" || ',' || ");
    const insert = `'INSERT INTO ${quoteName(table).replaceAll("'", "''")}(${names.replaceAll("'", "''")}) VALUES(' || ${values} || ');'`;

    let count = 0;
    for (let offset = 0; ; offset += PAGE_SIZE) {
      const lines = await column(
        `SELECT ${insert} FROM ${quoteName(table)} ORDER BY rowid LIMIT ? OFFSET ?`,
        [PAGE_SIZE, offset],
      );
      for (const line of lines) yield `${line}\n`;
      count += lines.length;
      if (lines.length < PAGE_SIZE) break;
    }
    yield `-- ${table}: ${count} rows\n`;
  }

  const placeholders = TABLES.map(() => "?").join(",");
  for (const sql of await column(
    `SELECT sql FROM sqlite_schema WHERE type IN ('index', 'trigger')
       AND sql IS NOT NULL AND tbl_name IN (${placeholders})`,
    TABLES,
  )) yield `${sql};\n`;
  for (const view of VIEWS) {
    const [sql] = await column("SELECT sql FROM sqlite_schema WHERE type = 'view' AND name = ?", [view]);
    if (typeof sql !== "string") throw new Error(`View not found: ${view}`);
    yield `${sql};\n`;
  }

  const hasSequence = (await column(
    "SELECT count(*) FROM sqlite_schema WHERE name = 'sqlite_sequence'",
  ))[0];
  if (Number(hasSequence) > 0) {
    for (const table of TABLES) {
      const [seq] = await column("SELECT seq FROM sqlite_sequence WHERE name = ?", [table]);
      if (seq !== undefined) {
        yield `UPDATE sqlite_sequence SET seq = ${Number(seq)} WHERE name = '${table.replaceAll("'", "''")}';\n`;
      }
    }
  }
  yield "COMMIT;\n-- export complete\n";
}

export default async function (request: Request): Promise<Response> {
  const token = Deno.env.get("SQLITE_EXPORT_TOKEN");
  if (!token || request.headers.get("authorization") !== `Bearer ${token}`) {
    return new Response("Unauthorized", { status: 401 });
  }
  const encoder = new TextEncoder();
  const iterator = exportSql();
  const body = new ReadableStream({
    async pull(controller) {
      try {
        const { value, done } = await iterator.next();
        if (done) controller.close();
        else controller.enqueue(encoder.encode(value));
      } catch (error) {
        controller.enqueue(encoder.encode(`\n-- export failed: ${error}\n`));
        controller.close();
      }
    },
  });
  return new Response(body, {
    headers: { "content-type": "application/sql; charset=utf-8", "cache-control": "no-store" },
  });
}

import { createHash } from "node:crypto";
import { createClient } from "@libsql/client";

const [url, ...tables] = process.argv.slice(2);
const db = createClient({ url, authToken: process.env.TURSO_AUTH_TOKEN, intMode: "string" });
const PAGE_SIZE = 1000;

for (const table of tables) {
  const name = `"${table.replaceAll('"', '""')}"`;
  const hash = createHash("sha256");
  let count = 0;
  let after = null;
  for (;;) {
    const result = await db.execute({
      sql: `SELECT rowid AS __rowid, * FROM ${name}
            WHERE ? IS NULL OR rowid > ? ORDER BY rowid LIMIT ?`,
      args: [after, after, PAGE_SIZE],
    });
    for (const row of result.rows) {
      const values = result.columns.slice(1).map((column) => {
        const value = row[column];
        return value instanceof ArrayBuffer ? Buffer.from(value).toString("base64") : value;
      });
      hash.update(`${JSON.stringify(values)}\n`);
    }
    count += result.rows.length;
    if (result.rows.length < PAGE_SIZE) break;
    after = result.rows.at(-1).__rowid;
  }
  console.log(table, count, hash.digest("hex"));
}