sqlite-chronicle

使用触发器跟踪SQLite表中行何时被更新或删除

安装

pip install sqlite-chronicle

enable_chronicle(conn, table_name)

此模块提供了一个函数：sqlite_chronicle.enable_chronicle(conn, table_name)，它执行以下操作：

1. 检查是否存在名为_chronicle_{table_name}的表。如果存在，则不执行任何操作。否则...
2. 创建该表，包含与原始表相同的主键列，以及整数列added_ms、updated_ms、version和deleted
3. 在编年史表中为原始表中的每一行创建一个新行，将added_ms和updated_ms设置为当前时间戳（毫秒），并将version列设置为1，对于后续的每一行递增
4. 在表上设置三个触发器

• 一个after insert触发器，在编年史表中创建一个新行，将added_ms和updated_ms设置为当前时间，并递增version
• 一个after update触发器，更新updated_ms时间戳，如果主键已更改（可能非常罕见），则更新任何主键，并递增version
• 一个after delete触发器，更新updated_ms、递增version并在deleted列中放置一个1

如果表没有单一或复合主键，则该函数将引发sqlite_chronicle.ChronicleError异常。

请注意，表的version是一个全局递增的数字，所以每次设置时，它都将设置为该表的当前max(version) + 1。

最终结果是类似于以下的编年史表

updates_since(conn, table_name, since=None, batch_size=1000)

sqlite_chronicle.updates_since()函数返回一个生成器，该生成器遍历Change对象列表。

这些对象代表自since版本号以来，或者如果未提供since，则自时间开始以来表中行发生的变化。

• conn是一个SQLite连接对象
• table_name是包含要获取变化的表名称的字符串
• since是一个可选的整数版本号 - 如果未提供，将返回所有变化
• batch_size是内部细节，控制一次从数据库返回的行数。您不需要更改此设置，因为函数实现了自己的内部分页。

从生成器返回的每个Change看起来像这样

Change(
    pks=(5,),
    added_ms=1701836971223,
    updated_ms=1701836971223,
    version=5,
    row={'id': 5, 'name': 'Simon'},
    deleted=0
)

Change是一个具有以下属性的数据类

• pks是行主键值的元组 - 对于普通主键，这将是单个项目的元组，对于复合主键，则包含多个项目
• added_ms是行添加时的毫秒时间戳
• updated_ms是行最后更新时的毫秒时间戳
• version是行的版本号 - 您可以使用此作为since值来获取自该点以来的变化
• row是一个字典，包含行的当前值 - 如果行已删除（除主键外），则这些值将是None
• deleted如果行未被删除，则值为0，如果已删除，则值为1

每次调用此函数时，您都应该跟踪最后看到的version号码，以便您可以在未来的调用中将它作为since值传递，以获取自该点以来发生的变化。

请注意，如果行在调用此函数之间有多个更新，则您仍将只看到该行的Change对象 - updated_ms和version将反映最近的更新。

潜在应用

编年史表可以用于高效地回答“自从我上次检查以来，哪些行被插入、更新或删除了”的问题 - 通过查看具有索引的version列，可以快速回答该问题。

这有无数潜在的应用，包括

• 同步和复制：其他数据库可以“订阅”表，跟踪它们上次刷新副本的时间，并请求自上次以来更改的行 - 并删除已标记为删除的行。
• 索引：如果您需要更新Elasticsearch索引或向量数据库嵌入索引等，您可以对自上次运行以来更改的记录运行 - 参见非规范化查询引擎设计模式
• 丰富：datasette-enrichments需要持久化一些表示“每个地址列都应该进行地理编码”的内容 - 然后有一个丰富功能，每X秒运行一次，寻找新插入或更新的行并仅丰富这些行。
• 向人们展示自上次访问以来发生了什么变化 - “自昨天以来，有52行被更新，16行被删除”之类的信息。