Large Community¶

Active Community¶

Responsive Community¶

I needed MSSQL order-by support; the community helped me add support

generally very welcoming of people willing to help

Cross DB¶

Basic CRUD¶

C - Create

R - Retrieve

U - Update

D - Delete

SQL: Create¶

 my $sth = $dbh->prepare('
    INSERT INTO books
    (title, author_id)
    values (?,?)
 ');

 $sth->execute( 'A book title', $author_id );

DBIC: Create¶

 my $book = $book_rs->create({
    title     => 'A book title',
    author_id => $author_id,
 });

See "create" in DBIx::Class::ResultSet

No need to pair placeholders and values

Automatically gets autoincremented id for you

Transparently uses INSERT ... RETURNING for databases that support it

SQL: Read¶

 my $sth = $dbh->prepare('
    SELECT title,
    authors.name as author_name
    FROM books, authors
    WHERE books.author = authors.id
 ');

 while ( my $book = $sth->fetchrow_hashref ) {
   say "Author of $book->{title} is $book->{author_name}";
 }

DBIC: Read¶

 my $book = $book_rs->find($book_id);

or

 my $book = $book_rs->search({ title => 'A book title' }, { rows => 1 })->next;

or

 my @books = $book_rs->search({ author => $author_id })->all;

or

 while( my $book = $books_rs->next ) {
   printf "Author of %s is %s\n", $book->title, $book->author->name;
 }

See "find" in DBIx::Class::ResultSet, "search" in DBIx::Class::ResultSet, "next" in DBIx::Class::ResultSet, and "all" in DBIx::Class::ResultSet

TMTOWTDI!

SQL: Update¶

 my $update = $dbh->prepare('
    UPDATE books
    SET title = ?
    WHERE id = ?
 ');

 $update->execute( 'New title', $book_id );

DBIC: Update¶

 $book->update({ title => 'New title' });

See "update" in DBIx::Class::Row

Will not update unless value changes

SQL: Delete¶

 my $delete = $dbh->prepare('DELETE FROM books WHERE id = ?');

 $delete->execute($book_id);

DBIC: Delete¶

 $book->delete

See "delete" in DBIx::Class::Row

SQL: Search¶

 my $sth = $dbh->prepare('
   SELECT title,
   authors.name as author_name
   FROM books
   WHERE books.name LIKE "%monte cristo%" AND
   books.topic = "jailbreak"
 ');

DBIC: Search¶

 my $book = $book_rs->search({
    'me.name'  => { -like => '%monte cristo%' },
    'me.topic' => 'jailbreak',
 })->next;

See SQL::Abstract, "next" in DBIx::Class::ResultSet, and "search" in DBIx::Class::ResultSet

(kinda) introspectible

Prettier than SQL

OO Overridability¶

Override new if you want to do validation

Override delete if you want to disable deletion

and on and on

Convenience Methods¶

"find_or_create" in DBIx::Class::ResultSet

"update_or_create" in DBIx::Class::ResultSet

Non-column methods¶

RELATIONSHIPS¶

"belongs_to" in DBIx::Class::Relationship

"has_many" in DBIx::Class::Relationship

"might_have" in DBIx::Class::Relationship

"has_one" in DBIx::Class::Relationship

"many_to_many" in DBIx::Class::Relationship

SET AND FORGET

->deploy¶

 my $schema = Frew::Schema->connect( $dsn, $user, $pass );

 $schema->deploy

See "deploy" in DBIx::Class::Schema.

See also: DBIx::Class::DeploymentHandler

Schema::Loader¶

 package Frew::Schema;

 use strict;
 use warnings;

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

 __PACKAGE__->loader_options({
    naming => 'v7',
    debug  => $ENV{DBIC_TRACE},
 });

 1;

 # elsewhere...

 my $schema = Frew::Schema->connect( $dsn, $user, $pass );

See DBIx::Class::Schema::Loader and "CONSTRUCTOR OPTIONS" in DBIx::Class::Schema::Loader::Base.

Populate¶

 $schema->populate([ Users =>
    [qw( username password )],
    [qw( frew     >=4char$ )],
    [qw(      ...          )],
    [qw(      ...          )],
 );

See "populate" in DBIx::Class::Schema

I use populate here <http://blog.afoolishmanifesto.com/archives/1255> to export our whole (200M~) db to SQLite

Multicreate¶

 $schema->resultset('Author')->create({
    name => 'Stephen King',
    books => [{ title => 'The Dark Tower' }],
    address => {
       street => '123 Turtle Back Lane',
       state  => { abbreviation => 'ME' },
       city   => { name => 'Lowell'     },
    },
 });

See "create" in DBIx::Class::ResultSet

books is a has_many

address is a belongs_to which in turn belongs to state and city each

for this to work right state and city must mark abbreviation and name as unique

Extensible¶

Extensibility example: DBIx::Class::Helpers¶

DBIx::Class::Helper::ResultSet::IgnoreWantarray

DBIx::Class::Helper::ResultSet::Random

DBIx::Class::Helper::ResultSet::SetOperations

DBIx::Class::Helper::Row::JoinTable

DBIx::Class::Helper::Row::NumifyGet

DBIx::Class::Helper::Row::SubClass

DBIx::Class::Helper::Row::ToJSON

DBIx::Class::Helper::Row::StorageValues

DBIx::Class::Helper::Row::OnColumnChange

Extensibility example: DBIx::Class::TimeStamp¶

See DBIx::Class::TimeStamp

Cross DB

set_on_create

set_on_update

Extensibility example: Kioku¶

See DBIx::Class::Schema::KiokuDB

Kioku is the new hotness

Mix RDBMS with Object DB

Result vs ResultSet¶

Result == Row

ResultSet == Query Plan

(less important but...)

ResultSource == Queryable collection of rows (Table, View, etc)

Storage == Database

Schema == associates a set of ResultSources with a Storage

ResultSet methods¶

 package MyApp::Schema::ResultSet::Book;

 use strict;
 use warnings;

 use base 'DBIx::Class::ResultSet';

 sub good {
    my $self = shift;
    $self->search({
       $self->current_source_alias . '.rating' => { '>=' => 4 }
    })
 };

 sub cheap {
    my $self = shift;
    $self->search({
       $self->current_source_alias . '.price' => { '<=' => 5}
    })
 };

 # ...

 1;

See "Predefined searches" in DBIx::Class::Manual::Cookbook

All searches should be ResultSet methods

Name has obvious meaning

"current_source_alias" in DBIx::Class::ResultSet helps things to work no matter what

ResultSet method in Action¶

 $schema->resultset('Book')->good

ResultSet Chaining¶

 $schema->resultset('Book')
    ->good
    ->cheap
    ->recent

search_related¶

 my $score = $schema->resultset('User')
    ->search({'me.userid' => 'frew'})
    ->related_resultset('access')
    ->related_resultset('mgmt')
    ->related_resultset('orders')
    ->telephone
    ->search_related( shops => {
       'shops.datecompleted' => {
          -between => ['2009-10-01','2009-10-08']
       }
    })->completed
    ->related_resultset('rpt_score')
    ->search(undef, { rows => 1})
    ->get_column('raw_scores')
    ->next;

The SQL that this produces (with placeholders filled in for clarity's sake) on our system (Microsoft SQL) is:

 SELECT raw_scores
   FROM (
     SELECT raw_scores, ROW_NUMBER() OVER (
         ORDER BY (
             SELECT (1)
           )
       ) AS rno__row__index
       FROM (
         SELECT rpt_score.raw_scores
           FROM users me
           JOIN access access
             ON access.userid = me.userid
           JOIN mgmt mgmt
             ON mgmt.mgmtid = access.mgmtid
           JOIN [order] orders
             ON orders.mgmtid = mgmt.mgmtid
           JOIN shop shops
             ON shops.orderno = orders.orderno
           JOIN rpt_scores rpt_score
             ON rpt_score.shopno = shops.shopno
         WHERE (
           datecompleted IS NOT NULL AND
           (
             (shops.datecompleted BETWEEN '2009-10-01' AND '2009-10-08')  AND
             (type = '1' AND me.userid = 'frew')
           )
         )
       ) rpt_score
   ) rpt_score
 WHERE rno__row__index BETWEEN 1 AND 1

See: "related_resultset" in DBIx::Class::ResultSet, "search_related" in DBIx::Class::ResultSet, and "get_column" in DBIx::Class::ResultSet.

bonus rel methods¶

 my $book = $author->create_related(
    books => {
       title => 'Another Discworld book',
    }
 );

 my $book2 = $pratchett->add_to_books({
    title => 'MOAR Discworld book',
 });

See "create_related" in DBIx::Class::Relationship::Base and "add_to_$rel" in DBIx::Class::Relationship::Base

Note that it automatically fills in foreign key for you

Excellent Transaction Support¶

 $schema->txn_do(sub {
    ...
 });

 $schema->txn_begin; # <-- low level
 # ...
 $schema->txn_commit;

See "txn_do" in DBIx::Class::Schema, "txn_begin" in DBIx::Class::Schema, and "txn_commit" in DBIx::Class::Schema.

InflateColumn¶

 package Frew::Schema::Result::Book;

 use strict;
 use warnings;

 use base 'DBIx::Class::Core';

 use DateTime::Format::MySQL;

 # Result code here

 __PACKAGE__->load_components('InflateColumn');

 __PACKAGE__->inflate_column(
    date_published => {
       inflate => sub { DateTime::Format::MySQL->parse_date( shift ) },
       deflate => sub { shift->ymd },
    },
 );

See DBIx::Class::InflateColumn, "inflate_column" in DBIx::Class::InflateColumn, and DBIx::Class::InflateColumn::DateTime.

InflateColumn: deflation¶

 $book->date_published(DateTime->now);
 $book->update;

InflateColumn: inflation¶

 say $book->date_published->month_abbr; # Nov

FilterColumn¶

 package Frew::Schema::Result::Book;

 use strict;
 use warnings;

 use base 'DBIx::Class::Core';

 # Result code here

 __PACKAGE__->load_components('FilterColumn');

 __PACKAGE__->filter_column(
    length => {
       to_storage   => 'to_metric',
       from_storage => 'to_imperial',
    },
 );

 sub to_metric   { $_[1] * .305 }
 sub to_imperial { $_[1] * 3.28 }

See DBIx::Class::FilterColumn and "filter_column" in DBIx::Class::FilterColumn

ResultSetColumn¶

 my $rsc = $schema->resultset('Book')->get_column('price');
 $rsc->first;
 $rsc->all;
 $rsc->min;
 $rsc->max;
 $rsc->sum;

See DBIx::Class::ResultSetColumn

Aggregates¶

 my @res = $rs->search(undef, {
    select   => [
       'price',
       'genre',
       { max => price },
       { avg => price },
    ],
    as       => [
       qw(price genre max_price avg_price)
    ],
    group_by => [qw(price genre)],
 });
 for (@res) {
    say $_->price . ' ' . $_->genre;
    say $_->get_column('max_price');
    say $_->get_column('avg_price');
 }

See "select" in DBIx::Class::ResultSet, "as" in DBIx::Class::ResultSet, and "group_by" in DBIx::Class::ResultSet

Careful, get_column can basically mean three things

private in which case you should use an accessor

public for what there is no accessor for

public for get resultset column (prev example)

HRI¶

 $rs->search(undef, {
   result_class => 'DBIx::Class::ResultClass::HashRefInflator',
 });

See "result_class" in DBIx::Class::ResultSet and DBIx::Class::ResultClass::HashRefInflator.

Easy on memory

Mega fast

Great for quick debugging

Great for performance tuning (we went from 2m to < 3s)

Subquery Support¶

 my $inner_query = $schema->resultset('Artist')
    ->search({
     name => [ 'Billy Joel', 'Brittany Spears' ],
 })->get_column('id')->as_query;

 my $rs = $schema->resultset('CD')->search({
     artist_id => { -in => $inner_query },
 });

See "Subqueries" in DBIx::Class::Manual::Cookbook

Bare SQL w/ Placeholders¶

 $rs->update({
    # !!! SQL INJECTION VECTOR
    price => \"price + $inc", # DON'T DO THIS
 });

Better:

 $rs->update({
    price => \['price + ?', [inc => $inc]],
 });

See "Literal SQL with placeholders and bind values (subqueries)" in SQL::Abstract