DBIx::Class

Contact Info

• IRC: irc.perl.org #dbix-class
• ML: http://lists.scsys.co.uk/mailman/listinfo/dbix-class
• DBIx::Class

Authors

Arthur Axel "fREW" Schmidt

What's up guys?

• How many people have used any ORM?
• In Perl?
  • DBIC?
  • Class::DBI?
  • Rose::DB?
  • Fey::ORM?
  • Others?
• AR?
• (N)Hibernate?

Purpose

The purpose of this talk is to show you as many features of DBIx::Class in 40 minutes so that when you need to do something with it later you will know what's possible

• Note: links in slides are so you can find docs for what I'm talking about later

DBIx::Class?

• ORM (object relational mapper)
• SQL <-> OO (using objects instead of SQL)
• There are many ORMs, DBIx::Class just happens to be the best in Perl (my correct opinion)

Meta

These are reasons that are not technical or inherent to the code of DBIC, but are totally awesome things about it.

Large Community

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

Active Community

Upcoming release (0.08197?) has 8 new features, and 26 bug fixes. Of course that ebbs and flows.

Responsive Community

• needed MSSQL order-by support, they helped me add support
• generally very welcoming of people willing to help

General ORM

These are things that are in most (but not all!) 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 - Read
• 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,
});

• create
• 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() ) {
  print 'Author of '
     . $book->{title}
     . ' is '
     . $book->{author_name}
     . "\n";
}

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 ) {
 print 'Author of '
    . $book->title
    . ' is '
    . $book->author->name
    . "\n";
}

• find
• search
• next
• all
• 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',
});

• update
• Won't update unless value changes

SQL: Delete

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

$delete->execute($book_id);

DBIC: Delete

$book->delete;

• delete

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;

• search
• next
• SQL::Abstract
• (kinda) introspectible
• Objectively 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

• Note: we don't get everything from OO, but I it's still pretty great without Giving Up

Convenience Methods

• find_or_create
• update_or_create

Non-column methods

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

         use Gravatar::URL ();
         sub gravatar_url {
            Gravatar::URL::gravatar_url({ email => $_[0]->email })
         }
      

RELATIONSHIPS

• belongs_to
• has_many
• might_have
• has_one
• many_to_many (technically not a relationship)
• SET AND FORGET

EXTENDED RELATIONSHIPS

 __PACKAGE__->belongs_to(
  output_device => 'My::Schema::Result::OutputDevice',
  sub {
    my $args = shift;

    my $other = $args->{foreign_alias};
    my $me   = $args->{self_alias};
    my $self = $args->{self_rowobj};
    return ({
      "$other.user"   => { -ident => "$me.user" },
      "$other.shared" => 1,
    },
    $self && {
       "$other.user"     => $self->user,
       "$other.shared" => 1,
    });
  },
);

->deploy

Perl -> DB

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

• deploy
• See also: DBIx::Class::DeploymentHandler

Schema::Loader

DB -> Perl

package Foo::Schema;
use base 'DBIx::Class::Schema::Loader';
__PACKAGE__->loader_options({
   naming => 'v7',
   debug  => $ENV{DBIC_TRACE},
});
1;

# elsewhere...

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

• DBIx::Class::Schema::Loader
• loader_options

Populate

Made for inserting lots of rows very quicky into database

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

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

Multicreate

Create an object and all of 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'     },
   },
});

• create
• 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. (more slides about components later)

Result vs ResultSet

• Result == Row
• ResultSet == Query Plan
  • Internal Join Optimizer for all DB's (!!!)
• (less important but...)
• ResultSource == Table
• Storage == Database

ResultSet methods

package MyApp::Schema::ResultSet::Book;
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}
   })
}
      

ResultSet method notes

• All searches should be ResultSet methods
• Name has obvious meaning
• current_source_alias 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')
   ->get_column('raw_scores')
   ->first;

• related_resultset
• search_related
• get_column

bonus rel methods

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

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

• create_related
• add_to_books
• Automaticaly fills in foreign key for you

Excellent Transaction Support

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

my $guard = $schema->txn_scope_guard;
# ...
$guard->commit;

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

• txn_do
• txn_scope_guard
• txn_begin
• txn_commit

