Rem drv: <create type="tables" pos="storage/storage_tables.sql+"/>
Rem
Rem $Header: swlib_tables.sql 29-jun-2005.01:47:55 gsbhatia Exp $
Rem
Rem swlib_tables.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      swlib_tables.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    gsbhatia    06/26/05 - New repmgr header impl 
Rem    wsmit       05/20/05 - lengthen fields 
Rem    kashukla    04/01/05 - adding table mgmt_swlib_revision_parameter 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    wsmit       01/20/05 - ensure reference order 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    wsmit       10/14/04 - add place to store root filepath 
Rem    wsmit       10/11/04 - 
Rem    wsmit       09/01/04 - 
Rem    rmadampa    07/22/04 - rmadampa_swlib_sql_scripts
Rem    rmadampa    07/08/04 - Created
Rem

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;