src/schema.ts

Functions

Imports

It imports src/types.ts. Imported by src/backup.ts, src/drill.ts, src/dump.ts, src/index.ts, src/restore.ts.

Source

1import type { D1Like } from "./types.js";
2
3// What a dump holds, read from sqlite_master rather than typed by each site, so a
4// table added by a migration is dumped without anyone editing a list.
5
6export interface SchemaEntry {
7  type: "table" | "index" | "trigger" | "view";
8  name: string;
9  tbl_name: string;
10  sql: string;
11}
12
13// internal: the text lives in the FTS table's own shadow tables, so the FTS table is
14//   dumped through its columns and restored by inserting them.
15// external: content='<table>', so the text is in an ordinary table the dump already
16//   holds, and the restore rebuilds the index from it.
17// contentless: content='', so SQLite keeps no text at all. Nothing can be dumped; the
18//   restore creates the table empty and reports it.
19export type FtsMode = "internal" | "external" | "contentless";
20
21export interface FtsTable {
22  name: string;
23  mode: FtsMode;
24  content?: string;
25}
26
27export interface Plan {
28  // Ordinary tables, parents before the tables whose foreign keys name them.
29  tables: string[];
30  fts: FtsTable[];
31  // The FTS5 shadow tables left out of the dump; SQLite creates them with their table.
32  shadow: string[];
33  // Everything the restore creates, shadow tables and SQLite's own left out.
34  schema: SchemaEntry[];
35}
36
37const FTS5_SHADOW_SUFFIXES = ["_data", "_idx", "_content", "_docsize", "_config"];
38
39export function quoteIdent(name: string): string {
40  return `"${name.replace(/"/g, '""')}"`;
41}
42
43// SQLite's own tables and Cloudflare's (_cf_KV, _cf_METADATA): neither is the site's
44// data, and D1 refuses reads of the second.
45function isInternal(name: string): boolean {
46  return name.startsWith("sqlite_") || name.startsWith("_cf_");
47}
48
49function ftsMode(sql: string): { mode: FtsMode; content?: string } {
50  const m = /\bcontent\s*=\s*(?:'((?:[^']|'')*)'|"((?:[^"]|"")*)"|([A-Za-z_]\w*))/i.exec(sql);
51  if (!m) return { mode: "internal" };
52  const content = (m[1] ?? m[2] ?? m[3] ?? "").replace(/''/g, "'").replace(/""/g, '"');
53  return content === "" ? { mode: "contentless" } : { mode: "external", content };
54}
55
56// Parents first, by the REFERENCES clauses in each CREATE TABLE, so a restore inserting
57// one table per batch never inserts a child before its parent. A cycle keeps the
58// schema's own order for the tables in it.
59function parentsFirst(tables: SchemaEntry[]): string[] {
60  const names = new Set(tables.map((t) => t.name));
61  const deps = new Map<string, Set<string>>();
62  for (const t of tables) {
63    const refs = new Set<string>();
64    for (const m of t.sql.matchAll(/\bREFERENCES\s+(?:"((?:[^"]|"")+)"|`([^`]+)`|\[([^\]]+)\]|([A-Za-z_]\w*))/gi)) {
65      const ref = (m[1] ?? m[2] ?? m[3] ?? m[4]).replace(/""/g, '"');
66      if (ref !== t.name && names.has(ref)) refs.add(ref);
67    }
68    deps.set(t.name, refs);
69  }
70  const order: string[] = [];
71  const placed = new Set<string>();
72  let progress = true;
73  while (order.length < tables.length && progress) {
74    progress = false;
75    for (const t of tables) {
76      if (placed.has(t.name)) continue;
77      if ([...(deps.get(t.name) ?? [])].every((d) => placed.has(d))) {
78        order.push(t.name);
79        placed.add(t.name);
80        progress = true;
81      }
82    }
83  }
84  for (const t of tables) if (!placed.has(t.name)) order.push(t.name);
85  return order;
86}
87
88export async function readPlan(db: D1Like): Promise<Plan> {
89  const { results } = await db
90    .prepare("SELECT type, name, tbl_name, sql FROM sqlite_master WHERE sql IS NOT NULL ORDER BY rowid")
91    .all<SchemaEntry>();
92  const entries = results.filter((e) => !isInternal(e.name) && !isInternal(e.tbl_name));
93
94  const fts: FtsTable[] = [];
95  const shadow = new Set<string>();
96  for (const e of entries) {
97    if (e.type !== "table" || !/^\s*CREATE\s+VIRTUAL\s+TABLE/i.test(e.sql)) continue;
98    // Any other virtual table (rtree, fts3, fts4) is refused rather than skipped: the
99    // dump would otherwise report success without it.
100    if (!/\bUSING\s+fts5\s*\(/i.test(e.sql)) throw new Error(`d1-dump: ${e.name} is a virtual table that is not FTS5; this package dumps FTS5 only`);
101    fts.push({ name: e.name, ...ftsMode(e.sql) });
102    for (const suffix of FTS5_SHADOW_SUFFIXES) shadow.add(e.name + suffix);
103  }
104  const ftsNames = new Set(fts.map((f) => f.name));
105  const schema = entries.filter((e) => !shadow.has(e.name) && !(e.type !== "table" && shadow.has(e.tbl_name)));
106  const ordinary = schema.filter((e) => e.type === "table" && !ftsNames.has(e.name));
107  return {
108    tables: parentsFirst(ordinary),
109    fts,
110    shadow: entries.filter((e) => e.type === "table" && shadow.has(e.name)).map((e) => e.name),
111    schema,
112  };
113}