Difference between revisions of "Chado::AutoDBI Presentation"

    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.
 
<syntaxhighlight lang="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 {{GlossaryLink|ORM|ORM}} Object: Feature
 
<syntaxhighlight lang="perl">
package Turnkey::Model::Feature;
use base 'Turnkey::Model::DBI';
 
Turnkey::Model::Feature->set_up_table('feature');
 
#
# 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
 
<syntaxhighlight lang="perl">
#
# 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
 
<syntaxhighlight lang="perl">
#
# 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
 
<syntaxhighlight lang="perl">
# skip over feature_synonym table
#
# method 1
#
sub synonyms { my $self = shift; return map $_->synonym_id, $self->feature_synonyms; }
#
# 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()
<syntaxhighlight lang="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
<syntaxhighlight lang="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:
 
<syntaxhighlight lang="perl">
Turnkey::Model::Feature->set_up_table('feature');
</perl>
 
=====Problem 1=====
 
* Create Feature & Add Description
 
<syntaxhighlight lang="perl">
# 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'
    		       });
 
# 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
 
<syntaxhighlight lang="perl">
# objects for global use
 
# the organism for our new feature
my $organism = Turnkey::Model::Organism->search(abbreviation => "S.cerevisiae")->next;
 
# the cvterm for a "Note"
my $note_cvterm = Turnkey::Model::Cvterm->retrieve(2);
 
# searching name by wildcard
 
my @results = Turnkey::Model::Feature->search_like(name => 'x-%');
</perl>
 
=====Problems 3, 4, & 5=====
 
* Update a Feature
 
<syntaxhighlight lang="perl">
# update the xfile gene name
 
$feature->name("x-file");
$feature->update();
</perl>
 
* Delete a Feature
 
<syntaxhighlight lang="perl">
# 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
 
 
[[Category:Chado]]
[[Category:Middleware]]
[[Category:Perl]]
[[Category:Turnkey]]