X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=doc%2Fref-fdml.xml;h=7113b6a98e3022056dcf050ecdc53a25520f44ab;hb=b50166ae0ba2bc09a9094c0e675ec92010b7293e;hp=f51a26c5d5ff216f439ee4b16bded62a7fe4d19f;hpb=cb683851a0af33e88b7c4995435dc0cf226f6cba;p=clsql.git
diff --git a/doc/ref-fdml.xml b/doc/ref-fdml.xml
index f51a26c..7113b6a 100644
--- a/doc/ref-fdml.xml
+++ b/doc/ref-fdml.xml
@@ -10,11 +10,26 @@
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,14 +94,14 @@
CACHE-TABLE-QUERIES
- Function CACHE-TABLE-QUERIES
- Controls the caching of attribute type information for a database table.
+ CACHE-TABLE-QUERIES
+ Control the caching of table attribute types.
Function
Syntax
- cache-table-queries table &key action database) =>
+ cache-table-queries table &key action database =>
Arguments and Values
@@ -174,11 +189,11 @@
Side Effects
-
+
The internal attribute cache for
database is modified.
-
-
+
+
Affected by
@@ -205,15 +220,12 @@
-
-
-
INSERT-RECORDS
- Function INSERT-RECORDS
+ INSERT-RECORDS
Insert tuples of data into a database table.
Function
@@ -358,12 +370,10 @@
See Also
-
-
- update-records
- delete-records
-
-
+
+ update-records
+ delete-records
+
Notes
@@ -378,7 +388,7 @@
UPDATE-RECORDS
- Function UPDATE-RECORDS
+ UPDATE-RECORDS
Updates the values of existing records.
Function
@@ -476,7 +486,7 @@
:from [employee]
:where [= [emplid] 1]
:field-names nil)
-=> (("Vladamir" "Lenin" "lenin@soviet.org"))
+=> (("Vladimir" "Lenin" "lenin@soviet.org"))
(update-records [employee]
:av-pairs'((first_name "Yuri")
(last_name "Gagarin")
@@ -538,7 +548,7 @@
DELETE-RECORDS
- Function DELETE-RECORDS
+ DELETE-RECORDS
Delete records from a database table.
Function
@@ -652,7 +662,7 @@
EXECUTE-COMMAND
- Generic Function EXECUTE-COMMAND
+ EXECUTE-COMMAND
Execute an SQL command which returns no values.
Generic Function
@@ -756,7 +766,7 @@
QUERY
- Generic Function QUERY
+ QUERY
Execute an SQL query and return the tuples as a
list.
Generic Function
@@ -798,7 +808,7 @@
A
field type
- specifier. The default is &nil;.
+ specifier. The default is :auto;.
The purpose of this argument is cause &clsql; to
@@ -894,22 +904,22 @@
Examples
(query "select emplid,first_name,last_name,height from employee where emplid = 1")
-=> ((1 "Vladamir" "Lenin" 1.5564661d0)),
+=> ((1 "Vladimir" "Lenin" 1.5564661d0)),
("emplid" "first_name" "last_name" "height")
(query "select emplid,first_name,last_name,height from employee where emplid = 1"
:field-names nil)
-=> ((1 "Vladamir" "Lenin" 1.5564661d0))
+=> ((1 "Vladimir" "Lenin" 1.5564661d0))
(query "select emplid,first_name,last_name,height from employee where emplid = 1"
:field-names nil
:result-types nil)
-=> (("1" "Vladamir" "Lenin" "1.5564661"))
+=> (("1" "Vladimir" "Lenin" "1.5564661"))
(query "select emplid,first_name,last_name,height from employee where emplid = 1"
:field-names nil
:result-types '(:int t t :double))
-=> ((1 "Vladamir" "Lenin" 1.5564661))
+=> ((1 "Vladimir" "Lenin" 1.5564661))
(query "select last_name from employee where emplid > 5" :flatp t)
=> ("Andropov" "Chernenko" "Gorbachev" "Yeltsin" "Putin"),
@@ -960,7 +970,7 @@
PRINT-QUERY
- Function PRINT-QUERY
+ PRINT-QUERY
Prints a tabular report of query results.
Function
@@ -1055,7 +1065,7 @@
:where [< [emplid] 5]]
:titles '("ID" "FORENAME" "SURNAME" "EMAIL"))
ID FORENAME SURNAME EMAIL
-1 Vladamir Lenin lenin@soviet.org
+1 Vladimir Lenin lenin@soviet.org
2 Josef Stalin stalin@soviet.org
3 Leon Trotsky trotsky@soviet.org
4 Nikita Kruschev kruschev@soviet.org
@@ -1069,7 +1079,7 @@ ID FORENAME SURNAME EMAIL
7 Konstantin Chernenko chernenko@soviet.org
8 Mikhail Gorbachev gorbachev@soviet.org
9 Boris Yeltsin yeltsin@soviet.org
-10 Vladamir Putin putin@soviet.org
+10 Vladimir Putin putin@soviet.org
=>
@@ -1116,14 +1126,14 @@ ID FORENAME SURNAME EMAIL
SELECT
- Function SELECT
+ SELECT
Executes a query given the supplied constraints.
Function
Syntax
- select &rest identifiers &key all distinct from group-by having order-by set-operation where result-types field-names flatp refresh database => result
+ select &rest identifiers &key all distinct from group-by having limit offset order-by set-operation where result-types field-names flatp refresh caching database => result
Arguments and Values
@@ -1178,6 +1188,22 @@ ID FORENAME SURNAME EMAIL
+
+ limit
+
+
+ A non-negative integer.
+
+
+
+
+ offset
+
+
+ A non-negative integer.
+
+
+
order-by
@@ -1222,7 +1248,7 @@ ID FORENAME SURNAME EMAIL
A
field type
- specifier. The default is &nil;.
+ specifier. The default is :auto.
The purpose of this argument is cause &clsql; to
@@ -1270,6 +1296,35 @@ ID FORENAME SURNAME EMAIL
+
+ refresh
+
+
+ This value is only considered when CLOS objects are being
+ selected. A boolean with a default value of &nil;. When
+ the value of the caching keyword is
+ &t;, a second equivalent select call
+ will return the same view class instance objects. When
+ refresh is &t;, then slots of the
+ existing instances are updated as necessary. In such
+ cases, you may wish to override the hook
+ instance-refresh.
+
+
+
+
+ caching
+
+
+ This value is only considered when CLOS objects are being
+ selected. A boolean with a default value of
+ *default-caching*. &clsql; caches
+ objects in accordance with the &commonsql; interface: a
+ second equivalent select call will
+ return the same view class instance objects.
+
+
+
result
@@ -1280,7 +1335,7 @@ ID FORENAME SURNAME EMAIL
in the tuple.
-
+
@@ -1302,8 +1357,8 @@ ID FORENAME SURNAME EMAIL
specified lisp type. The keyword arguments
all, distinct,
from, group-by,
- having,
- order-by,
+ having, limit,
+ offset, order-by,
set-operation and
where are used to specify, using the
symbolic SQL syntax, the corresponding components of the SQL
@@ -1358,7 +1413,7 @@ ID FORENAME SURNAME EMAIL
:field-names nil
:result-types nil
:order-by [first-name])
-=> ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladamir"
+=> ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
"Yuri")
(select [first-name] [count [*]] :from [employee]
@@ -1367,7 +1422,7 @@ ID FORENAME SURNAME EMAIL
:order-by [first-name]
:field-names nil)
=> (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
- ("Mikhail" "1") ("Nikita" "1") ("Vladamir" "2") ("Yuri" "1"))
+ ("Mikhail" "1") ("Nikita" "1") ("Vladimir" "2") ("Yuri" "1"))
(select [last-name] :from [employee]
:where [like [email] "%org"]
@@ -1384,7 +1439,7 @@ ID FORENAME SURNAME EMAIL
:result-types :auto)
=> (10)
-(clsql:select [avg [height]] :from [employee] :flatp t :field-names nil)
+(select [avg [height]] :from [employee] :flatp t :field-names nil)
=> (1.58999584d0)
(select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
@@ -1403,19 +1458,27 @@ ID FORENAME SURNAME EMAIL
:flatp t)
=> (1 2 3 4)
-(clsql:select [emplid] :from [employee]
- :where [in [emplid] '(1 2 3 4)]
- :flatp t
- :order-by [emplid]
- :field-names nil)
+(select [emplid] :from [employee]
+ :where [in [emplid] '(1 2 3 4)]
+ :flatp t
+ :order-by [emplid]
+ :field-names nil)
=> (1 2 3 4)
+(select [emplid] :from [employee]
+ :order-by [emplid]
+ :limit 5
+ :offset 3
+ :field-names nil
+ :flatp t)
+=> (4 5 6 7 8)
+
(select [first-name] [last-name] :from [employee]
:field-names nil
:order-by '(([first-name] :asc) ([last-name] :desc)))
=> (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
- ("Nikita" "Kruschev") ("Vladamir" "Putin") ("Vladamir" "Lenin")
+ ("Nikita" "Kruschev") ("Vladimir" "Putin") ("Vladimir" "Lenin")
("Yuri" "Andropov"))
(select [last-name] :from [employee]
@@ -1426,15 +1489,13 @@ ID FORENAME SURNAME EMAIL
:field-names nil)
=> ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
"Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
- "Trotsky" "Vladamir" "Yeltsin" "Yuri")
+ "Trotsky" "Vladimir" "Yeltsin" "Yuri")
Side Effects
-
Whatever effects the execution of the SQL query has on
the underlying database, if any.
-
Affected by
@@ -1463,6 +1524,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
@@ -1483,7 +1554,7 @@ ID FORENAME SURNAME EMAIL
DO-QUERY
- Macro DO-QUERY
+ DO-QUERY
Iterate over all the tuples of a query.
Macro
@@ -1659,85 +1730,15 @@ ID FORENAME SURNAME EMAIL
-
-
- FOR-EACH-ROW
-
-
- Function FOR-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
-
-
- Function PREPARE-SQL
- Create a prepared statement.
- Function
-
-
- Syntax
-
- prepare-sql sql-stmt types &key database result-types field-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
-
-
- Function RUN-PREPARED-SQL
- Execute a prepared statement.
- Function
-
-
- Syntax
-
- run-prepared-sql prepared-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
-
-
- Function FREE-PREPARED-SQL
- Delete a prepared statement object.
- Function
-
-
- Syntax
-
- free-prepared-sql prepared-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
-
-
- Function BIND-PARAMETER
- Bind a parameter in a prepared statement.
- Function
-
-
- Syntax
-
- bind-parameter prepared-stmt position value =>
-
-
- Arguments and Values
-
-
-
-
-
- Description
- Sets the value of a parameter in a prepared statement.
-
-
-
- Examples
-
-
-
-
-
- Side Effects
-
-
-
-
-
- Affected by
-
-
-
-
-
-
-
- Exceptional Situations
-
-
-
-
-
- See Also
-
-
-
-
-
-
-
- Notes
-
-
-
-
-
-
+