c - 交易前后的sqlite差异

Question

我现在使用 sqldiff 工具来计算我的数据库在执行事务之前和执行之后的差异。我这样做如下:

1. 将整个 sqlite 文件复制到 tmp 位置 (old-database.sqlite)
2. 执行事务(例如插入、架构更改...)
3. 比sqldiff --primary-key old-database.sqlite database.sqlite

这会输出交易所做的更改。尽管这非常有效，因为我的数据库不是那么大，最多只有几兆字节。我认为这可以更有效地完成。

是否可以使用任何机制在不复制数据库的情况下获得相同的输出。也许与日记有关？

亲切的问候，大安

Answer 1

实现起来可能有点笨拙，而且可能在某些阶段(规模方面)效率低下，但您可以结合使用 TRIGGERS(每个受监控的表 4 个是笨拙的部分)和 sqlite_master .

也就是说，对于要监视的每个表，您可以为该表(结构明智)附加两列的副本。 1表示 Action (插入、更新前、更新后或删除)，第2个(可选)记录时间戳。

然后对于每个要监控的表都有AFTER INSERT, BEFORE DELETE and AFTER and BEFORE UPDATE TRIGGERS 复制添加一个条目到表的日志表中，在事务之前清空(如果需要，否则时间戳可能是必填列)。

对于模式再次更改，您可以之前有一个 sqlite_master 的副本，然后在事务之后将它与 sqlite_master 进行比较(您不能将 TRIGGERS 应用于系统表)。

例如，考虑以下示例/演示:-

CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, mycolumn TEXT);
CREATE TABLE IF NOT EXISTS mytablelog AS SELECT * FROM mytable WHERE 0 = 1;
ALTER TABLE mytablelog ADD COLUMN logtype INTEGER;
ALTER TABLE mytablelog ADD COLUMN timestamp INTEGER TEXT;

CREATE TABLE IF NOT EXISTS schema_log AS SELECT * FROM sqlite_master WHERE 0=1;
CREATE TABLE IF NOT EXISTS pretrans_schema AS SELECT * FROM sqlite_master WHERE 0=1;

ALTER TABLE schema_log ADD COLUMN logtype INTEGER;
ALTER TABLE schema_log ADD COLUMN timestamp INTEGER TEXT;
CREATE TRIGGER IF NOT EXISTS mytable_inserts AFTER INSERT ON mytable 
    BEGIN 
        INSERT INTO mytablelog SELECT *,0,strftime('%s','now') FROM mytable WHERE id = new.id;
    END
;
CREATE TRIGGER IF NOT EXISTS mytable_deletions BEFORE DELETE ON mytable
    BEGIN
        INSERT INTO mytablelog SELECT *,1,strftime('%s','now') FROM mytable WHERE id =  old.id; 
    END
;
CREATE TRIGGER IF NOT EXISTS mytable_preupdates BEFORE UPDATE ON mytable
    BEGIN
        INSERT INTO mytablelog SELECT *,2,strftime('%s','now') FROM mytable WHERE id = old.id;
    END
;
CREATE TRIGGER IF NOT EXISTS mytable_postupdates AFTER UPDATE ON mytable
    BEGIN 
        INSERT INTO mytablelog SELECT *,3,strftime('%s','now') FROM mytable WHERE id = new.id;
    END
;
-- SELECT * FROM sqlite_master WHERE name LIKE 'sqlite_%';


/* BEFORE TRANSACTION PREPATION */
DELETE FROM mytablelog;
DELETE FROM schema_log;
DELETE FROM pretrans_schema;
INSERT INTO pretrans_schema SELECT * FROM sqlite_master;

/* DO SOMETHING AKA THE TRANSACTIONS */
CREATE TABLE IF NOT EXISTS newtable (id INTEGER PRIMARY KEY, acolumn TEXT);
INSERT INTO mytable (mycolumn) VALUES ('Mary')
 -- ,('Fred'),('Jane'),('Anne'),('Alfred'),('George'),('Alan')
 ,('Susan'),('Betty'),('Catherine'),('John')
 ,(100),(200)
;
UPDATE mytable SET mycolumn = mycolumn||' has the detected letter.' WHERE mycolumn LIKE '%n%';
DELETE FROM mytable WHERE CAST(mycolumn AS INTEGER) > 0;


/* AFTER TRANSACTION */
SELECT rowid,'sm',* FROM sqlite_master UNION ALL SELECT rowid,'pt',* FROM pretrans_schema ORDER BY type,name; /* FOR DEMO/TESTING */ 
/* Get items added to the schema */
INSERT INTO schema_log SELECT *,4,strftime('%s','now') FROM sqlite_master WHERE name NOT IN (SELECT name FROM pretrans_schema);
/* Get items deleted from the schema */
INSERT INTO schema_log SELECT *,5,strftime('%s','now') FROM pretrans_schema WHERE name NOT IN (SELECT name FROM sqlite_master);
/* get original schema if schema updates */
INSERT INTO schema_log SELECT *,6,strftime('%s','now') FROM sqlite_master AS s 
    WHERE (sql <> (SELECT sql FROM pretrans_schema WHERE type = s.type AND name = s.name )) AND type IS NOT NULL /* AND NAME <> 'pretrans_schema' optional */
;
/* get current schema if schema updates */
INSERT INTO schema_log SELECT *,7,strftime('%s','now') FROM pretrans_schema AS s 
    WHERE (sql <> (SELECT sql FROM sqlite_master WHERE type = s.type AND name = s.name)) AND type IS NOT NULL /* AND NAME <> 'pretrans_schema' */
;
SELECT * FROM schema_log;
SELECT * FROM mytablelog;

结果

1 前后架构(部分)

• 注意突出显示的行，没有 pt 条目，表明该表已添加(没有 sm 条目则它会被删除)。

2 - 架构更改日志(schema_log 表)

• 4 表示模式中的新项目(5 个已删除，6 个在更新前，7 个在更新后)
• 因此表 newtable 已作为事务的一部分添加。

3 - mytable 事务；

• O 是插入，1 分别在更新前和更新后删除 2 和 3(成对)。