InflateColumn

package Foo::Schema::Result::Book;
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 },
   },
);
# Automatic see: DBIC::InflateColumn::DateTime

• InflateColumn
• inflate_column

InflateColumn: deflation

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

InflateColumn: inflation

say $book->date_published->month_abbr;

Nov

FilterColumn

package Foo::Schema::Result::Book;
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 }

• FilterColumn
• filter_column

ResultSetColumn

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

Aggregates

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

• columns
• group_by

Aggregates Notes

• Careful, get_column can basicaly mean THREE things
• private for get what you should use an accessor for
• public for what there is no accessor for
• public for get resultset column (prev slide)

HRI

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

• result_class
• 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 $inside_query = $schema->resultset('Artist')
   ->search({
    name => [ 'Billy Joel', 'Iron Maiden' ],
})->get_column('id')->as_query;

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

Bare SQL w/ Placeholders

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

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

Advanced

Random example - SQL metaprogramming

    $shadow_rs->search({ shadow_val_title => 'stab' })->insert({

      shadow_stage => 2,

      shadow_timestamp => 12345678,

      shadowed_lifecycle => \"(
        SELECT COALESCE( MAX( shadowed_lifecycle ), 0 ) + 1 FROM $shadow_table
      )",

      rel_shadow_artists_lifecycle =>
        $shadowed_cd->search_related('artist')
                     ->search_related('shadows', {}, { rows => 1 })
                      ->get_column('shadowed_lifecycle')
                       ->as_query
      ,
    });

INSERT
  INTO shadow_cds (
    shadow_stage,
    shadow_timestamp,
    shadow_val_title,
    shadow_lifecycle,
    rel_shadw_artists_lifecycle
  ) VALUE (
    '2',
    '12345678',
    'stab',
    '2',
    (
      SELECT COALESCE( MAX( shadowed_lifecycle ), 0 ) + 1
        FROM shadow_cds
    ),
    (
      SELECT shadows.shadowed_lifecycle
        FROM artists me
        JOIN shadow_artists shadows
          ON shadows.shadowed_curpk_name = me.name
      WHERE me.name = 'gaga'
      LIMIT '1'
    ),
  )
 

Loosely coupled abstractions

• Query definition/manipulation framework (DBIx::Class::ResultSet)
• Storage I/O abstraction layer (DBIx::Class::Storage(::DBI ))
• Data-Source/Relationship Model abstraction framework (DBIx::Class::Schema/DBIx::Class::ResultSource)
• Retrieved result parser (DBIx::Class::Row / DBIx::Class::ResultClass::HashRefInflator / <your own class>)

Loosely coupled abstractions

  my $schema_instance               # knows about its registered sources (load_namespaces,
    = Schema::Class->connect(...);  # load_classes, compose_namespace, etc), and about its
                                    # storage instance (the storage IO abstraction)

  my $result_source_instance
    = $schema_instance->source('Artist') # knows how to refer to the actual data source
                                         # when talking to storage, knows the source
                                         # metadata, knows the default result inflation class

  my $resultset_instance            # query plan, holds a reference to the source and infers
    = $result_sources_instance;     # from it the storage-source name, and the default selection
