/*
 * @(#)Create_NSM_GUI_Views.ddl	1.46 03/06/06
 *
 * Warning: This file was generated programmatically by mof2ddl.
 *          Do not make changes to this file or they will be lost!
 *
 * Copyright 2001-2003 Sun Microsystems, Inc. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of Sun
 * Microsystems, Inc. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Sun.
 *
 * SUN MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY OF
 * THE SOFTWARE, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
 * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
 * PARTICULAR PURPOSE, OR NON-INFRINGEMENT. SUN SHALL NOT BE LIABLE FOR
 * ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
 * DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
 */

-- Creating Virtual Tables for GUI views

CREATE TABLE HostCluster
             (key       VARCHAR,
              removable BOOLEAN,
              name      VARCHAR)
;

CREATE TABLE StorageHost
             (key            VARCHAR,
              removable      BOOLEAN,
              hostname       VARCHAR,
              os             VARCHAR,
              platform       VARCHAR,
              hostclusterkey VARCHAR)
;

CREATE TABLE HBA
             (key            VARCHAR,
              removable      BOOLEAN,
              model          VARCHAR,
              path           VARCHAR,
              firmwarerev    VARCHAR,
              status         VARCHAR,
              vendor         VARCHAR,
              controllernum  VARCHAR,
              storagehostkey VARCHAR)
;

CREATE INDEX hba_idx ON HBA USING BTREE (key);

CREATE TABLE AttachedPort
             (key             VARCHAR,
              lastUpdate      TIMESTAMP,
              removable       BOOLEAN,
              portkey         VARCHAR,
              attachedportkey VARCHAR)
;

-- Temp table
-- CIM_ComputerSystem * CIM_SystemDevice * CIM_FCPort
CREATE TABLE ComputerSystemFCPort
             (fcpportkey          VARCHAR,
              dedicated           INTEGER[],
              cskey               VARCHAR,
              fcpStatus           VARCHAR,
              fcpPermanentAddress VARCHAR,
              fcpnetworkaddresses VARCHAR[],
              fcpPortType         INTEGER,
              fcpPortNumber       INTEGER,
              cslastupdate        TIMESTAMP,
              fcplastupdate       TIMESTAMP,
              sdlastupdate        TIMESTAMP,
              cspassnumber        INTEGER,
              sdpassnumber        INTEGER,
              fcppassnumber       INTEGER,
              fcpotherporttype    VARCHAR,
              csname              VARCHAR)
;

-- Temp table
-- Used in Porsche_Discovery Function to temporary store delete string
CREATE TABLE PorscheDeleteTable
             (TableName    VARCHAR,
              tablekey     VARCHAR)
;

-- Temp table
-- CIM_FCPort * CIM_DeviceIdentity * CIM_ScsiController
CREATE TABLE ScsiControllerFCPort
             (fcpportkey           VARCHAR,
              scsicontrollerkey    VARCHAR,
              identifyingdescriptions VARCHAR[],
              otheridentifyinginfo VARCHAR[],
              scsilastupdate       TIMESTAMP,
              fcplastupdate        TIMESTAMP,
              dilastupdate         TIMESTAMP,
              scpassnumber         INTEGER,
              dipassnumber         INTEGER,
              fcppassnumber        INTEGER,
              scsistatus           VARCHAR,
              deviceid             VARCHAR)
;

-- Temp table
-- CIM_ProtocolEndPoint * CIM_DeviceSAPImplementation 
-- * CIM_FCPort
CREATE TABLE ProtocolEndPointFCPort
             (fcpportkey           VARCHAR,
              pepkey               VARCHAR,
              fcplastupdate        TIMESTAMP,
              dsilastupdate        TIMESTAMP,
              peplastupdate        TIMESTAMP,
              fcppassnumber        INTEGER,
              dsipassnumber        INTEGER,
              peppassnumber        INTEGER)
;

-- Temp table
-- For SAN attached one side
CREATE TABLE AttachedportTemp1
             (key               VARCHAR,
              lastUpdate        TIMESTAMP,
              removable         BOOLEAN,
              portkey           VARCHAR,
              attachedportkey   VARCHAR)
;

-- Temp table
-- For SAN attached the other side
CREATE TABLE AttachedportTemp2
             (key               VARCHAR,
              lastUpdate        TIMESTAMP,
              removable         BOOLEAN,
              portkey           VARCHAR,
              attachedportkey   VARCHAR)
;

-- Temp table
-- For DAS attached, one side
CREATE TABLE AttachedportTemp3
             (key               VARCHAR,
              lastUpdate        TIMESTAMP,
              removable         BOOLEAN,
              portkey           VARCHAR,
              attachedportkey   VARCHAR)
;

-- Temp table
-- For DAS attached, the other side
CREATE TABLE AttachedportTemp4
             (key               VARCHAR,
              lastUpdate        TIMESTAMP,
              removable         BOOLEAN,
              portkey           VARCHAR,
              attachedportkey   VARCHAR)
;

-- Temp table
-- For StorageSubSystem temp that contains the JOIN :
-- CIM_ControlledBy * CIM_StorageVolume * CIM_BasedOn *
-- CIM_StorageExtent
CREATE TABLE StorageSubSystemTemp1
             (cbantecedent      VARCHAR,
              passnumber        INTEGER,
              seobjectpath      VARCHAR)
;

-- Temp table
-- For StorageSubSystem temp that contains the JOIN :
-- ComputerSystemFCPort * ScsiControllerFCPort *
-- StorageSubSystemTemp1
CREATE TABLE StorageSubSystemTemp2
             (ucsobjectpath        VARCHAR,
              passnumber           INTEGER,
              seobjectpath         VARCHAR)
;


CREATE INDEX AttachedPort_idx ON AttachedPort USING BTREE (portkey);

CREATE TABLE Port
             (key               VARCHAR,
              removable         BOOLEAN,
              gbictype          VARCHAR,
              state             VARCHAR,
              wwn               VARCHAR,
              zonekey           VARCHAR[])
;

CREATE TABLE SubSystemCluster
             (key               VARCHAR,
              removable         BOOLEAN,
              name              VARCHAR)
;

CREATE TABLE StorageSubSystem
             (key                 VARCHAR,
              removable           BOOLEAN,
              status              VARCHAR,
              name                VARCHAR,
              devicestate         VARCHAR,
              firmwarerev         VARCHAR,
              vendor              VARCHAR,
              model               VARCHAR,
              subsystemclusterkey VARCHAR)
;

CREATE TABLE LogicalDevice
             (key                 VARCHAR,
              removable           BOOLEAN,
              deviceid            VARCHAR,
              storagesubsystemkey VARCHAR)
;

CREATE TABLE Volume
             (key                 VARCHAR,
              removable           BOOLEAN,
              deviceid            VARCHAR,
              capacity            NUMERIC,
              storagesubsystemkey VARCHAR)
;

CREATE TABLE CompositeOSNode
             (key                 VARCHAR,
              removable           BOOLEAN,
              metapath            VARCHAR,
              storagehostkey      VARCHAR)
;

CREATE TABLE Fabric
             (key                 VARCHAR,
              removable           BOOLEAN,
              name                VARCHAR)
;

CREATE TABLE NxPort
             (key                 VARCHAR,
              removable           BOOLEAN,
              gbictype            VARCHAR,
              state               VARCHAR,
              wwn                 VARCHAR,
              zonekey             VARCHAR[],
              addressidentifier   VARCHAR,
              porttype            VARCHAR,
              hbakey              VARCHAR,
              storagesubsystemkey VARCHAR,
              nodewwn             VARCHAR,
              logicaldevicekey    VARCHAR)
