ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite
We've recently had the need to add columns to a few of our existing SQLite database tables. This can be done with ALTER TABLE ADD COLUMN . Of course, if the table has already been altered, we want to leave it alone. Unfortunately, SQLite doesn't support an IF NOT EXISTS clause on ALTER TABLE .
Our current workaround is to execute the ALTER TABLE statement and ignore any "duplicate column name" errors, just like this Python example (but in C++).
However, our usual approach to setting up database schemas is to have a .sql script containing CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS statements, which can be executed using sqlite3_exec or the sqlite3 command-line tool. We can't put ALTER TABLE in these script files because if that statement fails, anything after it won't be executed.
I want to have the table definitions in one place and not split between .sql and .cpp files. Is there a way to write a workaround to ALTER TABLE ADD COLUMN IF NOT EXISTS in pure SQLite SQL?
I have a 99% pure SQL method. The idea is to version your schema. You can do this in two ways:
Use the 'user_version' pragma command ( PRAGMA user_version ) to store an incremental number for your database schema version.
Store your version number in your own defined table.
In this way, when the software is started, it can check the database schema and, if needed, run your ALTER TABLE query, then increment the stored version. This is by far better than attempting various updates "blind", especially if your database grows and changes a few times over the years.
One workaround is to just create the columns and catch the exception/error that arise if the column already exist. When adding multiple columns, add them in separate ALTER TABLE statements so that one duplicate does not prevent the others from being created.
With sqlite-net, we did something like this. It's not perfect, since we can't distinguish duplicate sqlite errors from other sqlite errors.
Dictionary<string, string> columnNameToAddColumnSql = new Dictionary<string, string>
{
{
"Column1",
"ALTER TABLE MyTable ADD COLUMN Column1 INTEGER"
},
{
"Column2",
"ALTER TABLE MyTable ADD COLUMN Column2 TEXT"
}
};
foreach (var pair in columnNameToAddColumnSql)
{
string columnName = pair.Key;
string sql = pair.Value;
try
{
this.DB.ExecuteNonQuery(sql);
}
catch (System.Data.SQLite.SQLiteException e)
{
_log.Warn(e, string.Format("Failed to create column [{0}]. Most likely it already exists, which is fine.", columnName));
}
}
SQLite also supports a pragma statement called "table_info" which returns one row per column in a table with the name of the column (and other information about the column). You could use this in a query to check for the missing column, and if not present alter the table.
PRAGMA table_info(foo_table_name)
http://www.sqlite.org/pragma.html#pragma_table_info
链接地址: http://www.djcxy.com/p/19802.html