A new versioned schema for DBIx::Class

Peter Mottram (SysPete) <peter@sysnix.com>

Perl::Dancer Conference 2014, Hancock NY, 9th October 2014

About me

  • Currently living in Malta
  • 24 years UNIX & Linux
  • Network security, Perl, e-commerce
  • Interchange6 development team member

"slightly less than twice the size of Washington, DC"

© CIA World Fact Book

Why version our schema?

  • Deploy specific version
  • Upgrades
  • Downgrades
  • Validation

Existing solutions

DBIx::Class::Schema::Versioned

  • Part of DBIx::Class distro
  • Uses DDL diff files
  • One sql file per upgrade/db engine
  • No downgrades
  • No Perl scripts allowed
  • Use DH instead

DBIx::Class::DeploymentHandler

  • Not part of DBIx::Class distro
  • Much more powerful than ::Schema::Versioned
  • Downgrades in addition to upgrades
  • Multiple sql files files per upgrade/downgrade/install
  • Perl scripts allowed for upgrade/downgrade/install
  • Logging
  • Population
  • Large footprint (Moose!)

DBIx::Class::Migration

  • Not part of DBIx::Class distro
  • Extends ::DeploymentHandler
  • Downgrades in addition to upgrades
  • Multiple sql files files per upgrade/downgrade/install
  • Perl scripts allowed for upgrade/downgrade/install
  • DBIx::Class::Fixtures
  • Testing (Test::DBIx::Class)
  • Standard workflows with supplied scripts
  • Extensive documentation
  • Larger footprint that DH
  • More rigid than DH

A new versioned schema for DBIx::Class

DBIx::Class::Schema::Versioned::Inline

Why?

  • I'm a Perl hacker - I don't want to mess with SQL
  • I want to see my version info within the schema definition
  • I don't want to have to carry around piles of schema diffs
  • Seemed like a good idea at the time

Features

  • Extends DBIx::Class::Schema::Versioned
  • Extensive use of SQL::Translator
  • Version information stored in schema definition
  • No DDL diff files
  • Perl scripts
  • One file stores all upgrade data changes
  • Candy
  • No downgrades (yet)
  • No validation (yet)

MyApp::Schema

Without versioning



package MyApp::Schema;

use base 'DBIx::Class::Schema';

our $VERSION = '0.002';

__PACKAGE__->load_namespaces();

1;

  

With versioning



package MyApp::Schema;

use base 'DBIx::Class::Schema::Versioned::Inline';

our $FIRST_VERSION = '0.001';
our $VERSION = '0.002';

__PACKAGE__->load_namespaces();

1;

  

Schema definition

  • versioned
  • since
  • until (till)
  • renamed_from
  • changes

since

Specifies the first version at which a table, column or relationship exists



package MyApp::Schema::Result::Foo;
use base 'DBIx::Class::Core';

__PACKAGE__->resultset_attributes({ versioned => { since => '0.002' } });

__PACKAGE__->add_columns(
    foos_id => { data_type => 'integer', is_auto_increment => 1 },
    name => { data_type => 'text' },
    bar => { data_type => 'integer', versioned => { since => '0.003' } },
);

