GUS-3.6-SQL-Mod Tables

GUS 3.6: Modified Tables, Views, and Materialized Views

Work in Progress, Jan 23 2008; Check for updates.

IMPORTANT - Do not modify this page!

Please submit suggestions on the GUS-3.6 Schema Change Request wiki page, or send an email to frank@pcbi.upenn.edu

Back to Proposed Schema Changes

---

For NEW Tables, Views, and Materialized Views see GUS-3.6-SQL-New Tables

Note: This document contains "Psuedo SQL" representing the changes to existing tables. The actual SQL for performing schema upgrades will require recreating tables that specify new columns. This is due to the structure of GUS requiring GUS-specific columns and the end of table schemas. The upcoming release of GUS will contain only a build script, the upgrade script with be a separate release.

---

MODIFIED GUS Tables, View and Materialized Views

MODIFIED GUS Tables

  Core.GroupInfo
  Core.UserGroup
  DoTS.AAFeatureImp
  DoTS.AASequenceGroupImp
  DoTS.AASequenceImp
  DoTS.NAFeatureImp
  DoTS.NASequenceImp
  DoTS.SecondaryAccs
  DoTS.SequenceGroupImp
  DoTS.Similarity
  RAD.AcquisitionParam
  RAD.Analysis
  RAD.AnalysisInput
  RAD.AnalysisResultImp
  RAD.Control
  TESS.Analysis
  TESS.AnalysisInput
  TESS.AnalysisParam
  TESS.AnalysisQcParam
  TESS.Footprint
  TESS.LogicalGroup
  TESS.LogicalGroupLink
  TESS.ModelPredecessorGraph
  TESS.ModelRELATION
  TESS.ModelRESULT
  TESS.ParameterGroup
  TESS.PerformancePoint
  TESS.PerformancePTParamValue
  TESS.PerformanceResult
  TESS.PERFORMANCESUMMARYSTAT
  TESS.TRAININGSET

MODIFIED GUS Views

(Incomplete)

MODIFIED GUS Materialized Views

(Incomplete)

---

CORE

ALTER TABLE core.GroupInfo MODIFY group_id number(4,0);
ALTER TABLE core.UserGroup MODIFY group_id number(4,0);

DoTS

ALTER TABLE DoTS.secondaryaccs MODIFY SECONDARY_ACCS VARCHAR2(50);

ALTER TABLE DoTS.AAFeatureImp  MODIFY (
  INT1       NUMBER(12),
  INT2       NUMBER(12),
  INT3       NUMBER(12),
  INT4       NUMBER(12),
  INT5       NUMBER(12),
  INT6       NUMBER(12),
  INT7       NUMBER(12),
  INT8       NUMBER(12),
  INT9       NUMBER(12),
  INT10      NUMBER(12),
  TINYINT1   NUMBER(3),
  TINYINT2   NUMBER(3),
  TINYINT3   NUMBER(3),
  TINYINT4   NUMBER(3),
  TINYINT5   NUMBER(3),
  TINYINT6   NUMBER(3),
  FLOAT1     FLOAT(126),
  FLOAT2     FLOAT(126),
  FLOAT3     FLOAT(126),
  FLOAT4     FLOAT(126),
  FLOAT5     FLOAT(126),
  FLOAT6     FLOAT(126),
  STRING1    VARCHAR2(255),
  STRING2    VARCHAR2(255),
  STRING3    VARCHAR2(255),
  STRING4    VARCHAR2(255),
  STRING5    VARCHAR2(255),
  STRING6    VARCHAR2(255)
);


ALTER TABLE DoTS.AASequenceGroupImp ADD ( /* after NUMBER_OF_MEMBERS */
  MAX_SCORE FLOAT,
  MIN_SCORE FLOAT
);

ALTER TABLE DoTS.AASequenceImp MODIFY
  SOURCE_ID VARCHAR2(64);
ALTER TABLE DoTS.AASequenceImp ADD (
  TINYINT1 NUMBER(3,0) , /* after TAXON_ID */
  INT1 NUMBER(12,0) ,    /* after STRING4 */
  INT2 NUMBER(12,0)
);