;
CREATE INDEX nxport_idx ON NXPort USING BTREE (key);

CREATE TABLE DeviceOSNode
             (key                 VARCHAR,
              removable           BOOLEAN,
              lunid               VARCHAR,
              path                VARCHAR,
              storagehostkey      VARCHAR,
              logicaldevicekey    VARCHAR,
              compositeosnodekey  VARCHAR,
              nxportkey           VARCHAR)
;

CREATE TABLE AsymmetricOSNode
             (key                 VARCHAR,
              removable           BOOLEAN,
              lunid               VARCHAR,
              path                VARCHAR,
              role                INTEGER,
              storagehostkey      VARCHAR,
              logicaldevicekey    VARCHAR,
              compositeosnodekey  VARCHAR,
              nxportkey           VARCHAR)
;

CREATE TABLE Switch
             (key                 VARCHAR,
              removable           BOOLEAN,
              ipaddr              VARCHAR,
              status              VARCHAR,
              model               VARCHAR,
              firmwarerev         VARCHAR,
              vendor              VARCHAR,
              domainid            VARCHAR,
              wwn                 VARCHAR,
              name                VARCHAR,
              fabrickey           VARCHAR)
;

CREATE TABLE SwitchPort
             (key                 VARCHAR,
              removable           BOOLEAN,
              gbictype            VARCHAR,
              state               VARCHAR,
              wwn                 VARCHAR,
              zonekey             VARCHAR[],
              porttype            VARCHAR,
              number              INTEGER,
              switchkey           VARCHAR)
;

CREATE INDEX switchport_idx ON SwitchPort USING BTREE (key);

CREATE TABLE Zone
             (key                 VARCHAR,
              removable           BOOLEAN,
              name                VARCHAR,
              enforcement         VARCHAR,
              fabrickey           VARCHAR)
;

-- Build all the temporary tables to compress Complex Joins
-- These JOINs are very frequently and needs to be stored in
-- the temporary tables for performance
CREATE FUNCTION  refresh_TempTables() RETURNS INTEGER AS '
BEGIN
--  Build pre-cooked table: 
--  CIM_ComputerSystem * CIM_SystemDevice * CIM_FCPort
    DELETE FROM ComputerSystemFCPort;
    INSERT INTO ComputerSystemFCPort
    SELECT 
           cimfcp1.objectpath                 AS fcpportkey, 
           T2.dedicated		              AS dedicated,
           T2.cimcsobjectpath                 AS cskey,
           cimfcp1.Status                     AS fcpStatus, 
           cimfcp1.PermanentAddress           AS fcpPermanentAddress, 
           cimfcp1.networkaddresses           AS fcpnetworkaddresses,
           cimfcp1.PortType                   AS fcpPortType,
           cimfcp1.PortNumber                 AS fcpPortNumber,
           T2.cimcslastupdate                 AS cslastupdate,
           cimfcp1.lastupdate                 AS fcplastupdate,
           T2.sdlastupdate                    AS sdlastupdate,
           T2.cspassnumber                    AS cspassnumber,
           T2.sdpassnumber                    AS sdpassnumber,
           cimfcp1.passnumber                 AS fcppassnumber,
           cimfcp1.otherporttype              AS fcpotherporttype,
           T2.csname                          AS csname
    FROM 
           CIM_FCPort                                 cimfcp1,
           (SELECT cimsd.partcomponent  AS cimsdpartcomponent,
                   cimsd.lastupdate     AS sdlastupdate,
                   cimsd.passnumber     AS sdpassnumber,
                   T1.dedicated         AS dedicated,
                   T1.cimcsobjectpath   AS cimcsobjectpath,
                   T1.cspassnumber      AS cspassnumber,
                   T1.cimcslastupdate   AS cimcslastupdate,
                   T1.csname            AS csname
            FROM   CIM_SystemDevice     cimsd,
           (SELECT cimcs.objectpath     AS cimcsobjectpath,
                   cimcs.dedicated      AS dedicated,
                   cimcs.lastupdate     AS cimcslastupdate,
                   cimcs.passnumber     AS cspassnumber,
                   cimcs.name           AS csname
            FROM   CIM_ComputerSystem   cimcs) T1
            WHERE  T1.cimcsobjectpath = cimsd.groupcomponent)  T2
    WHERE T2.cimsdpartcomponent = cimfcp1.objectpath
;

--  Build pre-cooked table: 
--  CIM_FCPort * CIM_DeviceSAPImplementation * CIM_ProtocolEndPoint
    DELETE FROM ProtocolEndPointFCPort;
    INSERT INTO ProtocolEndPointFCPort
    SELECT 
           cimfcp1.objectpath                         AS fcpportkey, 
	   T2.cimpepobjectpath		              AS pepkey,
           cimfcp1.lastupdate                         AS fcplastupdate,
           T2.dsilastupdate                           AS dsilastupdate,
           T2.peplastupdate                           AS peplastupdate,
           cimfcp1.passnumber                         AS fcppassnumber,
           T2.dsipassnumber                           AS dsipassnumber,
           T2.peppassnumber                           AS peppassnumber
    FROM 
           CIM_FCPort                                 cimfcp1,
           (SELECT cimdsi1.antecedent   AS cimdsi1antecedent,
	           cimdsi1.passnumber   AS dsipassnumber,
	           T1.peppassnumber     AS peppassnumber,
                   T1.cimpepobjectpath  AS cimpepobjectpath,
                   T1.peplastupdate     AS peplastupdate,
                   cimdsi1.lastupdate   AS dsilastupdate
            FROM   cim_devicesapimplementation cimdsi1,
           (SELECT cimpep.objectpath  AS cimpepobjectpath,
                   cimpep.passnumber  AS peppassnumber,
                   cimpep.lastupdate  AS peplastupdate
            FROM   cim_protocolendpoint cimpep) T1
            WHERE  T1.cimpepobjectpath = cimdsi1.dependent)  T2
    WHERE T2.cimdsi1antecedent = cimfcp1.objectpath
;

--  Build pre-cooked table: 
--  CIM_ScsiController * CIM_DeviceIdentity * CIM_FCPort
    DELETE FROM ScsiControllerFCPort;
    INSERT INTO ScsiControllerFCPort
    SELECT 
           cimfcp.objectpath                         AS fcpportkey, 
           cimsc.objectpath                          AS scsicontrollerkey,
           cimsc.identifyingdescriptions             AS identifyingdescriptions,
           cimsc.otheridentifyinginfo                AS otheridentifyinginfo,
           cimsc.lastupdate                          AS scsilastupdate,
           cimfcp.lastupdate                         AS fcplastupdate,
           cimdi.lastupdate                          AS dilastupdate,
           cimsc.passnumber                          AS scpassnumber,
           cimdi.passnumber                          AS dipassnumber,
           cimfcp.passnumber                         AS fcppassnumber,
           cimsc.status                              AS scsistatus,
           cimsc.deviceid                            AS deviceid
    FROM 
           CIM_FCPort                                 cimfcp,
           CIM_DeviceIdentity                         cimdi,
           CIM_ScsiController                         cimsc
    WHERE  ((cimsc.objectpath = cimdi.sameelement AND
            cimdi.systemelement = cimfcp.objectpath) OR
           (cimdi.sameelement = cimfcp.objectpath AND
            cimsc.objectpath = cimdi.systemelement))
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


