I need to write a DROP COLUMN routine for manipulating SQLite databases.
It would be called something like this:
dropColumn("SomeTable", "SomeColumn");
The SQLite FAQ says that to drop a column, you have to create a temporary table containing just the columns you want, and then copy the data across to it, and then rename it.
It shouldn't be too hard to encapsulate that into a routine. But it looks like it would be kind of annoying to write it.
Surely someone out there has written such a routine already. If so, can I steal it please? :)
DB Browser for SQLite allows you to add or drop columns. In the main view, tab Database Structure , click on the table name. A button Modify Table gets enabled, which opens a new window where you can select the column/field and remove it.
SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows these alterations of an existing table: it can be renamed; a column can be renamed; a column can be added to it; or a column can be dropped from it.
Right-click the column you want to delete and choose Delete Column from the shortcut menu. If the column participates in a relationship (FOREIGN KEY or PRIMARY KEY), a message prompts you to confirm the deletion of the selected columns and their relationships. Choose Yes.
First, we need to specify the table name. Next, after the CHANGE COLUMN clause, we have to specify the old column name and new column name along with its definition. We must have to specify the column definition even it will not change. Finally, we need to specify the FIRST or AFTER keyword.
Here's some pseudo code for you:
columnNameList = ""
newTableStr = "CREATE TABLE tempMyTable ("
execute statement: "PRAGMA table_info('MyTable')"
While looping through RecordSet
If RecordSet.name != tableRowToDeleteName
If columnNameList != "" Then columnNameList += ","
columnNameList += RecordSet.name
newTableStr += RecordSet.name + " " + RecordSet.type
If RecordSet.notnull Then
newTableStr += " NOT NULL"
End If
If RecordSet.dflt_value != "" Then
newTableStr += " DEFAULT(" + RecordSet.dflt_value + ")"
End If
If Not Last Record in RecordSet
newTableStr += ","
End If
End If
End Loop
newTableStr += ");"
execute statement: newTableStr
execute statement: "INSERT INTO tempMyTable (" + columnNameList + ")" +
"SELECT " + columnNameList + " FROM MyTable;"
Delete table: MyTable
Rename Table: tempMyTable to MyTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With