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

Mark A. Jensen maj at fortinbras.us
Thu Feb 11 09:34:27 EST 2010


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
>
> 



More information about the Bioperl-l mailing list