X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=tests%2Ftest-fddl.lisp;h=83eada9f1074e699b6e584d90360be9e884be957;hp=067088c486894910e1f71f0f8d10de70ae3ec161;hb=151c009059521769a44ec35dfdceb86d5373af99;hpb=704367c500a9fac40e97663f794fa4042df1512a diff --git a/tests/test-fddl.lisp b/tests/test-fddl.lisp index 067088c..83eada9 100644 --- a/tests/test-fddl.lisp +++ b/tests/test-fddl.lisp @@ -1,23 +1,22 @@ ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- -;;;; ====================================================================== -;;;; File: test-fddl.lisp -;;;; Authors: Marcus Pearce and Kevin Rosenberg -;;;; Created: 30/03/2004 -;;;; Updated: $Id$ +;;;; ************************************************************************* +;;;; FILE IDENTIFICATION ;;;; -;;;; Tests for the CLSQL Functional Data Definition Language -;;;; (FDDL). +;;;; Name: test-fddl.lisp +;;;; Purpose: Tests for the CLSQL Functional Data Definition Language +;;;; Authors: Marcus Pearce and Kevin M. Rosenberg +;;;; Created: March 2004 ;;;; ;;;; This file is part of CLSQL. ;;;; ;;;; 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. -;;;; ====================================================================== +;;;; ************************************************************************* -(in-package #:clsql-tests) -#.(clsql:locally-enable-sql-reader-syntax) +(in-package #:clsql-tests) +(clsql-sys:file-enable-sql-reader-syntax) (def-dataset *ds-fddl* (:setup (lambda () @@ -33,6 +32,12 @@ "3,'matey','1992-02-29',0.0") (:cleanup "DROP TABLE ALPHA" "DROP TABLE BRAVO")) +(def-dataset *ds-fddl-parsing-oddities* + (:setup "CREATE TABLE ATEST ( +A varchar (32), +B varchar(32))") + (:cleanup "DROP TABLE ATEST")) + (setq *rt-fddl* '( @@ -40,7 +45,7 @@ (deftest :fddl/table/1 (with-dataset *ds-fddl* (sort (mapcar #'string-downcase - (clsql:list-tables :owner *test-database-user*)) + (clsql:list-tables )) #'string<)) ("alpha" "bravo")) @@ -52,10 +57,10 @@ ([name] (string 24)) ([comments] longchar))) (values - (clsql:table-exists-p [foo] :owner *test-database-user*) + (clsql:table-exists-p [foo]) (progn (clsql:drop-table [foo] :if-does-not-exist :ignore) - (clsql:table-exists-p [foo] :owner *test-database-user*)))) + (clsql:table-exists-p [foo])))) t nil) ;; create a table, list its attributes and drop it @@ -140,7 +145,7 @@ (with-dataset *ds-fddl* (sort (mapcar #'string-downcase - (clsql:list-attributes [alpha] :owner *test-database-user*)) + (clsql:list-attributes [alpha] )) #'string<))) "a" "c" "d" "f") @@ -149,8 +154,7 @@ (apply #'values (sort (mapcar #'(lambda (a) (string-downcase (car a))) - (clsql:list-attribute-types [alpha] - :owner *test-database-user*)) + (clsql:list-attribute-types [alpha])) #'string<))) "a" "c" "d" "f") @@ -164,12 +168,14 @@ (with-dataset *ds-fddl* (multiple-value-bind (type length scale nullable) (clsql:attribute-type [c] [alpha]) - (values (clsql-sys:in type :varchar :varchar2) length scale nullable))) + (values (clsql-sys:in type :varchar :varchar2 :nvarchar) + length scale nullable))) t 30 nil 1) (deftest :fddl/attributes/5 (with-dataset *ds-fddl* - (and (member (clsql:attribute-type [d] [alpha]) '(:datetime :timestamp :date)) t)) + (and (member (clsql:attribute-type [d] [alpha]) + '(:datetime :timestamp :date :smalldatetime)) t)) t) (deftest :fddl/attributes/6 @@ -182,6 +188,14 @@ (and (member (clsql:attribute-type [t_bigint] [TYPE_BIGINT]) '(:bigint :int8)) t)) t) +(deftest :fddl/attributes/8 + ;;this is mostly from sqlite3 sending back + (with-dataset *ds-fddl-parsing-oddities* + (values + (clsql-sys:in (clsql:attribute-type [a] [atest]) :varchar :varchar2) + (clsql-sys:in (clsql:attribute-type [b] [atest]) :varchar :varchar2))) + t t) + ;; create a view, test for existence, drop it and test again (deftest :fddl/view/1 @@ -223,7 +237,7 @@ (let ((result (list ;; Shouldn't exist - (clsql:select [a] [c] + (clsql:select [a] [c] :from [v1] :where [= [a] -1]) ;; Should exist @@ -250,11 +264,11 @@ (clsql:list-attributes [v1])) #'string<) ;; Shouldn't exist - (clsql:select [x] [y] + (clsql:select [x] [y] :from [v1] :where [= [x] -1]) ;; Should exist - (car (clsql:select [x] [y] + (car (clsql:select [x] [y] :from [v1] :where [= [x] 1]))))) @@ -268,11 +282,11 @@ (progn (clsql:create-index [bar] :on [alpha] :attributes '([a] [c]) :unique t) (values - (clsql:index-exists-p [bar] :owner *test-database-user*) + (clsql:index-exists-p [bar] ) (progn (clsql:drop-index [bar] :on [alpha] :if-does-not-exist :ignore) - (clsql:index-exists-p [bar] :owner *test-database-user*))))) + (clsql:index-exists-p [bar]))))) t nil) ;; create indexes with names as strings, symbols and in square brackets @@ -282,7 +296,7 @@ (result '())) (dolist (name names) (clsql:create-index name :on [alpha] :attributes '([a])) - (push (clsql:index-exists-p name :owner *test-database-user*) result) + (push (clsql:index-exists-p name ) result) (clsql:drop-index name :on [alpha] :if-does-not-exist :ignore)) (apply #'values result))) t t t) @@ -303,7 +317,7 @@ (sort (mapcar #'string-downcase - (clsql:list-indexes :on [i3test] :owner *test-database-user*)) + (clsql:list-indexes :on [i3test])) #'string-lessp) (progn (clsql:drop-index [bar] :on [i3test]) @@ -316,10 +330,10 @@ (deftest :fddl/sequence/1 (progn (clsql:create-sequence [foo]) (values - (clsql:sequence-exists-p [foo] :owner *test-database-user*) + (clsql:sequence-exists-p [foo]) (progn (clsql:drop-sequence [foo] :if-does-not-exist :ignore) - (clsql:sequence-exists-p [foo] :owner *test-database-user*)))) + (clsql:sequence-exists-p [foo])))) t nil) ;; create and increment a sequence @@ -357,6 +371,65 @@ (length (clsql:list-tables :owner nil))))) t) +(deftest :fddl/owner/table + (with-dataset *ds-fddl* + (values + (clsql-sys:table-exists-p [alpha]) + (clsql-sys:table-exists-p [alpha] :owner *test-database-user*) + (clsql-sys:table-exists-p [alpha] :owner *test-false-database-user*))) + t t nil) + +(deftest :fddl/owner/attributes + (with-dataset *ds-fddl* + (values + (length (clsql-sys:list-attributes [alpha])) + (length (clsql-sys:list-attributes [alpha] :owner *test-database-user*)) + (length (clsql-sys:list-attributes [alpha] :owner *test-false-database-user*)))) + 4 4 0) + +(deftest :fddl/owner/attribute-types + (with-dataset *ds-fddl* + (values + (length (clsql:list-attribute-types [alpha])) + (length (clsql:list-attribute-types [alpha] :owner *test-database-user*)) + (length (clsql:list-attribute-types [alpha] :owner *test-false-database-user*)))) + 4 4 0) + +(deftest :fddl/owner/index + (with-dataset *ds-fddl* + (progn (clsql:create-index [bar] :on [alpha] + :attributes '([a] [c])) + (values + (clsql:index-exists-p [bar] ) + (clsql:index-exists-p [bar] :owner *test-database-user*) + (clsql:index-exists-p [bar] :owner *test-false-database-user*) + + (length (clsql-sys:list-indexes :on [alpha])) + (length (clsql-sys:list-indexes :on [alpha] :owner *test-database-user*)) + (length (clsql-sys:list-indexes :on [alpha] :owner *test-false-database-user*)) + (progn + (clsql:drop-index [bar] :on [alpha] + :if-does-not-exist :ignore) + (clsql:index-exists-p [bar] :owner *test-database-user*)) + (clsql:index-exists-p [bar] )))) + t t nil + 1 1 0 + nil nil) + +(deftest :fddl/owner/sequence + (progn (clsql:create-sequence [foo]) + (values + (clsql:sequence-exists-p [foo]) + (clsql:sequence-exists-p [foo] :owner *test-database-user*) + (clsql:sequence-exists-p [foo] :owner *test-false-database-user*) + + (progn + (clsql:drop-sequence [foo] :if-does-not-exist :ignore) + (clsql:sequence-exists-p [foo] )))) + t t nil nil) + + + (deftest :fddl/cache-table-queries/1 (with-dataset *ds-fddl* (list @@ -377,5 +450,3 @@ (nil (t nil) t (t nil))) )) - -#.(clsql:restore-sql-reader-syntax-state)