Я пишу приложение/скрипт на языке 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 и/или Заметок (например, LVL для Жана и Луи и Заметки для Амели и Луи) Я хочу обновить members таблицу новыми значениями после записи предыдущих значений (в виде целых строк) с отметкой времени в member_changes таблице.

Каким будет минимальный набор запросов для достижения этой цели?

И каков лучший дизайн member_changes стола? Будет ли это то же members самое, что и с добавлением rowID в качестве первичного ключа и timestamp полей? И, естественно, MemberID разрешил бы дубликаты.

Большое спасибо,

Грабить

КРАТКОЕ ИЗЛОЖЕНИЕ развернутого ответа

Благодаря любезному ответу @forpas я объединил эту небольшую систему с 2 дополнительными триггерами. Новая информация поступает в виде 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;

 

При запуске приложения необходимо вызывать только следующие запросы:

а) тот, который заполняет tmp (из фрейма данных, собранного rvest)
б) запрос на добавление новых участников из tmp
c) запрос на удаление участников, не входящих tmp
d) запрос на опустошение tmp

Это благодаря настройке базы данных, любезно предложенной @forpas. Я никогда не использовал триггеры и, наконец, понял их смысл. Очень полезно для регистрации изменений.

Правильный дизайн для 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 INSERT триггер для таблицы tmp , чтобы для каждой вставленной строки в tmp нее была вставлена соответствующая строка из элементов members_changes (если какое-либо значение LVL или Notes отличается), и после этого новая строка из tmp обновит строку из members :

 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; 
 

Смотрите демонстрацию.