Rem
Rem $Header: ecm_hostpatch_uln_tables.sql 12-jul-2007.11:11:13 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 - <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    somukher    07/10/07 - 
Rem    tasingh     07/09/07 - Add advisories and packages table
Rem    tasingh     07/06/07 - New table for ULN channels
Rem    tasingh     07/06/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. A 
Rem   package may not belong to any advisory at all, for example, the packages
Rem   in _base channel belong to ISOs so they do not belong to any advisory.
Rem   So the advisory_id field can be null. Further, an advisory can belong to
Rem   multiple channels, so store channel_guid as well.
Rem   
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),
  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;