[BioSQL-l] querying your biosql db with the bioperl-db API

[Hilmar Lapp]

Your 'connection' call looks good. You can consult the respective code 
in load_seqdatabase.pl and load_ontology.pl for how to connect.

Bio::DB::BioDB->new() returns an adaptor factory for the chosen 
database (-database option; currently there's only biosql supported), 
or technically an implementation of Bio::DB::DBAdaptorI. Once you have 
the adaptor factory, you can ask it for the persistence adaptor for an 
object or class ($db->get_object_adaptor()) or to create a persistent 
object from a given object ($db->create_persistent(); internally the 
factory accomplishes this by obtaining the object's persistence adaptor 
and then delegating the call to the persistence adaptor's 
create_persistent() method).

Note that technically upon return from Bio::DB::BioDB->new() you're not 
connected yet, and the DBD driver therefore isn't loaded yet, because 
database connection(s) are only created on demand. I.e., the first 
database query (be it an INSERT or UPDATE or SELECT) will trigger the 
connect to the database and therefore loading of the DBD driver. 
Usually you don't need to worry at all about such details; the only 
reason I'm mentioning it here is for debugging purposes that successful 
return from Bio::DB::BioDB->new() doesn't mean that you successfully 
connected to the database or successfully loaded the DBD driver.

Neither the adaptor factory nor the persistence adaptor are DBI objects 
nor similar to them. However, should you ever need low-level access to 
the database handle that a persistence adaptor is using, call 
$adp->dbh(). This will return a DBI database handle. Note though that 
any statement you execute through that handle will be in the 
transaction that the adaptor has currently open, so you will want to be 
careful what you do with it. I usually use it only for debugging 
purposes in order to test things that haven't been committed yet. You 
may also obtain your own connection by calling 
$db->dbcontext->dbi->new_connection() on the adaptor factory. It is 
your responsibility to close and dispose of this connection when done 
with it.

Using SQL to query the database is not bad per se; it would defeat the 
purpose if your desired results are Bioperl objects, not plain tables. 
Bioperl-db's goal is to let you stay in object world and do the mapping 
to the relational world under the hood. If you don't care about staying 
in object world then bioperl-db will only add a layer of complication. 
Also, bioperl-db encapsulates you from the exact naming of the schema, 
or subtle differences between SQL dialects. Furthermore, if support for 
another database schema is added to bioperl-db (e.g., chado), using it 
only requires you to change your connection parameters, not anything 
else in your code. If none of these things matter to you and you know 
SQL and the schema anyway, then issuing SQL queries through DBI is 
probably your fastest way to solve your problem. I do many things 
against the Biosql schema through SQL, albeit through a SQL command 
shell, not even perl.

	-hilmar

On Aug 24, 2005, at 3:39 PM, Amit Indap wrote:

> Hi,
>
> I added a collection of bioentries read from a set of fasta files
> using a custom SeqProcessor (thanks Hilmar and Marc)
>
> What I want to do next is do some simple queries using the Bio::DB
> modules  (like retriveing sequences based on their accession numbers
> or bioentry_id's)
>
> I've been reading through the test scripts that came with the
> bioperl-db code, in particular the 14query.t code.
>
> I think I understand how the sql statements are being generated and 
> translated.
>
> What I don't know how to do is connect to my biosql db and execute my 
> queries.
>
> I have this code that creates a db adapter (would this be something
> similiar to a DBI object?)
>
> I read Hilmar's slides on biosql/bioperl form BOSC 2003 and understand
> the concept that using the bioperld-db api you can access your biosql
> schema. I'm just having a hard time understanding the necessary APIs.
> I guess I could query the biosql db using DBI but that would be
> defeating the whole purpose.
>
> my $dbadp = Bio::DB::BioDB->new(
>                                -database => 'biosql',
>                                -user     => 'amit',
>                                -dbname   =>  'test',
>                                -host     => 'foo.bar.edu',
>                                -port     => ****,
>                                -driver   => 'mysql',
>                    );
>
> Much thanks from a biosql and bioperl newbie
>
> Amit
>
>
>
> --
>
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at open-bio.org
> http://open-bio.org/mailman/listinfo/biosql-l
>
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------