SGJP / Kuznia

-- DEFAULTS

ALTER TABLE history
ALTER user_id_ SET DEFAULT current_setting('var.user_id' :: TEXT) :: INTEGER,
ALTER transaction_began_ SET DEFAULT transaction_timestamp(),
ALTER timestamp_ SET DEFAULT clock_timestamp();


-- make_history_

CREATE OR REPLACE FUNCTION make_history_()
    RETURNS TRIGGER
LANGUAGE plpgsql
AS
    $BODY$

    DECLARE
        ri                  RECORD;
        oldValue            TEXT;
        newValue            TEXT;
        isColumnSignificant BOOLEAN;
        isValueModified     BOOLEAN;
        lexemeId            INTEGER;
        patternId           INTEGER;
        temp                RECORD;
        userId              TEXT;
    BEGIN
        IF (TG_OP = 'DELETE')
        THEN
            temp := OLD;
        ELSE
            temp := NEW;
        END IF;

        SELECT
            current_setting('var.user_id')
        INTO userId;
        IF (userId = '0')
        THEN
            RETURN temp;
        END IF;

        IF (TG_TABLE_NAME = 'leksemy')
        THEN
            lexemeId := temp.id;
        ELSIF (TG_TABLE_NAME IN ('odmieniasie', 'leksemy_w_slownikach'))
            THEN
                lexemeId := temp.l_id;
        ELSIF (TG_TABLE_NAME IN
               ('kwalifikatory_leksemow', 'dictionary_lexemeav', 'dictionary_lexemecv'))
            THEN
                lexemeId := temp.lexeme_id;
        ELSIF (TG_TABLE_NAME = 'odsylacze')
            THEN
                lexemeId := temp.l_id_od;
        ELSIF (TG_TABLE_NAME = 'kwalifikatory_odmieniasiow')
            THEN
                SELECT
                    l_id
                INTO lexemeId
                FROM odmieniasie
                WHERE id = temp.lexemeinflectionpattern_id;
        END IF;
        IF (TG_TABLE_NAME = 'wzory')
        THEN
            patternId := temp.id;
        ELSIF (TG_TABLE_NAME = 'zakonczenia')
            THEN
                patternId := temp.w_id;
        ELSIF (TG_TABLE_NAME = 'kwalifikatory_zakonczen')
            THEN
                SELECT
                    w_id
                INTO patternId
                FROM zakonczenia
                WHERE id = temp.ending_id;
        END IF;

        FOR ri IN
        SELECT
            ordinal_position,
            column_name,
            data_type
        FROM information_schema.columns
        WHERE
            table_schema = quote_ident(TG_TABLE_SCHEMA)
            AND table_name = quote_ident(TG_TABLE_NAME)
        ORDER BY ordinal_position
        LOOP
-- NEW value
        IF (TG_OP = 'DELETE')
        THEN
            newValue := '' :: VARCHAR;
        ELSE
            EXECUTE 'SELECT ($1).' || ri.column_name || '::text'
            INTO newValue
            USING NEW;
        END IF;
-- OLD value
        IF (TG_OP = 'INSERT')
        THEN
            oldValue := '' :: VARCHAR;
        ELSE
            EXECUTE 'SELECT ($1).' || ri.column_name || '::text'
            INTO oldValue
            USING OLD;
        END IF;
        IF newValue IS NULL
        THEN
            newValue := '' :: VARCHAR;
        END IF;
        IF oldValue IS NULL
        THEN
            oldValue := '' :: VARCHAR;
        END IF;

        isColumnSignificant := (position('_x_' IN ri.column_name) < 1) AND
                               (ri.column_name NOT IN
                                ('id', 'l_id', 'lexeme_id', 'l_id_od', 'data_modyfikacji'));
        IF isColumnSignificant
        THEN
            isValueModified := oldValue != newValue;
            IF isValueModified
            THEN
                INSERT INTO history (operation_, table_oid_, table_name_, id_, column_name_, ordinal_position_of_column_, old_value_, new_value_, lexeme_id_, pattern_id_)
                    VALUES (TG_OP, TG_RELID, TG_TABLE_NAME, temp.id,
                            ri.column_name :: VARCHAR, ri.ordinal_position,
                            oldValue :: TEXT, newValue ::
                                              TEXT, lexemeId, patternId);
            END IF;
        END IF;
        END LOOP;

        RETURN temp;
    END;

    $BODY$;


-- TRIGGERS

-- leksemy

CREATE TRIGGER leksemy_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON leksemy
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER odmieniasie_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON odmieniasie
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER kwalifikatory_leksemow_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON kwalifikatory_leksemow
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER kwalifikatory_odmieniasiow_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON kwalifikatory_odmieniasiow
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER leksemy_w_slownikach_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON leksemy_w_slownikach
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER dictionary_lexemecv_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON dictionary_lexemecv
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER odsylacze_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON odsylacze
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER dictionary_lexemeav_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON dictionary_lexemeav
FOR EACH ROW EXECUTE PROCEDURE make_history_();

-- wzory

CREATE TRIGGER wzory_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON wzory
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER zakonczenia_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON zakonczenia
FOR EACH ROW EXECUTE PROCEDURE make_history_();

CREATE TRIGGER kwalifikatory_zakonczen_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON kwalifikatory_zakonczen
FOR EACH ROW EXECUTE PROCEDURE make_history_();

--CREATE TRIGGER XXX_trigger_history_
--AFTER INSERT OR UPDATE OR DELETE ON XXX_
--FOR EACH ROW EXECUTE PROCEDURE make_history_();