-- Host Cluster is never used in NSM 1.0.
-- But the function is still to keep the logic about how to get Cluster
CREATE FUNCTION  refresh_HostCluster() RETURNS INTEGER AS '
BEGIN
    DELETE FROM HostCluster;
    INSERT INTO HostCluster
    SELECT DISTINCT ON(key, Removable)
           cimc.objectpath                        AS key,
           isStale(cimc.lastUpdate)               AS Removable,
           cimc.name                              AS name  -- PK of this view
    FROM   
           cim_cluster                            AS cimc,
           cim_unitarycomputersystem              AS cimucs,
           cim_participatingcs                    AS cimpcs
    WHERE  
           cimpcs.dependent  = cimc.objectpath   AND
           cimpcs.antecedent = cimucs.objectpath AND
           cimucs.Dedicated  ISNULL              AND
           cimucs.passnumber = cimc.passnumber   AND
           cimc.passnumber   = cimpcs.passnumber
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- Materize the StorageHost table
CREATE FUNCTION  refresh_StorageHost() RETURNS INTEGER AS '
BEGIN
    DELETE FROM StorageHost;
    INSERT INTO StorageHost
    SELECT DISTINCT ON(key, Removable)
           cimucs.objectpath                      AS key,
           isStale(cimucs.lastUpdate)             AS Removable,
           cimucs.name                            AS hostName,
           StorageHost_OS(cimucs.objectpath)||cimucs.caption  
                                                  AS OS,
           cimucs.description                     AS platform,
           NULL                                   AS HostClusterKey
    FROM   
           cim_unitarycomputersystem              AS cimucs
    WHERE 
           cimucs.Dedicated  ISNULL
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Materize the HBA table
   Getting rid of fcport passnumber check due to the bug in IBFab provider,
   4808216. Later this change should be undone after IBFab fixes the bug.
*/
CREATE FUNCTION  refresh_HBA() RETURNS INTEGER AS '
BEGIN
    DELETE FROM HBA;
    INSERT INTO HBA
    SELECT DISTINCT ON(key, Removable)
       scfp.scsicontrollerkey                       AS key,
       isStale(scfp.scsilastupdate)                 AS Removable,
       HBA_model(scfp.scsicontrollerkey)            AS model,  
       OtherIdentifyingInfo_lookup(
           scfp.OtherIdentifyingInfo,
           scfp.identifyingdescriptions,
           ''/device path'')                          AS path,
       HBA_firmwareRev(scfp.scsicontrollerkey)      AS firmwareRev,
       CASE   WHEN isStale(scfp.scsilastupdate)
              THEN ''Lost Comm''
              ELSE scfp.scsistatus
              END                                   AS Status,
       HBA_vendor(scfp.scsicontrollerkey)           AS vendor,
       CASE   WHEN scfp.identifyingdescriptions *= ''Controller Number''
              THEN csfp.csname||'':''||OtherIdentifyingInfo_lookup(
                   scfp.OtherIdentifyingInfo,
                   scfp.identifyingdescriptions,
                   ''Controller Number'')
              ELSE csfp.csname||'':''||OtherIdentifyingInfo_lookup(
                   scfp.OtherIdentifyingInfo,
                   scfp.identifyingdescriptions,
                   ''/device path'')
              END                                   AS controllerNum,
       HBAStorageHostKey(scfp.scsicontrollerkey)    AS StorageHostKey
    FROM   
       ScsiControllerFCPort                         AS scfp,
       ComputerSystemFCPort                         AS csfp
    WHERE 
       scfp.fcpportkey = csfp.fcpportkey            AND
       csfp.Dedicated ISNULL                        AND
       scfp.otheridentifyinginfo NOTNULL            AND
       csfp.cspassnumber = csfp.sdpassnumber        AND
       csfp.sdpassnumber = scfp.scpassnumber        AND
       scfp.scpassnumber  = scfp.dipassnumber 
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


-- Materialize the AttachedPort table
-- To populate attachedport table, we need about 6 Joins for
-- SAN and about 15 Joins for DAS.
-- This is too costly for query processor to perform.
-- Many temporary tables are used to overcome this problem.
-- These temp tables are used to store partial Joins with the minimum
-- required columns.
CREATE FUNCTION  refresh_AttachedPort() RETURNS INTEGER AS '
DECLARE
    rec             RECORD;
BEGIN
--  Get rid of potential invalid attachedports
--  Attachedport between switch and HBA/StoragePort, one way
    DELETE FROM AttachedportTemp1;
    INSERT INTO AttachedportTemp1
    SELECT DISTINCT ON (key)
           apt1.fcpportkey||T2.cimfcp2objectpath      AS key,
           T2.cimaclastUpdate                         AS lastUpdate,
           isStale(T2.cimaclastUpdate)                AS Removable,
           apt1.fcpportkey                            AS PortKey,
           T2.cimfcp2objectpath                       AS attachedportkey
    FROM 
           ProtocolEndPointFCPort                          apt1,
           (SELECT cimac.antecedent     AS cimacantecedent,
                   cimac.lastUpdate     AS cimaclastUpdate,
                   T1.cimfcp2objectpath AS cimfcp2objectpath
            FROM   cim_activeconnection cimac,
           (SELECT apt2.fcpportkey      AS cimfcp2objectpath,
                   apt2.pepkey          AS cimpep2objectpath
            FROM   ProtocolEndPointFCPort  apt2)                   T1
            WHERE  T1.cimpep2objectpath = cimac.dependent) T2
    WHERE  T2.cimacantecedent = apt1.pepkey
;
    DELETE FROM AttachedportTemp2;
    INSERT INTO AttachedportTemp2
    SELECT DISTINCT ON (key)
           apt1.fcpportkey||T2.cimfcp2objectpath      AS key,
           T2.cimaclastUpdate                         AS lastUpdate,
           isStale(T2.cimaclastUpdate)                AS Removable,
           apt1.fcpportkey                            AS PortKey,
           T2.cimfcp2objectpath                       AS attachedportkey
    FROM 
           ProtocolEndPointFCPort                          apt1,
           (SELECT cimac.dependent      AS cimacdependent,
                   cimac.lastUpdate     AS cimaclastUpdate,
                   T1.cimfcp2objectpath AS cimfcp2objectpath
            FROM   cim_activeconnection cimac,
           (SELECT apt2.fcpportkey      AS cimfcp2objectpath,
                   apt2.pepkey          AS cimpep2objectpath
            FROM   ProtocolEndPointFCPort  apt2) T1
            WHERE  T1.cimpep2objectpath = cimac.antecedent) T2
    WHERE  T2.cimacdependent = apt1.pepkey
