Quantcast

novice sql generation

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

novice sql generation

Jonathan H N Chin-5
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
<[hidden email]> | systems mangler | tel/fax: +44 1223 767091/330508

                "respondeo etsi mutabor" --Rosenstock-Huessy
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: novice sql generation

Dan Kennedy

--- [hidden email] wrote:

> 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.

The expression "I" means the column "i". For the literal "I", use 'I'.
The query above will probably work if you bind (say) 'm1', 'i1', 'm1'.
Naturally don't include the quotes in the value passed to sqlite3_bind_text().



               
__________________________________
Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs
Loading...