DBIx::Class::Manual::Features - A boatload of DBIx::Class features with links to respective documentation

Currently there are 88 people listed as contributors to DBIC. That ranges from documentation help, to test help, to added features, to entire database support.

Currently (June 9, 2010) 6 active branches (committed to in the last two weeks) in git. Last release (0.08122) had 14 new features, and 16 bug fixes. Of course that ebbs and flows.)

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

generally very welcoming of people willing to help

These are things that are in most other ORMs, but are still reasons to use DBIC over raw SQL.

The vast majority of code should run on all databases without needing tweaking

C - Create

R - Retrieve

U - Update

D - Delete

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

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

 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

 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}";
 }

 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!

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

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

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

See update in DBIx::Class::Row

Will not update unless value changes

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

 $delete->execute($book_id);

 $book->delete

See delete in DBIx::Class::Row

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

 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

Override new if you want to do validation

Override delete if you want to disable deletion

and on and on

find_or_create in DBIx::Class::ResultSet

update_or_create in DBIx::Class::ResultSet

Need a method to get a user's gravatar URL? Add a gravatar_url method to the Result class

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

These things may be in other ORM's, but they are very specific, so doubtful

Create a database from your DBIx::Class schema.

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

 $schema->deploy

See deploy in DBIx::Class::Schema.

See also: DBIx::Class::DeploymentHandler

Create a DBIx::Class schema from your database.

 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.

Made for inserting lots of rows very quicky into database

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

See populate in DBIx::Class::Schema

I use populate here to export our whole (200M~) db to SQLite

Create an object and it's related objects all at once

 $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

DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is made to allow extensions to nearly every part of it.

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

See DBIx::Class::TimeStamp

Cross DB

set_on_create

set_on_update

See DBIx::Class::Schema::KiokuDB

Kioku is the new hotness

Mix RDBMS with Object DB

Result == Row

ResultSet == Query Plan

Internal Join Optimizer for all DB's (!!!)

(less important but...)

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

Storage == Database

Schema == associates a set of ResultSources with a Storage

 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

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

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

 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::ClassResultSet, and get_column in DBIx::Class::ResultSet.

 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

 $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.

 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.

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

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

 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

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

See DBIx::Class::ResultSetColumn

 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 basicaly 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)

 $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)

 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

 $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