;

    DELETE FROM AttachedportTemp3;
    INSERT INTO AttachedportTemp3
    SELECT DISTINCT ON (key) 
           csfcp.fcpportkey||T8.scfcp2fcpportkey 
                                              AS key,
           T8.iln2lastUpdate  
                AS lastUpdate,
           isStale(T8.iln2lastUpdate)         AS Removable,
           csfcp.fcpportkey                   AS PortKey,
           T8.scfcp2fcpportkey                AS attachedportkey
    FROM  
           ComputerSystemFCPort                       csfcp,
           (SELECT T7.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   T7.iln2lastupdate                  AS iln2lastupdate,
                   T7.pepfcp1fcpportkey               AS pepfcp1fcpportkey
            FROM   ScsiControllerFCPort               scfcp,
           (SELECT T6.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   T6.iln2lastupdate                  AS iln2lastupdate,
                   T6.lnname                          AS lnname,
                   pepfcp1.fcpportkey                 AS pepfcp1fcpportkey
            FROM   ProtocolEndPointFCPort             pepfcp1,
           (SELECT T5.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   T5.iln2lastupdate                  AS iln2lastupdate,
                   T5.lnname                          AS lnname,
                   iln.member                         AS ilnmember,
                   T5.pepfcp2pepkey                   AS pepfcp2pepkey
            FROM   CIM_InLogicalNetwork               iln,  
           (SELECT T4.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   T4.iln2lastupdate                  AS iln2lastupdate,
                   ln.name                            AS lnname,
                   ln.objectpath                      AS lnobjectpath,
                   T4.pepfcp2pepkey                   AS pepfcp2pepkey
            FROM   CIM_LogicalNetwork                 ln,  
           (SELECT T3.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   iln2.lastupdate                    AS iln2lastupdate,
                   iln2.collection                    AS iln2collection,
                   T3.pepfcp2pepkey                   AS pepfcp2pepkey
            FROM   CIM_InLogicalNetwork               iln2,  
           (SELECT  T2.scfcp2fcpportkey               AS scfcp2fcpportkey,
                    pepfcp2.pepkey                    AS pepfcp2pepkey
            FROM    ProtocolEndPointFCPort            pepfcp2,
           (SELECT  T1.scfcp2fcpportkey               AS scfcp2fcpportkey
            FROM    ComputerSystemFCPort              csfcp2,
           (SELECT  scfcp2.fcpportkey                 AS scfcp2fcpportkey
            FROM    ScsiControllerFCPort              scfcp2) T1
            WHERE   T1.scfcp2fcpportkey = csfcp2.fcpportkey AND
                    (csfcp2.dedicated *= 3 OR csfcp2.dedicated *= 15)) T2
            WHERE   T2.scfcp2fcpportkey = pepfcp2.fcpportkey) T3
            WHERE   T3.pepfcp2pepkey = iln2.member) T4
            WHERE   T4.iln2collection = ln.objectpath) T5
            WHERE   T5.lnobjectpath = iln.collection) T6
            WHERE   T6.ilnmember = pepfcp1.pepkey AND
                    NOT isActiveConnection(pepfcp1.pepkey, 
                    T6.pepfcp2pepkey)) T7
            WHERE   T7.pepfcp1fcpportkey = scfcp.fcpportkey AND
                    T7.lnname = scfcp.deviceid) T8
            WHERE   T8.pepfcp1fcpportkey = csfcp.fcpportkey AND
                    csfcp.dedicated ISNULL;

    DELETE FROM AttachedportTemp4;
    INSERT INTO AttachedportTemp4
    SELECT DISTINCT ON (key) 
           csfcp.fcpportkey||T8. scfcp2fcpportkey
                                              AS key,
           T8.iln2lastUpdate                  AS lastUpdate,
           isStale(T8.iln2lastUpdate)         AS Removable,
           csfcp.fcpportkey                   AS PortKey,
           T8.scfcp2fcpportkey                AS attachedportkey
    FROM  
           ComputerSystemFCPort                       csfcp,
           (SELECT T7.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   T7.iln2lastupdate                  AS iln2lastupdate,
                   T7.pepfcp1fcpportkey               AS pepfcp1fcpportkey
            FROM   ScsiControllerFCPort               scfcp,
           (SELECT T6.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   T6.iln2lastupdate                  AS iln2lastupdate,
                   T6.lnname                          AS lnname,
                   pepfcp1.fcpportkey                 AS pepfcp1fcpportkey
            FROM   ProtocolEndPointFCPort             pepfcp1,
           (SELECT T5.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   T5.iln2lastupdate                  AS iln2lastupdate,
                   T5.lnname                          AS lnname,
                   iln.member                         AS ilnmember,
                   T5.pepfcp2pepkey                   AS pepfcp2pepkey
            FROM   CIM_InLogicalNetwork               iln,  
           (SELECT T4.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   T4.iln2lastupdate                  AS iln2lastupdate,
                   ln.name                            AS lnname,
                   ln.objectpath                      AS lnobjectpath,
                   T4.pepfcp2pepkey                   AS pepfcp2pepkey
            FROM   CIM_LogicalNetwork                 ln,  
           (SELECT T3.scfcp2fcpportkey                AS scfcp2fcpportkey,
                   iln2.lastupdate                    AS iln2lastupdate,
                   iln2.collection                    AS iln2collection,
                   T3.scfcp2deviceid                  AS scfcp2deviceid,
                   T3.pepfcp2pepkey                   AS pepfcp2pepkey
            FROM   CIM_InLogicalNetwork               iln2,  
           (SELECT  T2.scfcp2fcpportkey               AS scfcp2fcpportkey,
                    pepfcp2.pepkey                    AS pepfcp2pepkey,
                    T2.scfcp2deviceid                 AS scfcp2deviceid
            FROM    ProtocolEndPointFCPort            pepfcp2,
           (SELECT  T1.scfcp2fcpportkey               AS scfcp2fcpportkey,
                    T1.scfcp2deviceid                 AS scfcp2deviceid
            FROM    ComputerSystemFCPort              csfcp2,
           (SELECT  scfcp2.fcpportkey                 AS scfcp2fcpportkey,
                    scfcp2.deviceid                   AS scfcp2deviceid
            FROM    ScsiControllerFCPort              scfcp2) T1
            WHERE   T1.scfcp2fcpportkey = csfcp2.fcpportkey AND
                    csfcp2.dedicated ISNULL) T2
            WHERE   T2.scfcp2fcpportkey = pepfcp2.fcpportkey) T3
            WHERE   T3.pepfcp2pepkey = iln2.member) T4
            WHERE   T4.iln2collection = ln.objectpath AND 
                    ln.name = T4.scfcp2deviceid) T5
            WHERE   T5.lnobjectpath = iln.collection) T6
            WHERE   T6.ilnmember = pepfcp1.pepkey AND
                    NOT isActiveConnection(pepfcp1.pepkey, 
                    T6.pepfcp2pepkey)) T7
            WHERE   T7.pepfcp1fcpportkey = scfcp.fcpportkey) T8
            WHERE   T8.pepfcp1fcpportkey = csfcp.fcpportkey AND
                    (csfcp.dedicated *= 3 OR csfcp.dedicated *= 15);

    DELETE FROM AttachedPort;
    INSERT INTO AttachedPort
    SELECT *
    FROM AttachedportTemp1
    UNION
    SELECT * 
    FROM AttachedportTemp2
    UNION
    SELECT * 
    FROM AttachedportTemp3
    UNION
    SELECT * 
    FROM AttachedportTemp4;
    
    DELETE FROM AttachedportTemp4;
    INSERT INTO AttachedportTemp4
    SELECT * FROM AttachedPort WHERE removable = ''t'';
    FOR rec IN
        SELECT portkey, attachedportkey
        FROM AttachedportTemp4
    LOOP
        PERFORM verifyAttachedport(rec.portkey, rec.attachedportkey);
    END LOOP;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- Materialize the Port table
CREATE FUNCTION  refresh_Port() RETURNS INTEGER AS '
DECLARE 
    rec    RECORD;
BEGIN

    DELETE FROM Port;
    INSERT INTO Port
    SELECT DISTINCT ON(key, Removable)
           cimfcp3.objectpath                          AS key,
           isStale(cimfcp3.lastUpdate)                 AS Removable,
           gbictype(cimfcp3.objectpath)                AS GBICtype, 
	   cimfcp3.Status                              AS state, 
	   cimfcp3.PermanentAddress                    AS WWN,  -- PK
           NULL                                        AS ZoneKey
    FROM 
           CIM_FCPort                                  AS cimfcp3
