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, '
[email protected]', '
[email protected]');
INSERT OR REPLACE INTO EmailView VALUES (2, '
[email protected]', '
[email protected]');
The updated rows contain:
MessageID ToAddr FromAddr
--------- ------ --------
1 NULL
[email protected]
2
[email protected] [email protected]
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?
