89 lines
3.6 KiB
JavaScript
89 lines
3.6 KiB
JavaScript
import { sqlQuoteIdent as q } from "../../utils/sql-safe";
|
|
import buildCreateTableSql from "./build-create-table-sql";
|
|
import checkIfTableExists from "./check-if-table-exists";
|
|
import getTableColumns from "./get-table-columns";
|
|
import recordSchemaChange from "./record-schema-change";
|
|
import resolveTable from "./resolve-table";
|
|
/**
|
|
* # Update an existing table
|
|
* @description SQLite has limited ALTER TABLE support, so a changed table is
|
|
* rebuilt: create a temp table with the new schema, copy the data over,
|
|
* drop the old table and rename the temp table. Unchanged tables are left
|
|
* alone.
|
|
*/
|
|
export default function updateTable(params) {
|
|
const { db, db_schema, table } = params;
|
|
if (table.isVector) {
|
|
recreateVectorTable(params);
|
|
return;
|
|
}
|
|
/**
|
|
* SQLite keeps the `CREATE TABLE` text (with the renamed table name), so
|
|
* an identical statement means the table already matches the schema.
|
|
*/
|
|
const current = db
|
|
.query(`SELECT sql FROM sqlite_master WHERE type='table' AND name=?`)
|
|
.get(table.tableName);
|
|
if (current?.sql === buildCreateTableSql({ db_schema, table })) {
|
|
return;
|
|
}
|
|
const tempTableName = `${table.tableName}_temp_${Date.now()}`;
|
|
// Columns are matched by name only: SQLite converts values to the new
|
|
// column affinity on copy, so a type change keeps the data instead of
|
|
// silently dropping the column.
|
|
const existingColumns = getTableColumns({ db, table_name: table.tableName });
|
|
const schemaColumns = resolveTable({ table, db_schema }).fields.map((f) => f.fieldName);
|
|
const columnsToKeep = existingColumns
|
|
.map((c) => c.name)
|
|
.filter((name) => schemaColumns.includes(name));
|
|
const columnsToDrop = existingColumns
|
|
.map((c) => c.name)
|
|
.filter((name) => !schemaColumns.includes(name));
|
|
recordSchemaChange(params, {
|
|
description: `Rebuild table "${table.tableName}"`,
|
|
});
|
|
for (const column of columnsToDrop) {
|
|
recordSchemaChange(params, {
|
|
description: `Drop column "${table.tableName}"."${column}"`,
|
|
destructive: true,
|
|
});
|
|
}
|
|
// Create temp table with new schema
|
|
db.run(buildCreateTableSql({
|
|
db_schema,
|
|
table: { ...table, tableName: tempTableName },
|
|
}));
|
|
// Copy data if there are common columns
|
|
if (columnsToKeep.length > 0) {
|
|
const columnList = columnsToKeep.map(q).join(", ");
|
|
db.run(`INSERT INTO ${q(tempTableName)} (${columnList}) SELECT ${columnList} FROM ${q(table.tableName)}`);
|
|
}
|
|
db.run(`DROP TABLE ${q(table.tableName)}`);
|
|
db.run(`ALTER TABLE ${q(tempTableName)} RENAME TO ${q(table.tableName)}`);
|
|
}
|
|
/**
|
|
* Vector tables are only rebuilt when `recreate_vector_table` is set.
|
|
*/
|
|
function recreateVectorTable(params) {
|
|
const { db, db_schema, table, recreate_vector_table } = params;
|
|
if (!recreate_vector_table) {
|
|
return;
|
|
}
|
|
recordSchemaChange(params, {
|
|
description: `Recreate vector table "${table.tableName}"`,
|
|
});
|
|
let existingRows = [];
|
|
if (checkIfTableExists({ db, table_name: table.tableName })) {
|
|
existingRows = db
|
|
.query(`SELECT * FROM ${q(table.tableName)}`)
|
|
.all();
|
|
db.run(`DROP TABLE ${q(table.tableName)}`);
|
|
}
|
|
db.run(buildCreateTableSql({ db_schema, table }));
|
|
for (const row of existingRows) {
|
|
const columns = Object.keys(row);
|
|
const placeholders = columns.map(() => "?").join(", ");
|
|
db.run(`INSERT INTO ${q(table.tableName)} (${columns.map(q).join(", ")}) VALUES (${placeholders})`, Object.values(row));
|
|
}
|
|
}
|