FAQ
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@genome.stanford.edu

Search Discussions

  • Benjamin Hitz at Sep 20, 2007 at 11:30 pm

    DBIx::Class::Feature->many_to_many(gos => 'go_annotations',
    'feature_no');
    DBIx::Class::Go_Annotation->many_to_many(references => 'go_refs',
    'go_annotation_no');
    These I am sure I messed up.
    In Go_Annotation.pm we have:
    =================
    __PACKAGE__->belongs_to("go_no", "DB::Mod::Go", { go_no => "go_no" });
    __PACKAGE__->belongs_to(
    "feature_no",
    "DB::Mod::Feature",
    { feature_no => "feature_no" },
    );

    and
    __PACKAGE__->has_many(
    "go_refs",
    "DB::Mod::Go_ref",
    { "foreign.go_annotation_no" => "self.go_annotation_no" },
    );
    ================

    So the above should be:

    DBIx::Class::Feature->many_to_many(goes => 'go_annotations', 'go_no');
    and



    with
    ===========
    __PACKAGE__->belongs_to(
    "reference_no",
    "DB::Mod::Reference",
    { reference_no => "reference_no" },
    );
    ===========
    In the Go_ref.pm module.

    DBIx::Class::Go_annotation->many_to_many(references => 'go_refs',
    reference_no);

    Still not sure if there is a tricky way to span directly from Feature-
    Reference via Go_annotation and Go_ref.
    Maybe just make my own accessor call "go_references" in Feature.pm?

    Thanks, and sorry for working this out myself via mailing list
    Ben
    --
    Ben Hitz
    Senior Scientific Programmer ** Saccharomyces Genome Database ** GO
    Consortium
    Stanford University ** hitz@genome.stanford.edu
  • Benjamin Hitz at Sep 20, 2007 at 11:45 pm
    And as requested, constraints, although understand this is not
    complete - I am just trying to isolate the regions of the schema
    germaine to my questions.
    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_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)
    /
    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
    )
    ALTER TABLE GO
    ADD (CONSTRAINT GO_PK PRIMARY KEY
    (GO_NO)
    ALTER TABLE GO
    ADD (CONSTRAINT GO_TERM_UK UNIQUE
    (GO_TERM
    ,GO_ASPECT)
    USING INDEX
    PCTFREE 10
    STORAGE
    (
    PCTINCREASE 0
    INITIAL 500K
    NEXT 500K
    )
    TABLESPACE INDEX1)
    /

    ALTER TABLE GO
    ADD (CONSTRAINT GO_GOID_UK UNIQUE
    (GOID)
    USING INDEX
    PCTFREE 5
    STORAGE
    (
    PCTINCREASE 0
    INITIAL 500K
    NEXT 500K
    )
    TABLESPACE INDEX1)
    /

    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 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))

    (but I think these are not relevant)


    --
    Ben Hitz
    Senior Scientific Programmer ** Saccharomyces Genome Database ** GO
    Consortium
    Stanford University ** hitz@genome.stanford.edu
  • Ben Hitz at Sep 24, 2007 at 5:11 pm
    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@genome.stanford.edu
  • Matt S Trout at Sep 24, 2007 at 9:56 pm

    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/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedSep 20, '07 at 8:05p
activeSep 24, '07 at 9:56p
posts5
users2
websitedbix-class.org
irc#dbix-class

2 users in discussion

Ben Hitz: 4 posts Matt S Trout: 1 post

People

Translate

site design / logo © 2022 Grokbase