Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="collections/collections_schema_upgrade.sql+"/>
Rem
Rem $Header: credentials_schema_upgrade.sql 02-sep-2005.10:28:25 neearora Exp $
Rem
Rem credentials_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      credentials_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    neearora    09/02/05 - Added ALTER TYPE MGMT_CRED_RECORD 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    skini       03/07/05 - Upgrade MGMT_CREDENTIALS 
Rem    skini       02/16/05 - Upgrade CRED_SET_COL_RECORD 
Rem    skini       02/15/05 - Schema upgrade 
Rem    rpinnama    02/06/05 - rpinnama_add_upgrade_scripts
Rem    rpinnama    02/04/05 - Created
Rem

CREATE OR REPLACE TYPE MGMT_CRED_ARRAY AS
     TABLE OF MGMT_CRED_RECORD;
/

CREATE OR REPLACE TYPE MGMT_COLLECTION_CRED_RECORD AS OBJECT (
    target_name VARCHAR2(256),
    target_type VARCHAR2(64),
    metric_name VARCHAR2(64),
    metric_column VARCHAR2(64),
    collection_name VARCHAR2(64),
    credential MGMT_CRED_RECORD
);
/

CREATE OR REPLACE TYPE MGMT_COLLECTION_CRED_ARRAY AS TABLE OF MGMT_COLLECTION_CRED_RECORD;
/

CREATE OR REPLACE TYPE MGMT_CREDS_LOUTPUT AS TABLE OF VARCHAR2(128);
/

CREATE OR REPLACE TYPE MGMT_CREDS_SOUTPUT AS TABLE OF VARCHAR2(10);
/

REM Represents a credentials set column value
CREATE OR REPLACE TYPE MGMT_CRED_SET_COL_VAL AS OBJECT (
    value VARCHAR2(4000),
    default_value NUMBER(1)
);
/

REM Represents an array of credential set column values
CREATE OR REPLACE TYPE MGMT_CRED_SET_COL_VAL_ARRAY AS 
     TABLE OF MGMT_CRED_SET_COL_VAL;
/

-------------------------------------------------------------------------
------------------------ SCHEMA UPGRADE ----------------------------------
-------------------------------------------------------------------------
ALTER TABLE MGMT_CREDENTIAL_TYPE_COL_VALS ADD(value_nlsid VARCHAR2(64));


CREATE TABLE MGMT_CREDENTIAL_SET_COL_VALS (
    target_type VARCHAR2(64),
    target_type_meta_ver VARCHAR2(8) DEFAULT '1.0',
    set_name VARCHAR2(64),
    set_column_name VARCHAR2(64),
    default_value NUMBER(1), 
    value VARCHAR2(1024),
    value_nlsid	VARCHAR2(64),
    CONSTRAINT PK_CRED_SET_COL_VALS 
        PRIMARY KEY(target_type, target_type_meta_ver, set_name, 
                    set_column_name, value),    
    CONSTRAINT FK_CRED_SET_COL_VALS FOREIGN KEY (target_type, 
                                                  target_type_meta_ver,
                                                  set_name)
        REFERENCES MGMT_CREDENTIAL_SETS(target_type, target_type_meta_ver, 
                                         set_name)
            ON DELETE CASCADE
)
  INITRANS 2
  MONITORING;

ALTER TABLE MGMT_TARGET_CREDENTIALS MODIFY (user_name VARCHAR2(256) DEFAULT '<SYSTEM>');

ALTER TABLE MGMT_HOST_CREDENTIALS MODIFY (user_name VARCHAR2(256) DEFAULT '<SYSTEM>');

ALTER TABLE MGMT_ENTERPRISE_CREDENTIALS MODIFY (user_name VARCHAR2(256) DEFAULT '<SYSTEM>');

ALTER TABLE MGMT_CONTAINER_CREDENTIALS MODIFY (user_name VARCHAR2(256) DEFAULT '<SYSTEM>');

ALTER TABLE MGMT_JOB_CREDENTIALS MODIFY (user_name VARCHAR2(256) DEFAULT '<SYSTEM>');

ALTER TABLE MGMT_CREDENTIALS MODIFY (user_name VARCHAR2(256) );

-- Drop and recreate, this table is guaranteed to have no data
DROP TABLE MGMT_COLLECTION_CREDENTIALS;
CREATE TABLE MGMT_COLLECTION_CREDENTIALS
    (target_guid RAW(16),
     metric_guid RAW(16),
     coll_name VARCHAR2(64),
     credential_set_name VARCHAR2(32),
     credential_guid RAW(16),
     CONSTRAINT PK_MGMT_COLL_CREDS 
         PRIMARY KEY (target_guid, metric_guid, coll_name, credential_set_name)
) ORGANIZATION INDEX
  INITRANS 2
  MONITORING;

CREATE TABLE MGMT_COLLECTION_TEMPLATE_CREDS
    (object_guid RAW(16),
     object_type NUMBER(1),
     target_guid RAW(16),
     metric_guid RAW(16),
     coll_name VARCHAR2(64),
     credential_set_name VARCHAR2(32),
     credential_guid RAW(16),
     CONSTRAINT PK_MGMT_COLL_TMPL_CREDS 
         PRIMARY KEY (object_guid, object_type, target_guid, metric_guid, coll_name, credential_set_name)
) ORGANIZATION INDEX
  INITRANS 2
  MONITORING;


ALTER TABLE MGMT_ARU_CREDENTIALS MONITORING;
ALTER TABLE MGMT_VIEW_USER_CREDENTIALS MONITORING;

-- Upgrade types
ALTER TYPE MGMT_CRED_SET_COL_RECORD ADD attribute (set_column_values MGMT_CRED_SET_COL_VAL_ARRAY) CASCADE;

ALTER TYPE MGMT_CRED_RECORD MODIFY ATTRIBUTE USER_NAME VARCHAR2(256) CASCADE;