modify column for SQLite [CODE]

[Emily-Elizabeth]

This will change the data type of a column.
It takes the original table, renames it to 'tmp', changes the data type of the column, recreates the table, copies the data from the tmp table to the new table and then deletes the tmp table.

Code: Select all

command DBModifyColumnForSQLite databaseID, tableName, columnName, columnDataType
   try
      if (_DBIsOpen(databaseID)) then
         local sql
         put "select sql from sqlite_master where type = 'table' and name ='" & tableName & "'" into sql
         local createTableSQL
         put revDataFromQuery(,, databaseID, sql) into createTableSQL
         local itemNumber
         put itemOffset(columnName, createTableSQL) into itemNumber
         if (itemNumber > 0) then  // if it is 0 then deleting a column that wasn't saved to the table
            local columnNames
            put revDatabaseColumnNames(databaseID, tableName) into columnNames
            // rename the existing table to "tmp"
            DBTableRename databaseID, tableName, "tmp"
            // insert the data from the original table into the temporary table
            put "INSERT INTO tmp SELECT " & columnNames & " FROM " & tableName into sql
            revExecuteSQL databaseID, sql
            // recreate the original table
            put QUOTE & columnName & QUOTE && columnDataType into item itemNumber of createTableSQL
            revExecuteSQL databaseID, createTableSQL
            // insert the data back into the main table
            put "INSERT INTO " & tableName & " SELECT " & columnNames & " FROM tmp" into sql
            revExecuteSQL databaseID, sql
            // delete the temporary table
            put "DROP TABLE tmp" into sql
            revExecuteSQL databaseID, sql
         end if
      end if
   end try
end DBModifyColumnForSQLite

[Emily-Elizabeth]

Just a quick update

Code: Select all

command DBModifyColumnForSQLite databaseID, tableName, columnName, columnDataType
   try
      if (_DBIsOpen(databaseID)) then
         local sql
         put "select sql from sqlite_master where type = 'table' and name ='" & tableName & "'" into sql
         local createTableSQL
         put revDataFromQuery(,, databaseID, sql) into createTableSQL
         local itemNumber
         put itemOffset(columnName, createTableSQL) into itemNumber
         if (itemNumber > 0) then  // if it is 0 then deleting a column that wasn't saved to the table
            local columnNames
            put revDatabaseColumnNames(databaseID, tableName) into columnNames
            // rename the existing table to "tmp"
            DBTableRename databaseID, tableName, "tmp"
            // recreate the original table
            put QUOTE & columnName & QUOTE && columnDataType into item itemNumber of createTableSQL
            if (character -1 of createTableSQL <> ")") then put ")" after createTableSQL
            revExecuteSQL databaseID, createTableSQL
            // insert the data from the original table into the temporary table
            put "INSERT INTO tmp SELECT " & columnNames & " FROM " & tableName into sql
            revExecuteSQL databaseID, sql
            // insert the data back into the main table
            put "INSERT INTO " & tableName & " SELECT " & columnNames & " FROM tmp" into sql
            revExecuteSQL databaseID, sql
            // delete the temporary table
            put "DROP TABLE tmp" into sql
            revExecuteSQL databaseID, sql
         end if
      end if
   end try
end DBModifyColumnForSQLite