|
Отслеживание изменений записей - дёшево и сердито
#33055112
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: ненадолго из запоя
Сообщения: 1 306
|
|
BusyManНе фиксируется вот что:
1. Какое поле изменили
2. Старое и новое значение поля
А зачем? Пример ниже, как можно такого рода инфу получить и провести rollback
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.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
create table global_history (
change_id bigint ,
change_date timestamptz,
change_table varchar( 64 ),
change_user varchar( 64 ),
change_type varchar( 20 ),
change_host varchar( 64 )
);
create sequence change_id_seq start 1 ;
create or replace function log_change(_table varchar( 64 ), _change_type varchar( 64 ),
_where_clause text, _user varchar( 64 ),
_host varchar( 64 )) returns void
AS $$
BEGIN
EXECUTE 'insert into '||_table||'_history select nextval(''change_id_seq''), now(), ' ||
quote_literal(_table) || ',' ||
quote_literal(_user) || ',' ||
quote_literal(_change_type) || ',' ||
quote_literal(_host) || ',' ||
' * from ' || _table || ' where ' || _where_clause;
return;
END $$ language 'plpgsql';
create table my_table (
id int primary key,
data1 text,
data2 text
);
CREATE TABLE
create table my_table_history (
like my_table,
primary key (change_id)
) inherits (global_history);
create type DIFF_RECORD AS (
att_name varchar( 20 ),
att_changed boolean
);
create or replace function log_diff(_table varchar( 64 ), _change_id_1 bigint,
_change_id_2 bigint) returns setof diff_record as $$
DECLARE log_row RECORD;
chg_row RECORD;
diff_row DIFF_RECORD;
BEGIN
FOR log_row IN SELECT a.attname as att_name FROM pg_class c, pg_attribute a
WHERE c.relname = _table AND a.attrelid = c.oid AND a.attnum> 0 ORDER BY a.attnum LOOP
FOR chg_row IN EXECUTE
'select (select '||log_row.att_name||' from '||_table||'_history where change_id='||text(_change_id_1)||
') <> (select '||log_row.att_name||' from '||_table||'_history where change_id='||text(_change_id_2)||
') as changed' LOOP
diff_row.att_name := log_row.att_name;
diff_row.att_changed := chg_row.changed;
if chg_row.changed IS NULL THEN diff_row.att_changed := true; END IF;
RETURN NEXT diff_row;
END LOOP;
END LOOP;
RETURN;
END $$ LANGUAGE 'plpgsql';
create or replace function log_rollback(_table varchar( 64 ), _change_id_1 bigint, _change_id_2 bigint,
_where_clause text) returns void as $$
DECLARE _update_sql TEXT;
diff_row DIFF_RECORD;
_count INTEGER;
_history_table TEXT;
BEGIN
_count := 0 ;
_history_table := _table || '_history';
_update_sql := 'UPDATE '||_table||' SET ';
FOR diff_row IN SELECT * FROM log_diff(_table, _change_id_1, _change_id_2) LOOP
IF diff_row.att_changed THEN
IF _count > 0 THEN _update_sql := _update_sql || ' , '; END IF;
_update_sql := _update_sql ||
diff_row.att_name || ' = ' || _history_table || '.' || diff_row.att_name;
_count := _count + 1 ;
END IF;
END LOOP;
IF _count > 0 THEN
_update_sql := _update_sql || ' FROM '|| _history_table || ' WHERE '||_history_table
|| '.change_id = ' || text(_change_id_1) || ' AND '||_where_clause;
RAISE NOTICE 'PERFORMING QUERY: %', _update_sql;
EXECUTE _update_sql;
END IF;
return;
END $$ language 'plpgsql';
insert into my_table (id, data1) values( 1 , 'test');
select log_change('my_table', 'create', 'id=1', 'anonymous', 'unknown');
update my_table set data1='test2' where id = 1 ;
select log_change('my_table', 'update', 'id=1', 'anonymous', 'unknown');
update my_table set data2='test3' where id = 1 ;
select log_change('my_table', 'update', 'id=1', 'anonymous', 'unknown');
select * from my_table;
id | data1 | data2
----+-------+-------
1 | test2 | test3
select * from log_diff ('my_table', 1 , 2 );
att_name | att_changed
----------+-------------
id | f
data1 | t
data2 | t
select * from log_diff ('my_table', 1 , 3 );
att_name | att_changed
----------+-------------
id | f
data1 | t
data2 | t
select * from log_diff ('my_table', 2 , 3 );
att_name | att_changed
----------+-------------
id | f
data1 | f
data2 | t
select log_rollback('my_table', 1 , 3 , 'my_table.id=1');
NOTICE: PERFORMING QUERY: UPDATE my_table SET data1 = my_table_history.data1 ,
data2 = my_table_history.data2 FROM my_table_history WHERE my_table_history.change_id = 1 AND my_table.id= 1
log_rollback
--------------
select * from my_table;
id | data1 | data2
----+-------+-------
1 | test |
select log_change('my_table','rollback to 1', 'id=1', 'anonymous', 'unknown');
log_change
------------
select * from global_history;
change_id | change_date | change_table | change_user | change_type | change_host
-----------+------------------------------+--------------+-------------+---------------+-------------
1 | 2005 - 05 - 09 14 : 58 : 09 . 06797 + 03 | my_table | anonymous | create | unknown
2 | 2005 - 05 - 09 14 : 58 : 09 . 06797 + 03 | my_table | anonymous | update | unknown
3 | 2005 - 05 - 09 14 : 58 : 09 . 06797 + 03 | my_table | anonymous | update | unknown
4 | 2005 - 05 - 09 14 : 58 : 09 . 06797 + 03 | my_table | anonymous | rollback to 1 | unknown
select * from my_table_history;
change_id | change_date | change_table | change_user | change_type | change_host | id | data1 | data2
-----------+------------------------------+--------------+-------------+---------------+-------------+----+-------+-------
1 | 2005 - 05 - 09 14 : 58 : 09 . 06797 + 03 | my_table | anonymous | create | unknown | 1 | test |
2 | 2005 - 05 - 09 14 : 58 : 09 . 06797 + 03 | my_table | anonymous | update | unknown | 1 | test2 |
3 | 2005 - 05 - 09 14 : 58 : 09 . 06797 + 03 | my_table | anonymous | update | unknown | 1 | test2 | test3
4 | 2005 - 05 - 09 14 : 58 : 09 . 06797 + 03 | my_table | anonymous | rollback to 1 | unknown | 1 | test |
|
|
|
