X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=sql%2Fdb-interface.lisp;h=5cdb719f3ea00cdc4915701fff2abec177aaff49;hp=0d67bb0147a25e7a85e581bedbbab6f620cfcee3;hb=6bee16be3f891067ae8fe1a67e13b39e8ee72598;hpb=18e34efea688a6758b6e997401fbc3f241da98f3

diff --git a/sql/db-interface.lisp b/sql/db-interface.lisp
index 0d67bb0..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,47 +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)))
+             (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)))
+           (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))
+           (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
@@ -102,22 +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)))
+           (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)))
+           (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."))
@@ -129,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)
@@ -141,17 +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-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)
@@ -172,20 +167,26 @@ 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 db-underlying-type)
   (:documentation "Return the type SQL type specifier as a string, for
@@ -194,37 +195,37 @@ the given lisp type and parameters."))
 (defgeneric database-list-tables (database &key owner)
   (: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)))
+           (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)))
+           (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))
+           (declare (ignore owner))
+           (signal-no-database-error database))
   (:method ((database database) &key owner)
-	   (database-list-tables database :owner owner)))
- 
+           (database-list-tables database :owner owner)))
+
 (defgeneric database-list-views (database &key owner)
   (: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)))
+           (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)))
+           (declare (ignore owner))
+           (signal-no-database-error database)))
 
 (defgeneric database-list-indexes (database &key owner)
   (: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)))
+           (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)))
@@ -232,53 +233,53 @@ the given lisp type and parameters."))
 (defgeneric database-list-table-indexes (table database &key owner)
   (: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)))
+           (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.")
   (:method (table (database database) &key owner)
-	   (declare (ignore table owner))
-	   (warn "database-list-attributes not implemented for database type ~A."
-		 (database-type database)))
+           (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)))
+           (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.")
   (: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)))
+           (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)))
+           (declare (ignore attribute table owner))
+           (signal-no-database-error database)))
 
 (defgeneric database-add-attribute (table attribute database)
   (: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)))
+           (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)))
+           (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.")
   (:method (table oldatt newname (database database))
-	   (declare (ignore table oldatt newname))
-	   (warn "database-rename-attribute not implemented for database type ~A."
-		 (database-type 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)))
+           (declare (ignore table oldatt newname))
+           (signal-no-database-error database)))
 
 (defgeneric oid (object)
   (:documentation "Return the unique ID of a database object."))
@@ -292,66 +293,72 @@ 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)
@@ -359,6 +366,23 @@ of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")
     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)
 
@@ -383,22 +407,22 @@ of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")
   (: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)))
+           :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)))
+           :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)))
+           :message (format nil "database-run-prepared not specialized for ~S" pstmt)))
   (:documentation "Execute a prepared statement."))
 
 (defgeneric database-free-prepared (prepared-stmt)
@@ -407,14 +431,28 @@ of TYPE_NAME (keyword) PRECISION SCALE NULLABLE.")
     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
 
 (defmethod database-disconnect :before ((database database))
   (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)))
@@ -434,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)
@@ -459,4 +497,4 @@ 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*."
-  (push path *foreign-library-search-paths*))
\ No newline at end of file
+  (pushnew path *foreign-library-search-paths* :test #'equal))