2

I have an application written in C++ which uses an SQLite database to store information. I need a way of assigning a version number to the database. By this I mean, I need to be able to assign a version number to the state of the database, and if a new 'state' (version) is available, then I need to update the current database state to match the state of the updated version.

I am wondering whether it would be good practice to store the information required for this to happen in a table. I would need the version number, and then some way of storing the tables and their columns related to each version number. This would allow me to make comparisons etc.

I realise that this question Set a version to a SQLite database file is related, however it doesn't quite answer my question, as I am unsure if my approach is correct, and if so, how I go about achieving this.

All help is much appreciated.

Community
  • 1
  • 1
Mark
  • 459
  • 2
  • 12
  • It seems you want to add version to static data that you provide to the software. Is the data static and changes only when a new version is available? You don't update these tables at any other time? Must the software be running when you update the data? Can there be a break in the software operation when you update the data? How long can it be? – selalerer Apr 28 '14 at 09:01
  • The idea would be to check if the version is out of date when the application starts up, and then there is a possibility for the update to happen then if necessary. The tables are not changed at any other time in the application. Data is inserted/updated/deleted during the course of the application running, however this can all happen after an update to the schema has been applied. – Mark Apr 28 '14 at 09:06
  • So this is changes to the data model but not to the data? I think this can come in the form an SQL script (with many ALTER TABLE statements). The version of the script can be checked against a version in the database (in a control table you create for this purpose) and if the script version is larger, execute it. – selalerer Apr 29 '14 at 17:33

1 Answers1

2

Use PRAGMA user_version to read and store an integer value in the database file.

When the version in your code and database file are the same, do nothing. When they are different, upgrade/downgrade accordingly and update the version number.

Asteroids With Wings
  • 17,071
  • 2
  • 21
  • 35
laalto
  • 150,114
  • 66
  • 286
  • 303
  • Thanks, this has given me a nudge in the right direction, however I am unsure as to how I would use this. In terms of; does the user_version refer to the latest database version, or the out of date version, meaning that I would need to provide the new version number. Or, can it refer to whichever I choose? – Mark Apr 28 '14 at 09:39
  • It's the version of the database file. It may be put there by an older version of your code and you can use the number to detect what changes your code needs to do to the database file to bring it up to date. – laalto Apr 28 '14 at 09:43
  • @AsteroidsWithWings Sorry, I checked the link was still working, but didn't check if the anchor tag worked. Thanks for fxining it. – Falco Apr 07 '20 at 09:57
  • This solution isn't sufficient for workflows involving the `.dump` command (e.g. to track the database in version control in a format where diffs are useful), as the user_version pragma isn't included – snazzybouche Jul 20 '21 at 17:06