如何版本控制数据库中的记录

假设我在数据库中有一个记录，管理员和普通用户都可以进行更新。

谁能建议一个好的方法/架构来控制这个表中的每个更改，这样就有可能将记录回滚到以前的版本?

91575 次浏览
小开

我认为您正在寻找对数据库记录的内容进行版本控制(当有人编辑问题/答案时，StackOverflow会这样做)。一个好的起点可能是查看一些使用修订跟踪的数据库模型。

我能想到的最好的例子是MediaWiki，维基百科引擎。比较数据库图在这里，特别是修改表. c。

根据您使用的技术，您必须找到一些好的差异/合并算法。

检查这个问题是否用于。net。

小开

在BI世界中，您可以通过向想要版本的表中添加startDate和endDate来实现这一点。当您将第一条记录插入到表中时，startDate被填充，但endDate为空。当您插入第二条记录时，您还可以用第二条记录的startDate更新第一条记录的endDate。

当您想要查看当前记录时，您可以选择endDate为空的记录。

这有时被称为类型2 慢慢改变维度。 另见TupleVersioning

小开
最佳答案

假设你有一个管理员和用户可以更新的FOO表。大多数情况下，您可以针对FOO表编写查询。快乐的日子。

然后，创建一个FOO_HISTORY表。它包含了FOO表的所有列。主键与FOO加上一个RevisionNumber列相同。有一个从FOO_HISTORY到FOO的外键。您还可以添加与修订相关的列，例如UserId和RevisionDate。在所有*_HISTORY表中以不断增加的方式填充RevisionNumbers(即从Oracle序列或等效的表中)。不要依赖于在一秒钟内只有一次更改(即不要将RevisionDate放入主键中)。

现在，每次更新FOO时，就在更新之前将旧值插入到FOO_HISTORY中。您可以在设计的某个基本级别上执行此操作，以便程序员不会意外地错过此步骤。

如果你想从FOO中删除一行，你有一些选择。要么级联并删除所有历史，要么通过将FOO标记为已删除来执行逻辑删除。

当您主要对当前值感兴趣，而只是偶尔对历史感兴趣时，此解决方案很好。如果你总是需要历史记录，那么你可以设置有效的开始和结束日期，并将所有记录保存在FOO本身中。然后，每个查询都需要检查这些日期。

小开

你没说什么数据库，我也没在帖子标签里看到。如果是Oracle，我可以推荐在Designer中内置的方法:使用期刊表。如果它适用于任何其他数据库，嗯，我基本上也推荐同样的方法……

它的工作方式,如果您想复制它在另一个数据库,或者如果你只是想了解它,是创建一个表有一个影子表也只是一个普通的数据库表,字段相同的规格,加上一些额外的字段:像上次采取行动(字符串,典型值“INS”插入、更新《乌利希期刊指南”和“▽”删除),datetime动作发生时,用户id是谁干的。

通过触发器，对表中任意行的每一个操作会在日志表中插入一个新行，其中包含新值、执行了什么操作、何时执行以及由哪个用户执行。你从来没有删除过任何行(至少在过去几个月没有)。是的，它会变得很大，很容易有数百万行，但你可以很容易地跟踪任何记录在任何时间点的值，因为日志开始或旧的日志行最后一次被清除，以及谁做了最后一次更改。

在Oracle中，你所需要的一切都是自动生成的SQL代码，你所要做的就是编译/运行它;它带有一个基本的CRUD应用程序(实际上只有“R”)来检查它。

小开

两个选择:

  1. 有一个历史表——每当原始数据更新时，将旧数据插入到这个历史表中。
  2. 审计表-存储之前和之后的值-仅用于审计表中修改的列以及其他信息，如谁更新和何时更新。
小开

您可以通过SQL触发器对SQL表执行审计。从触发器可以访问2个特殊表(插入和删除)。这些表包含每次更新表时插入或删除的确切行。在触发器SQL中，可以将这些修改过的行插入到审计表中。这种方法意味着你的审核对程序员来说是透明的;不需要他们付出任何努力，也不需要任何实现知识。

这种方法的额外好处是，无论sql操作是通过数据访问dll发生的，还是通过手动sql查询发生的，都将进行审计;(因为审计是在服务器本身上执行的)。

小开

升级到SQL 2008。

尝试使用SQL更改跟踪，在SQL 2008。您可以使用这个新特性跟踪数据库中数据的更改，而不是使用时间戳和墓碑列。

