[Dbix-class] complex relationships II

Ben Hitz hitz at genome.stanford.edu
Mon Sep 24 17:11:19 GMT 2007


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)

Thanks,

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