通过检查审计数据将db更改还原到指定时间的查询

Question

我有一个模块，在我的应用程序中处理一些业务规则，有几个表存储这些规则。

原始业务规则表:br_tbl_1

br_id | col_1 | col_2 
------+-------+--------
1    |  a    |   myk
2    |  b    |   abc

相关表:br_tbl_2

id | br_id | col_1 
---+-------+--------
1 |  1    |   something
2 |  1    |   something_else
3 |  2    |   Another thing

等等…

现在，为了跟踪对业务规则所做的更改，我为上面的每个表都设置了一个审计表，如下所示。

业务规则审计表:br_tbl_1_audit

id | br_id |  col_1 | col_2 | audit_dtme          | operation
---+-------+--------+-------+---------------------+-----------------
1 |  1    |   a    | xyz   | 01-01-2001 12:30:10 |   INSERT 
2 |  1    |   a    | myk   | 02-01-2001 01:00:00 |   UPDATE
3 |  2    |   b    | abc   | 02-01-2001 01:10:30 |   INSERT

通过查看br_tbl_1_audit表中的数据，我们可以看到br_id = 1的col_2的值已经从"xyz";myk"

同样，我们有一个审计表用于其他业务规则表。

相关表的审计表:br_tbl_2_audit

id | br_id | col_1            | audit_dtme           |  operation 
---+-------+------------------+----------------------+--------------
1 |  1    |   something      | 01-01-2001 12:30:10  |  INSERT 
2 |  1    |   something_else | 01-01-2001 12:30:10  |  INSERT
3 |  2    |   Another thing  | 02-01-2001 01:10:30  |  INSERT 

我需要一个查询，它接受一个br_id和一个audit_date_time，并回滚所有表中br_id的所有数据到audit_dtme

我可以用脚本做到这一点，但是我不是很好SQL查询，我感谢帮助。

供参考:我正在使用Postgres，但任何SQL应该足以推动我在正确的方向。

Answer 1

在任何给定的表中，您都可以使用distinct on:

select distinct on (a.br_id) a.*
from br_tbl_1_audit a
where a.audit_dtime <= $audit_date_time
order by a.br_id, a.audit_dtime desc;

您还可以筛选一个或多个br_id值。

您可以对所有您关心的表重复此操作。

如果需要替换一行，那么可以使用update:

update br_tbl_1 t
set col_1 = a.col_1,
col_2 = a.col_2
from (select a.*
from br_tbl_1_audit a
where a.audit_dtime <= $audit_date_time and
a.br_id = 1
order by a.audit_dtime desc
limit 1
) a
where t.br_id = 1;

Answer 2

我可能会说，如果你有太多的表链接，这将是非常困难的处理。

下面是示例代码，如果您只能从一个表中删除。现在你可以根据你的需要修改它。

declare @id int, 
@br_id int, 
@br_id_input int = 1, 
@col_1 varchar(100), 
@col_2 varchar(100), 
@audit_dtme datetime, 
@operation varchar(100), 
@audit_date_time datetime = '2001-01-01 12:30:10.000';
declare cur cursor 
for select id, br_id, col_1, col_2, audit_dtme, operation 
from br_tbl_1_audit 
where br_id = @br_id_input and audit_dtme > @audit_date_time 
order by id desc
open cur
fetch next from cur into @id, @br_id, @col_1, @col_2, @audit_dtme, @operation
while @@fetch_status = 0
begin
if (@operation = 'INSERT')
begin
delete from br_tbl_1 where br_id = @br_id;
end
else if (@operation = 'DELETE')
begin
set identity_insert br_tbl_1 on;
insert into br_tbl_1 (br_id, col_1, col_2)
values (@br_id, @col_1, @col_2)
set identity_insert br_tbl_1 off;
end
else
begin
;with cte
as 
(
select top 1 * from br_tbl_1_audit 
where br_id = @br_id and audit_dtme < @audit_dtme
order by id desc
)
update tb1
set tb1.col_1 = cte.col_1,
tb1.col_2 = cte.col_2
from br_tbl_1 tb1
join cte on cte.br_id = tb1.br_id
end
delete from br_tbl_1_audit where id = @id;
fetch next from cur into @id, @br_id, @col_1, @col_2, @audit_dtme, @operation
end
close cur
deallocate cur

要在外键表中删除，您必须在主游标中添加另一个游标，它将根据主键表的行在外键表中插入/更新/删除。

虽然游标可能不是最好的解决方案，因为如果有太多的表或数据行要恢复，它可能会很慢。