Chado::AutoDBI Presentation

This Wiki section is an edited version of Brian's presentation.

Relation to Turnkey

Turnkey is a package that auto-generates Web sites given a relational schema, based on SQL::Translator

• Turnkey authors: Allen Day, Scott Cain, Brian O'Connor
• Turnkey and Chado::AutoDBI objects are essentially the same

Technical Overview

• Code Generation

Project Overview

Convert SQL Queries/Inserts/Deletes -> Object Calls <sql>

 INSERT INTO feature (organism_id, name)
                                 VALUES (1, 'foo');

</sql> To: <perl>

   my $feature = Turnkey::Model::Feature->find_or_create({
                      organism_id => $organism,
   		       name => 'xfile', uniquename => 'xfile',
   		       type_id => $mrna_cvterm,
   		       is_analysis => 'f', is_obsolete => 'f'
   		       });

</perl>

Technical Overview

• Database connection: use a base class
• Set up base object and connect, then create a table object to access primary key.
• Class::DBI can find and insert records into other table, based on foreign key.

<perl> use base qw(Class::DBI::Pg);

my ($dsn, $name, $pass); $dsn = "dbi:Pg:host=localhost;dbname=chado;port=5432"; $name = "postgres"; $pass = "";

Turnkey::Model::DBI->set_db('Main', $dsn, $name, $pass, {AutoCommit => 1}); </perl>

Technical Overview

• Basic ORM Object: Feature

<perl> package Turnkey::Model::Feature; use base 'Turnkey::Model::DBI';

Turnkey::Model::Feature->set_up_table('feature');

2. Primary key accessors

sub id { shift->feature_id } sub feature { shift->feature_id } </perl>

• data field accessors by Class::Accessor

Technical Overview

• Basic ORM Object: Feature
  • has_a

<perl>

2. has_a

Turnkey::Model::Feature->has_a( type_id => "Turnkey::Model::Cvterm" ); sub cvterm { return shift->type_id; } </perl>

• Basic ORM Object: Feature
  • has_many

<perl>

2. has_many

Turnkey::Model::Feature->has_many('feature_synonym_feature_id',

          'Turnkey::Model::Feature_Synonym' => 'feature_id');

sub feature_synonyms { return shift->feature_synonym_feature_id; }

Turnkey::Model::Feature->has_many('featureprop_feature_id',

          'Turnkey::Model::Featureprop' => 'feature_id');

sub featureprops { return shift->featureprop_feature_id; } </perl>

• Can traverse tables, such as going from FEATURE to FEATUREPROP
  • Tell base object that the table object has_a() or has_many() keys corresponding to some key in other table object

Technical Overview

• Basic ORM Object: Feature
  • skipping linker tables for has_many

<perl>

1. skip over feature_synonym table
3. method 1

sub synonyms { my $self = shift; return map $_->synonym_id, $self->feature_synonyms; }

2. method 2

Turnkey::Model::Feature->has_many( synonyms2 =>

                     ['Turnkey::Model::Feature_Synonym' => 'synonym_id']);

</perl>

Technical Overview

• Transactions
  • Chado::AutoDBI supports transactions, and one can wrap the transaction in an eval()

<perl>

 sub do_transaction {
   my $class = shift;
   my ( $code ) = @_;
   # Turn off AutoCommit for this scope.
   # A commit will occur at the exit of this block automatically,
   # when the local AutoCommit goes out of scope.
   local $class->db_Main->{ AutoCommit };

   # Execute the required code inside the transaction.
   eval { $code->() };
   if ( $@ ) {
     my $commit_error = $@;
     eval { $class->dbi_rollback }; # might also die!
     die $commit_error;
   }
 }

</perl>

Technical Overview

• Lazy Loading
  • One can either do automated creation of objects or explicitly dictate which fields are incorporated into object

<perl> Turnkey::Model::Feature->columns( Primary => qw/feature_id/ ); Turnkey::Model::Feature->columns( Essential => qw/name organism_id type_id/ ); Turnkey::Model::Feature->columns( Others => qw/residues .../ ); </perl>

Typically:

<perl> Turnkey::Model::Feature->set_up_table('feature'); </perl>

Problem 1

• Create Feature & Add Description

<perl>

1. now create mRNA feature

my $feature = Turnkey::Model::Feature->find_or_create({

                      organism_id => $organism,
   		       name => 'xfile', uniquename => 'xfile',
   		       type_id => $mrna_cvterm,
   		       is_analysis => 'f', is_obsolete => 'f'
   		       });
   

1. create description

my $featureprop = Turnkey::Model::Featureprop->find_or_create({

                          value => 'A test gene for GMOD meeting',
                          feature_id => $feature,
   			   type_id => $note_cvterm,
   			   });

</perl>

Problem 2

• Retrieve a Feature via Searching
  • Search using strings or identifiers, a search will return an iterator object

<perl>

1. objects for global use

1. the organism for our new feature

my $organism = Turnkey::Model::Organism->search(abbreviation => "S.cerevisiae")->next;

1. the cvterm for a "Note"

my $note_cvterm = Turnkey::Model::Cvterm->retrieve(2);

1. searching name by wildcard

my @results = Turnkey::Model::Feature->search_like(name => 'x-%'); </perl>

Problems 3, 4, & 5

• Update a Feature

<perl>

1. update the xfile gene name

$feature->name("x-file"); $feature->update(); </perl>

• Delete a Feature

<perl>

1. now delete the x-file feature

$feature->delete(); </perl>

Things Chado::AutoDBI does well

• Easy to use
• Easy to port
• Use with other DBs
  • Both Oracle and Postgres used currently
• Autogenerated via Turnkey
• find_or_create method
• Performance is not as bad as you might guess
  • Due to Lazy loading
  • Even whole genome operations are feasible

Note that speed is relative: one can find bad performance using the wrong SQL and Chado::AutoDBI approach will be speedier.

For More Information

• Class::DBI
  • http://www.class-dbi.com
  • http://search.cpan.org

• Turnkey
  • http://turnkey.sf.net

• Biopackages
  • http://biopackages.net