;
    FOR rec IN
        SELECT key
        FROM Port
    LOOP
        PERFORM zonekey(''Port'', rec.key);
    END LOOP;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- Materialize the SubSystemCluster table
CREATE FUNCTION  refresh_SubSystemCluster() RETURNS INTEGER AS '
BEGIN
    DELETE FROM SubSystemCluster;
    INSERT INTO SubSystemCluster
    SELECT DISTINCT ON(key, Removable)
           cimc.objectpath                    AS key,
           isStale(cimc.lastUpdate)           AS Removable,
           cimc.name                          AS name       -- PK of this view
    FROM   
           cim_cluster                        AS cimc,
           cim_unitarycomputersystem          AS cimucs,
           cim_participatingcs                AS cimpcs
    WHERE  
           cimpcs.dependent  = cimc.objectpath   AND
           cimpcs.antecedent = cimucs.objectpath AND
           cimpcs.passnumber = cimucs.passnumber AND
           cimucs.passnumber = cimc.passnumber   AND
           (cimucs.Dedicated  *=  3 OR
            cimucs.Dedicated  *=  15 )
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Materize the StorageSubSystem table

   Getting rid of fcport passnumber check due to the bug in IBFab provider,
   4808216. Later this change should be undone after IBFab fixes the bug.
*/
CREATE FUNCTION  refresh_StorageSubSystem() RETURNS INTEGER AS '
BEGIN
--  Build pre-cooked table: 
-- CIM_ControlledBy * CIM_StorageVolume * CIM_BasedOn *
-- CIM_StorageExtent

    DELETE FROM StorageSubSystemTemp1;
    INSERT INTO StorageSubSystemTemp1
    SELECT 
           cimcb.antecedent                   AS cbantecedent,
           cimcb.passnumber                   AS passnumber,
           T3.seobjectpath                    AS seobjectpath
    FROM 
           CIM_ControlledBy                   AS cimcb,
           (SELECT cimsv.objectpath     AS svobjectpath,
                   cimsv.passnumber     AS svpassnumber,
                   T2.seobjectpath      AS seobjectpath
            FROM   CIM_StorageVolume    AS cimsv,
           (SELECT cimbo.antecedent     AS boantecedent,
                   cimbo.passnumber     AS bopassnumber,
                   T1.seobjectpath      AS seobjectpath
            FROM   CIM_BasedOn          AS cimbo,
           (SELECT cimse.objectpath     AS seobjectpath,
                   cimse.passnumber     AS sepassnumber
            FROM   CIM_StorageExtent    AS cimse) T1
            WHERE  T1.seobjectpath = cimbo.dependent AND
                   T1.sepassnumber = cimbo.passnumber)  T2
            WHERE  T2.boantecedent = cimsv.objectpath AND
                   T2.bopassnumber = cimsv.passnumber)  T3
            WHERE  T3.svobjectpath = cimcb.dependent AND
                   T3.svpassnumber = cimcb.passnumber
;

--  Build pre-cooked table: 
-- ComputerSystemFCPort * ScsiControllerFCPort *
-- StorageSubSystemTemp1
    DELETE FROM StorageSubSystemTemp2;
    INSERT INTO StorageSubSystemTemp2
    SELECT 
           csfp.cskey                      AS ucsobjectpath,
           csfp.cspassnumber               AS passnumber,
           T2.seobjectpath                 AS seobjectpath
    FROM 
           ComputerSystemFCPort            AS csfp,
           (SELECT scfp.fcpportkey         AS scfpfcpportkey,
                   scfp.scpassnumber       AS scfpscpassnumber,
                   T1.seobjectpath         AS seobjectpath
            FROM   ScsiControllerFCPort    AS scfp,
           (SELECT sst1.cbantecedent       AS cbantecedent,
                   sst1.passnumber         AS sst1passnumber,
                   sst1.seobjectpath       AS seobjectpath
            FROM   StorageSubSystemTemp1   AS sst1) T1
            WHERE  T1.cbantecedent = scfp.scsicontrollerkey AND
                   T1.sst1passnumber = scfp.scpassnumber AND
                   scfp.scpassnumber = scfp.dipassnumber)  T2
            WHERE  T2.scfpfcpportkey = csfp.fcpportkey AND
                   T2.scfpscpassnumber = csfp.cspassnumber AND
                   csfp.cspassnumber = csfp.sdpassnumber AND
                   (csfp.Dedicated *= 3 OR csfp.Dedicated *= 15)
;

    DELETE FROM StorageSubSystem;
    INSERT INTO StorageSubSystem
    SELECT DISTINCT ON(key, Removable)
           cimucs1.objectpath                    AS key,
           isStale(cimucs1.lastUpdate)           AS Removable,
           CASE   WHEN isStale(cimucs1.lastUpdate)
                  THEN ''Lost Comm''
                  ELSE cimucs1.status
                  END                            AS Status,
           cimucs1.name                          AS name,  --PK
           look_deviceState(cimucs1.objectpath)  AS deviceState,
           look_firmWareRev(cimucs1.objectpath)  AS firmWareRev,        
           look_manufacturer(cimucs1.objectpath) AS vendor,        
           look_model(cimucs1.objectpath)        AS model,        
           ClusterKey(cimucs1.objectpath)        AS SubSystemClusterKey
    FROM   
           cim_unitarycomputersystem             AS cimucs1
    WHERE  
           cimucs1.Dedicated *= 3 OR cimucs1.Dedicated *= 15
