[Interest] SQLite

[Graham Labdon]

Thanks

-----Original Message-----
From: interest-bounces+graham.labdon=avalonsciences.com at qt-project.org [mailto:interest-bounces+graham.labdon=avalonsciences.com at qt-project.org] On Behalf Of André Somers
Sent: 27 May 2015 09:52
To: interest at qt-project.org
Subject: Re: [Interest] SQLite

Graham Labdon schreef op 27-5-2015 om 09:39:
> Hi
> I am planning to use a SQLite database in my application to store application data.
> The application is large and complex so we are taking a phased delivery approach. This will mean that the structure of the database will change over time and that we need to provide backwards compatibility.
> So, what I need is some way to create a versioning system and a way of converting old format databases to the current format.
> I would be grateful if anyone could suggest a good approach to this
>
> Thanks
> _______________________________________________
> Interest mailing list
> Interest at qt-project.org
> http://lists.qt-project.org/mailman/listinfo/interest
We do it manually. We keep a data version number in the database properties, which you can set and get via a pragma. Then, we have a list of updater objects that can update a database from one version to the/a next using a script. For every database change we need to make, we up the version number we expect, update the code to create a new database and write an updater script from the previous version. We run the scripts we need to update to the version expected by the software at startup. So, if you run the software against a version 7 database and the software expects version 10, it will go through the scripts looking for a script that updates version 7. The updater that takes a version 7 then updates to version 8, so the software will look for a script that takes a version 8 database. This updater can then update to version 10 in for instance, skipping version 9 that may have been faulty in some sense. It is quite easy to extend and reasonably flexible in terms of making it possible to create scripts that skip version numbers if that makes sense, but not make it mandatory to write a separate script between each possible version.

Note that we don't support going backwards. That sometimes causes problems when clients want to reinstall a previous version because of some regression. We save backups of the database when we upgrade to help us mitigate problems (we have had a faulty database update script once, and that caused massive headaches to solve). A problem is that SQLite only supports a small subset of the DDL. Changing tables is hardly possible. That results in having to write more complicated scripts that create a new table with the modified structure, and then copy over the data from the old table, then delete the original table and rename the new table to the old one. That is a procedure in which it is easy to make a small mistake with big consequences. So I'd also advise to instigate a strict code review policy on any changes to database
structure: every change, no matter who made it, needs a code review.

André


_______________________________________________
Interest mailing list
Interest at qt-project.org
http://lists.qt-project.org/mailman/listinfo/interest