Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="webapp/webapp_schema_upgrade.sql+"/>
Rem
Rem $Header: swlib_schema_upgrade.sql 25-jul-2005.14:04:31 chyu Exp $
Rem
Rem swlib_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      swlib_schema_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    wsmit       07/12/05 - check semicolon 
Rem    wsmit       05/20/05 - lengthen fields 
Rem    kashukla    04/08/05 - 
Rem    eujang      03/10/05 - eujang_rep_upgrade_debug
Rem    kashukla    03/01/05 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100


DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"

--This table holds the entries for all directories created within
--the software library.
--Deletion of a directory will delete all the child directories.
CREATE TABLE "MGMT_SWLIB_DIRECTORIES"(
    DIRECTORY_ID    RAW(16) DEFAULT SYS_GUID()
                    CONSTRAINT "MSD_PK" PRIMARY KEY,
    NAME            NVARCHAR2(64) NOT NULL,
    PARENT_ID       CONSTRAINT "MSD_MSD_FK" 
                    REFERENCES MGMT_SWLIB_DIRECTORIES("DIRECTORY_ID")
                    ON DELETE CASCADE,
    DESCRIPTION     NVARCHAR2(256) DEFAULT '',
    OWNER           NVARCHAR2(64),
    CREATED_DATE    DATE DEFAULT SYSDATE
)
MONITORING;

--All entities are created here.
--Dependent on MGMT_SWLIB_DIRECTORIES and will not allow deletion
--of the directory unless the entity is disassociated from it.
CREATE TABLE "MGMT_SWLIB_ENTITIES"(
    ENTITY_ID       RAW(16)  DEFAULT SYS_GUID()
                    CONSTRAINT "MSE_PK" PRIMARY KEY,
    NAME            NVARCHAR2(64) NOT NULL,
    OWNER           NVARCHAR2(64) NOT NULL,
    CREATION_DATE   DATE DEFAULT SYSDATE,
    DESCRIPTION     NVARCHAR2(256) DEFAULT '',
    TYPE            NVARCHAR2(64) NOT NULL, --Deployment Recipe, Oracle Home
                                            --Image etc
    DIRECTORY_ID    CONSTRAINT "MSE_MSD_FK" 
                    REFERENCES MGMT_SWLIB_DIRECTORIES("DIRECTORY_ID")
		    ON DELETE SET NULL,
    VENDOR          NVARCHAR2(64)
)
MONITORING;

--The set of global maturity status values to be used across the
--SW Library entities. We will populate this table with an initial
--set of meaningful values. We will also allow addition to this set
--via the Java API's. These statuses will be ordered from least mature 
--to most mature.
CREATE TABLE "MGMT_SWLIB_MATURITY_STATUS"(
    MATURITY_STATUS_ID  NUMBER(2) CONSTRAINT "MSMS_PK" PRIMARY KEY,
    MATURITY_STATUS     VARCHAR2(16)
)
MONITORING;

--This table holds the entries for the versions of an entity.
--Deletion of an entity will delete all versions of the entity.
--Deletion of a maturity status can be done only after all entity 
--versions refering to the status has been modified appropriately.
CREATE TABLE "MGMT_SWLIB_ENTITY_REVISIONS"(
    REVISION_ID         RAW(16) DEFAULT SYS_GUID() 
                        CONSTRAINT "MSERV_PK" PRIMARY KEY,
    ENTITY_ID           NOT NULL
                        CONSTRAINT "MSERV_MSE_FK" 
                        REFERENCES MGMT_SWLIB_ENTITIES("ENTITY_ID")
                        ON DELETE CASCADE,
    REVISION            NVARCHAR2(16) NOT NULL,
    MODIFIED_DATE       DATE DEFAULT SYSDATE,
    REVISION_AUTHOR     NVARCHAR2(64),
    MATURITY_STATUS_ID  CONSTRAINT "MSERV_MSMS_FK" 
                        REFERENCES MGMT_SWLIB_MATURITY_STATUS("MATURITY_STATUS_ID"),
    STATUS              NUMBER(2), --Incomplete, Active, Deleted
    PRODUCT             NVARCHAR2(64),
    PRODUCT_VERSION     NVARCHAR2(16),
    DATA_TYPE           VARCHAR2(64),
    CHECKSUM            RAW(16),
    IS_CURRENT          NUMBER(1) --Y/N
)
MONITORING;

