[Interest] SQLite

[André Somers]

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é