INSERT OR IGNORE in a trigger

I have a database (for tracking email statistics) that has grown to hundreds of megabytes, and I've been looking for ways to reduce it.

It seems that the main reason for the large file size is that the same strings tend to be repeated in thousands of rows. To avoid this problem, I plan to create another table for a string pool, like so:

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. */
);

And for convenience, a view to join these tables:

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);

In order to be able to use this view as if it were a regular table, I've made some triggers:

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;

The problem

After:

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');

The updated rows contain:

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

There's a NULL that shouldn't be there. The corresponding cell in the EmailInfo table contains an orphaned ToAddrRef value.

If you do the INSERTs one at a time, you'll see that Alice's ID in the AddressLookup table changes!

It appears that this behavior is 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.

So the "REPLACE" in the top-level "INSERT OR REPLACE" statement开发者_开发问答 is overriding the critical "INSERT OR IGNORE" in the trigger program.

Is there a way I can make it work the way that I wanted?

Well, since SQLite is open-source, I'll just change the codeTriggerProgram function to handle ON CONFLICT the way it ought to be.

I have/had a similar problem and I think I found a way to trick sqlite into doing it right by moving the OR REPLACE from the outer statement into the table itself:

CREATE TABLE a (id INTEGER PRIMARY KEY ON CONFLICT REPLACE, dataA TEXT);
CREATE TABLE b (id INTEGER PRIMARY KEY, dataB TEXT);

CREATE TRIGGER auto_add AFTER INSERT ON a
    BEGIN INSERT OR IGNORE INTO b (id) VALUES (NEW.id); END;

When you INSERT OR REPLACE into table "a" you always replace the row added to table b because the trigger uses the OR REPLACE now.

But if you just do an INSERT INTO it seems to work because the on conflict handling is no longer part of the outer insert but part of the table itself.