--This table holds the entity versions referenced by other entities.
--SOURCE_ID points to the Referer and TARGET_ID points to the Referee
--Deletion of a source entity can be done only if there are no references
--from it. Deletion of a target entity should result in a null TARGET_ID.
CREATE TABLE "MGMT_SWLIB_ENTITY_REFERENCES"(
    REFERENCE_ID        RAW(16) DEFAULT SYS_GUID()
                        CONSTRAINT "MSER_PK" PRIMARY KEY,
    SOURCE_ID           NOT NULL
                        CONSTRAINT "MSER_MSERV_FK"
                        REFERENCES MGMT_SWLIB_ENTITY_REVISIONS("REVISION_ID"),
    NAME                NVARCHAR2(64) NOT NULL,
    TYPE                NVARCHAR2(64),--Type of entity refered to
                                      --Deployment Recipe, Oracle Home, Image etc
    REFINDEX            NUMBER(10), -- index for ensuring order (some headroom)
    TARGET_ID           CONSTRAINT "MSER_MSE_FK"
                        REFERENCES MGMT_SWLIB_ENTITIES("ENTITY_ID")
                        ON DELETE SET NULL,
    TARGET_REVISION_ID  CONSTRAINT "MSER_MSERV_FK2"
                        REFERENCES MGMT_SWLIB_ENTITY_REVISIONS("REVISION_ID")
                        ON DELETE SET NULL,
    TARGET_PRODUCTION   NUMBER(1) 
                        CHECK(TARGET_PRODUCTION IN (0, --0 for CURRENT
                                                    1) --1 for PRODUCTION
                              )
)
MONITORING;

--This table holds the collateral document associated with a reference.
--Deletion of a reference will delete the associated document.
--Deletion of the entity version will be allowed only when the associated
--references and documents are modified appropriately.
CREATE TABLE "MGMT_SWLIB_ENTITY_DOCUMENTS"(
    REVISION_ID         CONSTRAINT "MSED_MSERV_FK"
                        REFERENCES MGMT_SWLIB_ENTITY_REVISIONS("REVISION_ID"),
    NAME                NVARCHAR2(64) NOT NULL,
    REFERENCE_ID        CONSTRAINT "MSED_MSER_FK"
                        REFERENCES MGMT_SWLIB_ENTITY_REFERENCES("REFERENCE_ID")
                        ON DELETE CASCADE,
    VALUE               CLOB
)
LOB (VALUE)
    STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
MONITORING;

--This table holds the data associated with the entity.
--Deletion of the entity version will be allowed only when the associated
--entity data has been deleted.
CREATE TABLE "MGMT_SWLIB_ENTITY_DATA"(
    REVISION_ID         CONSTRAINT "MSEDA_MSERV_FK"
                        REFERENCES MGMT_SWLIB_ENTITY_REVISIONS("REVISION_ID")
			            ON DELETE CASCADE,
    EXTERNAL_KEY        NVARCHAR2(256),--if data is stored externally,will point to approp source
    DATA                BLOB
)
LOB (DATA)
    STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
MONITORING;

--This table holds the application-specific metadata information as name
--value pairs.
--Deletion of the entity will delete all entity parameters associated
--with it.
CREATE TABLE "MGMT_SWLIB_ENTITY_PARAMETERS"(
    ENTITY_ID           CONSTRAINT "MSEP_MSE_FK"
                        REFERENCES MGMT_SWLIB_ENTITIES("ENTITY_ID")
                        ON DELETE CASCADE,
    NAME                NVARCHAR2(64),
    VALUE               NVARCHAR2(256)
)
MONITORING;

