Menu
▾
▴
[GUSDEV] Re: bugzilla bug 95: comment and feature tables
|
From: Jian Lu <jl...@vb...> - 2005-08-04 22:55:33
|
Hi Chris, I don't have real feature relationship use cases. AAFeatureRelationship and AAFeatRelationshipType are completely based on consideration of consistency of NA and AA. I agree with you if this is not higher priority from community-wide need. Thank you. Jian Chris Stoeckert wrote: > Dear Jian, > No problem with your first two proposals to add comment_name_id and > AAFeatureComment. Should be able to address in an upcoming minor > release. However, these will be ALTER TABLE to add comment_name_id to > existing tables NAComment and NAFeatureComment rather than creating > tables. > Should also be no problem accepting the third proposal for > AAFeatureRelationship and AAFeatRelationshipType however our policy > has been to NOT add tables just for consistency in order to avoid > schema bloat with tables that no one uses. Do you (or anyone else) > have use cases for these? It will help in the documentation. > > Thanks, > Chris > > To store curation comments has been our concern for a while. By > looking at NA > and AA comment tables, they couldn't meet such needs. > 1. there is no way you can tell the type of comments from NAComment and > NAFeatureComment tables so that comment_name_id is needed as a foreign key > reference to CommentName table. > 2. To add curation comments to AA features from protein annotation, > new table > AAFeatureComment is needed. > 3. To keep feature relationship, there are tables > NAFEATRELATIONSHIPTYPE and > NAFEATURERELATIONSHIP. But there are no such tables on AA. To make > consistent > and systematic schema and have potential relationship between AA features, > similar tables AAFEATRELATIONSHIPTYPE and AAFEATURERELATIONSHIP are > needed. > > Following are SQL for those changes. > > CREATE TABLE DoTS.NAComment ( > NA_COMMENT_ID NUMBER(10,0) NOT NULL, > NA_SEQUENCE_ID NUMBER(10,0) NOT NULL, > COMMENT_NAME_ID(10,0), > COMMENT_STRING CLOB NOT NULL, > MODIFICATION_DATE DATE NOT NULL, > USER_READ NUMBER(1,0) NOT NULL, > USER_WRITE NUMBER(1,0) NOT NULL, > GROUP_READ NUMBER(1,0) NOT NULL, > GROUP_WRITE NUMBER(1,0) NOT NULL, > OTHER_READ NUMBER(1,0) NOT NULL, > OTHER_WRITE NUMBER(1,0) NOT NULL, > ROW_USER_ID NUMBER(12,0) NOT NULL, > ROW_GROUP_ID NUMBER(4,0) NOT NULL, > ROW_PROJECT_ID NUMBER(4,0) NOT NULL, > ROW_ALG_INVOCATION_ID NUMBER(12,0) NOT NULL); > ALTER TABLE DOTS.NAComment ADD CONSTRAINT NAComment_FK > FOREIGN KEY (COMMENT_NAME_ID) REFERENCES DOTS.CommentName > (COMMENT_NAME_ID); > > CREATE TABLE DoTS.NAFeatureComment ( > NA_Feature_COMMENT_ID NUMBER(10,0) NOT NULL, > NA_Feature_ID NUMBER(10,0) NOT NULL, > COMMENT_NAME_ID(10,0), > COMMENT_STRING CLOB NOT NULL, > MODIFICATION_DATE DATE NOT NULL, > USER_READ NUMBER(1,0) NOT NULL, > USER_WRITE NUMBER(1,0) NOT NULL, > GROUP_READ NUMBER(1,0) NOT NULL, > GROUP_WRITE NUMBER(1,0) NOT NULL, > OTHER_READ NUMBER(1,0) NOT NULL, > OTHER_WRITE NUMBER(1,0) NOT NULL, > ROW_USER_ID NUMBER(12,0) NOT NULL, > ROW_GROUP_ID NUMBER(4,0) NOT NULL, > ROW_PROJECT_ID NUMBER(4,0) NOT NULL, > ROW_ALG_INVOCATION_ID NUMBER(12,0) NOT NULL); > ALTER TABLE DOTS.NAFeatureComment ADD CONSTRAINT NAFeatureComment_FK > FOREIGN KEY (COMMENT_NAME_ID) REFERENCES DOTS.CommentName > (COMMENT_NAME_ID); > > CREATE TABLE DoTS.AAFeatureComment ( > AA_Feature_COMMENT_ID NUMBER(10,0) NOT NULL, > AA_Feature_ID NUMBER(10,0) NOT NULL, > COMMENT_NAME_ID(10,0), > COMMENT_STRING CLOB NOT NULL, > MODIFICATION_DATE DATE NOT NULL, > USER_READ NUMBER(1,0) NOT NULL, > USER_WRITE NUMBER(1,0) NOT NULL, > GROUP_READ NUMBER(1,0) NOT NULL, > GROUP_WRITE NUMBER(1,0) NOT NULL, > OTHER_READ NUMBER(1,0) NOT NULL, > OTHER_WRITE NUMBER(1,0) NOT NULL, > ROW_USER_ID NUMBER(12,0) NOT NULL, > ROW_GROUP_ID NUMBER(4,0) NOT NULL, > ROW_PROJECT_ID NUMBER(4,0) NOT NULL, > ROW_ALG_INVOCATION_ID NUMBER(12,0) NOT NULL); > ALTER TABLE DOTS.AAFeatureComment ADD CONSTRAINT AAFeatureComment_FK > FOREIGN KEY (COMMENT_NAME_ID) REFERENCES DOTS.CommentName > (COMMENT_NAME_ID); > > CREATE TABLE DoTS.AAFeatRelationshipType ( > AA_FEAT_RELATIONSHIP_TYPE_ID NUMBER(10,0) PRIMARY KEY, > NAME VARCHAR2(255) NOT NULL, > DESCRIPTION CLOB NOT NULL, > MODIFICATION_DATE DATE NOT NULL, > USER_READ NUMBER(1,0) NOT NULL, > USER_WRITE NUMBER(1,0) NOT NULL, > GROUP_READ NUMBER(1,0) NOT NULL, > GROUP_WRITE NUMBER(1,0) NOT NULL, > OTHER_READ NUMBER(1,0) NOT NULL, > OTHER_WRITE NUMBER(1,0) NOT NULL, > ROW_USER_ID NUMBER(12,0) NOT NULL, > ROW_GROUP_ID NUMBER(4,0) NOT NULL, > ROW_PROJECT_ID NUMBER(4,0) NOT NULL, > ROW_ALG_INVOCATION_ID NUMBER(12,0) NOT NULL); > > CREATE TABLE DoTS.AAFeatureRelationship ( > AA_Feature_RELATIONSHIP_ID NUMBER(10,0) PRIMARY KEY, > PARENT_AA_Feature_ID NUMBER(10,0) NOT NULL, > CHILD_AA_Feature_ID NUMBER(10,0) NOT NULL, > AA_FEAT_RELATIONSHIP_TYPE_ID NUMBER(10,0) NOT NULL, > ORDINAL NUMBER(10,0) , > MODIFICATION_DATE DATE NOT NULL, > USER_READ NUMBER(1,0) NOT NULL, > USER_WRITE NUMBER(1,0) NOT NULL, > GROUP_READ NUMBER(1,0) NOT NULL, > GROUP_WRITE NUMBER(1,0) NOT NULL, > OTHER_READ NUMBER(1,0) NOT NULL, > OTHER_WRITE NUMBER(1,0) NOT NULL, > ROW_USER_ID NUMBER(12,0) NOT NULL, > ROW_GROUP_ID NUMBER(4,0) NOT NULL, > ROW_PROJECT_ID NUMBER(4,0) NOT NULL, > ROW_ALG_INVOCATION_ID NUMBER(12,0) NOT NULL); > ALTER TABLE DoTS.AAFeatureRelationship ADD CONSTRAINT > AAFeatureRELATIONSHIP_FK1 > FOREIGN KEY (PARENT_AA_Feature_ID) REFERENCES DoTS.AAFeatureImp > (AA_Feature_ID); > > ALTER TABLE DoTS.AAFeatureRelationship ADD CONSTRAINT > AAFeatureRELATIONSHIP_FK2 > FOREIGN KEY (CHILD_AA_Feature_ID) REFERENCES DoTS.AAFeatureImp > (AA_Feature_ID); > > ALTER TABLE DoTS.AAFeatureRelationship ADD CONSTRAINT > AAFeatureRELATIONSHIP_FK3 > FOREIGN KEY (AA_FEAT_RELATIONSHIP_TYPE_ID) REFERENCES > DoTS.AAFeatRelationshipType (AA_FEAT_RELATIONSHIP_TYPE_ID); |
