My entire environment, java, js, and php are set up with our continuous integration server (Hudson). But how do I get out database into the mix?
I would like to deploy fresh MySql databases for unit testing, development, and qa.
And then I'd like to diff development against production and have an update script that would be used for releasing.
Isn't HyperSQL in-memory DB (http://hsqldb.org/) better for running your tests?
You could write a script in Ant to do all that stuff and execute it during the build.
I would look at liquibase (http://www.liquibase.org/). It's a open source java based db migration tool that can be integrated into your build script and can handle db diffing. I've used it before to manage db updates on a project before with a lot of success.
Write a script that sets up your test database. Run it from your build tool, whatever that is, before your build tests run. I do this manually and it works pretty well; still integrating it into maven. Shouldn't be too much trouble.
For managing migrations to your database sechema between releases, you could do a lot worse than to use Scala migrations: http://opensource.imageworks.com/?p=scalamigrations
It's an open source tool that I've found to integrate well in a Java development ecosystem, and has extra appeal if any of your team have been looking at ways to introduce Scala.
It should also be able to build you a database from scratch, for testing purposes.
Give http://code.google.com/p/mysql-php-migrations/ a try! Very PHP oriented, but seems to work well for most purposes.