可能是我的标题没说清楚。 我正在寻找某种对数据库表的版本控制，就像 subversion 对文件所做的那样，就像 wiki 所做的那样。
我想跟踪更改日志。
我想提取并反向运行差异。 （像“svn merge -r 101:100”一样撤消）。
我可能需要对历史记录进行索引搜索。
我读过“撤消引擎的设计模式”，但它与“模式”。 有什么东西我可以重复使用而不需要重新发明轮子吗？
编辑：
例如，银行账户交易。 我在表中更新了“余额”列（和其他列）。 10天后，用户会发现自己犯了一个错误，他会想要取消/回滚特定交易，而不更改其他交易。
我怎样才能在应用程序级别优雅地做到这一点？
May be my title is not clear. I am looking for some kind of version control on database tables, like subversion does on files, like wiki does.
I want to trace the changes log.
I want to extract and run the diff in reverse. (undo like a "svn merge -r 101:100").
I may need a indexed search on the history.
I've read the "Design Pattern for Undo Engine", but it is related to "Patterns". Are there anything I could reuse without reinvent the wheel?
EDIT:
For example, bank account transactions. I have column "balance"(and others) updated in table. a user will find a mistake by him 10 days later, and he will want to cancel/rollback the specific transaction, without changing others.
How can I do it gracefully in the application level?
发布评论
评论（7）
Martin Fowler 在 随时间变化的事物的模式中介绍了该主题。 仍然是模式而不是实际的框架，但他展示了示例数据以及如何使用它。
Martin Fowler covers the topic in Patterns for things that change with time. Still patterns and not an actual framework but he shows example data and how to use it.
您可以对要跟踪的每条记录使用修订方法。 这将涉及在表中为记录的每次修订保留一行。 这些记录将通过共享的“ID”绑定在一起，并且可以在“修订状态”上查询（例如获取最新的“已批准”记录）。
在您的应用程序层中，您可以单独处理这些记录并回滚如果需要，只要记录所有必要的信息即可恢复到较早的状态。
You could use a revision approach for each record that you want to trace. This would involve retaining a row in your table for every revision of a record. The records would be tied together by a shared 'ID' and could be queried on the 'Revision Status' (e.g. Get the latest "Approved" record).
In your application tier, you can handle these records individually and roll back to an earlier state if needed, as long as you record all the necessary information.
迂腐点。 您的银行帐户示例不会通过审计员/监管机构。
帐户中的任何错误条目都应留在那里以备记录。 同等且相反的修正交易将应用于该账户。 实际上回滚了原始事务，但留下了原始错误及其更正的非常明显的痕迹。
Pedantic point. Your bank account example would not get past an auditor/regulator.
Any erroneous entries in an account should be left there for the record. An equal and opposite correction transaction would be applied to the account. In effect rolling back the original transaction but leaving a very obvious trace of the original error and its correction.
我会采用双时态数据库设计，这将为您提供执行和回滚所需的所有数据，无论这意味着插入更多行还是只是删除以后的修改。
这种数据库设计有相当多的微妙之处，但有一本关于该主题的非常好的书：
Richard T. Snodgrass 编写的《用 SQL 开发面向时间的数据库应用程序》，
可在此处下载：
http://www.cs.arizona.edu/people/rts/tdbbook.pdf
使用数据库事务将是这是一个坏主意，因为它将在数据库中创建的锁 - 基本上数据库事务应该尽可能短。
应用程序层中的任何内容，除非它本身具有某种持久性机制，否则都无法在应用程序重新启动后继续存在（尽管这可能不是必需的）。
I'd go with a bi-temporal database design, which would give you all the data required to perform and rollback, whether that means inserting more rows or simply deleting the later modifications.
There's a fair amount of subtlety to such a database design but there's are very good book on the subject:
Developing Time-oriented Database Applications in SQL by Richard T. Snodgrass
available for download here:
http://www.cs.arizona.edu/people/rts/tdbbook.pdf
Using a database transaction would be a bad idea because the locks it would create in the database - basically database transactions should be as short as possible.
Anything in the application layer, unless it has some persistence mechanism itself, won't survive application restarts (although that might not be a requirement).
根据您对 James Anderson 的评论，我会让用户界面在取消交易时写入新的插入内容。 它将在表中插入一条新记录，该记录与已取消的事务具有相同的值，只不过该值是负数而不是正数。 如果您有一个包含定义交易目的的内容的结构，我会说已取消以及正在取消的交易的记录号。
Based on your comment to James Anderson, I would have the user interface write a new insert when cancelling a transaction. It would insert a new record into the table that had the same values as the cancelled transaction except the value would be a negative number instead of a positive number. If you have a structure that includes something to define the purpose of the transaction, I would make it say cancelled and the record number of the transaction it was cancelling.
根据各种评论，您的问题的可能解决方案是制作一个“日期有效”表格。
基本上，您可以向每个表添加“有效期”和“有效期”列。
“当前”记录的 valid_to_date 应该始终为“2999-12-31”或某个任意高的值。
当值更改时，您将“有效期”更改为当前日期并插入
有效日期为今天且有效日期为“2999-12-31”的新行复制旧行中的所有列（如果未更改）。
您可以使用以下命令创建视图
“select all-columns- except-valid-xx-date from table where valid-to-date = '2999-12-31'”
这将允许您当前的所有查询保持不变。
这是数据仓库环境中非常常见的技术，对于诸如汇率之类的情况来说，有效日期很重要。
撤消逻辑应该是显而易见的。
Based on the various comments a possible solution for your problem would be to make a "date effective" table.
Basicly you add valid-from-date and valid-to-date columns to every table.
The "current" record should always have a valid_to_date of "2999-12-31" or some arbiteraly high value.
When a value changes you change the "valid-to-date" to the current date and insert a
new row with a valid-from-date of today and a valid-to-date of "2999-12-31" copy all the columns from the old row if they have not been changed.
You can create views with
"select all-columns-except-valid-xx-date from table where valid-to-date = '2999-12-31'"
Which will allow all your current queries to work unchanged.
This is a very common tecnique in data warehouse environments and for thing like exchange rates where the effective date is important.
The undo logic should be obvious.
尽管我在使用触发器和行版本之前已经设置了完整的撤消/审核历史记录，但我不知道具体的模式。
有几个 MS Sql 应用程序可让您浏览日志并查看实际更改。
我在 MS SQL 2000 中使用了一个名为 Log Navigator 的工具，它可以让我撤消特定的历史事务 - 但现在我找不到它了。
http://www.lumigent.com 和 http://www.apexsql.com 提供用于查看日志的工具，但我认为两者都不能让您回滚它们。
我认为最好的方法是在编写您的应用程序时牢记这一点 - 您已经在这里提供了一些关于如何做到这一点的好建议。
I'm not aware of a specific pattern, although I have set up full undo/audit histories before using triggers and rowversions.
There are a couple of apps for MS Sql that let you trawl through the logs and see the actual changes.
I've used one called Log Navigator back with MS SQL 2000 that used to let me undo a specific historical transaction - I can't find it now though.
http://www.lumigent.com and http://www.apexsql.com do tools for viewing the logs, but I don't think either lets you roll them back.
I think the best way to do this is to write your application with this in mind - which you have a couple of good suggestions here already on how to do.