__PACKAGE__->has_many(
    thingies => 'MyApp::Schema::Result::Thingy', foos_id,
    { versioned => { since => '0.003' }
);

__PACKAGE__->set_primary_key('foos_id');

  

until

Specifies the first version at which a table, column or relationship no longer exists



package MyApp::Schema::Result::Foo;
use base 'DBIx::Class::Core';

__PACKAGE__->resultset_attributes({ versioned => { until => '0.05' } });

__PACKAGE__->add_columns(
    foos_id => { data_type => 'integer', is_auto_increment => 1 },
    name => { data_type => 'text' },
    bar => { data_type => 'integer', versioned => { until => '0.04' } },
);

__PACKAGE__->has_many(
    thingies => 'MyApp::Schema::Result::Thingy', foos_id,
    { versioned => { until => '0.04' }
);

__PACKAGE__->set_primary_key('foos_id');

  

renamed_from

tables and columns only - NOT relationships



package MyApp::Schema::Result::Foo;
use base 'DBIx::Class::Core';
__PACKAGE__->table('foos');
__PACKAGE__->resultset_attributes({ versioned => { until => '0.05' } });

package MyApp::Schema::Result::Bar;
use base 'DBIx::Class::Core';
__PACKAGE__->table('bars');
__PACKAGE__->resultset_attributes(
    { versioned => { since => '0.05', renamed_from => 'foos' } });

__PACKAGE__->add_columns(
    bar  => { data_type => 'integer', versioned => { until => '0.6' } },
    biff => { data_type => 'integer', default_value => 42,
              versioned => { since => '0.6', renamed_from => 'bar' } },
);

  

changes

columns only



__PACKAGE__->add_columns(
    bar => {
        data_type => 'integer',
        versioned => {
            since     => '0.04',
            changes   => {
                '0.07' => { data_type => 'integer', default_value => 0 },
                '0.10' => { data_type => 'integer', default_value => 8 },
                '0.12' => {
                    data_type     => 'integer',
                    default_value => 8,
                    is_nullable   => 0,
                },
            }
        }
    }
};

  

Candy

Mix in some DBIx::Class::Candy goodness with some ::Versioned::Inline::Candy



package TestCandy::Schema::Candy;

use base 'DBIx::Class::Candy';

sub base { $_[1] || 'DBIx::Class::Core' }

sub autotable { 1 }

sub parse_arguments {
    my $self = shift;
    my $args = $self->next::method(@_);
    push @{$args->{components}}, 'Schema::Versioned::Inline::Candy';
    return $args;
}

1;

  

since with Candy



package MyApp::Schema::Result::Foo;
use MyApp::Schema::Candy;

since '0.002';

primary_column
    foos_id => { data_type => 'integer', is_auto_increment => 1 };
column name => { data_type => 'text' };
column bar  => { data_type => 'integer', since => '0.003' };

has_many
    thingies => 'MyApp::Schema::Result::Thingy', foos_id,
    { since => '0.003' };

  

since



package MyApp::Schema::Result::Foo;
use base 'DBIx::Class::Core';

__PACKAGE__->resultset_attributes({ versioned => { since => '0.002' } });

__PACKAGE__->add_columns(
    foos_id => { data_type => 'integer', is_auto_increment => 1 },
    name => { data_type => 'text' },
    bar => { data_type => 'integer', versioned => { since => '0.003' } },
);

__PACKAGE__->has_many(
    thingies => 'MyApp::Schema::Result::Thingy', foos_id,
    { versioned => { since => '0.003' }
);

__PACKAGE__->set_primary_key('foos_id');

  

until with Candy

Note: until candy for class is named till


package MyApp::Schema::Result::Foo;
use MyApp::Schema::Candy;

till '0.05';

primary_column
    foos_id => { data_type => 'integer', is_auto_increment => 1 };
column name => { data_type => 'text' };
column bar  => { data_type => 'integer', until => '0.04' };

has_many
    thingies => 'MyApp::Schema::Result::Thingy', foos_id,
    { until => '0.004' };

  

renamed_from with Candy



package MyApp::Schema::Result::Foo;
use MyApp::Schema::Candy;

till '0.05';

package MyApp::Schema::Result::Bar;
use MyApp::Schema::Candy;

since '0.05';
renamed_from 'Foo';

column bar  => { data_type => 'integer', until => '0.6' };
column biff => { data_type => 'integer', default_value => 42,
                 since => '0.6', renamed_from => 'bar' };
  

changes with Candy



column bar => {
    data_type => 'integer',
    since     => '0.04',
    changes   => {
        '0.07' => { data_type => 'integer', default_value => 0 },
        '0.10' => { data_type => 'integer', default_value => 8 },
        '0.12' => {
            data_type     => 'integer',
            default_value => 8,
            is_nullable   => 0,
        },
    }
};

  

Upgrades


DBIx::Class::Schema::Versioned::Inline::Upgrade


Keywords

  • before
  • after

Upgrade



package MyApp::Schema::Upgrade;
use base 'DBIx::Class::Schema::Versioned::Inline::Upgrade';
use DBIx::Class::Schema::Versioned::Inline::Upgrade qw/before after/;

after '0.001' => sub {
    # initial population
};

before '0.002' => sub {
    my $schema = shift;
    my $rset = $schema->resultset('Foo')->search({ height => undef});
    $rset->update({ height => 0});
};

after '0.002' => sub {
    # add some more data
};

  

Upgrade

Multiple subroutines for one version



package MyApp::Schema::Upgrade;
use base 'DBIx::Class::Schema::Versioned::Inline::Upgrade';
use DBIx::Class::Schema::Versioned::Inline::Upgrade qw/before after/;

after '0.002' => sub {
    my $schema = shift;
    $schema->resultset('Bar')->create({ weight => 20 });
};

after '0.002' => sub {
    my $schema = shift;
    # do more stuff after the previously-defined sub
};

  

Questions?

Peter Mottram (SysPete) <peter@sysnix.com>