[Ur] SQL query generation

[Vladimir Shabanov]

2011/8/12 Adam Chlipala <adamc at impredicative.com>:
> Vladimir Shabanov wrote:
>>
>> Ur allows us to generate any part of XML using
>>     <xml>{...}</xml>.
>>
>> But with SQL only variable values can be specified:
>>     SELECT ... WHERE id = {[id]}
>>
>
> It's not true that only variables can go between {[...]} in SQL.  Any
> expressions are allowed (parsing-wise).

Sorry, I meant not variables between {[...]} but variable 'id' value
in WHERE (and generally $1, $2,... variables in prepared queries).

>> Is is possible to extend Ur to support generation of SQL expressions?
>> E.g. something like
>>    SELECT ...
>>    ORDER BY column {if descending then DESC else ASC}
>>
>
> That particular example would need a change to the frontend, since no one
> has asked for it before.  Feel free to submit a Mantis bug if you'd like!

done.

>> or
>>    SELECT ...
>>    WHERE column1>  {[val1]}
>>        {if filtering then {AND col2 = x} else {}}
>>
>
> This is already supported with only slightly different code.  I'd suggest:
>
> column1 > {[val1]} AND {if filtering then (SQL col2 = x) else (SQL TRUE)}

Wow. Didn't knew about this. It works (and generates two separate
prepared queries as I can see in /tmp/webapp.c, fantastic!). Maybe it
worth to add such an example in Ur/Web demo?

>> I need to make some queries which are largely the same but differs in
>> sorting order and/or have additional conditions. Currently I need to
>> copy&paste all the query and change few lines of code. Maybe there is
>> a better solution?
>>
>
> Like Hao said, there is nothing fundamental about the parsing extensions
> that generate SQL queries.  Underneath it all are combinators you can call
> directly and whose types you can find in lib/ur/basis.urs.

I looked at this combinators, but it's quite complex to use them directly.

> Still, the parsing extensions lead to much prettier code, so please always
> feel free to suggest new extensions.

Thanks.