MSDN SQL 2008 Change Tracking

小开

我也在做同样的事情。我正在为教案做一个数据库。这些计划需要原子变更版本的灵活性。换句话说，对教案的每一次修改，无论多么小，都需要被允许，但旧版本也需要保持完整。这样，课程创建者可以在学生使用课程计划时编辑课程计划。

它的工作方式是，一旦学生完成了一节课，他们的成绩就会附在他们完成的版本上。如果做了更改，他们的结果将始终指向他们的版本。

这样，如果一个教训标准被删除或移动，它们的结果不会改变。

我目前这样做的方式是在一个表中处理所有数据。通常我只会有一个id字段，但在这个系统中，我使用了一个id和一个子_id。通过更新和删除，sub_id始终与行保持一致。id是自动递增的。课程计划软件将链接到最新的子_id。学生成绩将链接到id。我还包含了一个时间戳，用于跟踪发生更改的时间，但这对于处理版本控制并不是必要的。

有一件事我可能会改变，一旦我测试了它，我可能会使用前面提到的endDate null思想。在我的系统中，为了找到最新的版本，我必须找到max(id)。另一个系统只查找endDate = null。不确定是否有另一个日期字段的好处。

我的意见。

小开

而@WW。另一种方法是创建一个版本列，并将所有版本保存在同一个表中。

对于单表方法你要么:

  • 使用标志来指示最新的ala < >强词按< / >强
  • 或执行一个大于outer join版本的nasty操作。

使用修订号的outer join方法的示例SQL如下:

SELECT tc.*
FROM text_content tc
LEFT OUTER JOIN text_content mc ON tc.path = mc.path
AND mc.revision > tc.revision
WHERE mc.revision is NULL
AND tc.path = '/stuff' -- path in this case is our natural id.

坏消息是上面需要outer join，外部连接可能很慢。好消息是，创建新条目理论上更便宜，因为你可以在一个没有事务的写操作中完成(假设你的数据库是原子的)。

对'/stuff'进行新修订的示例可能是:

INSERT INTO text_content (id, path, data, revision, revision_comment, enabled, create_time, update_time)
(
SELECT
(md5(random()::text)) -- {id}
, tc.path
, 'NEW' -- {data}
, (tc.revision + 1)
, 'UPDATE' -- {comment}
, 't' -- {enabled}
, tc.create_time
, now()
FROM text_content tc
LEFT OUTER JOIN text_content mc ON tc.path = mc.path
AND mc.revision > tc.revision
WHERE mc.revision is NULL
AND tc.path = '/stuff' -- {path}
)

我们使用旧数据进行插入。如果您只想更新一个列并避免乐观锁定和或事务，这尤其有用。

标记方法和历史表方法需要插入/更新两个行。

outer join修订号方法的另一个优点是，以后你总是可以用触发器重构为多表方法，因为触发器本质上应该做类似于上面的事情。

小开

只是想补充一下，这个问题的一个很好的解决方案是使用时态数据库。许多数据库供应商提供这个特性，要么开箱即用，要么通过扩展。我已经成功地使用了PostgreSQL的时态表扩展，但其他人也有它。每当您更新数据库中的记录时，数据库也会保留该记录的先前版本。

小开

Alok在上面建议Audit table，我想在我的帖子中解释它。

我在我的项目中采用了这种无模式的单表设计。

模式:

  • id -整数自动递增
  • 用户名-字符串
  • tablename - STRING
  • oldvalue -文本/ JSON
  • newvalue -文本/ JSON
  • createdon - DATETIME

这个表可以同时保存每个表的历史记录，在一条记录中包含完整的对象历史。该表可以使用触发器/钩子填充数据更改，存储目标行的旧值和新值快照。

这种设计的优点:

  • 更少的表管理的历史管理。
  • 存储每行新旧状态的完整快照。
  • 易于在每个表上搜索。
  • 可以按表创建分区。
  • 可以为每个表定义数据保留策略。

这种设计的缺点:

  • 如果系统有频繁的变化，数据量可能会很大。
小开

作为上述答案的附加步骤，我建议为每个生成的变更提供一个唯一的ID，可能包含日期/时间和每天的唯一计数器(这样一秒钟内多个更新就不会重叠)。我会在这个代码中包含一个动作类型代码，因此“9129128213939replace”。这提供了一种健壮性，允许检查其他历史记录系统是否正常工作。


提交答案