X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=doc%2Fref-syntax.xml;h=c83cab344d41ad08852dfb922f4db23adba092a7;hb=f7a356bb4c11d062eeeb5d4aaf4e187a3cb15a27;hp=511a168c50c52436f5651818c2308b042fcebbec;hpb=5248ae9f94d2022596ef8846e0581ea716607a84;p=clsql.git diff --git a/doc/ref-syntax.xml b/doc/ref-syntax.xml index 511a168..c83cab3 100644 --- a/doc/ref-syntax.xml +++ b/doc/ref-syntax.xml @@ -10,613 +10,957 @@ 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 + Macro ENABLE-SQL-READER-SYNTAX + Globally enable square bracket reader syntax. + Macro Syntax - (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 + Macro DISABLE-SQL-READER-SYNTAX + Globally disable square bracket reader syntax. + Macro Syntax - (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 + Macro LOCALLY-ENABLE-SQL-READER-SYNTAX + Globally enable square bracket reader syntax. + Macro Syntax - (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 + Macro LOCALLY-DISABLE-SQL-READER-SYNTAX + Locally disable square bracket reader syntax. + Macro Syntax - (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 + Macro RESTORE-SQL-READER-SYNTAX-STATE + + Restore square bracket reader syntax to its previous state. + + Macro Syntax - (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 - + Function SQL + Construct an SQL string from supplied expressions. Function Syntax - (SQL &REST ARGS) [function] => + sql &rest args => sql-expression Arguments 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 - + Function SQL-EXPRESSION + Constructs an SQL expression from supplied keyword arguments. Function Syntax - (SQL-EXPRESSION &KEY STRING TABLE ALIAS ATTRIBUTE TYPE) [function] => + sql-expression &key string table alias attribute type => result Arguments 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 - + Function SQL-OPERATION + Constructs an SQL expression from a supplied operator and arguments. Function Syntax - (SQL-OPERATION OPERATION &REST REST) [function] => + sql-operation operator &rest args => result + + sql-operation 'function func &rest args => result Arguments 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 - + Function SQL-OPERATOR + Returns the symbol for the supplied SQL operator. Function Syntax - (SQL-OPERATOR OPERATION) [function] => + sql-operator operator => result Arguments 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).