[Dbix-class] complex relationships I

[Benjamin Hitz]

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 long, so I will put the next one in another  
email)

"Trees" e.g.:
CREATE TABLE CV_TERM
(CV_TERM_NO NUMBER(10) NOT NULL
,CV_NO NUMBER(10) NOT NULL
,TERM_NAME VARCHAR2(1024) NOT NULL
,DBXREF_ID VARCHAR2(40)
,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
,CVTERM_DEFINITION VARCHAR2(4000)
)
CREATE TABLE CVTERM_RELATIONSHIP
(CVTERM_RELATIONSHIP_NO NUMBER(10) NOT NULL
,CHILD_CV_TERM_NO NUMBER(10) NOT NULL
,PARENT_CV_TERM_NO NUMBER(10) NOT NULL
,RELATIONSHIP_TYPE VARCHAR2(40) NOT NULL
,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
)
So CVTERM_RELATIONSHIP has two foreign keys to the CV_TERM table.

This comes out from Schema::Loader as:

In the Cv_term.pm module:

__PACKAGE__->has_many(
   "cvterm_relationship_child_cv_term_noes",
   "DB::Mod::Cvterm_relationship",
   { "foreign.child_cv_term_no" => "self.cv_term_no" },
);
__PACKAGE__->has_many(
   "cvterm_relationship_parent_cv_term_noes",
   "DB::Mod::Cvterm_relationship",
   { "foreign.parent_cv_term_no" => "self.cv_term_no" },
);

In the Cvterm_relationship.pm module:
__PACKAGE__->belongs_to(
   "child_cv_term_no",
   "DB::Mod::Cv_term",
   { cv_term_no => "child_cv_term_no" },
);
__PACKAGE__->belongs_to(
   "parent_cv_term_no",
   "DB::Mod::Cv_term",
   { cv_term_no => "parent_cv_term_no" },
);

So, I would guess I want to add:

__PACKAGE__->many_to_many (children =>  
'cvterm_relationship_child_cv_term_noes', 'child_cv_term_no');
and
__PACKAGE__->many_to_many (parents =>  
'cvterm_relationship_parent_cv_term_noes', 'parent_cv_term_no');

to Cv_term.pm

Of course, this is kinda silly because we don't really have multiple  
parents (but it is technically allowed in the schema).

So if I did something like:

my $CvTermObj = DBIx::Class::Schema::Subclass->schema->resultset 
('CV_TERM')->find({term_name => 'physical interactions'});
(actually not sure if find returns a single object in scalar context,  
but that's a detail)

my @arrayOfChildrenWhichAreAlsoCvTermObjs = $cvTermObj->children();

That didn't seem so bad... maybe the next one is tougher?

Ben


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