[Cosmo-dev] Data migration for Cosmo 0.6

[Randy Letness]

Brian Moseley wrote:
> my thought has always been to bundle a separate sql script for each
> schema change, with a wrapper script to apply them all in sequence
> manually (probably determining order via convention for sql script
> names), a maven plugin to run that wrapper script when necessary for
> the build, and a java component that performs automatic upgrades at
> startup time by loading the sql scripts and running the contained
> commands.
>
The problem with using scripts is that you have to use the native tools 
to run them.  You can use JDBC to execute single statements, but there 
is no easy way to execute an entire script.  You have to parse each 
statement out somehow and execute them one by one.  Also, if you use 
scripts, unless you stick to standard SQL that works across all dbs, you 
will end up using db-specific SQL.  Sometimes migration can be a pain(or 
impossible) using plain ol SQL.  In past projects I've worked on, we've 
had to maintain two sets of scripts (one for Oracle and one for SQL 
Server) and we required that the native client tools be present on the 
machine running the migration.   We might end up having to do this.

-Randy