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

Adam Witney awitney at sgul.ac.uk
Thu Feb 11 10:55:03 EST 2010


Thanks Mark, that makes sense and I've got SeqFeature.t running now with the Pg adaptor.

However, I have just noticed that Bio::DB::SeqFeature::Store::DBI::Pg did have some partial code to support "schema" as well as "namespace" (the latter by inheriting this from the mysql version). 

I have only just started using this code so unless i am not quite understanding what the namespace concept is trying to do here then they should both equate to the same thing? I can remove the redundancy and use just one term; which would be preferred, schema or namespace? it would make sense to use namespace as it would then use the same options as the mysql adaptor. I'll go ahead and do that unless anyone has any objections?

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




More information about the Bioperl-l mailing list