代码之家 › 专栏 › 技术社区 › flukus

查询以获取对象图的所有修订

union orm sql-server sql

flukus · 技术社区 · 16 年前

我正在数据库上实现一个审计日志,因此所有内容都有CreatedAt和RemovedAt列。现在,我希望能够列出对象图的所有修订,但我能想到的最好方法是使用联合。我需要获取每个唯一的CreatedAt和RemovedAt id。

如果我得到一份有省份的国家名单,联盟看起来像这样:

SELECT c.CreatedAt AS RevisionId from Countries as c where localId=@Country
UNION
SELECT p.CreatedAt AS RevisionId from Provinces as p 
INNER JOIN Countries as c ON p.CountryId=c.LocalId AND c.LocalId = @Country
UNION
SELECT c.RemovedAt AS RevisionId from Countries as c where localId=@Country
UNION
SELECT p.RemovedAt AS RevisionId from Provinces as p 
INNER JOIN Countries as c ON p.CountryId=c.LocalId AND c.LocalId = @Country

对于更复杂的查询,这可能会变得非常复杂,并且可能表现得非常糟糕,所以我想看看是否有人能想出更好的方法。这位于MSSQL服务器中。

我需要将它们全部放在一个列表中,因为这是在from子句中使用的,真正的数据来自于加入这个子句。

3 回复 | 直到 16 年前

Borzio 16 年前

您很可能已经实施了您的解决方案,但要解决一些问题;我建议考虑Aleris的解决方案,或其衍生方案。

在你的表中,你有一个“removed at”字段——好吧,如果该字段是活动的(填充的),从技术上讲,数据不应该在那里——或者你的实现已经将其标记为删除,一旦它被删除,就会中断日志记录是远离的。

当您在报告期间有多个更新时会发生什么?之前的日志条目将被覆盖。

使用单独的日志可以存档日志信息,并允许您设置与更新/编辑周期不同的日志分析周期。

添加所需的任何“链接”字段,使您能够返回原始源数据或使描述足够详细。

日志中包含的字段由您决定,但Aleris的解决方案是直接的。我可以创建一个操作表,并将字段类型从varchar更改为int,作为操作表的链接,迫使开发人员执行一些标准化的操作。

希望它能有所帮助。

Aleris 16 年前

另一种方法是创建一个可能如下所示的审计日志:

AuditLog table
    EntityName varchar(2000),
    Action varchar(255),
    EntityId int,
    OccuranceDate datetime

其中EntityName是表的名称(例如:Contries、Province),Action是审核操作(例如:Created、Removed等),EntityId是原始表中修改行的主键。

该表需要在对表执行的每个操作上保持同步。有几种方法可以做到这一点:

1) 在每个表上设置触发器,将行添加到AuditTable
2) 每次对重复表进行更改时,在应用程序的AuditTable中添加行

使用此解决方案在审计中获取日志列表非常简单。

如果你需要从原始表中获取列,也可以使用这样的连接:

select *
from 
    Contries C 
    join AuditLog L on C.Id = L.EntityId and EntityName = 'Contries'

Tom H zenazn 16 年前

你可能可以通过交叉连接和合并来实现,但从性能的角度来看,联合可能更好。你可以尝试测试每一个。

SELECT
     COALESCE(C.CreatedAt, P.CreatedAt)
FROM
     dbo.Countries C
FULL OUTER JOIN dbo.Provinces P ON
     1 = 0
WHERE
     C.LocalID = @Country OR
     P.LocalID = @Country