history.sql
4.58 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
-- 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', 'wartosci_klasyfikacji_lexemes')) 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', '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 wartosci_klasyfikacji_lexemes_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON wartosci_klasyfikacji_lexemes -- ta tabela zmieni nazwę
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_();
-- 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_();