Bio::Chado::Schema

NESCent, Durham, NC

March 5-7, 2011

What is DBIx::Class?

What is DBIx::Class?

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

Data Model

This isn't really middleware, it's a code library.

And therefore, Bio::Chado::Schema

As of 0.08: 205 Perl 5 classes

http://search.cpan.org/dist/Bio-Chado-Schema

continued...

And therefore, Bio::Chado::Schema

As of 0.08: 205 Perl 5 classes

http://search.cpan.org/dist/Bio-Chado-Schema

( So why do we need this? )

Chado needs encapsulation

Chado's design makes for:

Chado needs encapsulation

complex queries

Chado needs encapsulation

steep learning curve

BCS Usage

continued...

BCS Usage

my $chado = Bio::Chado::Schema->connect( 'dbi:Pg:...', $user, $pass );
continued...

BCS Usage

my $chado = Bio::Chado::Schema->connect( 'dbi:Pg:...', $user, $pass );
continued...

BCS Usage

my $chado = Bio::Chado::Schema->connect( 'dbi:Pg:...', $user, $pass );
$chado->resultset('Sequence::Feature');
continued...

BCS Usage

my $chado = Bio::Chado::Schema->connect( 'dbi:Pg:...', $user, $pass );
$chado->resultset('Sequence::Feature');
      ->search({ name => 'something' });
continued...

BCS Usage

my $chado = Bio::Chado::Schema->connect( 'dbi:Pg:...', $user, $pass );
$chado->resultset('Sequence::Feature');
      ->search({ name => 'something' });
      ->search_related('organism')
continued...

BCS Usage

my $chado = Bio::Chado::Schema->connect( 'dbi:Pg:...', $user, $pass );
$chado->resultset('Sequence::Feature');
      ->search({ name => 'something' });
      ->search_related('organism')
continued...

BCS Usage

my $chado = Bio::Chado::Schema->connect( 'dbi:Pg:...', $user, $pass );
$chado->resultset('Sequence::Feature');
      ->search({ name => 'something' });
      ->search_related('organism')
$chado->resultset('Sequence::Feature')
      ->find( 232432 );

BCS Usage

continued...

BCS Usage

continued...

BCS Usage

my $stuff = $chado->blahblahblah;
while( my $row = $stuff->next ) {
  # do something
}
continued...

BCS Usage

my $stuff = $chado->blahblahblah;
while( my $row = $stuff->next ) {
  # do something
}
continued...

BCS Usage

my $stuff = $chado->blahblahblah;
while( my $row = $stuff->next ) {
  # do something
}
say $some_feature->type->name;

BCS Usage: Joined Select

# get features via the potato organism, also joining in the cvterms table
continued...

BCS Usage: Joined Select

# get features via the potato organism, also joining in the cvterms table
my $potato_bacs =
     $chado->resultset('Organism::Organism')
continued...

BCS Usage: Joined Select

# get features via the potato organism, also joining in the cvterms table
my $potato_bacs =
     $chado->resultset('Organism::Organism')
           ->search({ species => 'Solanum tuberosum' })
continued...

BCS Usage: Joined Select

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

BCS Usage: Joined Select

# 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 type.name = 'BAC_clone' AND species = 'Solanum tuberosum'
EOS

BCS Usage: Loading

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

BCS Usage: Transactions

$chado->txn_do(sub {

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

    $chado->do_all_kinds_of_other_stuff;
});

The Real Advantages of DBIC

The Real Advantages of DBIC

continued...

The Real Advantages of DBIC

The Real Advantages of DBIC

The Real Advantages of DBIC

Using DBIC with your own tables

package My::DBIC:::Layer::OtherThing;
use base 'DBIx::Class::Core';

__PACKAGE__->table('other_thing');
__PACKAGE__->add_columns(
  'other_thing_id' => { ... },
  'name'           => { ... },
  'definition'     => { ... },
  'feature_id'     => { ... },
);
__PACKAGE__->set_primary_key('other_thing_id');
__PACKAGE__->add_unique_constraint('ot_c1', ['name']);

__PACKAGE__->belongs_to(
  'feature',
  'Bio::Chado::Schema::Sequence::Feature',
  { 'foreign.feature_id' => 'self.feature_id' },
);

"Duct tape" your own schema onto BCS

continued...

"Duct tape" your own schema onto BCS

Bio::Chado::Schema::Sequence::Feature->has_many(
  'other_things',
  'My::DBIC::Layer::OtherThing',
  { 'foreign.feature_id' => 'self.feature_id' },
);
continued...

"Duct tape" your own schema onto BCS

Bio::Chado::Schema::Sequence::Feature->has_many(
  'other_things',
  'My::DBIC::Layer::OtherThing',
  { 'foreign.feature_id' => 'self.feature_id' },
);
continued...

"Duct tape" your own schema onto BCS

Bio::Chado::Schema::Sequence::Feature->has_many(
  'other_things',
  'My::DBIC::Layer::OtherThing',
  { 'foreign.feature_id' => 'self.feature_id' },
);
Bio::Chado::Schema->register_source('OtherThing', 'My::DBIC::Layer::OtherThing');
continued...

"Duct tape" your own schema onto BCS

Bio::Chado::Schema::Sequence::Feature->has_many(
  'other_things',
  'My::DBIC::Layer::OtherThing',
  { 'foreign.feature_id' => 'self.feature_id' },
);
Bio::Chado::Schema->register_source('OtherThing', 'My::DBIC::Layer::OtherThing');
continued...

"Duct tape" your own schema onto BCS

Bio::Chado::Schema::Sequence::Feature->has_many(
  'other_things',
  'My::DBIC::Layer::OtherThing',
  { 'foreign.feature_id' => 'self.feature_id' },
);
Bio::Chado::Schema->register_source('OtherThing', 'My::DBIC::Layer::OtherThing');
$chado->resultset('Sequence::Feature')->other_things;

Making a composite schema

package My::Schema;
# load our own classes
__PACKAGE__->load_namespaces;
__PACKAGE__->load_namespaces(
    result_namespace    => '+Bio::Chado::Schema::Result',
    resultset_namespace => '+Bio::Chado::Schema::ResultSet',
  );

Development Roadmap

Acknowledgments