Rem drv: <migrate type="schema_upgrade" version="11.0.0.0"/>
Rem
Rem $Header: textManager_schema_upgrade.sql 14-mar-2007.04:07:04 ganessub Exp $
Rem
Rem textManager_schema_upgrade.sql
Rem
Rem Copyright (c) 2006, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      textManager_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    ganessub    03/14/07 - Added index on target_guid for
Rem                           mgmt_text_index_stats.
Rem    ganessub    03/06/07 - Added MGMT_TEXT_INDEX_STATS table.
Rem    bmallipe    08/14/06 - adding the schema upgrade script for textManager
Rem    bmallipe    08/14/06 - Created
Rem


Rem MGMT_TEXTINDEX holds information regarding the Text Indexes.
Rem 				on a Host.
Rem   COLUMNS:
Rem   TEXTINDEX_GUID           Unique identification of the text index.
Rem   TARGET_GUID              Unique identification of the target.
Rem   TEXTINDEX_NAME           Name of the Text Index.
rem   TEXTINDEX_SCHEMA_NAME    Name of the Text Index Schema.
Rem		ORACLE_HOME	The Oracle Home 
Rem		TNS_ADMIN		The TNS_ADMIN location

CREATE TABLE MGMT_TEXTINDEX
(
	TEXTINDEX_GUID RAW (50) DEFAULT SYS_GUID() NOT NULL,
	TARGET_GUID RAW (50) NOT NULL,
	TEXTINDEX_NAME VARCHAR2 (200) NOT NULL,
	TEXTINDEX_SCHEMA_NAME VARCHAR2 (200) NOT NULL
);
ALTER TABLE MGMT_TEXTINDEX 
ADD CONSTRAINT mgmt_textindex_logs_pk primary key(TEXTINDEX_GUID);

ALTER TABLE MGMT_TEXTINDEX 
ADD CONSTRAINT mgmt_textindex_logs_uq UNIQUE (TARGET_GUID,TEXTINDEX_NAME,TEXTINDEX_SCHEMA_NAME);


Rem MGMT_TEXTINDEX_LOGS_INFO holds log information of Text Index.
Rem 				on a Host.
Rem   COLUMNS:
Rem    TEXTINDEX_GUID    Unique identification of the text index. 
Rem    LOG_DIRECTORY     Log file location. 
Rem    LOG_FILE          Log file name. 
rem    START_DATE        Log file Start Date. 

CREATE TABLE MGMT_TEXTINDEX_LOGS_INFO
(
	TEXTINDEX_GUID RAW (50) NOT NULL,
	LOG_DIRECTORY  VARCHAR2 (2000),
	LOG_FILE       VARCHAR2 (2000),
	START_DATE     TIMESTAMP,
        TEXTINDEX_ACTION_TYPE VARCHAR2(200)
);

ALTER TABLE MGMT_TEXTINDEX_LOGS_INFO ADD CONSTRAINT MGMT_TEXTINDEX_LOGS_INFO_FK1 FOREIGN KEY
(TEXTINDEX_GUID) REFERENCES MGMT_TEXTINDEX(TEXTINDEX_GUID) ON DELETE CASCADE ENABLE;

Rem MGMT_TEXT_INDEX_STATS holds information regarding the Statistics of the Text Indexes
Rem                             on a Host.
Rem   COLUMNS:
Rem   TARGET_GUID            identification of the target.
Rem   COLLECTION_TIMESTAMP   date on which the data is collected.
Rem   INDEX_NAME        Name of the Text Index.
rem   PARTN_NAME    Name of the partition in case of partitioned text index.
Rem     STAT_DATA       text Index Statistics data.
Rem     SIZE_DATA       text Index Size data.
Rem     OBJECTS_DATA    text Index Objects data.

CREATE TABLE MGMT_TEXT_INDEX_STATS
(
        TARGET_GUID RAW (16) NOT NULL,
        COLLECTION_TIMESTAMP DATE  NOT NULL,
        INDEX_NAME  VARCHAR2 (400) NOT NULL,
        PARTN_NAME  VARCHAR2 (400) ,
        STAT_DATA  VARCHAR2 (4000) ,
        SIZE_DATA  VARCHAR2 (4000) ,
        OBJECTS_DATA  VARCHAR2 (4000)
);
CREATE INDEX MGMT_TEXT_INDEX_STATS_IDX ON MGMT_TEXT_INDEX_STATS(target_guid);