[odb-users] Support for database schema versioning/evolution

[Boris Kolpackov]

User 'kurige' at reddit/r/cpp posted[1] the following comment that
I am reposting here:

Better and better! Christmas has come early this year! Seriously, though,
keep up the good work - you guys are amazing. (On a related note: Is it
"you guys," plural? Or is CodeSynthesis a one-man-shop?)

First off, a bit of a correction. Apparently my terminilogy is mistaken.

"a database supports schema evolution if it permits modification of the
 schema without the loss of extant data; in addition, it supports schema
 versioning if it allows the querying of all data through user-definable
 version interfaces. ... we will consider the schema evolution as a special
 case of the schema versioning where only the current schema version is
 retained."[2]

So, what we are referring to is schema evolution.

I guess what I had in mind was not necessarily a full-blown schema evolution
system, but rather a set of "helper" classes and functions that would let me
manually write upgrade/downgrade scripts in C++ rather than in SQL, making it
completely independent of any specific backend database.

See here for example [3].

If you do end up going down the rabbit-hole, then at a minimum, whatever
versioning system you end up developing needs to be able to support the
following operations:

    * Add column
    * Drop column
    * Change column attributes
    * Change column name
    * Add class
    * Drop class
    * Change class name

Simply being able to add columns to existing tables is not enough. It's
essential that any versioning system support all of the above. If it
doesn't, then the first time I need to perform a modification to the
database that the ODB versioning doesn't support I'll be unable to use
the ODB versioning system from that point forward since the physical
database and the ODB generated model of the database will be in an
irreconcilable state on client's machines.

To complicate matters, there's no elegant solution to delete columns/tables
using "#pragma" directives withought adding quite a bit of unsightly cruft
to my header files, since even "deleted" columns would need to remain in
my header indefinitely with a "#pragma" above them indicated that they,
in fact, no longer exist.

A possible workaround would be for ODB to automagically create (at user's
request) and maintain a "version history" header that keeps track of all
changes made to a schema each time ODB is run. This could detect
additions/removals from the schema, modifications to column types, and
changes to the names of tables/columns via the db column('foo') and db
object table('bar') pragmas.

When I say automagically... I'm not kidding about the magic part. What
form that "version history" header file would take is a bit of a mystery
to me, but you might be able to use the semantic approach described in [2].


[1] http://www.reddit.com/r/cpp/comments/dkwwj/odb_compilerbased_orm_for_c/c111r9t

[2] "A semantic approach for schema evolution and versioning in object-oriented databases"
    http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.19.7241&rep=rep1&type=pdf

[3] "Active Record Migrations"
    http://api.rubyonrails.org/classes/ActiveRecord/Migration.html