[Dbix-class] complex relationships II

[Matt S Trout]

On Mon, Sep 24, 2007 at 09:11:19AM -0700, Ben Hitz wrote:
> 
> BUMP!
> >
> >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  
> >DB::Mod::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
> >)
> >
> >
> >ALTER TABLE GO_ANNOTATION
> >ADD (CONSTRAINT GO_ANNOTATION_UK UNIQUE
> >  (GO_NO
> >  ,FEATURE_NO
> >  ,GO_EVIDENCE
> >  ,ANNOTATION_TYPE
> >  ,SOURCE)
> >
> >ALTER TABLE GO_ANNOTATION ADD (CONSTRAINT
> >GOANN_GO_FK FOREIGN KEY
> >  (GO_NO) REFERENCES GO
> >  (GO_NO))
> >/
> >
> >ALTER TABLE GO_ANNOTATION ADD (CONSTRAINT
> >GOANN_FEAT_FK FOREIGN KEY
> >  (FEATURE_NO) REFERENCES FEATURE
> >  (FEATURE_NO) ON DELETE CASCADE)
> >/
> 
> >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
> >)
> >
> >ALTER TABLE GO
> >ADD (CONSTRAINT GO_PK PRIMARY KEY
> >  (GO_NO)
> >
> 
> >ALTER TABLE GO
> >ADD (CONSTRAINT GO_TERM_UK UNIQUE
> >  (GO_TERM
> >  ,GO_ASPECT)
> >
> >ALTER TABLE GO
> >ADD (CONSTRAINT GO_GOID_UK UNIQUE
> >  (GOID)
> 
> >
> >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
> >)
> >
> >
> >ALTER TABLE FEATURE
> >ADD (CONSTRAINT FEATURE_PK PRIMARY KEY
> >  (FEATURE_NO)
> >
> >ALTER TABLE FEATURE
> >ADD (CONSTRAINT FEAT_DBXREF_ID_UK UNIQUE
> >  (DBXREF_ID)
> >
> 
> >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
> >)
> >
> >
> >ALTER TABLE GO_REF
> >ADD (CONSTRAINT GO_REF_UK UNIQUE
> >  (REFERENCE_NO
> >  ,GO_ANNOTATION_NO)
> >
> >ALTER TABLE GO_REF ADD (CONSTRAINT
> >GOREF_REF_FK FOREIGN KEY
> >  (REFERENCE_NO) REFERENCES REFERENCE
> >  (REFERENCE_NO) ON DELETE CASCADE)
> >/
> >
> >ALTER TABLE GO_REF ADD (CONSTRAINT
> >GOREF_GOANN_FK FOREIGN KEY
> >  (GO_ANNOTATION_NO) REFERENCES GO_ANNOTATION
> >  (GO_ANNOTATION_NO) ON DELETE CASCADE)
> >/
> 
> >
> >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
> >)
> >ALTER TABLE REFERENCE
> >ADD (CONSTRAINT REFERENCE_PK PRIMARY KEY
> >  (REFERENCE_NO)
> >
> >ALTER TABLE REFERENCE ADD (CONSTRAINT
> >REF_BOOK_FK FOREIGN KEY
> >  (BOOK_NO) REFERENCES BOOK
> >  (BOOK_NO))
> >/
> >
> >ALTER TABLE REFERENCE ADD (CONSTRAINT
> >REF_JOUR_FK FOREIGN KEY
> >  (JOURNAL_NO) REFERENCES JOURNAL
> >  (JOURNAL_NO))
> >
> 
> I didn't get a reply to this, so I am rephrasing it:
> 
> DB::Mod.pm is a subclass of DBIx::Class::Schema, the DB::Mod::Table  
> classes are subclasses of DBIx::Class
> 
> And some relationships:
> DB::Mod::Feature->has_many(
>   "go_annotations",
>   "DB::Mod::Go_annotation",
>   { "foreign.feature_no" => "self.feature_no" },
> );
> 
> DB::Mod::Go_annotation->belongs_to("go_no", "DB::Mod::Go", { go_no =>  
> "go_no" });
> DB::Mod::Go_annotation->belongs_to(
>   "feature_no",
>   "DB::Mod::Feature",
>   { feature_no => "feature_no" },
> );
> DB::Mod::Go_annotation->has_many(
>   "go_refs",
>   "DB::Mod::Go_ref",
>   { "foreign.go_annotation_no" => "self.go_annotation_no" },
> );
> 
> DB::Mod::Go_ref->belongs_to(
>   "go_annotation_no",
>   "DB::Mod::Go_annotation",
>   { go_annotation_no => "go_annotation_no" },
> );
> DB::Mod::Go_ref->belongs_to(
>   "reference_no",
>   "DB::Mod::Reference",
>   { reference_no => "reference_no" },
> );
> 
> So we can say:
> DB::Mod::Feature->many_to_many(goes => 'go_annotations', 'go_no');
> as well as the reverse (obverse?)
> DB::Mod::Go->many_to_many(features => 'go_annotations', 'feature_no');
> 
> and
> DB::Mod::Go_annotation->many_to_many(references=>'go_refs',  
> 'reference_no');
> and the reverse
> DB::Mod::Reference->many_to_many(go_annotations=>'go_refs',  
> 'go_annotation_no');
> 
> BUT say I want the following method:
> 
> DB::Mod::Feature->go_references (which returns an array of  
> DB::Mod::Reference objects)
> 
> We cannot make a many_to_many between Feature and Go_ref, because  
> go_annotation does not belong_to go_ref (it has many)
> 
> What is the preferred way of setting this up?
> Just make a method in DB::Mod::Feature:
> sub go_references {
> 
> 	@refs = ();
> 	for $goAn ($self->go_annotations) {
> 	      @refs = (@refs, @{$goAn->refs});
>         }
> 	# actually we should probably put them in a hash to uniquify them...
> 	return \@refs;
> }
> 
> I have a few other cases in the schema where I want to span a  
> has_many -> many_to_many... should I my own relationship, or just  
> write specific methods (yes, I realize this is a style question)

All those will tend to return a resultset so

sub go_references { shift->go_annotations->search_related('refs', @_); }

should do the trick.

Must better phrasing of the question this time, thanks :)

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/