X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=db-odbc%2Fodbc-sql.lisp;h=43bcfbb7b64b43453997f414b1b7a522e61dd554;hb=a3974aaf6e6e53354b712bfe5db3b5b5db49c010;hp=30308e5024c078fd183af05fe89d461e70260f67;hpb=4eec15b82de673c5676f5f817f9dba0b939606cf;p=clsql.git diff --git a/db-odbc/odbc-sql.lisp b/db-odbc/odbc-sql.lisp index 30308e5..43bcfbb 100644 --- a/db-odbc/odbc-sql.lisp +++ b/db-odbc/odbc-sql.lisp @@ -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,28 +40,57 @@ (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: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"))))) + (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"))))) +(defun store-type-of-connected-database (db) + (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 + ((or (search "postgresql" server-name :test #'char-equal) + (search "postgresql" dbms-name :test #'char-equal)) + :postgresql) + ((or (search "mysql" server-name :test #'char-equal) + (search "mysql" dbms-name :test #'char-equal)) + :mysql) + ((or (search "oracle" server-name :test #'char-equal) + (search "oracle" dbms-name :test #'char-equal)) + :oracle)))) + (setf (database-odbc-db-type db) type))) + (defmethod database-disconnect ((database odbc-database)) - (odbc:disconnect (database-odbc-conn 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) (handler-case - (odbc:sql query-expression :db (database-odbc-conn database) - :types result-types) + (odbc-dbi:sql query-expression :db (database-odbc-conn database) + :result-types result-types) + (clsql-error (e) + (error e)) + #+ignore (error () (error 'clsql-sql-error :database database @@ -71,7 +101,10 @@ (defmethod database-execute-command (sql-expression (database odbc-database)) (handler-case - (odbc: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 @@ -81,7 +114,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) @@ -89,19 +122,19 @@ &key full-set result-types) (handler-case (multiple-value-bind (query column-names) - (odbc:sql query-expression + (odbc-dbi:sql query-expression :db (database-odbc-conn database) :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 @@ -110,13 +143,13 @@ :error "Query result set failed")))) (defmethod database-dump-result-set (result-set (database odbc-database)) - (odbc:close-query (odbc-result-set-query result-set)) + (odbc-dbi:close-query (odbc-result-set-query result-set)) t) (defmethod database-store-next-row (result-set (database odbc-database) list) - (let ((row (odbc:fetch-row (odbc-result-set-query result-set) nil 'eof))) + (let ((row (odbc-dbi:fetch-row (odbc-result-set-query result-set) nil 'eof))) (if (eq row 'eof) nil (progn @@ -141,11 +174,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 @@ -161,20 +194,75 @@ (database-query "SHOW TABLES LIKE '%clsql_seq%'" database 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)) + (let ((pos (position "TABLE_NAME" col-names :test #'string-equal))) + (when pos + (loop for row in rows + collect (nth pos 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)) + (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 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)) + (let ((pos (position "TYPE_NAME" col-names :test #'string-equal))) + (when pos + (loop for row in rows + collect (nth pos row)))))) + (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)))) + (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)))) (defmethod database-create (connection-spec (type (eql :odbc))) (warn "Not implemented."))