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;

        operation                  VARCHAR;
        table_oid                  INTEGER;
        table_name1                VARCHAR;
        id                         INTEGER;
        column_name1               VARCHAR;
        ordinal_position_of_column INTEGER;
        old_value                  TEXT;
        new_value                  TEXT;
        lexeme_id                  INTEGER;
        pattern_id                 INTEGER;
    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
                operation := TG_OP;
                table_oid := TG_RELID;
                table_name1 := TG_TABLE_NAME;
                id = temp.id;
                column_name1 := ri.column_name :: VARCHAR;
                ordinal_position_of_column := ri.ordinal_position;
                old_value := oldValue :: TEXT;
                new_value := newValue :: TEXT;
                lexeme_id := lexemeId;
                pattern_id := patternId;
                INSERT INTO history (operation_, table_oid_, table_name_, id_, column_name_, ordinal_position_of_column_, old_value_, new_value_, lexeme_id_, pattern_id_)
                    VALUES (operation, table_oid, table_name1, id, column_name1, ordinal_position_of_column, old_value, new_value, lexeme_id, pattern_id);
            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_();