X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=sql%2Fexpressions.lisp;h=aa9edcab98143431b00e3213906ea1fd01bbb605;hb=5282676789105fe52990b29ec991209dcfa84aa6;hp=93c97d94d8731924210e6c761b482024c859592e;hpb=ab37892bfa71e0d66021cc73f28cd189be30c81c;p=clsql.git diff --git a/sql/expressions.lisp b/sql/expressions.lisp index 93c97d9..aa9edca 100644 --- a/sql/expressions.lisp +++ b/sql/expressions.lisp @@ -110,13 +110,12 @@ `(make-instance 'sql-ident :name ',name))) (defmethod output-sql ((expr sql-ident) database) + (with-slots (name) expr (write-string - (convert-to-db-default-case - (etypecase name - (string name) - (symbol (symbol-name name))) - database) + (etypecase name + (string name) + (symbol (symbol-name name) database)) *sql-stream*)) t) @@ -137,9 +136,8 @@ (defmethod collect-table-refs ((sql sql-ident-attribute)) (let ((qual (slot-value sql 'qualifier))) - (if (and qual (symbolp (slot-value sql 'qualifier))) - (list (make-instance 'sql-ident-table :name - (slot-value sql 'qualifier)))))) + (when qual + (list (make-instance 'sql-ident-table :name qual))))) (defmethod make-load-form ((sql sql-ident-attribute) &optional environment) (declare (ignore environment)) @@ -150,33 +148,32 @@ :type ',type))) (defmethod output-sql ((expr sql-ident-attribute) database) - (with-slots (qualifier name type) expr - (if (and (not qualifier) (not type)) - (etypecase name - ;; Honor care of name - (string - (write-string name *sql-stream*)) - (symbol - (write-string (sql-escape (convert-to-db-default-case - (symbol-name name) database)) *sql-stream*))) - - ;;; KMR: The TYPE field is used by CommonSQL for type conversion -- it - ;;; should not be output in SQL statements - #+ignore - (format *sql-stream* "~@[~A.~]~A~@[ ~A~]" - (when qualifier - (convert-to-db-default-case (sql-escape qualifier) database)) - (sql-escape (convert-to-db-default-case name database)) - (when type - (convert-to-db-default-case (symbol-name type) database))) - (format *sql-stream* "~@[~A.~]~A" - (when qualifier - (typecase qualifier - (string (format nil "~s" qualifier)) - (t (convert-to-db-default-case (sql-escape qualifier) - database)))) - (sql-escape (convert-to-db-default-case name database)))) - t)) +;;; KMR: The TYPE field is used by CommonSQL for type conversion -- it +;;; should not be output in SQL statements + (let ((*print-pretty* nil)) + (labels ((quoted-string-p (inp) + (and (char-equal #\" (elt inp 0)) + (char-equal #\" (elt inp (1- (length inp)))))) + (safety-first (inp) + "do our best not to output sql that we can guarantee is invalid. + if the ident has a space or quote in it, instead output a quoted + identifier containing those chars" + (when (and (not (quoted-string-p inp)) + (find-if + (lambda (x) (member x '(#\space #\' #\") + :test #'char-equal)) inp)) + (setf inp (format nil "~s" (substitute "\\\"" "\"" inp :test #'string-equal)))) + inp)) + (with-slots (qualifier name type) expr + (format *sql-stream* "~@[~a.~]~a" + (typecase qualifier + (null nil) ; nil is a symbol + (string (format nil "~s" qualifier)) + (symbol (safety-first (sql-escape qualifier)))) + (typecase name ;; could never get this to be nil without getting another error first + (string (format nil "~s" name)) + (symbol (safety-first (sql-escape name))))) + t)))) (defmethod output-sql-hash-key ((expr sql-ident-attribute) database) (with-slots (qualifier name type) @@ -199,19 +196,13 @@ (defmethod output-sql ((expr sql-ident-table) database) (with-slots (name alias) expr - (let ((namestr (if (symbolp name) - (symbol-name name) - name))) - (if (null alias) - (write-string - (sql-escape (convert-to-db-default-case namestr database)) - *sql-stream*) - (progn - (write-string - (sql-escape (convert-to-db-default-case namestr database)) - *sql-stream*) - (write-char #\Space *sql-stream*) - (format *sql-stream* "~s" alias))))) + (etypecase name + (string + (format *sql-stream* "~s" (sql-escape name))) + (symbol + (write-string (sql-escape name) *sql-stream*))) + (when alias + (format *sql-stream* " ~s" alias))) t) (defmethod output-sql-hash-key ((expr sql-ident-table) database) @@ -266,6 +257,28 @@ (write-char #\) *sql-stream*))) t) +(defclass sql-array-exp (sql-relational-exp) + () + (:documentation "An SQL relational expression.")) + +(defmethod output-sql ((expr sql-array-exp) database) + (with-slots (operator sub-expressions) + expr + (let ((subs (if (consp (car sub-expressions)) + (car sub-expressions) + sub-expressions))) + (write-char #\( *sql-stream*) + (output-sql operator database) + (write-char #\[ *sql-stream*) + (do ((sub subs (cdr sub))) + ((null (cdr sub)) (output-sql (car sub) database)) + (output-sql (car sub) database) + (write-char #\, *sql-stream*) + (write-char #\Space *sql-stream*)) + (write-char #\] *sql-stream*) + (write-char #\) *sql-stream*))) + t) + (defclass sql-upcase-like (sql-relational-exp) () (:documentation "An SQL 'like' that upcases its arguments.")) @@ -412,9 +425,9 @@ (output-sql (car components) database) (when components (mapc #'(lambda (comp) - (write-string ", " *sql-stream*) - (output-sql comp database)) - (cdr components)))) + (write-string ", " *sql-stream*) + (output-sql comp database)) + (cdr components)))) t) (defclass sql-set-exp (%sql-expression) @@ -544,26 +557,26 @@ uninclusive, and the args from that keyword to the end." (find-class arg nil))) target-args)))) (multiple-value-bind (selections arglist) - (query-get-selections args) + (query-get-selections args) (if (select-objects selections) - (destructuring-bind (&key flatp refresh &allow-other-keys) arglist - (make-instance 'sql-object-query :objects selections - :flatp flatp :refresh refresh - :exp arglist)) - (destructuring-bind (&key all flatp set-operation distinct from where - group-by having order-by - offset limit inner-join on &allow-other-keys) - arglist - (if (null selections) - (error "No target columns supplied to select statement.")) - (if (null from) - (error "No source tables supplied to select statement.")) - (make-instance 'sql-query :selections selections - :all all :flatp flatp :set-operation set-operation - :distinct distinct :from from :where where - :limit limit :offset offset - :group-by group-by :having having :order-by order-by - :inner-join inner-join :on on)))))) + (destructuring-bind (&key flatp refresh &allow-other-keys) arglist + (make-instance 'sql-object-query :objects selections + :flatp flatp :refresh refresh + :exp arglist)) + (destructuring-bind (&key all flatp set-operation distinct from where + group-by having order-by + offset limit inner-join on &allow-other-keys) + arglist + (if (null selections) + (error "No target columns supplied to select statement.")) + (if (null from) + (error "No source tables supplied to select statement.")) + (make-instance 'sql-query :selections selections + :all all :flatp flatp :set-operation set-operation + :distinct distinct :from from :where where + :limit limit :offset offset + :group-by group-by :having having :order-by order-by + :inner-join inner-join :on on)))))) (defmethod output-sql ((query sql-query) database) (with-slots (distinct selections from where group-by having order-by @@ -574,13 +587,17 @@ uninclusive, and the args from that keyword to the end." (write-string "SELECT " *sql-stream*) (when all (write-string "ALL " *sql-stream*)) + (when (and limit (eq :odbc (database-type database))) + (write-string " TOP " *sql-stream*) + (output-sql limit database)) (when (and distinct (not all)) (write-string "DISTINCT " *sql-stream*) (unless (eql t distinct) (write-string "ON " *sql-stream*) (output-sql distinct database) (write-char #\Space *sql-stream*))) - (output-sql (apply #'vector selections) database) + (let ((*in-subselect* t)) + (output-sql (apply #'vector selections) database)) (when from (write-string " FROM " *sql-stream*) (flet ((ident-table-equal (a b) @@ -596,7 +613,7 @@ uninclusive, and the args from that keyword to the end." (remove-duplicates from :test #'ident-table-equal)) database)) - (string (write-string from *sql-stream*)) + (string (format *sql-stream* "~s" (sql-escape from))) (t (let ((*in-subselect* t)) (output-sql from database)))))) (when inner-join @@ -642,7 +659,7 @@ uninclusive, and the args from that keyword to the end." (when (cdr order) (write-char #\, *sql-stream*)))) (output-sql order-by database))) - (when limit + (when (and limit (not (eq :odbc (database-type database)))) (write-string " LIMIT " *sql-stream*) (output-sql limit database)) (when offset @@ -687,7 +704,7 @@ uninclusive, and the args from that keyword to the end." (write-string "INSERT INTO " *sql-stream*) (output-sql (typecase into - (string (sql-expression :attribute into)) + (string (sql-expression :table into)) (t into)) database) (when attributes @@ -795,8 +812,8 @@ uninclusive, and the args from that keyword to the end." (write-char #\Space *sql-stream*) (write-string (if (stringp db-type) db-type ; override definition - (database-get-type-specifier (car type) (cdr type) database - (database-underlying-type database))) + (database-get-type-specifier (car type) (cdr type) database + (database-underlying-type database))) *sql-stream*) (let ((constraints (database-constraint-statement (if (and db-type (symbolp db-type)) @@ -809,7 +826,10 @@ uninclusive, and the args from that keyword to the end." (with-slots (name columns modifiers transactions) stmt (write-string "CREATE TABLE " *sql-stream*) - (output-sql name database) + (etypecase name + (string (format *sql-stream* "~s" (sql-escape name))) + (symbol (write-string (sql-escape name) *sql-stream*)) + (sql-ident (output-sql name database))) (write-string " (" *sql-stream*) (do ((column columns (cdr column))) ((null (cdr column)) @@ -823,9 +843,9 @@ uninclusive, and the args from that keyword to the end." (write-string (car modifier) *sql-stream*))) (write-char #\) *sql-stream*) (when (and (eq :mysql (database-underlying-type database)) - transactions - (db-type-transaction-capable? :mysql database)) - (write-string " Type=InnoDB" *sql-stream*)))) + transactions + (db-type-transaction-capable? :mysql database)) + (write-string " Type=InnoDB" *sql-stream*)))) t) @@ -855,7 +875,7 @@ uninclusive, and the args from that keyword to the end." (defmethod database-output-sql ((str string) database) (declare (optimize (speed 3) (safety 1) - #+cmu (extensions:inhibit-warnings 3))) + #+cmu (extensions:inhibit-warnings 3))) (let ((len (length str))) (declare (type fixnum len)) (cond ((zerop len) @@ -865,13 +885,13 @@ uninclusive, and the args from that keyword to the end." (concatenate 'string "'" str "'")) (t (let ((buf (make-string (+ (* len 2) 2) :initial-element #\'))) - (declare (simple-string buf)) - (do* ((i 0 (incf i)) + (declare (simple-string buf)) + (do* ((i 0 (incf i)) (j 1 (incf j))) ((= i len) (subseq buf 0 (1+ j))) (declare (type fixnum i j)) (let ((char (aref str i))) - (declare (character char)) + (declare (character char)) (cond ((char= char #\') (setf (aref buf j) #\') (incf j) @@ -891,11 +911,9 @@ uninclusive, and the args from that keyword to the end." (defmethod database-output-sql ((sym symbol) database) (if (null sym) +null-string+ - (convert-to-db-default-case - (if (equal (symbol-package sym) keyword-package) - (concatenate 'string "'" (string sym) "'") - (symbol-name sym)) - database)))) + (if (equal (symbol-package sym) keyword-package) + (concatenate 'string "'" (string sym) "'") + (symbol-name sym))))) (defmethod database-output-sql ((tee (eql t)) database) (if database @@ -923,8 +941,8 @@ uninclusive, and the args from that keyword to the end." (defmethod database-output-sql ((arg vector) database) (format nil "~{~A~^,~}" (map 'list #'(lambda (val) - (sql-output val database)) - arg))) + (sql-output val database)) + arg))) (defmethod output-sql-hash-key ((arg vector) database) (list 'vector (map 'list (lambda (arg) @@ -950,13 +968,13 @@ uninclusive, and the args from that keyword to the end." (defmethod database-output-sql (thing database) (if (or (null thing) - (eq 'null thing)) + (eq 'null thing)) +null-string+ (error 'sql-user-error :message - (format nil - "No type conversion to SQL for ~A is defined for DB ~A." - (type-of thing) (type-of database))))) + (format nil + "No type conversion to SQL for ~A is defined for DB ~A." + (type-of thing) (type-of database))))) ;; @@ -974,7 +992,9 @@ uninclusive, and the args from that keyword to the end." (cons (symbol-name-default-case "UNSIGNED") "UNSIGNED") (cons (symbol-name-default-case "ZEROFILL") "ZEROFILL") (cons (symbol-name-default-case "AUTO-INCREMENT") "AUTO_INCREMENT") - (cons (symbol-name-default-case "UNIQUE") "UNIQUE"))) + (cons (symbol-name-default-case "UNIQUE") "UNIQUE") + (cons (symbol-name-default-case "IDENTITY") "IDENTITY (1,1)") ;Added Identity for MS-SQLServer support + )) (defmethod database-constraint-statement (constraint-list database) (declare (ignore database)) @@ -991,7 +1011,7 @@ uninclusive, and the args from that keyword to the end." (if (null output) (error 'sql-user-error :message (format nil "unsupported column constraint '~A'" - constraint)) + constraint)) (setq string (concatenate 'string string (cdr output)))) (if (< 1 (length constraint)) (setq string (concatenate 'string string " "))))))))