-- change refresh_switch back
DROP FUNCTION refresh_Switch();
DROP FUNCTION Switch_name(VARCHAR, VARCHAR);

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
            cimpp0.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();

-- setting IP addresses for T4
DROP FUNCTION setT4IPAddress();
CREATE FUNCTION setT4IPAddress () RETURNS INTEGER AS '
DECLARE
    rec RECORD;
    rec2 RECORD;
    iptmpAddr VARCHAR;
BEGIN
     -- Set the IP address from CIM_UnitaryComputerSystem for
     -- T4
     FOR rec IN
         SELECT
               cimucs.otheridentifyinginfo          AS otheridentifyinginfo,
               cimucs.identifyingdescriptions       AS identifyingdescriptions,
               cimucs.objectpath                    AS key
         FROM
               CIM_UnitaryComputerSystem                    AS cimucs
         WHERE
               (cimucs.dedicated *= 3 OR
                cimucs.dedicated *= 15)    AND
               OtherIdentifyingInfo_lookup(cimucs.OtherIdentifyingInfo,
                   cimucs.identifyingdescriptions, ''IP'') NOTNULL
     LOOP
        SELECT INTO rec2
               storagesubsystemkey
        FROM   Annotation
        WHERE  storagesubsystemkey = rec.key;
        IF NOT FOUND
        THEN
            iptmpAddr := OtherIdentifyingInfo_lookup(
                      rec.OtherIdentifyingInfo,
                      rec.identifyingdescriptions,
                      ''IP'');
            INSERT INTO  Annotation
               (storagesubsystemkey, ipaddr, ipeditable)
               VALUES (rec.key, iptmpAddr, ''f'' );
            RETURN 1;
        END IF;
     END LOOP;
     RETURN 0;
END;
' LANGUAGE 'plpgsql';


-- box model number
DROP FUNCTION Porsche_discovery();
CREATE FUNCTION Porsche_discovery () RETURNS INTEGER AS '
DECLARE
    vicomrec            RECORD;     --record to store vicom
    boxrec              RECORD;     --record to store cabinet info
    rec1                RECORD;     --record for inner LOOP
    rec2                RECORD;     --record for inner LOOP
    onerec              RECORD;     --record containing one row
    tmpFabricKey        VARCHAR;
    tmpString           VARCHAR;    --tmp String for insert and update
BEGIN
    -- Clear the temporary delete table
    DELETE FROM PorscheDeleteTable;
    -- Start deleting the devices inside the Box.
    FOR boxrec IN
        SELECT
               cimucs.objectpath          AS ssskey,
               cimpp.objectpath           AS cimppkey,
               cimpp.Name                 AS name,
               cimpp.Manufacturer         AS vendor,
               cimpp.Model                AS model,
               cimpp.Version              AS firmwareRev,
               cimpp.Status               AS status,
               cimpp.lastUpdate           AS lastUpdate
        FROM
               CIM_UnitaryComputerSystem  AS cimucs,
               CIM_ParticipatingCS        AS cimpcs,
               CIM_Cluster                AS cimc,
               CIM_ComputerSystemPackage  AS cimcsp,
               CIM_PhysicalPackage        AS cimpp
        WHERE
               cimucs.objectpath = cimpcs.antecedent AND
               cimpcs.dependent = cimc.objectpath AND
               (cimucs.dedicated *= 3 OR
                cimucs.dedicated *= 15)  AND
               cimc.objectpath = cimcsp.dependent   AND
               cimcsp.antecedent = cimpp.objectpath AND
               cimc.Dedicated *= 15                 AND
               cimc.passnumber = cimcsp.passnumber  AND
               cimc.passnumber = cimpp.passnumber
    LOOP
        -- Found a Vicom box or StorageSubSystem inside Box.
        -- DELETE StorageSubSystem
        INSERT INTO PorscheDeleteTable
           (TableName, tablekey)
           VALUES (''StorageSubSystem'', boxrec.ssskey);
        -- Start Inserting/Updating the collapsed assets.
        -- Insert StorageSubSystem
        SELECT INTO onerec * FROM StorageSubSystem WHERE key = boxrec.cimppkey;
        IF NOT FOUND
        THEN
            IF isStale(boxrec.lastUpdate)
            THEN tmpString := ''Lost Comm'';
            ELSE tmpString := boxrec.status;
            END IF;

        IF trim(both '' '' from boxrec.model) = ''T4''
        THEN  boxrec.model := ''SE6300'';
        END IF;

        INSERT INTO  StorageSubSystem
           (key, removable, status, name, firmwarerev, vendor, model)
           VALUES (boxrec.cimppkey, isStale(boxrec.lastUpdate), tmpString,
                   boxrec.name, boxrec.firmwareRev, boxrec.vendor,
                   boxrec.model);
            PERFORM setIPAddress(boxrec.cimppkey);
        END IF;
        -- Update StorageSubSystemKey column
        tmpString :=
            ''UPDATE NxPort '' ||
            ''SET StorageSubSystemKey='' ||
            '''''''' || boxrec.cimppkey || '''''''' || '','' ||            ''nodewwn =''||
            '''''''' || boxrec.name || '''''''' ||
            '' WHERE StorageSubSystemKey='' ||
            '''''''' || boxrec.ssskey ||'''''''' || '';'';
        EXECUTE tmpString;
        tmpString :=
            ''UPDATE LogicalDevice '' ||
            ''SET StorageSubSystemKey='' ||
            '''''''' || boxrec.cimppkey || '''''''' ||
            '' WHERE StorageSubSystemKey='' ||
            '''''''' || boxrec.ssskey ||'''''''' || '';'';
        EXECUTE tmpString;
        DELETE FROM tmp_LogicalDevice;
        INSERT INTO tmp_LogicalDevice
        SELECT key,removable,deviceid,storagesubsystemkey
        FROM LogicalDevice;
        DELETE FROM LogicalDevice;
        INSERT INTO LogicalDevice
        SELECT DISTINCT ON(key, StorageSubSystemKey, Removable)
               key,removable,deviceid,storagesubsystemkey
        FROM tmp_LogicalDevice;
        tmpString :=
            ''UPDATE Volume '' ||
            ''SET StorageSubSystemKey='' ||
            '''''''' || boxrec.cimppkey || '''''''' ||
            '' WHERE StorageSubSystemKey='' ||
            '''''''' || boxrec.ssskey ||'''''''' || '';'';
        EXECUTE tmpString;
        DELETE FROM tmp_volume;
        INSERT INTO tmp_volume
        SELECT key,removable,deviceid,capacity,storagesubsystemkey
        FROM Volume;
        DELETE FROM Volume;
        INSERT INTO Volume
        SELECT DISTINCT ON(key, StorageSubSystemKey, Removable)
               key,removable,deviceid,capacity,storagesubsystemkey
        FROM tmp_Volume;
    END LOOP;
    -- Starting deleting
    FOR boxrec IN
        SELECT tablename,
               tablekey
        FROM   PorscheDeleteTable
    LOOP
        tmpString :=
            ''DELETE FROM '' || boxrec.tablename ||
            '' WHERE key = '' ||
            '''''''' || boxrec.tablekey || '''''''';
        EXECUTE tmpString;
    END LOOP;
    -- Clean up the StorageCluster as it is not used but confuse topo engine.
    EXECUTE ''DELETE FROM SubSystemCluster'';
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- put annotations back to text
DROP TABLE Annotation;
CREATE TABLE Annotation ( --- No key, duplicates are allowed.
    text TEXT,
    ipaddr VARCHAR,
    ipeditable BOOLEAN default TRUE,
    StorageHostKey VARCHAR, -- may be NULL,This will be pointing to actual
    HBAKey VARCHAR, -- may be NULL          physical table.
    StorageSubSystemKey VARCHAR, -- may be NULL
    SwitchKey VARCHAR -- may be NULL
);

