How to use DBIx::Class::Migration

There is a tutorial (note: links are broken), but this is a cheat sheet:

Use case A: Developing - initial setup

Step 0: Set up a database instance

Use dbdeployer, it's great.

Step 1: Generate the DBIx::Class Result classes

• dbicdump
• dbicdump -o dump_directory=lib -o components='["InflateColumn::DateTime"]' Smurf::Foo::DB 'dbi:mysql:database=foo;host=127.0.0.1;port=8022;user=msandbox;password=msandbox'
• ...this will create:
• lib/Smurf/Foo/DB.pm
• Manually add: our $VERSION = 1; to this at the end.
• lib/Smurf/Foo/DB/Result/Table1.pm, etc.

Step 2: Generate the DBIx::Class::Migration files

• dbic-migration prepare
• dbic-migration -I lib prepare --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'
• ...this will create:
• share/migrations/_source/deploy/1/001-auto.yml
• share/migrations/_source/deploy/1/001-auto-__VERSION.yml
• share/migrations/MySQL/deploy/1/001-auto-__VERSION.sql
• share/migrations/MySQL/deploy/1/001-auto.sql
• share/fixtures/1/conf/all_tables.json

Notes and gotchas

• If you see a definition for dbix_class_deploymenthandler_versions anywhere, i.e. in lib/Smurf/Foo/DB/Result/DbixClassDeploymenthandlerVersion.pm then you're gonna have a bad time. This could mean you accidentally ran dbicdump after dbic-migration prepare. It will cause migrations to fail when they try to create the dbix_class_deploymenthandler_versions table from both 001-auto-__VERSION.sql and 001-auto.sql

Use case B: Making changes to the database

Step 3: Make changes and update files

• Do not write any SQL
• Make changes to the DBIC classes under `lib/Smurf/Foo/DB/Result`, but DO NOT MODIFY THE FIRST PART OF THE FILE (see DBIx::Class::Schema::Loader comments in those files)
• Bump the version in `lib/Smurf/Foo/DB.pm` (or add it - at the bottom outside the auto-generated section: `our $VERSION = 2;`
• Run `dbic-migration prepare --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'` to create a new migration in the `share` directory
• Run `dbicdump -o dump_directory=lib -o components='["InflateColumn::DateTime"]' Smurf::Foo::DB 'dbi:mysql:database=foo;...etc'` to see if it makes any updates to the first part of the DBIC classes under `lib/Smurf/Foo/DB/Result`

Use case C: Not making changes to the database

Step 4: Install and upgrade as needed

• Run dbic-migration install --schema_class Smurf::Foo::DB --target_dir=share --dsn='dbi:mysql:database=foo;...etc'
• Run dbic-migration upgrade ...etc

Perl Postgres test fixtures

Test::PostgreSQL approach:

Test::DBIx::Class approach:

Lock rows with DBIx::Class

$schema->txn_do(sub{

    $foos_rs->search({}, {for => 'update'})->all; # Lock rows

    # Check status of something

    # Update it
});

(source, source, source)

How to implement a link table in DBIC

# Define many-to-many relationship between foo and bar tables
# using the foos_bars linking table

# in the database:


TABLE foo ( id INTEGER, something TEXT );

TABLE bar ( id INTEGER, another_thing TEXT );
TABLE foo_bar ( foo_id INTEGER, bar_id INTEGER );

# in Result/Foo.pm:

__PACKAGE__->many_to_many(
    'bars', # name of the relationship you're creating in foo
    'foos_bars', # name of the relationship in foo that points to the link table
    'bar' # name of the relationship to bar on the link table
);

# in some nearby code:

@bars = $foo->bars;


CPAN docs

Complex joins with DBIC in Perl

In a DBIx::Class ResultSet, sometimes you want to return all rows from aaa that have foreign keys to rows in bbb, that have foreign keys to rows in ccc.

This returns rows from ccc which is not what you want:

    return $_->search({
        some_id => { '!=' => undef },
    })
        ->search_related('aaa')
        ->search_related('bbb')
        ->search_related('ccc');


...so rather than doing it in a Perly way which returns an array not a resultset, not to mention many more queries than strictly necessary:

    return grep {
        $_->search_related('aaa')
            ->search_related('bbb')
            ->search_related('ccc')->all;
        } $self->search({
             some_id => { '!=' => undef },
        });


...instead do it the DBIC way, which returns a resultset, and is further chainable, etc.:

    return $self->search({
        'me.some_id' => { '!=' => undef },
        'ccc.some_other_id' => { '!=' => undef },
    }, {
        join => { 'aaa' => { 'bbb' => 'ccc' } }
    });


See docs: DBIx/Class/Manual/Joining.pod#COMPLEX_JOINS_AND_STUFF

See SQL commands that DBIx::Class is generating

Set environment variable:
    DBIC_TRACE=1

Pretty printing is available, as of DBIx::Class 0.08124:
    DBIC_TRACE_PROFILE=console DBIC_TRACE=1

(sources: CPAN docs, and a foolish manifesto blog)

For plain DBI calls, see also:
    DBI_TRACE=1

(source)

Handle the database with Test::Class

package My::Test;

use base 'Test::Class';

sub startup : Tests(startup => 1) {
    $schema = NAP::PRL::Schema->connect( $dsn, $user, $password );
}

sub setup : Test(setup) {
    $schema->txn_begin;
}

sub teardown : Test(teardown) {
    $schema->txn_rollback;
}

DBIx::Class basics

# SELECT COUNT(*) FROM product WHERE id = 104316
my $rs = $schema->resultset( 'Public::Product' )->search( { id => 104316 });
print "test: ".$rs->all;

Perl: Debugging DBIx::Class with DBI

If your code uses DBIx::Class, but you want to debug using DBI:

     while (my ($name, $s) = each %{{
         'MySQL' => ...->get_schema(),
         'SQLite' => ...->get_schema(),
     }}) {
         diag("\n\n>> $name schema\n\n");
         foreach my $table qw(a b c) {
             my $r;
             eval { $r = $s->storage->dbh->selectall_arrayref("SELECT * FROM $table"); };
             if ($@) { note("No table $table"); }
             else { note(scalar(@$r)." rows in $table"); }
         }
     }

How to use Catalyst to automatically create Perl DBIx::Class modules

UNTESTED:

1. install Catalyst

2. pretend like you're going to create a catalyst app

catalyst.pl MyFakeApp

3.

cd MyFakeApp
./script/myfakeapp_create.pl model MyModelName DBIC::Schema \
MyApp::SchemaClass create=static dbi:mysql:... user password


http://search.cpan.org/~rkitover/Catalyst-Model-DBIC-Schema-0.29/lib/Catalys
t/Helper/Model/DBIC/Schema.pm



one I did earlier

perl ./script/tagindexer_create.pl model RequestDB DBIC::Schema
TagIndexer::Schema create=static
'dbi:mysql:database=request;host=[ip address];port=3306' tagindexer
tagindexer