;
    PERFORM setT4IPAddress();
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION  refresh_LogicalDevice() RETURNS INTEGER AS '
BEGIN
    DELETE FROM LogicalDevice;
    INSERT INTO LogicalDevice
    SELECT DISTINCT ON(key, StorageSubSystemKey, Removable)
           cimsv.objectpath                      AS key,
           isStale(cimsv.lastUpdate)             AS Removable,
	   cimsv.deviceId                        AS deviceId,  -- PK
           T3.csfpcskey                            AS StorageSubSystemKey
    FROM 
           CIM_StorageVolume                     AS cimsv,
	   (SELECT T2.csfpcskey                  AS csfpcskey,
                   cimcb.passnumber              AS cimcbpassnumber,
                   cimcb.dependent               AS cimcbdependent
            FROM   CIM_ControlledBy              AS cimcb,
           (SELECT T1.csfpcskey                  AS csfpcskey,
                   scfp.scpassnumber             AS scfpscpassnumber,
                   scfp.scsicontrollerkey        AS scfpscsicontrollerkey
            FROM   ScsiControllerFCPort          AS scfp,
           (SELECT csfp.cskey                    AS csfpcskey,
                   csfp.cspassnumber             AS csfpcspassnumber,
                   csfp.fcpportkey               AS csfpfcpportkey
            FROM   ComputerSystemFCPort          AS csfp
            WHERE  (csfp.Dedicated *= 3 OR csfp.Dedicated *= 15)  AND
                   csfp.cspassnumber = csfp.sdpassnumber          AND
                   csfp.sdpassnumber = csfp.fcppassnumber)        T1
            WHERE  scfp.fcpportkey = T1.csfpfcpportkey            AND
                   T1.csfpcspassnumber = scfp.scpassnumber        AND
                   scfp.scpassnumber  = scfp.dipassnumber         AND
                   scfp.dipassnumber  = scfp.fcppassnumber)       T2  
            WHERE  T2.scfpscsicontrollerkey = cimcb.antecedent    AND
                   T2.scfpscpassnumber = cimcb.passnumber         AND
                   cimcb.objectpath NOT LIKE ''%SCSIInterface%'') T3
    WHERE  cimsv.passnumber = T3.cimcbpassnumber                  AND
           T3.cimcbdependent = cimsv.objectpath
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION  refresh_Volume() RETURNS INTEGER AS '
BEGIN
    DELETE FROM Volume;
    INSERT INTO Volume 
    SELECT DISTINCT ON(key, StorageSubSystemKey, Removable)
           cimsv.objectpath                      AS key,
           isStale(cimsv.lastUpdate)             AS Removable,
	   cimsv.deviceId                        AS deviceId,  -- PK
           cimsv.BlockSize::text::float::numeric *
           cimsv.NumberOfBlocks::text::float::numeric
                                                 AS capacity,
           T3.csfpcskey                          AS StorageSubSystemKey
    FROM 
           CIM_StorageVolume                     AS cimsv,
	   (SELECT T2.csfpcskey                  AS csfpcskey,
                   cimcb.passnumber              AS cimcbpassnumber,
                   cimcb.dependent               AS cimcbdependent
            FROM   CIM_ControlledBy              AS cimcb,
           (SELECT T1.csfpcskey                  AS csfpcskey,
                   scfp.scpassnumber             AS scfpscpassnumber,
                   scfp.scsicontrollerkey        AS scfpscsicontrollerkey
            FROM   ScsiControllerFCPort          AS scfp,
           (SELECT csfp.cskey                    AS csfpcskey,
                   csfp.cspassnumber             AS csfpcspassnumber,
                   csfp.fcpportkey               AS csfpfcpportkey
            FROM   ComputerSystemFCPort          AS csfp
            WHERE  (csfp.Dedicated *= 3 OR csfp.Dedicated *= 15)  AND
                   csfp.cspassnumber = csfp.sdpassnumber          AND
                   csfp.sdpassnumber = csfp.fcppassnumber)        T1
            WHERE  scfp.fcpportkey = T1.csfpfcpportkey            AND
                   T1.csfpcspassnumber = scfp.scpassnumber        AND
                   scfp.scpassnumber  = scfp.dipassnumber         AND
                   scfp.dipassnumber  = scfp.fcppassnumber)       T2  
            WHERE  T2.scfpscsicontrollerkey = cimcb.antecedent    AND
                   T2.scfpscpassnumber = cimcb.passnumber         AND
                   cimcb.objectpath NOT LIKE ''%SCSIInterface%'') T3
    WHERE  cimsv.passnumber = T3.cimcbpassnumber                  AND
           T3.cimcbdependent = cimsv.objectpath
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION  refresh_CompositeOSNode() RETURNS INTEGER AS '
BEGIN
    DELETE FROM CompositeOSNode;
    INSERT INTO CompositeOSNode 
    SELECT DISTINCT ON(key, Removable)
           snhsi2.objectpath                      AS key,
           isStale(snhsi2.lastUpdate)             AS Removable,
           snhsi2.DevPath                         AS metaPath,  -- PK
           StorageHostKey(snhsi2.objectpath)      AS StorageHostKey
    FROM 
           Sun_NWS_HBA_ScsiInterface              AS snhsi2,
           sun_nws_hba_virtualcontroller          AS snhvc2
    WHERE
           snhvc2.objectpath = snhsi2.antecedent  AND
           snhsi2.passnumber = snhvc2.passnumber  AND
           snhvc2.creationclassname = ''Sun_NWS_HBA_VirtualController''
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION  refresh_Fabric() RETURNS INTEGER AS '
BEGIN
    DELETE FROM Fabric;
    INSERT INTO Fabric 
    SELECT DISTINCT ON(key, Removable)
           cimad.objectpath                    AS key,
           isStale(cimad.lastUpdate)           AS Removable,
           cimad.Name                          AS name  -- PK
    FROM 
           CIM_AdminDomain                     AS cimad,
           CIM_CollectedMSEs                   AS cimcmse,
           CIM_ComputerSystem                  AS cimcs
    WHERE
           cimcmse.collection = cimad.objectpath  AND
           cimcmse.member  = cimcs.objectpath     AND
           cimcs.Dedicated *= 5                   AND
           cimad.passnumber = cimcmse.passnumber  AND
           cimcmse.passnumber = cimcs.passnumber  AND
           isValidFabric(cimad.objectpath)
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Refreshes Storage ports and HBA ports

   Changed the check for the staleness of NxPort due to the bug in IBFab 
   provider, 4808216. Later this change should be undone after IBFab fixes 
   the bug.
*/

CREATE FUNCTION  refresh_NxPort() RETURNS INTEGER AS '
DECLARE 
    rec    RECORD;
BEGIN
    DELETE FROM NxPort;
    INSERT INTO NxPort 
    SELECT DISTINCT ON(key, Removable)
           csfp.fcpportkey                      AS key,
           isStale(csfp.fcplastupdate) OR
           isStale(csfp.sdlastupdate)  OR
           isStale(csfp.cslastupdate)           AS Removable,
           gbictype(csfp.fcpportkey)            AS GBICtype, 
	   csfp.fcpStatus                       AS state, 
	   csfp.fcpPermanentAddress             AS WWN,  -- PK
           NULL                                 AS ZoneKey ,
           csfp.fcpnetworkaddresses[1]          AS addressIdentifier, 
           CASE WHEN csfp.fcpPortType = 1
                THEN csfp.fcpotherporttype
                ELSE look_porttype(csfp.fcpPortType)
                END                             AS portType, 
           scfp.scsicontrollerkey               AS HBAKey,
           NULL                                 AS StorageSubSystemKey,
           nodeWWN(csfp.fcpportkey)             AS nodeWWN,
           LogicalDeviceKey(csfp.fcpportkey)    AS LogicalDeviceKey
    FROM 
           ComputerSystemFCPort                 AS csfp,
           ScsiControllerFCPort                 AS scfp
    WHERE 
           scfp.fcpportkey = csfp.fcpportkey        AND
           csfp.Dedicated ISNULL                    AND
           scfp.otheridentifyinginfo NOTNULL
;

    INSERT INTO NxPort 
    SELECT DISTINCT ON(key, Removable)
           csfp.fcpportkey                      AS key,
           isStale(csfp.fcplastupdate) OR
           isStale(csfp.sdlastupdate)  OR
           isStale(csfp.cslastupdate)           AS Removable,
           gbictype(csfp.fcpportkey)            AS GBICtype, 
	   csfp.fcpStatus                       AS state, 
	   csfp.fcpPermanentAddress             AS WWN,  -- PK
           NULL                                 AS ZoneKey ,
           csfp.fcpnetworkaddresses[1]          AS addressIdentifier, 
           CASE WHEN csfp.fcpPortType = 1
                THEN csfp.fcpotherporttype
                ELSE look_porttype(csfp.fcpPortType)
                END                             AS portType, 
           NULL                                 AS HBAKey,
           csfp.cskey                           AS StorageSubSystemKey,
           nodeWWN(csfp.fcpportkey)             AS nodeWWN,
           LogicalDeviceKey(csfp.fcpportkey)    AS LogicalDeviceKey
    FROM 
           ComputerSystemFCPort                 AS csfp
    WHERE 
           csfp.Dedicated *= 3 OR csfp.Dedicated *= 15
;
    FOR rec IN
        SELECT key
        FROM NXPort
    LOOP
        PERFORM zonekey(''NXPort'', rec.key);
    END LOOP;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


