CODEWITHSUNDEEP
sqlite
dan-gph
dan-gph

Reputation: 16919

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? :)

Upvotes: 8

Views: 2354

Answers (1)

Sparafusile
Sparafusile

Reputation: 4966

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

Upvotes: 5

Related Questions