( or $schema->resultset('Artist')   # and/or restrictions from source instance metadata
                                    # ALL OVERRIDABLE

  my $row_object
    = $resultset_instance->next     # actual query execution, raw result is processed and returned
                                    # according to the $resultset_instance settings
                                    # CAN BE ANYTHING

Query definition/manipulation framework (DBIx::Class::ResultSet)

• Query plan (geared to a specific source)
• Immutable
• Able to express/override all query primitives
• Holds returned selection parsing metadata:

      result_class
      inflation-specification (as)
  

• Inherits defaults from referenced Result Source (if any)
• EVERYTHING IS OVERRIDABLE

Overridable/augmentable default selection

  $rs->search({}, { '+columns' => ['foo', { max_bar => \'MAX(bar)' }] });
  $rs->search({}, { select => 'multi_value_func', as => [qw(val1 val2)] });

• Currently does not create accessors on the fly

Overridable/augmentable default selection: AS IS NOT AS

• Represents an inflation plan

    $artist_rs->search({}, { prefetch => { cds => 'tracks' } });
  
      ===
  
    $artist_rs->search({}, {
      join  => { cds => 'tracks' },
  
      '+columns' => {
        'cds.title'         => 'cds.title',
        'cds.year'          => 'cds.year',
        'cds.tracks.title'  => 'tracks.title',
        'cds.tracks.pos'    => 'tracks.pos',
      },
  
      <some collapser magic that I can not yet sanely expose>
      (in the works, promise!)
    });
  

• A source of limitless confusion
• columns/+columns design unfortunate

ResultSet operations (CRUD)

• No-result CREATE/UPDATE/RETRIEVE

      $rs->populate
      $rs->update
      $rs->delete
  

• Low level RETRIEVE ($sth->selectall_arrayref/fetchrow_array)
  (epecially when you want to limit DBIC to a super-smart SQL generator)

      $rs->cursor->next/all
  

• Roundtrip Result-inflating CREATE/RETRIEVE

      $rs->create
      $rs->next/all
  

• Inflation (what happens on ->next/->all)

      $rs->result_class->inflate_result(
        $rs->cursor->next
      + as-specification from $rs
      )
  

Storage I/O abstraction layer (DBIx::Class::Storage(::DBI ))

• Holds $dbh (almost like DBIx::Connector)
• Fork/thread and stale connection handling
• Transaction/savepoint handling
• Invokes correct DBIx::Class::Storage::SQLMaker subclass (sql dialects)
• Buggy DBD workarounds (over 8000 loc)
• Could be shared between multiple $schema's
• Much more on the abstraction front

DBIx::Class::Core / DBIx::Class::Row

• Data accessors
• Relationship Accessors
• Hooks to ResultSource/Schema/ResultSet instances
• Handy corresponding row manipulation methods
• Plugins (components/roles/whateveryouwannacallit)

Write your own result class

• DBIC layers have *NO EXPECTATIONS* of result format

The power of Extended Relationships

Just a coderef, so you can...

• warn, to create deprecated rels
• use dynamic (local) scope to have ghetto parameterized rels
• anything else

DBIx::Class Components

NOT ALL ME, LOTS OF AUTHORS

::Candy

Use this to avoid carpel tunnel (no more __PACKAGE__)

package Lynx::SMS::Schema::Result::Price;

use Lynx::SMS::Schema::Candy; # special subclass

primary_column id => {
  data_type => 'int',
  is_auto_increment => 1,
};

column part_code => {
  data_type => 'varchar',
  size      => 15,
};

column description => {
  data_type       => 'text',
  is_serializable => 1,
};

column type_id          => { data_type => 'int' };
column credits_per_year => { data_type => 'int' };
column list_price       => { data_type => 'money' };
column gsa_price        => { data_type => 'money' };

1;
      

::TimeStamp

USE THIS for any kind of timestamp (set on create or set on update)

Caveat: any bugs in DateTime are also in this

::EncodedColumn

USE THIS for password columns

use DBIx::Class::Candy -components => ['EncodedColumn'];
column password => {
   is_serializable => 0,
   data_type     => 'CHAR',
   size          => 59,
   encode_column => 1,
   encode_class  => 'Crypt::Eksblowfish::Bcrypt',
   encode_args   => { key_nul => 0, cost => 8 },
   encode_check_method => 'check_password',
};

Later..

die "liar!" unless $user->check_password($input)

Detour: Context--

my @foo = $rs->search(...)

vs

my $foo = $rs->search(...)

or more importantly...

$foo->bar($rs->search(...))

::Helper::ResultSet::IgnoreWantarray

USE THIS for less surprising resultsets (must call ->all)

      print_rs($rs->search(...)); # <3
      print_row($_) for $rs->search(...)->all; # <3
      

::Helper::Row::OnColumnChange

USE THIS to do something when a value changes (per column)

use DBIx::Class::Candy -components => [
   'Helper::Row::OnColumnChange'
];

after_column_change kind_of_id => {
   txn_wrap => 1,
   method   => '_fix_for_updates',
};

sub _fix_for_updates {
   my ( $self, $old, $new ) = @_;

   $self->_set_materialized_path;

   $_->_fix_for_updates for $self->direct_kinds->all
}
      

::Helper::ResultSet::SetOperations

USE THIS to use UNION and other set ops with DBIC

(excuse this insane example)

sub succeeded_computers {
   my $self = shift;

   my $c_links = $self->test_computer_links;
   my $d_links = $self->test_device_links;

   my $failed_ids =
      $c_links->failed->related_resultset('computer')->union(
         $c_links->untested->related_resultset('computer'),
      )->union_with_devices(
         $d_links->failed->related_resultset('device')->union(
            $d_links->untested->related_resultset('device'),
         )
      )->get_column('id')->as_query;

   $self->result_source->schema->resultset('Computer')->search({
      id => {
         -not_in => $failed_ids,
         -in     => $c_links->related_resultset('computer')->union_with_devices(
                       $d_links->related_resultset('device')
                    )->get_column('id')->as_query
      }
   });
}
      

::Helper::Row::RelationshipDWIM

USE THIS to avoid carpel tunnel (has_many cars => 'Cars', ...)

      use DBIx::Class::Candy -components => [
         'Helper::Row::RelationshipDWIM'
      ];
      belongs_to type => '::Type', 'type_id';

      

::Helper::Row::StorageValues

USE THIS to keep track of what's in the DB before you call update and after you use an accessor

      use DBIx::Class::Candy -components => [ 'Helper::Row::StorageValues' ];

      # use OnColumnChange for this, just an example (has bug)
      sub update {
         my $self = shift;
         log_trace {
            "name changing from " . $self->storage_value('name') .
               " to " . $self->name
         };

         $self->next::method(@_);
      }
      

::Helper::Row::SubClass

USE THIS to subclass parts of your schema (deprecated by new versions of Class::C3::Componentised)

automatically makes sure to set the same table and tries it's best to copy rels

::Helper::Row::ToJSON

USE THIS to generate a sensible TO_JSON method

::Helper::ResultSet::CorrelateRelationship

USE THIS for getting aggregates (and other things)

see later slide for in depth example

::Helper::ResultSet::SearchOr

USE THIS to reuse even more resultset methods

• search is "additive" (ANDs)
• search_or is safely not additive

•          $artists_rs->touring->has_record_label
           

• vs.

           $artists_rs->search_or([
              $artist_rs->touring
              $artist_rs->has_record_label
           ])
           

• vs.

           $artists_rs->touring->union([
              $artist_rs->has_record_label
           ])
           

::Helper::Schema::LintContents

USE THIS to check if the contents of your database are in good shape

• Can check for duplicate PKs and UCs
• Can check for broken FKs

::Helper::ResultSet::ResultClassDWIM

USE THIS to avoid carpel tunnel ( result_class => '::HRI' )

::Helper::ResultSet::RemoveColumns

Use this to remove columns from selection in a succicnt fashion

         $rs->search(undef, {
            remove_columns => ['password'],
         })
      

vs

         $rs->search(undef, {
            columns => [qw(id username email fullname ... )],
         })
      

Not just less to type, also more robust

::Helper::ResultSet::Random

Use this to grab random rows from a resultset

         my $rand = $rs->rand(1)->next;
      

::Helper::Row::JoinTable

Use this to reduce boilerplate involved in creating join tables

 package MyApp::Schema::Result::Foo_Bar;

 use DBIx::Class::Candy -components => ['Helper::Row::JoinTable'];

 join_table {
    left_class   => 'Foo',
    left_method  => 'foo',
    right_class  => 'Bar',
    right_method => 'bar',
 };
      

::DeploymentHandler

• Generate DDL for migrations automatically with SQLT
• Run Perl and raw SQL for migrations safely (with txns, durable(S::L), etc)
• show example!

Useful ResultSet Methods

1. sub with_all_computers_count {
      my $self = shift;
   
      $self->search(undef, {
         '+columns' => {
            all_computers_count =>
               $self->correlate('computers')->count_rs->as_query
         },
      })
   }
   

2. ::Helper::Row::ProxyResultSetMethod

   package MyApp::Schema::Result::Test;
   
   use DBIx::Class::Candy -components => [
      'Helper::Row::ProxyResultSetMethod',
   ];
   
   proxy_resultset_method => 'all_computers_count';
            

Questions?

END