2 <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN"
3 "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd" [
4 <!ENTITY % myents SYSTEM "entities.inc">
8 <!-- Symbolic SQL Syntax -->
9 <reference id="ref-syntax">
10 <title>The Symbolic SQL Syntax</title>
13 &clsql; provides a symbolic syntax allowing the construction of
14 SQL expressions as lists delimited by square brackets. The
15 syntax is turned off by default. This section describes
16 utilities for enabling and disabling the square bracket reader
17 syntax and for constructing symbolic SQL expressions.
21 <refentry id="enable-sql-reader-syntax">
23 <refentrytitle>ENABLE-SQL-READER-SYNTAX</refentrytitle>
26 <refname><emphasis>Macro</emphasis> <emphasis role="bold">ENABLE-SQL-READER-SYNTAX</emphasis></refname>
27 <refpurpose>Globally enable square bracket reader syntax.</refpurpose>
28 <refclass>Macro</refclass>
33 <function>enable-sql-reader-syntax</function> => <returnvalue></returnvalue></synopsis>
36 <title>Arguments and Values</title>
40 <title>Description</title>
41 <para>Turns on the SQL reader syntax setting the syntax state
42 such that if the syntax is subsequently disabled, <link
43 linkend="restore-sql-reader-syntax-state">
44 <function>restore-sql-reader-syntax-state</function></link> will
49 <title>Examples</title>
53 <title>Side Effects</title>
55 Sets the internal syntax state to enabled.
58 Modifies the default readtable.
62 <title>Affected by</title>
68 <title>Exceptional Situations</title>
74 <title>See Also</title>
76 <member><link linkend="disable-sql-reader-syntax"><function>disable-sql-reader-syntax</function></link></member>
77 <member><link linkend="locally-enable-sql-reader-syntax"><function>locally-enable-sql-reader-syntax</function></link></member>
78 <member><link linkend="locally-disable-sql-reader-syntax"><function>locally-disable-sql-reader-syntax</function></link></member>
79 <member><link linkend="restore-sql-reader-syntax-state"><function>restore-sql-reader-syntax-state</function></link></member>
85 The symbolic SQL syntax is disabled by default.
90 <refentry id="disable-sql-reader-syntax">
92 <refentrytitle>DISABLE-SQL-READER-SYNTAX</refentrytitle>
95 <refname><emphasis>Macro</emphasis> <emphasis role="bold">DISABLE-SQL-READER-SYNTAX</emphasis></refname>
96 <refpurpose>Globally disable square bracket reader syntax.</refpurpose>
97 <refclass>Macro</refclass>
100 <title>Syntax</title>
102 <function>disable-sql-reader-syntax</function> => <returnvalue></returnvalue></synopsis>
105 <title>Arguments and Values</title>
109 <title>Description</title>
110 <para>Turns off the SQL reader syntax setting the syntax state
111 such that if the syntax is subsequently enabled,
112 <link linkend="restore-sql-reader-syntax-state">
113 <function>restore-sql-reader-syntax-state</function></link> will
118 <title>Examples</title>
122 <title>Side Effects</title>
124 Sets the internal syntax state to disabled.
127 Modifies the default readtable.
131 <title>Affected by</title>
137 <title>Exceptional Situations</title>
143 <title>See Also</title>
145 <member><link linkend="enable-sql-reader-syntax"><function>enable-sql-reader-syntax</function></link></member>
146 <member><link linkend="locally-enable-sql-reader-syntax"><function>locally-enable-sql-reader-syntax</function></link></member>
147 <member><link linkend="locally-disable-sql-reader-syntax"><function>locally-disable-sql-reader-syntax</function></link></member>
148 <member><link linkend="restore-sql-reader-syntax-state"><function>restore-sql-reader-syntax-state</function></link></member>
154 The symbolic SQL syntax is disabled by default.
159 <refentry id="locally-enable-sql-reader-syntax">
161 <refentrytitle>LOCALLY-ENABLE-SQL-READER-SYNTAX</refentrytitle>
164 <refname><emphasis>Macro</emphasis> <emphasis role="bold">LOCALLY-ENABLE-SQL-READER-SYNTAX</emphasis></refname>
165 <refpurpose>Globally enable square bracket reader syntax.</refpurpose>
166 <refclass>Macro</refclass>
169 <title>Syntax</title>
171 <function>locally-enable-sql-reader-syntax</function> => <returnvalue></returnvalue></synopsis>
174 <title>Arguments and Values</title>
178 <title>Description</title>
179 <para>Turns on the SQL reader syntax without changing the syntax
180 state such that <link linkend="restore-sql-reader-syntax-state">
181 <function>restore-sql-reader-syntax-state</function></link> will
182 re-establish the current syntax state.
186 <title>Examples</title>
187 <para>Intended to be used in a file for code which uses the
188 square bracket syntax without changing the global state.
191 #.(locally-enable-sql-reader-syntax)
193 ... CODE USING SYMBOLIC SQL SYNTAX ...
195 #.(restore-sql-reader-syntax-state)
199 <title>Side Effects</title>
201 Modifies the default readtable.
205 <title>Affected by</title>
209 <title>Exceptional Situations</title>
215 <title>See Also</title>
217 <member><link linkend="enable-sql-reader-syntax"><function>enable-sql-reader-syntax</function></link></member>
218 <member><link linkend="disable-sql-reader-syntax"><function>disable-sql-reader-syntax</function></link></member>
219 <member><link linkend="locally-disable-sql-reader-syntax"><function>locally-disable-sql-reader-syntax</function></link></member>
220 <member><link linkend="restore-sql-reader-syntax-state"><function>restore-sql-reader-syntax-state</function></link></member>
226 The symbolic SQL syntax is disabled by default.
232 <refentry id="locally-disable-sql-reader-syntax">
234 <refentrytitle>LOCALLY-DISABLE-SQL-READER-SYNTAX</refentrytitle>
237 <refname><emphasis>Macro</emphasis> <emphasis role="bold">LOCALLY-DISABLE-SQL-READER-SYNTAX</emphasis></refname>
238 <refpurpose>Locally disable square bracket reader syntax.</refpurpose>
239 <refclass>Macro</refclass>
242 <title>Syntax</title>
244 <function>locally-disable-sql-reader-syntax</function> => <returnvalue></returnvalue></synopsis>
247 <title>Arguments and Values</title>
251 <title>Description</title>
252 <para>Turns off the SQL reader syntax without changing the
253 syntax state such that <link
254 linkend="restore-sql-reader-syntax-state">
255 <function>restore-sql-reader-syntax-state</function></link> will
256 re-establish the current syntax state.
260 <title>Examples</title>
261 <para>Intended to be used in a file for code in which the square
262 bracket syntax should be disabled without changing the global
266 #.(locally-disable-sql-reader-syntax)
268 ... CODE NOT USING SYMBOLIC SQL SYNTAX ...
270 #.(restore-sql-reader-syntax-state)
274 <title>Side Effects</title>
276 Modifies the default readtable.
280 <title>Affected by</title>
286 <title>Exceptional Situations</title>
292 <title>See Also</title>
294 <member><link linkend="enable-sql-reader-syntax"><function>enable-sql-reader-syntax</function></link></member>
295 <member><link linkend="disable-sql-reader-syntax"><function>disable-sql-reader-syntax</function></link></member>
296 <member><link linkend="locally-enable-sql-reader-syntax"><function>locally-enable-sql-reader-syntax</function></link></member>
297 <member><link linkend="restore-sql-reader-syntax-state"><function>restore-sql-reader-syntax-state</function></link></member>
303 The symbolic SQL syntax is disabled by default.
308 <refentry id="restore-sql-reader-syntax-state">
310 <refentrytitle>RESTORE-SQL-READER-SYNTAX-STATE</refentrytitle>
313 <refname><emphasis>Macro</emphasis> <emphasis role="bold">RESTORE-SQL-READER-SYNTAX-STATE</emphasis></refname>
315 Restore square bracket reader syntax to its previous state.
317 <refclass>Macro</refclass>
320 <title>Syntax</title>
322 <function>restore-sql-reader-syntax-state</function> => <returnvalue></returnvalue></synopsis>
325 <title>Arguments and Values</title>
329 <title>Description</title>
330 <para>Enables the SQL reader syntax if <link
331 linkend="enable-sql-reader-syntax">
332 <function>enable-sql-reader-syntax</function></link> has been
333 called more recently than <link
334 linkend="disable-sql-reader-syntax">
335 <function>disable-sql-reader-syntax</function></link> and
336 otherwise disables the SQL reader syntax. By default, the SQL
337 reader syntax is disabled.
341 <title>Examples</title>
344 linkend="locally-enable-sql-reader-syntax">
345 <function>locally-enable-sql-reader-syntax</function></link> and
347 linkend="locally-disable-sql-reader-syntax">
348 <function>locally-disable-sql-reader-syntax</function></link>.
352 <title>Side Effects</title>
354 Reverts the internal syntax state.
357 Modifies the default readtable.
361 <title>Affected by</title>
362 <para>The current internal syntax state.</para>
365 <title>Exceptional Situations</title>
371 <title>See Also</title>
373 <member><link linkend="enable-sql-reader-syntax"><function>enable-sql-reader-syntax</function></link></member>
374 <member><link linkend="disable-sql-reader-syntax"><function>disable-sql-reader-syntax</function></link></member>
375 <member><link linkend="locally-enable-sql-reader-syntax"><function>locally-enable-sql-reader-syntax</function></link></member>
376 <member><link linkend="locally-disable-sql-reader-syntax"><function>locally-disable-sql-reader-syntax</function></link></member>
382 The symbolic SQL syntax is disabled by default.
389 <refentrytitle>SQL</refentrytitle>
392 <refname><emphasis>Function</emphasis> <emphasis role="bold">SQL</emphasis></refname>
393 <refpurpose>Construct an SQL string from supplied expressions.</refpurpose>
394 <refclass>Function</refclass>
397 <title>Syntax</title>
399 <function>sql &rest</function> <replaceable>args</replaceable> => <returnvalue>sql-expression</returnvalue></synopsis>
402 <title>Arguments and Values</title>
405 <term><parameter>args</parameter></term>
407 <para>A set of expressions.</para>
411 <term><returnvalue>sql-expression</returnvalue></term>
413 <para>A string representing an SQL expression.</para>
419 <title>Description</title>
420 <para>Returns an SQL string generated from the expressions
421 <parameter>args</parameter>. The expressions are translated into
422 SQL strings and then concatenated with a single space delimiting
427 <title>Examples</title>
441 (sql '(nil foo "bar" 10))
442 => "(NULL,FOO,'bar',10)"
444 (sql #(nil foo "bar" 10))
445 => "NULL,FOO,'bar',10"
447 (sql [select [foo] [bar] :from [baz]] 'having [= [foo id] [bar id]]
448 'and [foo val] '< 5)
449 => "SELECT FOO,BAR FROM BAZ HAVING (FOO.ID = BAR.ID) AND FOO.VAL < 5"
453 <title>Side Effects</title>
457 <title>Affected by</title>
463 <title>Exceptional Situations</title>
464 <para>An error of type <link
465 linkend="sql-user-error"><function>sql-user-error</function></link>
466 is signalled if any element in <parameter>args</parameter> is
467 not of the supported types (a symbol, string, number or symbolic
468 SQL expression) or a list or vector containing only these
473 <title>See Also</title>
475 <member><link linkend="sql-expression"><function>sql-expression</function></link></member>
476 <member><link linkend="sql-operation"><function>sql-operation</function></link></member>
477 <member><link linkend="sql-operator"><function>sql-operator</function></link></member>
486 <refentry id="sql-expression">
488 <refentrytitle>SQL-EXPRESSION</refentrytitle>
491 <refname><emphasis>Function</emphasis> <emphasis role="bold">SQL-EXPRESSION</emphasis></refname>
492 <refpurpose>Constructs an SQL expression from supplied keyword arguments.</refpurpose>
493 <refclass>Function</refclass>
496 <title>Syntax</title>
498 <function>sql-expression &key</function> <parameter>string</parameter> <parameter>table</parameter> <parameter>alias</parameter> <parameter>attribute</parameter> <parameter>type</parameter> => <returnvalue>result</returnvalue></synopsis>
501 <title>Arguments and Values</title>
504 <term><parameter>string</parameter></term>
506 <para>A string.</para>
510 <term><parameter>table</parameter></term>
512 <para>A symbol representing a database table identifier.</para>
516 <term><parameter>alias</parameter></term>
518 <para>A table alias.</para>
522 <term><parameter>attribute</parameter></term>
524 <para>A symbol representing an attribute identifier.</para>
528 <term><parameter>type</parameter></term>
530 <para>A type specifier.</para>
534 <term><returnvalue>result</returnvalue></term>
536 <para>A object of type <type>sql-expression</type>.</para>
542 <title>Description</title>
543 <para>Returns an SQL expression constructed from the supplied
544 arguments which may be combined as follows:</para>
545 <itemizedlist mark='opencircle'>
548 <parameter>attribute</parameter> and
549 <parameter>type</parameter>;
554 <parameter>attribute</parameter>;
559 <parameter>alias</parameter> or <parameter>table</parameter> and
560 <parameter>attribute</parameter> and
561 <parameter>type</parameter>;
566 <parameter>alias</parameter> or
567 <parameter>table</parameter> and
568 <parameter>attribute</parameter>;
573 <parameter>table</parameter>,
574 <parameter>attribute</parameter> and
575 <parameter>type</parameter>;
580 <parameter>table</parameter> and
581 <parameter>attribute</parameter>;
586 <parameter>table</parameter>
587 and <parameter>alias</parameter>;
592 <parameter>table</parameter>;
597 <parameter>string</parameter>.
603 <title>Examples</title>
605 (sql-expression :table 'foo :attribute 'bar)
606 => #<CLSQL-SYS:SQL-IDENT-ATTRIBUTE FOO.BAR>
608 (sql-expression :attribute 'baz)
609 => #<CLSQL-SYS:SQL-IDENT-ATTRIBUTE BAZ>
613 <title>Side Effects</title>
617 <title>Affected by</title>
623 <title>Exceptional Situations</title>
624 <para>An error of type <link
625 linkend="sql-user-error"><function>sql-user-error</function></link>
626 is signalled if an unsupported combination of keyword arguments is
631 <title>See Also</title>
633 <member><link linkend="sql"><function>sql</function></link></member>
634 <member><link linkend="sql-operation"><function>sql-operation</function></link></member>
635 <member><link linkend="sql-operator"><function>sql-operator</function></link></member>
644 <refentry id="sql-operation">
646 <refentrytitle>SQL-OPERATION</refentrytitle>
649 <refname><emphasis>Function</emphasis> <emphasis role="bold">SQL-OPERATION</emphasis></refname>
650 <refpurpose>Constructs an SQL expression from a supplied operator and arguments.</refpurpose>
651 <refclass>Function</refclass>
654 <title>Syntax</title>
656 <function>sql-operation</function> <parameter>operator</parameter> <function>&rest</function> <parameter>args</parameter> => <returnvalue>result</returnvalue></synopsis>
658 <function>sql-operation</function> 'function <parameter>func</parameter> <function>&rest</function> <parameter>args</parameter> => <returnvalue>result</returnvalue></synopsis>
661 <title>Arguments and Values</title>
664 <term><parameter>operator</parameter></term>
666 <para>A symbol denoting an SQL operator.</para>
670 <term><parameter>func</parameter></term>
672 <para>A string denoting an SQL function.</para>
676 <term><parameter>args</parameter></term>
678 <para>A set of arguments for the specified SQL operator or function.</para>
682 <term><returnvalue>result</returnvalue></term>
684 <para>A object of type <function>sql-expression</function>.</para>
690 <title>Description</title>
691 <para>Returns an SQL expression constructed from the supplied
692 SQL operator or function <parameter>operator</parameter> and
693 its arguments <parameter>args</parameter>. If
694 <parameter>operator</parameter> is passed the symbol 'function
695 then the first value in <parameter>args</parameter> is taken to
696 be a valid SQL function and the remaining values in
697 <parameter>args</parameter> its arguments.
701 <title>Examples</title>
703 (sql-operation 'select
704 (sql-expression :table 'foo :attribute 'bar)
705 (sql-operation 'sum (sql-expression :table 'foo :attribute 'baz))
707 (sql-expression :table 'foo)
709 (sql-operation '> (sql-expression :attribute 'bar) 12)
710 :order-by (sql-operation 'sum (sql-expression :attribute 'baz)))
711 => #<SQL-QUERY SELECT FOO.BAR,SUM(FOO.BAZ) FROM FOO WHERE (BAR > 12) ORDER BY SUM(BAZ)>
713 (sql-operation 'function "strpos" "CLSQL" "SQL")
714 => #<CLSQL-SYS:SQL-FUNCTION-EXP STRPOS('CLSQL','SQL')>
718 <title>Side Effects</title>
722 <title>Affected by</title>
728 <title>Exceptional Situations</title>
729 <para>An error of type <link
730 linkend="sql-user-error"><function>sql-user-error</function></link>
731 is signalled if <parameter>operator</parameter> is not a symbol
732 representing a supported SQL operator.</para>
735 <title>See Also</title>
737 <member><link linkend="sql"><function>sql</function></link></member>
738 <member><link linkend="sql-expression"><function>sql-expression</function></link></member>
739 <member><link linkend="sql-operator"><function>sql-operator</function></link></member>
748 <refentry id="sql-operator">
750 <refentrytitle>SQL-OPERATOR</refentrytitle>
753 <refname><emphasis>Function</emphasis> <emphasis role="bold">SQL-OPERATOR</emphasis></refname>
754 <refpurpose>Returns the symbol for the supplied SQL operator.</refpurpose>
755 <refclass>Function</refclass>
758 <title>Syntax</title>
760 <function>sql-operator</function> <parameter>operator</parameter> => <returnvalue>result</returnvalue></synopsis>
763 <title>Arguments and Values</title>
766 <term><parameter>operator</parameter></term>
768 <para>A symbol denoting an SQL operator.</para>
772 <term><returnvalue>result</returnvalue></term>
774 <para>The Lisp symbol used by &clsql; to represent the
775 specified operator.</para>
781 <title>Description</title>
782 <para>Returns the Lisp symbol corresponding to the SQL operator
783 represented by the symbol <parameter>operator</parameter>. If
784 <parameter>operator</parameter> does not represent a supported
785 SQL operator or is not a symbol, nil is returned.
789 <title>Examples</title>
796 <title>Side Effects</title>
800 <title>Affected by</title>
806 <title>Exceptional Situations</title>
810 <title>See Also</title>
812 <member><link linkend="sql"><function>sql</function></link></member>
813 <member><link linkend="sql-expression"><function>sql-expression</function></link></member>
814 <member><link linkend="sql-operation"><function>sql-operation</function></link></member>
819 <para>&clsql;'s symbolic SQL syntax currently has support for
820 the following SQL operators:
823 <!-- CommonSQL Compatible -->
824 <member><function>any
825 </function></member>,
826 <member><function>some
827 </function></member>,
828 <member><function>all
829 </function></member>,
830 <member><function>not
831 </function></member>,
832 <member><function>union
833 </function></member>,
834 <member><function>intersect
835 </function></member>,
836 <member><function>minus
837 </function></member>,
838 <member><function>except
839 </function></member>,
840 <member><function>order-by
841 </function></member>,
842 <member><function>null
843 </function></member>,
845 </function></member>,
847 </function></member>,
849 </function></member>,
851 </function></member>,
852 <member><function>like
853 </function></member>,
854 <member><function>and
855 </function></member>,
857 </function></member>,
859 </function></member>,
860 <member><function>substr
861 </function></member>,
863 </function></member>,
865 </function></member>,
866 <member><function><
867 </function></member>,
869 </function></member>,
871 </function></member>,
872 <member><function><=
873 </function></member>,
874 <member><function><>
875 </function></member>,
876 <member><function>count
877 </function></member>,
878 <member><function>max
879 </function></member>,
880 <member><function>min
881 </function></member>,
882 <member><function>avg
883 </function></member>,
884 <member><function>sum
885 </function></member>,
886 <member><function>function
887 </function></member>,
888 <member><function>between
889 </function></member>,
890 <member><function>distinct
891 </function></member>,
892 <member><function>nvl
893 </function></member>,
894 <member><function>slot-value
895 </function></member>,
896 <member><function>userenv
897 <!-- CLSQL Extensions -->
898 </function></member>,
899 <member><function>concat
900 </function></member>,
901 <member><function>substring
902 </function></member>,
903 <member><function>limit
904 </function></member>,
905 <member><function>group-by
906 </function></member>,
907 <member><function>having
908 </function></member>,
909 <member><function>not-null
910 </function></member>,
911 <member><function>exists
912 </function></member>,
913 <member><function>uplike
914 </function></member>,
916 </function></member>,
918 </function></member>,
919 <member><function>the
920 </function></member>,
921 <member><function>coalesce
922 </function></member>,
923 <member><function>view-class
926 <para>as well as the pseudo-operator
927 <function>function</function>. Note that some of these operators
928 are not supported by all of the RDBMS supported by