One of the more interesting problems I've had the opportunity to address this year at $work has been database schema migration.
Database schema migration itself is not interesting, there's various ways to do it, some better than others.
What makes it interesting for me is the constraints the solution needed to work under.
For starts, this is an Oracle database, and a big one at that.
It weighs in 100-200gig on disk, with pretty almost all the data being active data and very little archival of log data.
There are 163 tables with 313 indexes, 71 triggers, and about 10 PL/SQL packages, some of which are pretty damned large (one implements a highly complex search engine for some very specific structural data).
It holds canonical authentication data, some financial data, order data, and supports on-the-fly generation of custom products.
It's mission critical enough that the storage the database runs on has been allocated a private fibre dark fibre strand across the Sydney harbour bridge to the disaster recovery site, ensuring every single disk block write that hits the disk is provably written simultaneously to physical platters on either side of the city.
Most interesting though, the deployment of new releases occurs in a 4 hour window, but it takes 15 hours to fully restore the database from backups. If a new software release fails (which typically happens as we hit peak load) and the database has to be rolled back, it has to do so WITHOUT losing any data that has been added to the data since it was rolled forwards.
The current implementation of our migration system, built over the last year, takes most of these issues into account.
The migration process is conceptually based on ActiveRecordMigration. Or at least, it is to the extend that it implements version control for schemas.
History begins at the point that a schema_info table was created to store the schema metadata (we've declared there was no history before that point) and moves forwards.
Each schema changes comes as a pair of patches (forwards and backwards). All changes must be logical changes and each pair must be able to round-trip itself.
A few subtleties also allow From and To versions, allowing situations like having 3 or 4 forward scripts to set up a new structure, but have one rollback patch to clean up all of them.
So far, mostly the same as ActiveRecordMigration.
Where we differ is that where ActiveRecordMigration are logical abstracted changes are written in Ruby, we define each patch in raw SQL (for maximum control). This allows not just PL/SQL changes, but also to shift tables between table spaces, allow the DBAs to provide some performance tweaking patch pairs, and so on.
Perl script patches are supported, but only raw DBI code is permitted. The main reason for this is that your object model is going to be evolving, but your patches describe all of known history. If you need to bring a 2 year old instance forwards 5 or 10 releases (which could easily involve 50 schema patches) you can't allow the migration at old points in history to be influenced by the current code structure. Data and code MUST be controlled separately from each other.
For each migration, the migration planner will attempt to resolve a migration plan from the current schema version to the target destination. For convenience, we support things like HEAD (further increasing the similarity to version control).
Since the new migration system was implemented, we've had a practically flawless migration history.
The plan is to donate this migration system back to CPAN, once it's complete.
Missing features include additional pre and post checks on the database integrity, and (more exciting) branch traversal support.
Schema branch support will involve expanding the schema metadata to include not only the version, but also the root of the svn tree for the code that matches the database.
On something like the test server, when a release is pushed out that has a different branch to the current schema branch, the migration planner will examine BOTH the patch directory for the current branch and the patch directory for the target branch.
It will then compare the two and discover the Last Common Ancestor (the most recent patch pair that is common between the two branches) and then produce a migration plan that rolls backwards on the current branch to the last common ancestor, and then forwards down the target branch to the destination version.
We see this as a key part of improving our development process, as is makes the process of implementing new code on branches SIGNIFICANTLY easier, because now we can change the database on a branch as easily as we change the code and push these arbitrary branches to a test server. The 100gig database copies on the test servers will then just Do The Right Thing and move themselves to the state they need to.
As an initial (VERY limited) release of this migration system, I've created ORLite::Migrate.
ORLite::Migrate provides a highly simplified implementation of our schema migration system, specifically intended to work with the ORLite light weight ORM system.
The intended use for this migration system is, of course, Padre.
Padre uses ORLite to implement a limited ORM layer over the embedded SQLite database that holds Padre's persistent data. Until now, we haven't been able to use the full ORM functionality because there was no good way to upgrade the database to gain new tables or columns.
From 0.22 Padre should start taking advantage of ORLite::Migrate to automatically upgrade the SQLite database whenever you upgrade Padre itself.
With upgrade issues solved, we can start using the full ORM functionality, which will greatly improve the usability in the Padre database layer.
Following on from ORLite::Migrate, some time next year (once we've got branches working) I hope to release the full implementation of our database migration system to CPAN. But for now, this limited implementation should make upgrading embedded databases a LOT easier.
(This is still not The Sekrit)
That sounds very intresting.
And pretty similar to what we're doing at work. But your handling of changes in different branches seems better. We basically use numbered migration levels and then sort out renumbering when merging to trunk.
That's a bit annoying when also dealing with maintenance branches.
Re:Cool!
Alias on 2008-12-16T00:33:41
We still need to do some renumbering when we merge to trunk.
We manage the problem (not solve, just manage) BASIC-style by incrementing the SQL version by 100 each release.
If someone it working on a branch, they'll having their own area of the 100...
migrate-2250-2251.sql
migrate-2251-2252.sql
(and so on for another 10 patches)They may need to rename the first two numbers onto the final version for the release that they end up merged onto, but at least they don't need to do so while they are living on the branch.
