I'm using SQLite as application data file format for our software product line, and being compatible in both directions is important for us (and honored by our users). I've only recently moved to SQLite, so the thoughts listed here haven't stood the test of time yet, but I'd assume they are helpful nonetheless.
-------- 1. Add a version scheme and reject changes of the major version in the very first software you release. Instead of user_version, I am using a major.minor field, and reject (in the first to the current release) everything that is different in major version. Note that a future release can always opt to transparently support older versions. I've also added a text field containing an optional message displayed when the database is rejected. This allows future software versions to "send" instructions to the users how they could upgrade (e.g. where to download a conversion tool). -------- 2. If you can't reject the database, you still have to deal with the cases: - the schema of the file is older than the one the application knows - the schema of the file is newer than the one the application knows In the first case (code "knows" the old schema) the easiest way is to upgrade the schema automatically when the database is opened. This way, all the "different version" support is conveniently located in one place, and doesn't creep into the entire database layer. If the database is opened only for reading, or the upgrade would break previous versions of the software, I could still fall back to adjusting some queries on the fly. From my past experience, that would be r In the second case, the database has a scheme you don't know. You can only trust the newer application to not have broken anything for you. It is fairly easy to allow future versions to add columns, though: - SELECT statements need to query for explicit columns, rather than using SELECT * - All columns added in later versions need a default value, so they don't need to be specified in INSERT's (That probably won't help for complex queries). -------- 3. Reformat on the fly There's another option to simplify handling old data that might help in some cases: when opening the database, you can create a temporary copy of key tables, and reformat / update that temporary copy. For tables that see complex changes, don't hold large amounts of data and are mostly read from, this again moves the versioning problems to a single point, a lot of the remaining code can work as if the database was of the newer format. (You would still need to handle the different versions for non-SELECT statements, or maybe provide a generic mechanism that writes back the changes made to the in-memory copy). --------- 4. Maintenance Operation I've added a "Maintenance" operation that is motivated for the user as "try this first if the database behaves strangely". This operaiton can transparently make some changes. ----- Use case: After Schema 1.0, I've added the ability to de-duplicate the data held in large-ish blobs. For this I have added a "Hash" column that default-initializes to null. When opening an older database, it is only upgraded to contain this column. When storing a record, I calculate the hash of the new record and look for an existing record with the same hash. During Maintenance, I can calculate missing hashes, and fold duplicates of the existing data. Hope that gives you some ideas. -- View this message in context: http://sqlite.1065341.n5.nabble.com/Strategies-for-checking-the-version-of-a-database-tp64330p64407.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