-- temp table (looks like old annotation table - change so that old
-- annotations are copied into here)
CREATE TABLE AnnotationOld ( --- No key, duplicates are allowed.
    text SMALLINT[],
    ipaddr VARCHAR,
    ipeditable BOOLEAN default TRUE,
    StorageHostKey VARCHAR, -- may be NULL,This will be pointing to actual
    HBAKey VARCHAR, -- may be NULL          physical table.
    StorageSubSystemKey VARCHAR, -- may be NULL
    SwitchKey VARCHAR -- may be NULL
);

-- adds escapes to characters as needed
CREATE FUNCTION convert_char (SMALLINT) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    txtchar	VARCHAR;
BEGIN
    txtchar := chr(inValue);
    IF (txtchar = ''\\'''')
    THEN RETURN ''\\\\'' || txtchar;
    ELSE RETURN txtchar;
    END IF;
END;
' LANGUAGE 'plpgsql';

-- converts old string based annotations (stored into 'annotationOld' table)
-- to byte array annotations (stored in new 'annotation' table)
CREATE FUNCTION convert_annotations () RETURNS INTEGER AS '
DECLARE
    annotrec            RECORD;
    counter             INTEGER;
    insertString        VARCHAR;
    value               SMALLINT;
BEGIN
    FOR annotrec IN
        SELECT * FROM annotationOld
    LOOP
        insertString := ''INSERT INTO annotation (ipaddr,ipeditable,storagehostkey,hbakey,storagesubsystemkey,switchkey,text) VALUES ( '';
        IF (annotrec.ipaddr ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
                insertString := insertString || '''''''' || annotrec.ipaddr || '''''''' || '','';
        END IF;
        IF (annotrec.ipeditable ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            IF (annotrec.ipeditable)
	    THEN
                insertString := insertString || ''''''t'''''' || '','';
	    ELSE
		insertString := insertString || ''''''f'''''' || '','';
	    END IF;
        END IF;
        IF (annotrec.storagehostkey ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            insertString := insertString || '''''''' || annotrec.storagehostkey || '''''''' || '','';
        END IF;
        IF (annotrec.hbakey ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            insertString := insertString || '''''''' || annotrec.hbakey || '''''''' || '','';
        END IF;
        IF (annotrec.storagesubsystemkey ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            insertString := insertString || '''''''' || annotrec.storagesubsystemkey || '''''''' || '','';
        END IF;
        IF (annotrec.switchkey ISNULL)
        THEN
            insertString := insertString || '' null, '';
        ELSE
            insertString := insertString || '''''''' || annotrec.switchkey || '''''''' || '','';
        END IF;
        IF ( annotrec.text ISNULL )
        THEN
            insertString := insertString || '' null '';
        ELSE
            counter := 1;
            insertString := insertString || '''''''';
            LOOP
                IF (annotrec.text[counter] ISNULL)
                THEN
                    EXIT; -- reached end of loop
                END IF;
                value := annotrec.text[counter];
                insertString := insertString || convert_char(value);
		counter := counter + 1;
            END LOOP;
            insertString := insertString || '''''''';
        END IF;
        insertString := insertString || '');'';
        EXECUTE insertString;
    END LOOP;

    RETURN 1;
END;
' LANGUAGE 'plpgsql';
