I am trying to write my first application using sqlite in perl.
The perl part is no problem, but I have not used sql before and
am having some difficulty.
I have three tables:
CREATE TABLE i2u(i UNIQUE, u);
INSERT INTO i2u VALUES('i1','u1');
INSERT INTO i2u VALUES('i2','u2');
INSERT INTO i2u VALUES('i3','u3');
CREATE TABLE m2u(m UNIQUE, u);
INSERT INTO m2u VALUES('m1','u1');
INSERT INTO m2u VALUES('m2','u2');
CREATE TABLE mu(m UNIQUE, u);
Given some I and M, I wish to generate U from the first success
when attempting to map I->U in i2u or M->U in m2u.
So, for example, ("i1","m2") should map to ("u1").
If the first stage found a U, I want to insert a new row into mu.
So, continuing the example, we should insert ("m2", "u1").
I feel sure there must be a way to implement this as a single
sql query, without having to resort to messy perl wrapping.
The sqlite sql documentation gives me some ideas, but none yet
that works properly. So far, the closet to functional code I have
come up with is:
INSERT INTO mu
SELECT ?,u FROM (
SELECT u FROM i2u WHERE i = ?
UNION
SELECT u FROM m2u WHERE m = ?
)
ORDER BY ROWID
LIMIT 1
;
If I bind "M","I","M" to this as a test, then I do not get
the result I hoped for (ie. nothing inserted).
This seems to be because, for example, this:
SELECT u FROM i2u WHERE i = "I";
matches all three of the existing rows.
I should have thought that make them all match, I would
have had to do something like:
SELECT u FROM i2u WHERE i LIKE "%I%";
so I am quite confused.
Anyway, I am very unsure that using the ordering on rowid is
going to give me the correct behaviour. I get the impression
that rowid is not guaranteed to be allocated monotonically
increasing.
Also, the first "?" (immediately after select) looks like it
probably not a good way to try and force a literal as output.
Suggestions for the correct way to specify my query would be
greatly appreciated.
thanks,
-jonathan
--
Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK
<[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508
"respondeo etsi mutabor" --Rosenstock-Huessy
