Reactor Migrations

[Dutch Rapley]

On the drive home from work today, I was thinking about Reactor and migrations. In Rails speak, migrations is the way to handle changes to a database schema.

I've attached a couple draft files as an example to show how a migration would be defined.

There are several advantages to doing this:

1. Creating, dropping, and modifying tables would be database agnostic
2. No more writing RDBMS specific SQL for table maintenance
3. No more keeping track and managing sql files to manage changes
4. real version control for your database

Notice two things about the attached files:

1. Each file is prepended with a number. These numbers would be in chronological order. When you go to launch your application into production, a script will loop through the files in the cleverly named "migrations" directory and build your database.
2. in each migration, there are up and down nodes with children. The idea is that you can "freeze" your migrations and check them in with a 1.0 release of an application (for example 095_dbChanges.xml). Let's say you upgrade to 1.1 of the application (123_moreDbChanges.xml), but things didn't go so well. You could easily migrate your db back to version 095, and anything in the down node would make changes to your DB to take it back to that version. Typically, anything in down is the exact opposite of what you did in up for a given file.

Something else we will want to do is to also create migration files for databases that have been built, or applications already using Reactor. I'm also assuming that the dashboard may also be a good place to manage migrations.

Thoughts? I didn't come up with this on my own, the ideas are heavily borrowed from Rails. Also, nothing is set in stone in the xml tags. I can see us adding support for schema, owner, and datasource attributes to the table tag.

The real grunt work here would be writing the appropriate SQL for each RDBMS that we support. Something like this could com in *very* handy for working with built-in derby.

-Dutch

[Tom Chiverton]

2008/11/13 Dutch Rapley <dutch....@gmail.com>:

> Thoughts? I didn't come up with this on my own, the ideas are heavily
> borrowed from Rails. Also, nothing is set in stone in the xml tags. I can
> see us adding support for schema, owner, and datasource attributes to the
> table tag.

Reactor (2?) should add these 'real' ORM features, yup.

Maybe it could do with three 'sync modes' - bring all the DB changes
down and update reactor.xml, make the database match reactor.xml and
'both together'.
Hope I don't have to write those though :-)

--
Tom