Clause

SQL Clauses

Introduction

SQL Clauses typically contain

  1. clause expression
  2. clause parameters (optional)

Clause examples:

[" WHERE emp.id = ?" 1039]
[" GROUP BY ?" "category"]
[" LIMIT ?, ?" 50 60]
["SELECT * FROM emp"]

The SQL clause functions are available in the org.bituf.sqlrat.clause namespace.

user=> (use 'org.bituf.sqlrat.clause)

All functions that return a clause or some structure containing clauses create a valid clause before returning them. To explicitly create a clause you can use the as-clause function.

user=> (as-clause "SELECT * FROM emp") ; create a clause explicitly
["SELECT * FROM emp"]

user=> (as-clause 5569) ; create a clause explicitly
["5569"]

user=> (as-clause :list) ; create a clause explicitly
["list"]

user=> (as-clause [45]) ; error! if it's a collection then first element must be string
java.lang.IllegalArgumentException: Clause must begin with string:

The clause? function tells whether an argument is a valid clause (annotated with clause meta data).

user=> (clause? (as-clause "select * from emp"))
true

user=> (clause? "select * from emp")
false

user=> (clause? ["select * from emp"])
false

Free form clauses

;; free-form SQL clauses and statements
user=> (query "SELECT * FROM emp")
["SELECT * FROM emp"]

user=> (>> "SELECT * FROM emp") ; >> is an alias to 'query' function
["SELECT * FROM emp"]

user=> (let [table :emp]
         (query "SELECT * FROM " table))
["SELECT * FROM emp"]

user=> (query "SELECT * FROM emp WHERE" :id "=" 50)
["SELECT * FROM emp WHERE id = 50"]

user=> (query "SELECT * FROM emp WHERE id =" (? 50)) ; value clause (details below)
["SELECT * FROM emp WHERE id = ?" 50]

user=> (query "SELECT * FROM emp WHERE" (=? :id 50)) ; binary-operator clause (details below)
["SELECT * FROM emp WHERE id=?" 50]

Function names in uppercase (AND, OR, FN) enclose the emitted SQL.

Value clause

The as-value-clause function turns a value or a set of values into a parameterized value-clause.

user=> (as-value-clause 45)
["?" 45]

user=> (as-value-clause [45 78])
["?, ?" 45 78]

user=> (as-value-clause nil) ; nil, keyword, fn  not allowed as values
java.lang.IllegalArgumentException: Invalid input: nil and clauses are not allowed

user=> (as-value-clause [[]]) ; bad input
java.lang.IllegalArgumentException: Invalid input: nil, collection and function are not allowed as clause elements

In practice you may like to use the ? function (which is a smarter version) instead of as-value-clause function.

You can use the query and as-value clause functions to combine the results:

user=> (query "SELECT * FROM itemorder WHERE qty =" (as-value-clause 45))
["SELECT * FROM itemorder WHERE qty = ?" 45]

Merging clauses

Merging of clauses is perhaps the single most important feature here. Two or more clauses can be merged to form a super-clause. Each argument is promoted to a clause if not already one.

user=> (merge-clauses  ["a=?" 10] " AND " ["b=?" 20])
["a=? AND b=?" 10 20]

user=> (merge-clauses  :where ["b=?" 20]) ; no space-padding between clauses
["whereb=?" 20]

user=> (merge-clauses  "where b=" (as-value-clause 20)) ; no space-padding between clauses
["where b=?" 20]

WHERE clause functions

IS NULL, NOT NULL

user=> (is-null :code)
["code IS NULL"]

user=> (not-null :code)
["code NOT NULL"]

Functions with two operands

user=> (=? :code 50)
["code=?" 50]

user=> (=? :code nil) ; =? is intelligent about nil values
["code IS NULL"]

user=> (<>? :code 50) ; or (!=? :code 50)
["code<>?" 50]

user=> (<>? :code nil) ; <>? is nil-aware too
["code NOT NULL"]

user=> (>? :code 50) ; or >=?, <?, <=? etc
["code>?" 50]

user=> (>? :code nil)
java.lang.AssertionError: Assert failed: v

LIKE expressions

user=> (like? :flag "on")
["flag LIKE ?" "on"]

user=> (begins-with? :name "jess")
["name LIKE ?" "jess%"]

user=> (ends-with? :name "jess")
["name LIKE ?" "%jess"]

user=> (includes? :name "ford")
["name LIKE ?" "%ford%"]

BETWEEN expression

user=> (between? :qty 50 60)
["qty BETWEEN ? AND ?" 50 60]

IN, NOT IN expressions

user=> (in? :dayofweek [1 3 5])
["dayofweek IN (?, ?, ?)" 1 3 5]

user=> (in? :dayofweek [1 3 nil]) ; beware!
["dayofweek IN (?, ?, ?)" 1 3 nil]

user=> (in=? :dayofweek [1 3 5]) ; in=? may be easier to use at times
["dayofweek IN (?, ?, ?)" 1 3 5]

user=> (in=? :dayofweek nil)
["dayofweek IS NULL"]

user=> (in=? :dayofweek 88)
["dayofweek=?" 88]

user=> (not-in? :dayofweek [2 4 6 7]) ; NOT IN
["dayofweek NOT IN (?, ?, ?, ?)" 2 4 6 7]

user=> (not-in<>? :dayofweek [2 4 6 7]) ; Combination of NOT IN and <>
["dayofweek NOT IN (?, ?, ?, ?)" 2 4 6 7]

user=> (not-in<>? :dayofweek 7) ; Combination of NOT IN and <>
["dayofweek<>?" 7]

user=> (not-in<>? :dayofweek nil) ; intelligent about nil
["dayofweek NOT NULL"]

user=> (not-in!=? :dayofweek 7) ; this is the != version of the same thing
["dayofweek!=?" 7]

Merge functions, and ? (the smart value parameter function)

user=> (? 80)
["?" 80]

user=> (? nil) ; beware!
java.lang.IllegalArgumentException: Invalid input: nil and clauses are not allowed

user=> (? [1 3 5])
["?, ?, ?" 1 3 5]

user=> (? {:a 10 :b 20}) ; you can combine them using the AND/OR function
(["a=?" 10] ["b=?" 20])

user=> (AND (>? :qty 3) (in? :discountcode [77 88 99])) ; AND is a merge function
["(qty>? AND discountcode IN (?, ?, ?))" 3 77 88 99]

user=> (OR (>? :qty 3) (in? :discountcode [77 88 99])) ; so is OR
["(qty>? OR discountcode IN (?, ?, ?))" 3 77 88 99]

user=> (AND (? {:a 10 :b 20 :c 30})) ; if you pass only one arg to AND, it must be a collection of clauses
["(a=? AND b=? AND c=?)" 10 20 30]

user=> (AND (? {:a 10 :b 20 :c [30 40 50]})) ; ? uses 'in=?' to define the binary relation
["(a=? AND b=? AND c IN (?, ?, ?))" 10 20 30 40 50]

user=> (OR (? {:a 10 :b 20 :c 30})) ; OR is like AND, only with interpose string "OR"
["(a=? OR b=? OR c=?)" 10 20 30]

LIMIT clause

user=> (limit 40)
[" LIMIT ?" 40]

user=> (limit 30 40)
[" LIMIT ?, ?" 30 40]

Sub queries

Sub-queries are supported by the =? and in? family of functions:

user=> (query :where (in? :q (query "select qty from orders")))
["where q IN (select qty from orders)"]

user=> (query :where (=? :q (query "select qty from orders where" (=? :code 557))))
["where q=(select qty from orders where code=?)" 557]

Convenience functions

Comma separated names

Column names are comma separated -- you can use csnames function to convert a collection of column names to a clause.

user=> (query :SELECT [:a :b] :WHERE (=? :id 45))
java.lang.IllegalArgumentException: Clause must begin with string: [:a :b]

user=> (query :SELECT (csnames [:a :b]) :WHERE (=? :id 45))
["SELECT a, b WHERE id=?" 45]

user=> (query :SELECT (>| :a :b) :WHERE (=? :id 45))
["SELECT a, b WHERE id=?" 45]

Database Functions

Database functions can be embedded in an SQL clause using the sqlfn function or its alias FN.

user=> (sqlfn :AVG :qty)
["AVG(qty)"]

user=> (FN :NOW)
["NOW()"]

user=> (sqlfn :foo :bar :+ 45)
["foo(bar+45)"]

user=> (FN :foo :bar "+45")
["foo(bar+45)"]

user=> (FN :foo :bar :+ (? 45))
["foo(bar+?)" 45]

Column aliases

Column names can be aliased in a SELECT statement. The as and asfn functions create an appropriate clause for this.

user=> (as :avgqty "AVG(qty)")
["AVG(qty) AS avgqty"]

user=> (csnames [:name (as :avgqty "AVG(qty)")]) ; >| is shortcut version for csnames
["name, AVG(qty) AS avgqty"]

user=> (>| :name (asfn :avgqty :avg :qty)) ; asfn is 'as' and 'sqlfn' combined
["name, AVG(qty) AS avgqty"]

Complete statements

Writing SQL using query function may be cumbersome, so there are few shortcuts to make this job easier:

user=> (SELECT (csnames [:col1 :col2])
         (FROM :table)
         (WHERE (=? :id 34)))
["SELECT col1, col2 FROM table WHERE id=?" 34]

user=> (SELECT (>| (asfn :cnt :count :*) :category :batch)
         (FROM :items)
         (GROUP-BY (>| :category :batch)))
["SELECT COUNT(*) as cnt, category, batch FROM items GROUP BY category, batch"]

Updated

Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.