SQLite 删除列例程

Question

我需要编写一个 DROP COLUMN 例程来操作 SQLite 数据库。

它会被称为这样的东西:

dropColumn("SomeTable", "SomeColumn");

SQLite FAQ 说要删除一个列，您必须创建一个只包含您想要的列的临时表，然后将数据复制到其中，然后重命名它。

将其封装到例程中应该不会太难。不过写起来好像有点烦人。

肯定有人已经编写了这样的例程。如果是这样，我可以偷吗？ :)

Answer 1

这里有一些伪代码:

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