Rem drv:
Rem
Rem $Header: assoc_tables.sql 07-jul-2006.22:03:43 pmodi Exp $
Rem
Rem assoc_tables.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem assoc_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem pmodi 07/07/06 - Backport pmodi_bug-5097850 from main
Rem pmodi 05/08/06 - Bug:5097850 - Increase data length in flat table
Rem pmodi 08/09/05 - Changing clause for global table
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem ramalhot 04/21/05 - Renamed 'mgmt_target_assoc_count' to 'mgmt_target_assoc_status'
Rem streddy 04/20/05 - Change the propcount column size
Rem pmodi 04/16/05 - created a new table called
Rem ramalhot 04/08/05 - Modified mgmt_target_assoc_defs table
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 ktlaw 01/11/05 - add repmgr header
Rem ramalhot 08/18/04 - column name changed
Rem ramalhot 07/09/04 - mgmt_flat_target_assoc modifed
Rem ramalhot 07/07/04 - ramalhot_assoc_object_creation
Rem ramalhot 07/06/04 - Created
Rem
rem MGMT_TARGET_ASSOC_DEFS table
rem
rem ASSOC_DEF_NAME : The name of the association def
rem DESCRIPTION : Description of the association.
rem DESCRIPTION_NLSID : NLSID of the description
rem SOURCE_TARGET_TYPE : The target type of the target for which
rem association is being defined. "ANY" can be used
rem to indicate to any target type.
rem ASSOC_TARGET_TYPE : The target type of the associated target. "ANY"
rem means to indicate to any target type.
rem SCOPE_TARGET_TYPE : For non-global associations, this defines the
rem target type in which the association is valid.
rem CARDINALITY : The cardinality of the association,
rem where 0 means 0..1 (optional)
rem 1 means "1" (required)
rem 2 means 0..* (optional, multicardinal)
rem 3 means "1..*" (required, multicardinal)
rem PROP_VIEW_PRIV : Whether to Propagate view priviledge along the association,
rem where 0 means no view priviledge will be propagated
rem 1 means view priviledge will be propagated to only one Level
rem 2 means view priviledge will be propagated to all Levels
rem ASSOCIATION_TYPE : The type of association
CREATE TABLE MGMT_TARGET_ASSOC_DEFS (
assoc_guid RAW(16),
assoc_def_name VARCHAR2(64),
name_nlsid VARCHAR2(64),
description VARCHAR2(1024),
description_nlsid VARCHAR2(64),
source_target_type VARCHAR2(64) NOT NULL,
assoc_target_type VARCHAR2(64) NOT NULL,
scope_target_type VARCHAR2(64) NOT NULL,
cardinality NUMBER(1) DEFAULT 2,
prop_view_priv NUMBER(1) DEFAULT 0,
association_type VARCHAR2(64),
CONSTRAINT PK_MGMT_TARGET_ASSOC_DEFS PRIMARY KEY(assoc_def_name,source_target_type,scope_target_type)
)MONITORING;
rem MGMT_TARGET_ASSOCS table
rem
rem ASSOCIATION_GUID :The guid for the association
rem SOURCE_TARGET_GUID :Source target guid.
rem ASSOC_TARGET_GUID :Associated target guid.
rem SCOPE_TARGET_GUID :Scope target guid.
rem IS_EDITABLE :The property that defines wherther the association can
rem be edited by end-user
rem "0" means association can NOT be edited by enduser
rem "1" means association can be edited by enduser
rem created_by :The property that defines the creator of the association
rem "0" means association was created by system
rem "1" means association was inferred relation
rem "2" means association was cretead by agent
rem "3" means association was created by the end-user
CREATE TABLE MGMT_TARGET_ASSOCS(
assoc_guid RAW(16) NOT NULL,
source_target_guid RAW(16) NOT NULL,
assoc_target_guid RAW(16) NOT NULL,
scope_target_guid RAW(16) DEFAULT '0000000000000000',
is_editable NUMBER(1) DEFAULT 0,
created_by NUMBER(1) DEFAULT 3,
CONSTRAINT PK_MGMT_TARGET_ASSOCS
PRIMARY KEY(source_target_guid, assoc_guid,assoc_target_guid,scope_target_guid )
)MONITORING;
rem MGMT_TARGET_PENDING_ASSOCS table
rem
rem ASSOCIATION_GUID : The guid for the association
rem SOURCE_TARGET_GUID : Source target guid.
rem ASSOC_TARGET_GUID : Associated target guid.
rem SCOPE_TARGET_GUID : Scope target guid.
rem IS_EDITABLE : The property that defines wherther the association can
rem by edited by end-user
rem "0" means association can NOT be edited by enduser
rem "1" means association can be edited by enduser
rem created_by : The property that defines the creator of the association
rem "0" means association was created by system
rem "1" means association was inferred relation
rem "2" means association was cretead by agent
rem "3" means association was created by the end-user
CREATE TABLE MGMT_TARGET_PENDING_ASSOCS(
assoc_guid RAW(16) NOT NULL,
source_target_guid RAW(16) NOT NULL,
assoc_target_guid RAW(16) NOT NULL,
scope_target_guid RAW(16) DEFAULT '0000000000000000',
is_editable NUMBER(1) DEFAULT 0,
created_by NUMBER(1) DEFAULT 3,
CONSTRAINT PK_MGMT_TARGET_PENDING_ASSOCS
PRIMARY KEY(source_target_guid, assoc_guid,assoc_target_guid,scope_target_guid )
)MONITORING;
rem MGMT_TARGET_ASSOC_PROP_DEFS
rem property_name - name of the property
rem is_required - is the property a required property?
CREATE TABLE MGMT_TARGET_ASSOC_PROP_DEFS (
assoc_guid RAW(16) NOT NULL,
property_name VARCHAR2(64),
property_nlsid VARCHAR2(64),
property_default VARCHAR2(64),
is_required NUMBER(1),
CONSTRAINT PK_MGMT_TARGET_ASSOC_PROP_DEFS PRIMARY KEY(assoc_guid, property_name)
)MONITORING;
rem MGMT_TARGET_ASSOC_PROP
rem property_name name of the property
rem property_value value of the property
CREATE TABLE MGMT_TARGET_ASSOC_PROP (
assoc_guid RAW(16) NOT NULL,
source_target_guid RAW(16) NOT NULL,
scope_target_guid RAW(16) NOT NULL,
assoc_target_guid RAW(16) NOt NULL,
property_name VARCHAR2(64),
property_value VARCHAR2(1024)
)MONITORING;
rem MGMT_TARGET_ASSOC_ERROR
rem SOURCE_TARGET_GUID : source target guid
rem SCOPE_TARGET_GUID : scope target guid
rem ASSOC_GUID : GUID of the association that has an error
rem ERROR_CODE : 0 - required association missing
rem 1 - multiple associations found when expecting single
CREATE TABLE MGMT_TARGET_ASSOC_ERROR (
source_target_guid RAW(16) NOT NULL,
scope_target_guid RAW(16) NOT NULL,
assoc_guid RAW(16) NOT NULL,
error_code NUMBER(1),
CONSTRAINT PK_MGMT_TARGET_ASSOC_ERROR PRIMARY KEY (source_target_guid, assoc_guid,scope_target_guid)
)MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_FLAT_TARGET_ASSOC table holds the flattened list of
rem targets and associated targets.
rem
rem COLUMNS
rem
rem SOURCE_TARGET_GUID - the unique id of the source target
rem
rem ASSOC_TARGET_GUID - the unique id of the target that is associated to
rem this source target.
rem
rem IS_MEMBERSHIP - Column that specifies whether the relation ship is
rem of type "member-of"
rem
rem
rem membership_count - Internal column to track direct/indirect membership count
rem ref_count - Internal column to track direct/indirect assoc count
rem prop_count - Internal column to track direct/indirect propagation path count
CREATE TABLE MGMT_FLAT_TARGET_ASSOC
(source_target_guid RAW(16) NOT NULL,
assoc_target_guid RAW(16) NOT NULL,
is_membership NUMBER(1) DEFAULT 0,
is_connected NUMBER(1) DEFAULT 0,
membership_count NUMBER(10) DEFAULT 0,
ref_count NUMBER(10) DEFAULT 0,
prop_count NUMBER(10) DEFAULT 0
)MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_TARGET_TEMP_LIST table holds the list of
rem targets traversed during assoc flattenening.
rem
CREATE global temporary TABLE MGMT_TARGET_TEMP_LIST
(
target_guid RAW(16)
)
ON COMMIT PRESERVE ROWS;
rem
rem PURPOSE
rem The MGMT_TARGET_ASSOC_STATUS table holds the assoc count and last_updated timestamp
rem for each target.
rem
rem COLUMNS
rem TARGET_GUID - the target id
rem
rem ASSOC_COUNT - The association starting from the target
rem
rem LAST_UPDATE_TIME - Time at which associations for the target were last updated.
rem
CREATE TABLE MGMT_TARGET_ASSOC_STATUS
(target_guid RAW(16) NOT NULL,
assoc_count NUMBER DEFAULT 0,
last_update_time DATE DEFAULT SYSDATE,
CONSTRAINT PK_MGMT_TARGET_ASSOC_STATUS PRIMARY KEY (target_guid)
)MONITORING;