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 are specified in the "[...]" syntax using the following rules:
There must be one, two or three Lisp forms inside the square brackets. The first form must be a symbol, string or a recursive database identifier (that is, another square brackets expression). The second form, if present, must be a symbol or a string. The third form, if present, must be a keyword.
The case with a single form that is a string is special, and is interpreted as a direct SQL expression rather than an identifier (see Enclosing a SQL expression directly below).
When a string or a symbol is used to specify all or part of the identifier and the string (or name of the symbol) cannot be used as an identifier (because it contains special characters or matches a SQL reserved word), then it is wrapped with double quotes in the resulting SQL.
If there is more than one form inside the square brackets, and the first form is a symbol that is recognized as a SQL operator or a pseudo-operator, then the expression is interpreted as an operation rather than as an identifier (see the following sections).
The first form is always interpreted as specifying a string that is part or all of the identifier. For a symbol, it is the symbol name and for a recursive identifier it is the string that would be generated for this identifier. In the examples below, the text following the => (and optionally up to the semicolon) shows what is generated for the resulting SQL.
If there is only one form, it specifies the full name of the identifier. For example:
=> FOO
=> foo
=> FOO
=> W%((jj ; single form string not quoted.
If the second form is a string and the first form is not a string, then the first form specifies the name of the identifier and the second form specifies an alias. In this case there must not be a third form. The alias identifier is useful for giving tables aliases in the from part of the SQL select statement:
=> FOO AA
=> FOO.AA bb ; first form is recursive.
If there is a third form, or the second form is not keyword, or the first form is a string, then the second form specifies an identifier qualified by the first form, that is they are combined with a period in the middle:
=> FOO.AA
=> FOO.AA ; with type :integer (below).
=> foo.AA ; compare to [foo "AA"] above.
If there are only two forms and the second form is a keyword, or there are three forms, then the second form (in the two form case) or the third form (in the three form case) specifies a type associated with the identifier. The type does not affect the SQL statement that the database sees. It is used when the identifier is part of the selection list, to tell Common SQL what type the value should be. Such identifiers should appear only in the selection list of queries.
=> COLUMNNAME ; type :integer.
[[TableName ColumnName] :string]
=> TABLENAME.COLUMNNAME ; type :string.
[TableName ColumnName :string]
=> TABLENAME.COLUMNNAME ; type :string (same as previous).
Inside select (which is recognized as a SQL operator):
[select [id :integer] [name :string] :from [TableName]]
=> SELECT ID, NAME FROM TABLENAME ; interpret ID as an integer and NAME as a string.
[[TableName ColumnName] "MyAlias" :string]
=> TABLENAME.COLUMNNAME MyAlias ; type :string
[[[[CatalogName SchemaName] TableName] ColumnName] "MyAlias" :string]
=> CATALOGNAME.SCHEMANAME.TABLENAME.COLUMNNAME MyAlias ; type :string.
=> "W%((jj".AA ; string is quoted.
=> W%((jj.AA ; string not quoted because it is a single form.
When you use a keyword to specify the type of an expression as described in Enclosing database identifiers, you are telling common SQL that the values retrieved for this expression should be of a specific type. For example, if you call:
(sql:select [name :string] :from [TableName])
then the :string keyword tells common SQL that the values for name should be strings.
There are four keywords that are supported by all common SQL backends: :string, integer, :double-float and :single-float. For each of these keywords, the values are mapped to the matching Common Lisp type. If this is not possible, the value is returned as nil.
Note that if you specify a keyword that is incompatible with the type in the database column then either an error is signaled or all returned values will be nil.
The keyword :int is accepted as an alias for :integer.
The keyword :binary is supported by most of backends (except Microsoft Access and PostgreSQL). The value that is returned for :binary is an array with element type (unsigned-byte 8). On Oracle, :binary can be used only for columns of binary type, so it is only useful when you want to retrieve the contents of a BLOB directly, because for plain RAW columns it is the default anyway. Other backends allow you to retrieve at least strings as binary values.
Other keywords are supported by some of the backends, and are documented in the backend specific sections.
When the first form in the square brackets is a symbol that is one of the SQL operators listed below, the expression is interpreted as an operation. For example:
[any '(3 4)] -> #<SQL-VALUE-EXP "(ANY (3,4))">
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 run time, for example:
[> [foo] x]
(SQL-> #<SQL-IDENT "FOO"> X)
which constructs the actual SQL string at run time.
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:
(sql: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.
An SQL expression can simply be enclosed directly 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 non-portable function condition in :where:
(sql:select [*] :from ["aTable"]
:where ["non_portable_function() > 89"])
SQL string literals can be used as arguments to operators, for example with a constant Lisp string:
[= [name] "John"]
or with a Lisp expression that evaluates to string:
(defun find-person-age (name)
(car (select [age] from [table]
:where [= [name] name])))
where the argument name is a string.
However, Microsoft SQL Server (which can be used via ODBC) requires the N syntax for string literal that are not entirely ASCII, or contain characters that are not recognized by the server code page. (The N syntax prefixes the string literal by the character N, for example N'Greek', rather than 'Greek'.) Although this syntax is part of the SQL standard, not all SQL backends accept it (in particular, SQLite and Microsoft Access (via ODBC) do not). Thus the decision whether to use the N syntax needs to be made at run time and requires the SQL backend (which is represented by the database object that connect returns). By default, the symbolic SQL syntax does not use the N syntax, but the special pseudo-operator string can be used to override this. string takes a required argument, which must be a string, and an optional argument, a database (which defaults to *default-database*), and produces the appropriate syntax for that database. The example above can be written using string like this:
(defun find-person-age (name)
(car (select [age] from [table]
:where [= [name] [string name]])))
The same database must be used for the string pseudo-operator and the function/macro that uses the resulting expression. In the example above, the function is select and the database not specified at all, so both string and select will use *default-database*. This restriction means that the string pseudo-operator cannot be used to generate a pre-existing expression, which is otherwise possible with the symbolic SQL syntax. For example, your code might contain:
(defvar *match-name-starting-with-cf* [like [name] "CF%"])
which defines *match-name-starting-with-cf* at load time, and then use it elsewhere:
(defun some-function (arg1 ..)
..
(select [*] :from [table]
:where *match-name-starting-with-cf*)
..
)
But if you use [string "CF%"] in the defvar, it will try to use the database at load time, which is normally before the database is connected.
You can perform approximately what the string pseudo-operator does by using string-prefix-with-n-if-needed:
(let ((maybe-qualified
(string-prefix-with-n-if-needed name))
(car (select [age] from [table]
:where [= [name] maybe-qualified])))
Another option is to set the variable *use-n-syntax-for-non-ascii-strings* to t at compile time, which causes all string literals that are not entirely ASCII to be produced with N syntax. That would generate code that will work with almost all SQL backends, but not with SQLite or Microsoft Access (which do not support the N syntax). The advantage is that, if you have a large number of string literals, then you do not have to change them all: you just need to recompile your code with *use-n-syntax-for-non-ascii-strings* set to t.
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 - 20 Sep 2017