updated the alter table with the correct 12-step generalized alter table procedure (TBF)

Gioee · Gioee · commit db6ab437c8bf · 2024-01-30T14:58:31.000+01:00
diff --git a/src/manager.ts b/src/manager.ts
@@ -62,7 +62,7 @@ export class SQLiteManager {
         query += ');'
       } else {
         if (typeof op != 'undefined' && column) {
-          let rand: string
+          let oldname: string
           switch (op) {
             case AT.RENAME_TABLE:
               this.query += 'ALTER TABLE "' + this.table.name + '" RENAME TO "' + column.name + '";\n'
@@ -77,14 +77,27 @@ export class SQLiteManager {
               if (newColumn) this.query += 'ALTER TABLE "' + this.table.name + '" RENAME COLUMN "' + column.name + '" TO "' + newColumn + '";\n'
               break
             default:
-              rand = Math.floor(Math.random() * 1000000000000).toString()
               this.query += '\n\nPRAGMA foreign_keys = OFF;\n'
               this.query += 'BEGIN TRANSACTION;\n'
-              this.query += 'ALTER TABLE "' + this.table.name + '" RENAME TO sqlitemanager_temp_table_' + rand + ';\n'
+              this.query += 'SELECT type, sql FROM sqlite_schema WHERE tbl_name=' + this.table.name + ';\n'
               this.create = true
+              oldname = this.table.name
+              this.table.name = 'new_' + this.table.name
               this.query += this.queryBuilder()
               this.create = false
-              this.query += '\nDROP TABLE sqlitemanager_temp_table_' + rand + ';\n'
+              this.query += '\nINSERT INTO "' + this.table.name + '" SELECT * FROM "' + oldname + '";\n'
+              this.query += 'DROP TABLE "' + oldname + '";\n'
+              this.query += 'ALTER TABLE "' + this.table.name + '" RENAME TO "' + oldname + '";\n'
+              this.table.name = oldname
+              /*          
+TODO  
+Use CREATE INDEX, CREATE TRIGGER, and CREATE VIEW to reconstruct indexes, triggers, and views associated with table X. Perhaps use the old format of the triggers, indexes, and views saved from step 3 above as a guide, making changes as appropriate for the alteration.
+
+If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.
+
+If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints.
+
+*/
               this.query += 'COMMIT;\n'
               this.query += 'PRAGMA foreign_keys = ON;\n'
 
diff --git a/test/manager.test.ts b/test/manager.test.ts
@@ -59,10 +59,10 @@ describe('Create a table', () => {
 
     expect(risCh).toContain('PRAGMA foreign_keys = OFF;')
     expect(risCh).toContain('BEGIN TRANSACTION;')
-    expect(risCh).toContain('ALTER TABLE "' + testTable.name + '" RENAME TO sqlitemanager_temp_table_')
-    expect(risCh).toContain('CREATE TABLE "' + testTable.name + '" ("')
+    expect(risCh).toContain('ALTER TABLE "new_' + testTable.name + '" RENAME TO "' + testTable.name + '";')
+    expect(risCh).toContain('CREATE TABLE "new_' + testTable.name + '" ("')
     expect(risCh).toContain('"' + testTable.columns[2].name + '" TEXT')
-    expect(risCh).toContain('DROP TABLE sqlitemanager_temp_table_')
+    expect(risCh).toContain('DROP TABLE "' + testTable.name)
     expect(risCh).toContain('COMMIT;')
     expect(risCh).toContain('PRAGMA foreign_keys = ON;')
 
