[Interest] SQLite

[Graham Labdon]

Hi Bo
It would be really interesting to see your sources and I would be grateful if you could email them to me

Thanks

Graham

-----Original Message-----
From: Bo Thorsen [mailto:bo at vikingsoft.eu] 
Sent: 27 May 2015 21:56
To: interest at qt-project.org; Graham Labdon
Subject: Re: [Interest] SQLite

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