Forked from nbbaier/sqliteDump
Unlisted
Script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
import { Statement, StatementInstance } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { sqlite as sqliteAPI } from "https://esm.town/v/std/sqlite";
export async function sqliteDump({
tables = undefined,
subset = {},
sqlite = sqliteAPI,
allSchemas = false,
transformStatement = (statement) => statement,
}: SqliteDumpOptions = {}) {
const subsetNormalized = Object.fromEntries(
Object.entries(subset).map(([key, value]) => {
return [
key.toLowerCase(),
value,
];
}),
);
const allTableNames = (() => {
let res;
return () => {
res ??= (async () => {
const { rows } = await sqlite.execute(
`select name from sqlite_schema where type = 'table' and name not like 'sqlite_%'`,
);
return rows.map(row => row[0]);
})();
return res;
};
})();
const rowTables = tables !== undefined
? tables.filter(table => !table.match(/^sqlite_/i))
: (
await allTableNames()
);
const schemaTables = allSchemas
? await allTableNames()
: rowTables;
const toFetch = schemaTables.map(table => {
const tableLower = table.toLowerCase();
const TABLE = new StatementInstance(table);
return [
Statement`SELECT sql FROM sqlite_schema WHERE LOWER(name) = LOWER('${TABLE}')`,
!!rowTables.find(rowTable => rowTable.toLowerCase() === tableLower)
? subsetNormalized[tableLower] ?? Statement`select * from ${TABLE}`
: Statement`SELECT 1 WHERE FALSE`,
];
}).flat();
const results = await sqlite.batch(toFetch);
const statements: StatementInstance[] = [];
for (const table of schemaTables) {
const [{ rows: [[tableSchema]] }, { columns, rows }] = results.splice(0, 2);
const createStatement = new StatementInstance(tableSchema.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS"));
const tableName = new StatementInstance(table);
const insertStatements = generateInsertStatements({
tableName,
columns,
rows,
transformStatement,
});
statements.push(transformStatement(createStatement, tableName.sql), ...insertStatements);
}
return statements;
}
function generateInsertStatements({ tableName, columns, rows, transformStatement }: {
tableName: StatementInstance;
columns: StatementInstance[];
rows: any[][];
transformStatement: (statement: StatementInstance, table: string) => StatementInstance;
}): StatementInstance[] {
const columnNames = new StatementInstance(columns.join(", "));
let insertStatements = [];
for (const row of rows) {
const values = new StatementInstance(
row.map(v => "?").join(", "),
);
const insertStatement = Statement`INSERT INTO ${tableName} (${columnNames}) VALUES (${values})`;
insertStatements.push(transformStatement(new StatementInstance(insertStatement.sql, row), tableName.sql));
}
return insertStatements;
}
export type SqliteDumpOptions = {
tables?: string[];
subset?: {
[key: string]: StatementInstance;
};
sqlite?: {
Val Town is a social website to write and deploy JavaScript.
Build APIs and schedule functions from your browser.
Comments
Nobody has commented on this val yet: be the first!
March 9, 2024