SQL Server 的记录版本控制

• 下载演示项目和源代码 - 1.73 KB

引言

维护 SQL Server 数据的版本历史记录有许多好处，其中最重要的三点是：

• 撤销历史记录
• 审计
• 软删除

记录版本控制会在 CRUD 操作之上增加一个层，使其更加复杂，尤其是在更新和删除时。您不能再简单地删除记录。相反，您必须将其标记为已删除（“软删除”）。您不能再简单地更新记录；相反，您必须执行软删除，然后进行插入。

报表也是一个挑战。大多数报表框架不理解版本化数据的概念。它们期望每条记录都是一个独立的数据项，而不是它已经见过的某个数据项的“版本”。

没有版本控制解决方案可以规避版本化记录的基本挑战，但我们可以极大地改进传统的审计数据方法。

典型解决方案

记录版本控制通常是通过创建独立的审计表来完成的，这些表镜像主表的架构。

这看起来对吗？对我来说，它有几个糟糕的迹象。

• 我不喜欢架构重复。它增加了表的混乱，使维护更加困难，并且总体上使新开发人员更难理解。我认为我们可以做得更好。
• 感觉不安全。移动数据的基本原则涉及从旧的目标位置删除。我认为我们可以做得更好。
• 它与 ORM 不兼容。如果您使用 ORM 工具或使用业务对象处理审计跟踪，您将被迫将每个字段显式地从旧业务对象复制到新的“审计”业务对象。同样，我认为我们可以做得更好。
• 它不具扩展性。如果我想添加一个 BlogComment 表，我就必须添加另一个审计表。现在，我们不仅有架构重复，而且还有重复的审计抽象，这些抽象会随着时间的推移而产生分歧。想象一下几年后，BlogComment_Archive 表的行为与 Blog_Archive 表不同。这使得理解架构更加困难。
• 我不想针对这个架构编写报表。当我想要历史数据钻取时，我必须指向单独的表，这似乎是不必要的。

更好的计划

事实证明，我们确实可以做得更好！通过使用一些巧妙的实体继承，我们可以一次性解决数据库中所有表的审计问题，而不是一次只解决一个表。

该解决方案涉及一个基础审计表，所有可审计表都继承自该表。研究以下图表，然后查看下面的注释。

• Audit 表包含所有版本信息。日期戳、活动状态、更新者。您的审计需求可能包括此处的所有其他字段。
• Audit 表包含一个 PermanentRecordId。因为当您插入新版本时 Blog 记录的主键 (PK) 会发生变化，所以我们需要一个永久 ID 来标识单个博客条目并对同一个博客条目的版本进行分组。
• Blog 表在主键上共享 1:1 关系。Audit.Id 是 PK，Blog.Id 是 FK。请注意，Blog.Id **不是**自增列。
• Blog 表在没有所有审计垃圾干扰的情况下看起来更加整洁。当您查看 Blog 表时，您可以立即理解其用途。同样，Audit 表也一目了然。

CRUD 操作

让我们尝试几个 CRUD 操作，看看这种新方法感觉如何。

创建 (插入) 和读取 (选择)

实体继承通常需要额外的两次插入，因为您必须为一个完整的“记录”插入到多个表中。

在这种情况下，插入现在涉及两个操作。首先，您必须将一条记录插入到基础 Audit 表中。然后，您必须获取该插入记录的 PK ID 以便用于对 Blog 表进行第二次插入。

每次操作进行多次插入是实体继承策略的一个缺点，但它可以被封装。例如，上面的插入示例可以转换为一个存储过程，该存储过程接受 Blog 表的值以及 Audit.Updated_By 的值。

/* Setup */
delete from blog
delete from comment
delete from audit

declare @id bigint
/* ------- */

/* Blog insert */
insert into Audit(Updated_By) values ('Ben')
select @id = @@identity
insert into Blog (Id, UserId, Title, Body) values ( @id, 1, 'My first entry', 'My blog body')
select * from Blog join Audit on Blog.Id = Audit.Id where IsActive=1

正如您所见，Audit 表立即发挥了作用并完成了其工作。我们有这个博客条目的 PermanentRecordId，并且所有其他信息都完好无损。另请注意，ID 列已同步，并且记录被标记为活动状态。

虽然使用此策略选择任何内容确实必须执行带有 WHERE 子句的 INNER JOIN，但该操作的成本并不高，因为它是在索引字段上执行的。

更新

在版本化记录中，更新实际上是软删除后进行插入。

软删除直接针对审计表执行。这是一个很好的特性：要执行软删除，您甚至不需要知道记录类型。这意味着您可以创建一个单独的存储过程 `spSoftDelete(id)`，它接受要软删除的记录的 ID。

