davewood

I used to work with DBIx::Class::Schema::Versioned to upgrade my DBIC Schema but soon I needed more then it offered.

Starting with DBIx::Class::DeploymentHandler was a bit troublesome because I had a hard-ish time understanding the extensive documentation.

Now that I moved past that phase I want to present my way of using DeploymentHandler and hopefully spare you some of the burden.

Note: I write my DBIC schema resultclasses by hand and deploy to whatever database system I need.

Feature list * each schema version should be installable to a clean/empty database * single step upgrades of schema versions * create pl scripts that do something on the schema before and/or after upgrade

 ./script/database.pl 
usage:
  database.pl --cmd prepare [ --from-version $from --to-version $to ]
  database.pl --cmd install [ --version $version ]
  database.pl --cmd upgrade
  database.pl --cmd database-version
  database.pl --cmd schema-version

Simply create your DBIC schema and once you are ready add

our $VERSION = 1;

to Schema.pm

Run

database.pl --cmd prepare

this command creates the following files

db_upgrades/
├── PostgreSQL
│   └── deploy
│       └── 1
│           ├── 001-auto.sql
│           └── 001-auto-__VERSION.sql
└── _source
    └── deploy
        └── 1
            ├── 001-auto-__VERSION.yml
            └── 001-auto.yml

Run

database.pl --cmd install

to deploy the schema to your database

Next we change the schema. add a column to a table and increase the schema version to 2.

then run

database.pl --cmd prepare --from-version 1 --to-version 2

the deployment directory now look like this:

db_upgrades/
├── PostgreSQL
│   ├── deploy
│   │   ├── 1
│   │   │   ├── 001-auto.sql
│   │   │   └── 001-auto-__VERSION.sql
│   │   └── 2
│   │       ├── 001-auto.sql
│   │       └── 001-auto-__VERSION.sql
│   └── upgrade
│       └── 1-2
│           └── 001-auto.sql
└── _source
    └── deploy
        ├── 1
        │   ├── 001-auto-__VERSION.yml
        │   └── 001-auto.yml
        └── 2
            ├── 001-auto-__VERSION.yml
            └── 001-auto.yml

Sometimes you want to do stuff with your schema before you change the DDL. With DBIx::Class::DeploymentHandler you can run SQL and or PL files before and after changing the DDL.

Since perl scripts are mostly independent of your choice of DBRMS it's best to put them in the special directory _common. Files from _common will be merged with the storage specific files So we have to make sure the file names reflect the order we want them exectuted in.

create directroy

mkdir -p db_upgrades/_common/upgrade/1-2/

create perl script

touch db_upgrades/_common/upgrade/1-2/001_do_stuff_BEFORE_ddl_change.pl

rename auto-generated sql file so the perl script is exectured before the DDL change

mv db_upgrades/PostgreSQL//upgrade/1-2/001-auto.sql db_upgrades/PostgreSQL//upgrade/1-2/002-auto.sql

db_upgrades/
├── _common
│   └── upgrade
│       └── 1-2
│           └── 001_do_stuff_BEFORE_ddl_change.pl
├── PostgreSQL
│   ├── deploy
│   │   ├── 1
│   │   │   ├── 002-auto.sql
│   │   │   └── 001-auto-__VERSION.sql
│   │   └── 2
│   │       ├── 001-auto.sql
│   │       └── 001-auto-__VERSION.sql
│   └── upgrade
│       └── 1-2
│           └── 001-auto.sql
└── _source
    └── deploy
        ├── 1
        │   ├── 001-auto-__VERSION.yml
        │   └── 001-auto.yml
        └── 2
            ├── 001-auto-__VERSION.yml
            └── 001-auto.yml

Here is an example script 001dostuffBEFOREddl_change.pl

#!/usr/bin/env perl
use strict;
use warnings;

use DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator::ScriptHelpers 'schema_from_schema_loader';

schema_from_schema_loader(
    { naming => 'current' },
    sub {
        my ( $schema, $versions ) = @_;
        # do stuff with $schema
    }
);

and finally my database.pl file

#!/usr/bin/env perl

use strict;
use warnings;
use 5.010;
use DBIx::Class::DeploymentHandler;
use feature qw/ switch /;
use Getopt::Long;

my $cmd = '';
my $from_version;
my $to_version;
my $version;
GetOptions(
    'command|cmd|c=s' => \$cmd,
    'from-version=i'  => \$from_version,
    'to-version=i'    => \$to_version,
    'version=i'       => \$version,
);

sub usage {
    say <<'HERE';
usage:
  database.pl --cmd prepare [ --from-version $from --to-version $to ]
  database.pl --cmd install [ --version $version ]
  database.pl --cmd upgrade
  database.pl --cmd database-version
  database.pl --cmd schema-version
HERE
    exit(0);
}

my $schema = '...'; # wherever you get your schema from.
my $deployment_handler_dir = './db_upgrades'

my $dh = DBIx::Class::DeploymentHandler->new(
    {   schema           => $schema,
        script_directory => $deployment_handler_dir,
        databases        => 'PostgreSQL',
        force_overwrite  => 1,
    }
);

die "We only support positive integers for versions."
    unless $dh->schema_version =~ /^\d+$/;

for ($cmd) {
    when ('prepare')          { prepare() }
    when ('install')          { install() }
    when ('upgrade')          { upgrade() }
    when ('database-version') { database_version() }
    when ('schema-version')   { schema_version() }
    default                   { usage() }
}

sub prepare {
    say "running prepare_install()";
    $dh->prepare_install;

    if ( defined $from_version && defined $to_version ) {
        say
            "running prepare_upgrade({ from_version => $from_version, to_version => $to_version })";
        $dh->prepare_upgrade(
            {   from_version => $from_version,
                to_version   => $to_version,
            }
        );
    }
}

sub install {
    if ( defined $version ) {
        $dh->install({ version => $version });
    }
    else {
        $dh->install;
    }
}

sub upgrade {
    $dh->upgrade;
}

sub database_version {
    say $dh->database_version;
}

sub schema_version {
    say $dh->schema_version;
}