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

[Adam Witney]

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