X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=db-mysql%2Fmysql-sql.lisp;h=55918a935600d81aa07c738f2b026f948c04582f;hb=fa32c4233b4a02ae631602dbb0a234ab10df8aaf;hp=eaea5d2aa8f004be9861ef6a714b404d0221ed78;hpb=81d2ebcdd182dd674191b29e00274c82660fd3f3;p=clsql.git diff --git a/db-mysql/mysql-sql.lisp b/db-mysql/mysql-sql.lisp index eaea5d2..55918a9 100644 --- a/db-mysql/mysql-sql.lisp +++ b/db-mysql/mysql-sql.lisp @@ -2,37 +2,17 @@ ;;;; ************************************************************************* ;;;; FILE IDENTIFICATION ;;;; -;;;; Name: mysql-sql.cl +;;;; Name: mysql-sql.lisp ;;;; Purpose: High-level MySQL interface using UFFI -;;;; Programmers: Kevin M. Rosenberg based on -;;;; Original code by Pierre R. Mai ;;;; Date Started: Feb 2002 ;;;; -;;;; $Id: mysql-sql.lisp,v 1.6 2003/06/23 19:25:30 kevin Exp $ -;;;; -;;;; This file, part of CLSQL, is Copyright (c) 2002 by Kevin M. Rosenberg -;;;; and Copyright (c) 1999-2001 by Pierre R. Mai +;;;; $Id$ ;;;; ;;;; CLSQL users are granted the rights to distribute and use this software ;;;; as governed by the terms of the Lisp Lesser GNU Public License ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL. ;;;; ************************************************************************* -(eval-when (:compile-toplevel) - (declaim (optimize (debug 3) (speed 3) (safety 1) (compilation-speed 0)))) - -;;;; Modified by Kevin Rosenberg, Feb 20002 -;;;; -- Added support for Allegro CL and Lispworks using UFFI layer -;;;; -- Changed database-connect to use mysql-real-connect. This way, -;;;; can avoid using double (unwind-protect) -;;;; -- Changed database-connect to have MySQL library allocate space -;;;; for MYSQL structure. This will make the code more robust in -;;;; the event that MySQL library changes the size of the mysql-mysql -;;;; structure. -;;;; -;;;; Mar 2002 -;;;; Added field types - (defpackage #:clsql-mysql (:use #:common-lisp #:clsql-base-sys #:mysql #:clsql-uffi) (:export #:mysql-database) @@ -43,6 +23,7 @@ ;;; Field conversion functions (defun make-type-list-for-auto (num-fields res-ptr) + (declare (fixnum num-fields)) (let ((new-types '()) #+ignore (field-vec (mysql-fetch-fields res-ptr))) (dotimes (i num-fields) @@ -69,16 +50,15 @@ (nreverse new-types))) (defun canonicalize-types (types num-fields res-ptr) - (if (null types) - nil - (let ((auto-list (make-type-list-for-auto num-fields res-ptr))) - (cond - ((listp types) - (canonicalize-type-list types auto-list)) - ((eq types :auto) - auto-list) - (t - nil))))) + (when types + (let ((auto-list (make-type-list-for-auto num-fields res-ptr))) + (cond + ((listp types) + (canonicalize-type-list types auto-list)) + ((eq types :auto) + auto-list) + (t + nil))))) (defmethod database-initialize-database-type ((database-type (eql :mysql))) t) @@ -98,7 +78,9 @@ (check-connection-spec connection-spec database-type (host db user password)) (destructuring-bind (host db user password) connection-spec (declare (ignore password)) - (concatenate 'string host "/" db "/" user))) + (concatenate 'string + (if host host "localhost") + "/" db "/" user))) (defmethod database-connect (connection-spec (database-type (eql :mysql))) (check-connection-spec connection-spec database-type (host db user password)) @@ -144,20 +126,22 @@ (defmethod database-query (query-expression (database mysql-database) - types) + result-types) (declare (optimize (speed 3) (safety 0) (debug 0) (space 0))) (let ((mysql-ptr (database-mysql-ptr database))) (uffi:with-cstring (query-native query-expression) - (if (zerop (mysql-query mysql-ptr query-native)) + (if (zerop (mysql-real-query mysql-ptr query-native + (length query-expression))) (let ((res-ptr (mysql-use-result mysql-ptr))) (if res-ptr (unwind-protect (let ((num-fields (mysql-num-fields res-ptr))) (declare (fixnum num-fields)) - (setq types (canonicalize-types - types num-fields + (setq result-types (canonicalize-types + result-types num-fields res-ptr)) (loop for row = (mysql-fetch-row res-ptr) + for lengths = (mysql-fetch-lengths res-ptr) until (uffi:null-pointer-p row) collect (do* ((rlist (make-list num-fields)) @@ -170,7 +154,9 @@ (uffi:deref-array row '(:array (* :unsigned-char)) i) - types i))))) + result-types i + (uffi:deref-array lengths '(:array :unsigned-long) + i)))))) (mysql-free-result res-ptr)) (error 'clsql-sql-error :database database @@ -185,7 +171,7 @@ #+ignore (defmethod database-query (query-expression (database mysql-database) - types) + result-types) (declare (optimize (speed 3) (safety 0) (debug 0) (space 0))) (let ((mysql-ptr (database-mysql-ptr database))) (uffi:with-cstring (query-native query-expression) @@ -195,8 +181,8 @@ (unwind-protect (let ((num-fields (mysql-num-fields res-ptr))) (declare (fixnum num-fields)) - (setq types (canonicalize-types - types num-fields + (setq result-types (canonicalize-types + result-types num-fields res-ptr)) (loop for row = (mysql-fetch-row res-ptr) until (uffi:null-pointer-p row) @@ -207,7 +193,7 @@ (uffi:deref-array row '(:array (* :unsigned-char)) i) - types i)))) + result-types i)))) (mysql-free-result res-ptr)) (error 'clsql-sql-error :database database @@ -224,7 +210,8 @@ (uffi:with-cstring (sql-native sql-expression) (let ((mysql-ptr (database-mysql-ptr database))) (declare (type mysql-mysql-ptr-def mysql-ptr)) - (if (zerop (mysql-query mysql-ptr sql-native)) + (if (zerop (mysql-real-query mysql-ptr sql-native + (length sql-expression))) t (error 'clsql-sql-error :database database @@ -240,13 +227,14 @@ (full-set nil :type boolean)) -(defmethod database-query-result-set (query-expression +(defmethod database-query-result-set ((query-expression string) (database mysql-database) - &key full-set types) + &key full-set result-types) (uffi:with-cstring (query-native query-expression) (let ((mysql-ptr (database-mysql-ptr database))) (declare (type mysql-mysql-ptr-def mysql-ptr)) - (if (zerop (mysql-query mysql-ptr query-native)) + (if (zerop (mysql-real-query mysql-ptr query-native + (length query-expression))) (let ((res-ptr (if full-set (mysql-store-result mysql-ptr) (mysql-use-result mysql-ptr)))) @@ -259,7 +247,7 @@ :full-set full-set :types (canonicalize-types - types num-fields + result-types num-fields res-ptr)))) (if full-set (values result-set @@ -286,6 +274,7 @@ (defmethod database-store-next-row (result-set (database mysql-database) list) (let* ((res-ptr (mysql-result-set-res-ptr result-set)) (row (mysql-fetch-row res-ptr)) + (lengths (mysql-fetch-lengths res-ptr)) (types (mysql-result-set-types result-set))) (declare (type mysql-mysql-res-ptr-def res-ptr) (type mysql-row-def row)) @@ -297,10 +286,158 @@ (convert-raw-field (uffi:deref-array row '(:array (* :unsigned-char)) i) types - i))) + i + (uffi:deref-array lengths '(:array :unsigned-long) i)))) list))) +;; Table and attribute introspection + +(defmethod database-list-tables ((database mysql-database) &key (owner nil)) + (declare (ignore owner)) + (remove-if #'(lambda (s) + (and (>= (length s) 11) + (string= (subseq s 0 11) "_clsql_seq_"))) + (mapcar #'car (database-query "SHOW TABLES" database nil)))) + +;; MySQL 4.1 does not support views +(defmethod database-list-views ((database mysql-database) + &key (owner nil)) + (declare (ignore owner)) + nil) + +(defmethod database-list-indexes ((database mysql-database) + &key (owner nil)) + (let ((result '())) + (dolist (table (database-list-tables database :owner owner) result) + (mapc #'(lambda (index) (push (nth 2 index) result)) + (database-query + (format nil "SHOW INDEX FROM ~A" (string-upcase table)) + database nil))))) + +(defmethod database-list-attributes ((table string) (database mysql-database) + &key (owner nil)) + (declare (ignore owner)) + (mapcar #'car + (database-query + (format nil "SHOW COLUMNS FROM ~A" table) + database nil))) + +(defmethod database-attribute-type (attribute (table string) + (database mysql-database) + &key (owner nil)) + (declare (ignore owner)) + (let ((result + (mapcar #'cadr + (database-query + (format nil + "SHOW COLUMNS FROM ~A LIKE '~A'" table attribute) + database nil)))) + (let* ((str (car result)) + (end-str (position #\( str)) + (substr (subseq str 0 end-str))) + (if substr + (intern (string-upcase substr) :keyword) nil)))) + +;;; Sequence functions + +(defun %sequence-name-to-table (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_") + (subseq table-name 11))) + +(defmethod database-create-sequence (sequence-name + (database mysql-database)) + (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)") + database) + (database-execute-command + (concatenate 'string "INSERT INTO " table-name + " VALUES (-1)") + database))) + +(defmethod database-drop-sequence (sequence-name + (database mysql-database)) + (database-execute-command + (concatenate 'string "DROP TABLE " (%sequence-name-to-table sequence-name)) + database)) + +(defmethod database-list-sequences ((database mysql-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))) + +(defmethod database-set-sequence-position (sequence-name + (position integer) + (database mysql-database)) + (database-execute-command + (format nil "UPDATE ~A SET id=~A" (%sequence-name-to-table sequence-name) + position) + database) + (mysql:mysql-insert-id (clsql-mysql::database-mysql-ptr database))) + +(defmethod database-sequence-next (sequence-name (database mysql-database)) + (database-execute-command + (concatenate 'string "UPDATE " (%sequence-name-to-table sequence-name) + " SET id=LAST_INSERT_ID(id+1)") + database) + (mysql:mysql-insert-id (clsql-mysql::database-mysql-ptr database))) + +(defmethod database-sequence-last (sequence-name (database mysql-database)) + (declare (ignore sequence-name))) + + + +(defmethod database-create (connection-spec (type (eql :mysql))) + (destructuring-bind (host name user password) connection-spec + (multiple-value-bind (output status) + (clsql-base-sys:command-output "mysqladmin create -u~A -p~A -h~A ~A" + user password + (if host host "localhost") + name) + (if (or (not (eql 0 status)) + (and (search "failed" output) (search "error" output))) + (error 'clsql-access-error + :connection-spec connection-spec + :database-type type + :error + (format nil "database-create failed: ~A" output)) + t)))) + +(defmethod database-destory (connection-spec (type (eql :mysql))) + (destructuring-bind (host name user password) connection-spec + (multiple-value-bind (output status) + (clsql-base-sys:command-output "mysqladmin drop -u~A -p~A -h~A ~A" + user password + (if host host "localhost") + name) + (if (or (not (eql 0 status)) + (and (search "failed" output) (search "error" output))) + (error 'clsql-access-error + :connection-spec connection-spec + :database-type type + :error + (format nil "database-destroy failed: ~A" output)) + t)))) + +(defmethod database-probe (connection-spec (type (eql :mysql))) + (destructuring-bind (host name user password) connection-spec + (let ((database (database-connect (list host "mysql" user password) type))) + (unwind-protect + (when + (find name (database-query "select db from db" + database :auto) + :key #'car :test #'string-equal) + t) + (database-disconnect database))))) + + (when (clsql-base-sys:database-type-library-loaded :mysql) - (clsql-base-sys:initialize-database-type :database-type :mysql) - (pushnew :mysql cl:*features*)) + (clsql-base-sys:initialize-database-type :database-type :mysql))