GMOD

Chado%253A%253AAutoDBI Presentation

This Wiki section is an edited version of Brian O’Connor’s presentation.

Contents

• 1 Relation to Turnkey
• 2 Technical Overview
• 3 Project Overview
• 4 Technical Overview
• 5 Technical Overview
• 6 Technical Overview
• 7 Technical Overview
• 8 Technical Overview
• 9 Technical Overview
• 10 Problem 1
• 11 Problem 2
• 12 Problems 3, 4, & 5
• 13 Things Chado%253A%253AAutoDBI does well
• 14 For More Information

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%253A%253AAutoDBI 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%253A%253AAutoDBI 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%253A%253AAutoDBI 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%253A%253AAutoDBI 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

Categories:

• Chado
• Middleware
• Perl
• Turnkey

Navigation menu

Navigation

• GMOD Home
• Software
• Categories / Tags

Documentation

• Overview
• FAQs
• HOWTOs
• Glossary

Community

• GMOD News
• Training / Outreach
• Support
• GMOD Promotion
• Meetings
• Calendar

Tools

• Browse properties

• Last updated at 23:33 on 8 October 2012.
• Content is available under a GNU Free Documentation License unless otherwise noted.