Expression Representation

Simple Expressions

Literals

The simplest expressions are just Clojure literals

=> (map expr-to-sql [nil 5 6.7 :tbl.fld true false "hi"])
("NULL" "5" "6.7" "tbl.fld" "TRUE" "FALSE" "'hi'")

Intervals

Time intervals are represented by an interval map:

{:interval ...expr...
 :units ...name...}

A select statement can also be used as an expression. Depending on its context, it may need to be a scalar subquery or a vector subquery. When used as subqueries, select statements are parenthesized:

=> (def q (select [[count :*]] (from :pet)))
=> (expr-to-sql q)
"(SELECT COUNT(*) FROM pet)"

Expression Forms

All other expressions are encoded as possibly nested collections, typically a vector or a quoted or sytax-quoted form. As in Clojure, the first item in an expression is in function position. That first item can be a keyword, symbol, or string. Certain operators or functions can be used without quoting:

#{+ - * / < <= = not= >= > not max min count val vals mod}

Most expressions work like you would expect. When converted to SQL, operator precedence will determine if parentheses get added: For example:

=> (expr-to-sql '(* (- 20 (<< 4 2)) (+ 2 3)))
"(20 - (4 << 2)) * (2 + 3)"
=> (expr-to-sql '(and (> 5 3) (or (< n 10) (= x 5))))
"5 > 3 AND (n < 10 OR x = 5)"

In Clojure, certain functions like + and * allow more than two arguments. Seaquell supports the same idea:

=> (expr-to-sql [+ :a :b :c])
"a + b + c"

Relational operators with multiple arguments are usually joined by AND.

=> (expr-to-sql [> :a :b :c])
"a > b AND b > c"

The exception is not=, which is joined by OR since we don’t require that every value is distinct, only that the values are not all the same. Thus, the two expressions below are equivalent.

=> (expr-to-sql [not= :a :b :c])
"a <> b OR b <> c"
=> (expr-to-sql [not [= :a :b :c]])
"NOT (a = b AND b = c)"

Val and Vals

Both val and vals can be used to generate a parenthesized expression or a tuple.

=> (expr-to-sql [val [> :n 5]])
"(n > 5)"
=> (expr-to-sql [vals 4 5 6])
"(4, 5, 6)"

Cast

Cast expressions look like function calls with two arguments: an expression and a type name

=> (expr-to-sql [:cast 5 :unsigned])
"CAST(5 AS UNSIGNED)"

Actually, you can use expression syntax for the type to perform more complicated casts.

=> (expr-to-sql [:cast 5.6789 [:decimal 5 3]])
"CAST(5.6789 AS DECIMAL(5, 3))"

Case

Simple SQL CASE expressions are encoded in a Clojure case form:

=> (expr-to-sql '(case 2 3 "Three" 2 "Two" nil))
"CASE 2 WHEN 3 THEN 'Three' WHEN 2 THEN 'Two' ELSE NULL END"

You can omit the last argument to leave out the ELSE part.

Cond

General SQL CASE expressions are encoded in a Clojure cond form:

=> (expr-to-sql '(cond (< n 10) "Small" (< n 100) "Big" :else "Huge" ))
"CASE WHEN n < 10 THEN 'Small' WHEN n < 100 THEN 'Big' ELSE 'Huge' END"

Not the idiomatic use of :else to define the optional ELSE part.

Exists and Not Exists

=> (def q (select :* :from :pet :where [= :name "fluffy"]))
=> (expr-to-sql [:exists q])
"EXISTS (SELECT * FROM pet WHERE name = 'fluffy')"

Use :not-exists to test that a subquery would not return any rows.

In and Not In

In is actually treated like a binary operator. But here are the three common ways of using it:

Compare item against rows of a one-column table
Compare item against result of a vector subquery
Compare item against a list of values

=> (expr-to-sql [:in :item :items])
"item IN items"
=> (expr-to-sql [:in :item {:sql-stmt :select :from :items}])
"item IN (SELECT * FROM items)"
=> (expr-to-sql [:not-in 6 [vals 7 8]])
"6 NOT IN (7, 8)"

Between and Not Between

=> (expr-to-sql [:between :b :a :c])
"b BETWEEN a AND c"
=> (expr-to-sql [:not-between :e :a :c])
"e NOT BETWEEN a AND c"

Function Calls

Any form where the first item is not recognized as a unary, binary, or special operator will be treated like a function call:

=> (expr-to-sql [:coalesce nil 4 true])
"COALESCE(NULL, 4, TRUE)"

If the first argument to a function is the distinct symbol or function, it will be used as a function modifier. Some SQL dialects allow this on aggregate functions. For example:

=> (expr-to-sql [count distinct :owner])
"COUNT(DISTINCT owner)"

Expression Maps

Predicate expressions can also be described using a map, similar to how Korma does it. This form of syntactic sugar is especially useful in WHERE clauses. When converted to SQL, such expressions will be rewritten as a logical and with one relational comparison for each key/value pair.

By default, each key and value are compared for equality. But if the value is a form representing a binary predicate of some kind, then the key will be inserted as the first argument to the predicate.

For example:

=> (expr-to-sql '{pi (> 3.1), 20 (* 2 10), name "RingMan", age (between 30 50)})
"pi > 3.1 AND 20 = 2 * 10 AND name = 'RingMan' AND age BETWEEN 30 AND 50"