Rem
Rem $Header: ecm_hostpatch_uln_tables.sql 10-jul-2007.18:06:00 somukher Exp $
Rem
Rem ecm_hostpatch_uln_tables.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_hostpatch_uln_tables.sql - Tables for ULN Channels
Rem
Rem    DESCRIPTION
Rem      Creates EM Repositories tables for ULN Channels to be used by linux
Rem      host patching
Rem
Rem    NOTES
Rem      None
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    somukher    07/10/07 - 
Rem    tasingh     07/10/07 - Add tables for advisories
Rem    tasingh     07/05/07 - Tables for ULN channels
Rem    tasingh     07/05/07 - Created
Rem

Rem PURPOSE
Rem 
Rem   Stores the GUID of the hosts on which staging server is setup.
Rem
Rem COLUMNS
Rem
Rem   STAGE_SERVER_GUID - the GUID for the stage server host (from MGMT_TARGETS)
Rem
CREATE TABLE mgmt_ecm_uln_stage_servers
(
  stage_server_guid RAW(16) NOT NULL,
  CONSTRAINT mgmt_ecm_uln_stage_server_pk
    PRIMARY KEY(stage_server_guid)
) MONITORING;

Rem
Rem PURPOSE
Rem
Rem   Stores the uln channel names to which all the staging servers are 
Rem   currently subscribed to.
Rem
Rem COLUMNS
Rem
Rem   CHANNEL_GUID - the identifier for the uln channel
Rem   CHANNEL_NAME - the name of the uln channel
Rem
CREATE TABLE mgmt_ecm_uln_channels
(
  channel_guid RAW(16) NOT NULL,
  channel_name VARCHAR2(256) NOT NULL,
  CONSTRAINT mgmt_ecm_uln_channel_pk
    PRIMARY KEY(channel_guid),
  CONSTRAINT mgmt_ecm_uln_channel_u
    UNIQUE(channel_name)
) MONITORING;

Rem
Rem PURPOSE
Rem
Rem   Stores the relationship between staging server and uln channels. A 
Rem   staging server can subscribe to multiple channels and two or more 
Rem   staging servers can subscribe to same channels.
Rem
Rem COLUMNS
Rem 
Rem   STAGE_SERVER_GUID - the target GUID of the stage server (host)
Rem   CHANNEL_GUID - the identifier of the uln channel.
Rem
CREATE TABLE mgmt_ecm_uln_ss_channels
(
  stage_server_guid RAW(16) NOT NULL,
  channel_guid RAW(16) NOT NULL,
  CONSTRAINT mgmt_ecm_uln_ss_channel_u
    UNIQUE(stage_server_guid, channel_guid),
  CONSTRAINT mgmt_ecm_uln_ss_server_fk
    FOREIGN KEY(stage_server_guid)
    REFERENCES mgmt_ecm_uln_stage_servers(stage_server_guid),
  CONSTRAINT mgmt_ecm_uln_chs_ch_fk
    FOREIGN KEY(channel_guid)
    REFERENCES mgmt_ecm_uln_channels(channel_guid)
) MONITORING;

Rem
Rem PURPOSE
Rem 
Rem   Stores the advisories information for a channel
Rem
Rem COLUMNS
Rem 
Rem   CHANNEL_GUID - the identifier of the channel
Rem   ADVISORY_ID - advisory name or id (e.g ELSA_2006-0720)
Rem   SUMMARY - a one line summary of the advisory
Rem   DESCRIPTION - a detailed description of the advisory
Rem
Rem NOTES
Rem 
Rem   An advisory can provide multiple packages. Further a package can belong
Rem   to multiple advisories. To get pacakges provided by an advisory, query 
Rem   the mgmt_ecm_uln_channel_pkgs table
Rem
CREATE TABLE mgmt_ecm_uln_ch_adv
(
  channel_guid RAW(16) NOT NULL,
  advisory_id VARCHAR2(256) NOT NULL,
  summary VARCHAR2(256), 
  description VARCHAR2(1024),
  CONSTRAINT mgmt_ecm_uln_channel_adv_u
    UNIQUE(channel_guid, advisory_id),
  CONSTRAINT mgmt_ecm_uln_ch_adv_ch_fk
    FOREIGN KEY(channel_guid)
    REFERENCES mgmt_ecm_uln_channels(channel_guid)
) MONITORING;

Rem
Rem PURPOSE
Rem 
Rem   Stores the package information that belong to a channel
Rem
Rem COLUMNS
Rem
Rem   PKG_NAME - the name of the package
Rem   PKG_VERSION - the base version of the package
Rem   PKG_RELEASE - the release version of the package, if any
Rem   PKG_EPOCH - the epoch for the package, if any.
Rem   PKG_ARCH - the architecture for which the package is applicable
Rem   ADVISORY_ID - the advisory ID to which this package belongs
Rem
Rem NOTES
Rem
Rem   A package can belong to multiple advisories, so for the same package
Rem   there can be multiple entries with different Advisory_ID values
CREATE TABLE mgmt_ecm_uln_channel_pkgs
(
  channel_guid RAW(16) NOT NULL,
  pkg_name VARCHAR2(256) NOT NULL,
  pkg_version VARCHAR2(64) NOT NULL,
  pkg_release VARCHAR2(32),
  pkg_epoch VARCHAR2(32),
  pkg_arch VARCHAR2(32) NOT NULL,
  advisory_id VARCHAR2( 256) NOT NULL,
  CONSTRAINT mgmt_ecm_uln_ch_pkg_u
    UNIQUE(pkg_name,pkg_release,pkg_version,pkg_epoch,pkg_arch,advisory_id),
  CONSTRAINT mgmt_ecm_uln_ch_pkg_channel_fk
    FOREIGN KEY(channel_guid)
    REFERENCES mgmt_ecm_uln_channels(channel_guid)
) MONITORING;