Using pragma user_version

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Using pragma user_version

Damian Slee
In the sqlite wiki below it describes the use of pragma user_version, but doesn't really say how to use it.  I'm evaluating the latest sqlite.exe.

Say on initial creation of me DB I set user_version to 1.  then product with DB schema 1 gets released.

Then at some point in the future I want to insert some data into tables only if user_version ==1.  then update user_version to 2.

I don't think the sqlite syntax supports?
IF (pragma.user_version == 1)
        INSERT into x

Do I have to query user_version from code instead, through sqlite API?


Q) On sqlite3_Open(), when the file with given filename doesn't exists, SQLite creates new database. But usually, the application needs to do some initialization work to be able to use this new database (create tables etc.) So, what is the most natural way to determine, that Open() created new database instead of opening the existing one?
A) Use some system API funtion like IsFileExists(DB_filename) before calling sqlite_open(DB_filename)
A) or, use the "user_version" pragma. Right after calling open(), do "pragma user_version". If it returns "0", then assume that this is a new file -- create your tables and do "pragma user_version=1" to mark that you've created your tables in this db. The next time you do "pragma user_version", it will return 1, signifying that you've previously set up this db.

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.322 / Virus Database: 266.11.17 - Release Date: 5/25/2005