X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=sql%2Fdb-interface.lisp;h=5cdb719f3ea00cdc4915701fff2abec177aaff49;hp=385e08ba07b7b0811b11f5a29f03f16228b4e040;hb=6bee16be3f891067ae8fe1a67e13b39e8ee72598;hpb=8c6c643e3debe875bd14408cc3129d8148dfd125 diff --git a/sql/db-interface.lisp b/sql/db-interface.lisp index 385e08b..5cdb719 100644 --- a/sql/db-interface.lisp +++ b/sql/db-interface.lisp @@ -6,12 +6,10 @@ ;;;; Purpose: Generic function definitions for DB interfaces ;;;; Programmers: Kevin M. Rosenberg based on ;;;; Original code by Pierre R. Mai. Additions from -;;;; onShoreD to support UncommonSQL front-end +;;;; onShoreD to support UncommonSQL front-end ;;;; Date Started: Feb 2002 ;;;; -;;;; $Id$ -;;;; -;;;; This file, part of CLSQL, is Copyright (c) 2002-2004 by Kevin M. Rosenberg +;;;; This file, part of CLSQL, is Copyright (c) 2002-2010 by Kevin M. Rosenberg ;;;; and Copyright (c) 1999-2001 by Pierre R. Mai, and onShoreD ;;;; ;;;; CLSQL users are granted the rights to distribute and use this software @@ -44,34 +42,47 @@ was called with the connection-spec.")) (defgeneric database-reconnect (database) (:method ((database t)) - (signal-no-database-error database)) + (signal-no-database-error database)) (:documentation "Internal generic implementation of reconnect.")) (defgeneric database-disconnect (database) (:method ((database t)) - (signal-no-database-error database)) + (signal-no-database-error database)) (:documentation "Internal generic implementation of disconnect.")) (defgeneric database-query (query-expression database result-types field-names) (:method (query-expression (database t) result-types field-names) - (declare (ignore query-expression result-types field-names)) - (signal-no-database-error database)) + (declare (ignore query-expression result-types field-names)) + (signal-no-database-error database)) + (:method (query-expression (database database) result-types field-names) + (declare (ignore query-expression result-types field-names)) + (warn "database-query not implemented for database type ~A." + (database-type database))) (:documentation "Internal generic implementation of query.")) (defgeneric database-execute-command (sql-expression database) (:method (sql-expression (database t)) - (declare (ignore sql-expression)) - (signal-no-database-error database)) + (declare (ignore sql-expression)) + (signal-no-database-error database)) + (:method (sql-expression (database database)) + (declare (ignore sql-expression)) + (warn "database-execute-command not implemented for database type ~A." + (database-type database))) (:documentation "Internal generic implementation of execute-command.")) ;;; Mapping and iteration (defgeneric database-query-result-set (query-expression database &key full-set result-types) (:method (query-expression (database t) &key full-set result-types) - (declare (ignore query-expression full-set result-types)) - (signal-no-database-error database) - (values nil nil nil)) + (declare (ignore query-expression full-set result-types)) + (signal-no-database-error database) + (values nil nil nil)) + (:method (query-expression (database database) &key full-set result-types) + (declare (ignore query-expression full-set result-types)) + (warn "database-query-result-set not implemented for database type ~A." + (database-type database)) + (values nil nil nil)) (:documentation "Internal generic implementation of query mapping. Starts the query specified by query-expression on the given database and returns @@ -89,14 +100,22 @@ function should signal a sql-database-data-error.")) (defgeneric database-dump-result-set (result-set database) (:method (result-set (database t)) - (declare (ignore result-set)) - (signal-no-database-error database)) + (declare (ignore result-set)) + (signal-no-database-error database)) + (:method (result-set (database database)) + (declare (ignore result-set)) + (warn "database-dump-result-set not implemented for database type ~A." + (database-type database))) (:documentation "Dumps the received result-set.")) (defgeneric database-store-next-row (result-set database list) (:method (result-set (database t) list) - (declare (ignore result-set list)) - (signal-no-database-error database)) + (declare (ignore result-set list)) + (signal-no-database-error database)) + (:method (result-set (database database) list) + (declare (ignore result-set list)) + (warn "database-store-next-row not implemented for database type ~A." + (database-type database))) (:documentation "Returns t and stores the next row in the result set in list or returns nil when result-set is finished.")) @@ -108,9 +127,9 @@ returns nil when result-set is finished.")) (defgeneric database-probe (connection-spec type) (:method (spec type) (declare (ignore spec)) - (warn "database-proe not support for database-type ~A." type)) + (warn "database-probe not support for database-type ~A." type)) (:documentation - "Probes for the existence of a database, returns T if database found or NIL + "Probes for the existence of a database, returns T if database found or NIL if not found. May signal an error if unable to communicate with database server.")) (defgeneric database-list (connection-spec type) @@ -120,23 +139,14 @@ if not found. May signal an error if unable to communicate with database server. (:documentation "Lists all databases found for TYPE. May signal an error if unable to communicate with database server.")) -(defgeneric database-destroy (connection-spec database) - (:documentation "Destroys (drops) a database.")) - (defgeneric database-truncate (database) (:method ((database t)) (signal-no-database-error database)) (:documentation "Remove all data from database.")) -(defgeneric database-describe-table (database table) - (:method ((database t) table) - (declare (ignore table)) - (signal-no-database-error database)) - (:documentation "Return a list of name/type for columns in table")) - -(defgeneric database-destory (connection-spec type) +(defgeneric database-destroy (connection-spec type) (:documentation - "Destroys a database, returns T if successfull or signals an error + "Destroys (drops) a database, returns T if successfull or signals an error if unable to destory.")) (defgeneric database-create-sequence (name database) @@ -157,49 +167,119 @@ if unable to destory.")) (defgeneric database-sequence-last (name database) (:documentation "Select the last value in sequence NAME in DATABASE.")) +(defgeneric database-last-autoincrement-id (database table column) + (:documentation "Many databases have the notion of an auto-increment + id; i.e. a sequence implicitly on a table. This function should + return that ID." )) + + (defgeneric database-start-transaction (database) (:documentation "Start a transaction in DATABASE.") (:method ((database t)) - (signal-no-database-error database))) + (signal-no-database-error database))) (defgeneric database-commit-transaction (database) (:documentation "Commit current transaction in DATABASE.") (:method ((database t)) - (signal-no-database-error database))) + (signal-no-database-error database))) (defgeneric database-abort-transaction (database) (:documentation "Abort current transaction in DATABASE.") (:method ((database t)) - (signal-no-database-error database))) + (signal-no-database-error database))) -(defgeneric database-get-type-specifier (type args database) +(defgeneric database-get-type-specifier (type args database db-underlying-type) (:documentation "Return the type SQL type specifier as a string, for the given lisp type and parameters.")) (defgeneric database-list-tables (database &key owner) - (:documentation "List all tables in the given database")) - + (:documentation "List all tables in the given database") + (:method ((database database) &key owner) + (declare (ignore owner)) + (warn "database-list-tables not implemented for database type ~A." + (database-type database))) + (:method ((database t) &key owner) + (declare (ignore owner)) + (signal-no-database-error database))) + +(defgeneric database-list-tables-and-sequences (database &key owner) + (:documentation "List all tables in the given database, may include seqeneces") + (:method ((database t) &key owner) + (declare (ignore owner)) + (signal-no-database-error database)) + (:method ((database database) &key owner) + (database-list-tables database :owner owner))) + (defgeneric database-list-views (database &key owner) - (:documentation "List all views in the DATABASE.")) + (:documentation "List all views in the DATABASE.") + (:method ((database database) &key owner) + (declare (ignore owner)) + (warn "database-list-views not implemented for database type ~A." + (database-type database))) + (:method ((database t) &key owner) + (declare (ignore owner)) + (signal-no-database-error database))) (defgeneric database-list-indexes (database &key owner) - (:documentation "List all indexes in the DATABASE.")) + (:documentation "List all indexes in the DATABASE.") + (:method ((database database) &key owner) + (declare (ignore owner)) + (warn "database-list-indexes not implemented for database type ~A." + (database-type database))) + (:method ((database t) &key owner) + (declare (ignore owner)) + (signal-no-database-error database))) (defgeneric database-list-table-indexes (table database &key owner) - (:documentation "List all indexes for a table in the DATABASE.")) + (:documentation "List all indexes for a table in the DATABASE.") + (:method (table (database database) &key owner) + (declare (ignore table owner)) + (warn "database-list-table-indexes not implemented for database type ~A." + (database-type database))) + (:method (table (database t) &key owner) + (declare (ignore table owner)) + (signal-no-database-error database))) (defgeneric database-list-attributes (table database &key owner) - (:documentation "List all attributes in TABLE.")) + (:documentation "List all attributes in TABLE.") + (:method (table (database database) &key owner) + (declare (ignore table owner)) + (warn "database-list-attributes not implemented for database type ~A." + (database-type database))) + (:method (table (database t) &key owner) + (declare (ignore table owner)) + (signal-no-database-error database))) (defgeneric database-attribute-type (attribute table database &key owner) (:documentation "Return the type of ATTRIBUTE in TABLE. Returns multiple values -of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")) +of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.") + (:method (attribute table (database database) &key owner) + (declare (ignore attribute table owner)) + (warn "database-list-attribute-type not implemented for database type ~A." + (database-type database))) + (:method (attribute table (database t) &key owner) + (declare (ignore attribute table owner)) + (signal-no-database-error database))) (defgeneric database-add-attribute (table attribute database) - (:documentation "Add the attribute to the table.")) + (:documentation "Add the attribute to the table.") + (:method (table attribute (database database)) + (declare (ignore table attribute)) + (warn "database-add-attribute not implemented for database type ~A." + (database-type database))) + (:method (table attribute (database t)) + (declare (ignore table attribute)) + (signal-no-database-error database))) (defgeneric database-rename-attribute (table oldatt newname database) - (:documentation "Rename the attribute in the table to NEWNAME.")) + (:documentation "Rename the attribute in the table to NEWNAME.") + (:method (table oldatt newname (database database)) + (declare (ignore table oldatt newname)) + (warn "database-rename-attribute not implemented for database type ~A." + (database-type database))) + (:method (table oldatt newname (database t)) + (declare (ignore table oldatt newname)) + (signal-no-database-error database))) (defgeneric oid (object) (:documentation "Return the unique ID of a database object.")) @@ -213,68 +293,97 @@ of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")) (defgeneric db-type-use-column-on-drop-index? (db-type) (:method (db-type) - (declare (ignore db-type)) - nil) + (declare (ignore db-type)) + nil) (:documentation "NIL [default] if database-type does not use column name on DROP INDEX.")) +(defgeneric db-type-use-fully-qualified-column-on-drop-index? (db-type) + (:method (db-type) + (declare (ignore db-type)) + nil) + (:documentation "NIL [default] if database-type does not require fully qualified column name on DROP INDEX.")) + (defgeneric db-type-has-views? (db-type) (:method (db-type) - (declare (ignore db-type)) - ;; SQL92 has views - t) + (declare (ignore db-type)) + ;; SQL92 has views + t) (:documentation "T [default] if database-type supports views.")) (defgeneric db-type-has-bigint? (db-type) (:method (db-type) - (declare (ignore db-type)) - ;; SQL92 has bigint - t) + (declare (ignore db-type)) + ;; SQL92 has bigint + t) (:documentation "T [default] if database-type supports bigint.")) (defgeneric db-type-default-case (db-type) (:method (db-type) - (declare (ignore db-type)) - ;; By default, CommonSQL converts identifiers to UPPER case. - :upper) + (declare (ignore db-type)) + ;; By default, CommonSQL converts identifiers to UPPER case. + :upper) (:documentation ":upper [default] if means identifiers mapped to UPPER case SQL like CommonSQL API. However, Postgresql maps identifiers to lower case, so PostgreSQL uses a value of :lower for this result.")) (defgeneric db-type-has-fancy-math? (db-type) (:method (db-type) - (declare (ignore db-type)) - nil) + (declare (ignore db-type)) + nil) (:documentation "NIL [default] if database-type does not have fancy math.")) (defgeneric db-type-has-subqueries? (db-type) (:method (db-type) - (declare (ignore db-type)) - t) + (declare (ignore db-type)) + t) (:documentation "T [default] if database-type supports views.")) (defgeneric db-type-has-boolean-where? (db-type) (:method (db-type) - (declare (ignore db-type)) - ;; SQL99 has boolean where - t) + (declare (ignore db-type)) + ;; SQL99 has boolean where + t) (:documentation "T [default] if database-type supports boolean WHERE clause, such as 'WHERE MARRIED'.")) (defgeneric db-type-has-union? (db-type) (:method (db-type) - (declare (ignore db-type)) - t) + (declare (ignore db-type)) + t) (:documentation "T [default] if database-type supports boolean UNION.")) (defgeneric db-backend-has-create/destroy-db? (db-type) (:method (db-type) - (declare (ignore db-type)) - t) + (declare (ignore db-type)) + t) (:documentation "T [default] if backend can destroy and create databases.")) (defgeneric db-type-transaction-capable? (db database) (:method (db database) - (declare (ignore db database)) - t) + (declare (ignore db database)) + t) (:documentation "T [default] if database can supports transactions.")) +(defgeneric db-type-has-prepared-stmt? (db-type) + (:method ((db-type t)) + nil) + (:documentation "T if database backend supports prepared statements.")) + +(defgeneric db-type-has-intersect? (db-type) + (:method (db-type) + (declare (ignore db-type)) + t) + (:documentation "T [default] if database-type supports INTERSECT.")) + +(defgeneric db-type-has-except? (db-type) + (:method (db-type) + (declare (ignore db-type)) + t) + (:documentation "T [default] if database-type supports EXCEPT.")) + +(defgeneric db-type-has-auto-increment? (db-type) + (:method (db-type) + (declare (ignore db-type) + nil)) + (:documentation "NIL [default] if database-type supports auto-incrementing columns.")) + ;;; Large objects support (Marc Battyani) (defgeneric database-create-large-object (database) @@ -289,6 +398,52 @@ of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")) (defgeneric database-delete-large-object (object-id database) (:documentation "Deletes the large object in the database")) +;; Prepared statements + +(defgeneric database-prepare (stmt types database result-types field-names) + (:method (stmt types (database t) result-types field-names) + (declare (ignore stmt types result-types field-names)) + (signal-no-database-error database)) + (:method (stmt types (database database) result-types field-names) + (declare (ignore stmt types result-types field-names)) + (error 'sql-database-error + :message + (format nil "DATABASE-PREPARE not implemented for ~S" database))) + (:documentation "Prepare a statement for later execution.")) + +(defgeneric database-bind-parameter (prepared-stmt position value) + (:method ((pstmt t) position value) + (declare (ignore position value)) + (error 'sql-database-error + :message + (format nil "database-bind-paremeter not implemented for ~S" pstmt))) + (:documentation "Bind a parameter for a prepared statement.")) + +(defgeneric database-run-prepared (prepared-stmt) + (:method ((pstmt t)) + (error 'sql-database-error + :message (format nil "database-run-prepared not specialized for ~S" pstmt))) + (:documentation "Execute a prepared statement.")) + +(defgeneric database-free-prepared (prepared-stmt) + (:method ((pstmt t)) + ;; nothing to do by default + nil) + (:documentation "Free the resources of a prepared statement.")) + +(defgeneric database-acquire-from-conn-pool (database) + (:documentation "Acquire a database connection from the pool. This +is a chance to test the connection for validity before returning it to +the user. If this function returns NIL or throws an error that +database connection is considered bad and we make a new one. + +Database objects have a chance to specialize, otherwise the default +method uses the database-underlying-type and tries to do something +appropriate.")) + +(defgeneric database-release-to-conn-pool (database) + (:documentation "Chance for the database to cleanup before it is + returned to the connection pool.")) ;; Checks for closed database @@ -296,8 +451,8 @@ of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")) (unless (is-database-open database) (signal-closed-database-error database))) -(defmethod database-query :before (query-expression (database database) - result-set field-names) +(defmethod database-query :before (query-expression (database database) + result-set field-names) (declare (ignore query-expression result-set field-names)) (unless (is-database-open database) (signal-closed-database-error database))) @@ -317,7 +472,7 @@ of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")) (declare (ignore result-set)) (unless (is-database-open database) (signal-closed-database-error database))) - + (defmethod database-store-next-row :before (result-set (database database) list) (declare (ignore result-set list)) (unless (is-database-open database) @@ -335,6 +490,11 @@ of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")) (unless (is-database-open database) (signal-closed-database-error database))) -(defgeneric describe-table (table &key database) - (:documentation "Describes a table, returns a list of name/type for columns in table")) +(defvar *foreign-library-search-paths* nil + "A list of pathnames denoting directories where CLSQL will look +for foreign libraries \(in addition to the default places).") +(defun push-library-path (path) + "Adds the pathspec PATH \(which should denote a directory) to +the list *FOREIGN-LIBRARY-SEARCH-PATHS*." + (pushnew path *foreign-library-search-paths* :test #'equal))