[GUSDEV] bugzilla bug 95: comment and feature tables

[Chris S. <sto...@pc...>]

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