What is the best way to update sqlite schema and data on a software update?

Question

I'm including an SQLite database in a recent application I'm designing. This will sit in the application folder and will contain a mixture of stock and user data. It's being developed via .net and entity framework.

I'm wanting to know how to approach updating this database between application updates (say adding tables and fields) whilst also preserving user data client-side when the user updates the application. I'm assuming that if I just change the database schema and data development side and then just overwrite to client side .db file on update, then you'll loose all of the user data.

Any suggestions?

Answer 1

You basically need something to compare against in the live database against the new.

e.g. if adding a table then you could use SELECT * FROM sqlite_master WHERE name = your_new_table_name to see if the table already exists. However you could use the simpler CREATE TABLE IF NOT EXISTS ........

New columns, with limitations, you could use ALTER TABLE your_existing_tablename ADD COLUMN you_new_column_definition (existing data is kept, if provided a default value will be applied).

Limitations are :-

1. The column may not have a PRIMARY KEY or UNIQUE constraint.
2. The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
3. If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

4. If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.

   • Note also that when adding a CHECK constraint, the CHECK constraint is not tested against preexisting rows of the table. This can result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate CHECK constraints as they are added.

SQL As Understood By SQLite - ALTER TABLE

If the column already exists then the result would include a message saying that the column is a duplicate e.g. :-

ALTER TABLE ex01 ADD COLUMN col5 TEXT
> duplicate column name: col5
> Time: 0s

You could always extract the existing columns using PRAGMA table_info(your_existing_table_name); and only proceed if the column to be added is not one of the values in the name column of the result set.

• Note that the link above suggests other ways to handle more complex situations.

With Android the stock SQLiteDatabase methods utilise the user_version (bytes 60 63in the database header).) to hold the actual version of the database which is compared against a provided value. If the provided value is greater then the onUpgrade method is invoked.

PRAGMA Statements Database File Format

P.S. Asking Best can be lead to opinion based responses, sometimes casuing heated arguments and thus asking for the Best should be avodied. Rather ask for ways.