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
import { sqlite } from "https://esm.town/v/std/sqlite?v=6";
const tableName = "DateMeDocs";
const newTableName = tableName + "__new";
const archiveTableName = tableName + "__archive";
// 1. get old schema
const oldSchema = await sqlite.execute(`SELECT * FROM pragma_table_info('${tableName}')`);
const createTableStatement = `CREATE TABLE ${tableName} (
${oldSchema.rows.map((row) => `${row[1]} ${row[2]}`).join(",\n ")}
)`;
// console.log(createTableStatement);
/* CREATE TABLE DateMeDocs (
Name TEXT,
Profile TEXT, // below we will add primary key here (the point of the migration)
Gender TEXT,
InterestedIn TEXT,
Age INTEGER,
Location TEXT,
Style TEXT,
WantsKids TEXT,
LocationFlexibility TEXT,
Community TEXT,
Contact TEXT,
LastUpdated TEXT,
id TEXT
) */
// 2. create new schema
const newCreateTableStatement = `CREATE TABLE ${newTableName} (
Name TEXT,
Profile TEXT PRIMARY KEY,
Gender TEXT,
InterestedIn TEXT,
Age INTEGER,
Location TEXT,
Style TEXT,
WantsKids TEXT,
LocationFlexibility TEXT,
Community TEXT,
Contact TEXT,
LastUpdated TEXT,
id TEXT
)`;
// console.log(newCreateTableStatement);
// await sqlite.execute(newCreateTableStatement);
// 3. add the rows from the old table to the new table
// await sqlite.execute(`INSERT INTO ${newTableName} SELECT * FROM ${tableName}`);
// test out all is well
// console.log(await sqlite.execute(`SELECT * FROM ${newTableName} limit 2`));
// 4. archive the old table
// await sqlite.execute(`ALTER TABLE ${tableName} RENAME TO ${archiveTableName}`);
// 5. rename the new table to the old table
// await sqlite.execute(`ALTER TABLE ${newTableName} RENAME TO ${tableName}`);