Friday, January 21, 2011

Android SQLite Alter Table

When upgrading SQLite database sometimes you need to change the tables scheme. The problem is that SQLite Alter Table SQL format does not support all the options you might need (specifically - dropping columns, changing column types, renaming columns).

One way around this issue is to create a new table & copy the data to the new table. There are few options here, but the best way (I think) is as follows:
1. Create new table in the new format.
2. Copy the data.
3. Drop the original table
4. Rename the new table

For example:
BEGIN TRANSACTION;
CREATE TABLE t_new(a,b);
INSERT INTO t_new SELECT a,b FROM t;
DROP TABLE t;
ALTER TABLE t_new RENAME TO t;
COMMIT;


IMPORTANT: As far as I know 'execSQL' of 'SQLiteDatabase' will NOT run multiple commands. You should run each SQL command in a separate 'execSQL' call (the transaction can be handled from the SQLiteDatabase, not need for explicit SQL commands).

No comments: