The Symbolic SQL Syntax
-
+ &clsql; provides a symbolic syntax allowing the construction of
+ SQL expressions as lists delimited by square brackets. The
+ syntax is turned off by default. This section describes
+ utilities for enabling and disabling the square bracket reader
+ syntax and for constructing symbolic SQL expressions.
-
+
+
+ ENABLE-SQL-READER-SYNTAX
+
- DISABLE-SQL-READER-SYNTAX
-
- Function
+ ENABLE-SQL-READER-SYNTAX
+ Globally enable square bracket reader syntax.
+ MacroSyntax
- (DISABLE-SQL-READER-SYNTAX) [macro] =>
+ enable-sql-reader-syntax => Arguments and Values
-
-
-
+ None.Description
- Turns off the SQL reader syntax setting the
- syntax state such that if the syntax is subsequently
- enabled, RESTORE-SQL-READER-SYNTAX-STATE will disable
- it again.
+ Turns on the SQL reader syntax setting the syntax state
+ such that if the syntax is subsequently disabled,
+ restore-sql-reader-syntax-state will
+ enable it again.
Examples
-
-
-
+ None.Side Effects
-
+ Sets the internal syntax state to enabled.
+
+
+ Modifies the default readtable.
Affected by
-
-
-
+ None.
Exceptional Situations
-
+ None.
See Also
-
-
-
-
-
+
+ disable-sql-reader-syntax
+ locally-enable-sql-reader-syntax
+ locally-disable-sql-reader-syntax
+ restore-sql-reader-syntax-state
+ Notes
-
+ The symbolic SQL syntax is disabled by default.
+
+
+ &clsql; differs from &commonsql; in that
+ enable-sql-reader-syntax is defined as a
+ macro rather than a function.
-
+
+
+ DISABLE-SQL-READER-SYNTAX
+
- ENABLE-SQL-READER-SYNTAX
-
- Function
+ DISABLE-SQL-READER-SYNTAX
+ Globally disable square bracket reader syntax.
+ MacroSyntax
- (ENABLE-SQL-READER-SYNTAX) [macro] =>
+ disable-sql-reader-syntax => Arguments and Values
-
-
-
+ None.Description
- Turns on the SQL reader syntax setting the
- syntax state such that if the syntax is subsequently
- disabled, RESTORE-SQL-READER-SYNTAX-STATE will enable
- it again.
+ Turns off the SQL reader syntax setting the syntax state
+ such that if the syntax is subsequently enabled,
+
+ restore-sql-reader-syntax-state will
+ disable it again.
Examples
-
-
-
+ None.Side Effects
-
+ Sets the internal syntax state to disabled.
+
+
+ Modifies the default readtable.
Affected by
-
-
-
+ None.
Exceptional Situations
-
+ None.
See Also
-
-
-
-
-
+
+ enable-sql-reader-syntax
+ locally-enable-sql-reader-syntax
+ locally-disable-sql-reader-syntax
+ restore-sql-reader-syntax-state
+ Notes
-
+ The symbolic SQL syntax is disabled by default.
+
+
+ &clsql; differs from &commonsql; in that
+ disable-sql-reader-syntax is defined as a
+ macro rather than a function.
-
+
+
+ LOCALLY-ENABLE-SQL-READER-SYNTAX
+
- LOCALLY-DISABLE-SQL-READER-SYNTAX
-
- Function
+ LOCALLY-ENABLE-SQL-READER-SYNTAX
+ Globally enable square bracket reader syntax.
+ MacroSyntax
- (LOCALLY-DISABLE-SQL-READER-SYNTAX) [macro] =>
+ locally-enable-sql-reader-syntax => Arguments and Values
-
-
-
+ None.Description
- Turns off the SQL reader syntax without
- changing the syntax state such that
- RESTORE-SQL-READER-SYNTAX-STATE will re-establish the
- current syntax state.
+ Turns on the SQL reader syntax without changing the syntax
+ state such that
+ restore-sql-reader-syntax-state will
+ re-establish the current syntax state.
- Examples
+ Examples
+ Intended to be used in a file for code which uses the
+ square bracket syntax without changing the global state.
+
-
+ #.(locally-enable-sql-reader-syntax)
+
+ ... CODE USING SYMBOLIC SQL SYNTAX ...
+
+ #.(restore-sql-reader-syntax-state)
Side Effects
-
+ Modifies the default readtable.
Affected by
-
-
-
-
-
+ None.Exceptional Situations
-
+ None.
See Also
-
-
-
-
-
+
+ enable-sql-reader-syntax
+ disable-sql-reader-syntax
+ locally-disable-sql-reader-syntax
+ restore-sql-reader-syntax-state
+ Notes
-
+ The symbolic SQL syntax is disabled by default.
+
+
+ &clsql; differs from &commonsql; in that
+ locally-enable-sql-reader-syntax is
+ defined as a macro rather than a function.
-
+
+
+
+ LOCALLY-DISABLE-SQL-READER-SYNTAX
+
- LOCALLY-ENABLE-SQL-READER-SYNTAX
-
- Function
+ LOCALLY-DISABLE-SQL-READER-SYNTAX
+ Locally disable square bracket reader syntax.
+ MacroSyntax
- (LOCALLY-ENABLE-SQL-READER-SYNTAX) [macro] =>
+ locally-disable-sql-reader-syntax => Arguments and Values
-
-
-
+ None.Description
- Turns on the SQL reader syntax without
- changing the syntax state such that
- RESTORE-SQL-READER-SYNTAX-STATE will re-establish the
- current syntax state.
+ Turns off the SQL reader syntax without changing the
+ syntax state such that
+ restore-sql-reader-syntax-state will
+ re-establish the current syntax state.
Examples
+ Intended to be used in a file for code in which the square
+ bracket syntax should be disabled without changing the global
+ state.
+
-
+ #.(locally-disable-sql-reader-syntax)
+
+ ... CODE NOT USING SYMBOLIC SQL SYNTAX ...
+
+ #.(restore-sql-reader-syntax-state)
Side Effects
-
+ Modifies the default readtable.
Affected by
-
-
-
+ None.
Exceptional Situations
-
+ None.
See Also
-
-
-
-
-
+
+ enable-sql-reader-syntax
+ disable-sql-reader-syntax
+ locally-enable-sql-reader-syntax
+ restore-sql-reader-syntax-state
+ Notes
-
+ The symbolic SQL syntax is disabled by default.
+
+
+ &clsql; differs from &commonsql; in that
+ locally-disable-sql-reader-syntax is
+ defined as a macro rather than a function.
+
+ RESTORE-SQL-READER-SYNTAX-STATE
+ RESTORE-SQL-READER-SYNTAX-STATE
-
- Function
+
+ Restore square bracket reader syntax to its previous state.
+
+ MacroSyntax
- (RESTORE-SQL-READER-SYNTAX-STATE) [macro] =>
+ restore-sql-reader-syntax-state => Arguments and Values
-
-
-
+ None.Description
- Enables the SQL reader syntax if
- ENABLE-SQL-READER-SYNTAX has been called more recently
- than DISABLE-SQL-READER-SYNTAX and otherwise disables
- the SQL reader syntax. By default, the SQL reader
- syntax is disabled.
+ Enables the SQL reader syntax if
+ enable-sql-reader-syntax has been
+ called more recently than
+ disable-sql-reader-syntax and
+ otherwise disables the SQL reader syntax. By default, the SQL
+ reader syntax is disabled.
Examples
-
-
-
+
+ See
+ locally-enable-sql-reader-syntax and
+
+ locally-disable-sql-reader-syntax.
+ Side Effects
-
+ Reverts the internal syntax state.
+
+
+ Modifies the default readtable.
Affected by
-
-
-
-
-
+ The current internal syntax state.Exceptional Situations
-
+ None.
See Also
-
-
-
-
-
+
+ enable-sql-reader-syntax
+ disable-sql-reader-syntax
+ locally-enable-sql-reader-syntax
+ locally-disable-sql-reader-syntax
+ Notes
-
+ The symbolic SQL syntax is disabled by default.
+
+
+ &clsql; differs from &commonsql; in that
+ restore-sql-reader-syntax-state is
+ defined as a macro rather than a function.
+
+ SQL
+ SQL
-
+ Construct an SQL string from supplied expressions.FunctionSyntax
- (SQL &REST ARGS) [function] =>
+ sql &restargs => sql-expressionArguments and Values
-
+
+ args
+
+ A set of expressions.
+
+
+
+ sql-expression
+
+ A string representing an SQL expression.
+
+ Description
- Returns an SQL string generated from the SQL expressions
- ARGS. The expressions are translated into SQL strings and then
- concatenated with a single space delimiting each expression.
+ Returns an SQL string generated from the expressions
+ args. The expressions are translated into
+ SQL strings and then concatenated with a single space delimiting
+ each expression.
Examples
-
+(sql nil)
+=> "NULL"
+
+(sql 'foo)
+=> "FOO"
+
+(sql "bar")
+ => "'bar'"
+
+(sql 10)
+=> "10"
+
+(sql '(nil foo "bar" 10))
+=> "(NULL,FOO,'bar',10)"
+
+(sql #(nil foo "bar" 10))
+=> "NULL,FOO,'bar',10"
+
+(sql [select [foo] [bar] :from [baz]] 'having [= [foo id] [bar id]]
+ 'and [foo val] '< 5)
+=> "SELECT FOO,BAR FROM BAZ HAVING (FOO.ID = BAR.ID) AND FOO.VAL < 5"
Side Effects
-
-
-
+ None.Affected by
-
-
-
+ None.
Exceptional Situations
-
-
-
+ An error of type sql-user-error
+ is signalled if any element in args is
+ not of the supported types (a symbol, string, number or symbolic
+ SQL expression) or a list or vector containing only these
+ supported types.
+ See Also
-
-
-
-
-
+
+ sql-expression
+ sql-operation
+ sql-operator
+ Notes
-
-
-
+ None.
-
+
+ SQL-EXPRESSION
+ SQL-EXPRESSION
-
+ Constructs an SQL expression from supplied keyword arguments.FunctionSyntax
- (SQL-EXPRESSION &KEY STRING TABLE ALIAS ATTRIBUTE TYPE) [function] =>
+ sql-expression &keystringtablealiasattributetype => resultArguments and Values
-
+
+ string
+
+ A string.
+
+
+
+ table
+
+ A symbol representing a database table identifier.
+
+
+
+ alias
+
+ A table alias.
+
+
+
+ attribute
+
+ A symbol representing an attribute identifier.
+
+
+
+ type
+
+ A type specifier.
+
+
+
+ result
+
+ A object of type sql-expression.
+
+ Description
- Returns an SQL expression constructed from
- the supplied arguments which may be combined as
- follows: ATTRIBUTE and TYPE; ATTRIBUTE; ALIAS or TABLE
- and ATTRIBUTE and TYPE; ALIAS or TABLE and ATTRIBUTE;
- TABLE, ATTRIBUTE and TYPE; TABLE and ATTRIBUTE; TABLE
- and ALIAS; TABLE; and STRING.
-
+ Returns an SQL expression constructed from the supplied
+ arguments which may be combined as follows:
+
+
+
+ attribute and
+ type;
+
+
+
+
+ attribute;
+
+
+
+
+ alias or table and
+ attribute and
+ type;
+
+
+
+
+ alias or
+ table and
+ attribute;
+
+
+
+
+ table,
+ attribute and
+ type;
+
+
+
+
+ table and
+ attribute;
+
+
+
+
+ table
+ and alias;
+
+
+
+
+ table;
+
+
+
+
+ string.
+
+
+ Examples
-
+(sql-expression :table 'foo :attribute 'bar)
+=> #<CLSQL-SYS:SQL-IDENT-ATTRIBUTE FOO.BAR>
+
+(sql-expression :attribute 'baz)
+=> #<CLSQL-SYS:SQL-IDENT-ATTRIBUTE BAZ>
Side Effects
-
-
-
+ None.Affected by
-
-
-
+ None.
Exceptional Situations
-
-
-
+ An error of type sql-user-error
+ is signalled if an unsupported combination of keyword arguments is
+ specified.
+ See Also
-
-
-
+
+ sql
+ sql-operation
+ sql-operator
- Notes
-
-
-
+ None.
+
+ SQL-OPERATION
+ SQL-OPERATION
-
+ Constructs an SQL expression from a supplied operator and arguments.FunctionSyntax
- (SQL-OPERATION OPERATION &REST REST) [function] =>
+ sql-operationoperator&restargs => result
+
+ sql-operation 'function func&restargs => resultArguments and Values
-
+
+ operator
+
+ A symbol denoting an SQL operator.
+
+
+
+ func
+
+ A string denoting an SQL function.
+
+
+
+ args
+
+ A set of arguments for the specified SQL operator or function.
+
+
+
+ result
+
+ A object of type sql-expression.
+
+ Description
- Returns an SQL expression constructed from
- the supplied SQL operator or function OPERATION and
- its arguments REST. If OPERATION is passed the symbol
- FUNCTION then the first value in REST is taken to be a
- valid SQL function and the remaining values in REST
- its arguments.
+ Returns an SQL expression constructed from the supplied
+ SQL operator or function operator and
+ its arguments args. If
+ operator is passed the symbol 'function
+ then the first value in args is taken to
+ be a valid SQL function and the remaining values in
+ args its arguments.
Examples
-
+(sql-operation 'select
+ (sql-expression :table 'foo :attribute 'bar)
+ (sql-operation 'sum (sql-expression :table 'foo :attribute 'baz))
+ :from
+ (sql-expression :table 'foo)
+ :where
+ (sql-operation '> (sql-expression :attribute 'bar) 12)
+ :order-by (sql-operation 'sum (sql-expression :attribute 'baz)))
+=> #<SQL-QUERY SELECT FOO.BAR,SUM(FOO.BAZ) FROM FOO WHERE (BAR > 12) ORDER BY SUM(BAZ)>
+
+(sql-operation 'function "strpos" "CLSQL" "SQL")
+=> #<CLSQL-SYS:SQL-FUNCTION-EXP STRPOS('CLSQL','SQL')>
Side Effects
-
-
-
+ None.Affected by
-
-
-
+ None.
Exceptional Situations
-
-
-
+ An error of type sql-user-error
+ is signalled if operator is not a symbol
+ representing a supported SQL operator.See Also
-
-
-
-
-
+
+ sql
+ sql-expression
+ sql-operator
+ Notes
-
-
-
+ None.
+
+ SQL-OPERATOR
+ SQL-OPERATOR
-
+ Returns the symbol for the supplied SQL operator.FunctionSyntax
- (SQL-OPERATOR OPERATION) [function] =>
+ sql-operatoroperator => resultArguments and Values
-
+
+ operator
+
+ A symbol denoting an SQL operator.
+
+
+
+ result
+
+ The Lisp symbol used by &clsql; to represent the
+ specified operator.
+
+ Description
- Returns the Lisp symbol corresponding to the
- SQL operation represented by the symbol
- OPERATION.
+ Returns the Lisp symbol corresponding to the SQL operator
+ represented by the symbol operator. If
+ operator does not represent a supported
+ SQL operator or is not a symbol, nil is returned.
Examples
-
+(sql-operator 'like)
+=> SQL-LIKE
Side Effects
-
-
-
+ None.Affected by
-
-
-
+ None.
Exceptional Situations
-
-
-
+ None.See Also
-
-
-
-
-
+
+ sql
+ sql-expression
+ sql-operation
+ Notes
-
+ &clsql;'s symbolic SQL syntax currently has support for the
+ following &commonsql; compatible SQL operators:
+
+
+
+ any
+ some
+ all
+ not
+ union
+ intersect
+ minus
+ except
+
+ order-by
+
+ null
+
+ *
+
+ +
+
+ /
+
+ -
+
+ like
+
+ and
+
+ or
+
+ in
+
+ substr
+
+ ||
+
+ =
+
+ <
+
+ >
+
+ >=
+
+ <=
+
+ <>
+
+ count
+
+ max
+
+ min
+
+ avg
+
+ sum
+
+ function
+
+ between
+
+ distinct
+
+ nvl
+
+ slot-value
+
+ userenv
+
+
+
+ as well as the pseudo-operator function.
+
+ The following operators are provided as &clsql; extensions to
+ the &commonsql; API.
+
+
+ concat
+
+ substring
+
+ limit
+
+ group-by
+
+ having
+
+ not-null
+
+ exists
+
+ uplike
+
+ is
+
+ ==
+
+ the
+
+ coalesce
+
+ view-class
+
+
+
+
+ Note that some of these operators are not supported by all of
+ the RDBMS supported by &clsql; (see the Appendix for details).