/* Blog update */
declare @BlogRecordId uniqueidentifier
select @BlogRecordId=PermanentRecordId from Audit where Id = @id
update Audit set IsActive=0 where Id=@id
insert into Audit(Updated_By, PermanentRecordId) values ('Ben', @BlogRecordId)
insert into Blog (Id, UserId, Title, Body) 
   values ( @@identity, 1, 'My updated first entry', 'My blog body is different now')
select * from Blog join Audit on Blog.Id = Audit.Id

现在我们有两个具有相同 PermanentRecordId 的条目。这意味着它们是同一个逻辑记录的不同版本。但请注意，旧记录不再处于活动状态。我们可以选择 MAX(Created)，但活动标志更快，并且正如您将看到的，对于撤销操作是必需的。

删除

删除会停用记录的所有版本。这可以仅在 Audit 表上执行，从而易于封装。

/* Blog delete */
update Audit set IsActive=0 where PermanentRecordId=@BlogRecordId
select * from Blog join Audit on Blog.Id = Audit.Id

或者，如果您拥有准确的 Blog.Id 或 Audit.Id，您可以通过这种方式设置 IsActive。只要您所有的更新操作都正确完成，应该只有一条记录 IsActive=1。您甚至可以设置一个约束来检查这一点。

CRUD 扩展

版本控制为撤销/重做等扩展操作提供了可能性。要使用这些 CRUD 扩展，请添加另外两个版本，总共四个博客条目。

/* Setup for CRUD extensions */
insert into Audit(Updated_By, PermanentRecordId) values ('Ben', @BlogRecordId)
insert into Blog (Id, UserId, Title, Body) 
   values ( @@identity, 1, 'My 3rd updated first entry', 'My blog body is different now')
update Audit set IsActive=0 where PermanentRecordId=@BlogRecordId
insert into Audit(Updated_By, PermanentRecordId) values ('Ben', @BlogRecordId)
insert into Blog (Id, UserId, Title, Body) 
   values ( @@identity, 1, 'My 4th updated first entry', 'My blog body is different now')
/* ------------------------- */

撤销/回滚/恢复

撤销操作涉及几个步骤：

1. 找到活动版本之前的一个版本（如果存在）。
2. 软删除活动版本。
3. 激活您在步骤 1 中找到的版本。

在下面的示例中，我们通过依赖主键的顺序来查找活动版本之前的一个版本。版本 #3 总是具有比同一记录的版本 #4 小的 PK ID。有些程序员不喜欢使用索引主键来确定时间顺序。如果您是其中之一，您可能需要考虑使用另一个索引列来维护时间顺序。（注意：SQL Server 的“timestamp”数据类型将不起作用，因为记录在它们的活动状态更改时会被更新，这会更改 timestamp 值。）

/* Rollback/undo to previous (to 3rd) */
select top 1 @id=Id from Audit where PermanentRecordId=@BlogRecordId
    and Id < (select Id from Audit where 
    PermanentRecordId=@BlogRecordId and IsActive=1)  order by Id desc
update Audit set IsActive=0 where PermanentRecordId=@BlogRecordId
update Audit set IsActive=1 where Id = @id
select * from Blog join Audit on Blog.Id = Audit.Id

/* Rollback/undo to previous (to 2nd) */
select top 1 @id=Id from Audit where PermanentRecordId=@BlogRecordId
    and Id < (select Id from Audit where 
    PermanentRecordId=@BlogRecordId and IsActive=1)  order by Id desc
update Audit set IsActive=0 where PermanentRecordId=@BlogRecordId
update Audit set IsActive=1 where Id = @id
select * from Blog join Audit on Blog.Id = Audit.Id

/* Rollback/undo to previous (to 1st) */
select top 1 @id=Id from Audit where PermanentRecordId=@BlogRecordId
    and Id < (select Id from Audit where 
    PermanentRecordId=@BlogRecordId and IsActive=1)  order by Id desc
update Audit set IsActive=0 where PermanentRecordId=@BlogRecordId
update Audit set IsActive=1 where Id = @id
select * from Blog join Audit on Blog.Id = Audit.Id

/* Rollback/undo to previous (still on 1st) */
select top 1 @id=Id from Audit where PermanentRecordId=@BlogRecordId
    and Id < (select Id from Audit where 
    PermanentRecordId=@BlogRecordId and IsActive=1)  order by Id desc
update Audit set IsActive=0 where PermanentRecordId=@BlogRecordId
update Audit set IsActive=1 where Id = @id
select * from Blog join Audit on Blog.Id = Audit.Id

重做

重做是撤销操作的简单反转。找到活动版本之后的第一个版本，并将其激活。

