Rem
Rem $Header: ecm_config_fw_metadata.sql 10-jun-2003.15:16:15 rmenon Exp $
Rem
Rem ecm_config_fw_metadata.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      ecm_config_fw_metadata.sql - ECm configuration framework metadata
Rem
Rem    DESCRIPTION
Rem      This file contains view definitions for objects specifically used by 
Rem   the config framework AND then invokes another sql script for running the 
Rem   DML that loads the data into the snapshot metadata tables.
Rem
Rem    NOTES
Rem      NOTE.  DO NOT MODIFY THE VIEWS IN THIS FILE UNLESS YOU ALSO MAKE 
Rem    appropriate changes to the various xml files (e.g., ecm_cfw_host_config.xml)
Rem    that are used to generate the scripts invoked from this file.  If you 
Rem    do modify any of the .xml files, make sure you regenerate the metadata 
Rem    script files. (e.g. ecm_cfw_host_config.sql.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rmenon      06/10/03 - added address_length_in_bits to ECM$OS_SUMMARY; added local_disk_space_in_gb to ECM$HARDWARE_MASTER
Rem    shuberma    04/07/03 - shuberma_new_host_config_fw_def
Rem    shuberma    04/04/03 - 
Rem    shuberma    04/04/03 - Created
Rem

Rem   =====================================================================
Rem           Host Configuration Views
Rem   The following views are used by the Host Configuration comparison code.
Rem   The comparison code is driven by  the metadata, defined in ecm_cfw_host_config.sql 
Rem   and generated from ecm_cfw_host_config.xml.
Rem   The views are a thin layer on top of various host_config tables.

Rem   View of MGMT_HC_OS_SUMMARY
create or replace view ECM$OS_SUMMARY as
select 
  snapshot_guid as ecm_snapshot_id,
  name,
  vendor_name,
  base_version,
  update_level,
  distributor_version,
  address_length_in_bits,
  max_swap_space_in_mb
from MGMT_HC_OS_SUMMARY;

Rem   View of MGMT_HC_OS_PROPERTIES
create or replace view ECM$OS_PROPERTIES as
select 
  snapshot_guid as ecm_snapshot_id,
  type,
  name,
  value
from MGMT_HC_OS_PROPERTIES;

Rem   View of MGMT_HC_FS_MOUNT_DETAILS
create or replace view ECM$FS_MOUNT_DETAILS as
select 
  snapshot_guid as ecm_snapshot_id,
  resource_name,
  type,
  mount_location,
  mount_options
from MGMT_HC_FS_MOUNT_DETAILS;

Rem   View of MGMT_HC_OS_COMPONENTS other than patches
create or replace view ECM$OS_COMPONENTS as
select
    snapshot_guid as ecm_snapshot_id,
    name,
    type,
    version,
    description
from MGMT_HC_OS_COMPONENTS
where type <> 'Patch';

Rem   View of MGMT_HC_OS_COMPONENTS, for patches
create or replace view ECM$OS_PATCHES as
select
    snapshot_guid as ecm_snapshot_id,
    name
from MGMT_HC_OS_COMPONENTS
where type = 'Patch';

Rem   View of MGMT_HC_VENDOR_SW_SUMMARY
create or replace view ECM$OS_REGISTERED_SW as
select
    snapshot_guid as ecm_snapshot_id,
    name,
    vendor_name,
    version,
    count(*) as count
from MGMT_HC_VENDOR_SW_SUMMARY
group by snapshot_guid, name, vendor_name, version;

Rem   View of MGMT_INV_COMPONENT with some other values from joins with
Rem   Versioned patches and home (container).
create or replace view ECM$ORACLE_TOPLEVEL as
select
    h.snapshot_guid as ecm_snapshot_id,
    c.name,
    c.external_name,
    case when p.version IS NULL THEN c.version ELSE p.version END as version,
    count(*) as count
from
  MGMT_INV_CONTAINER h,
  MGMT_INV_COMPONENT c,
  MGMT_INV_VERSIONED_PATCH p
where h.container_guid = c.container_guid
  and c.component_guid = p.component_guid(+)
  and c.IS_TOP_LEVEL = 'Y'
group by h.snapshot_guid, c.name, c.external_name, case when p.version IS NULL THEN c.version ELSE p.version END;

Rem   View of MGMT_HC_HARDWARE_MASTER
-- Views for the Hardware drillins:
create or replace view ECM$HARDWARE_MASTER as
select
  snapshot_guid as ecm_snapshot_id,
  vendor_name,
  system_config,
  machine_architecture,
  local_disk_space_in_gb,
  clock_freq_in_mhz,
  memory_size_in_mb,
  cpu_count, 
  cpu_board_count,
  iocard_count,
  fan_count,
  power_supply_count
from MGMT_HC_HARDWARE_MASTER;


Rem   View of MGMT_HC_CPU_DETAILS
create or replace view ECM$CPU_DETAILS as
select 
  snapshot_guid as ecm_snapshot_id,
  vendor_name, 
  freq_in_mhz,
  ecache_in_mb,
  impl,
  revision,
  mask,
  count(*) as count
from
  MGMT_HC_CPU_DETAILS
group by snapshot_guid, vendor_name, freq_in_mhz,ecache_in_mb,impl, revision,mask;

Rem   View of MGMT_HC_IOCARD_DETAILS
create or replace view ECM$IOCARD_DETAILS as
select 
  snapshot_guid as ecm_snapshot_id,
  vendor_name, 
  name,
  freq_in_mhz,
  bus,
  revision,
  count(*) as count
from
  MGMT_HC_IOCARD_DETAILS
group by snapshot_guid, vendor_name, name,freq_in_mhz,bus, revision;

Rem   View of MGMT_HC_NIC_DETAILS
create or replace view ECM$NIC_DETAILS as
select 
  snapshot_guid as ecm_snapshot_id,
  name,
  flags,
  max_transfer_unit,
  mask,
  count(*) as count
from
  MGMT_HC_NIC_DETAILS
group by snapshot_guid, name, flags,max_transfer_unit, mask;

show errors;