Quelg: All Examples

Table

For example, we consider the products table, which has columns of product ID (pid), name, and price (price), and the orders table, which has columns of order ID (oid), product ID (pid), and quantity (qty). We assume the following simple database schema in our implementation:

 val products : unit -> < pid:int; name:string; price:int > list
 val orders   : unit -> < oid:int; pid:int; qty:int > list

The embedding has a bit of noise in the form of extra arguments of type unit, to delay the evaluation of conditional branches and to get around the value restriction.

Q1: Simple Query

From these tables, we consider a query for sales by grouping by oid. We call this query \(Q_1\), and in Quelg we can write:

\( Q_1 = \mathcal{G}_{({\rm oid}, \alpha)}({\rm for}~(p \leftarrow {\rm table}(“{\rm products}”)) \\ ~~~~~~~~~~~~~~~~~~~~~~~{\rm for}~(o \leftarrow {\rm table}(“{\rm orders}”)) \\ ~~~~~~~~~~~~~~~~~~~~~~~{\rm where}~(p.{\rm pid} = o.{\rm pid}) \\ ~~~~~~~~~~~~~~~~~~~~~~~{\rm yield}~\{{\rm oid} = o.{\rm oid}, {\rm sales} = p.{\rm price} * o.{\rm qty}\}) \\ ~~~~~~~~{\rm Where}~~\alpha = \{({\rm sales}, {\rm SUM}, {\rm sale\_sum})\} \)
A query written in Quelg like the above query is represented in our implementation code in OCaml as:

  module Q1(S:SYM_SCHEMA) = struct
    open S
    let table_orders   = table ("orders", orders ())
    let table_products = table ("products", products ())

    let key = ooid
    let alpha = [(osale, (string "sum"), (string "sale_sum"))]
    let query = foreach (fun () -> table_products) @@ fun p ->
                foreach (fun () -> table_orders) @@ fun o ->
                where ((pid p) =% (opid o)) @@ fun () ->
                yield @@ osales (oid o) ((price p) *% (qty o))

    let q1 = group key alpha query @@ fun v key -> q1_res key (sale_sum v)

    let observe = observe
  end

We can evaluate the above to translate an SQL string by the FixGenSQL interpreter. In our implementation, the queries for grouping are transformed as subqueries for clarity.

let module M = Q1(FixGenSQL) in M.observe (fun () -> M.q1);;
(*
  - : < key : int; sale_sum : int > list Schema.FixGenSQL.obs =
  "SELECT x.oid AS key, SUM(x.sale) AS sale_sum 
   FROM (SELECT z.oid AS oid, y.price * z.qty AS sale 
         FROM products AS y, orders AS z 
         WHERE true AND y.pid = z.pid) AS x 
   WHERE true 
   GROUP BY x.oid"
*)

Q2: Query with Nested Control Structures

We consider a query with Nested Control Structures. We call this query \(Q_2\), and in Quelg we can write:

\( Q'_2 = \mathcal{G}_{({\rm oid}, \alpha)}({\rm for}~(p \leftarrow {\rm table}(“{\rm products}”)) \\ ~~~~~~~~~~~~~~~~~~~~~~~{\rm for}~(o \leftarrow {\rm table}(“{\rm orders}”)) \\ ~~~~~~~~~~~~~~~~~~~~~~~{\rm where}~(p.{\rm pid} = o.{\rm pid}) \\ ~~~~~~~~~~~~~~~~~~~~~~~{\rm yield}~\{{\rm oid} = o.{\rm oid}, {\rm sales} = p.{\rm price} * o.{\rm qty}\}) \\ ~~~~~~~~{\rm Where}~~\alpha = \{({\rm sales}, {\rm SUM}, {\rm sale\_sum}), ({\rm qty}, {\rm SUM}, {\rm qty\_sum})\} \)
\( Q_2 = {\rm for}~(q \leftarrow Q'_2) \\ ~~~~~~~~~~{\rm yield}~\{{\rm oid} = q.{\rm oid}, {\rm average} = q.{\rm sale\_sum}/q.{\rm qty\_sum}\} \)
A query written in Quelg like the above query is represented in our implementation code in OCaml as:

 module Q2'(S:SYM_SCHEMA) = struct
   open S
   let table_orders   = table ("orders", orders ())
   let table_products = table ("products", products ())

   let key = qoid
   let alpha = [(qsale, (string "sum"), (string "sale_sum"));
                (qqty,  (string "sum"), (string "qty_sum"))]
   let q2' = group key alpha (foreach (fun () -> table_products) @@ fun p ->
                              foreach (fun () -> table_orders) @@ fun o ->
                              where ((pid p) =% (opid o)) @@ fun () ->
                              yield @@ qsales (oid o) ((price p) *% (qty o)) (qty o))
                   (fun v key -> q2'_res key (sale_sum v) (qty_sum v))
 end

 module Q2(S:SYM_SCHEMA) = struct
   open S
   module M = Q2'(S)

   let q2 = foreach (fun () -> M.q2') @@ fun q ->
            yield @@ q2_res (q2_key q) ((q2_sale_sum q) /% (q2_qty_sum q))

   let observe = observe
 end