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

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

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 值。

如果您一次执行一个 INSERT,您会看到 AddressLookup 表中 Alice 的 ID 发生了变化!

看来这种行为是 documented :



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

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

最佳答案

好吧,由于 SQLite 是开源的,我将更改 codeTriggerProgram 函数以按照它应该的方式处理 ON CONFLICT

关于sqlite - 在触发器中插入或忽略,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/2742219/

10-12 03:37
查看更多