How to preserve and upgrade SQLITE database during app upgrade?
 

Hi,

I'm developing two Qt based apps. Both of them rely on a local SQLITE database. The database is included in my installation package and everything works just fine. My problem is that the database includes a fair amount of data that will be generated by the users. How do I make sure to preserve the database when the users perform an 'upgrade' to newer versions of the apps?

I have looked into the postinst, preinst, prerm and postrm scripts and their documentations in: http://www.debian.org/doc/debian-pol...erscripts.html. Unfortunately, I still haven't figured out how to make use of them.

Another related problem is that with new version of my app, the structure of the database (tables and columns) as well as the default data (the rows) might get changed. I'd have to run 'upgrade scripts' on the users existing Db. Is there any best practice here? I can think of several approaches:

1. My app includes, in code, the necessary SQL statements to perform the upgrade.
2. I provide some script file which will upgrade the database.
3. Before the upgrade of the app, I do an 'export' of the user configurable data which I subsequently import back into the new database delivered with the new version (catering for any structural changes).

One of the apps also has some files (stylesheets to make the UI somewhat user configurable). I want to be able to preserve them as well. Anyway, the solution would be the same.

Db and files all reside in an .appname folder.

Any ideas?

Regards,

Magnus

Re: How to preserve and upgrade SQLITE database during app upgrade?
 

You should be able to flag the files as configuration files to prevent them being replaced - see http://www.debian.org/doc/debian-pol...conffiles.html

As for the database updates - either 1 or 2 would work. It may depend on whether any user interaction would be required for the updates (e.g. to ask for default values for a new column), in which case #1 would definitely be better. You'd also have to make sure that whatever process you use can deal with updating across multiple versions (as users may not install all intervening versions).

Re: How to preserve and upgrade SQLITE database during app upgrade?
 

I'd say that the safest way to upgrade a DB is to export the data to a neutral format, change the database/create a new one and then import the data while taking care of the changes that need to be done.

Changing the DB structure without saving the data is to ask for problems. The only reason for doing changes to a DB with data is if temporary storage takes too much space.