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.
88 &clsql; differs from &commonsql; in that
89 <function>enable-sql-reader-syntax</function> is defined as a
90 macro rather than a function.
95 <refentry id="disable-sql-reader-syntax">
97 <refentrytitle>DISABLE-SQL-READER-SYNTAX</refentrytitle>
100 <refname><emphasis>Macro</emphasis> <emphasis role="bold">DISABLE-SQL-READER-SYNTAX</emphasis></refname>
101 <refpurpose>Globally disable square bracket reader syntax.</refpurpose>
102 <refclass>Macro</refclass>
105 <title>Syntax</title>
107 <function>disable-sql-reader-syntax</function> => <returnvalue></returnvalue></synopsis>
110 <title>Arguments and Values</title>
114 <title>Description</title>
115 <para>Turns off the SQL reader syntax setting the syntax state
116 such that if the syntax is subsequently enabled,
117 <link linkend="restore-sql-reader-syntax-state">
118 <function>restore-sql-reader-syntax-state</function></link> will
123 <title>Examples</title>
127 <title>Side Effects</title>
129 Sets the internal syntax state to disabled.
132 Modifies the default readtable.
136 <title>Affected by</title>
142 <title>Exceptional Situations</title>
148 <title>See Also</title>
150 <member><link linkend="enable-sql-reader-syntax"><function>enable-sql-reader-syntax</function></link></member>
151 <member><link linkend="locally-enable-sql-reader-syntax"><function>locally-enable-sql-reader-syntax</function></link></member>
152 <member><link linkend="locally-disable-sql-reader-syntax"><function>locally-disable-sql-reader-syntax</function></link></member>
153 <member><link linkend="restore-sql-reader-syntax-state"><function>restore-sql-reader-syntax-state</function></link></member>
159 The symbolic SQL syntax is disabled by default.
162 &clsql; differs from &commonsql; in that
163 <function>disable-sql-reader-syntax</function> is defined as a
164 macro rather than a function.
169 <refentry id="locally-enable-sql-reader-syntax">
171 <refentrytitle>LOCALLY-ENABLE-SQL-READER-SYNTAX</refentrytitle>
174 <refname><emphasis>Macro</emphasis> <emphasis role="bold">LOCALLY-ENABLE-SQL-READER-SYNTAX</emphasis></refname>
175 <refpurpose>Globally enable square bracket reader syntax.</refpurpose>
176 <refclass>Macro</refclass>
179 <title>Syntax</title>
181 <function>locally-enable-sql-reader-syntax</function> => <returnvalue></returnvalue></synopsis>
184 <title>Arguments and Values</title>
188 <title>Description</title>
189 <para>Turns on the SQL reader syntax without changing the syntax
190 state such that <link linkend="restore-sql-reader-syntax-state">
191 <function>restore-sql-reader-syntax-state</function></link> will
192 re-establish the current syntax state.
196 <title>Examples</title>
197 <para>Intended to be used in a file for code which uses the
198 square bracket syntax without changing the global state.
201 #.(locally-enable-sql-reader-syntax)
203 ... CODE USING SYMBOLIC SQL SYNTAX ...
205 #.(restore-sql-reader-syntax-state)
209 <title>Side Effects</title>
211 Modifies the default readtable.
215 <title>Affected by</title>
219 <title>Exceptional Situations</title>
225 <title>See Also</title>
227 <member><link linkend="enable-sql-reader-syntax"><function>enable-sql-reader-syntax</function></link></member>
228 <member><link linkend="disable-sql-reader-syntax"><function>disable-sql-reader-syntax</function></link></member>
229 <member><link linkend="locally-disable-sql-reader-syntax"><function>locally-disable-sql-reader-syntax</function></link></member>
230 <member><link linkend="restore-sql-reader-syntax-state"><function>restore-sql-reader-syntax-state</function></link></member>
236 The symbolic SQL syntax is disabled by default.
239 &clsql; differs from &commonsql; in that
240 <function>locally-enable-sql-reader-syntax</function> is
241 defined as a macro rather than a function.
247 <refentry id="locally-disable-sql-reader-syntax">
249 <refentrytitle>LOCALLY-DISABLE-SQL-READER-SYNTAX</refentrytitle>
252 <refname><emphasis>Macro</emphasis> <emphasis role="bold">LOCALLY-DISABLE-SQL-READER-SYNTAX</emphasis></refname>
253 <refpurpose>Locally disable square bracket reader syntax.</refpurpose>
254 <refclass>Macro</refclass>
257 <title>Syntax</title>
259 <function>locally-disable-sql-reader-syntax</function> => <returnvalue></returnvalue></synopsis>
262 <title>Arguments and Values</title>
266 <title>Description</title>
267 <para>Turns off the SQL reader syntax without changing the
268 syntax state such that <link
269 linkend="restore-sql-reader-syntax-state">
270 <function>restore-sql-reader-syntax-state</function></link> will
271 re-establish the current syntax state.
275 <title>Examples</title>
276 <para>Intended to be used in a file for code in which the square
277 bracket syntax should be disabled without changing the global
281 #.(locally-disable-sql-reader-syntax)
283 ... CODE NOT USING SYMBOLIC SQL SYNTAX ...
285 #.(restore-sql-reader-syntax-state)
289 <title>Side Effects</title>
291 Modifies the default readtable.
295 <title>Affected by</title>
301 <title>Exceptional Situations</title>
307 <title>See Also</title>
309 <member><link linkend="enable-sql-reader-syntax"><function>enable-sql-reader-syntax</function></link></member>
310 <member><link linkend="disable-sql-reader-syntax"><function>disable-sql-reader-syntax</function></link></member>
311 <member><link linkend="locally-enable-sql-reader-syntax"><function>locally-enable-sql-reader-syntax</function></link></member>
312 <member><link linkend="restore-sql-reader-syntax-state"><function>restore-sql-reader-syntax-state</function></link></member>
318 The symbolic SQL syntax is disabled by default.
321 &clsql; differs from &commonsql; in that
322 <function>locally-disable-sql-reader-syntax</function> is
323 defined as a macro rather than a function.
328 <refentry id="restore-sql-reader-syntax-state">
330 <refentrytitle>RESTORE-SQL-READER-SYNTAX-STATE</refentrytitle>
333 <refname><emphasis>Macro</emphasis> <emphasis role="bold">RESTORE-SQL-READER-SYNTAX-STATE</emphasis></refname>
335 Restore square bracket reader syntax to its previous state.
337 <refclass>Macro</refclass>
340 <title>Syntax</title>
342 <function>restore-sql-reader-syntax-state</function> => <returnvalue></returnvalue></synopsis>
345 <title>Arguments and Values</title>
349 <title>Description</title>
350 <para>Enables the SQL reader syntax if <link
351 linkend="enable-sql-reader-syntax">
352 <function>enable-sql-reader-syntax</function></link> has been
353 called more recently than <link
354 linkend="disable-sql-reader-syntax">
355 <function>disable-sql-reader-syntax</function></link> and
356 otherwise disables the SQL reader syntax. By default, the SQL
357 reader syntax is disabled.
361 <title>Examples</title>
364 linkend="locally-enable-sql-reader-syntax">
365 <function>locally-enable-sql-reader-syntax</function></link> and
367 linkend="locally-disable-sql-reader-syntax">
368 <function>locally-disable-sql-reader-syntax</function></link>.
372 <title>Side Effects</title>
374 Reverts the internal syntax state.
377 Modifies the default readtable.
381 <title>Affected by</title>
382 <para>The current internal syntax state.</para>
385 <title>Exceptional Situations</title>
391 <title>See Also</title>
393 <member><link linkend="enable-sql-reader-syntax"><function>enable-sql-reader-syntax</function></link></member>
394 <member><link linkend="disable-sql-reader-syntax"><function>disable-sql-reader-syntax</function></link></member>
395 <member><link linkend="locally-enable-sql-reader-syntax"><function>locally-enable-sql-reader-syntax</function></link></member>
396 <member><link linkend="locally-disable-sql-reader-syntax"><function>locally-disable-sql-reader-syntax</function></link></member>
402 The symbolic SQL syntax is disabled by default.
405 &clsql; differs from &commonsql; in that
406 <function>restore-sql-reader-syntax-state</function> is
407 defined as a macro rather than a function.
414 <refentrytitle>SQL</refentrytitle>
417 <refname><emphasis>Function</emphasis> <emphasis role="bold">SQL</emphasis></refname>
418 <refpurpose>Construct an SQL string from supplied expressions.</refpurpose>
419 <refclass>Function</refclass>
422 <title>Syntax</title>
424 <function>sql &rest</function> <replaceable>args</replaceable> => <returnvalue>sql-expression</returnvalue></synopsis>
427 <title>Arguments and Values</title>
430 <term><parameter>args</parameter></term>
432 <para>A set of expressions.</para>
436 <term><returnvalue>sql-expression</returnvalue></term>
438 <para>A string representing an SQL expression.</para>
444 <title>Description</title>
445 <para>Returns an SQL string generated from the expressions
446 <parameter>args</parameter>. The expressions are translated into
447 SQL strings and then concatenated with a single space delimiting
452 <title>Examples</title>
466 (sql '(nil foo "bar" 10))
467 => "(NULL,FOO,'bar',10)"
469 (sql #(nil foo "bar" 10))
470 => "NULL,FOO,'bar',10"
472 (sql [select [foo] [bar] :from [baz]] 'having [= [foo id] [bar id]]
473 'and [foo val] '< 5)
474 => "SELECT FOO,BAR FROM BAZ HAVING (FOO.ID = BAR.ID) AND FOO.VAL < 5"
478 <title>Side Effects</title>
482 <title>Affected by</title>
488 <title>Exceptional Situations</title>
489 <para>An error of type <link
490 linkend="sql-user-error"><function>sql-user-error</function></link>
491 is signalled if any element in <parameter>args</parameter> is
492 not of the supported types (a symbol, string, number or symbolic
493 SQL expression) or a list or vector containing only these
498 <title>See Also</title>
500 <member><link linkend="sql-expression"><function>sql-expression</function></link></member>
501 <member><link linkend="sql-operation"><function>sql-operation</function></link></member>
502 <member><link linkend="sql-operator"><function>sql-operator</function></link></member>
511 <refentry id="sql-expression">
513 <refentrytitle>SQL-EXPRESSION</refentrytitle>
516 <refname><emphasis>Function</emphasis> <emphasis role="bold">SQL-EXPRESSION</emphasis></refname>
517 <refpurpose>Constructs an SQL expression from supplied keyword arguments.</refpurpose>
518 <refclass>Function</refclass>
521 <title>Syntax</title>
523 <function>sql-expression &key</function> <parameter>string</parameter> <parameter>table</parameter> <parameter>alias</parameter> <parameter>attribute</parameter> <parameter>type</parameter> => <returnvalue>result</returnvalue></synopsis>
526 <title>Arguments and Values</title>
529 <term><parameter>string</parameter></term>
531 <para>A string.</para>
535 <term><parameter>table</parameter></term>
537 <para>A symbol representing a database table identifier.</para>
541 <term><parameter>alias</parameter></term>
543 <para>A table alias.</para>
547 <term><parameter>attribute</parameter></term>
549 <para>A symbol representing an attribute identifier.</para>
553 <term><parameter>type</parameter></term>
555 <para>A type specifier.</para>
559 <term><returnvalue>result</returnvalue></term>
561 <para>A object of type <type>sql-expression</type>.</para>
567 <title>Description</title>
568 <para>Returns an SQL expression constructed from the supplied
569 arguments which may be combined as follows:</para>
570 <itemizedlist mark='opencircle'>
573 <parameter>attribute</parameter> and
574 <parameter>type</parameter>;
579 <parameter>attribute</parameter>;
584 <parameter>alias</parameter> or <parameter>table</parameter> and
585 <parameter>attribute</parameter> and
586 <parameter>type</parameter>;
591 <parameter>alias</parameter> or
592 <parameter>table</parameter> and
593 <parameter>attribute</parameter>;
598 <parameter>table</parameter>,
599 <parameter>attribute</parameter> and
600 <parameter>type</parameter>;
605 <parameter>table</parameter> and
606 <parameter>attribute</parameter>;
611 <parameter>table</parameter>
612 and <parameter>alias</parameter>;
617 <parameter>table</parameter>;
622 <parameter>string</parameter>.
628 <title>Examples</title>
630 (sql-expression :table 'foo :attribute 'bar)
631 => #<CLSQL-SYS:SQL-IDENT-ATTRIBUTE FOO.BAR>
633 (sql-expression :attribute 'baz)
634 => #<CLSQL-SYS:SQL-IDENT-ATTRIBUTE BAZ>
638 <title>Side Effects</title>
642 <title>Affected by</title>
648 <title>Exceptional Situations</title>
649 <para>An error of type <link
650 linkend="sql-user-error"><function>sql-user-error</function></link>
651 is signalled if an unsupported combination of keyword arguments is
656 <title>See Also</title>
658 <member><link linkend="sql"><function>sql</function></link></member>
659 <member><link linkend="sql-operation"><function>sql-operation</function></link></member>
660 <member><link linkend="sql-operator"><function>sql-operator</function></link></member>
669 <refentry id="sql-operation">
671 <refentrytitle>SQL-OPERATION</refentrytitle>
674 <refname><emphasis>Function</emphasis> <emphasis role="bold">SQL-OPERATION</emphasis></refname>
675 <refpurpose>Constructs an SQL expression from a supplied operator and arguments.</refpurpose>
676 <refclass>Function</refclass>
679 <title>Syntax</title>
681 <function>sql-operation</function> <parameter>operator</parameter> <function>&rest</function> <parameter>args</parameter> => <returnvalue>result</returnvalue></synopsis>
683 <function>sql-operation</function> 'function <parameter>func</parameter> <function>&rest</function> <parameter>args</parameter> => <returnvalue>result</returnvalue></synopsis>
686 <title>Arguments and Values</title>
689 <term><parameter>operator</parameter></term>
691 <para>A symbol denoting an SQL operator.</para>
695 <term><parameter>func</parameter></term>
697 <para>A string denoting an SQL function.</para>
701 <term><parameter>args</parameter></term>
703 <para>A set of arguments for the specified SQL operator or function.</para>
707 <term><returnvalue>result</returnvalue></term>
709 <para>A object of type <function>sql-expression</function>.</para>
715 <title>Description</title>
716 <para>Returns an SQL expression constructed from the supplied
717 SQL operator or function <parameter>operator</parameter> and
718 its arguments <parameter>args</parameter>. If
719 <parameter>operator</parameter> is passed the symbol 'function
720 then the first value in <parameter>args</parameter> is taken to
721 be a valid SQL function and the remaining values in
722 <parameter>args</parameter> its arguments.
726 <title>Examples</title>
728 (sql-operation 'select
729 (sql-expression :table 'foo :attribute 'bar)
730 (sql-operation 'sum (sql-expression :table 'foo :attribute 'baz))
732 (sql-expression :table 'foo)
734 (sql-operation '> (sql-expression :attribute 'bar) 12)
735 :order-by (sql-operation 'sum (sql-expression :attribute 'baz)))
736 => #<SQL-QUERY SELECT FOO.BAR,SUM(FOO.BAZ) FROM FOO WHERE (BAR > 12) ORDER BY SUM(BAZ)>
738 (sql-operation 'function "strpos" "CLSQL" "SQL")
739 => #<CLSQL-SYS:SQL-FUNCTION-EXP STRPOS('CLSQL','SQL')>
743 <title>Side Effects</title>
747 <title>Affected by</title>
753 <title>Exceptional Situations</title>
754 <para>An error of type <link
755 linkend="sql-user-error"><function>sql-user-error</function></link>
756 is signalled if <parameter>operator</parameter> is not a symbol
757 representing a supported SQL operator.</para>
760 <title>See Also</title>
762 <member><link linkend="sql"><function>sql</function></link></member>
763 <member><link linkend="sql-expression"><function>sql-expression</function></link></member>
764 <member><link linkend="sql-operator"><function>sql-operator</function></link></member>
773 <refentry id="sql-operator">
775 <refentrytitle>SQL-OPERATOR</refentrytitle>
778 <refname><emphasis>Function</emphasis> <emphasis role="bold">SQL-OPERATOR</emphasis></refname>
779 <refpurpose>Returns the symbol for the supplied SQL operator.</refpurpose>
780 <refclass>Function</refclass>
783 <title>Syntax</title>
785 <function>sql-operator</function> <parameter>operator</parameter> => <returnvalue>result</returnvalue></synopsis>
788 <title>Arguments and Values</title>
791 <term><parameter>operator</parameter></term>
793 <para>A symbol denoting an SQL operator.</para>
797 <term><returnvalue>result</returnvalue></term>
799 <para>The Lisp symbol used by &clsql; to represent the
800 specified operator.</para>
806 <title>Description</title>
807 <para>Returns the Lisp symbol corresponding to the SQL operator
808 represented by the symbol <parameter>operator</parameter>. If
809 <parameter>operator</parameter> does not represent a supported
810 SQL operator or is not a symbol, nil is returned.
814 <title>Examples</title>
821 <title>Side Effects</title>
825 <title>Affected by</title>
831 <title>Exceptional Situations</title>
835 <title>See Also</title>
837 <member><link linkend="sql"><function>sql</function></link></member>
838 <member><link linkend="sql-expression"><function>sql-expression</function></link></member>
839 <member><link linkend="sql-operation"><function>sql-operation</function></link></member>
845 &clsql;'s symbolic SQL syntax currently has support for the
846 following &commonsql; compatible SQL operators:
849 <!-- CommonSQL Compatible -->
850 <member><function>any
851 </function></member>,
852 <member><function>some
853 </function></member>,
854 <member><function>all
855 </function></member>,
856 <member><function>not
857 </function></member>,
858 <member><function>union
859 </function></member>,
860 <member><function>intersect
861 </function></member>,
862 <member><function>minus
863 </function></member>,
864 <member><function>except
865 </function></member>,
866 <member><function>order-by
867 </function></member>,
868 <member><function>null
869 </function></member>,
871 </function></member>,
873 </function></member>,
875 </function></member>,
877 </function></member>,
878 <member><function>like
879 </function></member>,
880 <member><function>and
881 </function></member>,
883 </function></member>,
885 </function></member>,
886 <member><function>substr
887 </function></member>,
889 </function></member>,
891 </function></member>,
892 <member><function><
893 </function></member>,
895 </function></member>,
897 </function></member>,
898 <member><function><=
899 </function></member>,
900 <member><function><>
901 </function></member>,
902 <member><function>count
903 </function></member>,
904 <member><function>max
905 </function></member>,
906 <member><function>min
907 </function></member>,
908 <member><function>avg
909 </function></member>,
910 <member><function>sum
911 </function></member>,
912 <member><function>function
913 </function></member>,
914 <member><function>between
915 </function></member>,
916 <member><function>distinct
917 </function></member>,
918 <member><function>nvl
919 </function></member>,
920 <member><function>slot-value
921 </function></member>,
922 <member><function>userenv
923 </function></member>,
926 as well as the pseudo-operator <function>function</function>.
928 <para> The following operators are provided as &clsql; extensions to
931 <!-- CLSQL Extensions -->
932 <member><function>concat
933 </function></member>,
934 <member><function>substring
935 </function></member>,
936 <member><function>limit
937 </function></member>,
938 <member><function>group-by
939 </function></member>,
940 <member><function>having
941 </function></member>,
942 <member><function>not-null
943 </function></member>,
944 <member><function>exists
945 </function></member>,
946 <member><function>uplike
947 </function></member>,
949 </function></member>,
951 </function></member>,
952 <member><function>the
953 </function></member>,
954 <member><function>coalesce
955 </function></member>,
956 <member><function>view-class.
961 Note that some of these operators are not supported by all of
962 the RDBMS supported by &clsql; (see the <link
963 linkend="appendix">Appendix</link> for details).