--This table holds the revision-specific metadata information as name
--value pairs.
--Deletion of the entity will delete all the revision parameter associated
--with it.
CREATE TABLE "MGMT_SWLIB_REVISION_PARAMETERS"(
    REVISION_ID         CONSTRAINT "MSRP_MSER_FK"
                        REFERENCES MGMT_SWLIB_ENTITY_REVISIONS("REVISION_ID")
                        ON DELETE CASCADE,
    NAME                NVARCHAR2(64),
    VALUE               NVARCHAR2(256)
)
MONITORING;


--This table holds the entity's supported platforms information.
--Deletion of the entity will delete the corresponding platforms assoc
--with it.
CREATE TABLE "MGMT_SWLIB_ENTITY_PLATFORMS"(
    ENTITY_ID           CONSTRAINT "MSEPL_MSE_FK"
                        REFERENCES MGMT_SWLIB_ENTITIES("ENTITY_ID")
                        ON DELETE CASCADE,
    PLATFORM            NVARCHAR2(64)
)
MONITORING;

--This table holds a list of filesystem directories to be used as
--mount points.
--It is not keyed to any other table.
CREATE TABLE "MGMT_SWLIB_DATA_DIRECTORIES"(
    PATH_ID             RAW(16) DEFAULT SYS_GUID()
                        CONSTRAINT "MSDD_PK" PRIMARY KEY,
    FILEPATH            VARCHAR2(255)
)
MONITORING;




-- upgrade indexes now
ALTER TABLE MGMT_SWLIB_ENTITY_PLATFORMS
  ADD CONSTRAINT MGMT_SWLIB_PLAT_PK
    PRIMARY KEY(ENTITY_ID, PLATFORM);

ALTER TABLE MGMT_SWLIB_REVISION_PARAMETERS
  ADD CONSTRAINT MGMT_SWLIB_REV_PAR_PK
    PRIMARY KEY(REVISION_ID, NAME);

ALTER TABLE MGMT_SWLIB_ENTITY_PARAMETERS
  ADD CONSTRAINT MGMT_SWLIB_PAR_PK
    PRIMARY KEY(ENTITY_ID, NAME);

ALTER TABLE MGMT_SWLIB_ENTITY_DATA
  ADD CONSTRAINT MGMT_SWLIB_DAT_PK
    PRIMARY KEY(REVISION_ID);

CREATE INDEX MGMT_SWLIB_DIR_IDX1 ON 
  MGMT_SWLIB_DIRECTORIES(PARENT_ID)
;

CREATE INDEX MGMT_SWLIB_ENT_IDX1 ON
  MGMT_SWLIB_ENTITIES(DIRECTORY_ID, TYPE, NAME)
;

CREATE INDEX MGMT_SWLIB_REV_IDX1 ON
  MGMT_SWLIB_ENTITY_REVISIONS(ENTITY_ID, REVISION)
;

CREATE INDEX MGMT_SWLIB_REF_IDX1 ON
  MGMT_SWLIB_ENTITY_REFERENCES(TARGET_ID)
;


CREATE INDEX MGMT_SWLIB_REF_IDX2 ON
  MGMT_SWLIB_ENTITY_REFERENCES(TARGET_REVISION_ID)
;

CREATE INDEX MGMT_SWLIB_REF_IDX3 ON
  MGMT_SWLIB_ENTITY_REFERENCES(SOURCE_ID, REFINDEX)
;

CREATE INDEX MGMT_SWLIB_DOC_IDX1 ON
  MGMT_SWLIB_ENTITY_DOCUMENTS(REVISION_ID, REFERENCE_ID, NAME)
;

CREATE INDEX MGMT_SWLIB_PAR_IDX1 ON
  MGMT_SWLIB_ENTITY_PARAMETERS(NAME, VALUE)
;

CREATE INDEX MGMT_SWLIB_REV_PAR_IDX1 ON
  MGMT_SWLIB_REVISION_PARAMETERS(NAME, VALUE)
;

