----
presentation_topic: dbic_chado
presentation_title: Introduction to DBIx::Class
presentation_subtitle: 
presentation_place: Portland, OR
presentation_date: April 14, 2010
----
= What is DBIx::Class?

* Object-relational mapping framework for Perl
* is now the de-facto standard
* nice features

----
= What is DBIx::Class?

* query building (the magic of chainable ResultSets)
* cross-database deployment (using |SQL::Translator| in the backend)
* lots and lots of plugins availabe (dates, testing, ...)

see the manual: http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual.pod

----
= What it looks like

  package My::Schema;

  __PACKAGE__->load_namespaces;

  package My::Schema::Result::Foo;

  __PACKAGE__->table('foo');
  __PACKAGE__->add_columns(qw( foo_id bar baz ));
  __PACKAGE__->has_many('bars' => 'My::Schema::Result::Bar');
  # also belongs_to  many_to_many

  package My::Schema::Result::Bar;

  __PACKAGE__->table('bar');
  # and so on

* use |DBIx::Class::Schema::Loader| to generate this from a DB
* use |$schema->deploy| to make a DB from this

----
= Basic Usage

* open the schema.  ( actual DB connection is deferred )
+  my $schema = My::Schema->connect( 'dbi:Pg:...', $user, $pass );

+* get a ResultSet object representing a set of Rows
+  my $orgs_rs = $schema->resultset('Organism');

+* take a subset of those (another ResultSet)
+  my $chickeny_things = $orgs_rs->search({ genus => 'Gallus' });

+* first one in the result set
+  my $chicken = $chickeny_things->first;     #< actual query is run

----
= Basic Usage

+* getting actual data
+  my $wombat = $orgs_rs->find( 232432 );
+  say $wombat->genus.' '.$wombat->species; #< print 'Vombatus ursinus' or something

  # or search in list context returns all resulting rows
  my @all_nightshades = $orgs_rs->search({ genus => 'Solanum' });

----
= Basic Usage

* traverse relationships (joined queries)
+  my $chickeny_things = $ck_rs->search({ genus => 'Gallus' });
+  my @chr = $chickeny_things->chromosomes;  #< query is run here
+  my @genes = $chicken->chromosomes->features->search({ type => 'gene' });

+* loading
+   $orgs_rs>create({ genus => 'Mus', species => 'musculus' })
+   # or piecewise
+   my $new = $orgs_rs->new;
   $new->genus('Mus');  $new->species('musculus');
   $new->insert;

+* deleting
  $chicken->chromosomes->features->search({ type => 'gene' })->delete;


----
= When not to use DBIC

* tiny app, tiny schema (setup overhead)
+* or tiny number of different queries
+* or hate objects

strengths/weaknesses ...

----
= Strengths and Weaknesses

* strengths
+** chainable ResultSets
+** lazy querying
+** good support for custom SQL
+** prefetching
+** lots of nice extensions

+* weaknesses
+** non-ORM-y things are possible, but can be cumbersome

----
= Chainable ResultSets

  my $styx_tracks =
       $music_schema->resultset('Artist')
                    ->search({ name => 'Styx' })
                    ->albums
                    ->tracks;

  SELECT ...
    FROM artist
    JOIN albums ON ...
    JOIN tracks ON ...
    WHERE artist.name = 'Styx'

----
= Lazy Querying

* does not run the query until data is actually needed

  # fetch all
  my @tracks = $styx_tracks->all;

  # or iterate
  while( my $track = $styx_tracks->next ) { ... }

----
= Custom SQL

* can make a view |ResultSource| for big tuned queries

  package My::Schema::SummarizedFrobs;
  __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
  __PACKAGE__->result_source_instance->view_definition(<<'');
     SELECT omg_so_much_stuff
     UNION
     SELECT ( SELECT blah blah blah FROM blah ),
            ( blah blah blah ),
     WHERE blah blah blah
     UNION
        blah blah blah
      
  __PACKAGE__->add_column( <cols in your view> )

----
= Custom SQL

* for custom conditions, searches accept |SQL::Abstract| syntax

  $schema->resultSet('Sequence::Feature')
         ->search({
              'me.feature_id' =>
                \[ "IN( select feature_id from clone_feature where clone_id = ?)",
                   [ dummy => $self->clone_id ],
                 ],
              },
              { rows => 10,
                order_by => [qw[ me.name me.type_id ]],
                having
              });


----
= Prefetching

  # query will get the wombat organism, and also prefetch *all its
  # chromosomes and features on those chromosomes*
  my $rs = $schema->resultset('Organism')->search(
   { genus => 'Vombatus', species => 'ursinus' },
   { prefetch => { chromosomes => features => } },
  );

(in a real biological DB this would probably blow your memory)

----
= Extensions

* |DBIx::Class::Helpers|
** |fREW|'s miscellaneous things
+* |DBIx::Class::Cursor::Cached|
** tunable, flexible resultset caching
+* |DBIx::Class::Ordered|
** nice for doing ordering or ranking columns in a table
+* |DBIx::Class::InflateColumn::DateTime|, |DBIx::Class::Timestamp|
** work with dates, times, and timestamps as |DateTime| objects

----
= Thread-Safe / Fork-Safe

* |theory|'s |DBix::Connector|

----
= Non-linear Join

  # get features via the potato organism, also joining in the cvterms table
  my $potato_bacs =
       $schema->resultset('Organism')
              ->search({ species => 'Solanum tuberosum' })
              ->search_related( 'features',
                               { 'type.name' => 'BAC_clone'},
                               { 'join' => 'type' },
                             );

----
= Non-linear Join

  # the equivalent bare SQL
  my $potato_bacs = $dbh->selectall_arrayref( <<EOS, undef, 'Solanum tuberosum', 'BAC_clone');
  SELECT features.feature_id
       , features.dbxref_id
       , features.organism_id
       , features.name
       , features.uniquename
       , features.residues
       , features.seqlen
       , features.md5checksum
       , features.type_id
       , features.is_analysis
       , features.is_obsolete
       , features.timeaccessioned
       , features.timelastmodified
  FROM organism me
  LEFT JOIN feature features
         ON features.organism_id = me.organism_id
  JOIN cvterm type
         ON type.cvterm_id = features.type_id
  WHERE species = ? AND type.name = ?
  EOS

----
= Convenient Loading of Relationships

  $chado->resultset( 'Cv::Cv' )
        ->find_or_create({ name => 'My Fake Ontology' })
        ->create_related(  'cvterm',
                           { name => 'MyFakeTerm' });

makes the SQL:

  SELECT me.cv_id
       , me.name
       , me.definition
  FROM cv me
  WHERE ( me.name = 'my fake ontology' )

  INSERT INTO cv ( name )
          VALUES ( 'my fake ontology' )
       RETURNING cv_id

  INSERT INTO cvterm ( cv_id,  name        )
              VALUES ( ?,     'MyFakeTerm' )

----
= Transactions

  $schema->txn_do(sub {

      $schema->resultset('Cv::Cv')
             ->find_or_create({ name => 'My Fake Ontology' })
             ->create_related( 'cvterm', { name => 'MyFakeTerm' } );

  });

----
= The Real Advantages of DBIC

* easier to manipulate and assemble queries

----
= The Real Advantages of DBIC

* complex joined queries (Chado queries) are very easy and compact

----
= The Real Advantages of DBIC

* SQL syntax errors are more difficult to make

----
= The Real Advantages of DBIC

* it's all objects.  you can delegate to them, pass them around, etc.
* HOWEVER:
+** usually you don't want to subclass them
** but, see |DBIx::Class::Manual::Cookbook|

----
== That's All

* The END