Rem drv: <create type="views" pos="storage/storage_views.sql+"/>
Rem
Rem $Header: provision_views.sql 04-dec-2005.22:48:27 rattipal Exp $
Rem
Rem provision_views.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      provision_views.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    rattipal    12/04/05 - Backport rattipal_damtr from main 
Rem    kashukla    12/26/05 - minor modification in mgmt_provision_si_status 
Rem                           view 
Rem    rattipal    11/17/05 - Fix 4627280 
Rem    rmadampa    07/13/05 - select last_suc_asn_guid 
Rem    pshroff     07/01/05 - changing search2 query 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    pshroff     06/17/05 - adding error state also in search2 view 
Rem    rkaggarw    05/26/05 - added mgmt_provision_suitemember_cnt
Rem			      and mgmt_provision_si_status views	
Rem    rkaggarw    05/19/05 - added mgmt_provision_clus_node_cnt view
Rem			      enhanced mgmt_provision_cluster_status 			
Rem    rmadampa    05/13/05 - add asn tgt search view 
Rem    rmadampa    05/09/05 - add cluster serach view 
Rem    rmadampa    04/27/05 - data model enhancements 1 
Rem    pshroff     02/11/05 - modifying STATUS_OP view definition to include 
Rem                           targetType 
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    pshroff     11/02/04 - 
Rem    pshroff     11/01/04 - Adding Search View Definition 
Rem    pshroff     10/26/04 - Modified DB Schema for Deafult-Image 
Rem                           modification 
Rem    pshroff     10/18/04 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

show errors;


CREATE OR REPLACE VIEW mgmt_provision_hardware_status
AS 
SELECT 
    hw.hw_guid, hw.name, hw.description, hw.hostname, hw.new_hostname, 
    hw.mac_address1, hw.mac_address2, hw.mac_address3, hw.mac_address4,
    hw.interface_name1, hw.interface_name2, hw.interface_name3, hw.interface_name4,
    hw.serial_number, hw.rf_id, hw.purpose, 
    tgt.current_asn_guid, tgt.last_suc_asn_guid,tgt.component_urn, 
    tgt.network_urn, tgt.status       
FROM
    mgmt_prov_hardware hw, mgmt_prov_tgt_status tgt
WHERE
    hw.hw_guid = tgt.prov_tgt_guid
AND     
    tgt.prov_target_type = 'hw'
WITH READ ONLY;




CREATE OR REPLACE VIEW mgmt_provision_clus_node_cnt
AS
SELECT
    cluster_guid, count(*) node_count
FROM 
    mgmt_prov_cluster_nodes
GROUP BY 
    cluster_guid
WITH READ ONLY;	




CREATE OR REPLACE VIEW mgmt_provision_cluster_status
AS 
SELECT 
    clus.cluster_guid, clus.name, clus.description, clus.purpose, 
    tgt.current_asn_guid, tgt.component_urn, tgt.network_urn, tgt.status, 
    sim.suite_inst_guid, si.name suite_inst_name, cnc.node_count
FROM
    mgmt_prov_cluster clus 
INNER JOIN 
   mgmt_prov_tgt_status tgt 
ON
   clus.cluster_guid = tgt.prov_tgt_guid 
AND 
   tgt.prov_target_type = 'cluster'
LEFT OUTER JOIN
   mgmt_prov_suite_inst_members sim 
ON  
   sim.member_guid = clus.cluster_guid 
LEFT OUTER JOIN
   mgmt_prov_suite_instance si 
ON  
   sim.suite_inst_guid = si.suite_inst_guid 
LEFT OUTER JOIN 
   mgmt_provision_clus_node_cnt cnc
ON
   cnc.cluster_guid = clus.cluster_guid
WITH READ ONLY;





CREATE OR REPLACE VIEW mgmt_provision_suitemember_cnt
AS
SELECT
    suite_inst_guid, count(*) member_count
FROM 
    mgmt_prov_suite_inst_members
GROUP BY 
    suite_inst_guid
WITH READ ONLY;	




CREATE OR REPLACE view mgmt_prov_suite_hw_cnt
AS
SELECT
    suite_inst_guid, sum(member_count) as member_count
FROM 
(
    SELECT 
        st.suite_inst_guid, SUM(clus.node_count) as member_count
    FROM 
        mgmt_provision_clus_node_cnt clus, mgmt_prov_suite_inst_members st
    WHERE
        st.member_guid =  clus.cluster_guid and st.member_type = 'cluster'
    GROUP BY
        st.suite_inst_guid
    UNION ALL   
    SELECT 
        suite_inst_guid, count(*) as member_count
    FROM
        mgmt_prov_suite_inst_members
    WHERE 
        member_type = 'hw'
    GROUP BY 
        suite_inst_guid
)
GROUP BY suite_inst_guid
WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt_provision_si_status
AS 
SELECT 
    si.suite_inst_guid, si.name, si.description, si.purpose, 
    tgt.current_asn_guid, si.suite_urn, tgt.network_urn, tgt.status, 
    smc.member_count
FROM
    mgmt_prov_suite_instance si 
INNER JOIN 
   mgmt_prov_tgt_status tgt 
ON
   si.suite_inst_guid = tgt.prov_tgt_guid 
AND 
   tgt.prov_target_type = 'suiteInstance'
LEFT OUTER JOIN 
   mgmt_prov_suite_hw_cnt smc
ON
   smc.suite_inst_guid = si.suite_inst_guid
WITH READ ONLY;



CREATE OR REPLACE VIEW mgmt_provision_status_op
AS 
SELECT  
    tgt.prov_tgt_guid, hist.assignment_guid, 
    op.op_guid, tgt.prov_target_type, 
    op.last_modified_time, op.op_type, op.job_id, 
    op.status_msg, op.fraction_complete, op.creation_time
FROM
    mgmt_prov_tgt_status tgt, mgmt_prov_history hist, mgmt_prov_operation op
WHERE
    tgt.prov_tgt_guid = hist.prov_tgt_guid 
AND 
    (tgt.current_asn_guid IS NULL OR 
     tgt.current_asn_guid = hist.assignment_guid) 
AND
    hist.op_guid = op.op_guid
WITH READ ONLY;

CREATE OR REPLACE VIEW mgmt_provision_hw_search
AS 
SELECT 
    hw.hw_guid, hw.hostname, hw.new_hostname, hw.purpose, hw.serial_number, 
    hw.name, hw.description, tgt.current_asn_guid, 
    tgt.component_urn, tgt.network_urn, tgt.status,
    summ.system_config, summ.mem, summ.freq, summ.cpu_count         
FROM 
    mgmt_prov_hardware hw, mgmt_prov_tgt_status tgt, mgmt$os_hw_summary summ
WHERE 
    hw.hw_guid = tgt.prov_tgt_guid 
AND
    summ.host_name = hw.hostname 
AND 
    tgt.prov_target_type = 'hw'
WITH READ ONLY;


show errors;