X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=db-odbc%2Fodbc-sql.lisp;h=656e8f43c13a0388d2d9697f76462cfc8bb08359;hp=a92e6feeca8bd7462d3c81fa60d3fdd9351342f5;hb=8a8ee2d7d791b7a3efaed06420802a925d16fca3;hpb=a7e38685365a6cf067290843c0ed168b6fb545e9 diff --git a/db-odbc/odbc-sql.lisp b/db-odbc/odbc-sql.lisp index a92e6fe..656e8f4 100644 --- a/db-odbc/odbc-sql.lisp +++ b/db-odbc/odbc-sql.lisp @@ -17,7 +17,7 @@ ;;;; ************************************************************************* (defpackage #:clsql-odbc - (:use #:common-lisp #:clsql-base-sys) + (:use #:common-lisp #:clsql-sys) (:export #:odbc-database) (:documentation "This is the CLSQL interface to ODBC.")) @@ -26,7 +26,8 @@ ;; ODBC interface (defclass odbc-database (database) - ((odbc-conn :accessor database-odbc-conn :initarg :odbc-conn))) + ((odbc-conn :accessor database-odbc-conn :initarg :odbc-conn) + (odbc-db-type :accessor database-odbc-db-type))) (defmethod database-name-from-spec (connection-spec (database-type (eql :odbc))) @@ -39,24 +40,33 @@ (check-connection-spec connection-spec database-type (dsn user password)) (destructuring-bind (dsn user password) connection-spec (handler-case - (make-instance 'odbc-database - :name (database-name-from-spec connection-spec :odbc) - :odbc-conn - (odbc-dbi:connect :user user - :password password - :data-source-name dsn)) - (error () ;; Init or Connect failed - (error 'clsql-connect-error - :database-type database-type - :connection-spec connection-spec - :errno nil - :error "Connection failed"))))) - -#+nil + (let ((db + (make-instance 'odbc-database + :name (database-name-from-spec connection-spec :odbc) + :database-type :odbc + :odbc-conn + (odbc-dbi:connect :user user + :password password + :data-source-name dsn)))) + (store-type-of-connected-database db) + db) + (clsql-error (e) + (error e)) + #+ignore + (error () ;; Init or Connect failed + (error 'clsql-connect-error + :database-type database-type + :connection-spec connection-spec + :errno nil + :error "Connection failed"))))) + +(defmethod database-underlying-type ((database odbc-database)) + (database-odbc-db-type database)) + (defun store-type-of-connected-database (db) - (let* ((odbc-db (odbc-db db)) - (server-name (get-odbc-info odbc-db odbc::$SQL_SERVER_NAME)) - (dbms-name (get-odbc-info odbc-db odbc::$SQL_DBMS_NAME)) + (let* ((odbc-conn (database-odbc-conn db)) + (server-name (odbc-dbi::get-odbc-info odbc-conn odbc::$SQL_SERVER_NAME)) + (dbms-name (odbc-dbi::get-odbc-info odbc-conn odbc::$SQL_DBMS_NAME)) (type ;; need SERVER-NAME and DBMS-NAME because many drivers mix this up (cond @@ -69,18 +79,22 @@ ((or (search "oracle" server-name :test #'char-equal) (search "oracle" dbms-name :test #'char-equal)) :oracle)))) - (setf (database-type db) type))) + (setf (database-odbc-db-type db) type))) - (defmethod database-disconnect ((database odbc-database)) (odbc-dbi:disconnect (database-odbc-conn database)) (setf (database-odbc-conn database) nil) t) -(defmethod database-query (query-expression (database odbc-database) result-types) +(defmethod database-query (query-expression (database odbc-database) + result-types field-names) (handler-case (odbc-dbi:sql query-expression :db (database-odbc-conn database) - :types result-types) + :result-types result-types + :column-names field-names) + (clsql-error (e) + (error e)) + #+ignore (error () (error 'clsql-sql-error :database database @@ -91,7 +105,10 @@ (defmethod database-execute-command (sql-expression (database odbc-database)) (handler-case - (odbc-dbi:sql sql-expression (database-odbc-conn database)) + (odbc-dbi:sql sql-expression :db (database-odbc-conn database)) + (clsql-error (e) + (error e)) + #+ignore (error () (error 'clsql-sql-error :database database @@ -101,7 +118,7 @@ (defstruct odbc-result-set (query nil) - (types nil :type cons) + (types nil) (full-set nil :type boolean)) (defmethod database-query-result-set ((query-expression string) @@ -114,14 +131,14 @@ :row-count nil :column-names t :query t - :result-types result-types - ) + :result-types result-types) (values (make-odbc-result-set :query query :full-set full-set :types result-types) (length column-names) nil ;; not able to return number of rows with odbc )) + #+ignore (error () (error 'clsql-sql-error :database database @@ -149,11 +166,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 @@ -161,11 +178,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 @@ -177,34 +194,170 @@ (defmethod database-list-sequences ((database odbc-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))) + ;; FIXME: Underlying database backend stuff should come from that backend + + (case (database-odbc-db-type database) + (:mysql + (mapcan #'(lambda (s) + (let ((sn (%table-name-to-sequence-name (car s)))) + (and sn (list sn)))) + (database-query "SHOW TABLES" database nil nil))) + ((:postgresql :postgresql-socket) + (mapcar #'(lambda (s) (%table-name-to-sequence-name (car s))) + (database-query "SELECT RELNAME FROM pg_class WHERE RELNAME LIKE '%clsql_seq%'" + database nil nil))))) + +(defmethod database-list-tables ((database odbc-database) + &key (owner nil)) + (declare (ignore owner)) + (multiple-value-bind (rows col-names) + (odbc-dbi:list-all-database-tables :db (database-odbc-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 odbc-database) + &key (owner nil)) + (declare (ignore owner)) + (multiple-value-bind (rows col-names) + (odbc-dbi:list-all-database-tables :db (database-odbc-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 odbc-database) + &key (owner nil)) + (declare (ignore owner)) + (multiple-value-bind (rows col-names) + (odbc-dbi:list-all-table-columns table :db (database-odbc-conn database)) + (declare (ignore col-names)) + ;; COLUMN_NAME is hard-coded by odbc spec as fourth position + (loop for row in rows + collect (fourth row)))) + +(defmethod database-attribute-type ((attribute string) (table string) (database odbc-database) + &key (owner nil)) + (declare (ignore owner)) + (multiple-value-bind (rows col-names) + (odbc-dbi:list-all-table-columns table :db (database-odbc-conn database)) + (declare (ignore col-names)) + ;; COLUMN_NAME is hard-coded by odbc spec as fourth position + ;; TYPE_NAME is the sixth column + ;; PRECISION/COLUMN_SIZE is the seventh column + ;; SCALE/DECIMAL_DIGITS is the ninth column + ;; NULLABLE is the eleventh column + (loop for row in rows + when (string-equal attribute (fourth row)) + do + (let ((size (seventh row)) + (precision (ninth row)) + (scale (nth 10 row))) + (return (values (ensure-keyword (sixth row)) + (when size (parse-integer size)) + (when precision (parse-integer precision)) + (when scale (parse-integer scale)))))))) (defmethod database-set-sequence-position (sequence-name (position integer) (database odbc-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 odbc-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 odbc-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 :odbc))) + (declare (ignore connection-spec)) (warn "Not implemented.")) (defmethod database-destroy (connection-spec (type (eql :odbc))) + (declare (ignore connection-spec)) (warn "Not implemented.")) (defmethod database-probe (connection-spec (type (eql :odbc))) - (warn "Not implemented.")) + (when (find (car connection-spec) (database-list connection-spec type) + :test #'string-equal) + t)) + +(defmethod database-list (connection-spec (type (eql :odbc))) + (declare (ignore connection-spec)) + (odbc-dbi:list-all-data-sources)) + +(defmethod database-list-indexes ((database odbc-database) + &key (owner nil)) + (let ((result '())) + (dolist (table (database-list-tables database :owner owner) result) + (setq result + (append (database-list-table-indexes table database :owner owner) + result))))) + +(defmethod database-list-table-indexes (table (database odbc-database) + &key (owner nil)) + (declare (ignore owner)) + (odbc-list-table-indexes table database)) + +(defun odbc-list-table-indexes (table database) + (multiple-value-bind (rows col-names) + (odbc-dbi:list-table-indexes + table + :db (database-odbc-conn database)) + (declare (ignore col-names)) + ;; INDEX_NAME is hard-coded in sixth position by ODBC driver + ;; FIXME: ??? is hard-coded in the fourth position + (do ((results nil) + (loop-rows rows (cdr loop-rows))) + ((null loop-rows) (nreverse results)) + (let* ((row (car loop-rows)) + (col (nth 5 row))) + (unless (find col results :test #'string-equal) + (push col results)))))) + +;;; Database capabilities + +(defmethod db-backend-has-create/destroy-db? ((db-type (eql :odbc))) + nil) + + +(defmethod database-initialize-database-type ((database-type (eql :odbc))) + ;; nothing to do + t) -#+ignore -(when (clsql-base-sys:database-type-library-loaded :odbc) - (clsql-base-sys:initialize-database-type :database-type :odbc)) +(when (clsql-sys:database-type-library-loaded :odbc) + (clsql-sys:initialize-database-type :database-type :odbc))