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 [EMAIL PROTECTED];
}
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 ** [EMAIL PROTECTED]
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
