SQLite drop column routine
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? :)
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
精彩评论