[Ur] Constructing SQL WHERE clause at runtime

[Adam Chlipala]

I could swear I sent an example of this kind to the list years ago, but 
I couldn't find it quickly, so here's a new one!  The function 
[matching] is fine to call with expressions that are only determined at 
runtime.

I do believe all of the ingredients (e.g. parser extensions) here are 
documented in the manual, but admittedly it's not trivial to piece them 
all together into a sufficient intuitive understanding.

table t : { A : int, B : int }

datatype int_exp =
          A
        | B
        | Plus of int_exp * int_exp

datatype bool_exp =
          Eq of int_exp * int_exp
        | And of bool_exp * bool_exp

fun int_exp (e : int_exp) : sql_exp [T = [A = int, B = int]] [] [] int =
     case e of
         A => (SQL t.A)
       | B => (SQL t.B)
       | Plus (e1, e2) => (SQL {int_exp e1} + {int_exp e2})

fun bool_exp (e : bool_exp) : sql_exp [T = [A = int, B = int]] [] [] bool =
     case e of
         Eq (e1, e2) => (SQL {int_exp e1} = {int_exp e2})
       | And (e1, e2) => (SQL {bool_exp e1} AND {bool_exp e2})

fun matching (e : bool_exp) : transaction (list {A : int, B : int}) =
     queryL1 (SELECT * FROM t WHERE {bool_exp e})


On 06/02/2015 03:43 PM, Sergey Mironov wrote:
> Hi! I am trying to implement a post-query filter. Of cause it is
> possible to use [queryL] followed by server-side filtering code, but
> using the DB engine is a better solution in my case.
>
> Typically, I'd like to convert a list of string patterns to a WHERE
> clause of the form  (OR Field LIKE {[patter1]} OR Filed LIKE
> {[pattern2]} OR ... pattern x ...  OR FALSE)
>
> I think it should be possible to write it in Ur/Web, but I can't
> remember any examples demonstrating this. Is it really possible?