From: Kevin M. Rosenberg Date: Fri, 28 May 2004 19:03:58 +0000 (+0000) Subject: r9508: condition fixes X-Git-Tag: v3.8.6~363 X-Git-Url: http://git.kpe.io/?p=clsql.git;a=commitdiff_plain;h=fc6e46a000589bffc1a1b69cb3fe13bcbaade59b r9508: condition fixes --- diff --git a/ChangeLog b/ChangeLog index f347578..f682d2c 100644 --- a/ChangeLog +++ b/ChangeLog @@ -8,6 +8,10 @@ * doc/appendix.xml: Add Oracle backend information * db-oracle/oracle-objects.lisp: Add database-get-type-specifier for universal-time. Convert BIGINT CLSQL type to CHAR SQL type + * db-mysql/mysql-sql.lisp: Fix condition name to sql-connection-error + * doc/ref-clsql.xml: Renamed from ref_clsql.xml. Change the documentation + for map-query to reflect changed in arguments to be CommonSQL compatible. + Updated old clsql conditions to new CommonSQL compatible conditions. 25 May 2004 Kevin Rosenberg * sql/oodml.lisp: (string n) now produces a CHAR field. Add new VARCHAR diff --git a/db-mysql/mysql-sql.lisp b/db-mysql/mysql-sql.lisp index f85ffac..513c9ed 100644 --- a/db-mysql/mysql-sql.lisp +++ b/db-mysql/mysql-sql.lisp @@ -117,7 +117,7 @@ db-native 0 socket-native 0)) (progn (setq error-occurred t) - (error 'sql-connect-error + (error 'sql-connection-error :database-type database-type :connection-spec connection-spec :error-id (mysql-errno mysql-ptr) diff --git a/db-oracle/oracle-sql.lisp b/db-oracle/oracle-sql.lisp index 119a669..222e030 100644 --- a/db-oracle/oracle-sql.lisp +++ b/db-oracle/oracle-sql.lisp @@ -397,11 +397,7 @@ the length of that format.") (#.SQLT-INT (ecase (cd-sizeof cd) (4 - (uffi:deref-array b '(:array :int) irow)) - (8 - (deref-oci-int64 b irow)) - (16 - (deref-oci-int128 b irow)))) + (uffi:deref-array b '(:array :int) irow)))) (#.SQLT-DATE (deref-oci-string b irow (cd-sizeof cd)))))))) (when (and (eq :string (cd-result-type cd)) diff --git a/doc/clsql.xml b/doc/clsql.xml index 3b2ecc9..561316f 100644 --- a/doc/clsql.xml +++ b/doc/clsql.xml @@ -12,7 +12,7 @@ - + diff --git a/doc/ref-clsql.xml b/doc/ref-clsql.xml new file mode 100644 index 0000000..fc99b08 --- /dev/null +++ b/doc/ref-clsql.xml @@ -0,0 +1,2414 @@ + + +%myents; +]> + + + <symbol>CLSQL</symbol> + + This part gives a reference to the symbols exported from the + CLSQL package. These symbols constitute + the normal user-interface of &clsql;. Currently, the symbols of + the &commonsql;-API are not documented here. + + + + + SQL-CONDITION + the super-type of all + &clsql;-specific + conditions + Condition Type + + + Class Precedence List + + + sql-condition + condition + t + + + + + Description + This is the super-type of all + &clsql;-specific conditions + defined by &clsql;, or any of it's + database-specific interfaces. There are no defined + initialization arguments nor any accessors. + + + + + SQL-ERROR + the super-type of all + &clsql;-specific + errors + Condition Type + + + Class Precedence List + + + sql-error + error + serious-condition + sql-condition + condition + t + + + + + Description + This is the super-type of all + &clsql;-specific conditions that + represent errors, as defined by + &clsql;, or any of it's + database-specific interfaces. There are no defined + initialization arguments nor any accessors. + + + + + SQL-WARNING + the super-type of all + &clsql;-specific + warnings + Condition Type + + + Class Precedence List + + + sql-warning + warning + sql-condition + condition + t + + + + + Description + This is the super-type of all + &clsql;-specific conditions that + represent warnings, as defined by + &clsql;, or any of it's + database-specific interfaces. There are no defined + initialization arguments nor any accessors. + + + + + + CLSQL-USER-ERROR + condition representing errors because of invalid + parameters from the library user. + Condition Type + + + Class Precedence List + + + sql-error + sql-condition + condition + t + + + + + Description + This condition represents errors that occur because the + user supplies invalid data to &clsql;. This includes errors such as + an invalid format connection specification or an error in the syntax + for the LOOP macro extensions. + + + + + SQL-CONNECTION-ERROR + condition representing errors during + connection + Condition Type + + + Class Precedence List + + + sql-connection-error + sql-database-error + sql-error + sql-condition + condition + t + + + + + Description + This condition represents errors that occur while trying + to connect to a database. The following initialization + arguments and accessors exist: + + Initarg + Accessor + Description + + :database-type + sql-connection-error-database-type + Database type for the connection attempt + + + :connection-spec + sql-connection-error-connection-spec + The connection specification used in the + connection attempt. + + + :errno + sql-connection-error-errno + The numeric or symbolic error specification + returned by the database back-end. The values and + semantics of this are interface specific. + + + :error + sql-connection-error-error + A string describing the problem that occurred, + possibly one returned by the database back-end. + + + + + + + SQL-DATABASE-ERROR + condition representing errors during query or + command execution + Condition Type + + + Class Precedence List + + + sql-database-error + sql-error + error + serious-condition + sql-condition + condition + t + + + + + Description + This condition represents errors that occur while + executing SQL statements, either as part of query operations + or command execution, either explicitly or implicitly, as + caused e.g. by with-transaction. + The following initialization arguments and accessors exist: + + Initarg + Accessor + Description + + :database + sql-database-error-database + The database object that was involved in the + incident. + + + :expression + sql-database-error-expression + The SQL expression whose execution caused the error. + + + :errno + sql-database-error-errno + The numeric or symbolic error specification + returned by the database back-end. The values and + semantics of this are interface specific. + + + :error + sql-database-error-error + A string describing the problem that occurred, + possibly one returned by the database back-end. + + + + + + + + + *DEFAULT-DATABASE-TYPE* + The default database type to use + Variable + + + Value Type + Any keyword representing a valid database back-end of + &clsql;, or + nil. + + + Initial Value + nil + + + Description + The value of this variable is used in calls to + initialize-database-type and + connect as the default + value of the database-type + parameter. + + If the value of this variable is nil, + then all calls to + initialize-database-type or + connect will have to specify the + database-type to use, or a + general-purpose error will be signalled. + + + + Examples + +(setf *default-database-type* :mysql) +=> :mysql +(initialize-database-type) +=> t + + + + Affected By + None. + + + See Also + None. + + + Notes + None. + + + + + *INITIALIZED-DATABASE-TYPES* + List of all initialized database types + Variable + + + Value Type + A list of all initialized database types, each of which + represented by it's corresponding keyword. + + + Initial Value + nil + + + Description + This variable is updated whenever + initialize-database-type is called for a + database type which hasn't already been initialized before, + as determined by this variable. In that case the keyword + representing the database type is pushed onto the list + stored in + *INITIALIZED-DATABASE-TYPES*. + + Attempts to modify the value of this variable will + result in undefined behaviour. + + + + Examples + +(setf *default-database-type* :mysql) +=> :mysql +(initialize-database-type) +=> t +*initialized-database-types* +=> (:MYSQL) + + + + Affected By + + + initialize-database-type + + + + + See Also + None. + + + Notes + Direct access to this variable is primarily provided + because of compatibility with Harlequin's Common + SQL. + + + + + INITIALIZE-DATABASE-TYPE + Initializes a database type + Function + + + Syntax + initialize-database-type &key database-type => result + + + Arguments and Values + + + database-type + + The database type to initialize, i.e. a keyword + symbol denoting a known database back-end. Defaults to + the value of + *default-database-type*. + + + + result + + Either nil if the initialization + attempt fails, or t otherwise. + + + + + + Description + If the back-end specified by + database-type has not already been + initialized, as seen from + *initialized-database-types*, an attempt is + made to initialize the database. If this attempt succeeds, + or the back-end has already been initialized, the function + returns t, and places the keyword denoting the database type + onto the list stored in + *initialized-database-types*, if not + already present. + If initialization fails, the function returns + nil, and/or signals an error of type + clsql-error. The kind of action + taken depends on the back-end and the cause of the + problem. + + + Examples + +*initialized-database-types* +=> NIL +(setf *default-database-type* :mysql) +=> :MYSQL +(initialize-database-type) +>> Compiling LAMBDA (#:G897 #:G898 #:G901 #:G902): +>> Compiling Top-Level Form: +>> +=> T +*initialized-database-types* +=> (:MYSQL) +(initialize-database-type) +=> T +*initialized-database-types* +=> (:MYSQL) + + + + Side Effects + The database back-end corresponding to the database type + specified is initialized, unless it has already been + initialized. This can involve any number of other side + effects, as determined by the back-end implementation (like + e.g. loading of foreign code, calling of foreign code, + networking operations, etc.). If initialization is + attempted and succeeds, the + database-type is pushed onto the list + stored in + *initialized-database-types*. + + + Affected by + + + *default-database-type* + *initialized-database-types* + + + + + Exceptional Situations + If an error is encountered during the initialization + attempt, the back-end may signal errors of kind + clsql-error. + + + See Also + None. + + + Notes + None. + + + + + + *CONNECT-IF-EXISTS* + Default value for the + if-exists parameter of + connect. + Variable + + + Value Type + A valid argument to the if-exists + parameter of connect, i.e. one of + + :new + :warn-new + :error + :warn-old + :old + . + + + + Initial Value + :error + + + Description + The value of this variable is used in calls to + connect as the default + value of the if-exists + parameter. See connect for + the semantics of the valid values for this variable. + + + Examples + None. + + + Affected By + None. + + + See Also + + + connect + + + + + Notes + None. + + + + + CONNECTED-DATABASES + Return the list of active database + objects. + Function + + + Syntax + connected-databases => databases + + + Arguments and Values + + + databases + + The list of active database objects. + + + + + + Description + This function returns the list of active database + objects, i.e. all those database objects created by calls to + connect, which have not been closed by + calling disconnect on them. + + The consequences of modifying the list returned by + connected-databases are + undefined. + + + + Examples + +(connected-databases) +=> NIL +(connect '(nil "template1" "dent" nil) :database-type :postgresql) +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}> +(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) +=> #<CLSQL-MYSQL:MYSQL-DATABASE {4830C5AD}> +(connected-databases) +=> (#<CLSQL-MYSQL:MYSQL-DATABASE {4830C5AD}> + #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>) +(disconnect) +=> T +(connected-databases) +=> (#<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>) +(disconnect) +=> T +(connected-databases) +=> NIL + + + + Side Effects + None. + + + Affected By + + + connect + disconnect + + + + + Exceptional Situations + None. + + + See Also + None. + + + Notes + None. + + + + + *DEFAULT-DATABASE* + The default database object to use + Variable + + + Value Type + Any object of type database, or nil to + indicate no default database. + + + Initial Value + nil + + + Description + Any function or macro in + &clsql; that operates on a + database uses the value of this variable as the default + value for it's database + parameter. + The value of this parameter is changed by calls to + connect, which sets + *default-database* to the database object + it returns. It is also changed by calls to + disconnect, when the database object + being disconnected is the same as the value of + *default-database*. In this case + disconnect sets + *default-database* to the first database + that remains in the list of active databases as returned by + connected-databases, or + nil if no further active databases + exist. + The user may change *default-database* + at any time to a valid value of his choice. + + If the value of *default-database* is + nil, then all calls to + &clsql; functions on databases + must provide a suitable database + parameter, or an error will be signalled. + + + + Examples + +(connected-databases) +=> NIL +(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) +=> #<CLSQL-MYSQL:MYSQL-DATABASE {48385F55}> +(connect '(nil "template1" "dent" nil) :database-type :postgresql) +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {483868FD}> +(connect '("dent" "newesim" "dent" "dent") :database-type :mysql :if-exists :new) +=> #<CLSQL-MYSQL:MYSQL-DATABASE {48387265}> +*default-database* +=> #<CLSQL-MYSQL:MYSQL-DATABASE {48387265}> +(disconnect) +=> T +*default-database* +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {483868FD}> +(disconnect) +=> T +*default-database* +=> #<CLSQL-MYSQL:MYSQL-DATABASE {48385F55}> +(disconnect) +=> T +*default-database* +=> NIL +(connected-databases) +=> NIL + + + + Affected By + + + connect + disconnect + + + + + See Also + + + connected-databases + + + + + Notes + + This variable is intended to facilitate working with + &clsql; in an interactive + fashion at the top-level loop, and because of this, + connect and + disconnect provide some fairly + complex behaviour to keep + *default-database* set to useful values. + Programmatic use of &clsql; + should never depend on the value of + *default-database* and should provide + correct database objects via the + database parameter to functions + called. + + + + + + + DATABASE + The super-type of all + &clsql; databases + Class + + + Class Precedence List + + + database + standard-object + t + + + + + Description + This class is the superclass of all + &clsql; databases. The different + database back-ends derive subclasses of this class to + implement their databases. No instances of this class are + ever created by &clsql;. + + + + + CLOSED-DATABASE + The class representing all closed + &clsql; databases + Class + + + Class Precedence List + + + closed-database + standard-object + t + + + + + Description + &clsql; database + instances are changed to this class via + change-class after they are closed via + disconnect. All functions and generic + functions that take database objects as arguments will + signal errors of type + clsql-closed-error when they are + called on instances of closed-database, with + the exception of database-name, which + will continue to work as for instances of + database. + + + + + + DATABASE-NAME + Get the name of a database object + Generic Function + + + Syntax + database-name database => name + + + Arguments and Values + + + database + + A database object, either of type + database or of type + closed-database. + + + + name + + A string describing the identity of the database + to which this database object is connected to. + + + + + + Description + This function returns the database name of the given + database. The database name is a string which somehow + describes the identity of the database to which this + database object is or has been connected. The database name + of a database object is determined at + connect time, when a call to + database-name-from-spec derives the + database name from the connection specification passed to + connect in the + connection-spec parameter. + The database name is used via + find-database in + connect to determine whether database + connections to the specified database exist already. + Usually the database name string will include + indications of the host, database name, user, or port that + where used during the connection attempt. The only + important thing is that this string shall try to identify + the database at the other end of the connection. Connection + specifications parts like passwords and credentials shall + not be used as part of the database name. + + + Examples + +(database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql) +=> "dent/newesim/dent" +(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) +=> #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}> +(database-name *default-database*) +=> "dent/newesim/dent" + +(database-name-from-spec '(nil "template1" "dent" nil) :postgresql) +=> "/template1/dent" +(connect '(nil "template1" "dent" nil) :database-type :postgresql) +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> +(database-name *default-database*) +=> "/template1/dent" + +(database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql) +=> "www.pmsf.de/template1/dent" + + + + Side Effects + None. + + + Affected By + + + database-name-from-spec + + + + + Exceptional Situations + Will signal an error if the object passed as the + database parameter is neither of type + database nor of type + closed-database. + + + See Also + + + connect + find-database + + + + + Notes + None. + + + + + FIND-DATABASE + Locate a database object through it's + name. + Function + + + Syntax + find-database database &optional errorp => result + + + Arguments and Values + + + database + + A database object or a string, denoting a database + name. + + + + errorp + + A generalized boolean. Defaults to + t. + + + + result + + Either a database object, or, if + errorp is nil, + possibly nil. + + + + + + Description + find-database locates an active + database object given the specification in + database. If + database is an object of type + database, find-database + returns this. Otherwise it will search the active databases + as indicated by the list returned by + connected-databases for a database + whose name (as returned by + database-name is equal as per + string= to the string passed as + database. If it succeeds, it returns + the first database found. + If it fails to find a matching database, it will signal + an error of type clsql-error if + errorp is true. If + errorp is nil, it + will return nil instead. + + + Examples + +(database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql) +=> "dent/newesim/dent" +(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) +=> #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}> +(database-name *default-database*) +=> "dent/newesim/dent" + +(database-name-from-spec '(nil "template1" "dent" nil) :postgresql) +=> "/template1/dent" +(connect '(nil "template1" "dent" nil) :database-type :postgresql) +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> +(database-name *default-database*) +=> "/template1/dent" + +(database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql) +=> "www.pmsf.de/template1/dent" + +(find-database "dent/newesim/dent") +=> #<CLSQL-MYSQL:MYSQL-DATABASE {484E91C5}> +(find-database "/template1/dent") +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> +(find-database "www.pmsf.de/template1/dent" nil) +=> NIL +(find-database **) +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> + + + + Side Effects + None. + + + Affected By + + + connected-databases + + + + + Exceptional Situations + Will signal an error of type + clsql-error if no matching database + can be found, and errorp is true. + Will signal an error if the value of + database is neither an object of type + database nor a string. + + + See Also + + + database-name + database-name-from-spec + + + + + Notes + None. + + + + + + CONNECT + create a connection to a database + Function + + + Syntax + connect connection-spec &key if-exists database-type pool => database + + + Arguments and Values + + + connection-spec + + A connection specification + + + + if-exists + + This indicates the action to take if a connection + to the same database exists already. See below for the + legal values and actions. It defaults to the value of + *connect-if-exists*. + + + + database-type + + A database type specifier, i.e. a keyword. + This defaults to the value of + *default-database-type* + + + + pool + + A boolean flag. If &t;, acquire connection from a + pool of open connections. If the pool is empty, a new + connection is created. The default is &nil;. + + + + + database + + The database object representing the connection. + + + + + + Description + This function takes a connection specification and + a database type and creates a connection to the database + specified by those. The type and structure of the + connection specification depend on the database type. + The parameter if-exists specifies + what to do if a connection to the database specified exists + already, which is checked by calling + find-database on the database name + returned by database-name-from-spec + when called with the connection-spec + and database-type parameters. The + possible values of if-exists are: + + + :new + + Go ahead and create a new connection. + + + + :warn-new + + This is just like :new, but + also signals a warning of type + clsql-exists-warning, + indicating the old and newly created + databases. + + + + :error + + This will cause connect to + signal a correctable error of type + clsql-exists-error. The + user may choose to proceed, either by indicating + that a new connection shall be created, via the + restart create-new, or by + indicating that the existing connection shall be + used, via the restart + use-old. + + + + :old + + This will cause connect to + use an old connection if one exists. + + + + :warn-old + + This is just like :old, but + also signals a warning of type + clsql-exists-warning, + indicating the old database used, via the slots + old-db and + new-db + + + + + The database name of the returned database object will + be the same under string= as that which + would be returned by a call to + database-name-from-spec with the given + connection-spec and + database-type parameters. + + + Examples + +(database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql) +=> "dent/newesim/dent" +(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) +=> #<CLSQL-MYSQL:MYSQL-DATABASE {48036F6D}> +(database-name *) +=> "dent/newesim/dent" + +(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) +>> In call to CONNECT: +>> There is an existing connection #<CLSQL-MYSQL:MYSQL-DATABASE {48036F6D}> to database dent/newesim/dent. +>> +>> Restarts: +>> 0: [CREATE-NEW] Create a new connection. +>> 1: [USE-OLD ] Use the existing connection. +>> 2: [ABORT ] Return to Top-Level. +>> +>> Debug (type H for help) +>> +>> (CONNECT ("dent" "newesim" "dent" "dent") :IF-EXISTS NIL :DATABASE-TYPE ...) +>> Source: +>> ; File: /prj/CLSQL/sql/sql.cl +>> (RESTART-CASE (ERROR 'CLSQL-EXISTS-ERROR :OLD-DB OLD-DB) +>> (CREATE-NEW NIL :REPORT "Create a new connection." +>> (SETQ RESULT #)) +>> (USE-OLD NIL :REPORT "Use the existing connection." +>> (SETQ RESULT OLD-DB))) +>> 0] 0 +=> #<CLSQL-MYSQL:MYSQL-DATABASE {480451F5}> + + + + Side Effects + A database connection is established, and the resultant + database object is registered, so as to appear in the list + returned by connected-databases. + + + Affected by + + + *default-database-type* + *connect-if-exists* + + + + + Exceptional Situations + If the connection specification is not syntactically or + semantically correct for the given database type, an error + of type clsql-invalid-spec-error is + signalled. If during the connection attempt an error is + detected (e.g. because of permission problems, network + trouble or any other cause), an error of type + sql-connection-error is + signalled. + If a connection to the database specified by + connection-spec exists already, + conditions are signalled according to the + if-exists parameter, as described + above. + + + See Also + + + connected-databases + disconnect + + + + + Notes + None. + + + + + + DISCONNECT + close a database connection + Function + + + Syntax + disconnect &key database pool => t + + + Arguments and Values + + + pool + + A boolean flag indicating whether to put the database into a +pool of opened databases. If &t;, rather than terminating the database connection, the +connection is left open and the connection is placed into a pool of connections. Subsequent +calls to connect can then reuse this connection. +The default is &nil;. + + + + + database + + The database to disconnect, which defaults to the + database indicated by + *default-database*. + + + + + + Description + This function takes a database object as + returned by connect, and closes the + connection. The class of the object passed is changed to + closed-database after the disconnection + succeeds, thereby preventing further use of the object as + an argument to &clsql; functions, + with the exception of database-name. + If the user does pass a closed database object to any other + &clsql; function, an error of type + clsql-closed-error is + signalled. + + + Examples + +(disconnect :database (find-database "dent/newesim/dent")) +=> T + + + + Side Effects + The database connection is closed, and the database + object is removed from the list of connected databases as + returned by connected-databases. + The class of the database object is changed to + closed-database. + If the database object passed is the same under + eq as the value of + *default-database*, then + *default-database* is set to the first + remaining database from + connected-databases or to nil if no + further active database exists. + + + Affected by + + + *default-database* + + + + + Exceptional Situations + If during the disconnection attempt an error is + detected (e.g. because of network trouble or any other + cause), an error of type clsql-error + might be signalled. + + + See Also + + + connect + closed-database + + + + + Notes + None. + + + + + + DISCONNECT-POOLED + closes all pooled database connections + Function + + + Syntax + disconnect-pool => t + + + Description + This function disconnects all database connections + that have been placed into the pool. Connections are placed + in the pool by calling disconnection. + + + + Examples + +(disconnect-pool) +=> T + + + + Side Effects + Database connections will be closed and entries in the pool are removed. + + + + Affected by + + + disconnect + + + + + Exceptional Situations + If during the disconnection attempt an error is + detected (e.g. because of network trouble or any other + cause), an error of type clsql-error + might be signalled. + + + See Also + + + connect + closed-database + + + + + Notes + None. + + + + + + CREATE-DATABASE + create a database + Function + + + Syntax + create-database connection-spec &key database-type => success + + + Arguments and Values + + + connection-spec + + A connection specification + + + + database-type + + A database type specifier, i.e. a keyword. + This defaults to the value of + *default-database-type* + + + + success + + A boolean flag. If &t;, a new database was + successfully created. + + + + + + + Description + This function creates a database in the database system + specified by database-type. + + + + Examples + +(create-database '("localhost" "new" "dent" "dent") :database-type :mysql) +=> T + +(create-database '("localhost" "new" "dent" "badpasswd") :database-type :mysql) +=> +Error: While trying to access database localhost/new/dent + using database-type MYSQL: + Error database-create failed: mysqladmin: connect to server at 'localhost' failed +error: 'Access denied for user: 'root@localhost' (Using password: YES)' + has occurred. + [condition type: CLSQL-ACCESS-ERROR] + + + + Side Effects + A database will be created on the filesystem of the host. + + + Exceptional Situations + An exception will be thrown if the database system does + not allow new databases to be created or if database creation + fails. + + + Notes + This function may invoke the operating systems + functions. Thus, some database systems may require the + administration functions to be available in the current + PATH. At this time, the + :mysql backend requires + mysqladmin and the + :postgresql backend requires + createdb. + + + + + + DESTROY-DATABASE + destroys a database + Function + + + Syntax + destroy-database connection-spec &key database-type => success + + + Arguments and Values + + + connection-spec + + A connection specification + + + + database-type + + A database type specifier, i.e. a keyword. + This defaults to the value of + *default-database-type* + + + + success + + A boolean flag. If &t;, the database was + successfully destroyed. + + + + + + + Description + This function destroy a database in the database system + specified by database-type. + + + + Examples + +(destroy-database '("localhost" "new" "dent" "dent") :database-type :postgresql) +=> T + +(destroy-database '("localhost" "new" "dent" "dent") :database-type :postgresql) +=> +Error: While trying to access database localhost/test2/root + using database-type POSTGRESQL: + Error database-destory failed: dropdb: database removal failed: ERROR: database "test2" does not exist + has occurred. + [condition type: CLSQL-ACCESS-ERROR] + + + + Side Effects + A database will be removed from the filesystem of the host. + + + Exceptional Situations + An exception will be thrown if the database system does not + allow databases to be removed, the database does not exist, or + if database removal fails. + + + Notes + This function may invoke the operating systems + functions. Thus, some database systems may require the + administration functions to be available in the current + PATH. At this time, the + :mysql backend requires + mysqladmin and the + :postgresql backend requires + dropdb. + + + + + + PROBE-DATABASE + tests for existence of a database + Function + + + Syntax + probe-database connection-spec &key database-type => success + + + Arguments and Values + + + connection-spec + + A connection specification + + + + database-type + + A database type specifier, i.e. a keyword. + This defaults to the value of + *default-database-type* + + + + success + + A boolean flag. If &t;, the database exists + in the database system. + + + + + + + Description + This function tests for the existence of a database in + the database system specified by + database-type. + + + + Examples + +(probe-database '("localhost" "new" "dent" "dent") :database-type :postgresql) +=> T + + + + Side Effects + None + + + Exceptional Situations + An exception maybe thrown if the database system does + not receive administrator-level authentication since function + may need to read the administrative database of the database + system. + + + Notes + None. + + + + + + DATABASE-NAME-FROM-SPEC + Return the database name string corresponding to + the given connection specification. + Generic Function + + + Syntax + + database-name-from-spec connection-spec database-type => name + + + Arguments and Values + + + connection-spec + + A connection specification, whose structure and + interpretation are dependent on the + database-type. + + + + database-type + + A database type specifier, i.e. a keyword. + + + + name + + A string denoting a database name. + + + + + + Description + This generic function takes a connection specification + and a database type and returns the database name of the + database object that would be created had + connect been called with the given + connection specification and database types. + This function is useful in determining a database name + from the connection specification, since the way the + connection specification is converted into a database name + is dependent on the database type. + + + Examples + +(database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql) +=> "dent/newesim/dent" +(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) +=> #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}> +(database-name *default-database*) +=> "dent/newesim/dent" + +(database-name-from-spec '(nil "template1" "dent" nil) :postgresql) +=> "/template1/dent" +(connect '(nil "template1" "dent" nil) :database-type :postgresql) +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> +(database-name *default-database*) +=> "/template1/dent" + +(database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql) +=> "www.pmsf.de/template1/dent" + +(find-database "dent/newesim/dent") +=> #<CLSQL-MYSQL:MYSQL-DATABASE {484E91C5}> +(find-database "/template1/dent") +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> +(find-database "www.pmsf.de/template1/dent" nil) +=> NIL +(find-database **) +=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> + + + + Side Effects + None. + + + Affected by + None. + + + Exceptional Situations + If the value of connection-spec + is not a valid connection specification for the given + database type, an error of type + clsql-invalid-spec-error might be + signalled. + + + See Also + + + connect + + + + + Notes + None. + + + + + + EXECUTE-COMMAND + Execute an SQL command which returns no + values. + Function + + + Syntax + execute-command sql-expression &key database => t + + + Arguments and Values + + + sql-expression + + An sql + expression that represents an SQL + statement which will return no values. + + + + database + + A + database + object. This will default to the value + of *default-database*. + + + + + + Description + This will execute the command given by + sql-expression in the + database specified. If the execution + succeeds it will return t, otherwise an + error of type sql-database-error will + be signalled. + + + Examples + +(execute-command "create table eventlog (time char(30),event char(70))") +=> T + +(execute-command "create table eventlog (time char(30),event char(70))") +>> +>> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}> +>> with expression "create table eventlog (time char(30),event char(70))": +>> Error NIL: ERROR: amcreate: eventlog relation already exists +>> has occurred. +>> +>> Restarts: +>> 0: [ABORT] Return to Top-Level. +>> +>> Debug (type H for help) +>> +>> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))| +>> #<unused-arg> +>> #<unused-arg> +>> #<unavailable-arg> +>> #<unavailable-arg>) +>> Source: (ERROR 'SQL-DATABASE-ERROR :DATABASE DATABASE :EXPRESSION ...) +>> 0] 0 + +(execute-command "drop table eventlog") +=> T + + + + Side Effects + Whatever effects the execution of the SQL statement has + on the underlying database, if any. + + + Affected by + None. + + + Exceptional Situations + If the execution of the SQL statement leads to any + errors, an error of type + sql-database-error is signalled. + + + See Also + + + query + + + + + Notes + None. + + + + + QUERY + Execute an SQL query and return the tuples as a + list + Function + + + Syntax + query query-expression &key database result-types field-names => result + + + Arguments and Values + + + query-expression + + An sql + expression that represents an SQL + query which is expected to return a (possibly empty) + result set. + + + + database + + A + database + object. This will default to the value + of *default-database*. + + + + result-types + + A + field type + specifier. The default is &nil;. + + + The purpose of this argument is cause &clsql; to + import SQL numeric fields into numeric Lisp objects + rather than strings. This reduces the cost of + allocating a temporary string and the &clsql; users' + inconvenience of converting number strings into number + objects. + + + A value of :auto causes &clsql; + to automatically convert SQL fields into a + numeric format where applicable. The default value of + &nil; causes all fields to be returned as strings + regardless of the SQL type. Otherwise a list is expected + which has a element for each field that specifies the + conversion. If the list is shorter than the number + of fields, the a value of t is + assumed for the field. If the list is longer than + the number of fields, the extra elements are + ignored. + + :int Field is imported as a + signed integer, from 8-bits to 64-bits depending + upon the field type. + + :double Field is imported as a + double-float number. + + t Field is imported as a + string. + + + + + + + field-names + + A boolean with a default value of &t;. When &t;, this + function results a second value of a list of field + names. When &nil;, this function only returns one value + - the list of rows. + + + + result + + A list representing the result set obtained. For + each tuple in the result set, there is an element in + this list, which is itself a list of all the attribute + values in the tuple. + + + + + + Description + This will execute the query given by + query-expression in the + database specified. If the execution + succeeds it will return the result set returned by the + database, otherwise an error of type + sql-database-error will + be signalled. + + + Examples + +(execute-command "create table simple (name char(50), salary numeric(10,2))") +=> T +(execute-command "insert into simple values ('Mai, Pierre',10000)") +=> T +(execute-command "insert into simple values ('Hacker, Random J.',8000.50)") +=> T +(query "select * from simple") +=> (("Mai, Pierre" "10000.00") ("Hacker, Random J." "8000.50")) +(query "select salary from simple") +=> (("10000.00") ("8000.50")) +(query "select salary from simple where salary > 10000") +=> NIL +(query "select salary,name from simple where salary > 10000") +=> NIL +(query "select salary,name from simple where salary > 9000") +=> (("10000.00" "Mai, Pierre")) +(query "select salary,name from simple where salary > 8000") +=> (("10000.00" "Mai, Pierre") ("8000.50" "Hacker, Random J.")) + +;; MySQL-specific: +(query "show tables") +=> (("demo") ("log") ("newlog") ("simple") ("spacetrial")) + + + + Side Effects + Whatever effects the execution of the SQL query has + on the underlying database, if any. + + + Affected by + None. + + + Exceptional Situations + If the execution of the SQL query leads to any + errors, an error of type + sql-database-error is signalled. + + + See Also + + + execute-command + + + + + Notes + None. + + + + + + MAP-QUERY + Map a function over all the tuples from a + query + Function + + + Syntax + map-query output-type-spec function query-expression &key database result-types => result + + + Arguments and Values + + + output-type-spec + + A sequence type specifier or nil. + + + + function + + A function designator. + function takes a single argument which + is the atom value for a query single with a single column + or is a list of values for a multi-column query. + + + + query-expression + + An sql + expression that represents an SQL + query which is expected to return a (possibly empty) + result set. + + + + database + + A + database + object. This will default to the value + of *default-database*. + + + + result-types + + + A field type specifier. + The default is &nil;. See query + for the semantics of this argument. + + + + + result + + If output-type-spec is a + type specifier other than nil, then a + sequence of the type it denotes. Otherwise + nil is returned. + + + + + + Description + Applies function to the + successive tuples in the result set returned + by executing the SQL + query-expression. If the + output-type-spec is + nil, then the result of each application + of function is discarded, and + map-query returns + nil. Otherwise the result of each + successive application of function is + collected in a sequence of type + output-type-spec, where the jths + element is the result of applying + function to the attributes of the + jths tuple in the result set. The collected sequence is the + result of the call to map-query. + + If the output-type-spec is a + subtype of list, the result will be a + list. + If the result-type is a subtype + of vector, then if the implementation can + determine the element type specified for the + result-type, the element type of the + resulting array is the result of + upgrading that element type; or, if the + implementation can determine that the element type is + unspecified (or *), the element type of the + resulting array is t; otherwise, an error is + signaled. + + + Examples + +(map-query 'list #'(lambda (tuple) + (multiple-value-bind (salary name) tuple + (declare (ignorable name)) + (read-from-string salary))) + "select salary,name from simple where salary > 8000") +=> (10000.0 8000.5) + +(map-query '(vector double-float) + #'(lambda (tuple) + (multiple-value-bind (salary name) tuple + (declare (ignorable name)) + (let ((*read-default-float-format* 'double-float)) + (coerce (read-from-string salary) 'double-float)) + "select salary,name from simple where salary > 8000"))) +=> #(10000.0d0 8000.5d0) +(type-of *) +=> (SIMPLE-ARRAY DOUBLE-FLOAT (2)) + +(let (list) + (values (map-query nil #'(lambda (tuple) + (multiple-value-bind (salary name) tuple + (push (cons name (read-from-string salary)) list)) + "select salary,name from simple where salary > 8000") + list)) +=> NIL +=> (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0)) + + + + Side Effects + Whatever effects the execution of the SQL query has + on the underlying database, if any. + + + Affected by + None. + + + Exceptional Situations + If the execution of the SQL query leads to any + errors, an error of type + sql-database-error is signalled. + An error of type type-error must + be signaled if the output-type-spec is + not a recognizable subtype of list, not a + recognizable subtype of vector, and not + nil. + An error of type type-error + should be signaled if + output-type-spec specifies the number + of elements and the size of the result set is different from + that number. + + + See Also + + + query + do-query + + + + + Notes + None. + + + + + DO-QUERY + Iterate over all the tuples of a + query + Macro + + + Syntax + do-query ((&rest args) query-expression &key database result-types) &body body => nil + + + Arguments and Values + + + args + + A list of variable names. + + + + query-expression + + An sql + expression that represents an SQL + query which is expected to return a (possibly empty) + result set, where each tuple has as many attributes as + function takes arguments. + + + + database + + A + database + object. This will default to + *default-database*. + + + + result-types + + + A field type specifier. + The default is &nil;. See query + for the semantics of this argument. + + + + + body + + A body of Lisp code, like in a + destructuring-bind form. + + + + + + Description + Executes the body of code + repeatedly with the variable names in + args bound to the attributes of each + tuple in the result set returned by executing the SQL + query-expression on the + database specified. + The body of code is executed in a block named + nil which may be returned from prematurely + via return or + return-from. In this case the result + of evaluating the do-query form will be + the one supplied to return or + return-from. Otherwise the result will + be nil. + The body of code appears also is if wrapped in a + destructuring-bind form, thus allowing + declarations at the start of the body, especially those + pertaining to the bindings of the variables named in + args. + + + Examples + +(do-query ((salary name) "select salary,name from simple") + (format t "~30A gets $~2,5$~%" name (read-from-string salary))) +>> Mai, Pierre gets $10000.00 +>> Hacker, Random J. gets $08000.50 +=> NIL + +(do-query ((salary name) "select salary,name from simple") + (return (cons salary name))) +=> ("10000.00" . "Mai, Pierre") + + + + Side Effects + Whatever effects the execution of the SQL query has + on the underlying database, if any. + + + Affected by + None. + + + Exceptional Situations + If the execution of the SQL query leads to any + errors, an error of type + sql-database-error is signalled. + If the number of variable names in + args and the number of attributes in + the tuples in the result set don't match up, an error is + signalled. + + + See Also + + + query + map-query + + + + + Notes + None. + + + + + LOOP-FOR-AS-TUPLES + Iterate over all the tuples of a + query via a loop clause + Loop Clause + + + Compatibility + loop-for-as-tuples only works with &cmucl;. + + + Syntax + var [type-spec] being {each | the} {record | records | tuple | tuples} {in | of} query [from database] + + + Arguments and Values + + + var + + A d-var-spec, as defined in the + grammar for loop-clauses in the + ANSI Standard for Common Lisp. This allows for the + usual loop-style destructuring. + + + + type-spec + + An optional type-spec either + simple or destructured, as defined in the grammar for + loop-clauses in the ANSI Standard + for Common Lisp. + + + + query + + An sql + expression that represents an SQL + query which is expected to return a (possibly empty) + result set, where each tuple has as many attributes as + function takes arguments. + + + + database + + An optional + database + object. This will default to the value + of *default-database*. + + + + + + Description + This clause is an iteration driver for + loop, that binds the given variable + (possibly destructured) to the consecutive tuples (which are + represented as lists of attribute values) in the result set + returned by executing the SQL query + expression on the database + specified. + + + Examples + +(defvar *my-db* (connect '("dent" "newesim" "dent" "dent")) + "My database" +=> *MY-DB* +(loop with time-graph = (make-hash-table :test #'equal) + with event-graph = (make-hash-table :test #'equal) + for (time event) being the tuples of "select time,event from log" + from *my-db* + do + (incf (gethash time time-graph 0)) + (incf (gethash event event-graph 0)) + finally + (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v))) + (format t "~&Time-Graph:~%===========~%") + (maphash #'show-graph time-graph) + (format t "~&~%Event-Graph:~%============~%") + (maphash #'show-graph event-graph)) + (return (values time-graph event-graph))) +>> Time-Graph: +>> =========== +>> D => 53000 +>> X => 3 +>> test-me => 3000 +>> +>> Event-Graph: +>> ============ +>> CLOS Benchmark entry. => 9000 +>> Demo Text... => 3 +>> doit-text => 3000 +>> C Benchmark entry. => 12000 +>> CLOS Benchmark entry => 32000 +=> #<EQUAL hash table, 3 entries {48350A1D}> +=> #<EQUAL hash table, 5 entries {48350FCD}> + + + + Side Effects + Whatever effects the execution of the SQL query has + on the underlying database, if any. + + + Affected by + None. + + + Exceptional Situations + If the execution of the SQL query leads to any + errors, an error of type + sql-database-error is signalled. + Otherwise, any of the exceptional situations of + loop applies. + + + See Also + + + query + map-query + do-query + + + + + Notes + None. + + + diff --git a/doc/ref_clsql.xml b/doc/ref_clsql.xml deleted file mode 100644 index c24b2ce..0000000 --- a/doc/ref_clsql.xml +++ /dev/null @@ -1,2688 +0,0 @@ - - -%myents; -]> - - - <symbol>CLSQL</symbol> - - This part gives a reference to the symbols exported from the - package CLSQL-SYS, which are also re-exported - from the package CLSQL. These symbols constitute - the normal user-interface of &clsql;. Currently, the symbols of - the &commonsql;-API are not documented here. - - - - - CLSQL-CONDITION - the super-type of all - &clsql;-specific - conditions - Condition Type - - - Class Precedence List - - - clsql-condition - condition - t - - - - - Description - This is the super-type of all - &clsql;-specific conditions - defined by &clsql;, or any of it's - database-specific interfaces. There are no defined - initialization arguments nor any accessors. - - - - - CLSQL-ERROR - the super-type of all - &clsql;-specific - errors - Condition Type - - - Class Precedence List - - - clsql-error - error - serious-condition - clsql-condition - condition - t - - - - - Description - This is the super-type of all - &clsql;-specific conditions that - represent errors, as defined by - &clsql;, or any of it's - database-specific interfaces. There are no defined - initialization arguments nor any accessors. - - - - - CLSQL-SIMPLE-ERROR - Unspecific simple - &clsql; errors - Condition Type - - - Class Precedence List - - - clsql-simple-error - simple-condition - clsql-error - error - serious-condition - clsql-condition - condition - t - - - - - Description - This condition is used in all instances of errors, where - there exists no &clsql;-specific - condition that is more specific. The valid initialization - arguments and accessors are the same as for - simple-condition. - - - - - CLSQL-WARNING - the super-type of all - &clsql;-specific - warnings - Condition Type - - - Class Precedence List - - - clsql-warning - warning - clsql-condition - condition - t - - - - - Description - This is the super-type of all - &clsql;-specific conditions that - represent warnings, as defined by - &clsql;, or any of it's - database-specific interfaces. There are no defined - initialization arguments nor any accessors. - - - - - CLSQL-SIMPLE-WARNING - Unspecific simple - &clsql; warnings - Condition Type - - - Class Precedence List - - - clsql-simple-warning - simple-condition - clsql-warning - warning - clsql-condition - condition - t - - - - - Description - This condition is used in all instances of warnings, - where there exists no - &clsql;-specific condition that is - more specific. The valid initialization arguments and - accessors are the same as for - simple-condition. - - - - - - CLSQL-INVALID-SPEC-ERROR - condition representing errors because of invalid - connection specifications - Condition Type - - - Class Precedence List - - - clsql-invalid-spec-error - clsql-error - error - serious-condition - clsql-condition - condition - t - - - - - Description - This condition represents errors that occur because the - user supplies an invalid connection specification to either - database-name-from-spec or - connect. The following initialization - arguments and accessors exist: - - Initarg - Accessor - Description - - :connection-spec - clsql-invalid-spec-error-connection-spec - The invalid connection specification used. - - - :database-type - clsql-invalid-spec-error-database-type - The Database type used in the attempt. - - - :template - clsql-invalid-spec-error-template - An argument describing the template that a valid - connection specification must match for this database type. - - - - - - - CLSQL-CONNECT-ERROR - condition representing errors during - connection - Condition Type - - - Class Precedence List - - - clsql-connect-error - clsql-error - error - serious-condition - clsql-condition - condition - t - - - - - Description - This condition represents errors that occur while trying - to connect to a database. The following initialization - arguments and accessors exist: - - Initarg - Accessor - Description - - :database-type - clsql-connect-error-database-type - Database type for the connection attempt - - - :connection-spec - clsql-connect-error-connection-spec - The connection specification used in the - connection attempt. - - - :errno - clsql-connect-error-errno - The numeric or symbolic error specification - returned by the database back-end. The values and - semantics of this are interface specific. - - - :error - clsql-connect-error-error - A string describing the problem that occurred, - possibly one returned by the database back-end. - - - - - - - CLSQL-SQL-ERROR - condition representing errors during query or - command execution - Condition Type - - - Class Precedence List - - - clsql-sql-error - clsql-error - error - serious-condition - clsql-condition - condition - t - - - - - Description - This condition represents errors that occur while - executing SQL statements, either as part of query operations - or command execution, either explicitly or implicitly, as - caused e.g. by with-transaction. - The following initialization arguments and accessors exist: - - Initarg - Accessor - Description - - :database - clsql-sql-error-database - The database object that was involved in the - incident. - - - :expression - clsql-sql-error-expression - The SQL expression whose execution caused the error. - - - :errno - clsql-sql-error-errno - The numeric or symbolic error specification - returned by the database back-end. The values and - semantics of this are interface specific. - - - :error - clsql-sql-error-error - A string describing the problem that occurred, - possibly one returned by the database back-end. - - - - - - - CLSQL-EXISTS-CONDITION - condition indicating situations arising because of - existing connections - Condition Type - - - Class Precedence List - - - clsql-exists-condition - clsql-condition - condition - t - - - - - Description - This condition is the super-type of all conditions which - represents problems that occur during calls to - connect, if a connection to the - database exists already. Depending on the value of - if-exists to the call of - connect, either a warning, an error or - no condition at all is signalled. If a warning or error is - signalled, either - clsql-exists-warning or - clsql-exists-error is signalled, - which are subtypes of - clsql-exists-condition and - clsql-warning or - clsql-error. - clsql-exists-condition is never - signalled itself. - - The following initialization arguments and accessors exist: - - Initarg - Accessor - Description - - :old-db - clsql-exists-condition-old-db - The database object that represents the existing - connection. This slot is always filled. - - - :new-db - clsql-exists-condition-new-db - The database object that will be used and returned by - this call to connect, if execution continues normally. - This can be either nil, indicating that - a new database object is to be created on continuation, - or a database object representing the newly created - continuation, or the same database object as - old-db, indicating that the existing - database object will be reused. This slot is always - filled and defaults to nil. - - - - - - - CLSQL-EXISTS-WARNING - condition representing warnings arising because of - existing connections - Condition Type - - - Class Precedence List - - - clsql-exists-warning - clsql-exists-condition - clsql-warning - warning - clsql-condition - condition - t - - - - - Description - This condition is a subtype of - clsql-exists-condition, and is - signalled during calls to connect when - there is an existing connection, and - if-exists is either - :warn-new or :warn-old. - In the former case, new-db will be the - newly created database object, in the latter case it will be - the existing old database object. - - The initialization arguments and accessors are the same as - for clsql-exists-condition. - - - - - CLSQL-EXISTS-ERROR - condition representing errors arising because of - existing connections - Condition Type - - - Class Precedence List - - - clsql-exists-error - clsql-exists-condition - clsql-error - error - serious-condition - clsql-condition - condition - t - - - - - Description - This condition is a subtype of - clsql-exists-condition, and is - signalled during calls to connect when - there is an existing connection, and - if-exists is :error. - In this case, new-db will be - nil, indicating that the database object to - be returned by connect depends on user - action in continuing from this correctable error. - - The initialization arguments and accessors are the same as - for clsql-exists-condition. - - - - - CLSQL-CLOSED-ERROR - condition representing errors because the database - has already been closed - Condition Type - - - Class Precedence List - - - clsql-closed-error - clsql-error - error - serious-condition - clsql-condition - condition - t - - - - - Description - This condition represents errors that occur because the - user invokes an operation on the given database object, - although the database is invalid because - disconnect has already been called on - this database object. - Functions which signal this error when called with a - closed database will usually provide a - continue restart, that will just return nil - from the function. - - The following initialization arguments and accessors exist: - - Initarg - Accessor - Description - - :database - clsql-closed-error-database - The database object that was involved in the - incident. - - - - - - - - - *DEFAULT-DATABASE-TYPE* - The default database type to use - Variable - - - Value Type - Any keyword representing a valid database back-end of - &clsql;, or - nil. - - - Initial Value - nil - - - Description - The value of this variable is used in calls to - initialize-database-type and - connect as the default - value of the database-type - parameter. - - If the value of this variable is nil, - then all calls to - initialize-database-type or - connect will have to specify the - database-type to use, or a - general-purpose error will be signalled. - - - - Examples - -(setf *default-database-type* :mysql) -=> :mysql -(initialize-database-type) -=> t - - - - Affected By - None. - - - See Also - None. - - - Notes - None. - - - - - *INITIALIZED-DATABASE-TYPES* - List of all initialized database types - Variable - - - Value Type - A list of all initialized database types, each of which - represented by it's corresponding keyword. - - - Initial Value - nil - - - Description - This variable is updated whenever - initialize-database-type is called for a - database type which hasn't already been initialized before, - as determined by this variable. In that case the keyword - representing the database type is pushed onto the list - stored in - *INITIALIZED-DATABASE-TYPES*. - - Attempts to modify the value of this variable will - result in undefined behaviour. - - - - Examples - -(setf *default-database-type* :mysql) -=> :mysql -(initialize-database-type) -=> t -*initialized-database-types* -=> (:MYSQL) - - - - Affected By - - - initialize-database-type - - - - - See Also - None. - - - Notes - Direct access to this variable is primarily provided - because of compatibility with Harlequin's Common - SQL. - - - - - INITIALIZE-DATABASE-TYPE - Initializes a database type - Function - - - Syntax - initialize-database-type &key database-type => result - - - Arguments and Values - - - database-type - - The database type to initialize, i.e. a keyword - symbol denoting a known database back-end. Defaults to - the value of - *default-database-type*. - - - - result - - Either nil if the initialization - attempt fails, or t otherwise. - - - - - - Description - If the back-end specified by - database-type has not already been - initialized, as seen from - *initialized-database-types*, an attempt is - made to initialize the database. If this attempt succeeds, - or the back-end has already been initialized, the function - returns t, and places the keyword denoting the database type - onto the list stored in - *initialized-database-types*, if not - already present. - If initialization fails, the function returns - nil, and/or signals an error of type - clsql-error. The kind of action - taken depends on the back-end and the cause of the - problem. - - - Examples - -*initialized-database-types* -=> NIL -(setf *default-database-type* :mysql) -=> :MYSQL -(initialize-database-type) ->> Compiling LAMBDA (#:G897 #:G898 #:G901 #:G902): ->> Compiling Top-Level Form: ->> -=> T -*initialized-database-types* -=> (:MYSQL) -(initialize-database-type) -=> T -*initialized-database-types* -=> (:MYSQL) - - - - Side Effects - The database back-end corresponding to the database type - specified is initialized, unless it has already been - initialized. This can involve any number of other side - effects, as determined by the back-end implementation (like - e.g. loading of foreign code, calling of foreign code, - networking operations, etc.). If initialization is - attempted and succeeds, the - database-type is pushed onto the list - stored in - *initialized-database-types*. - - - Affected by - - - *default-database-type* - *initialized-database-types* - - - - - Exceptional Situations - If an error is encountered during the initialization - attempt, the back-end may signal errors of kind - clsql-error. - - - See Also - None. - - - Notes - None. - - - - - - *CONNECT-IF-EXISTS* - Default value for the - if-exists parameter of - connect. - Variable - - - Value Type - A valid argument to the if-exists - parameter of connect, i.e. one of - - :new - :warn-new - :error - :warn-old - :old - . - - - - Initial Value - :error - - - Description - The value of this variable is used in calls to - connect as the default - value of the if-exists - parameter. See connect for - the semantics of the valid values for this variable. - - - Examples - None. - - - Affected By - None. - - - See Also - - - connect - - - - - Notes - None. - - - - - CONNECTED-DATABASES - Return the list of active database - objects. - Function - - - Syntax - connected-databases => databases - - - Arguments and Values - - - databases - - The list of active database objects. - - - - - - Description - This function returns the list of active database - objects, i.e. all those database objects created by calls to - connect, which have not been closed by - calling disconnect on them. - - The consequences of modifying the list returned by - connected-databases are - undefined. - - - - Examples - -(connected-databases) -=> NIL -(connect '(nil "template1" "dent" nil) :database-type :postgresql) -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}> -(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) -=> #<CLSQL-MYSQL:MYSQL-DATABASE {4830C5AD}> -(connected-databases) -=> (#<CLSQL-MYSQL:MYSQL-DATABASE {4830C5AD}> - #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>) -(disconnect) -=> T -(connected-databases) -=> (#<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {4830BC65}>) -(disconnect) -=> T -(connected-databases) -=> NIL - - - - Side Effects - None. - - - Affected By - - - connect - disconnect - - - - - Exceptional Situations - None. - - - See Also - None. - - - Notes - None. - - - - - *DEFAULT-DATABASE* - The default database object to use - Variable - - - Value Type - Any object of type database, or nil to - indicate no default database. - - - Initial Value - nil - - - Description - Any function or macro in - &clsql; that operates on a - database uses the value of this variable as the default - value for it's database - parameter. - The value of this parameter is changed by calls to - connect, which sets - *default-database* to the database object - it returns. It is also changed by calls to - disconnect, when the database object - being disconnected is the same as the value of - *default-database*. In this case - disconnect sets - *default-database* to the first database - that remains in the list of active databases as returned by - connected-databases, or - nil if no further active databases - exist. - The user may change *default-database* - at any time to a valid value of his choice. - - If the value of *default-database* is - nil, then all calls to - &clsql; functions on databases - must provide a suitable database - parameter, or an error will be signalled. - - - - Examples - -(connected-databases) -=> NIL -(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) -=> #<CLSQL-MYSQL:MYSQL-DATABASE {48385F55}> -(connect '(nil "template1" "dent" nil) :database-type :postgresql) -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {483868FD}> -(connect '("dent" "newesim" "dent" "dent") :database-type :mysql :if-exists :new) -=> #<CLSQL-MYSQL:MYSQL-DATABASE {48387265}> -*default-database* -=> #<CLSQL-MYSQL:MYSQL-DATABASE {48387265}> -(disconnect) -=> T -*default-database* -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {483868FD}> -(disconnect) -=> T -*default-database* -=> #<CLSQL-MYSQL:MYSQL-DATABASE {48385F55}> -(disconnect) -=> T -*default-database* -=> NIL -(connected-databases) -=> NIL - - - - Affected By - - - connect - disconnect - - - - - See Also - - - connected-databases - - - - - Notes - - This variable is intended to facilitate working with - &clsql; in an interactive - fashion at the top-level loop, and because of this, - connect and - disconnect provide some fairly - complex behaviour to keep - *default-database* set to useful values. - Programmatic use of &clsql; - should never depend on the value of - *default-database* and should provide - correct database objects via the - database parameter to functions - called. - - - - - - - DATABASE - The super-type of all - &clsql; databases - Class - - - Class Precedence List - - - database - standard-object - t - - - - - Description - This class is the superclass of all - &clsql; databases. The different - database back-ends derive subclasses of this class to - implement their databases. No instances of this class are - ever created by &clsql;. - - - - - CLOSED-DATABASE - The class representing all closed - &clsql; databases - Class - - - Class Precedence List - - - closed-database - standard-object - t - - - - - Description - &clsql; database - instances are changed to this class via - change-class after they are closed via - disconnect. All functions and generic - functions that take database objects as arguments will - signal errors of type - clsql-closed-error when they are - called on instances of closed-database, with - the exception of database-name, which - will continue to work as for instances of - database. - - - - - - DATABASE-NAME - Get the name of a database object - Generic Function - - - Syntax - database-name database => name - - - Arguments and Values - - - database - - A database object, either of type - database or of type - closed-database. - - - - name - - A string describing the identity of the database - to which this database object is connected to. - - - - - - Description - This function returns the database name of the given - database. The database name is a string which somehow - describes the identity of the database to which this - database object is or has been connected. The database name - of a database object is determined at - connect time, when a call to - database-name-from-spec derives the - database name from the connection specification passed to - connect in the - connection-spec parameter. - The database name is used via - find-database in - connect to determine whether database - connections to the specified database exist already. - Usually the database name string will include - indications of the host, database name, user, or port that - where used during the connection attempt. The only - important thing is that this string shall try to identify - the database at the other end of the connection. Connection - specifications parts like passwords and credentials shall - not be used as part of the database name. - - - Examples - -(database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql) -=> "dent/newesim/dent" -(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) -=> #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}> -(database-name *default-database*) -=> "dent/newesim/dent" - -(database-name-from-spec '(nil "template1" "dent" nil) :postgresql) -=> "/template1/dent" -(connect '(nil "template1" "dent" nil) :database-type :postgresql) -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> -(database-name *default-database*) -=> "/template1/dent" - -(database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql) -=> "www.pmsf.de/template1/dent" - - - - Side Effects - None. - - - Affected By - - - database-name-from-spec - - - - - Exceptional Situations - Will signal an error if the object passed as the - database parameter is neither of type - database nor of type - closed-database. - - - See Also - - - connect - find-database - - - - - Notes - None. - - - - - FIND-DATABASE - Locate a database object through it's - name. - Function - - - Syntax - find-database database &optional errorp => result - - - Arguments and Values - - - database - - A database object or a string, denoting a database - name. - - - - errorp - - A generalized boolean. Defaults to - t. - - - - result - - Either a database object, or, if - errorp is nil, - possibly nil. - - - - - - Description - find-database locates an active - database object given the specification in - database. If - database is an object of type - database, find-database - returns this. Otherwise it will search the active databases - as indicated by the list returned by - connected-databases for a database - whose name (as returned by - database-name is equal as per - string= to the string passed as - database. If it succeeds, it returns - the first database found. - If it fails to find a matching database, it will signal - an error of type clsql-error if - errorp is true. If - errorp is nil, it - will return nil instead. - - - Examples - -(database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql) -=> "dent/newesim/dent" -(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) -=> #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}> -(database-name *default-database*) -=> "dent/newesim/dent" - -(database-name-from-spec '(nil "template1" "dent" nil) :postgresql) -=> "/template1/dent" -(connect '(nil "template1" "dent" nil) :database-type :postgresql) -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> -(database-name *default-database*) -=> "/template1/dent" - -(database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql) -=> "www.pmsf.de/template1/dent" - -(find-database "dent/newesim/dent") -=> #<CLSQL-MYSQL:MYSQL-DATABASE {484E91C5}> -(find-database "/template1/dent") -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> -(find-database "www.pmsf.de/template1/dent" nil) -=> NIL -(find-database **) -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> - - - - Side Effects - None. - - - Affected By - - - connected-databases - - - - - Exceptional Situations - Will signal an error of type - clsql-error if no matching database - can be found, and errorp is true. - Will signal an error if the value of - database is neither an object of type - database nor a string. - - - See Also - - - database-name - database-name-from-spec - - - - - Notes - None. - - - - - - CONNECT - create a connection to a database - Function - - - Syntax - connect connection-spec &key if-exists database-type pool => database - - - Arguments and Values - - - connection-spec - - A connection specification - - - - if-exists - - This indicates the action to take if a connection - to the same database exists already. See below for the - legal values and actions. It defaults to the value of - *connect-if-exists*. - - - - database-type - - A database type specifier, i.e. a keyword. - This defaults to the value of - *default-database-type* - - - - pool - - A boolean flag. If &t;, acquire connection from a - pool of open connections. If the pool is empty, a new - connection is created. The default is &nil;. - - - - - database - - The database object representing the connection. - - - - - - Description - This function takes a connection specification and - a database type and creates a connection to the database - specified by those. The type and structure of the - connection specification depend on the database type. - The parameter if-exists specifies - what to do if a connection to the database specified exists - already, which is checked by calling - find-database on the database name - returned by database-name-from-spec - when called with the connection-spec - and database-type parameters. The - possible values of if-exists are: - - - :new - - Go ahead and create a new connection. - - - - :warn-new - - This is just like :new, but - also signals a warning of type - clsql-exists-warning, - indicating the old and newly created - databases. - - - - :error - - This will cause connect to - signal a correctable error of type - clsql-exists-error. The - user may choose to proceed, either by indicating - that a new connection shall be created, via the - restart create-new, or by - indicating that the existing connection shall be - used, via the restart - use-old. - - - - :old - - This will cause connect to - use an old connection if one exists. - - - - :warn-old - - This is just like :old, but - also signals a warning of type - clsql-exists-warning, - indicating the old database used, via the slots - old-db and - new-db - - - - - The database name of the returned database object will - be the same under string= as that which - would be returned by a call to - database-name-from-spec with the given - connection-spec and - database-type parameters. - - - Examples - -(database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql) -=> "dent/newesim/dent" -(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) -=> #<CLSQL-MYSQL:MYSQL-DATABASE {48036F6D}> -(database-name *) -=> "dent/newesim/dent" - -(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) ->> In call to CONNECT: ->> There is an existing connection #<CLSQL-MYSQL:MYSQL-DATABASE {48036F6D}> to database dent/newesim/dent. ->> ->> Restarts: ->> 0: [CREATE-NEW] Create a new connection. ->> 1: [USE-OLD ] Use the existing connection. ->> 2: [ABORT ] Return to Top-Level. ->> ->> Debug (type H for help) ->> ->> (CONNECT ("dent" "newesim" "dent" "dent") :IF-EXISTS NIL :DATABASE-TYPE ...) ->> Source: ->> ; File: /prj/CLSQL/sql/sql.cl ->> (RESTART-CASE (ERROR 'CLSQL-EXISTS-ERROR :OLD-DB OLD-DB) ->> (CREATE-NEW NIL :REPORT "Create a new connection." ->> (SETQ RESULT #)) ->> (USE-OLD NIL :REPORT "Use the existing connection." ->> (SETQ RESULT OLD-DB))) ->> 0] 0 -=> #<CLSQL-MYSQL:MYSQL-DATABASE {480451F5}> - - - - Side Effects - A database connection is established, and the resultant - database object is registered, so as to appear in the list - returned by connected-databases. - - - Affected by - - - *default-database-type* - *connect-if-exists* - - - - - Exceptional Situations - If the connection specification is not syntactically or - semantically correct for the given database type, an error - of type clsql-invalid-spec-error is - signalled. If during the connection attempt an error is - detected (e.g. because of permission problems, network - trouble or any other cause), an error of type - clsql-connect-error is - signalled. - If a connection to the database specified by - connection-spec exists already, - conditions are signalled according to the - if-exists parameter, as described - above. - - - See Also - - - connected-databases - disconnect - - - - - Notes - None. - - - - - - DISCONNECT - close a database connection - Function - - - Syntax - disconnect &key database pool => t - - - Arguments and Values - - - pool - - A boolean flag indicating whether to put the database into a -pool of opened databases. If &t;, rather than terminating the database connection, the -connection is left open and the connection is placed into a pool of connections. Subsequent -calls to connect can then reuse this connection. -The default is &nil;. - - - - - database - - The database to disconnect, which defaults to the - database indicated by - *default-database*. - - - - - - Description - This function takes a database object as - returned by connect, and closes the - connection. The class of the object passed is changed to - closed-database after the disconnection - succeeds, thereby preventing further use of the object as - an argument to &clsql; functions, - with the exception of database-name. - If the user does pass a closed database object to any other - &clsql; function, an error of type - clsql-closed-error is - signalled. - - - Examples - -(disconnect :database (find-database "dent/newesim/dent")) -=> T - - - - Side Effects - The database connection is closed, and the database - object is removed from the list of connected databases as - returned by connected-databases. - The class of the database object is changed to - closed-database. - If the database object passed is the same under - eq as the value of - *default-database*, then - *default-database* is set to the first - remaining database from - connected-databases or to nil if no - further active database exists. - - - Affected by - - - *default-database* - - - - - Exceptional Situations - If during the disconnection attempt an error is - detected (e.g. because of network trouble or any other - cause), an error of type clsql-error - might be signalled. - - - See Also - - - connect - closed-database - - - - - Notes - None. - - - - - - DISCONNECT-POOLED - closes all pooled database connections - Function - - - Syntax - disconnect-pool => t - - - Description - This function disconnects all database connections - that have been placed into the pool. Connections are placed - in the pool by calling disconnection. - - - - Examples - -(disconnect-pool) -=> T - - - - Side Effects - Database connections will be closed and entries in the pool are removed. - - - - Affected by - - - disconnect - - - - - Exceptional Situations - If during the disconnection attempt an error is - detected (e.g. because of network trouble or any other - cause), an error of type clsql-error - might be signalled. - - - See Also - - - connect - closed-database - - - - - Notes - None. - - - - - - CREATE-DATABASE - create a database - Function - - - Syntax - create-database connection-spec &key database-type => success - - - Arguments and Values - - - connection-spec - - A connection specification - - - - database-type - - A database type specifier, i.e. a keyword. - This defaults to the value of - *default-database-type* - - - - success - - A boolean flag. If &t;, a new database was - successfully created. - - - - - - - Description - This function creates a database in the database system - specified by database-type. - - - - Examples - -(create-database '("localhost" "new" "dent" "dent") :database-type :mysql) -=> T - -(create-database '("localhost" "new" "dent" "badpasswd") :database-type :mysql) -=> -Error: While trying to access database localhost/new/dent - using database-type MYSQL: - Error database-create failed: mysqladmin: connect to server at 'localhost' failed -error: 'Access denied for user: 'root@localhost' (Using password: YES)' - has occurred. - [condition type: CLSQL-ACCESS-ERROR] - - - - Side Effects - A database will be created on the filesystem of the host. - - - Exceptional Situations - An exception will be thrown if the database system does - not allow new databases to be created or if database creation - fails. - - - Notes - This function may invoke the operating systems - functions. Thus, some database systems may require the - administration functions to be available in the current - PATH. At this time, the - :mysql backend requires - mysqladmin and the - :postgresql backend requires - createdb. - - - - - - DESTROY-DATABASE - destroys a database - Function - - - Syntax - destroy-database connection-spec &key database-type => success - - - Arguments and Values - - - connection-spec - - A connection specification - - - - database-type - - A database type specifier, i.e. a keyword. - This defaults to the value of - *default-database-type* - - - - success - - A boolean flag. If &t;, the database was - successfully destroyed. - - - - - - - Description - This function destroy a database in the database system - specified by database-type. - - - - Examples - -(destroy-database '("localhost" "new" "dent" "dent") :database-type :postgresql) -=> T - -(destroy-database '("localhost" "new" "dent" "dent") :database-type :postgresql) -=> -Error: While trying to access database localhost/test2/root - using database-type POSTGRESQL: - Error database-destory failed: dropdb: database removal failed: ERROR: database "test2" does not exist - has occurred. - [condition type: CLSQL-ACCESS-ERROR] - - - - Side Effects - A database will be removed from the filesystem of the host. - - - Exceptional Situations - An exception will be thrown if the database system does not - allow databases to be removed, the database does not exist, or - if database removal fails. - - - Notes - This function may invoke the operating systems - functions. Thus, some database systems may require the - administration functions to be available in the current - PATH. At this time, the - :mysql backend requires - mysqladmin and the - :postgresql backend requires - dropdb. - - - - - - PROBE-DATABASE - tests for existence of a database - Function - - - Syntax - probe-database connection-spec &key database-type => success - - - Arguments and Values - - - connection-spec - - A connection specification - - - - database-type - - A database type specifier, i.e. a keyword. - This defaults to the value of - *default-database-type* - - - - success - - A boolean flag. If &t;, the database exists - in the database system. - - - - - - - Description - This function tests for the existence of a database in - the database system specified by - database-type. - - - - Examples - -(probe-database '("localhost" "new" "dent" "dent") :database-type :postgresql) -=> T - - - - Side Effects - None - - - Exceptional Situations - An exception maybe thrown if the database system does - not receive administrator-level authentication since function - may need to read the administrative database of the database - system. - - - Notes - None. - - - - - - DATABASE-NAME-FROM-SPEC - Return the database name string corresponding to - the given connection specification. - Generic Function - - - Syntax - - database-name-from-spec connection-spec database-type => name - - - Arguments and Values - - - connection-spec - - A connection specification, whose structure and - interpretation are dependent on the - database-type. - - - - database-type - - A database type specifier, i.e. a keyword. - - - - name - - A string denoting a database name. - - - - - - Description - This generic function takes a connection specification - and a database type and returns the database name of the - database object that would be created had - connect been called with the given - connection specification and database types. - This function is useful in determining a database name - from the connection specification, since the way the - connection specification is converted into a database name - is dependent on the database type. - - - Examples - -(database-name-from-spec '("dent" "newesim" "dent" "dent") :mysql) -=> "dent/newesim/dent" -(connect '("dent" "newesim" "dent" "dent") :database-type :mysql) -=> #<CLSQL-MYSQL:MYSQL-DATABASE {48391DCD}> -(database-name *default-database*) -=> "dent/newesim/dent" - -(database-name-from-spec '(nil "template1" "dent" nil) :postgresql) -=> "/template1/dent" -(connect '(nil "template1" "dent" nil) :database-type :postgresql) -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> -(database-name *default-database*) -=> "/template1/dent" - -(database-name-from-spec '("www.pmsf.de" "template1" "dent" nil) :postgresql) -=> "www.pmsf.de/template1/dent" - -(find-database "dent/newesim/dent") -=> #<CLSQL-MYSQL:MYSQL-DATABASE {484E91C5}> -(find-database "/template1/dent") -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> -(find-database "www.pmsf.de/template1/dent" nil) -=> NIL -(find-database **) -=> #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {48392D2D}> - - - - Side Effects - None. - - - Affected by - None. - - - Exceptional Situations - If the value of connection-spec - is not a valid connection specification for the given - database type, an error of type - clsql-invalid-spec-error might be - signalled. - - - See Also - - - connect - - - - - Notes - None. - - - - - - EXECUTE-COMMAND - Execute an SQL command which returns no - values. - Function - - - Syntax - execute-command sql-expression &key database => t - - - Arguments and Values - - - sql-expression - - An sql - expression that represents an SQL - statement which will return no values. - - - - database - - A - database - object. This will default to the value - of *default-database*. - - - - - - Description - This will execute the command given by - sql-expression in the - database specified. If the execution - succeeds it will return t, otherwise an - error of type clsql-sql-error will - be signalled. - - - Examples - -(execute-command "create table eventlog (time char(30),event char(70))") -=> T - -(execute-command "create table eventlog (time char(30),event char(70))") ->> ->> While accessing database #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE {480B2B6D}> ->> with expression "create table eventlog (time char(30),event char(70))": ->> Error NIL: ERROR: amcreate: eventlog relation already exists ->> has occurred. ->> ->> Restarts: ->> 0: [ABORT] Return to Top-Level. ->> ->> Debug (type H for help) ->> ->> (CLSQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-EXECUTE-COMMAND (T POSTGRESQL-DATABASE))| ->> #<unused-arg> ->> #<unused-arg> ->> #<unavailable-arg> ->> #<unavailable-arg>) ->> Source: (ERROR 'CLSQL-SQL-ERROR :DATABASE DATABASE :EXPRESSION ...) ->> 0] 0 - -(execute-command "drop table eventlog") -=> T - - - - Side Effects - Whatever effects the execution of the SQL statement has - on the underlying database, if any. - - - Affected by - None. - - - Exceptional Situations - If the execution of the SQL statement leads to any - errors, an error of type - clsql-sql-error is signalled. - - - See Also - - - query - - - - - Notes - None. - - - - - QUERY - Execute an SQL query and return the tuples as a - list - Function - - - Syntax - query query-expression &key database result-types field-names => result - - - Arguments and Values - - - query-expression - - An sql - expression that represents an SQL - query which is expected to return a (possibly empty) - result set. - - - - database - - A - database - object. This will default to the value - of *default-database*. - - - - result-types - - A - field type - specifier. The default is &nil;. - - - The purpose of this argument is cause &clsql; to - import SQL numeric fields into numeric Lisp objects - rather than strings. This reduces the cost of - allocating a temporary string and the &clsql; users' - inconvenience of converting number strings into number - objects. - - - A value of :auto causes &clsql; - to automatically convert SQL fields into a - numeric format where applicable. The default value of - &nil; causes all fields to be returned as strings - regardless of the SQL type. Otherwise a list is expected - which has a element for each field that specifies the - conversion. If the list is shorter than the number - of fields, the a value of t is - assumed for the field. If the list is longer than - the number of fields, the extra elements are - ignored. - - :int Field is imported as a - signed integer, from 8-bits to 64-bits depending - upon the field type. - - :double Field is imported as a - double-float number. - - t Field is imported as a - string. - - - - - - - field-names - - A boolean with a default value of &t;. When &t;, this - function results a second value of a list of field - names. When &nil;, this function only returns one value - - the list of rows. - - - - result - - A list representing the result set obtained. For - each tuple in the result set, there is an element in - this list, which is itself a list of all the attribute - values in the tuple. - - - - - - Description - This will execute the query given by - query-expression in the - database specified. If the execution - succeeds it will return the result set returned by the - database, otherwise an error of type - clsql-sql-error will - be signalled. - - - Examples - -(execute-command "create table simple (name char(50), salary numeric(10,2))") -=> T -(execute-command "insert into simple values ('Mai, Pierre',10000)") -=> T -(execute-command "insert into simple values ('Hacker, Random J.',8000.50)") -=> T -(query "select * from simple") -=> (("Mai, Pierre" "10000.00") ("Hacker, Random J." "8000.50")) -(query "select salary from simple") -=> (("10000.00") ("8000.50")) -(query "select salary from simple where salary > 10000") -=> NIL -(query "select salary,name from simple where salary > 10000") -=> NIL -(query "select salary,name from simple where salary > 9000") -=> (("10000.00" "Mai, Pierre")) -(query "select salary,name from simple where salary > 8000") -=> (("10000.00" "Mai, Pierre") ("8000.50" "Hacker, Random J.")) - -;; MySQL-specific: -(query "show tables") -=> (("demo") ("log") ("newlog") ("simple") ("spacetrial")) - - - - Side Effects - Whatever effects the execution of the SQL query has - on the underlying database, if any. - - - Affected by - None. - - - Exceptional Situations - If the execution of the SQL query leads to any - errors, an error of type - clsql-sql-error is signalled. - - - See Also - - - execute-command - - - - - Notes - None. - - - - - - MAP-QUERY - Map a function over all the tuples from a - query - Function - - - Syntax - map-query output-type-spec function query-expression &key database result-types => result - - - Arguments and Values - - - output-type-spec - - A sequence type specifier or nil. - - - - function - - A function designator. - function must take as many - arguments as are attributes in the result set returned - by executing the SQL - query-expression. - - - - query-expression - - An sql - expression that represents an SQL - query which is expected to return a (possibly empty) - result set, where each tuple has as many attributes as - function takes arguments. - - - - database - - A - database - object. This will default to the value - of *default-database*. - - - - result-types - - - A field type specifier. - The default is &nil;. See query - for the semantics of this argument. - - - - - result - - If output-type-spec is a - type specifier other than nil, then a - sequence of the type it denotes. Otherwise - nil is returned. - - - - - - Description - Applies function to the - attributes of successive tuples in the result set returned - by executing the SQL - query-expression. If the - output-type-spec is - nil, then the result of each application - of function is discarded, and - map-query returns - nil. Otherwise the result of each - successive application of function is - collected in a sequence of type - output-type-spec, where the jths - element is the result of applying - function to the attributes of the - jths tuple in the result set. The collected sequence is the - result of the call to map-query. - - If the output-type-spec is a - subtype of list, the result will be a - list. - If the result-type is a subtype - of vector, then if the implementation can - determine the element type specified for the - result-type, the element type of the - resulting array is the result of - upgrading that element type; or, if the - implementation can determine that the element type is - unspecified (or *), the element type of the - resulting array is t; otherwise, an error is - signaled. - - - Examples - -(map-query 'list #'(lambda (salary name) - (declare (ignorable name)) - (read-from-string salary)) - "select salary,name from simple where salary > 8000") -=> (10000.0 8000.5) - -(map-query '(vector double-float) - #'(lambda (salary name) - (declare (ignorable name)) - (let ((*read-default-float-format* 'double-float)) - (coerce (read-from-string salary) 'double-float)) - "select salary,name from simple where salary > 8000")) -=> #(10000.0d0 8000.5d0) -(type-of *) -=> (SIMPLE-ARRAY DOUBLE-FLOAT (2)) - -(let (list) - (values (map-query nil #'(lambda (salary name) - (push (cons name (read-from-string salary)) list)) - "select salary,name from simple where salary > 8000") - list)) -=> NIL -=> (("Hacker, Random J." . 8000.5) ("Mai, Pierre" . 10000.0)) - - - - Side Effects - Whatever effects the execution of the SQL query has - on the underlying database, if any. - - - Affected by - None. - - - Exceptional Situations - If the execution of the SQL query leads to any - errors, an error of type - clsql-sql-error is signalled. - An error of type type-error must - be signaled if the output-type-spec is - not a recognizable subtype of list, not a - recognizable subtype of vector, and not - nil. - An error of type type-error - should be signaled if - output-type-spec specifies the number - of elements and the size of the result set is different from - that number. - - - See Also - - - query - do-query - - - - - Notes - None. - - - - - DO-QUERY - Iterate over all the tuples of a - query - Macro - - - Syntax - do-query ((&rest args) query-expression &key database result-types) &body body => nil - - - Arguments and Values - - - args - - A list of variable names. - - - - query-expression - - An sql - expression that represents an SQL - query which is expected to return a (possibly empty) - result set, where each tuple has as many attributes as - function takes arguments. - - - - database - - A - database - object. This will default to - *default-database*. - - - - result-types - - - A field type specifier. - The default is &nil;. See query - for the semantics of this argument. - - - - - body - - A body of Lisp code, like in a - destructuring-bind form. - - - - - - Description - Executes the body of code - repeatedly with the variable names in - args bound to the attributes of each - tuple in the result set returned by executing the SQL - query-expression on the - database specified. - The body of code is executed in a block named - nil which may be returned from prematurely - via return or - return-from. In this case the result - of evaluating the do-query form will be - the one supplied to return or - return-from. Otherwise the result will - be nil. - The body of code appears also is if wrapped in a - destructuring-bind form, thus allowing - declarations at the start of the body, especially those - pertaining to the bindings of the variables named in - args. - - - Examples - -(do-query ((salary name) "select salary,name from simple") - (format t "~30A gets $~2,5$~%" name (read-from-string salary))) ->> Mai, Pierre gets $10000.00 ->> Hacker, Random J. gets $08000.50 -=> NIL - -(do-query ((salary name) "select salary,name from simple") - (return (cons salary name))) -=> ("10000.00" . "Mai, Pierre") - - - - Side Effects - Whatever effects the execution of the SQL query has - on the underlying database, if any. - - - Affected by - None. - - - Exceptional Situations - If the execution of the SQL query leads to any - errors, an error of type - clsql-sql-error is signalled. - If the number of variable names in - args and the number of attributes in - the tuples in the result set don't match up, an error is - signalled. - - - See Also - - - query - map-query - - - - - Notes - None. - - - - - LOOP-FOR-AS-TUPLES - Iterate over all the tuples of a - query via a loop clause - Loop Clause - - - Compatibility - loop-for-as-tuples only works with &cmucl;. - - - Syntax - var [type-spec] being {each | the} {record | records | tuple | tuples} {in | of} query [from database] - - - Arguments and Values - - - var - - A d-var-spec, as defined in the - grammar for loop-clauses in the - ANSI Standard for Common Lisp. This allows for the - usual loop-style destructuring. - - - - type-spec - - An optional type-spec either - simple or destructured, as defined in the grammar for - loop-clauses in the ANSI Standard - for Common Lisp. - - - - query - - An sql - expression that represents an SQL - query which is expected to return a (possibly empty) - result set, where each tuple has as many attributes as - function takes arguments. - - - - database - - An optional - database - object. This will default to the value - of *default-database*. - - - - - - Description - This clause is an iteration driver for - loop, that binds the given variable - (possibly destructured) to the consecutive tuples (which are - represented as lists of attribute values) in the result set - returned by executing the SQL query - expression on the database - specified. - - - Examples - -(defvar *my-db* (connect '("dent" "newesim" "dent" "dent")) - "My database" -=> *MY-DB* -(loop with time-graph = (make-hash-table :test #'equal) - with event-graph = (make-hash-table :test #'equal) - for (time event) being the tuples of "select time,event from log" - from *my-db* - do - (incf (gethash time time-graph 0)) - (incf (gethash event event-graph 0)) - finally - (flet ((show-graph (k v) (format t "~40A => ~5D~%" k v))) - (format t "~&Time-Graph:~%===========~%") - (maphash #'show-graph time-graph) - (format t "~&~%Event-Graph:~%============~%") - (maphash #'show-graph event-graph)) - (return (values time-graph event-graph))) ->> Time-Graph: ->> =========== ->> D => 53000 ->> X => 3 ->> test-me => 3000 ->> ->> Event-Graph: ->> ============ ->> CLOS Benchmark entry. => 9000 ->> Demo Text... => 3 ->> doit-text => 3000 ->> C Benchmark entry. => 12000 ->> CLOS Benchmark entry => 32000 -=> #<EQUAL hash table, 3 entries {48350A1D}> -=> #<EQUAL hash table, 5 entries {48350FCD}> - - - - Side Effects - Whatever effects the execution of the SQL query has - on the underlying database, if any. - - - Affected by - None. - - - Exceptional Situations - If the execution of the SQL query leads to any - errors, an error of type - clsql-sql-error is signalled. - Otherwise, any of the exceptional situations of - loop applies. - - - See Also - - - query - map-query - do-query - - - - - Notes - None. - - -