我有一个数据库(用于跟踪电子邮件统计信息)已经增长到数百兆字节,我一直在寻找减少它的方法。
看起来文件大的主要原因是相同的字符串往往在数千行中重复。为了避免这个问题,我计划为字符串池创建另一个表,如下所示:
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/