Yeah, I know, I should write more blog entries, but i'm still not used to it, but this small "snippet" of code i definitly want to share. I'm farly new to DBIx::Class and thought, it should be farly easy to make something to automatically upgrade the database on changing of the schema. In my last job at work where I worked 2 months in a row, while learning all the stuff about Catalyst, DBIx::Class and many other Perl Modules, I dont want to list on right here.

But inside that development I had lots of changes on the schema, cause of course while changing usage and the plan how to solve the details, i added rows, removed them and so on. Till yesterday i usually took the evil approach to just kill the DB, and let the Application recreate it. Which is actually not a bad concept, just has the disadvantage that if you are at a specific testcase you will lose all your data. DBIx::Class can be very pissy when a row is missing, cause it totally suggest this cols exist, so if you modify the scheme you need to modify the DB somehow. To directly point to a real solution, many DBIx::Class people told me to use DBIx::Class::DeploymentHandler, but still it doesnt help you totally on that problem of instant database upgrades.

Taking the chance of the visit of ribasushi I asked him, about that problem, with that question in final: "This should be only 4-5 lines of code, right?". On Perl not surprisingly, he answered me "yeah its actually really just 4 lines" ;). Ok with the "stuff around" its not really 4 lines, but the "magical command" that does the stuff we need, is 4 lines ;).

So less talking, more code:

#!/usr/bin/perl
use MyApp::Schema;
use SQL::Translator::Diff;
use SQL::Translator;

{
    package MyApp::SchemaOld;
    use base qw/DBIx::Class::Schema::Loader/;
}
my $schema = MyApp::Schema->connect( 'dbi:mysql:' . $ENV{DB_DATABASE} . ':' . $ENV{DB_HOSTPORT},
    $ENV{DB_USERNAME}, $ENV{DB_PASSWORD}, );
my $old_schema = MyApp::SchemaOld->connect( 'dbi:mysql:' . $ENV{DB_DATABASE} . ':' . $ENV{DB_HOSTPORT},
    $ENV{DB_USERNAME}, $ENV{DB_PASSWORD}, );
my $diff = SQL::Translator::Diff::schema_diff(
    SQL::Translator->new(
        parser      => 'SQL::Translator::Parser::DBIx::Class',
        parser_args => { package => $old_schema }
      )->translate(),
    $old_schema->storage->sqlt_type,
    SQL::Translator->new(
        parser      => 'SQL::Translator::Parser::DBIx::Class',
        parser_args => { package => $schema }
      )->translate(),
    $schema->storage->sqlt_type,
    {
        ignore_constraint_names => 1,
        ignore_index_names      => 1,
        caseopt                 => 1,
        no_comments             => 1
    }
);
for (@diff) {
    next if m/^-- /;
    $schema->storage->dbh_do(
        sub {
            my ( $self, $dbh ) = @_;
            $dbh->do($_);
        }
    );
}

WARNING: Its to - from not from - to Sadly SQL::Translator has some bugs, which we workaround with this check for comments. Also we must take in concern that the do method on the dbh of the DBIx::Class cant handle multiply statements in one string. P.S.: yes i will change away from YAML as storage for my blog ;).

What I need to say more :). Have fun!