Re: [GUSDEV] Incomplete generation of child relations from FK on PostgreSQL

[John I. <io...@pc...>]

Dr. Galea,

Thank you for finding this solution.  I'll test it and check it in to  
the repository.  This will take a little while, since I'll have to  
install PostgreSQL and GUS.  But that will be a good thing to have.

John Iodice
Computational Biology and Informatics Lab


On Aug 18, 2006, at 9:19 AM, an...@ne... wrote:

> Hello all,
> I've stumbled upon a bug in the PostgreSQL implementation of the  
> Perl object
> layer: when generating Perl objects, most childRelations are  
> missing even if
> the PostgreSQL database contains the relevant FK constraint.
>
> The same bug shows up on two different PostgreSQL versions, namely  
> 8.1.4 and
> 7.4.13.
>
> The problem lies in file '$PROJECT_HOME/GUS/ObjRelP/lib/perl/ 
> PostgreSQL.pm':
> the subroutine 'tableChildRelationsSql' returns incorrect (schema  
> names are
> reversed) and incomplete (filtering by schema name discards  
> relations to
> external schemas) data.
>
> I'm attaching a file 'query.pl' that shows the problem on child  
> relations for
> table 'GUS::Model::SRes::Contact', and a modified version of  
> PostgreSQL.pm with
> a query that works correctly for me.
>
> Here is the output with the original query:
>
> TABLE GUS::Model::SRes::Contact
> ---- Child relations from $db->getTableChildRelations
> GUS::Model::SRes::BibliographicReference, contact_id, contact_id
> GUS::Model::SRes::Contact, contact_id, affiliation_id
> ------------
>
> While this is the output with the modified SQL (incidentally, this  
> very same
> output should be what is obtained from a working installation on  
> Oracle):
>
> TABLE GUS::Model::SRes::Contact
> ---- Child relations from $db->getTableChildRelations
> GUS::Model::Core::UserInfo, contact_id, contact_id
> GUS::Model::DoTS::AASequenceEnzymeClass, contact_id,  
> ec_assignment_contact_id
> GUS::Model::DoTS::Attribution, contact_id, contact_id
> GUS::Model::DoTS::CloneSet, contact_id, contact_id
> GUS::Model::DoTS::EST, contact_id, contact_id
> GUS::Model::DoTS::NASequenceImp, contact_id,  
> sequencing_center_contact_id
> GUS::Model::RAD::Analysis, contact_id, operator_id
> GUS::Model::RAD::ArrayDesign, contact_id, manufacturer_id
> GUS::Model::RAD::Assay, contact_id, operator_id
> GUS::Model::RAD::Quantification, contact_id, operator_id
> GUS::Model::SRes::BibliographicReference, contact_id, contact_id
> GUS::Model::SRes::Contact, contact_id, affiliation_id
> GUS::Model::Study::BioMaterialImp, contact_id, bio_source_provider_id
> GUS::Model::Study::Study, contact_id, contact_id
> ------------
>
> Is there anyone using GUSDB on PostgreSQL who can confirm this  
> issue and test
> that my solution does not break anything else?
>
> Thanks a lot in advance,
>
> Antonio Galea
>
>
>
> ----------------------------------------------------------------
> Net Wise webmail system - http://www.netwise.it
> This message was sent using IMP, the Internet Messaging Program.
> <PostgreSQL.pm>
> <query.pl>