On 9/8/16, Stephen Chrzanowski <pontia...@gmail.com> wrote: > That said, since I know that the backup API will make the schema version > change, is it safe to read the schema_version at the beginning of the > application, retain it for the life time of the application, then after the > backup is written, rewrite the schema version to the backup?
That might work. But really, when you manually change "PRAGMA schema_version" you always run the risk that some reader will miss the fact that the schema has changed, fail to recompile a prepared statement, then run the obsolete prepared statement in a way that corrupts the database file. It is difficult to construct a scenario where that happens, but it is possible. Hence, while this is likely to work during testing, you could get occasional, unreproducible failures after deployment. If you want to manually keep track of your schema version, I suggest creating a separate table in the database schema specifically for this purpose. In the Fossil application (http://fossil-scm.org/), the database always contains a CONFIG table like this: CREATE TABLE config( name TEXT PRIMARY KEY, value CLOB, mtime INTEGER ) WITHOUT ROWID; This CONFIG table stores various configuration parameters, among which is a schema version number. Every time Fossil opens a new database file, it checks the schema version number to know how to deal with that particular database file and whether or not the schema needs to be upgraded. You can see this "Schema Version" on the antepenultimate status line at http://www.sqlite.org/src/stat for example. Fossil uses a date for the schema version, since the schema changes infrequently. You could use sequential numbers, if you prefer. The key point is that the "schema version" used by the application should be separate and distinct from the "schema version" used by SQLite internally. Note also that the application-level "schema version" only needs to change when there is an application-relevant change to the schema. So, for example, if the WITHOUT ROWID clause were omitted from the CONFIG table above, that would not require an application-level schema change, since the two table definitions are equivalent from the point of view of all queries. Likewise, the addition, modification, or deletion of indexes does not change the application-level schema version, since queries get the same result regardless of the available indexes. (Indexes do not change the result of queries, they only help to obtain that result faster.) Only schema changes that make a difference to application queries should change the application-level schema version. In contrast, the SQLite internal schema version needs to change whenever the database is VACUUM-ed or backed up, for technical reasons, as discussed in previous emails. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
