This recent journal entry links to an article which asserts that changing data structures in a relational database is hard.
Pah!
As autarch points out regardless of how you store your data, a change to the structure of the data will require a change to the code that uses it.
People are often resistant to change, even when the change is a fix to something that's broken. Often this is simply due to inertia. Sometimes, especially in the IT world, it's due to a fear of breaking other stuff.
In my $work_project we aim to develop for the current requirements without trying to second-guess future requirements. In fact just about anything I can think of that we put in because we thought we'd need it in the future, turned out to be a mistake. We all make mistakes. Hopefully we learn from our mistakes. And if we embrace change rather than fearing it, hopefully we can fix our mistakes as they become apparent.
Our database schema is moderately complex. Our PostgreSQL database has nearly 200 inter-related tables and takes up around 60GB of storage. As we add new features to the application, data structures and relationships are added and updated. Often these updates will have a ripple effect where existing bits of code need to be changed as well. Do we fear change? No, we embrace it. Our development workflow and infrastructure has been built up over time, to support frequent change.
Regression tests are obviously a critical part of how we work. We have almost 2000 test scripts which we use like this:
Another critical element of our project workflow is our 'apply_patches.pl' script. A 'patch' in this context is a .sql file that can be fed into psql to update the database schema. A file naming scheme is used to ensure that patches get applied in a consistent order and to allow us to know what 'patch level' any system is up to.
The patches are applied automatically in the development environment when we run regression tests. They are also applied automatically when 'good' code is checked out in the staging environment. Our production migration process is scripted and it also applies patches automatically. By the time a patch has made its way through developer testing and staging deployment, the chances of it going wrong in a production migration are fairly slim. And because it's all automated, we can be confident that code changes and schema changes will remain in sync.
In the three years since the project went live, nearly 450 schema patches have been deployed. Obviously many of those are trivial inserts/updates/deletes to tables of 'constants' (or 'lookup' data), but patches which add/drop/alter tables number in the hundreds.
Fear of making a mistake can slow development. Providing an infrastructure which supports rapidly fixing mistakes has significantly improved our productivity.
It would be great if you could show us some of your code or point us to the tools you are using.
Re:show us your code!
grantm on 2006-09-28T07:57:14
There's a simplified version of our apply_patches script here.
It's built around the psql utility that comes with PostgreSQL. We could have used DBI of course, but the psql macro support (the \set command) has been very useful. It also allows us to debug a patch by piping it into psql directly.
The default behaviour is to query the installed patchlevel, search for patches of a 'higher' level and install any that are found.
The script uses command-line options to specify database host, database, and login details (the passwords live in the user's
.pgpass file). In practise, our version of the script is able to determine sensible defaults for all those from the environment in which the script is run so we almost never specify them. Our version of the script has since evolved to handle
.pl patches as well as .sql ones. There have been a few situations where having the power of Perl available at patch time has proven useful. Another implementation you might like to investigate is the migrations system that's part of Ruby-on-Rails. One major difference is that migrations are written in Ruby to make them database agnostic. Of course it does support running SQL from Ruby (which may be necessary to set up foreign keys and other things Railers disapprove of). Migrations also support the concept of 'undo', which in theory is useful in development (we just rebuild up to a specific patchlevel) but probably isn't in production.
Our script for applying patches in the staging environment is a fairly simple shell script that updates a CVS working copy to a specific tag and then runs apply_patches. The output from those processes is saved to a log file which is emailed to the development team if there are any errors.
Fear of making a mistake can slow development.
Indeed, I've seen the things people do to prevent making mistakes slow down development and ossify systems into unchangeable messes way too often. (Look at the languages people design to prevent bad developers from making mistakes, or change control requests to prevent customers from giving feedback after development has started.)
I used to work for a company that had similar concerns. Its database had over 1000 tables, most of them with 3 triggers on them, and hundreds of stored procedures. Dozens of database modification scripts were submitted each week.
When I started to work there, it was chaos. Developpers submitted scripts, and noone was really testing how well the scripts worked, usually leading to very frustrated field engineers (those applying the patches to the customers).
15 months later, no developer could even submit a database modification without me signing off on it. Then, between the development and testing environments we had two databases. Any patches submitted by a developer were applied against the first database. If there was any warning or error, the patch would be rejected and the second database would be dumped and restored over the first one. With no warnings or errors, the patches would be applied against the second database.
The quality of patches send to the clients improved dramatically. Of course, it was still far from perfect. If a patch would be rejected by a tester (or by the nightly regression test) the databases between development and testing would contain rejected patches. So, on a regular bases, submission of patches was suspended, the testing queue flushed and all areas (development, staging, testing, release) were synced.
