I'm trying to implement a string pool using views and triggers:
CREATE TABLE StringPool (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
Val TEXT UNIQUE
);
CREATE TABLE T (
Key TEXT PRIMARY KEY,
ValRef INTEGER REFERENCES StringPool(ID)
);
CREATE VIEW V AS
SELECT Key, Val FROM T LEFT JOIN StringPool ON ValRef = StringPool.ID;
CREATE TRIGGER _trg_ii_V
INSTEAD OF INSERT ON V
BEGIN
INSERT OR IGNORE INTO StringPool(Val) VALUES (NEW.Val);
INSERT INTO T
SELECT NEW.Key, ID FROM StringPool WHERE Val = NEW.Val;
END;
The approach seemed to be working fine:
sqlite> INSERT INTO V VALUES ('K1', 'String1');
sqlite> INSERT INTO V VALUES ('K2', 'String2');
sqlite> SELECT * FROM V;
K1|String1
K2|String2
sqlite> SELECT * FROM StringPool;
1|String1
2|String2
...until I tried a REPLACE instead of a normal INSERT:
sqlite> INSERT OR REPLACE INTO V VALUES ('K3', 'String1');
sqlite> SELECT * FROM V;
K1|
K2|String2
K3|String1
sqlite> SELECT * FROM StringPool;
2|String2
3|String1
The trigger is replacing the ID from the string pool even though I had
specifically NOT written a REPLACE in the trigger program.
It turns out that this is documented behavior (
http://www.sqlite.org/lang_createtrigger.html ):
"An ON CONFLICT <http://www.sqlite.org/lang_conflict.html> clause may
be specified as part of an UPDATE
<http://www.sqlite.org/lang_update.html> or INSERT
<http://www.sqlite.org/lang_insert.html> action within the body of the
trigger. However if an ON CONFLICT
<http://www.sqlite.org/lang_conflict.html> clause is specified as part
of the statement causing the trigger to fire, then conflict handling
policy of the outer statement is used instead."
...and that other people have been bitten by it:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg01668.html
http://www.mail-archive.com/sqlite-users@sqlite.org/msg21206.html
So, my questions are:
(1) Why was it done this way? Is there an example in which SQLite's
behavior here is desirable?
If this is indeed a bug, then it seems to be a trivial one to fix:
85432c85432
< pParse->eOrconf = (orconf==OE_Default)?pStep->orconf:(u8)orconf;
---
> pParse->eOrconf =
(pStep->orconf==OE_Default)?(u8)orconf:pStep->orconf;
(2) How, other than changing the SQLite source code, could I make my
trigger work the way I want it to?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
