+21 May 2004 Marcus Pearce (m.t.pearce@city.ac.uk)
+ * sql/basic-sql.lisp: reworked docstrings.
+ * sql/transactions.lisp: reworked docstrings.
+ * sql/sql.lisp: reworked docstrings.
+ * sql/initialize.lisp: reworked docstrings. INITIALIZE-DATABASE-TYPE
+ sets *DEFAULT-DATABASE-TYPE* for CommonSQL compatibility.
+ * sql/database.lisp: reworked docstrings.
+ * doc/TODO: added notes about START-TRANSACTION and IN-TRANSACTION-P
+ and FDML extensions and database extensions.
+
20 May 2004 Kevin Rosenberg (kevin@rosenberg.net)
* db-oracle/oracle-sql: Use clsql-specific error conditions. Use owner keyword.
* db-oracle/make9.sh: add makefile for building with Oracle 9 client
2. CLSQL extensions to CommonSQL
- - RESULT-TYPES, FIELD-NAMES keywords for SELECT and QUERY
+ - RESULT-TYPES, FIELD-NAMES keywords for SELECT, QUERY, DO-QUERY and
+ MAP-QUERY.
- New types such as wall-time, boolean, and bigint
index-exists-p, create-sequence, drop-sequence, list-sequences,
sequence-exists-p, sequence-next, sequence-last, set-sequence-position
+ - FDML: TRUNCATE-DATABASE, DESCRIBE-TABLE, FOR-EACH-ROW and large object
+ support.
+
- OODML: *db-auto-sync*
- SELECT: additional keyword arguments accepted include :LIMIT, :OFFSET,
- CREATE-TABLE: keyword args :TRANSACTIONS (for MySQL) and :CONSTRAINTS
description arg accepts optional DB-TYPE string.
+ - transactions: START-TRANSACTION and IN-TRANSACTION-P.
+
+ - FIND-DATABASE: :db-type keyword arg.
+
+ - CONNECT: :make-default and :pool keyword args.
+
+
4. Documenting lower level, non-CommonSQL functions (some of this is already
done).
- connection pools
- database-query-result-set
-
+ - with-default-database, with-database, create-database, probe-database,
+ destroy-database, list-databases.
+
5. Notes on any peculiarities of each of the backends (e.g., unsupported
features, notable extensions etc.).
(defgeneric query (query-expression &key database result-types flatp field-names)
(:documentation
- "Execute the SQL query expression QUERY-EXPRESSION on the given
-DATABASE which defaults to *default-database*. RESULT-TYPES is a list
-of symbols such as :string and :integer, one for each field in the
-query, which are used to specify the types to return. The FLATP
-argument, which has a default value of nil, specifies if full
-bracketed results should be returned for each matched entry. If FLATP
-is nil, the results are returned as a list of lists. If FLATP is t,
-the results are returned as elements of a list, only if there is only
-one result per row. Returns a list of lists of values of the result of
-that expression and a list of field names selected in sql-exp."))
+ "Executes the SQL query expression QUERY-EXPRESSION, which may
+be an SQL expression or a string, on the supplied DATABASE which
+defaults to *DEFAULT-DATABASE*. RESULT-TYPES is a list of symbols
+which specifies the lisp type for each field returned by
+QUERY-EXPRESSION. If RESULT-TYPES is nil all results are returned
+as strings whereas the default value of :auto means that the lisp
+types are automatically computed for each field. FIELD-NAMES is t
+by default which means that the second value returned is a list
+of strings representing the columns selected by
+QUERY-EXPRESSION. If FIELD-NAMES is nil, the list of column names
+is not returned as a second value. FLATP has a default value of
+nil which means that the results are returned as a list of
+lists. If FLATP is t and only one result is returned for each
+record selected by QUERY-EXPRESSION, the results are returned as
+elements of a list."))
(defmethod query ((query-expression string) &key (database *default-database*)
(result-types :auto) (flatp nil) (field-names t))
(record-sql-command query-expression database)
- (multiple-value-bind (rows names) (database-query query-expression database result-types
- field-names)
+ (multiple-value-bind (rows names)
+ (database-query query-expression database result-types field-names)
(let ((result (if (and flatp (= 1 (length (car rows))))
(mapcar #'car rows)
rows)))
(defgeneric execute-command (expression &key database)
(:documentation
- "Executes the SQL command specified by EXPRESSION for the database
-specified by DATABASE, which has a default value of
-*DEFAULT-DATABASE*. The argument EXPRESSION may be any SQL statement
-other than a query. To run a stored procedure, pass an appropriate
-string. The call to the procedure needs to be wrapped in a BEGIN END
-pair."))
+ "Executes the SQL command EXPRESSION, which may be an SQL
+expression or a string representing any SQL statement apart from
+a query, on the supplied DATABASE which defaults to
+*DEFAULT-DATABASE*."))
(defmethod execute-command ((sql-expression string)
&key (database *default-database*))
;;; Database handling
(defvar *connect-if-exists* :error
- "Default value for the if-exists parameter of connect calls.")
+ "Default value for the if-exists keyword argument in calls to
+CONNECT. Meaningful values are :new, :warn-new, :error, :warn-old
+and :old.")
(defvar *connected-databases* nil
"List of active database objects.")
(defun connected-databases ()
- "Return the list of active database objects."
+ "Returns the list of active database objects."
*connected-databases*)
(defvar *default-database* nil
(eql (database-state database) :open))
(defun find-database (database &key (errorp t) (db-type nil))
- "The function FIND-DATABASE, given a string DATABASE, searches
-amongst the connected databases for one matching the name DATABASE. If
-there is exactly one such database, it is returned and the second
-return value count is 1. If more than one databases match and ERRORP
-is nil, then the most recently connected of the matching databases is
-returned and count is the number of matches. If no matching database
-is found and ERRORP is nil, then nil is returned. If none, or more
-than one, matching databases are found and ERRORP is true, then an
-error is signalled. If the argument database is a database, it is
-simply returned."
+ "Returns the connected databases of type DB-TYPE whose names
+match the string DATABASE. If DATABASE is a database object, it
+is returned. If DB-TYPE is nil all databases matching the string
+DATABASE are considered. If no matching databases are found and
+ERRORP is nil then nil is returned. If ERRORP is nil and one or
+more matching databases are found, then the most recently
+connected database is returned as a first value and the number of
+matching databases is returned as a second value. If no, or more
+than one, matching databases are found and ERRORP is true, an
+error is signalled."
(etypecase database
(database
(values database 1))
(make-default t)
(pool nil)
(database-type *default-database-type*))
- "Connects to a database of the given database-type, using the
-type-specific connection-spec. The value of if-exists determines what
-happens if a connection to that database is already established. A
-value of :new means create a new connection. A value of :warn-new
-means warn the user and create a new connect. A value of :warn-old
-means warn the user and use the old connection. A value of :error
-means fail, notifying the user. A value of :old means return the old
-connection. If make-default is true, then *default-database* is set
-to the new connection, otherwise *default-database is not changed. If
-pool is t the connection will be taken from the general pool, if pool
-is a conn-pool object the connection will be taken from this pool."
+ "Connects to a database of the supplied DATABASE-TYPE which
+defaults to *DEFAULT-DATABASE-TYPE*, using the type-specific
+connection specification CONNECTION-SPEC. The value of IF-EXISTS,
+which defaults to *CONNECT-IF-EXISTS*, determines what happens if
+a connection to the database specified by CONNECTION-SPEC is
+already established. A value of :new means create a new
+connection. A value of :warn-new means warn the user and create
+a new connect. A value of :warn-old means warn the user and use
+the old connection. A value of :error means fail, notifying the
+user. A value of :old means return the old connection.
+MAKE-DEFAULT is t by default which means that *DEFAULT-DATABASE*
+is set to the new connection, otherwise *DEFAULT-DATABASE* is not
+changed. If POOL is t the connection will be taken from the
+general pool, if POOL is a CONN-POOL object the connection will
+be taken from this pool."
(unless database-type
(error 'sql-database-error :message "Must specify a database-type."))
(defun disconnect (&key (database *default-database*) (error nil))
- "Closes the connection to DATABASE and resets *default-database* if
-that database was disconnected. If database is a database object, then
-it is used directly. Otherwise, the list of connected databases is
-searched to find one with DATABASE as its connection
-specifications. If no such database is found, then if ERROR and
-DATABASE are both non-nil an error is signaled, otherwise DISCONNECT
-returns nil. If the database is from a pool it will be released to
-this pool."
+ "Closes the connection to DATABASE and resets
+*DEFAULT-DATABASE* if that database was disconnected. If DATABASE
+is a database instance, this object is closed. If DATABASE is a
+string, then a connected database whose name matches DATABASE is
+sought in the list of connected databases. If no matching
+database is found and ERROR and DATABASE are both non-nil an
+error is signaled, otherwise nil is returned. If the database is
+from a pool it will be released to this pool."
(let ((database (find-database database :errorp (and database error))))
(when database
(if (conn-pool database)
(quote ,template))))))
(defun reconnect (&key (database *default-database*) (error nil) (force t))
- "Reconnects DATABASE to its underlying RDBMS. If successful, returns
-t and the variable *default-database* is set to the newly reconnected
-database. The default value for DATABASE is *default-database*. If
-DATABASE is a database object, then it is used directly. Otherwise,
-the list of connected databases is searched to find one with database
-as its connection specifications (see CONNECT). If no such database is
-found, then if ERROR and DATABASE are both non-nil an error is
-signaled, otherwise RECONNECT returns nil. FORCE controls whether an
-error should be signaled if the existing database connection cannot be
-closed. When non-nil (this is the default value) the connection is
-closed without error checking. When FORCE is nil, an error is signaled
-if the database connection has been lost."
+ "Reconnects DATABASE which defaults to *DEFAULT-DATABASE* to
+the underlying database management system. On success, t is
+returned and the variable *DEFAULT-DATABASE* is set to the newly
+reconnected database. If DATABASE is a database instance, this
+object is closed. If DATABASE is a string, then a connected
+database whose name matches DATABASE is sought in the list of
+connected databases. If no matching database is found and ERROR
+and DATABASE are both non-nil an error is signaled, otherwise nil
+is returned. When the current database connection cannot be
+closed, if FORCE is non-nil, as it is by default, the connection
+is closed and errors are suppressed. If force is nil and the
+database connection cannot be closed, an error is signalled."
(let ((db (etypecase database
(database database)
((or string list)
(defun status (&optional full)
- "The function STATUS prints status information to the standard
-output, for the connected databases and initialized database types. If
-full is T, detailed status information is printed. The default value
-of full is NIL."
+ "Prints information about the currently connected databases to
+*STANDARD-OUTPUT*. The argument FULL is nil by default and a
+value of t means that more detailed information about each
+database is printed."
(flet ((get-data ()
(let ((data '()))
(dolist (db (connected-databases) data)
(mapc #'database-type-load-foreign *loaded-database-types*))
(defvar *default-database-type* nil
- "Specifies the default type of database.")
+ "Designates the default database type which is initialised by
+ the function INITIALISE-DATABASE-TYPE.")
(defvar *initialized-database-types* nil
- "Contains a list of database types which have been initialized by calls
-to initialize-database-type.")
+ "A list of database types which have currently been initialised
+by calling INITIALIZE-DATABASE-TYPE.")
(defun initialize-database-type (&key (database-type *default-database-type*))
- "Initialize the given database-type, if it is not already
-initialized, as indicated by `*initialized-database-types*'."
+ "Initializes the supplied DATABASE-TYPE, if it is not already
+initialized, as indicated by *INITIALIZED-DATABASE-TYPES* and
+returns DATABASE-TYPE. *DEFAULT-DATABASE-TYPE* is set to
+DATABASE-TYPE and, if DATABASE-TYPE has not been initialised, it
+is added to *INITIALIZED-DATABASE-TYPES*. "
(when (member database-type *initialized-database-types*)
(return-from initialize-database-type database-type))
(when (database-initialize-database-type database-type)
(push database-type *initialized-database-types*)
+ (setf *default-database-type* database-type)
database-type))
(defun print-query (query-exp &key titles (formats t) (sizes t) (stream t)
(database *default-database*))
- "The PRINT-QUERY function takes a symbolic SQL query expression and
-formatting information and prints onto STREAM a table containing the
-results of the query. A list of strings to use as column headings is
-given by TITLES, which has a default value of NIL. The FORMATS
-argument is a list of format strings used to print each attribute, and
-has a default value of T, which means that ~A or ~VA are used if sizes
-are provided or computed. The field sizes are given by SIZES. It has a
-default value of T, which specifies that minimum sizes are
-computed. The output stream is given by STREAM, which has a default
-value of T. This specifies that *STANDARD-OUTPUT* is used."
+ "Prints a tabular report of the results returned by the SQL
+query QUERY-EXP, which may be a symbolic SQL expression or a
+string, in DATABASE which defaults to *DEFAULT-DATABASE*. The
+report is printed onto STREAM which has a default value of t
+which means that *STANDARD-OUTPUT* is used. The TITLE argument,
+which defaults to nil, allows the specification of a list of
+strings to use as column titles in the tabular output. SIZES
+accepts a list of column sizes, one for each column selected by
+QUERY-EXP, to use in formatting the tabular report. The default
+value of t means that minimum sizes are computed. FORMATS is a
+list of format strings to be used for printing each column
+selected by QUERY-EXP. The default value of FORMATS is t meaning
+that ~A is used to format all columns or ~VA if column sizes are
+used."
(flet ((compute-sizes (data)
(mapcar #'(lambda (x)
(apply #'max (mapcar #'(lambda (y)
(av-pairs nil)
(query nil)
(database *default-database*))
- "Inserts a set of values into a table. The records created contain
-values for attributes (or av-pairs). The argument VALUES is a list of
-values. If ATTRIBUTES is supplied then VALUES must be a corresponding
-list of values for each of the listed attribute names. If AV-PAIRS is
-non-nil, then both ATTRIBUTES and VALUES must be nil. If QUERY is
-non-nil, then neither VALUES nor AV-PAIRS should be. QUERY should be a
-query expression, and the attribute names in it must also exist in the
-table INTO. The default value of DATABASE is *DEFAULT-DATABASE*."
+ "Inserts records into the table specified by INTO in DATABASE
+which defaults to *DEFAULT-DATABASE*. There are five ways of
+specifying the values inserted into each row. In the first VALUES
+contains a list of values to insert and ATTRIBUTES, AV-PAIRS and
+QUERY are nil. This can be used when values are supplied for all
+attributes in INTO. In the second, ATTRIBUTES is a list of column
+names, VALUES is a corresponding list of values and AV-PAIRS and
+QUERY are nil. In the third, ATTRIBUTES, VALUES and QUERY are nil
+and AV-PAIRS is an alist of (attribute value) pairs. In the
+fourth, VALUES, AV-PAIRS and ATTRIBUTES are nil and QUERY is a
+symbolic SQL query expression in which the selected columns also
+exist in INTO. In the fifth method, VALUES and AV-PAIRS are nil
+and ATTRIBUTES is a list of column names and QUERY is a symbolic
+SQL query expression which returns values for the specified
+columns."
(let ((stmt (make-sql-insert :into into :attrs attributes
:vals values :av-pairs av-pairs
:subquery query)))
(defun delete-records (&key (from nil)
(where nil)
(database *default-database*))
- "Deletes rows from a database table specified by FROM in which the
-WHERE condition is true. The argument DATABASE specifies a database
-from which the records are to be removed, and defaults to
-*default-database*."
+ "Deletes records satisfying the SQL expression WHERE from the
+table specified by FROM in DATABASE specifies a database which
+defaults to *DEFAULT-DATABASE*."
(let ((stmt (make-instance 'sql-delete :from from :where where)))
(execute-command stmt :database database)))
(av-pairs nil)
(where nil)
(database *default-database*))
- "Changes the values of existing fields in TABLE with columns
-specified by ATTRIBUTES and VALUES (or AV-PAIRS) where the WHERE
-condition is true."
+ "Updates the attribute values of existing records satsifying
+the SQL expression WHERE in the table specified by TABLE in
+DATABASE which defaults to *DEFAULT-DATABASE*. There are three
+ways of specifying the values to update for each row. In the
+first, VALUES contains a list of values to use in the update and
+ATTRIBUTES, AV-PAIRS and QUERY are nil. This can be used when
+values are supplied for all attributes in TABLE. In the second,
+ATTRIBUTES is a list of column names, VALUES is a corresponding
+list of values and AV-PAIRS and QUERY are nil. In the third,
+ATTRIBUTES, VALUES and QUERY are nil and AV-PAIRS is an alist
+of (attribute value) pairs."
(when av-pairs
(setf attributes (mapcar #'car av-pairs)
values (mapcar #'cadr av-pairs)))
&key (database '*default-database*) (result-types :auto))
&body body)
"Repeatedly executes BODY within a binding of ARGS on the
-attributes of each record resulting from QUERY-EXPRESSION. The
-return value is determined by the result of executing BODY. The
-default value of DATABASE is *DEFAULT-DATABASE*."
+fields of each row selected by the SQL query QUERY-EXPRESSION,
+which may be a string or a symbolic SQL expression, in DATABASE
+which defaults to *DEFAULT-DATABASE*. The values returned by the
+execution of BODY are returned. RESULT-TYPES is a list of symbols
+which specifies the lisp type for each field returned by
+QUERY-EXPRESSION. If RESULT-TYPES is nil all results are returned
+as strings whereas the default value of :auto means that the lisp
+types are automatically computed for each field."
(let ((result-set (gensym "RESULT-SET-"))
(qe (gensym "QUERY-EXPRESSION-"))
(columns (gensym "COLUMNS-"))
(defun map-query (output-type-spec function query-expression
&key (database *default-database*)
(result-types :auto))
- "Map the function over all tuples that are returned by the
-query in QUERY-EXPRESSION. The results of the function are
-collected as specified in OUTPUT-TYPE-SPEC and returned like in
-MAP."
+ "Map the function FUNCTION over the attribute values of each
+row selected by the SQL query QUERY-EXPRESSION, which may be a
+string or a symbolic SQL expression, in DATABASE which defaults
+to *DEFAULT-DATABASE*. The results of the function are collected
+as specified in OUTPUT-TYPE-SPEC and returned like in
+MAP. RESULT-TYPES is a list of symbols which specifies the lisp
+type for each field returned by QUERY-EXPRESSION. If RESULT-TYPES
+is nil all results are returned as strings whereas the default
+value of :auto means that the lisp types are automatically
+computed for each field."
(typecase query-expression
(sql-object-query
(map output-type-spec #'(lambda (x) (apply function x))
(defmacro with-transaction ((&key (database '*default-database*)) &rest body)
- "Executes BODY within a transaction for DATABASE (which defaults to
-*DEFAULT-DATABASE*). The transaction is committed if the body finishes
-successfully (without aborting or throwing), otherwise the database is
-rolled back."
+ "Starts a transaction in the database specified by DATABASE,
+which is *DEFAULT-DATABASE* by default, and executes BODY within
+that transaction. If BODY aborts or throws, DATABASE is rolled
+back and otherwise the transaction is committed."
(let ((db (gensym "db-")))
`(let ((,db ,database))
(unwind-protect
(database-abort-transaction ,db))))))
(defun commit (&key (database *default-database*))
- "Commits changes made to DATABASE which defaults to *DEFAULT-DATABASE*."
+ "If DATABASE, which defaults to *DEFAULT-DATABASE*, is
+currently within the scope of a transaction, commits changes made
+since the transaction began."
(database-commit-transaction database))
(defun rollback (&key (database *default-database*))
- "Rolls back changes made in DATABASE, which defaults to
-*DEFAULT-DATABASE* since the last commit, that is changes made since
-the last commit are not recorded."
+ "If DATABASE, which defaults to *DEFAULT-DATABASE*, is
+currently within the scope of a transaction, rolls back changes
+made since the transaction began."
(database-abort-transaction database))
(defun start-transaction (&key (database *default-database*))
"Starts a transaction block on DATABASE which defaults to
-*default-database* and which continues until ROLLBACK or COMMIT are
-called."
+*DEFAULT-DATABASE* and which continues until ROLLBACK or COMMIT
+are called."
(unless (in-transaction-p :database database)
(database-start-transaction database)))
(defun in-transaction-p (&key (database *default-database*))
- "A predicate to test whether we are currently within the scope of a
-transaction in DATABASE."
+ "A predicate to test whether DATABASE, which defaults to
+*DEFAULT-DATABASE*, is currently within the scope of a
+transaction."
(and database (transaction database) (= (transaction-level database) 1)))
projects / clsql.git / commitdiff