analytics/configuration/schemas/ans/oracle/ANS2_UPDATE_Oracle.sql

--
--    Licensed Materials - Property of IBM
--    
--    BI and PM: ANS
--    
--    (c) Copyright IBM Corp. 2003, 2010.
--    
--    US Government Users Restricted RigANS - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
--    


-------------------------------------------------------------------------------
-- ANS2_UPDATE_Oracle.SQL	   *DO NOT EDIT*	Data Store Version: 2		 
-------------------------------------------------------------------------------


-------------------------------------------------------------------------------
--$100-- backup create begin
-------------------------------------------------------------------------------
CREATE TABLE BAK_DIMENSIONMEMBER (id NUMBER NOT NULL, compositeID VARCHAR2(255) NOT NULL, dimensionId NUMBER NOT NULL, PRIMARY KEY (id));
CREATE TABLE BAK_CONTEXT (id NUMBER NOT NULL, compositeID VARCHAR2(255), PRIMARY KEY (id));
CREATE TABLE BAK_DIMENSION_METADATAITEM (dimensionId NUMBER, metadataId NUMBER);
CREATE TABLE BAK_MODEL_DIMENSION (modelId NUMBER, dimensionId NUMBER);
CREATE TABLE BAK_DIMENSION (id NUMBER NOT NULL, compositeID VARCHAR2(255), PRIMARY KEY (id));

-------------------------------------------------------------------------------
--$110-- backup inserts begin
-------------------------------------------------------------------------------
INSERT INTO BAK_DIMENSIONMEMBER (id, compositeID, dimensionId) SELECT id, compositeID, dimensionId  FROM ANN_DIMENSIONMEMBER;
INSERT INTO BAK_CONTEXT (id, compositeID) SELECT id, compositeID FROM ANN_CONTEXT;
INSERT INTO BAK_DIMENSION_METADATAITEM (dimensionId, metadataId)SELECT dimensionId, metadataId FROM ANN_DIMENSION_METADATAITEM;
INSERT INTO BAK_MODEL_DIMENSION (modelId, dimensionId)SELECT modelId, dimensionId FROM ANN_MODEL_DIMENSION;
INSERT INTO BAK_DIMENSION (id, compositeID)SELECT id, compositeID FROM ANN_DIMENSION;
-------------------------------------------------------------------------------
--$130-- views drop begin
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$140-- tables drop begin
-------------------------------------------------------------------------------
DROP TABLE ANN_DIMENSIONMEMBER;
DROP TABLE ANN_CONTEXT;
DROP TABLE ANN_DIMENSION_METADATAITEM;
DROP TABLE ANN_MODEL_DIMENSION;
DROP TABLE ANN_DIMENSION;

-------------------------------------------------------------------------------
--$200-- recreate tables begin
-------------------------------------------------------------------------------

CREATE TABLE ANN_DIMENSIONMEMBER (id NUMBER NOT NULL, compID VARCHAR2(255) NOT NULL, dimId NUMBER NOT NULL, PRIMARY KEY (id), CONSTRAINT UNQ_dimIdcompID UNIQUE (dimId, compID));
CREATE TABLE ANN_CONTEXT (id NUMBER NOT NULL, compID VARCHAR2(255), PRIMARY KEY (id));
CREATE TABLE ANN_DIMENSION_METADATAITEM (dimId NUMBER, metadataId NUMBER);
CREATE TABLE ANN_MODEL_DIMENSION (modelId NUMBER, dimId NUMBER);
CREATE TABLE ANN_DIMENSION (id NUMBER NOT NULL, compID VARCHAR2(255), PRIMARY KEY (id));
-------------------------------------------------------------------------------
--$202-- primary keys add begin
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$203-- alter tables begin
-------------------------------------------------------------------------------

CREATE UNIQUE INDEX I_NN_CTXT_COMPOSIT ON ANN_CONTEXT (compID);
CREATE UNIQUE INDEX I_NN_DNSN_COMPOSIT ON ANN_DIMENSION (compID);
CREATE INDEX I_NN_DMBR_COMPOSIT ON ANN_DIMENSIONMEMBER (compID);
CREATE INDEX I_NN_DMBR_DIMENSIO ON ANN_DIMENSIONMEMBER (dimId);
CREATE INDEX I_NN_DTTM_DIMENSIO ON ANN_DIMENSION_METADATAITEM (dimId);
CREATE INDEX I_NN_MNSN_ELEMENT ON ANN_MODEL_DIMENSION (dimId);

-------------------------------------------------------------------------------
--$210-- views create begin
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
--$220-- server data inserts begin
-------------------------------------------------------------------------------
INSERT INTO ANN_VERSION(id, version) VALUES ('2', 2);
 -------------------------------------------------------------------------------
 --$230-- restore inserts begin
 -------------------------------------------------------------------------------
INSERT INTO ANN_DIMENSIONMEMBER (id, compID, dimId) SELECT id, compositeID, dimensionId  FROM BAK_DIMENSIONMEMBER;
INSERT INTO ANN_CONTEXT (id, compID) SELECT id, compositeID FROM BAK_CONTEXT;
INSERT INTO ANN_DIMENSION_METADATAITEM (dimId, metadataId)SELECT dimensionId, metadataId FROM BAK_DIMENSION_METADATAITEM;
INSERT INTO ANN_MODEL_DIMENSION (modelId, dimId)SELECT modelId, dimensionId FROM BAK_MODEL_DIMENSION;
INSERT INTO ANN_DIMENSION (id, compID)SELECT id, compositeID FROM BAK_DIMENSION;
 -------------------------------------------------------------------------------
 --$250-- backup drop begin
 -------------------------------------------------------------------------------
DROP TABLE BAK_DIMENSIONMEMBER;
DROP TABLE BAK_CONTEXT;
DROP TABLE BAK_DIMENSION_METADATAITEM;
DROP TABLE BAK_MODEL_DIMENSION;
DROP TABLE BAK_DIMENSION;

-------------------------------------------------------------------------------
--$260-- end sql by function
-------------------------------------------------------------------------------
-- END ANS2_UPDATE_Oracle.SQL			*	DO NOT EDIT	 * 		 
-------------------------------------------------------------------------------