DBIx::Class::Manual::Features

(source, CPAN)

#NAME

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

#META

#Large Community

There are hundres of DBIC contributors listed in AUTHORS. That ranges from documentation help, to test help, to added features, to entire database support.

#Active Community

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

#Responsive Community

#I needed MSSQL order-by support; the community helped me add support
#generally very welcoming of people willing to help

#General ORM

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

#Cross DB

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

#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);

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

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

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::Classic, "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

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

#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

#DBIx::Class Specific Features

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

#->deploy

Create a database from your DBIx::Class schema.

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

$schema->deploy

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

#Schema::Loader

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.

#Populate

Made for inserting lots of rows very quickly 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

#Multicreate

Create an object and its 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

#Extensible

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.

#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

#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

#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

#bonus rel methods

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

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

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::Classic

#FURTHER QUESTIONS?

Check the list of additional DBIC resources.

#COPYRIGHT AND LICENSE

This module is free software copyright by the DBIx::Class (DBIC) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library.

CONTENTS