X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=db-aodbc%2Faodbc-sql.lisp;h=060db96d844d8d98c97c529ec3962292617e6d00;hb=d0b59237966d6fbc27adcbbae11fa85087ea122d;hp=181c3edeee490678d82963ce2ef9a9f33c7f11cf;hpb=8fb55434ea4d6e0a0775b0012c83a2b27061bb59;p=clsql.git diff --git a/db-aodbc/aodbc-sql.lisp b/db-aodbc/aodbc-sql.lisp index 181c3ed..060db96 100644 --- a/db-aodbc/aodbc-sql.lisp +++ b/db-aodbc/aodbc-sql.lisp @@ -19,25 +19,23 @@ (in-package #:clsql-aodbc) ;; interface foreign library loading routines -(defmethod clsql-base-sys:database-type-library-loaded ((database-type (eql :aodbc))) +(defmethod clsql-sys:database-type-library-loaded ((database-type (eql :aodbc))) "T if foreign library was able to be loaded successfully. " (when (find-package :dbi) ;; finds Allegro's DBI (AODBC) package t)) -(defmethod clsql-base-sys:database-type-load-foreign ((databae-type (eql :aodbc))) +(defmethod clsql-sys:database-type-load-foreign ((databae-type (eql :aodbc))) t) (when (find-package :dbi) - (clsql-base-sys:database-type-load-foreign :aodbc)) - -(defmethod database-initialize-database-type ((database-type (eql :aodbc))) - t) + (clsql-sys:database-type-load-foreign :aodbc)) ;; AODBC interface (defclass aodbc-database (database) - ((aodbc-conn :accessor database-aodbc-conn :initarg :aodbc-conn))) + ((aodbc-conn :accessor database-aodbc-conn :initarg :aodbc-conn) + (aodbc-db-type :accessor database-aodbc-db-type :initform :unknown))) (defmethod database-name-from-spec (connection-spec (database-type (eql :aodbc))) @@ -53,16 +51,18 @@ (handler-case (make-instance 'aodbc-database :name (database-name-from-spec connection-spec :aodbc) + :database-type :aodbc :aodbc-conn (dbi:connect :user user :password password :data-source-name dsn)) + (clsql-error (e) + (error e)) (error () ;; Init or Connect failed - (error 'clsql-connect-error + (error 'sql-connection-error :database-type database-type :connection-spec connection-spec - :errno nil - :error "Connection failed"))))) + :message "Connection failed"))))) (defmethod database-disconnect ((database aodbc-database)) #+aodbc-v2 @@ -70,29 +70,32 @@ (setf (database-aodbc-conn database) nil) t) -(defmethod database-query (query-expression (database aodbc-database) result-types) +(defmethod database-query (query-expression (database aodbc-database) result-types field-names) #+aodbc-v2 (handler-case (dbi:sql query-expression :db (database-aodbc-conn database) - :types result-types) + :types result-types + :column-names field-names) + (clsql-error (e) + (error e)) (error () - (error 'clsql-sql-error + (error 'sql-database-data-error :database database :expression query-expression - :errno nil - :error "Query failed")))) + :message "Query failed.")))) (defmethod database-execute-command (sql-expression (database aodbc-database)) #+aodbc-v2 (handler-case (dbi:sql sql-expression :db (database-aodbc-conn database)) + (clsql-error (e) + (error e)) (error () - (error 'clsql-sql-error + (error 'sql-database-data-error :database database :expression sql-expression - :errno nil - :error "Execute command failed")))) + :error "Execute command failed.")))) (defstruct aodbc-result-set (query nil) @@ -118,12 +121,13 @@ (length column-names) nil ;; not able to return number of rows with aodbc )) + (clsql-error (e) + (error e)) (error () - (error 'clsql-sql-error + (error 'sql-database-data-error :database database :expression query-expression - :errno nil - :error "Query result set failed")))) + :error "Query result set failed.")))) (defmethod database-dump-result-set (result-set (database aodbc-database)) #+aodbc-v2 @@ -147,11 +151,11 @@ ;;; Sequence functions (defun %sequence-name-to-table (sequence-name) - (concatenate 'string "_clsql_seq_" (sql-escape sequence-name))) + (concatenate 'string "_CLSQL_SEQ_" (sql-escape sequence-name))) (defun %table-name-to-sequence-name (table-name) (and (>= (length table-name) 11) - (string= (subseq table-name 0 11) "_clsql_seq_") + (string-equal (subseq table-name 0 11) "_CLSQL_SEQ_") (subseq table-name 11))) (defmethod database-create-sequence (sequence-name @@ -159,11 +163,11 @@ (let ((table-name (%sequence-name-to-table sequence-name))) (database-execute-command (concatenate 'string "CREATE TABLE " table-name - " (id int NOT NULL PRIMARY KEY AUTO_INCREMENT)") + " (last_value int NOT NULL PRIMARY KEY, increment_by int, min_value int, is_called char(1))") database) (database-execute-command (concatenate 'string "INSERT INTO " table-name - " VALUES (0)") + " VALUES (1,1,1,'f')") database))) (defmethod database-drop-sequence (sequence-name @@ -175,24 +179,101 @@ (defmethod database-list-sequences ((database aodbc-database) &key (owner nil)) (declare (ignore owner)) - (mapcar #'(lambda (s) (%table-name-to-sequence-name (car s))) - (database-query "SHOW TABLES LIKE '%clsql_seq%'" - database nil))) + (warn "database-list-sequences not implemented for AODBC.") + nil) + +(defmethod database-list-tables ((database aodbc-database) + &key (owner nil)) + (declare (ignore owner)) + #+aodbc-v2 + (multiple-value-bind (rows col-names) + (dbi:list-all-database-tables :db (database-aodbc-conn database)) + (declare (ignore col-names)) + ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager + ;; TABLE_NAME in third column, TABLE_TYPE in fourth column + (loop for row in rows + when (and (not (string-equal "information_schema" (nth 1 row))) + (string-equal "TABLE" (nth 3 row))) + collect (nth 2 row)))) + +(defmethod database-list-views ((database aodbc-database) + &key (owner nil)) + (declare (ignore owner)) + #+aodbc-v2 + (multiple-value-bind (rows col-names) + (dbi:list-all-database-tables :db (database-aodbc-conn database)) + (declare (ignore col-names)) + ;; TABLE_SCHEM is hard-coded in second column by ODBC Driver Manager + ;; TABLE_NAME in third column, TABLE_TYPE in fourth column + (loop for row in rows + when (and (not (string-equal "information_schema" (nth 1 row))) + (string-equal "VIEW" (nth 3 row))) + collect (nth 2 row)))) + +(defmethod database-list-attributes ((table string) (database aodbc-database) + &key (owner nil)) + (declare (ignore owner)) + #+aodbc-v2 + (multiple-value-bind (rows col-names) + (dbi:list-all-table-columns table :db (database-aodbc-conn database)) + (let ((pos (position "COLUMN_NAME" col-names :test #'string-equal))) + (when pos + (loop for row in rows + collect (nth pos row)))))) + +(defmethod database-attribute-type ((attribute string) (table string) (database aodbc-database) + &key (owner nil)) + (declare (ignore owner)) + #+aodbc-v2 + (multiple-value-bind (rows col-names) + (dbi:list-all-table-columns table :db (database-aodbc-conn database)) + (let ((pos (position "TYPE_NAME" col-names :test #'string-equal))) + (when pos + (loop for row in rows + collect (nth pos row)))))) + +(defmethod database-list-indexes ((database aodbc-database) + &key (owner nil)) + (warn "database-list-indexes not implemented for AODBC.") + nil) (defmethod database-set-sequence-position (sequence-name (position integer) (database aodbc-database)) (database-execute-command - (format nil "UPDATE ~A SET id=~A" (%sequence-name-to-table sequence-name) + (format nil "UPDATE ~A SET last_value=~A,is_called='t'" + (%sequence-name-to-table sequence-name) position) database) position) (defmethod database-sequence-next (sequence-name (database aodbc-database)) - (warn "Not implemented.")) - + (without-interrupts + (let* ((table-name (%sequence-name-to-table sequence-name)) + (tuple + (car (database-query + (concatenate 'string "SELECT last_value,is_called FROM " + table-name) + database :auto nil)))) + (cond + ((char-equal (schar (second tuple) 0) #\f) + (database-execute-command + (format nil "UPDATE ~A SET is_called='t'" table-name) + database) + (car tuple)) + (t + (let ((new-pos (1+ (car tuple)))) + (database-execute-command + (format nil "UPDATE ~A SET last_value=~D" table-name new-pos) + database) + new-pos)))))) + (defmethod database-sequence-last (sequence-name (database aodbc-database)) - (declare (ignore sequence-name))) + (without-interrupts + (caar (database-query + (concatenate 'string "SELECT last_value FROM " + (%sequence-name-to-table sequence-name)) + database :auto nil)))) (defmethod database-create (connection-spec (type (eql :aodbc))) (warn "Not implemented.")) @@ -203,6 +284,16 @@ (defmethod database-probe (connection-spec (type (eql :aodbc))) (warn "Not implemented.")) -#+ignore -(when (clsql-base-sys:database-type-library-loaded :aodbc) - (clsql-base-sys:initialize-database-type :database-type :aodbc)) +;;; Backend capabilities + +(defmethod database-underlying-type ((database aodbc-database)) + (database-aodbc-db-type database)) + +(defmethod db-backend-has-create/destroy-db? ((db-type (eql :aodbc))) + nil) + +(defmethod database-initialize-database-type ((database-type (eql :aodbc))) + t) + +(when (clsql-sys:database-type-library-loaded :aodbc) + (clsql-sys:initialize-database-type :database-type :aodbc))