-- Refreshes LUN view.
CREATE FUNCTION  refresh_DeviceOSNode() RETURNS INTEGER AS '
BEGIN
    DELETE FROM DeviceOSNode;
    INSERT INTO DeviceOSNode 
    SELECT DISTINCT ON(key, Removable)
           snhsi1.objectpath                     AS key,
           isStale(snhsi1.lastUpdate)            AS Removable,
    	   snhsi1.TargetLun                      AS LUNid, 
	   T2.csname||'':''||snhsi1.DevPath      AS path,  -- PK
	   T2.csfpcskey                          AS StorageHostKey,
	   LogicalDeviceKeyOS(snhsi1.objectpath) AS LogicalDeviceKey, 
	   snhsi1.objectpath                     AS CompositeOSNodeKey,
	   T2.csfpfcpportkey                     AS NXPortKey
    FROM   Sun_NWS_HBA_ScsiInterface             AS snhsi1,
           (SELECT T1.csfpcskey                  AS csfpcskey,
                   T1.csfpfcpportkey             AS csfpfcpportkey,
                   T1.csname                     AS csname,
                   scfp.scpassnumber             AS scfpscpassnumber,
                   scfp.scsicontrollerkey        AS scfpscsicontrollerkey
            FROM   ScsiControllerFCPort          AS scfp,
           (SELECT csfp.cskey                    AS csfpcskey,
                   csfp.cspassnumber             AS csfpcspassnumber,
                   csfp.fcpportkey               AS csfpfcpportkey,
                   csfp.csname                   AS csname
            FROM   ComputerSystemFCPort          AS csfp
            WHERE  csfp.Dedicated ISNULL                          AND
                   csfp.cspassnumber = csfp.sdpassnumber          AND
                   csfp.sdpassnumber = csfp.fcppassnumber)        T1
            WHERE  scfp.fcpportkey = T1.csfpfcpportkey            AND
                   T1.csfpcspassnumber = scfp.scpassnumber        AND
                   scfp.scpassnumber  = scfp.dipassnumber         AND
                   scfp.dipassnumber  = scfp.fcppassnumber)       T2  
    WHERE  T2.scfpscsicontrollerkey = snhsi1.antecedent   AND
           T2.scfpscpassnumber = snhsi1.passnumber   
    -- MPXIO support
    UNION
    SELECT DISTINCT ON(key, Removable)
           snhsi1.objectpath                     AS key,
           isStale(snhsi1.lastUpdate)            AS Removable,
    	   snhsi1.TargetLun                      AS LUNid, 
	   T4.csname||'':''||snhsi1.DevPath      AS path,  -- PK
	   T4.csfpcskey                          AS StorageHostKey,
	   LogicalDeviceKeyOS(snhsi1.objectpath) AS LogicalDeviceKey, 
	   snhsi1.objectpath                     AS CompositeOSNodeKey,
	   T4.csfpfcpportkey                     AS NXPortKey
    FROM 
           Sun_NWS_HBA_ScsiInterface             AS snhsi1,
           (SELECT T3.csfpfcpportkey             AS csfpfcpportkey,
                   T3.csfpcskey                  AS csfpcskey,
                   T3.csname                     AS csname,
                   sc.objectpath                 AS scobjectpath,
                   sc.passnumber                 AS scpassnumber
            FROM   CIM_ScsiController            AS sc,
                   CIM_LogicalIdentity           AS li,
                   CIM_ExtraCapacityGroup        AS ecg,
           (SELECT T2.csfpfcpportkey             AS csfpfcpportkey,
                   T2.csfpcskey                  AS csfpcskey,
                   T2.csname                     AS csname,
                   rc.passnumber                 AS rcpassnumber,
                   rc.groupcomponent             AS rcgroupcomponent
            FROM   CIM_RedundancyComponent       AS rc,
           (SELECT T1.csfpcskey                  AS csfpcskey,
                   T1.csfpfcpportkey             AS csfpfcpportkey,
                   T1.csname                     AS csname,
                   scfp.scpassnumber             AS scfpscpassnumber,
                   scfp.scsicontrollerkey        AS scfpscsicontrollerkey 
            FROM   ScsiControllerFCPort          AS scfp,
           (SELECT csfp.cskey                    AS csfpcskey,
                   csfp.cspassnumber             AS csfpcspassnumber,
                   csfp.fcpportkey               AS csfpfcpportkey,
                   csfp.csname                   AS csname
            FROM   ComputerSystemFCPort          AS csfp
            WHERE  csfp.Dedicated ISNULL                          AND
                   csfp.cspassnumber = csfp.sdpassnumber          AND
                   csfp.sdpassnumber = csfp.fcppassnumber)        T1
            WHERE  scfp.fcpportkey = T1.csfpfcpportkey            AND
                   T1.csfpcspassnumber = scfp.scpassnumber        AND
                   scfp.scpassnumber  = scfp.dipassnumber         AND
                   scfp.dipassnumber  = scfp.fcppassnumber)       T2
            WHERE  T2.scfpscsicontrollerkey = rc.partcomponent    AND
                   T2.scfpscpassnumber = rc.passnumber)           T3
            WHERE  T3.rcpassnumber = sc.passnumber                AND
                   sc.passnumber = li.passnumber                  AND
                   li.passnumber = ecg.passnumber                 AND
                   T3.rcgroupcomponent = ecg.objectpath           AND
                   ((li.sameelement = sc.objectpath     AND
                     li.systemelement = ecg.objectpath)     OR
                    (li.sameelement = ecg.objectpath    AND
                     li.systemelement = sc.objectpath)))           T4
    WHERE   T4.scobjectpath = snhsi1.antecedent AND
            T4.scpassnumber = snhsi1.passnumber
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION  refresh_AsymmetricOSNode() RETURNS INTEGER AS '
BEGIN
    DELETE FROM AsymmetricOSNode;
    INSERT INTO AsymmetricOSNode 
    SELECT DISTINCT ON(key, Removable)
           snhsi1.objectpath                     AS key,
           isStale(snhsi1.lastUpdate)            AS Removable,
    	   snhsi1.TargetLun                      AS LUNid, 
	   snhsi1.DevPath                        AS path,  -- PK
   -- The following role field is not known where it is coming from at this
   -- moment.  From Discussion with Jeff H, We postponed to later
           1	                                 AS role,
           cimcs1.objectpath                     AS StorageHostKey,
           LogicalDeviceKeyOS(snhsi1.objectpath) AS LogicalDeviceKey, 
  -- CompositeOSNodeKey is duplicate to path, No way in CIM, DeviceOSNode to
  -- CompositeOSNodeKey
           snhsi1.objectpath                     AS CompositeOSNodeKey,
           cimfcp1.objectpath                    AS NXPortKey
    FROM 
           Sun_NWS_HBA_ScsiInterface             AS snhsi1,
           CIM_ScsiController                    AS cimsc1,
           CIM_LogicalIdentity                   AS cimli1,
           CIM_FCPort                            AS cimfcp1,
           CIM_SystemDevice                      AS cimsd1,
           CIM_ComputerSystem                    AS cimcs1
    WHERE
          snhsi1.antecedent = cimsc1.objectpath              AND
          snhsi1.passnumber = cimsc1.passnumber              AND
          cimsc1.passnumber = cimli1.passnumber              AND
          cimli1.passnumber = cimfcp1.passnumber             AND
          cimfcp1.passnumber = cimsd1.passnumber             AND
          cimsd1.passnumber = cimcs1.passnumber              AND
          (cimli1.SystemElement = cimsc1.objectpath OR
           cimli1.SameElement = cimsc1.objectpath)           AND
          (cimli1.SystemElement = cimfcp1.objectpath OR
           cimli1.SameElement = cimfcp1.objectpath)          AND
          cimsd1.PartComponent = cimfcp1.objectpath          AND
          (cimsd1.GroupComponent = cimcs1.objectpath AND
           cimcs1.Dedicated ISNULL)
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION Switch_name (VARCHAR, VARCHAR) RETURNS VARCHAR AS '
DECLARE
    tryValue ALIAS FOR $1;
    defValue ALIAS FOR $2;
