We have a e-mail program that uses a table like:
CREATE TABLE Emails (
MessageID TEXT,
FromAddr TEXT,
ToAddr TEXT,
-- ...
);
The database takes up hundreds of megabytes of disk space. In order to
avoid the duplication of storing the same addresses thousands of times,
I'm planning to replace this with something like:
CREATE TABLE Addresses (
ID INTEGER PRIMARY KEY,
Address TEXT UNIQUE
);
CREATE TABLE Emails (
MessageID TEXT,
FromAddrRef INTEGER REFERENCES Addresses(ID),
ToAddrRef INTEGER REFERENCES Addresses(ID),
-- ...
);
-- Emulate the SELECT behavior of the old table.
CREATE VIEW EmailsView AS
SELECT
MessageID,
AF.Address AS FromAddr,
AT.Address AS ToAddr,
-- ...
FROM Emails
LEFT JOIN Addresses AF ON (FromAddrRef = AF.ID)
LEFT JOIN Addresses AT ON (ToAddrRef = AT.ID);
I'd like to be able to write INSERT statements on this view like on the
old version of the Emails table. Something like:
CREATE TRIGGER TriggerUpdateEmailsView
INSTEAD OF INSERT ON EmailsView
BEGIN
INSERT OR IGNORE INTO Addresses VALUES(NULL, FromAddr);
INSERT OR IGNORE INTO Addresses VALUES(NULL, ToAddr);
-- The statement below is invalid syntax.
INSERT INTO Emails (
MessageID,
(SELECT ID FROM Addresses WHERE Address=FromAddr),
(SELECT ID FROM Addresses WHERE Address=ToAddr)
)
END;
How do I do this?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
