[Dbix-class] complex relationships II

Benjamin Hitz hitz at genome.stanford.edu
Thu Sep 20 20:05:41 GMT 2007


Hi, we are in the process of replacing an old, hand-rolled API (and  
bags-o-sql) with DBIc.  Our Oracle database is pretty large (~110  
tables) with some complicated relationships.  We have used  
DBIx::Class::Schema::Loader::Oracle to generate some table classes,  
but of course they don't find all the many-to-manys.

Looking for some advice on how to implement a couple types of  
relationships. This is the second of two emails, and I think is where  
it starts to get ugly
"Triple Linking Tables"
e.g.


CREATE TABLE GO_ANNOTATION
(GO_ANNOTATION_NO NUMBER(10) NOT NULL
,GO_NO NUMBER(10) NOT NULL
,FEATURE_NO NUMBER(10) NOT NULL
,GO_EVIDENCE VARCHAR2(40) NOT NULL
,ANNOTATION_TYPE VARCHAR2(40) NOT NULL
,SOURCE VARCHAR2(40) NOT NULL
,DATE_LAST_REVIEWED DATE DEFAULT SYSDATE NOT NULL
,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
)

This table has foreign keys to GO table and FEATURE table, while the  
GO_REF table acts as a linking table between GO_ANNOTATION and  
REFERENCE.

CREATE TABLE GO
(GO_NO NUMBER(10) NOT NULL
,GOID NUMERIC(10) NOT NULL
,GO_TERM VARCHAR2(240) NOT NULL
,GO_ASPECT VARCHAR2(40) NOT NULL
,GO_DEFINITION VARCHAR2(2000)
,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
)


CREATE TABLE FEATURE
(FEATURE_NO NUMBER(10) NOT NULL
,FEATURE_NAME VARCHAR2(40) NOT NULL
,DBXREF_ID VARCHAR2(40) NOT NULL
,FEATURE_TYPE VARCHAR2(40) NOT NULL
,SOURCE VARCHAR2(40) NOT NULL
,COORD_VERSION DATE
,STOP_COORD NUMBER(10)
,START_COORD NUMBER(10)
,STRAND VARCHAR2(1)
,GENE_NAME VARCHAR2(10)
,NAME_DESCRIPTION VARCHAR2(100)
,GENETIC_POSITION NUMBER(5,2)
,HEADLINE VARCHAR2(960)
,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
)

CREATE TABLE GO_REF
(GO_REF_NO NUMBER(10) NOT NULL
,REFERENCE_NO NUMBER(10) NOT NULL
,GO_ANNOTATION_NO NUMBER(10) NOT NULL
,HAS_QUALIFIER VARCHAR2(1) NOT NULL
,HAS_SUPPORTING_EVIDENCE VARCHAR2(1) NOT NULL
,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
)


CREATE TABLE REFERENCE
(REFERENCE_NO NUMBER(10) NOT NULL
,SOURCE VARCHAR2(40) NOT NULL
,STATUS VARCHAR2(40) NOT NULL
,PDF_STATUS VARCHAR2(40) NOT NULL
,DBXREF_ID VARCHAR2(40) NOT NULL
,CITATION VARCHAR2(480) NOT NULL
,YEAR NUMBER(4) NOT NULL
,PUBMED NUMBER(10)
,DATE_PUBLISHED VARCHAR2(40)
,DATE_REVISED NUMBER(8)
,ISSUE VARCHAR2(40)
,PAGE VARCHAR2(40)
,VOLUME VARCHAR2(40)
,TITLE VARCHAR2(400)
,JOURNAL_NO NUMBER(10)
,BOOK_NO NUMBER(10)
,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
)

So... The following is clear:
(I do have DBIx::Class subclassed for our specific usage, but so as  
not to pollute your brain-name-space)

DBIx::Class::Feature->many_to_many(gos => 'go_annotations',  
'feature_no');
DBIx::Class::Go_Annotation->many_to_many(references => 'go_refs',  
'go_annotation_no');

And DBIx::Class::Go and DBIx::Class::Feature could have many_to_many 
(go_refs)...
but I guess I want the following

DBIx::Class::Feature->many_to_many(go_refs => ??? , ???) which will  
give me DBIx::Class::Reference objects
This would be kind of a many-to-many-to-many???

Ben
--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO  
Consortium
Stanford University ** hitz at genome.stanford.edu






More information about the DBIx-Class mailing list