-- @(#)Element_mgr_trigger-patch01.ddl	1.7 05/10/13
--
-- ##################################################################
--
-- What is the change: alter existing functions to deal with model-vendor
--     combinations for which there are no template entries, and to deal
--     with null IP addresses returned from StorADE with <ip> placeholders
--     in template entries
--
-- Why change is needed: location values are showing up as "null" and 
--     element names are linking to nowhere
--
-- Risks to existing user data: No risk: this is system data, not user data.
--
--
-- Responsibility here is to initialize the element_manager field to either
-- a meaningful value or to null.
-- 
-- If a matching entry is found in element_manager_template table for the
-- new record's model and vendor field values, and that template entry has 
-- a placeholder for <host> in its location_format field, and the new record 
-- does have a host value, then set the new record's element_manager field to use 
-- that host value. 
-- 
-- If the template entry has a <host> placeholder but no host value is present
-- in the new record, set the new record's element_manager field to null and.
-- return that new record.
--
-- If the template entry has an <ip> placeholder and an IP value is present in
-- the new record, set the new record's ip_address field to that value. 
--
-- If the template entry has an <ip> placeholder but no IP value is present
-- in the new record, set the new record's element_manager field to null and.
-- return that new record.
--
-- If no match is found in element_manager_template table for the new record's 
-- model and vendor field values, set the new record's element_manager field to null.
-- 
-- In either event, return the new record as initialized from above steps.
--
CREATE OR REPLACE FUNCTION fntr_element_manager () RETURNS TRIGGER AS '
DECLARE
    rec     RECORD;
    ipPos   INTEGER;
    hostPos INTEGER;
BEGIN
    SELECT INTO rec \*
    FROM element_manager_template AS emt
    WHERE new.model  = emt.model AND
          new.vendor = emt.vendor;
    IF FOUND
    THEN
        new.element_manager := rec.location_format;
        hostPos := position(''<host>'' in rec.location_format);
        IF (hostPos > 0)
        THEN
            IF (rec.host IS NOT NULL)
            THEN
                new.element_manager := replace(rec.location_format, ''<host>'', rec.host);
            ELSE
                new.element_manager := null;
                RETURN NEW;
            END IF;
        END IF;
        ipPos := position(''<ip>'' in rec.location_format);
        IF (ipPos > 0)
        THEN
            IF (new.ip_address IS NOT NULL)
            THEN
                new.element_manager := replace(new.element_manager, ''<ip>'', new.ip_address);
            ELSE
                new.element_manager := null;
                RETURN NEW;
            END IF;
        END IF;
    ELSE
        new.element_manager := null;
        RETURN NEW;
    END IF;
    RETURN NEW;
END;
 ' LANGUAGE 'plpgsql' ;
--
--
--
-- Responsibility here is to initialize the element.element_manager field to
-- a meaningful value or to null.
--
-- For each entry found in element table that matches the new record's model
-- and vendor field values:
--
-- (1) if the new record has a placeholder for <host> in its location_format
-- field and a non-null host value, then update the element_manager field in
-- each matching element table entry to use that host value.
--
-- (2) if the new record has a placeholder for <host> in its location_format
-- field and a null host value, then update the element_manager field in
-- each matching element table entry, setting these values to null and return
-- the new record.
--
-- (3) if the element table entry has a placeholder for <ip> in its element_manager
-- field and a non-null IP value, then update the element_manager field in
-- that element table entry to use that IP value.
--
-- (4) if the element table entry has a placeholder for <ip> in its element_manager
-- field and a null IP value, then update the element_manager field in
-- each matching element table entry, setting these values to null and return
-- the new record.
--
-- In either event, return the new record as initialized from above steps.
--
CREATE OR REPLACE FUNCTION fntr_element_manager_template () RETURNS TRIGGER AS '
DECLARE
    rec     RECORD;
    ipPos   INTEGER;
    hostPos INTEGER;
BEGIN
    FOR rec IN
        SELECT \* FROM element
        WHERE new.model  = element.model AND
              new.vendor = element.vendor
    LOOP
        rec.element_manager := new.location_format;
        hostPos := position(''<host>'' in new.location_format);
        IF (hostPos > 0)
        THEN
            IF (new.host IS NOT NULL)
            THEN
                rec.element_manager := replace(rec.element_manager, ''<host>'', new.host);
                UPDATE element SET element_manager = rec.element_manager
                WHERE new.model = element.model
                AND new.vendor = element.vendor;
            ELSE
                rec.element_manager := null;
                UPDATE element SET element_manager = rec.element_manager
                WHERE new.model = element.model
                AND new.vendor = element.vendor;
                RETURN NEW;
            END IF;
        END IF;
        ipPos := position(''<ip>'' in rec.element_manager);
        IF (ipPos > 0)
        THEN
            IF (rec.ip_address IS NOT NULL)
            THEN
                rec.element_manager := replace(rec.element_manager, ''<ip>'', rec.ip_address);
                UPDATE element SET element_manager = rec.element_manager
                WHERE new.model = element.model
                AND new.vendor = element.vendor;
            ELSE
                rec.element_manager := null;
                UPDATE element SET element_manager = rec.element_manager
                WHERE new.model = element.model
                AND new.vendor = element.vendor;
                RETURN NEW;
            END IF;
        END IF;
    END LOOP;
    RETURN NEW;
END;
 ' LANGUAGE 'plpgsql' ;