CREATE INDEX MGMT_SWLIB_PLAT_IDX1 ON
  MGMT_SWLIB_ENTITY_PLATFORMS(PLATFORM)
;


-- upgrade types now 
CREATE OR REPLACE TYPE MGMT_SWLIB_NVPAIR IS OBJECT
(
    NAME                VARCHAR2(64),
    VALUE               VARCHAR2(256)
);
/
CREATE OR REPLACE TYPE MGMT_SWLIB_PARAMS_LIST IS TABLE OF MGMT_SWLIB_NVPAIR;
/
CREATE OR REPLACE TYPE MGMT_SWLIB_PLATFORM_LIST IS TABLE OF VARCHAR2(64);
/

CREATE OR REPLACE TYPE MGMT_SWLIB_PATH_LIST IS TABLE OF VARCHAR2(255);
/

CREATE OR REPLACE TYPE MGMT_SWLIB_DOCUMENT_RECORD IS OBJECT
(
    REVISION_ID         VARCHAR2(32),
    NAME                VARCHAR2(64),
    REFERENCE_ID        VARCHAR2(32),
    VALUE               CLOB
);
/

CREATE OR REPLACE TYPE MGMT_SWLIB_DOCUMENT_LIST IS TABLE OF MGMT_SWLIB_DOCUMENT_RECORD;
/

CREATE OR REPLACE TYPE MGMT_SWLIB_REVISION_RECORD IS OBJECT
(
    REVISION_ID         VARCHAR2(32),
    REVISION            VARCHAR2(16),
    REVISION_AUTHOR     VARCHAR2(64),
    MATURITY_STATUS     VARCHAR2(16),
    STATUS              NUMBER(2),---enum?
    PRODUCT             VARCHAR2(64),
    PRODUCT_VERSION     VARCHAR2(16),
    DATA_TYPE           VARCHAR2(64),
    CHECKSUM            VARCHAR2(16),
    REV_PARAMS_INFO     MGMT_SWLIB_PARAMS_LIST,
    DOCUMENTS           MGMT_SWLIB_DOCUMENT_LIST 
);
/

CREATE OR REPLACE TYPE MGMT_SWLIB_ENTITY_RECORD IS OBJECT
(
    ENTITY_ID       VARCHAR2(32),
    NAME            VARCHAR2(64),
    OWNER           VARCHAR2(64),
    CREATION_DATE   DATE,
    DESCRIPTION     VARCHAR2(256),
    TYPE            VARCHAR2(64),
    DIRECTORY_ID    VARCHAR2(32),
    VENDOR          VARCHAR2(64),
    REVISION_INFO   MGMT_SWLIB_REVISION_RECORD,
    PLATFORM_INFO   MGMT_SWLIB_PLATFORM_LIST,
    PARAMS_INFO     MGMT_SWLIB_PARAMS_LIST
);
/


CREATE OR REPLACE TYPE MGMT_SWLIB_REFERENCE_RECORD IS OBJECT
(
    REFERENCE_ID        VARCHAR2(32),
    SOURCE_ID           VARCHAR2(32),
    NAME                VARCHAR2(64),
    TYPE                VARCHAR2(64),
    TARGET_ID           VARCHAR2(32),
    TARGET_REVISION     VARCHAR2(16),
    TARGET_PRODUCTION   NUMBER(1),
    DOCUMENTS           MGMT_SWLIB_DOCUMENT_LIST
);
/

CREATE OR REPLACE TYPE MGMT_SWLIB_REFERENCE_LIST IS TABLE OF MGMT_SWLIB_REFERENCE_RECORD;
/

CREATE OR REPLACE TYPE MGMT_SWLIB_DIRECTORY_RECORD IS OBJECT
(
    DIRECTORY_ID	VARCHAR2(32),
    NAME		VARCHAR2(64),
    PARENT_ID		VARCHAR2(32),
    DESCRIPTION		VARCHAR2(256),
    OWNER    		VARCHAR2(64)
);
/
show errors;
/