Rem
Rem $Header: ecm_hostpatch_tables.sql 14-feb-2005.05:06:29 gsbhatia Exp $
Rem
Rem ecm_hostpatch_tables.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_hostpatch_tables.sql - Tables for Host Patching.
Rem
Rem    DESCRIPTION
Rem      Creates EM Repository tables for use by Host Patching.
Rem
Rem    NOTES
Rem      None.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gsbhatia    02/14/05 - Removing header 
Rem    mgoodric    02/09/05 - mgoodric_bug-4166973
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    ranmath     12/20/04 - (er-4046560) Add new columns to 
Rem                           MGMT_ECM_HOSTPATCH_GROUPS. 
Rem    ranmath     12/08/04 - bug-4053039: Do not use TARGET_GUID in column 
Rem                           names. 
Rem    ranmath     12/03/04 - (bug-4046371) Join with MGMT$TARGET instead of 
Rem                           storing Group/Host names. 
Rem    ranmath     11/03/04 - Change primary key constraint for 
Rem                           MGMT_ECM_HOSTPATCH_REPOS_PKGS to uniqueness. 
Rem    ranmath     09/14/04 - Add MONITORING clause. 
Rem    ranmath     09/10/04 - Created
Rem

rem
rem  PURPOSE
rem
rem    Store additional Host Patching specific information for Groups
rem    created for Host Patching.
rem
rem  COLUMNS
rem
rem    GROUP_GUID - the GUID for the Group (from MGMT_TARGETS).
rem    MATURITY_LEVEL - the maturity level of the Group.
rem    NEED_REBOOT_PKGS - comma-separated list of specific package names
rem      or package name regular expressions denoting packages that should
rem      only be updated on a reboot.
rem    MARK_ROGUE_PKGS - "1" if rogue packages should be considered for
rem      compliance, "0" otherwise. (er-4046560)
rem    IGNORED_UNKNOWN_PKGS - comma-separated list of specific package names
rem      or package name regular expressions denoting packages on a Host
rem      that should be ignored if not available from a package repository.
rem      (er-4046560)
rem    MARK_HIGHER_VER_PKG_ROGUE - "1" if a higher-versioned Host package
rem      should be marked rogue, "0" otherwise. (er-4046560)
rem    STRICT_VER_CHECK_PKGS - comma-separated list of specific package names
rem      or package name regular expressions denoting packages on a Host
rem      that must strictly be of the same version as available from a
rem      package repository. (er-4046560)
rem    UPDATER_JOB_GUID - the Id of the Job, if any, that periodically 
rem      updates the Hosts in the Group.
rem    COLLECTOR_JOB_GUID - the Id of the Job, if any, that periodically
rem      collects the compliance information of Hosts in the Group.
rem
rem  NOTES
rem
rem    Group records should be cleaned if Group is deleted from elsewhere.
rem
CREATE TABLE mgmt_ecm_hostpatch_groups
(
  group_guid RAW(16) NOT NULL,
  maturity_level VARCHAR2(32),
  need_reboot_pkgs VARCHAR2(256),
  mark_rogue_pkgs NUMBER(1) DEFAULT 1 NOT NULL,
  ignored_unknown_pkgs VARCHAR2(256),
  mark_higher_ver_pkg_rogue NUMBER(1) DEFAULT 0 NOT NULL,
  strict_ver_check_pkgs VARCHAR2(256),
  updater_job_guid RAW(16),
  collector_job_guid RAW(16),
  CONSTRAINT mgmt_ecm_hp_group_pk
    PRIMARY KEY(group_guid)
) MONITORING;

rem
rem  PURPOSE
rem    
rem    Stores Host Patching specific information for Hosts.
rem
rem  COLUMNS
rem
rem    HOST_GUID - the GUID for the Host (from MGMT_TARGETS).
rem    GROUP_GUID - the GUID of the (single) Group that the Host 
rem      belongs to (for the purpose of Host Patching).
rem    OUT_OF_DATE_PKGS - the number of packages on the Host that have
rem      been found to be out-of-date.
rem    ROGUE_PKGS - the number of packages on the Host that have
rem      been found to be rogue.
rem    NEED_REBOOT_PKGS - the number of out-of-date packages on the Host
rem      that can only be updated on a reboot.
rem
rem  NOTES
rem
rem    Need to clean records of a Host if it is deleted from either the
rem    Group or from the EM Repository from elsewhere.
rem 
CREATE TABLE mgmt_ecm_hostpatch_hosts
(
  host_guid RAW(16) NOT NULL,
  group_guid RAW(16) NOT NULL,
  out_of_date_pkgs NUMBER(6) DEFAULT -1 NOT NULL,
  rogue_pkgs NUMBER(6) DEFAULT -1 NOT NULL,
  need_reboot_pkgs NUMBER(6) DEFAULT -1 NOT NULL,
  CONSTRAINT mgmt_ecm_hp_host_pk
    PRIMARY KEY(host_guid),
  CONSTRAINT mgmt_ecm_hp_host_group_fk
    FOREIGN KEY(group_guid)
    REFERENCES mgmt_ecm_hostpatch_groups(group_guid)
) MONITORING;

