如何在关系数据库中保留大型字符串字段的编辑历史记录

Question

N.B. 我认为答案可能是以设计为中心的，因此基本上与实现无关，但如果有一些特别适合使用这些技术的解决方案，我会将 Java+Hibernate 与 Postgres 一起使用。

我有一个带有特定字段的表，该字段将包含大字符串，假设博客文章平均为 +10000 个字符。

在我的应用程序中，您可以根据需要多次编辑博客文章，最新版本将在更新后始终立即显示。但是，应用需要保留这些编辑的完整版本历史记录，以便可以查看它们。

一个

明显的策略是保留一个单独的表，类似于 blog_post_history ，其中博客文章行在创建时和每次后续更新到主"实时"blog_post表时重复插入，版本号递增，因此这些版本在将来需要时都可用。我正在考虑使用类似Hibernate Envers的东西来设置它。

然而，存储(以及 - 也许更重要的是 - 传输(10000个字符的文本块的多个版本似乎非常低效，其中每个版本之间的唯一区别可能是修复拼写错误，添加几个单词等。 由于对博客文章的编辑的性质，可能会有许多像这样的小增量更改， 而不是更少，更大的变化。

有没有更好的方法？

我正在考虑在进行编辑时仅存储当前版本和先前版本之间的增量，然后在请求时以编程方式从这些增量重建版本历史记录，也许在客户端上，以便通过网络发送的数据最小化。

我很可能会将最新版本存储为全文，因为我想针对最频繁的请求进行优化，然后存储从当前版本向后追溯的增量链，以便在请求时重建历史版本。

Answer 1

我现在正在研究的解决方案，到目前为止运行良好，实现了我在问题中提出的设计

我正在考虑在进行编辑时仅存储当前版本和先前版本之间的增量，然后在请求时以编程方式从这些增量重建版本历史记录，也许在客户端上，以便通过网络发送的数据最小化。

我很可能会将最新版本存储为全文，因为我想针对最频繁的请求进行优化，然后存储从当前版本向后追溯的增量链，以便在请求时重建历史版本。

我将在这里分享我的实现细节

为了创建增量并使用 重建全文，我正在使用梦幻般的谷歌差异匹配补丁库。您可以阅读与实现无关的 API 文档，以更好地理解下面的代码示例，尽管无论如何它都非常可读。

google-diff-match-patch具有Java和JS实现，因此我可以使用它来计算服务器上Java的增量。我选择将每个增量转换为字符串，以便可以轻松地将其存储在数据库中，并易于被客户端上的JS库使用。更多内容见下文。

public String getBackwardsDelta(String editedBlogPost, String existingBlogPost) {
    diff_match_patch dmp = new diff_match_patch();
    LinkedList<diff_match_patch.Patch> patches = 
        dmp.patch_make(editedBlogPost, existingBlogPost);
    return dmp.patch_toText(patches);
}

注意：我花了一段时间才弄清楚如何使用maven拉下google-diff-match-patch的官方版本。它不在 maven 中央存储库中，而是在 googlecode.com 上自己的存储库中。请注意，有些人已经分叉了它并将他们的分叉版本放在 maven central 中，但如果你真的想要正式版本，你可以通过在pom.xml中添加存储库和依赖项来获得，如下所示

<repository>
  <id>google-diff-patch-match</id>
  <name>google-diff-patch-match</name>
  <url>https://google-diff-match-patch.googlecode.com/svn/trunk/maven/</url>
</repository>
<dependency>
  <groupId>diff_match_patch</groupId>
  <artifactId>diff_match_patch</artifactId>
  <version>current</version>
</dependency>

对于前端，我传递最新的博客文章全文，以及代表每次编辑的时间倒退的增量链，然后在 JS 浏览器中重建每个版本的全文。

为了获取库，我使用的是 npm + browserify。该库在 npm 上作为 diff-match-patch 提供。版本 1.0.0 是唯一的版本。

getTextFromDelta: function(originalText, delta) {
  var DMP = require('diff-match-patch'); // get the constructor function
  var dmp = new DMP();
  var patches = dmp.patch_fromText(delta);
  return dmp.patch_apply(patches, originalText)[0];
}

就是这样，它的效果非常好。

在存储博客文章的编辑方面，我只使用一个表BLOG_POST_EDITS其中存储博客文章 ID、编辑时间的时间戳(我后来使用它来正确排序编辑以在客户端上重建全文版本时创建链(，以及BLOG_POST表中当前实时博客文章之间的向后增量， 以及博客文章的传入编辑版本。

我选择存储增量的"链"，因为它非常适合我的用例，并且在服务器代码端更简单。这确实意味着为了重建 N 的版本 M，我必须向客户端发送一条 N-(M-1( 增量链，从实时博客文章全文返回到版本 M。但是在我的用例中，无论如何，我碰巧每次都想发送整个链，所以这很好。

为了稍微提高请求特定版本的在线效率，每次进行编辑时，可以将所有增量从新编辑的博客文章版本重新计算回每个(还原的(版本，但这意味着服务器上的工作量和复杂性更高。

Answer 2

即使在我看来，只有性能测试实际上可以回复哪种解决方案更好，我也不会回复存储差异或完整更改，因为完整的内容日志意味着更大的数据库但服务器的工作更少。

相反，我想分享我使用 postgresql 保持历史的经验。我在服务器站点上非常成功地工作，只是在postgresql上没有编写任何代码。在Postgresql上使用这组函数，触发器和扩展

http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html

它们简单易实现，您可以忘记代码上的历史记录，但只需从日志表中读取以显示内容的差异。

所以我的应用程序是用 php 编写的，带有 YII 框架，带有我为数据设计的数据库方案和结构，只有几个表作为框架本身的服务(用户、角色和一般日志(，这很重要，因为如果数据库中的数据结构太复杂，下面总结的方法仍然有效，但更复杂。

安装后，您可以在此处找到的 postgresql 扩展表日志http://pgfoundry.org/projects/tablelog/

您可以按以下方式进行：首先，您必须选择包含需要保留历史记录的内容的表 (mytable(。您将此 mytable 复制(我确实复制到新的模式 log.mytable 中(添加了一些新列来跟踪历史记录(如在表日志扩展中的自述文件中描述的那样(。

你必须在 pgplsql 中的 postgresl 上创建一些简单的函数

CREATE FUNCTION table_log ()
    RETURNS TRIGGER
    AS '$libdir/table_log' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT)
    RETURNS VARCHAR
    AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT)
    RETURNS VARCHAR
    AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR)
    RETURNS VARCHAR
    AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ)
    RETURNS VARCHAR
    AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';

现在，您必须在 mytable 上创建一个触发器，如下所示

在 MYTABLE 上更新或插入或删除后创建触发器mytable_trg 对于每一行，执行过程table_log('日志.MYTABLE'(;

就是这样，在每次插入，更新或删除时，您都会跟踪历史记录，并且您可以使用之前创建的函数轻松恢复旧版本，因此在您的应用程序代码中只需执行并SQL调用函数本身。

在我的应用程序中，我在需要的几个点上为历史记录添加了一个图标，然后单击后，我打开了一个对话框，其中包含表格和表格中的选项，以显示所有历史记录并选择您可以还原的版本。

在表单创建中，选择内容表单log.mytable，在我看来，您可以放置一个函数，该函数从所有版本中提取与当前版本的差异，但是如果您将每个版本的完整内容存储在数据库中，这很容易，因为相反，可能很难恢复接近最新版本的版本。事实上，如果你保持差异，考虑它们与下一个进行比较而不是与当前进行比较。

另一个优点是，一切都是服务器端，在客户端可以检测到写入额外数据的延迟。

仅呈现下面提到的差异的功能也可以是一个 pgplsql 函数，以避免以这种方式将完整内容中的所有版本发送到客户端，这些内容有时可能很大，但这必须取决于内容类型，对于 html 的文本较少，对于其他类型的内容来说更复杂。

我的应用程序非常复杂，但以这种方式保留更改历史记录已经变得简单干净，完成后我忘记了它，因为它总是运行顺利。

卢卡