/* Redo (to 2nd) */
select top 1 @id=Id from Audit where PermanentRecordId=@BlogRecordId
     and Id > (select Id from Audit where 
     PermanentRecordId=@BlogRecordId and IsActive=1)  order by Id asc
update Audit set IsActive=0 where PermanentRecordId=@BlogRecordId
update Audit set IsActive=1 where Id = @id
select * from Blog join Audit on Blog.Id = Audit.Id

添加新的可审计表

在我们的版本控制系统下添加新表很容易，而且您已经看到的 CRUD 代码很容易适应，特别是如果您选择将其封装在存储过程中。

让我们添加一个表来支持可版本化、嵌套的博客评论，以演示新表的 CRUD 有多么相似。另请注意，当添加新表时，上面演示的 CRUD 根本不需要更改。太棒了。

起初，支持来自多个表的多条记录听起来非常困难，但几乎不需要额外的努力就能实现。

• Comment.Id 是 Audit.Id 的 FK，就像 Blog.Id 一样。
• Comment.PermanentBlogId 将存储博客条目的 PermanentId。您可以直接使用 FK 到 Blog.Id，但缺点是当 Blog 记录的活动版本更改时，您必须更新所有 FK 引用。这样，您就放弃了一点引用完整性（如果需要，您可以使用约束将其添加回来），但通过解耦的修订更改获得了简单性。
• Comment.ParentId 指向 Comment.Id，以允许嵌套评论。

所以这变得很有趣！现在，评论可以像博客文章一样拥有版本，但没有任何内容会丢失。让我们再次检查我们的 CRUD，以确保一切正常。

创建 (插入)

这里唯一的区别是我们需要引用 PermanentBlogId。这之所以是必需的，仅仅是因为评论由博客拥有。幸运的是，我们在之前的演示脚本中已经有了。如果您有 Blog.Id，您可以使用它来获取博客条目的 PermanentId。

/* Comment insert */
insert into Audit(Updated_By) values ('Commenter')
select @id = @@identity
insert into Comment (Id, PermanentBlogId, CommentingUserId, Comment) 
   values ( @id, @BlogRecordId, 2, 'My first comment')
select * from Comment join Audit on Comment.Id = Audit.Id where IsActive=1

如果您研究一下，您会发现此评论版本具有不同的 PermanentRecordId。这是正确的，因为这是一组不同的版本化数据。每条评论都会获得自己的 PermanentId。

更新

除了插入的值不同外，更新是相同的。

/* Comment update */
declare @CommentRecordId uniqueidentifier
select @CommentRecordId=PermanentRecordId from Audit where Id = @id
update Audit set IsActive=0 where Id=@id
insert into Audit(Updated_By, PermanentRecordId) values ('Commenter', @CommentRecordId)
insert into Comment (Id, PermanentBlogId, CommentingUserId, Comment) 
   values ( @@identity, @BlogRecordId, 1, 'My updated first comment')
select * from Comment join Audit on Comment.Id = Audit.Id

选择某个博客条目的所有评论

好的，您现在已经确信版本控制有效。您可以看到回滚和恢复操作将在与每条评论关联的 PermanentId 值上执行，并且您可以看到每条评论都有自己的 PermanentId。想象博客文章和评论都有版本，这让人感到困惑！

那么，如何选择博客当前版本的所有评论呢？很简单。

/* Insert 2nd comment */
insert into Audit(Updated_By) values ('Commenter #2')
select @id = @@identity
insert into Comment (Id, PermanentBlogId, CommentingUserId, Comment) 
   values ( @id, @BlogRecordId, 2, 'I have a comment too')
select * from Comment join Audit on Comment.Id = Audit.Id where IsActive=1

/* Insert 3rd nested comment */
declare @parentId bigint
select @parentId = @id
insert into Audit(Updated_By) values ('Commenter #3')
select @id = @@identity
insert into Comment (Id, PermanentBlogId, CommentingUserId, Comment, ParentId) 
   values ( @id, @BlogRecordId, 3, 'I have a comment about the comment', @parentId)
select * from Comment join Audit on Comment.Id = Audit.Id where IsActive=1

/* Select all comments for this blog entry */
select * from Comment join Audit on Comment.Id = Audit.Id where IsActive=1 and
   PermanentBlogId = @BlogRecordId order by Created asc

进一步研究

如果您对此方法感兴趣，我建议您研究以下高级主题：

• 将记录版本控制与您最喜欢的 ORM 工具一起使用
• 将记录版本控制与代码生成的 DAL 一起使用
• 实体/表继承
• 分层版本（例如，如果您想让博客回滚也让评论回滚）
• 为报表编写视图
• 封装和抽象插入/更新操作