Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

203 lines (180 sloc) 8.252 kB
# -*-Perl-*-
# $Id$
BEGIN {
use lib '.';
use Bio::Root::Test;
test_begin(-tests => 23);
use_ok('Bio::DB::Query::SqlQuery');
use_ok('Bio::DB::Query::SqlGenerator');
use_ok('Bio::DB::Query::BioQuery');
use_ok('Bio::DB::Query::QueryConstraint');
use_ok('Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver');
}
my $query = Bio::DB::Query::SqlQuery->new(-tables => ["table1"]);
my $sqlgen = Bio::DB::Query::SqlGenerator->new(-query => $query);
my $sql = $sqlgen->generate_sql();
is ($sql, "SELECT * FROM table1");
$query->add_datacollection("table1", "table2");
$sql = $sqlgen->generate_sql();
is ($sql, "SELECT * FROM table1, table2");
$query->selectelts("col1", "col2", "col3");
$sql = $sqlgen->generate_sql();
is ($sql, "SELECT col1, col2, col3 FROM table1, table2");
$query->groupelts("col1", "col3");
$sql = $sqlgen->generate_sql();
is ($sql, "SELECT col1, col2, col3 FROM table1, table2 GROUP BY col1, col3");
$query->groupelts([]);
$query->orderelts("col2","col3");
$sql = $sqlgen->generate_sql();
is ($sql, "SELECT col1, col2, col3 FROM table1, table2 ORDER BY col2, col3");
$query->where(["col4 = ?", "col5 = 'somevalue'"]);
$sql = $sqlgen->generate_sql();
is ($sql, "SELECT col1, col2, col3 FROM table1, table2 WHERE col4 = ? AND col5 = 'somevalue' ORDER BY col2, col3");
$query->where(["and",
["or", "col4 = ?", "col5 = 'somevalue'"],
["col2 = col4", "col6 not like 'abcd*'"]]);
$sql = $sqlgen->generate_sql();
is ($sql, "SELECT col1, col2, col3 FROM table1, table2 WHERE (col4 = ? OR col5 = 'somevalue') AND (col2 = col4 AND col6 NOT LIKE 'abcd\%') ORDER BY col2, col3");
$query = Bio::DB::Query::BioQuery->new();
$mapper = Bio::DB::BioSQL::mysql::BasePersistenceAdaptorDriver->new();
$query->selectelts(["accession_number","version"]);
$query->datacollections(["Bio::PrimarySeqI"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql, "SELECT bioentry.accession, bioentry.version FROM bioentry");
$query->selectelts([]);
$query->datacollections(["Bio::Species=>Bio::PrimarySeqI"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql, "SELECT * FROM bioentry, taxon_name WHERE bioentry.taxon_id = taxon_name.taxon_id");
$query->datacollections(["Bio::PrimarySeqI e",
"Bio::Species=>Bio::PrimarySeqI sp"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql, "SELECT * FROM bioentry e, taxon_name sp WHERE e.taxon_id = sp.taxon_id");
$query->datacollections(["Bio::PrimarySeqI e",
"Bio::Species=>Bio::PrimarySeqI sp",
"BioNamespace=>Bio::PrimarySeqI db"]);
$query->where(["sp.binomial like 'Mus *'",
"e.desc like '*receptor*'",
"db.namespace = 'ensembl'"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql,
"SELECT * ".
"FROM bioentry e, taxon_name sp, biodatabase db ".
"WHERE e.taxon_id = sp.taxon_id AND e.biodatabase_id = db.biodatabase_id ".
"AND (sp.name LIKE 'Mus \%' AND e.description LIKE '\%receptor\%' ".
"AND db.name = 'ensembl')");
$query->selectelts(["e.accession_number","e.version"]);
$query->datacollections(["Bio::PrimarySeqI e",
"Bio::Species=>Bio::PrimarySeqI sp",
"BioNamespace=>Bio::PrimarySeqI db",
"Bio::Annotation::DBLink xref",
"Bio::PrimarySeqI<=>Bio::Annotation::DBLink"]);
$query->where(["sp.binomial like 'Mus *'",
"e.desc like '*receptor*'",
"db.namespace = 'ensembl'",
"xref.database = 'SWISS'"]);
#$query->flag();
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql,
"SELECT e.accession, e.version ".
"FROM bioentry e, taxon_name sp, biodatabase db, dbxref xref, bioentry_dbxref ".
"WHERE e.taxon_id = sp.taxon_id AND e.biodatabase_id = db.biodatabase_id ".
"AND e.bioentry_id = bioentry_dbxref.bioentry_id ".
"AND xref.dbxref_id = bioentry_dbxref.dbxref_id ".
"AND (sp.name LIKE 'Mus \%' AND e.description LIKE '\%receptor\%' ".
"AND db.name = 'ensembl' AND xref.dbname = 'SWISS')");
$query = Bio::DB::Query::BioQuery->new();
$query->datacollections(["Bio::PrimarySeqI<=>Bio::Annotation::SimpleValue"]);
$query->where(["Bio::PrimarySeqI::primary_key = 10",
"Bio::Annotation::SimpleValue::ontology = 3"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql,
"SELECT * ".
"FROM bioentry, term, bioentry_qualifier_value ".
"WHERE bioentry.bioentry_id = bioentry_qualifier_value.bioentry_id ".
"AND term.term_id = bioentry_qualifier_value.term_id ".
"AND (bioentry.bioentry_id = 10 AND term.ontology_id = 3)");
$query->datacollections(
["Bio::PrimarySeqI e",
"Bio::Annotation::SimpleValue sv",
"Bio::PrimarySeqI<=>Bio::Annotation::SimpleValue esva"]);
$query->where(["Bio::PrimarySeqI::primary_key = 10",
"Bio::Annotation::SimpleValue::ontology = 3"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql,
"SELECT * ".
"FROM bioentry e, term sv, bioentry_qualifier_value esva ".
"WHERE e.bioentry_id = esva.bioentry_id ".
"AND sv.term_id = esva.term_id ".
"AND (e.bioentry_id = 10 AND sv.ontology_id = 3)");
$query->datacollections(
["Bio::DB::BioSQL::PrimarySeqAdaptor",
"Bio::DB::BioSQL::SimpleValueAdaptor sv",
"Bio::DB::BioSQL::PrimarySeqAdaptor<=>Bio::DB::BioSQL::SimpleValueAdaptor"]);
$query->where(["Bio::PrimarySeqI::primary_key = 10",
"Bio::Annotation::SimpleValue::ontology = 3"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql,
"SELECT * ".
"FROM bioentry, term sv, bioentry_qualifier_value ".
"WHERE bioentry.bioentry_id = bioentry_qualifier_value.bioentry_id ".
"AND sv.term_id = bioentry_qualifier_value.term_id ".
"AND (bioentry.bioentry_id = 10 AND sv.ontology_id = 3)");
$query->datacollections(
["Bio::PrimarySeqI c::subject",
"Bio::PrimarySeqI p::object",
"Bio::PrimarySeqI<=>Bio::PrimarySeqI<=>Bio::Ontology::TermI"]);
$query->where(["p.accession_number = 'Hs.2'",
"Bio::Ontology::TermI::name = 'cluster member'"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql,
"SELECT * ".
"FROM bioentry c, bioentry p, term, bioentry_relationship ".
"WHERE c.bioentry_id = bioentry_relationship.subject_bioentry_id ".
"AND p.bioentry_id = bioentry_relationship.object_bioentry_id ".
"AND term.term_id = bioentry_relationship.term_id ".
"AND (p.accession = 'Hs.2' AND term.name = 'cluster member')");
# this must also work with different objects in the association that map
# to the same tables though
$query->datacollections(
["Bio::PrimarySeqI c::subject",
"Bio::PrimarySeqI p::object",
"Bio::PrimarySeqI<=>Bio::ClusterI<=>Bio::Ontology::TermI"]);
$query->where(["p.accession_number = 'Hs.2'",
"Bio::Ontology::TermI::name = 'cluster member'"]);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql,
"SELECT * ".
"FROM bioentry c, bioentry p, term, bioentry_relationship ".
"WHERE c.bioentry_id = bioentry_relationship.subject_bioentry_id ".
"AND p.bioentry_id = bioentry_relationship.object_bioentry_id ".
"AND term.term_id = bioentry_relationship.term_id ".
"AND (p.accession = 'Hs.2' AND term.name = 'cluster member')");
$query = Bio::DB::Query::BioQuery->new(
-datacollections => ["Bio::Ontology::OntologyI=>Bio::Ontology::PathI o",
"Bio::Ontology::TermI=>Bio::Ontology::PathI ts::subject",
"Bio::Ontology::TermI=>Bio::Ontology::PathI to::object",
],
-where => ["o.name = 'My Test Ontology'",
"ts.name = 'exon'",
"to.name = 'gene'"]
);
$tquery = $query->translate_query($mapper);
$sql = $sqlgen->generate_sql($tquery);
is ($sql,
"SELECT * ".
"FROM term_path, ontology o, term ts, term to ".
"WHERE term_path.ontology_id = o.ontology_id ".
"AND term_path.subject_term_id = ts.term_id ".
"AND term_path.object_term_id = to.term_id ".
"AND (o.name = 'My Test Ontology' ".
"AND ts.name = 'exon' AND to.name = 'gene')");
Jump to Line
Something went wrong with that request. Please try again.