Functional Data Manipulation Language (FDML)
-
+ The functional data manipulation interface provided by &clsql;
+ includes functions for inserting, updating and deleting records
+ in existing database tables and executing SQL queries and
+ statements with the results of queries returned as Lisp types.
+ SQL statements expressed as strings may be executed with the
+ query and
+ execute-command
+ functions. The select function, on
+ the other hand, allows for the construction of queries in Lisp
+ using the symbolic SQL syntax. Finally, iterative manipulation
+ of query results is supported by do-query, map-query and an
+ extended clause for the loop macro.
-
@@ -22,7 +37,7 @@
*CACHE-TABLE-QUERIES-DEFAULT*
- Variable*CACHE-TABLE-QUERIES-DEFAULT*
+ *CACHE-TABLE-QUERIES-DEFAULT*Specifies the default behaviour for caching of
attribute types.Variable
@@ -79,141 +94,22 @@
CACHE-TABLE-QUERIES
- FunctionCACHE-TABLE-QUERIES
- Controls the caching of attribute type information for a database table.
- Function
+ CACHE-TABLE-QUERIES
+ Control the caching of table attribute types.Syntax
-
- cache-table-queriestable &key actiondatabase) =>
-
-
- Arguments and Values
-
-
- table
-
-
- A string representing a database table, &t; or
- :default.
-
-
-
-
- action
-
-
- &t;, &nil; or :flush.
-
-
-
-
- database
-
- A
- database
- object. This will default to the value
- of *default-database*.
-
-
-
-
-
- Description
- Controls the caching of attribute type information on the
- table specified by table in
- database which defaults to
- *default-database*. action
- specifies the caching behaviour to adopt. If its value is &t;
- then attribute type information is cached whereas if its value
- is &nil; then attribute type information is not cached. If
- action is :flush then
- all existing type information in the cache for
- table is removed, but caching is still
- enabled. table may be a string
- representing a table for which the caching action is to be taken
- while the caching action is applied to all tables if
- table is &t;. Alternatively, when
- table is :default, the
- default caching action specified by
- *cache-table-queries-default* is applied to all
- tables for which a caching action has not been explicitly set.
-
-
-
- Examples
-
-(setf *cache-table-queries-default* t)
-=> T
-(create-table [foo]
- '(([id] integer)
- ([height] float)
- ([name] (string 24))
- ([comments] varchar)))
-=>
-(cache-table-queries "foo")
-=>
-(list-attribute-types "foo")
-=> (("id" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) ("name" :BPCHAR 24 NIL 1)
- ("comments" :VARCHAR 255 NIL 1))
-(drop-table "foo")
-=>
-(create-table [foo]
- '(([id] integer)
- ([height] float)
- ([name] (string 36))
- ([comments] (string 100))))
-=>
-(cache-table-queries "foo" :action :flush)
-=>
-(list-attribute-types "foo")
-=> (("id" :INT4 4 NIL 1) ("height" :FLOAT8 8 NIL 1) ("name" :BPCHAR 36 NIL 1)
- ("comments" :BPCHAR 100 NIL 1))
-
-
-
- Side Effects
-
- The internal attribute cache for
- database is modified.
-
-
-
- Affected by
-
- *cache-table-queries-default*
-
-
-
- Exceptional Situations
-
- None.
-
-
-
- See Also
-
- *cache-table-queries-default*
-
-
-
- Notes
- None.
-
-
-
INSERT-RECORDS
- FunctionINSERT-RECORDS
+ INSERT-RECORDSInsert tuples of data into a database table.Function
@@ -376,7 +272,7 @@
UPDATE-RECORDS
- FunctionUPDATE-RECORDS
+ UPDATE-RECORDSUpdates the values of existing records.Function
@@ -536,7 +432,7 @@
DELETE-RECORDS
- FunctionDELETE-RECORDS
+ DELETE-RECORDSDelete records from a database table.Function
@@ -650,7 +546,7 @@
EXECUTE-COMMAND
- Generic FunctionEXECUTE-COMMAND
+ EXECUTE-COMMANDExecute an SQL command which returns no values.Generic Function
@@ -754,7 +650,7 @@
QUERY
- Generic FunctionQUERY
+ QUERYExecute an SQL query and return the tuples as a
list.Generic Function
@@ -958,7 +854,7 @@
PRINT-QUERY
- FunctionPRINT-QUERY
+ PRINT-QUERYPrints a tabular report of query results.Function
@@ -1114,7 +1010,7 @@ ID FORENAME SURNAME EMAIL
SELECT
- FunctionSELECT
+ SELECTExecutes a query given the supplied constraints.Function
@@ -1278,7 +1174,7 @@ ID FORENAME SURNAME EMAIL
in the tuple.
-
+
@@ -1429,10 +1325,8 @@ ID FORENAME SURNAME EMAIL
Side Effects
- Whatever effects the execution of the SQL query has on
the underlying database, if any.
- Affected by
@@ -1461,6 +1355,16 @@ ID FORENAME SURNAME EMAIL
Notes
+
+ The select function is actually
+ implemented in &clsql; with a single
+ &rest parameter (which is subsequently
+ destructured) rather than the keyword parameters presented
+ here for the purposes of exposition. This means that incorrect
+ or missing keywords or values may not trigger errors in the
+ way that they would if select had been
+ defined using keyword arguments.
+
The field-names and
result-types keyword arguments are a
@@ -1481,7 +1385,7 @@ ID FORENAME SURNAME EMAIL
DO-QUERY
- MacroDO-QUERY
+ DO-QUERYIterate over all the tuples of a query.Macro
@@ -1657,85 +1561,15 @@ ID FORENAME SURNAME EMAIL
-
-
- FOR-EACH-ROW
-
-
- FunctionFOR-EACH-ROW
-
- Function
-
-
- Syntax
-
- for-each-row &KEY FROM ORDER-BY WHERE DISTINCT LIMIT &REST FIELDS &body body =>
-
-
- Arguments and Values
-
-
-
-
-
- Description
-
-
-
-
-
- Examples
-
-
-
-
-
- Side Effects
-
-
-
-
-
- Affected by
-
-
-
-
-
-
-
- Exceptional Situations
-
-
-
-
-
- See Also
-
- query
- print-query
- do-query
- map-query
- loop
- select
-
-
-
- Notes
-
-
-
-
-
-
LOOP
- Additional clause for LOOP
- Iterate over all the tuples of a
- query via a loop clause.
+ LOOP
+ Extension to Common Lisp
+ Loop to iterate over all the
+ tuples of a query via a loop clause.Loop Clause
-
-
-
- PREPARE-SQL
-
-
- FunctionPREPARE-SQL
- Create a prepared statement.
- Function
-
-
- Syntax
-
- prepare-sqlsql-stmt types &key databaseresult-typesfield-names => result
-
-
- Arguments and Values
-
-
-
-
-
- Description
- Prepares a SQL statement sql-stmt
- for execution. types contains a list of
- types corresponding to the input parameters. Returns a
- prepared-statement object.
-
- A type can be
- :int
- :double
- :null
- (:string n)
-
-
-
-
- Examples
-
-
-
-
-
- Side Effects
-
-
-
-
-
- Affected by
-
-
-
-
-
-
-
- Exceptional Situations
-
-
-
-
-
- See Also
-
-
-
-
-
-
-
- Notes
-
-
-
-
-
-
-
-
- RUN-PREPARED-SQL
-
-
- FunctionRUN-PREPARED-SQL
- Execute a prepared statement.
- Function
-
-
- Syntax
-
- run-prepared-sqlprepared-stmt =>
-
-
- Arguments and Values
-
-
-
-
-
- Description
- Execute the prepared sql statment. All input
- parameters must be bound.
-
-
-
- Examples
-
-
-
-
-
- Side Effects
-
-
-
-
-
- Affected by
-
-
-
-
-
-
-
- Exceptional Situations
-
-
-
-
-
- See Also
-
-
-
-
-
-
-
- Notes
-
-
-
-
-
-
-
-
- FREE-PREPARED-SQL
-
-
- FunctionFREE-PREPARED-SQL
- Delete a prepared statement object.
- Function
-
-
- Syntax
-
- free-prepared-sqlprepared-stmt =>
-
-
- Arguments and Values
-
-
-
-
-
- Description
- Delete the objects associated with a prepared
- statement.
-
-
-
- Examples
-
-
-
-
-
- Side Effects
-
-
-
-
-
- Affected by
-
-
-
-
-
-
-
- Exceptional Situations
-
-
-
-
-
- See Also
-
-
-
-
-
-
-
- Notes
-
-
-
-
-
-
-
-
- BIND-PARAMETER
-
-
- FunctionBIND-PARAMETER
- Bind a parameter in a prepared statement.
- Function
-
-
- Syntax
-
- bind-parameterprepared-stmtpositionvalue =>
-
-
- Arguments and Values
-
-
-
-
-
- Description
- Sets the value of a parameter in a prepared statement.
-
-
-
- Examples
-
-
-
-
-
- Side Effects
-
-
-
-
-
- Affected by
-
-
-
-
-
-
-
- Exceptional Situations
-
-
-
-
-
- See Also
-
-
-
-
-
-
-
- Notes
-
-
-
-
-
-
+