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

[Scott Cain]

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