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

Adam Witney awitney at sgul.ac.uk
Thu Feb 11 06:35:14 EST 2010


Hi Scott,

This actually only implements it in the same way as it does for the mysql version and does not make use of PostgeSQL schemas. That is it adds a namespace prefix with an underscore to the table names.

I think it should be workable using PostgreSQL schemas, but this will then work differently to the mysql version, is that a problem?

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




More information about the Bioperl-l mailing list