[Bioperl-l] Adding namespace support to Bio::DB::SeqFeature::Store::DBI::Pg
Adam Witney
awitney at sgul.ac.uk
Sat Feb 13 16:43:08 UTC 2010
Hi guys,
I have this working now using PostgreSQL schema support. I have filed a patch on bugzilla (ID: 3011), which includes some extra tests in SeqFeature.t for the DBI::mysql and DBI::Pg adaptors.
let me know if there are any questions or anything you want me to change.
thanks
adam
On 12 Feb 2010, at 13:38, Chris Fields wrote:
> Code for post-test cleanup is found in Build.PL file; look for the code re: Bio::DB::Seqfeature::Store tests for BDB, Pg, MySQL, etc.
>
> chris
>
> On Feb 12, 2010, at 7:25 AM, Adam Witney wrote:
>
>>
>> Hi Mark,
>>
>> What is the general approach for creating a database in the tests and where does that test database then get deleted? My test runs, but it leaves the database in place.
>>
>> 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
>>>>
>>>
>>
>>
>> _______________________________________________
>> 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