在sqlite数据库中保留日志表? [英] keeping a log table in sqlite database?

问题描述

我正在寻找一种方法来设置(通过 SQL)一个日志表，其中包含对我的 sqlite 数据库所做的一切(最好是在向数据库发出的插入、创建表等语句方面).我确信有办法通过在每个表上设置触发器来做到这一点，但这只是太多的工作，如果我稍后更改数据库的架构，这不是好兆头.是否有适用于数据库的包罗万象的全局事物(例如数据库本身的触发器)?

I'm looking for a way to set up (via SQL) a log table containing everything that had been done to my sqlite database (preferably in terms of the insert, create table etc. statements as issued to the database). I'm sure there are way to do it via setting trigger on each table, but that is just WAY too much work and does not bode well if I change the database's schema later. Is there a catch-all global thing that work on the database (like trigger on database itself)?

对于保留对 sqlite 数据库所做更改的记录的其他建议，我也愿意接受其他建议，以便我可以在几个月后回顾这些更改.

I'm also open for other suggestions for keeping records of changes done to sqlite database so that I can look back months later on the changes.

(当然有编程方式，但我不能确定我的程序是唯一写入数据库的程序).

(Programatically of course there are ways but I can't be sure that my program is the only program writing to the database).

推荐答案

虽然以下内容不能满足您的所有要求，但您可能希望看到一种方法.Mike Chirico 的 SQLite 教程 有一节关于记录所有插入、更新和删除b> 模仿 MySQL 的 binlog 的功能.

Though the following doesn't meet all your requirements, you may wish to see one way to do it. Mike Chirico's SQLite Tutorial has a section on Logging All Inserts, Updates, and Deletes that mimics the functionality of MySQL's binlog.

它依赖于触发器，这些触发器必须与您希望跟踪其更改的每个表的架构相匹配.也就是说，如果您的表有一个名为a"的字段，那么日志表需要跟踪aOLD"和aNEW".通过这种方式，触发器能够记录对该特定表中的字段所做的更新、插入和删除.

It relies on triggers that must match the schema for each table whose changes you wish to track. That is, if your table has a field called "a", then the logging table needs to keep track of "aOLD" and "aNEW". In this way, the trigger is able to record updates, inserts, and deletes made to the fields in that particular table.

这篇关于在sqlite数据库中保留日志表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！