Difference between revisions of "Chado::AutoDBI Presentation"
From GMOD
m (Text replace - "</perl>" to "</syntaxhighlight>") |
m (Text replace - "</sql>" to "</syntaxhighlight>") |
||
(One intermediate revision by the same user not shown) | |||
Line 16: | Line 16: | ||
Convert SQL Queries/Inserts/Deletes -> Object Calls | Convert SQL Queries/Inserts/Deletes -> Object Calls | ||
− | <sql> | + | <syntaxhighlight lang="sql"> |
INSERT INTO feature (organism_id, name) | INSERT INTO feature (organism_id, name) | ||
VALUES (1, 'foo'); | VALUES (1, 'foo'); | ||
− | </ | + | </syntaxhighlight> |
To: | To: | ||
<syntaxhighlight lang="perl"> | <syntaxhighlight lang="perl"> |
Latest revision as of 23:33, 8 October 2012
This Wiki section is an edited version of Brian O'Connor's presentation.
Contents
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
INSERT INTO feature (organism_id, name) VALUES (1, 'foo');
To:
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' });
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.
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});
Technical Overview
- Basic ORM Object: Feature
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 }
- data field accessors by Class::Accessor
Technical Overview
- Basic ORM Object: Feature
- has_a
# # has_a # Turnkey::Model::Feature->has_a( type_id => "Turnkey::Model::Cvterm" ); sub cvterm { return shift->type_id; }
- Basic ORM Object: Feature
- has_many
# # 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; }
- 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
# 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']);
Technical Overview
- Transactions
- Chado::AutoDBI supports transactions, and one can wrap the transaction in an eval()
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; } }
Technical Overview
- Lazy Loading
- One can either do automated creation of objects or explicitly dictate which fields are incorporated into object
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 .../ );
Typically:
Turnkey::Model::Feature->set_up_table('feature');
Problem 1
- Create Feature & Add Description
# 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, });
Problem 2
- Retrieve a Feature via Searching
- Search using strings or identifiers, a search will return an iterator object
# 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-%');
Problems 3, 4, & 5
- Update a Feature
# update the xfile gene name $feature->name("x-file"); $feature->update();
- Delete a Feature
# now delete the x-file feature $feature->delete();
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
- Turnkey
- Biopackages