>>>>> Igor Tandetnik writes:
[…]
> Note that ending up in the IGNORE branch of INSERT OR IGNORE
> statement doesn't constitute failure, but normal execution. The same
> is true for INSERT ... SELECT statement where SELECT returns zero
> rows so nothing is actually inserted.
Thus was my question: how do I ensure that a SELECT … INSERT
statement results in exactly one row being inserted?
(Additional constraint: it should be possible to use the code in
a trigger.) The best thing I was able to imagine is to replace:
INSERT INTO "bar" ("foo")
SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1;
with:
INSERT INTO "bar" ("foo")
VALUES ((SELECT f."rowid" FROM "foo" f WHERE f."foo" = ?1));
… The task I'm solving seems unlike an unusual one. Suppose
that, e. g., I'm recording a history of clients. Each client
has a name, and is served an arbitrary (but positive) number of
times, at particular dates. Or, for the 0'th approximation:
CREATE TABLE "history" (
"client-name" TEXT NOT NULL,
-- as in: CAST (strftime ('%s', 'now') AS INTEGER), or now ()
"timestamp" INTEGER NOT NULL
);
The redundancy of the schema above could, however, be reduced as
follows:
CREATE TABLE "client" (
"name" TEXT NOT NULL
);
CREATE UNIQUE INDEX "client-unique"
ON "client" ("name");
CREATE TABLE "history-1" (
"client" INTEGER NOT NULL REFERENCES "client",
"timestamp" INTEGER NOT NULL
);
Now, I wish to preserve the simplicity of access of the first
variant. Thus, I create a VIEW:
CREATE VIEW "history"
AS SELECT c."name" AS "client-name",
h."timestamp"
FROM "history-1" h
INNER JOIN "client" c
ON (c."rowid" = h."client");
… and a TRIGGER:
CREATE TRIGGER "history-insert"
INSTEAD OF INSERT ON "history" FOR EACH ROW BEGIN
INSERT OR IGNORE INTO "client" ("name")
VALUES (new."client-name");
INSERT INTO "history-1" ("client", "timestamp")
VALUES ((SELECT c."rowid"
FROM "client".c
WHERE c."name" = new."client-name"),
new."timestamp");
END;
This way, an application can SELECT from and INSERT into this
new "history" VIEW with just the same simplicity as was possible
with the former "history" TABLE. Naturally, this trigger
requires that a single INSERT to the "history" VIEW results in
exactly a single INSERT to the underlying "history-1" TABLE.
(Which is what brought my question above.)
To note is that, with little CASE trickery, it's possible to
support “hybrid” (id, value) views (c."rowid" AS "client-id" in
the VIEW above), and NULL non-constraints, just as well.
--
FSF associate member #7257 np. ml_hygm.xm
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
