[Interest] SQLite

[Kurt Pattyn]

That is more or less the same mechanism I have been using to accomplish this.
But I only used one updater object, and a list of update scripts. The name of the scripts was marked with the version of the database schema, like update_v3.sql.
At startup the version of the current scheme is read from the database and compared with the latest version of the scripts. If there is no match, then the database is updated one version at a time using the scripts; e.g. if the database has version 2, and the latest version of the script is 4 (update_v4.sql), then the script update_v3.sql is executed, followed by update_v4.sql.


On 28 May 2015, at 20:31, Jason H <jhihn at gmx.com> wrote:

>>> Den 27-05-2015 kl. 10:51 skrev 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.