Re: Updates/Changes to a database

On 7/12/07, imageguy <imageguy1206(at)gmail(dot)com> wrote:
> Are there pre-existing tools out there that does this sort of thing ??

Rails and Django -- two popular web development frameworks -- support
a simple mechanism for doing schema migrations.

In Rails, in particular, each schema change is encapsulated as a
class. Each such change is called a migration, and implements two
methods for effecting and rolling back the migration, respectively.

Since these are Ruby classes, they can do anything at all -- execute
SQL, flush caches, restart daemons, etc. Transactions ensure that each
migration is executed atomically.

All the migrations are then collected in a directory, and numbered:

$ ls -l db/migrate
...
-rw-r--r-- 1 alex alex 1691 Jun 28 15:21
163_send_dns_message_to_domain_owners.rb
-rw-r--r-- 1 alex alex 711 Jun 28 20:56 164_create_image_batches.rb
-rw-r--r-- 1 alex alex 1087 Jun 28 17:12 165_delete_some_dns_messages.rb
-rw-r--r-- 1 alex alex 970 Jul 2 14:39
166_add_reader_to_visitor_transistion.rb
-rw-r--r-- 1 alex alex 1267 Jul 2 15:33 170_create_indexes3.rb

In the database, a dedicated table is used to store the last applied
migration number. Rails itself provides a command that sets the
database to a specific migration number, allowing you to roll forward
and backward in the schema evolution.

I know somebody has released an independent schema migration tool
based on numbered SQL scripts, but I don't remember the name. Might be
of use to you. I recommend looking on SourceForge or FreshMeat.

Alexander.