-- This contains the SQL changes made since the -03 version of the patch 
-- was released.

-- change to get IP address from "IP" instead of "GMAL"
DROP FUNCTION refresh_Switch();

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';

select refresh_Switch();

-- get rid of unneeded function
DROP FUNCTION getIPGMAL(VARCHAR);
