sql - 在 SQLite 中仅使用 SQL 更新和记录更改的行

Question

我正在用 R 编写一个更新 SQLite 数据库的应用程序/脚本。

我的道歉 - 我没有这方面的经验。

我的表由 4 个字段Id, Name, LVL,组成Notes：

CREATE TABLE members (
Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
LVL INTEGER NOT NULL,
Notes TEXT
);

INSERT INTO members (Name,LVL,Notes)
VALUES  ('Jean',12,'First stage'),
        ('Jacques',1,'Second stage'),
        ('Amelie',1,'Second stage'),
        ('Louis',13,'Some other note altogether')
;

我想对照另一张桌子检查它tmp

CREATE TABLE tmp (
Name TEXT NOT NULL,
LVL INTEGER NOT NULL,
Notes TEXT
);

INSERT INTO tmp (Name,LVL,Notes)
VALUES  ('Jean',13,'First stage'),
        ('Jacques',1,'Second stage'),
        ('Amelie',1,'Third stage'),
        ('Louis',14,'Fourth stage')
;

并且如果 LVL 和/或 Notes 字段（如 Jean 和 Louis 的 LVL 以及 Amelie 和 Louis 的 Notes）发生变化，我想members在记录以前的值（作为整行）和时间戳后用新值更新表member_changes桌子。

实现这一目标的最小查询集是什么？

member_changes桌子的更好设计是什么？是否与添加为主键和字段相同members但添加rowID了主键和timestamp字段？并且自然 memberID 将允许重复。

非常感谢，

抢

扩展答案的概要

感谢@forpas 的友好回答，我把这个小系统和两个额外的触发器放在一起。新信息通过tmp表格进入。假定成员名称是唯一的；可能members.Id不需要主键。尽管如此：

-- CREATE members table for current guild members
-- Id is prim key and Name has unique index
CREATE TABLE members (
  Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  Name TEXT NOT NULL UNIQUE,
  LVL INTEGER NOT NULL,
  Notes TEXT
);
-- SAMPLE DATA
INSERT INTO members (Name,LVL,Notes) VALUES  
        ('Jean',12,'First stage'),
        ('Jacques',1,'Second stage'),
        ('Amelie',1,'Second stage'),
        ('Louis',13,'Some other note altogether');
-- LOG table to see membership changes over time
CREATE TABLE members_changes (
  timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
  Id INTEGER REFERENCES members(Id),
  Name TEXT NOT NULL,
  LVL INTEGER NOT NULL,
  Notes TEXT
);
-- TABLE through which the updates will come in via rvest
-- presumed cannot contain duplicate names
CREATE TABLE tmp (
  Name TEXT NOT NULL UNIQUE,
  LVL INTEGER NOT NULL,
  Notes TEXT
);
-- TRIGGERS (3)
-- (1) UPDATES MEMBERS if insertion in tmp shows changes
-- also LOGS this change in members_changes
CREATE TRIGGER IF NOT EXISTS tr_insert_tmp AFTER INSERT ON tmp
BEGIN
  INSERT INTO members_changes(Id,Name,LVL,Notes)
  SELECT Id,Name,LVL,Notes
  FROM members
  WHERE Name = NEW.NAME AND (LVL IS NOT NEW.LVL OR Notes IS NOT NEW.Notes);
  
  UPDATE members 
  SET LVL = NEW.LVL, Notes = NEW.Notes
  WHERE Name = NEW.Name AND (LVL IS NOT NEW.LVL OR Notes IS NOT NEW.Notes);
END;
-- (2) LOGS DELETIONS from members
CREATE TRIGGER IF NOT EXISTS tr_delete_members BEFORE DELETE ON members
BEGIN
    INSERT INTO members_changes(Id,Name,LVL,Notes)
    SELECT Id,Name,LVL,Notes || " :Deleted"
    FROM members
    WHERE Name = OLD.Name;
END;
-- (3) LOGS INSERTS into members (new members)
CREATE TRIGGER IF NOT EXISTS tr_insert_members AFTER INSERT ON members
BEGIN
    INSERT INTO members_changes(Id,Name,LVL,Notes)
    SELECT Id,Name,LVL,Notes || " :Inserted"
    FROM members
    WHERE Name = NEW.Name;
END;
-- this shows all defined triggers
select * from sqlite_master where type = 'trigger';

-- QUERIES to be run from the script after tmp is updated (b,c,d)
-- ADD NEW MEMBERS
-- it should mostly fail (changes are slow and few)
-- this is logged via tr_insert_members
INSERT OR IGNORE INTO members(Name,LVL,Notes) SELECT Name, LVL, Notes FROM tmp;
-- DELETE OLD MEMBERS 
-- logged via tr_delete_members
DELETE FROM members WHERE Name NOT IN (SELECT Name FROM tmp);
-- EMPTY tmp at the end of the script run
DELETE FROM tmp;

当应用程序运行时，唯一需要调用的查询是：

a）填充tmp（来自rvest收集的数据框）
b）查询以添加新成员tmp
c）查询以删除不在tmp
d中的成员）查询为空tmp

这要归功于@forpas 建议的数据库设置。我从来没有使用过触发器，最终对它们有所了解。对记录更改非常有帮助。

标签： sqlrsqlitetriggers

Answer 1

一个合适的设计members_changes是这样的：

CREATE TABLE members_changes (
  timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
  Id INTEGER REFERENCES members(Id),
  Name TEXT NOT NULL,
  LVL INTEGER NOT NULL,
  Notes TEXT
);

该列timestamp的默认值是当前时间戳。

您需要一个AFTER INSERTtable 触发器tmp，以便对于插入tmp的每个行，将插入成员中的相应行（如果或的members_changes任何值不同），之后新行将更新 的行：LVLNotestmpmembers

CREATE TRIGGER IF NOT EXISTS tr_insert_tmp AFTER INSERT ON tmp
BEGIN
  INSERT INTO members_changes(Id,Name,LVL,Notes)
  SELECT Id,Name,LVL,Notes
  FROM members
  WHERE Name = NEW.NAME AND (LVL IS NOT NEW.LVL OR Notes IS NOT NEW.Notes);
  
  UPDATE members 
  SET LVL = NEW.LVL, Notes = NEW.Notes
  WHERE Name = NEW.Name AND (LVL IS NOT NEW.LVL OR Notes IS NOT NEW.Notes);
END; 

请参阅演示。