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

[Adam Witney]

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