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;

        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_();