[Bioperl-l] Adding namespace support to Bio::DB::SeqFeature::Store::DBI::Pg

Adam Witney awitney at sgul.ac.uk
Sat Feb 13 11:43:08 EST 2010


Hi guys,

I have this working now using PostgreSQL schema support. I have filed a patch on bugzilla (ID: 3011), which includes some extra tests in SeqFeature.t for the DBI::mysql and DBI::Pg adaptors.

let me know if there are any questions or anything you want me to change.

thanks 

adam




On 12 Feb 2010, at 13:38, Chris Fields wrote:

> Code for post-test cleanup is found in Build.PL file; look for the code re: Bio::DB::Seqfeature::Store tests for BDB, Pg, MySQL, etc.
> 
> chris
> 
> On Feb 12, 2010, at 7:25 AM, Adam Witney wrote:
> 
>> 
>> Hi Mark,
>> 
>> What is the general approach for creating a database in the tests and where does that test database then get deleted? My test runs, but it leaves the database in place.
>> 
>> thanks
>> 
>> adam
>> 
>> 
>> On 11 Feb 2010, at 14:34, Mark A. Jensen wrote:
>> 
>>> Hey Adam,
>>> The SeqFeature tests are slightly complex in the Build-- during Build.PL execution,
>>> the user specifies the DBMS, which is dynamically incorporated during the test.
>>> So, any new tests you want to make, you can probably just add to SeqFeature.t,
>>> and the Build script will make sure these are routed to the right handler. Jason/Chris
>>> will certainly correct me if I'm wrong....
>>> cheers MAJ
>>> ----- Original Message ----- From: "Adam Witney" <awitney at sgul.ac.uk>
>>> To: "Scott Cain" <scott at scottcain.net>
>>> Cc: "BioPerl" <bioperl-l at lists.open-bio.org>
>>> Sent: Thursday, February 11, 2010 9:21 AM
>>> Subject: Re: [Bioperl-l] Adding namespace support toBio::DB::SeqFeature::Store::DBI::Pg
>>> 
>>> 
>>>> 
>>>> Hi Scott,
>>>> 
>>>> ok I now have a version working with PostgreSQL schemas.
>>>> 
>>>> I was looking for the SFS tests, but can only find SeqFeature.t which only tests the "memory" adaptor. Are there any tests for the mysql/Pg adaptors?
>>>> 
>>>> thanks
>>>> 
>>>> adam
>>>> 
>>>> On 10 Feb 2010, at 19:35, Scott Cain wrote:
>>>> 
>>>>> Hi Adam,
>>>>> 
>>>>> I don't have a problem with this approach. Can you verify that this
>>>>> patch still passes the Pg SFS tests? I don't generally use
>>>>> schemas/name spaces (same thing,right?) much, so I wouldn't feel
>>>>> comfortable evaluting it completely.  And, even better, could you add
>>>>> to the tests that exercise this behavior?
>>>>> 
>>>>> Thanks,
>>>>> Scott
>>>>> 
>>>>> 
>>>>> On Wednesday, February 10, 2010, Adam Witney <awitney at sgul.ac.uk> wrote:
>>>>>> 
>>>>>> I noticed that namespace's didn't work with Bio::DB::SeqFeature::Store::DBI::Pg because when creating the database the CREATE INDEX statements didn't pass through _qualify to add the namespace prefix, thus giving a "relation "typelist_tab" already exists" error.
>>>>>> 
>>>>>> The patch below fixes this, but the question is does this seem like a good approach?
>>>>>> 
>>>>>> thanks
>>>>>> 
>>>>>> adam
>>>>>> 
>>>>>> 
>>>>>> Index: Pg.pm
>>>>>> ===================================================================
>>>>>> --- Pg.pm       (revision 16551)
>>>>>> +++ Pg.pm       (working copy)
>>>>>> @@ -256,22 +256,20 @@
>>>>>> indexed  int default 1,
>>>>>> object     bytea not null
>>>>>> );
>>>>>> -  CREATE INDEX feature_stuff ON feature(seqid,tier,bin,typeid);
>>>>>> -  CREATE INDEX feature_typeid ON feature(typeid);
>>>>>> END
>>>>>> 
>>>>>>       locationlist => <<END,
>>>>>> (
>>>>>> id         serial primary key,
>>>>>> seqname    varchar(256)   not null
>>>>>> -); CREATE INDEX locationlist_seqname ON locationlist(seqname);
>>>>>> +);
>>>>>> END
>>>>>> 
>>>>>>       typelist => <<END,
>>>>>> (
>>>>>> id       serial primary key,
>>>>>> tag      varchar(256)  not null
>>>>>> -); CREATE INDEX typelist_tab ON typelist(tag);
>>>>>> +);
>>>>>> END
>>>>>>       name => <<END,
>>>>>> (
>>>>>> @@ -279,8 +277,6 @@
>>>>>> name         varchar(256)  not null,
>>>>>> display_name int       default 0
>>>>>> );
>>>>>> -  CREATE INDEX name_id ON name(id);
>>>>>> -  CREATE INDEX name_name ON name(name);
>>>>>> END
>>>>>> 
>>>>>>       attribute => <<END,
>>>>>> @@ -289,8 +285,6 @@
>>>>>> attribute_id     int   not null,
>>>>>> attribute_value  text
>>>>>> );
>>>>>> -  CREATE INDEX attribute_id ON attribute(id);
>>>>>> -  CREATE INDEX attribute_id_val ON attribute(attribute_id,SUBSTR(attribute_value, 1, 10));
>>>>>> END
>>>>>> 
>>>>>>       attributelist => <<END,
>>>>>> @@ -298,14 +292,12 @@
>>>>>> id       serial primary key,
>>>>>> tag      varchar(256)  not null
>>>>>> );
>>>>>> -  CREATE INDEX attributelist_tag ON attributelist(tag);
>>>>>> END
>>>>>>       parent2child => <<END,
>>>>>> (
>>>>>> id               int       not null,
>>>>>> child            int       not null
>>>>>> );
>>>>>> -  CREATE INDEX parent2child_id_child ON parent2child(id,child);
>>>>>> END
>>>>>> 
>>>>>>       meta => <<END,
>>>>>> @@ -325,6 +317,22 @@
>>>>>>      };
>>>>>> }
>>>>>> 
>>>>>> +sub index_definitions {
>>>>>> +  my $self = shift;
>>>>>> +  return {
>>>>>> +         feature_stuff  => "feature(seqid,tier,bin,typeid)",
>>>>>> +         feature_typeid => "feature(typeid)",
>>>>>> +         locationlist_seqname => "locationlist(seqname)",
>>>>>> +      typelist_tab => "typelist(tag)",
>>>>>> +      name_id => "name(id)",
>>>>>> +      name_name => "name(name)",
>>>>>> +      attribute_id => "attribute(id)",
>>>>>> +      attribute_id_val => "attribute(attribute_id,SUBSTR(attribute_value, 1, 10))",
>>>>>> +      attributelist_tag =>  "attributelist(tag)",
>>>>>> +      parent2child_id_child => "parent2child(id,child)",
>>>>>> +        };
>>>>>> +}
>>>>>> +
>>>>>> sub schema {
>>>>>> my ($self, $schema) = @_;
>>>>>> $self->{'schema'} = $schema if defined($schema);
>>>>>> @@ -354,6 +362,18 @@
>>>>>>                     $dbh->do($query) or $self->throw($dbh->errstr);
>>>>>>             }
>>>>>> }
>>>>>> +
>>>>>> +  my $indexes = $self->index_definitions;
>>>>>> +  foreach (keys %$indexes) {
>>>>>> +    my $index = $self->_qualify($_);
>>>>>> +    my $index_def = $self->_qualify($indexes->{$_});
>>>>>> +    $dbh->do("DROP INDEX IF EXISTS $index") if $erase;
>>>>>> +    my @index_exists = $dbh->selectrow_array("SELECT * FROM pg_indexes WHERE indexname = '$index'");
>>>>>> +               if (!scalar(@index_exists)) {
>>>>>> +                       my $query = "CREATE INDEX $index ON $index_def";
>>>>>> +                       $dbh->do($query) or $self->throw($dbh->errstr);
>>>>>> +               }
>>>>>> +  }
>>>>>> $self->subfeatures_are_indexed(1) if $erase;
>>>>>> 1;
>>>>>> }
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> _______________________________________________
>>>>>> Bioperl-l mailing list
>>>>>> Bioperl-l at lists.open-bio.org
>>>>>> http://lists.open-bio.org/mailman/listinfo/bioperl-l
>>>>>> 
>>>>> 
>>>>> -- 
>>>>> ------------------------------------------------------------------------
>>>>> Scott Cain, Ph. D.                                   scott at scottcain dot net
>>>>> GMOD Coordinator (http://gmod.org/)                     216-392-3087
>>>>> Ontario Institute for Cancer Research
>>>>> 
>>>>> _______________________________________________
>>>>> Bioperl-l mailing list
>>>>> Bioperl-l at lists.open-bio.org
>>>>> http://lists.open-bio.org/mailman/listinfo/bioperl-l
>>>> 
>>>> 
>>>> _______________________________________________
>>>> Bioperl-l mailing list
>>>> Bioperl-l at lists.open-bio.org
>>>> http://lists.open-bio.org/mailman/listinfo/bioperl-l
>>>> 
>>> 
>> 
>> 
>> _______________________________________________
>> Bioperl-l mailing list
>> Bioperl-l at lists.open-bio.org
>> http://lists.open-bio.org/mailman/listinfo/bioperl-l
> 
> 
> _______________________________________________
> Bioperl-l mailing list
> Bioperl-l at lists.open-bio.org
> http://lists.open-bio.org/mailman/listinfo/bioperl-l




More information about the Bioperl-l mailing list