rem
rem  PURPOSE
rem
rem    Stores information about packages repositories for Host Patching.
rem
rem  COLUMNS
rem
rem    REPOS_GUID - the unique identifier for the packages repository.
rem    REPOS_URL - the (unique at any time) URL for the packages repository.
rem    REFRESHER_JOB_GUID - the Id of the Job, if any, that periodically
rem      refreshes available packages information from the packages
rem      repository.
rem
rem  NOTES
rem
rem    Need to store canonical forms of the URLs to avoid creating
rem    duplicate entries for what is essentially the same packages
rem    repository.
rem 
CREATE TABLE mgmt_ecm_hostpatch_repos
(
  repos_guid RAW(16) NOT NULL,
  repos_url VARCHAR2(256) NOT NULL,
  refresher_job_guid RAW(16),
  CONSTRAINT mgmt_ecm_hp_repo_pk
    PRIMARY KEY(repos_guid),
  CONSTRAINT mgmt_ecm_hp_repo_url_u
    UNIQUE(repos_url)
) MONITORING;

rem
rem  PURPOSE
rem
rem    Stores the last known information about packages available from a
rem    packages repository.
rem
rem  COLUMNS
rem
rem    REPOS_GUID - the identifier for the packages repository.
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
rem  NOTES
rem
rem    If PKG_ARCH is "noarch", the package is supposed to be platform
rem    agnostic. "release" and "epoch" only make sense for RPM packages.
rem 
CREATE TABLE mgmt_ecm_hostpatch_repos_pkgs
(
  repos_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,
  CONSTRAINT mgmt_ecm_hp_repo_pkg_u
    UNIQUE(repos_guid,pkg_name,pkg_version,pkg_release,pkg_epoch,pkg_arch),
  CONSTRAINT mgmt_ecm_hp_pkg_repo_fk
    FOREIGN KEY(repos_guid)
    REFERENCES mgmt_ecm_hostpatch_repos(repos_guid)
) MONITORING;

rem
rem  PURPOSE
rem
rem    Stores the relationship between a Group and the associated packages
rem    repositories.
rem
rem  COLUMNS
rem
rem    GROUP_GUID - the Target GUID of the Group.
rem    REPOS_GUID - the identifier for the packages repository.
rem    GROUP_REPOS_ORDER - the priority order for the packages repository
rem      for the Group.
rem
rem  NOTES
rem 
rem    "0" represents the highest priority, "1" lower, "2" lower still, etc.
rem 
CREATE TABLE mgmt_ecm_hostpatch_group_repos
(
  group_guid RAW(16) NOT NULL,
  repos_guid RAW(16) NOT NULL,
  group_repos_order NUMBER(2) NOT NULL,
  CONSTRAINT mgmt_ecm_hp_gr_group_fk
    FOREIGN KEY(group_guid)
    REFERENCES mgmt_ecm_hostpatch_groups(group_guid),
  CONSTRAINT mgmt_ecm_hp_gr_repo_fk
    FOREIGN KEY(repos_guid)
    REFERENCES mgmt_ecm_hostpatch_repos(repos_guid)
) MONITORING;

rem
rem  PURPOSE
rem 
rem    Stores the compliance information for a Host with respect to
rem    installed packages.
rem
rem  COLUMNS
rem 
rem    HOST_GUID - the Target GUID of the Host.
rem    PKG_NAME - the name of the installed package on the Host.
rem    PKG_VERSPEC - the version specification of the package.
rem    IS_ROGUE - "1" if the package is rogue, "0" otherwise.
rem    IS_OUT_OF_DATE - "1" if the package is out-of-date, "0" otherwise.
rem    NEEDS_REBOOT - "1" if the package needs reboot, "0" otherwise.
rem
rem  NOTES
rem
rem    We only store information about non-compliant packages.
rem    Only out-of-date packages can need a reboot to be updated.
rem 
CREATE TABLE mgmt_ecm_hostpatch_host_compl
(
  host_guid RAW(16) NOT NULL,
  pkg_name VARCHAR2(256) NOT NULL,
  pkg_verspec VARCHAR2(64) NOT NULL,
  is_rogue NUMBER(1) DEFAULT 0 NOT NULL,
  is_out_of_date NUMBER(1) DEFAULT 0 NOT NULL,
  needs_reboot NUMBER(1) DEFAULT 0 NOT NULL,
  CONSTRAINT mgmt_ecm_hp_host_pkg_pk
    PRIMARY KEY(host_guid,pkg_name,pkg_verspec),
  CONSTRAINT mgmt_ecm_hp_compl_host_fk
    FOREIGN KEY(host_guid)
    REFERENCES mgmt_ecm_hostpatch_hosts(host_guid)
) MONITORING;

rem
rem  PURPOSE
rem 
rem    Stores the compliance summary history for Host Patching Groups.
rem
rem  COLUMNS
rem 
rem    GROUP_GUID - the Target GUID of the Host Patching Group.
rem    TOTAL_HOSTS - the number of Host Patching Hosts in the Group.
rem    COMPL_HOSTS - the number of compliant Hosts in the Group.
rem    CHECKED_ON - the date on which this record was collected.
rem
rem  NOTES
rem
rem    We are only interested in the date and not the precise time
rem    at which the compliance information for the Group was determined.
rem    Consequently, always use TRUNC( ) while storing dates in this
rem    table to be able to do meaningful comparisons.
rem 
CREATE TABLE mgmt_ecm_hostpatch_compl_hist
(
  group_guid RAW(16) NOT NULL,
  total_hosts NUMBER(6) DEFAULT 0 NOT NULL,
  compl_hosts NUMBER(6) DEFAULT 0 NOT NULL,
  checked_on DATE NOT NULL,
  CONSTRAINT mgmt_ecm_hp_group_compl_pk
    PRIMARY KEY(group_guid,checked_on),
  CONSTRAINT mgmt_ecm_hp_compl_group_fk
    FOREIGN KEY(group_guid)
    REFERENCES mgmt_ecm_hostpatch_groups(group_guid)
) MONITORING;