I'm managing a database with sqlite and I want to hold a general property describing the version of my application. This property will be updated for every database change. Is it possible?
Asked
Active
Viewed 55 times
0
-
1Sure, why not make a Prefs table that is key/value pairs? – Caius Jard Dec 02 '18 at 08:08
-
@CaiusJard Is it a common practice? – Sanich Dec 02 '18 at 08:09
-
[See here](https://stackoverflow.com/questions/7739444/declare-variable-in-sqlite-and-use-it) for a discussion relevant to your question. SQLite does not support variables. – Tim Biegeleisen Dec 02 '18 at 08:15
-
It actually sounds like you want a database migration tool. Take a look at Fluent Migrator for one such example – Caius Jard Dec 02 '18 at 08:45
2 Answers
2
If all you want to do is track an application version, Sqlite has a couple of pragmas for querying and setting integer values that are part of the database file format itself, intended for just such a thing.
APPLICATION_ID: This one is meant to be assigned an application-specific ID number that you can check to make sure the database is one created by your program and not something else.
USER_VERSION: This is probably what you'll want to use. I use it for the same purpose you're describing, incrementing it with every new table or whatever, so that if a database with an older schema version is loaded into a program that uses a newer one, it can then upgrade the database as appropriate.
Shawn
- 47,241
- 3
- 26
- 60
1
Is it possible?
Yes and is frequently used.
Android's SQLiteOpenHelper class (which you can subclass to) checks the version passed against the version stored.
If the version passed is higher than the version stored then the method (abstract so has to be overridden) onUpgrade is called.
If the version is lower then the onDowngrade method (not abstract but if not overridden results in an exception).
The value is stored in the user_version (offset 60-63 inclusive, in the database header, the first part of the database file) and is a 32bit signed integer.
It can be read by issuing PRAGMA user_versionand then extracted from the result set as column user_version.
It can be set by issuing PRAGMA user_version = ? (where ? is the number that you want it set to).
The highest value that it can be set to is 2147483647, the lowest is -2147483648 values outside of this range result in 0.
As an example :-
PRAGMA user_version = -2147483648;
PRAGMA user_version;
PRAGMA user_version = 2147483647;
PRAGMA user_version;
will result in the two requests to return the user_version (2nd an 4th lines) as per :-
and
The next 4 bytes in the header the application_id could be used in a similar way. It's intended to describe a file type akin to file extension like .doc, .pdf and so on and used when Sqlite is used as an application file format. This value being interrogated by the application which then acts accordingly reading storing and retrieving the data.
It's probably not a frequently used value so could also be used in the same way just with different PRAGMA's. PRAGMA application_id and PRAGMA application_id = ? (again where ? is a 32bit signed integer)
SQLite As An Application File Format
SQLite As An Application File Format - Benefits(short version)
MikeT
- 51,415
- 16
- 49
- 68