Common SQL supports a symbolic query syntax across both the functional and object-oriented interface layers. It allows SQL and Common Lisp expressions to be mixed together -- with as much processing as possible done at compile-time. Symbolic SQL expressions are read as square-bracketed lists to distinguish them from Lisp expressions. However, each can be nested within the other to achieve the desired result.
By default, this reader syntax is turned off. To turn it on see Utilities.
The square bracket syntax for the SQL interface is heavily overloaded to provide the most intuitive behavior in all situations. There are three uses of square brackets:
Each of these uses is demonstrated below.
Database identifiers can be enclosed in the square bracket syntax as shown in the following examples.
This case corresponds to an unqualified SQL identifier as in: SELECT FOO FROM BAR.
[foo bar] => #<SQL-IDENT "FOO.BAR">
This corresponds to a qualified SQL identifier as in: SELECT FOO.BAR FROM FOO
["foo" bar] => #<SQL-IDENT "\"foo\".BAR">
This corresponds to a qualified SQL identifier with an aliased table name containing special characters as in: SELECT "foo".BAR FROM BAZ "foo".
[foo "bar"] => #<SQL-IDENT FOO \"bar\">
This corresponds to an alias definition as in:
SELECT "bar".* FROM FOO "bar".
[foo :integer] => #<SQL-IDENT "FOO" :INTEGER>
As above, but including a type coercion component.
[foo bar :integer] -> #<SQL-IDENT "FOO.BAR" :INTEGER>
As above, but includes a type coercion component.
There are some SQL operators which may take a single argument (for example any, some, all, not, union, intersect, except, and minus). These are read as calls to the appropriate SQL operator. For example:
[any '(3 4)] -> #<SQL-VALUE-EXP "(ANY (3,4))">
This causes no conflict, however, as it is illegal to use these reserved words as identifiers in SQL. Similarly with two argument operators:
[> [baz] [beep]]
-> #<SQL-RELATIONAL-EXP "(BAZ > BEEP)">
The select statement itself may be prepared for later query execution using the [] syntax. For example:
[select [person_id] [surname] :from [person]]
This form results in a SQL expression, which could be bound to a Lisp variable and later given to query to execute. For example:
[select [foo] [bar *]
:from '([baz] [bar])
:where [or [= [foo] 3]
[> [baz.quux] 10]]]
->
#<SQL-QUERY
"(SELECT FOO,BAR.* FROM BAZ,BAR
WHERE ((FOO = 3)
OR (BAZ.QUUX > 10)))">
Strings can be inserted in place of database identifiers within a select:
[select [foo bar] [baz]
:from '([foo] [quux])
:where [or [> [baz] 3]
[like [foo bar] "SU%"]]]
->
#<SQL-QUERY:
"(SELECT FOO.BAR,BAZ
FROM FOO,QUUX
WHERE ((BAZ > 3)
OR (FOO.BAR LIKE 'SU%')))">
Any non-constant included gets filled in at runtime, for example:
[> [foo] x]
(SQL-> #<SQL-IDENT "FOO"> X)
which constructs the actual SQL string at runtime.
Any arguments to a SQL operator that are Lisp constants are translated to the matching SQL construct at compile-time, for example:
"foo" -> "'foo'"
3 -> "3"
'("this" 5 "that") -> "('this', 5, 'that')"
'xyz -> "XYZ"
SQL operators which are supported are null, exists, *, +, /, -, like, substr, and, or, not, in, all, any, some,||, =, <, > ,>=, <=, <>, order-by, count, max, min, avg, sum, minus, nvl, distinct, except, intersect, union, slot-value, between and userenv. There are also pseudo operators for calling database functions (see Calling database functions).
The general syntax is: [<operator> <operand> ...], for instance:
(select [count [*]] :from [emp])
The operand can itself be a SQL expression, as in the following example:
(sql:create-table [company]
'(([name] (varchar 20) not-null)))
(loop for company in '("LispWorks Ltd"
"Harlequin"
"Oracle"
"Rover"
"Microsoft")
do
(sql:insert-records :into [company]
:av-pairs `(([name] ,company))))
(sql:create-table [person]
'(([surname] (varchar 20) not-null)
([firstname] (varchar 20) not-null)))
(loop for person in '(("Joe" "Bloggs")
("Fred" "Smith")
("Rover" "the Dog")
("Fido" "the Dog"))
do (sql:insert-records :into [person]
:av-pairs
`(([firstname] ,(car person))
([surname] ,(second person)))))
(sql:select [name]
:from [company]
:where [= [name]
[any [select [surname]
:from [person]]]])
(sql:select [surname]
:from [person]
:set-operation [union [select [firstname]
:from [person]]])
An arbitrary function can be included in the SQL using the pseudo operator sql-function. The first argument is the function name and the rest are its arguments, for example:
(select [sql-function "COS" [age]] :from [EMPLOYEES])
(insert-records
:into [atable]
:attributes '(a b)
:values
(list 1 [sql-function "TO_DATE" "02/06/99" "mm/DD/RR"]))
Also you can call SQL infix operators using the pseudo operators sql-boolean-operator and sql-operator.
Literal SQL statements can simply be enclosed in the square bracket syntax, as shown below.
Creating a full query (which can be used as argument to query):
["SELECT FOO, BAR FROM BAZ"]
-> #<SQL "SELECT FOO, BAR FROM BAZ">
Using an unportable function condition in :where:
(sql:select [*] :from ["aTable"]
:where ["unportable_function() > 89"])
In some cases it is necessary to build SQL-expressions dynamically under program control.
The function sql-operation returns the SQL expression for an operator applied to its arguments. It also supports building SQL expressions which contain arbitrary SQL functions using the pseudo operators sql-function, sql-operator and sql-boolean-operator. For examples see sql-operation.
The function sql-expression makes a SQL expression from the given keywords. This is equivalent to the first and third uses of the [] syntax as discussed in The "[...]" Syntax.
The function sql-operator returns the Lisp symbol for a SQL operator.
The function sql makes SQL out of the arguments supplied. Each argument to sql is turned into SQL and then the args are concatenated with a single space between each pair. A Lisp string maps to the same characters enclosed between single quotes (this corresponds to a SQL string constant). nil maps to "NULL", that is, a SQL null value. Symbols and numbers map to strings. A list maps to a parenthesised, comma-separated expression. A vector maps to a comma-separated expression, which allows the easy generation of SQL lists that require no parentheses such as table lists in select statements.
The rules for the conversion are fully specified in sql.
The following example function, taken from the object-oriented SQL interface layer, makes a SQL query fragment that finds the records corresponding a CLOS object (using the slots as attributes), when built into the where -clause of an updating form.
(let* ((class (class-of object))
(key-slots (db-class-keyfields class)))
(loop
for key in key-slots
for slot-name = (slot-definition-name key)
for slot-type = (db-slot-definition-type key)
collect
[= (make-field-name class key)
(lisp-to-sql-format
(slot-value object slot-name)
(if (listp slot-type)
(car slot-type)
slot-type))]
into cols
finally (apply (sql-operator 'and) cols)))
->
#<SQL-RELATIONAL-EXP "(EMP.EMPNO = 7369">
Here is another example that produces a SQL select statement:
(sql-operation 'select
(sql-expression :table 'foo
:attribute 'bar)
(sql-expression :attribute 'baz)
:from (list
(sql-expression :table 'foo)
(sql-expression :table 'quux))
:where (sql-operation 'or
(sql-operation '>
(sql-expression :attribute 'baz)
3)
(sql-operation 'like
(sql-expression :table 'foo
:attribute 'bar)
"SU%")))
->
#<SQL-QUERY "SELECT FOO.BAR,BAZ FROM FOO,QUUX
WHERE ((BAZ > 3) OR (FOO.BAR LIKE 'SU%'))">
The function enable-sql-reader-syntax switches square bracket syntax on and sets the state so that restore-sql-reader-syntax-state restores the syntax again if it is subsequently disabled. The function disable-sql-reader-syntax switches square bracket syntax off and sets the state so that restore-sql-reader-syntax-state disables the syntax again if it is subsequently enabled.
The functions locally-enable-sql-reader-syntax and locally-disable-sql-reader-syntax switch square bracket syntax on and off, but do not change the state restored by restore-sql-reader-syntax-state. The intended use of these is in a file:
#.(locally-enable-sql-reader-syntax)
<code using [...]>
#.(restore-sql-reader-syntax-state)
LispWorks User Guide and Reference Manual - 13 Feb 2015