On Tue, Sep 18, 2012 at 6:38 AM, Rui Maciel <[email protected]> wrote:
> Say there is an application that relies on sqlite for its database needs, > and that from a version onward it starts to use a different database > schema. When faced with this type of change, it is always nice to have a > way to infer if the application's database corresponds to the old schema in > order to migrate it to the current format. > > Considering this, and according to your experience, what's the best > sqlite-friendly way to check for the version of a database schema? > Fossil (http://www.fossil-scm.org/) is the DVCS that manages SQLite. Fossil uses SQLite as its application file format. And so it needs to check the schema version whenever it starts up. The Fossil schema is divided into a core "content-schema" and an outer "aux-schema". The aux-schema changes much more frequently than the content-schema (though, the last schema change was on 2011-04-25). The version number and date of both schemas are stored in the CONFIG table, which also stores lots of other configuration information (such as the name of the project, user preferences, etc.) The content-schema never changes unless the aux-schema also changes, so in most cases it is only necessary to test for changes in the aux-schema. Only when running the routine that updates the database schema is it necessary to check the content-schema. The Fossil source code contains a #define AUX_SCHEMA that defines the current schema number. ( http://www.fossil-scm.org/fossil/artifact/55ec98db6d1?ln=45) Whenever Fossil tries to do something with the database that requires a specific schema version, it invokes http://www.fossil-scm.org/fossil/artifact/0c04b50c2f10a5?ln=1023-1030 to check the version number and throws an error if the schema version is incorrect. Some operations (ex: "fossil rebuild" and "fossil sql") do not require a specific version of the schema and so do not invoke the db_schema_is_outofdate() function. But most other operations do use this function to check that the database is up-to-date. The "fossil rebuild" command is what updates the schema to the latest. the rebuild_update_schema() function does the work of updating the schema. See http://www.fossil-scm.org/fossil/artifact/d266a029721?ln=84-158 for the latest implementation. All of the above is just an example. There are a gazillion other ways to do the same thing. But perhaps this example will give you some ideas. > > > Thanks in advance, > Rui Maciel > ______________________________**_________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
