X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=tests%2Ftest-fddl.lisp;h=32e645bb277135ae9201299e2c7ff1f4aa86e0e7;hp=98cd21601d13b73ce54105526dfa0b6f2efcbaef;hb=8a8ee2d7d791b7a3efaed06420802a925d16fca3;hpb=a4097e19c5157e87b9991549bc44f3ef598aeb90 diff --git a/tests/test-fddl.lisp b/tests/test-fddl.lisp index 98cd216..32e645b 100644 --- a/tests/test-fddl.lisp +++ b/tests/test-fddl.lisp @@ -1,9 +1,9 @@ ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*- ;;;; ====================================================================== -;;;; File: test-fddl.lisp -;;;; Author: Marcus Pearce -;;;; Created: 30/03/2004 -;;;; Updated: $Id$ +;;;; File: test-fddl.lisp +;;;; Authors: Marcus Pearce and Kevin Rosenberg +;;;; Created: 30/03/2004 +;;;; Updated: $Id$ ;;;; ;;;; Tests for the CLSQL Functional Data Definition Language ;;;; (FDDL). @@ -27,8 +27,8 @@ (apply #'values (sort (mapcar #'string-downcase (clsql:list-tables :owner *test-database-user*)) - #'string>)) - "employee" "company") + #'string<)) + "addr" "company" "ea_join" "employee" "type_table") ;; create a table, test for its existence, drop it and test again (deftest :fddl/table/2 @@ -64,9 +64,9 @@ (sort (mapcar #'string-downcase (clsql:list-attributes [employee] - :owner *test-database-user*)) + :owner *test-database-user*)) #'string<)) - "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height" + "birthday" "ecompanyid" "email" "emplid" "first_name" "groupid" "height" "last_name" "managerid" "married") (deftest :fddl/attributes/2 @@ -76,76 +76,91 @@ (clsql:list-attribute-types [employee] :owner *test-database-user*)) #'string<)) - "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height" + "birthday" "ecompanyid" "email" "emplid" "first_name" "groupid" "height" "last_name" "managerid" "married") -;; create a view, test for existence, drop it and test again +;; Attribute types are vendor specific so need to test a range +(deftest :fddl/attributes/3 + (and (member (clsql:attribute-type [emplid] [employee]) '(:int :integer :int4)) t) + t) + +(deftest :fddl/attributes/4 + (clsql:attribute-type [first-name] [employee]) + :varchar 30 nil 1) + +(deftest :fddl/attributes/5 + (and (member (clsql:attribute-type [birthday] [employee]) '(:datetime :timestamp)) t) + t) + +(deftest :fddl/attributes/6 + (and (member (clsql:attribute-type [height] [employee]) '(:float :float8)) t) + t) + + + +;; create a view, test for existence, drop it and test again (deftest :fddl/view/1 (progn (clsql:create-view [lenins-group] - ;;not in sqlite - ;;:column-list '([forename] [surname] [email]) - :as [select [first-name] [last-name] [email] - :from [employee] - :where [= [managerid] 1]]) - (values - (clsql:view-exists-p [lenins-group] :owner *test-database-user*) - (progn - (clsql:drop-view [lenins-group] :if-does-not-exist :ignore) - (clsql:view-exists-p [lenins-group] :owner *test-database-user*)))) + :as [select [first-name] [last-name] [email] + :from [employee] + :where [= [managerid] 1]]) + (values + (clsql:view-exists-p [lenins-group] :owner *test-database-user*) + (progn + (clsql:drop-view [lenins-group] :if-does-not-exist :ignore) + (clsql:view-exists-p [lenins-group] :owner *test-database-user*)))) t nil) - -;; create a view, list its attributes and drop it -(deftest :fddl/view/2 - (progn (clsql:create-view [lenins-group] - ;;not in sqlite - ;;:column-list '([forename] [surname] [email]) - :as [select [first-name] [last-name] [email] - :from [employee] - :where [= [managerid] 1]]) - (prog1 - (sort (mapcar #'string-downcase - (clsql:list-attributes [lenins-group])) - #'string<) - (clsql:drop-view [lenins-group] :if-does-not-exist :ignore))) - ("email" "first_name" "last_name")) - -;; create a view, select stuff from it and drop it + + ;; create a view, list its attributes and drop it +(when (clsql-sys:db-type-has-views? *test-database-underlying-type*) + (deftest :fddl/view/2 + (progn (clsql:create-view [lenins-group] + :as [select [first-name] [last-name] [email] + :from [employee] + :where [= [managerid] 1]]) + (prog1 + (sort (mapcar #'string-downcase + (clsql:list-attributes [lenins-group])) + #'string<) + (clsql:drop-view [lenins-group] :if-does-not-exist :ignore))) + ("email" "first_name" "last_name"))) + + ;; create a view, select stuff from it and drop it (deftest :fddl/view/3 (progn (clsql:create-view [lenins-group] - :as [select [first-name] [last-name] [email] - :from [employee] - :where [= [managerid] 1]]) - (let ((result - (list - ;; Shouldn't exist - (clsql:select [first-name] [last-name] [email] - :from [lenins-group] - :where [= [last-name] "Lenin"]) - ;; Should exist - (car (clsql:select [first-name] [last-name] [email] - :from [lenins-group] - :where [= [last-name] "Stalin"]))))) - (clsql:drop-view [lenins-group] :if-does-not-exist :ignore) - (apply #'values result))) + :as [select [first-name] [last-name] [email] + :from [employee] + :where [= [managerid] 1]]) + (let ((result + (list + ;; Shouldn't exist + (clsql:select [first-name] [last-name] [email] + :from [lenins-group] + :where [= [last-name] "Lenin"]) + ;; Should exist + (car (clsql:select [first-name] [last-name] [email] + :from [lenins-group] + :where [= [last-name] "Stalin"]))))) + (clsql:drop-view [lenins-group] :if-does-not-exist :ignore) + (apply #'values result))) nil ("Josef" "Stalin" "stalin@soviet.org")) - -;; not in sqlite + (deftest :fddl/view/4 (progn (clsql:create-view [lenins-group] - :column-list '([forename] [surname] [email]) - :as [select [first-name] [last-name] [email] - :from [employee] - :where [= [managerid] 1]]) + :column-list '([forename] [surname] [email]) + :as [select [first-name] [last-name] [email] + :from [employee] + :where [= [managerid] 1]]) (let ((result (list ;; Shouldn't exist - (clsql:select [forename] [surname] [email] - :from [lenins-group] - :where [= [surname] "Lenin"]) - ;; Should exist - (car (clsql:select [forename] [surname] [email] - :from [lenins-group] - :where [= [surname] "Stalin"]))))) + (clsql:select [forename] [surname] [email] + :from [lenins-group] + :where [= [surname] "Lenin"]) + ;; Should exist + (car (clsql:select [forename] [surname] [email] + :from [lenins-group] + :where [= [surname] "Stalin"]))))) (clsql:drop-view [lenins-group] :if-does-not-exist :ignore) (apply #'values result))) nil ("Josef" "Stalin" "stalin@soviet.org")) @@ -176,25 +191,27 @@ ;; test list-table-indexes (deftest :fddl/index/3 (progn - (clsql:execute-command "CREATE TABLE I3TEST (a char(10), b integer)") - (clsql:create-index [bar] :on [i3test] :attributes - '([a]) :unique t) + (clsql:create-table [i3test] '(([a] (string 10)) + ([b] integer))) (clsql:create-index [foo] :on [i3test] :attributes - '([b]) :unique nil) + '([b]) :unique nil) + (clsql:create-index [bar] :on [i3test] :attributes + '([a]) :unique t) (values - + (clsql:table-exists-p [i3test]) + (clsql:index-exists-p [foo]) + (clsql:index-exists-p [bar]) (sort (mapcar #'string-downcase (clsql:list-table-indexes [i3test] :owner *test-database-user*)) - #'string-lessp) - (sort (clsql:list-table-indexes [company] :owner *test-database-user*) - #'string-lessp) + #'string-lessp) (progn (clsql:drop-index [bar] :on [i3test]) (clsql:drop-index [foo] :on [i3test]) + (clsql:drop-table [i3test]) t))) - ("bar" "foo") nil t) + t t t ("bar" "foo") t) ;; create an sequence, test for existence, drop it and test again (deftest :fddl/sequence/1