sqlite - 在触发器中插入或忽略

Question

我有一个数据库(用于跟踪电子邮件统计信息)已经增长到数百兆字节，我一直在寻找减少它的方法。

看起来文件大的主要原因是相同的字符串往往在数千行中重复。为了避免这个问题，我计划为字符串池创建另一个表，如下所示:

CREATE TABLE AddressLookup (
   ID      INTEGER PRIMARY KEY AUTOINCREMENT,
   Address TEXT UNIQUE
);

CREATE TABLE EmailInfo (
   MessageID   INTEGER PRIMARY KEY AUTOINCREMENT,
   ToAddrRef   INTEGER REFERENCES AddressLookup(ID),
   FromAddrRef INTEGER REFERENCES AddressLookup(ID)
   /* Additional columns omitted for brevity. */
);

为方便起见，加入这些表的 View :

CREATE VIEW EmailView AS
SELECT
   MessageID,
   A1.Address AS ToAddr,
   A2.Address AS FromAddr
FROM EmailInfo
   LEFT JOIN AddressLookup A1 ON (ToAddrRef = A1.ID)
   LEFT JOIN AddressLookup A2 ON (FromAddrRef = A2.ID);

为了能够像使用普通表一样使用这个 View ，我做了一些触发器:

CREATE TRIGGER trg_id_EmailView
   INSTEAD OF DELETE ON EmailView
BEGIN
   DELETE FROM EmailInfo WHERE MessageID = OLD.MessageID;
END;

CREATE TRIGGER trg_ii_EmailView
   INSTEAD OF INSERT ON EmailView
BEGIN
   INSERT OR IGNORE INTO AddressLookup(Address) VALUES (NEW.ToAddr);
   INSERT OR IGNORE INTO AddressLookup(Address) VALUES (NEW.FromAddr);
   INSERT INTO EmailInfo
      SELECT NEW.MessageID, A1.ID, A2.ID
      FROM AddressLookup A1, AddressLookup A2
      WHERE A1.Address = NEW.ToAddr AND A2.Address = NEW.FromAddr;
END;

CREATE TRIGGER trg_iu_EmailView
   INSTEAD OF UPDATE ON EmailView
BEGIN
   UPDATE EmailInfo SET MessageID = NEW.MessageID
      WHERE MessageID = OLD.MessageID;
   REPLACE INTO EmailView
      SELECT NEW.MessageID, NEW.ToAddr, NEW.FromAddr;
END;

问题

后:

INSERT OR REPLACE INTO EmailView VALUES (1, 'alice@example.com', 'bob@example.com');
INSERT OR REPLACE INTO EmailView VALUES (2, 'alice@example.com', 'chad@example.com');

更新的行包含:

MessageID   ToAddr               FromAddr
---------   ------               --------
1           NULL                 bob@example.com
2           alice@example.com    chad@example.com

有一个不应该在那里的 NULL。 EmailInfo中对应的单元格表包含一个孤立的 ToAddrRef值(value)。

如果您一次执行一次插入操作，您将在 AddressLookup 中看到 Alice 的 ID。表变了!

看来这种行为是 documented :

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.

因此，顶级“INSERT OR REPLACE”语句中的“REPLACE”覆盖了触发器程序中的关键“INSERT OR IGNORE”。

有没有办法让它按照我想要的方式工作？

Answer 1

好吧，由于 SQLite 是开源的，我将更改 codeTriggerProgram处理函数 ON CONFLICT它应该是这样的。