Android Question SQLite db & ALTER TABLE

[luke2012]

Hi to all & hi @Erel ,
I need to add a new column to an existing table in a SQLite production db (db reside on local user's device and the app is published on Play Store).

Can I run the ALTER TABLE with ADD COLUMN command within activity_create event without any additional code ?

I have some questions about this :

1) ALTER TABLE with ADD COLUMN will be executed each time that activity_create event will be called. The SQLite DB engine will natively check if the column already exist and than ignore the command if the column already exist ?

2) ALTER TABLE with ADD COLUMN statement will introduce issues from the app's user side after the command execution ?

 

[Reviewnow]

SQLite does not support an if not exists for a column

SQLite 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.

http://www.sqlite.org/pragma.html#pragma_table_info

I would suggest also adding a versioning system to your SQLite db
if version = 1 then
' create new field
'update new version = 2
end if