ALTER TABLE DoTS.NAFeatureImp DROP COLUMN (
 NUMBER3,
 NUMBER4,
 NUMBER5,
 NUMBER6,
 NUMBER7,
 NUMBER8,
 NUMBER9,
 NUMBER10
);
ALTER TABLE DoTS.NAFeatureImp ADD (
  INT1       NUMBER(12), /* after REVIEW_STATUS_ID */
  INT2       NUMBER(12),
  INT3       NUMBER(12),
  INT4       NUMBER(12),
  INT5       NUMBER(12),
  INT6       NUMBER(12),
  TINYINT2   NUMBER(3),
  TINYINT3   NUMBER(3),
  TINYINT4   NUMBER(3),
  TINYINT5   NUMBER(3),
  TINYINT6   NUMBER(3),
  FLOAT4     FLOAT(126), /* after FLOAT3 */
  FLOAT5     FLOAT(126),
  FLOAT6     FLOAT(126),
  STRING43  VARCHAR2(255), /* after STRING42 */
  STRING44  VARCHAR2(255),
  STRING45  VARCHAR2(255),
  STRING46  VARCHAR2(255),
  STRING47  VARCHAR2(255),
  STRING48  VARCHAR2(255),
  STRING49  VARCHAR2(255),
  STRING50  VARCHAR2(255)
);
ALTER TABLE DoTS.NAFeatureImp MODIFY (
 NUMBER1   NUMBER(3),
 STRING1   VARCHAR2(1000),
 STRING2   VARCHAR2(255),
 STRING3   VARCHAR2(255),
 STRING4   VARCHAR2(255),
 STRING5   VARCHAR2(2000),
 STRING6   VARCHAR2(255),
 STRING7   VARCHAR2(255),
 STRING8   VARCHAR2(255),
 STRING9   VARCHAR2(1000),
 STRING10  VARCHAR2(1024),
 STRING11  VARCHAR2(255),
 STRING12  VARCHAR2(255),
 STRING13  VARCHAR2(1000),
 STRING14  VARCHAR2(500),
 STRING15  VARCHAR2(255),
 STRING16  VARCHAR2(255),
 STRING17  VARCHAR2(255),
 STRING18  VARCHAR2(255),
 STRING19  VARCHAR2(255),
 STRING20  VARCHAR2(4000),
 STRING21  VARCHAR2(255),
 STRING22  VARCHAR2(255),
 STRING23  VARCHAR2(255),
 STRING24  VARCHAR2(255),
 STRING25  VARCHAR2(255),
 STRING26  VARCHAR2(255),
 STRING27  VARCHAR2(255),
 STRING28  VARCHAR2(255),
 STRING29  VARCHAR2(255),
 STRING30  VARCHAR2(255),
 STRING31  VARCHAR2(255),
 STRING32  VARCHAR2(255),
 STRING33  VARCHAR2(255),
 STRING34  VARCHAR2(255),
 STRING35  VARCHAR2(255),
 STRING36  VARCHAR2(255),
 STRING37  VARCHAR2(255),
 STRING38  VARCHAR2(255),
 STRING39  VARCHAR2(255),
 STRING40  VARCHAR2(255),
 STRING41  VARCHAR2(255),
 STRING42  VARCHAR2(255)
);
ALTER TABLE DoTS.NAFeatureImp RENAME (
 CLOB1 TO TEXT1,
 NUMBER1 TO  TINYINT1
);



ALTER TABLE DoTS.NASequenceImp ADD (
  STRING5 VARCHAR2(255) ,  /* after STRING4 */
  TINYINT1 NUMBER(3,0) ,   /* after STRING5 */
  INT5 NUMBER(12),         /* after INT4    */
  BIT1 NUMBER(1,0),        /* after INT5    */
  CLOB3 CLOB,              /* after CLOB2   */
  CLOB4 CLOB,              /* after CLOB3   */
  DATE1 DATE,              /* after CLOB4   */
  DATE2 DATE               /* after DATE2   */
);
ALTER TABLE DoTS.NASequenceImp RENAME (
  NUMBER1 TO INT1,
  NUMBER2 TO INT2,
  NUMBER3 TO INT3,
  NUMBER4 TO INT4
);