BEGIN
    IF tryValue ISNULL
	THEN RETURN defValue;
    END IF;
    RETURN tryValue;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION  refresh_Switch() RETURNS INTEGER AS '
BEGIN
    DELETE FROM Switch;
    INSERT INTO Switch 
    SELECT DISTINCT ON(key, Removable) 
            cimcs0.objectpath                     AS key,
            isStale(cimcs0.lastUpdate)            AS Removable,
--    ipAddr = Sun_NWS_IBFAB_Switch:OtherIdentifyingInfo[???]
--             Find ??? by looking at identifyingdescriptions for the location
--             that contains "IP".
            OtherIdentifyingInfo_lookup(
                 cimcs0.OtherIdentifyingInfo,
                 cimcs0.identifyingdescriptions,
                 ''IP'')                          AS ipAddr,
            CASE  WHEN isStale(cimcs0.lastUpdate)
                  THEN ''Lost Comm''
                  ELSE cimcs0.status
                  END                             AS Status,
            Switch_model(cimcs0.objectpath)       AS model,
            Switch_firmwareRev(cimcs0.objectpath) AS firmwareRev,
            Switch_vendor(cimcs0.objectpath)      AS vendor,
--    domainid = Sun_NWS_IBFAB_Switch:OtherIdentifyingInfo[???]
--             Find ??? by looking at identifyingdescriptions for the location
--             that contains "GDID".
            OtherIdentifyingInfo_lookup(
                 cimcs0.OtherIdentifyingInfo,
                 cimcs0.identifyingdescriptions,
                 ''GDID'')                        AS domainId,
            cimcs0.Name                           AS WWN,   -- PK
	    Switch_name(cimpp0.Name, cimcs0.Name) AS name, 
            SwitchFabricKey(cimcs0.objectpath)    AS FabricKey
    FROM 
           CIM_ComputerSystem                     AS cimcs0,
           CIM_PhysicalPackage                    AS cimpp0,
           CIM_ComputerSystemPackage              AS cimcsp0,
           CIM_CollectedMSEs                      AS cimcmses0,
           CIM_AdminDomain                        AS cimad0
    WHERE
           cimcs0.Dedicated *= 5                     AND
           cimcs0.objectpath = cimcmses0.member      AND
           cimcs0.passnumber = cimpp0.passnumber     AND
           cimpp0.passnumber = cimcsp0.passnumber    AND
           cimcsp0.passnumber = cimcmses0.passnumber AND
           cimcs0.objectpath = cimcsp0.dependent     AND
           cimcsp0.antecedent = cimpp0.objectpath    AND
           cimcmses0.collection = cimad0.objectpath
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION  refresh_SwitchPort() RETURNS INTEGER AS '
DECLARE 
    rec    RECORD;
BEGIN
    DELETE FROM SwitchPort;
    INSERT INTO SwitchPort 
    SELECT DISTINCT ON(key, Removable)
           csfp.fcpportkey                             AS key,
           isStale(csfp.fcplastupdate)                 AS Removable,
           gbictype(csfp.fcpportkey)                   AS GBICtype, 
	   csfp.fcpStatus                              AS state, 
	   csfp.fcpPermanentAddress                    AS WWN,  -- PK
           NULL                                        AS ZoneKey ,
           CASE WHEN csfp.fcpPortType = 1
                THEN csfp.fcpotherporttype
                ELSE look_porttype(csfp.fcpPortType)
                END                                    AS portType, 
           csfp.fcpPortNumber                          AS number,
           csfp.cskey                                  AS SwitchKey
    FROM 
           ComputerSystemFCPort                        AS csfp
    WHERE 
           csfp.Dedicated *= 5
;

    FOR rec IN
        SELECT key
        FROM SwitchPort
    LOOP
        PERFORM zonekey(''SwitchPort'', rec.key);
    END LOOP;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION  refresh_Zone() RETURNS INTEGER AS '
BEGIN
    DELETE FROM Zone;
    INSERT INTO Zone
    SELECT DISTINCT ON(key, Removable)
           cimz.objectpath||enforcement_lookup(cimz.ZoneType)
                                                 AS key,
           isStale(cimz.lastUpdate)              AS Removable,
           cimz.InstanceName||enforcement_lookup(cimz.ZoneType)
                                                 AS name,   -- PK
           enforcement_lookup(cimz.ZoneType)     AS enforcement,
           cimad.objectpath                      AS FabricKey
    FROM 
           CIM_Zone                              AS cimz,
           Sun_NSM_OOBFAB_ZoneSetMember          AS snozsm,
           CIM_ZoneSet                           AS cimzs,
           Sun_NSM_OOBFAB_ActiveZoneSet          AS snoazs,
           CIM_AdminDomain                       AS cimad
    WHERE
           snozsm.member = cimz.objectpath        AND
           snozsm.collection = cimzs.objectpath   AND
           snoazs.member = cimzs.objectpath       AND
           snoazs.collection = cimad.objectpath   AND
           cimz.passnumber = snozsm.passnumber    AND
           snozsm.passnumber = cimzs.passnumber   AND
           cimzs.passnumber = snoazs.passnumber   AND
           snoazs.passnumber = cimad.passnumber 
;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


-- Materialize all the GUI views.
-- The order is significant here.
CREATE FUNCTION  refresh_VirtualTables() RETURNS INTEGER AS '
BEGIN
     RAISE NOTICE ''Start Refreshing NSM Virtual Tables '';
     RAISE NOTICE ''refresh_TempTables '';
     PERFORM refresh_TempTables();
     RAISE NOTICE ''refresh_StorageHost '';
     PERFORM refresh_StorageHost();
     RAISE NOTICE ''refresh_HBA '';
     PERFORM refresh_HBA();
     RAISE NOTICE ''refresh_Port '';
     PERFORM refresh_Port();
     RAISE NOTICE ''refresh_NxPort '';
     PERFORM refresh_NxPort();
     RAISE NOTICE ''refresh_SwitchPort '';
     PERFORM refresh_SwitchPort();
     RAISE NOTICE ''refresh_AttachedPort '';
     PERFORM refresh_AttachedPort();
     RAISE NOTICE ''refresh_SubSystemCluster '';
     PERFORM refresh_SubSystemCluster();
     RAISE NOTICE ''refresh_StorageSubSystem '';
     PERFORM refresh_StorageSubSystem();
     RAISE NOTICE ''refresh_LogicalDevice '';
     PERFORM refresh_LogicalDevice();
     RAISE NOTICE ''refresh_Volume '';
     PERFORM refresh_Volume();
     RAISE NOTICE ''refresh_Fabric '';
     PERFORM refresh_Fabric();
     RAISE NOTICE ''refresh_DeviceOSNode '';
     PERFORM refresh_DeviceOSNode();
     RAISE NOTICE ''refresh_Switch '';
     PERFORM refresh_Switch();
     RAISE NOTICE ''refresh_Zone '';
     PERFORM refresh_Zone();
     RAISE NOTICE ''Porsche_discovery '';
     PERFORM Porsche_discovery();
     RAISE NOTICE ''Done Refreshing NSM Virtual Tables '';
     RETURN 1;
END;
' LANGUAGE 'plpgsql';
