-- This contains the DB changes made for the -04 version of the patch.

-- change refresh_switch back
-- recreate getIPGMAL
/*
   Given a GMAL value from CIM_CS:OtherIdentifyingInfo,
   Extract the IP address following http://
   This is necessary to extract the IP address of the management
   app for McData Switch.

   Input param : 
         inValue   : OtherIdentifyingInfo of CIM_ComputerSystem
   Output
         IP address
*/
CREATE FUNCTION getIPGMAL (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    GMALValue ALIAS FOR $1;
    begincounter INTEGER;
    endcounter   INTEGER;
    counter      INTEGER;
    subtmpString    VARCHAR;
    tmpString       VARCHAR;
    found           BOOLEAN;
BEGIN
    IF GMALValue ISNULL
    THEN RETURN NULL; 
    END IF;
    begincounter := position(''http://'' in GMALValue) + 7;
    subtmpString := substring(GMALValue FROM begincounter FOR 20);

    -- Find the endcounter
    found := ''f'';
    counter := 1;
    WHILE NOT found 
    LOOP
       tmpString := substring(subtmpString FROM counter FOR 1);
       IF ((tmpString >= ''0'' AND tmpString <= ''9'') OR
           (tmpString = ''.''))
       THEN
            counter := counter + 1;
       ELSE
            found := ''t'';
       END IF;
    END LOOP;
    
    endcounter := begincounter + counter - 1;
    IF endcounter <= begincounter
    THEN
        endcounter := char_length(subtmpString) + begincounter;
    END IF;
    RETURN substring(GMALValue FROM begincounter FOR endcounter-begincounter);
END;
' LANGUAGE 'plpgsql';

-- recreate refresh_Switch
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 "GMAL".
            getIPGMAL(OtherIdentifyingInfo_lookup(
                 cimcs0.OtherIdentifyingInfo,
                 cimcs0.identifyingdescriptions,
                 ''GMAL''))                           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();
