代码之家 › 专栏 › 技术社区 › 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 a t”字段——好吧,如果该字段是活动的(填充的),那么从技术上讲,数据不应该在那里——或者您的实现已经将其标记为删除,一旦它将中断日志记录。是远离的。

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

单独的日志允许对日志信息进行存档,并允许您设置不同于更新/编辑周期的日志分析周期。

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

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

希望它有帮助。

Aleris 16 年前

另一种选择是创建一个如下所示的审计日志:

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

其中entityname是表的名称(例如:贡献、省),操作是审计操作(例如:创建、删除等),entityid是原始表中修改行的主键。

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

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

使用此解决方案很容易获得审核中的日志列表。

如果需要从原始表中获取列,也可以使用如下联接:

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

Tom H 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