SQL: A Structured Notation for SQL Statements

This library provides an S-expression notation for a subset of SQL. It provides forms that produce statements (as opaque values rather than strings) that can be used directly with Racket’s db library. It also provides macros and functions for creating and manipulating SQL ASTs.

1 Using the SQL Library

This library complements the db library. The database library manages connecting to databases and executing queries; this library helps construction of the queries to execute.

We’ll start by going through the examples Introduction to Using Database Connections using this library’s SQL notation instead.

> (require sql db)

> (define pgc ....)

First we create a temporary table to play around with:

> (query-exec pgc     (create-table #:temporary the_numbers       #:columns [n integer #:not-null] [d varchar]))

> (query-exec pgc     (insert #:into the_numbers #:set [n 0] [d "nothing"]))

Let’s take a look at the statements that just went by:

> (create-table #:temporary the_numbers     #:columns [n integer #:not-null] [d varchar])

(sql-statement "... not in current dialect ...")

> (insert #:into the_numbers #:set [n 0] [d "nothing"])

(sql-statement "INSERT INTO the_numbers (n, d) VALUES (0, 'nothing')")

Now let’s add another row, using “computed” values rather than literals. We can use unquote (or ,) in a scalar expression position to insert a Racket value:

> (define n1 1)

> (define d1 "the loneliest number")

> (query-exec pgc     (insert #:into the_numbers #:set [n ,n1] [d ,d1]))

Let’s look at that last statement:

> (insert #:into the_numbers #:set [n ,n1] [d ,d1])

(sql-statement  "INSERT INTO the_numbers (n, d) VALUES (?, ?)"  1  "the loneliest number")

The unquoted expressions turned into parameter placeholders, and the statement stores their values separately. Strangely, the placeholders appear as ?, and PostgreSQL doesn’t understand ? placeholders; they should have been $1 and $2. But the statement seems to have worked. What’s going on?

We can introduce placeholders explicitly (although unquote is usually more convenient). An explicit placeholder is written ?, regardless of the dialect. The parameters are given in the query call as usual:

> (query-exec pgc     (insert #:into the_numbers #:set [n ?] [d ?])     (+ 1 1) "company")

You can, of course, mix constant literals and unquotes (or placeholders).

> (query-exec pgc     (insert #:into the_numbers #:set [n 3] [d ,"a crowd"]))

SELECT statements are constructed similarly, and they follow the same rules regarding parameters. The statements work the same with all of the query operations.

> (query pgc     (select n d #:from the_numbers #:where (= (% n 2) 0)))

(rows-result  '(((name . "n") (typeid . 23) (type-size . 4) (type-mod . -1))    ((name . "d") (typeid . 1043) (type-size . -1) (type-mod . -1)))  '(#(0 "nothing") #(2 "company")))

> (query-rows pgc     (select n d #:from the_numbers #:where (= (+ n n) (* n n))))

'(#(0 "nothing") #(2 "company"))

> (query-row pgc     (select n d #:from the_numbers #:where (< n 1)))

'#(0 "nothing")

> (query-list pgc     (select d #:from the_numbers #:where (= 0 (% n 2))))

'("nothing" "company")

> (query-value pgc     (select (string_agg d ", ") #:from the_numbers #:where (= 0 (% n 2))))

"nothing, company"

There are S-expression notations for many common SQL operators and expression forms. See SQL Scalar Expressions for details.

2 Statement Forms

The macros in this section create statement values suitable for passing to the query functions of the db library. These statement values satisfy the db library’s statement? predicate. They are different from the statement ASTs produced by statement-qq.

The printing of a statement value is controlled by (current-sql-dialect), but the code it generates when passed to a query function is determined by the dialect of the connection the query is performed on.

Changed in version 1.1 of package sql: Added #:or-ignore for insert.

Changed in version 1.1 of package sql: Added #:if-not-exists option.

3 S-expression Syntax for SQL

3.1 SQL Names and Identifiers

A name is either an unqualified identifier or an identifier qualified with another name, which depending on its usage might represent a catalog, schema, table, range variable, etc.

Concrete SQL has both unquoted and quoted identifiers. Different SQL environments (eg, database backends) have different restrictions on unquoted identifiers, regarding illegal characters and reserved words. Most (but not all) systems also apply some case-folding rule to unquoted identifiers (eg, PostgreSQL converts to lowercase, some others convert to uppercase).

Similarly, this library has both “tagged” and “untagged” notations for identifiers and names. Untagged identifiers are written as raw symbols; they are short and convenient, but they run the risk of confusion with operators and special symbols used by this library. Examples of special symbols include select, as, and from. Examples of identifiers containing operator characters include hello-goodbye and first/last. These identifiers must be written in tagged form.

(Ident: symbol)

Unquoted if possible; case-folded and quoted according the SQL dialect’s rules if symbol is a reserved word or contains illegal characters.

(Ident: MyTable)                ; MyTable

(Ident: Select)                 ; "SELECT"

(Ident: a+b.c)                  ; "a+b.c"

(Ident: string)

Always quoted without case-folding.

(Ident: "MyTable")              ; "MyTable"

(Ident: "Select")               ; "Select"

(Ident: "x1.$!!")               ; "x1.$!!"

(Name: name ...+)

Qualified name; each name except the last qualifies the name to its right.

(Name: x y z)                   ; x.y.z

(Name: x y.z)                   ; x.y.z

(Name: x (Ident: y.z))          ; x."y.z"

symbol

Must not be a special symbol; otherwise an error is raised.

Equivalent to (Ident: symbol) if symbol contains no dot (.) characters and matches the pattern #px"^(?:\\p{L}|_)(?:\\p{L}|\\p{N}|[_$])*$"—that is, a letter or underscore followed by zero or more letters, numbers, underscores, and dollar signs.

If symbol consists of dot-separated parts satisfying the rule above, it is equivalent to (Name: part ...).

MyTable                         ; MyTable

x.y.z                           ; x.y.z

x.select.as                     ; x."SELECT"."AS"

Because case-folding behavior is system-dependent, it is wisest to either always quote a given name or never quote it.

3.2 SQL Scalar Expressions

A scalar expression is either a name, a literal integer or string value, or an application of some function or operator. Note: not every kind of expression is supported in every SQL dialect.

(exists table-expr)

Produces an EXISTS expression:

(exists (select 1 #:from t))        ; EXISTS (SELECT 1 FROM t)

(in scalar-expr #:from table-expr) (in scalar-expr #:values scalar-expr ...)

There are two forms of IN expression, one for table expressions and one for lists of scalar expressions:

(in x #:from (select y #:from ys))  ; x IN (SELECT y FROM ys)

(in x #:values 1 2 3)               ; x IN (1, 2, 3)

(case [scalar-expr scalar-expr] ... maybe-else) (case #:of scalar-expr [scalar-expr scalar-expr] ... maybe-else)

There are two forms of CASE expression, one like Racket’s cond and the other like Racket’s case:

(case [(= x 0) "zero"] [else "no"])  ; CASE WHEN x = 0 THEN 'zero' ELSE 'no' END

(case #:of x [0 "zero"] [else "no"]) ; CASE x WHEN 0 THEN 'zero' ELSE 'no' END

(compare-operator scalar-expr #:some table-expr) (compare-operator scalar-expr #:all table-expr)

Produces an “all-or-any” comparison between a scalar (or row) expression and a table expression.

(= x #:some (select y #:from ys))   ; x = SOME (SELECT y FROM ys)

(< x #:all (select y #:from ys))    ; x < ALL (select y FROM ys)

(name scalar-expr ...)

Represents an ordinary function call; no arity checking is done.

(coalesce x y z)              ; coalesce(x, y, z)

table-expr

Represents a subquery; the query must return at most one row.

(select y #:from ys #:where (x = 0))  ; (SELECT y FROM ys WHERE x = 0)

3.2.1 Special Scalar Expressions

(operator/special scalar-expr ...)

This function-like syntax is used to represent uses of SQL operators, standard SQL functions that don’t use ordinary function-call notation, and a few other special cases, listed below.

• The CAST and EXTRACT special functions:

  (cast "2015-03-15" DATE)      ; CAST('2015-03-15' AS DATE)

  (cast "123" (NUMERIC 5 0))    ; CAST('123' AS NUMERIC(5, 0))

  (extract YEAR dob)            ; EXTRACT(YEAR FROM dob)

  Note that as above, types and fields are written as “scalar expressions”, in a mild abuse of syntax.

• The OVERLAY, POSITION, and SUBSTRING functions:

  (overlay "abc" "z" 2 1)       ; OVERLAY('abc' PLACING 'z' FROM 2 FOR 1)

  (position "c" "abc")          ; POSITION('c' IN 'abc)

  (substring "abc" 2 1)         ; SUBSTRING('abc' FROM 2 FOR 1)

• The TRIM function is written using one of the following variants:

  (trim-leading "z" "zzabc")    ; TRIM(LEADING 'z' FROM 'zzabc')

  (trim-trailing "z" "abczz")   ; TRIM(TRAILING 'z' FROM 'abczz')

  (trim-both "z" "zzabczz")     ; TRIM(BOTH 'z' FROM 'zzabczz')

• The syntax COUNT(*) can be written as follows:

  (count-all)                   ; COUNT(*)

• The chaining arithmetic operators +, -, *, and /:

  (+ 1 2 3 4)                   ; 1 + 2 + 3 + 4

• The chaining infix logical operators AND and OR:

  (and x y z)                   ; x AND y AND z

  (or x y z)                    ; x OR y OR z

• The chaining infix binary operator || can be written as \|\| or as ||; the latter reads as the empty symbol.

  (|| lname ", " fname)         ; lname || ', ' || fname

  (\|\| lname ", " fname)       ; lname || ', ' || fname

• Any identifier consisting of only characters in ~!@#%^&*-_=+|<>?/ is considered a non-chaining infix binary operator:

  (< x y)                       ; x < y

  (%&! 1 2)                     ; 1 %&! 2

• The following operators:

  (not x)                       ; NOT x

  (is-null x)                   ; x IS NULL

  (is-not-null x)               ; x IS NOT NULL

  (is-true x)                   ; x IS TRUE

  (is-not-true x)               ; x IS NOT TRUE

  (is-false x)                  ; x IS FALSE

  (is-not-false x)              ; x IS NOT FALSE

  (is-unknown x)                ; x IS UNKNOWN

  (is-not-unknown x)            ; x IS NOT UNKNOWN

  (collate x utf8)              ; x COLLATE utf8

  (between-and 5 1 10)          ; 5 BETWEEN 1 AND 10

  (distinct-from x y)           ; x DISTINCT FROM y

  (like "abc" "a%")             ; 'abc' LIKE 'a%'

  (ilike "aBC" "ab_")           ; 'aBC' ILIKE 'ab_'

  (similar-to "abc" "(a|z)%")   ; 'abc' SIMILAR TO '(a|z)%'

• Field selection is written as a regular identifier (or *) prefixed by a dot.

  (.city state)                 ; (state).city

  (.* table1)                   ; (table1).*

  (.*)                          ; *

• Row constructors (the ROW syntax is a PostgreSQL extension):

  (%row 1 2 3)                  ; (1, 2, 3)

  (row 1 2 3)                   ; ROW(1, 2, 3)

• Arrays and array indexing (PostgreSQL extension):

  (%array 1 2 3)                ; ARRAY[1, 2, 3]

  (%ref x 1)                    ; (x)[1]

  (%ref x 1 2 3)                ; (x)[1,2,3]

3.2.2 Placeholders and Unquoted Parameters

There are two variants of scalar-expr that enable the construction of parameterized queries. The first is the placeholder, written ? (regardless of the notation used by the database the query is to be sent to). The second is the unquote form, which is equivalent to inserting a placeholder and also providing the expression as a query parameter.

Note: Due to limitations in the db library, unquote parameters and ordinary placeholders cannot be mixed in the same statement.

The resulting statement can be used with parameters thus:

(query-value c (select a #:from mytable #:where (= b ?)) 10)

Using the unquote form eliminates the need to keep track of positional parameters; instead, the parameter value is written as a Racket expression within the statement. It is automatically translated to SQL code containing placeholders.

The resulting statement must be called without additional parameters:

(query-value c (select a #:from mytable #:where (= b ,b-param)))

Note that placeholder syntax varies between SQL dialects. We can see the code a statement produces for a specific dialect by setting the current-sql-dialect parameter:

> (parameterize ((current-sql-dialect 'postgresql))     (sql-statement->string (select a #:from mytable #:where (= b ,b-param))))

"SELECT a FROM mytable WHERE (b = $1)"

3.3 SQL Table References and Expressions

A table reference is either a reference to a defined table (or view) or a computed table with a name or named components. A table expression can be formed using join and set operations.

Note: in the final variant of table-ref, the table-expr must be a join table expression, specifically.

3.4 SQL Statements

A statement is one of the four standard DML statements or a WITH statement that combines them with one or more common table expressions.

Select

A select-statement can contain each kind of select-clause at most once. The clauses can occur in any order. If the first form of select-statement is used (that is, with the initial select-items), then the #:values clause may not also be used.

Changed in version 1.2 of package sql: Added distinct-clause

Insert

Changed in version 1.1 of package sql: Added #:or-ignore.

Update

Delete

With

3.5 SQL DDL Statements

Changed in version 1.1 of package sql: Added #:if-not-exists option for create-table.
Changed in version 1.4: Added action-decl for foreign-key and the #:default column-option for column-def.

3.6 SQL Dialect

This parameter does not affect statement (sql-statement?) values used with connection query methods; generation of SQL code for a query method automatically uses the dialect associated with the connection the query is performed on.

3.7 Dynamic Statement Composition and SQL Injection

This library allows the dynamic composition of statements and the injection of SQL text using the following extensions to the SQL grammar.

Warning: Never use the INJECT forms to include SQL computed from an untrusted source. Use placeholders or unquote parameters instead; see Placeholders and Unquoted Parameters.

Changed in version 1.2 of package sql: Added Ident:AST and Name:AST

Added in version 1.2 of package sql.

Added in version 1.2 of package sql.

Equivalent to (scalar-expr-qq ,value).

Added in version 1.2 of package sql.

Note that all of the rows (i.e. the inner lists) must be of the same length.

Added in version 1.3 of package sql.