ALTER TABLE DoTS.SecondaryAccs MODIFY SECONDARY_ACCS VARCHAR2(50); /* was VARCHAR2(20) */
ALTER TABLE DoTS.SequenceGroupImp ADD {
  MAX_SCORE FLOAT,   /* after NUMBER_OF_TAXA */
  MIN_SCORE FLOAT
);

ALTER TABLE DoTS.Similarity ADD ALGORITHM_ID NUMBER(5,0); /* after READING_FRAME */

RAD

ALTER TABLE RAD.AcquisitionParam MODIFY (ACQUISITION_ID NUMBER(8));

ALTER TABLE RAD.Control modify (control_id number(10));

ALTER TABLE RAD.Analysis ADD NAME VARCHAR2(200); /* after PROTOCOL_ID */

ALTER TABLE RAD.AnalysisInput ADD ORDER_NUM NUMBER(8,0); /* after ANALYSIS_ID */

ALTER TABLE RAD.AnalysisResultImp ADD (
  SMALLINT1 NUMBER(5,0),    /* after ROW_ID */
  SMALLINT2 NUMBER(5,0),
  SMALLINT3 NUMBER(5,0),
  INT1 NUMBER(8,0),
  INT2 NUMBER(8,0),
  INT3 NUMBER(8,0),
  INT4 NUMBER(8,0),
  TINYSTRING1 VARCHAR2(10), /* after FLOAT7 */
  STRING2 VARCHAR2(200),    /* after STRING2 */
  STRING3 VARCHAR2(200)
);

Study

SRES

ALTER TABLE SRES.GOSYNONYM MODIFY TEXT varchar(1000);

TESS

ALTER TABLE TESS.ANALYSIS MODIFY NAME NULL;
ALTER TABLE TESS.ANALYSIS MODIFY NAME varchar(200);
ALTER TABLE TESS.ANALYSIS
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.analysisInput
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.analysisParam
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.analysisQcParam
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.FOOTPRINT
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.LOGICALGROUP
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.LOGICALGROUPLINK
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.MODELPREDECESSORGRAPH
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.MODELRELATION
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.MODELRESULT
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.PARAMETERGROUP
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.PERFORMANCEPOINT
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.PERFORMANCEPTPARAMVALUE
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.PERFORMANCERESULT
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.PERFORMANCESUMMARYSTAT
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

ALTER TABLE TESS.TRAININGSET
modify (
 ROW_USER_ID            NUMBER(12),
 ROW_GROUP_ID           NUMBER(3),
 ROW_PROJECT_ID         NUMBER(4),
 ROW_ALG_INVOCATION_ID  NUMBER(12)
);

---

GUS Ver Tables - Incomplete

COREVer

ALTER TABLE core.GroupInfoVer MODIFY group_id number(4,0);
ALTER TABLE core.UserGroupVer MODIFY group_id number(4,0);

DOTSVer

*Create table DoTSver.stsver - note, need to check current install script, may move this to table create script

ALTER TABLE DoTS.SequenceGroupImp ADD {
  MAX_SCORE FLOAT, /* after NUMBER_OF_TAXA */
  MIN_SCORE FLOAT
);

RADVer

ALTER TABLE RADVER.Controlver modify (control_id number(10));

SRESVer

StudyVer

TessVer

---

These tables were DEPRECATED in GUS 3.5

  RAD.ProcessImplementation
  RAD.ProcessImplementationParam
  RAD.ProcessInvocation
  RAD.ProcessInvocationParam
  RAD.ProcessInvQuantification
  RAD.ProcessIO
  RAD.ProcessIOElement
  RAD.ProcessResult

---

Back to Proposed Schema Changes