Code: Select all
command DBDropColumnForSQLite databaseID, tableName, columnName
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
// create the temporary table
delete item itemNumber in createTableSQL
if (char -1 of createTableSQL <> ")") then put ")" after createTableSQL
replace tableName with tableName & "_backup" in createTableSQL
replace "CREATE TABLE" with "CREATE TEMPORARY TABLE" in createTableSQL
revExecuteSQL databaseID, createTableSQL
// insert the data from the original table into the temporary table
local columnNames
put revDatabaseColumnNames(databaseID, tableName) into columnNames
put itemOffset(columnName, columnNames) into itemNumber
delete item itemNumber in columnNames
put "INSERT INTO " & tableName & "_backup SELECT " & columnNames & " FROM " & tableName into sql
revExecuteSQL databaseID, sql
// drop the original table
put "DROP TABLE " & tableName into sql
revExecuteSQL databaseID, sql
// recreate the original table (dbdMainTable)
replace "CREATE TEMPORARY TABLE" with "CREATE TABLE" in createTableSQL
replace tableName & "_backup" with tableName in createTableSQL
revExecuteSQL databaseID, createTableSQL
// insert the data back into the main table
put "INSERT INTO " & tableName & " SELECT " & columnNames & " FROM " & tableName & "_backup" into sql
revExecuteSQL databaseID, sql
// delete the temporary table
put "DROP TABLE " & tableName & "_backup" into sql
revExecuteSQL databaseID, sql
end if
end if
end try
end DBDropColumnForSQLite