[Interest] SQLite

[Bo Thorsen]

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.

I have a similar approach to this, except that I code generate the db 
code. Some tables can be generated based on the contents of other 
tables, so those are just wiped when upgrading a table to a new version. 
For those that can't be generated, I have to implement an upgrade function.

I have been meaning to clean this up a bit and allow others to use it, 
but so far haven't found the time.

Graham (or anyone else who might be interested), if you want to see it, 
I could send you the sources for the code generator.

Bo.

-- 
Viking Software
Qt and C++ developers for hire
http://www.vikingsoft.eu