+31 May 2004 Marcus Pearce <m.t.pearce@city.ac.uk>
+ * db-odbc/odbc-sql.lisp: DB-TYPE replaced with DATABASE-TYPE in
+ DATABASE-CONNECT.
+ * sql/operations.lisp: substr now generates SUBSTR for use on
+ Oracle; added a new operator SUBSTRING for use elsewhere. minus
+ now generates MINUS for use on Oracle. Nvl signals an error if
+ not called with exactly 2 arguments. Add concat function for use
+ instead of the || operator on MySQL and Oracle.
+ * sql/syntax.lisp: changed internal symbol for the || operator to
+ CONCAT-OP.
+ * sql/expressions.lisp: removed redundant and unused functions
+ GENERATE-SQL (cf. SQL-OUTPUT) and DATABASE-CONSTRAINT-DESCRIPTION
+ (cf. MAKE-CONSTRAINTS-DESCRIPTION).
+ * sql/generics.lisp: removed generic function for
+ DATABASE-CONSTRAINT-DESCRIPTION (see above).
+ * tests/test-syntax.lisp: modified/added tests according to changes
+ in sql/operations.lisp.
+ * tests/test-fdml.lisp: changed SUBSTR to SUBSTRING in test
+ :fdml/select/21.
+ * sql/package.lisp: added the operators SQL-SUBSTRING, SQL-CONCAT-OP
+ and SQL-USERENV to the shared exports list. Removed
+ ROLLBACK-TRANSACTION, COMMIT-TRANSACTION, DATABASE-START-TRANSACTION,
+ DATABASE-ABORT-TRANSACTION, DATABASE-COMMIT-TRANSACTION,
+ TRANSACTION-LEVEL, TRANSACTION, RECORD-SQL-COMMAND and
+ RECORD-SQL-RESULT from shared exports list.
+
30 May 2004 Kevin Rosenberg <kevin@rosenberg.net>
* db-postgresql/postgresl-sql.lisp: Avoid computing
result-types lisp when nil result-types. Return only
(store-type-of-connected-database db)
;; Ensure this database type is initialized so can check capabilities of
;; underlying database
- (initialize-database-type :database-type db-type)
+ (initialize-database-type :database-type database-type)
db)
(error () ;; Init or Connect failed
(error 'sql-connection-error
sql
`(make-instance 'sql-ident-table :name ',name :table-alias ',alias)))
-(defun generate-sql (expr database)
- (let ((*sql-stream* (make-string-output-stream)))
- (output-sql expr database)
- (get-output-stream-string *sql-stream*)))
-
(defmethod output-sql ((expr sql-ident-table) database)
(with-slots (name alias)
expr
;; Convert type spec to sql syntax
;;
-(defmethod database-constraint-description (constraint database)
- (declare (ignore database))
- (let ((output (assoc (symbol-name constraint) *constraint-types*
- :test #'equal)))
- (if (null output)
- (error 'sql-user-error
- :message (format nil "unsupported column constraint '~A'"
- constraint))
- (cdr output))))
-
(defmethod database-constraint-statement (constraint-list database)
(declare (ignore database))
(make-constraints-description constraint-list))
)
(defgeneric database-output-sql (arg database)
)
-(defgeneric database-constraint-description (constraint database)
- )
(defgeneric database-pkey-constraint (class database)
)
(defgeneric database-constraint-statement (constraints database)
(defsql sql-minus (:symbol "minus") (&rest rest)
(make-instance 'sql-set-exp
- :operator 'except :sub-expressions rest))
+ :operator 'minus :sub-expressions rest))
(defsql sql-limit (:symbol "limit") (&rest rest)
(make-instance 'sql-query-modifier-exp
(make-instance 'sql-relational-exp
:operator 'in :sub-expressions rest))
-(defsql sql-concat (:symbol "concat") (&rest rest)
+(defsql sql-concat-op (:symbol "concat-op") (&rest rest)
(make-instance 'sql-relational-exp
:operator '\|\| :sub-expressions rest))
+(defsql sql-concat (:symbol "concat") (&rest rest)
+ (make-instance 'sql-function-exp
+ :name 'concat :args rest))
+
(defsql sql-substr (:symbol "substr") (&rest rest)
(if (= (length rest) 3)
(make-instance 'sql-function-exp
- :name 'substring :args rest)
+ :name 'substr :args rest)
(error 'sql-user-error :message "SUBSTR must have 3 arguments.")))
+(defsql sql-substring (:symbol "substring") (&rest rest)
+ (if (= (length rest) 3)
+ (make-instance 'sql-function-exp
+ :name 'substring :args rest)
+ (error 'sql-user-error :message "SUBSTRING must have 3 arguments.")))
+
(defsql sql-is (:symbol "is") (&rest rest)
(make-instance 'sql-relational-exp
:operator 'is :sub-expressions rest))
:name 'coalesce :args rest))
(defsql sql-nvl (:symbol "nvl") (&rest rest)
- (make-instance 'sql-function-exp
- :name 'coalesce :args rest))
+ (if (= (length rest) 2)
+ (make-instance 'sql-function-exp
+ :name 'coalesce :args rest)
+ (error 'sql-user-error :message "NVL accepts exactly 2 arguments.")))
(defsql sql-userenv (:symbol "userenv") (&rest rest)
(make-instance 'sql-function-exp
#:sql-stream
#:start-sql-recording
#:stop-sql-recording
- ;; CLSQL Extensions
- #:record-sql-command
- #:record-sql-result
;; FDDL (fddl.lisp)
#:create-table
#:commit
#:rollback
;; CLSQL Extensions
- #:commit-transaction
- #:rollback-transaction
#:add-transaction-commit-hook
#:add-transaction-rollback-hook
#:start-transaction
#:in-transaction-p
- #:database-start-transaction
- #:database-abort-transaction
- #:database-commit-transaction
- #:transaction-level
- #:transaction
#:set-autocommit
- ;; OODDL (ooddl.lisp)
+ ;; OODDL (ooddl.lisp)
#:standard-db-object
#:def-view-class
#:create-view-from-class
#:sql-and
#:sql-or
#:sql-in
- #:sql-concat
#:sql-substr
+ #:sql-concat-op
#:sql-=
#:sql-<
#:sql->
#:sql-distinct
#:sql-nvl
#:sql-slot-value
+ #:sql-userenv
;; CLSQL Extensions
+ #:sql-concat
+ #:sql-substring
#:sql-limit
#:sql-group-by
#:sql-having
(declare (ignore char))
(let ((sqllist (read-delimited-list #\] stream t)))
(cond ((string= (write-to-string (car sqllist)) "||")
- (cons (sql-operator 'concat) (cdr sqllist)))
+ (cons (sql-operator 'concat-op) (cdr sqllist)))
((and (= (length sqllist) 1) (eql (car sqllist) '*))
(apply #'generate-sql-reference sqllist))
((sql-operator (car sqllist))
("1" "2" "3" "4"))
(deftest :fdml/select/21
- (clsql:select [substr [first-name] 1 4] :from [employee]
+ (clsql:select [substring [first-name] 1 4] :from [employee]
:flatp t :order-by [emplid] :field-names nil)
("Vlad" "Jose" "Leon" "Niki" "Leon" "Yuri" "Kons" "Mikh" "Bori" "Vlad"))
(deftest :syntax/substr/1
(clsql:sql [substr [hello] 1 4])
+ "SUBSTR(HELLO,1,4)")
+
+(deftest :syntax/substring/1
+ (clsql:sql [substring [hello] 1 4])
"SUBSTRING(HELLO,1,4)")
(clsql:sql [|| [foo] [bar] [baz]])
"(FOO || BAR || BAZ)")
+(deftest :syntax/concat/2
+ (clsql:sql [concat [foo] [bar]])
+ "CONCAT(FOO,BAR)")
+
(deftest :syntax/pattern/1
(clsql:sql [like [foo] "%v"])
(clsql:sql [nvl [foo] "not specified"])
"COALESCE(FOO,'not specified')")
+(deftest :syntax/nvl/1
+ (clsql:sql [nvl [foo] "not specified"])
+ "COALESCE(FOO,'not specified')")
+
+
(deftest :syntax/sets/1
(clsql:sql [union [select [foo] :from [bar]] [select [baz] :from [bar]]])
(deftest :syntax/sets/4
(clsql:sql [minus [select [foo] :from [bar]] [select [baz] :from [bar]]])
- "SELECT FOO FROM BAR EXCEPT SELECT BAZ FROM BAR")
+ "SELECT FOO FROM BAR MINUS SELECT BAZ FROM BAR")
(deftest :syntax/function/1