X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=tests%2Ftest-fddl.lisp;h=e231592c82610d5368c3607984c217b875415de5;hb=db9892632e6eb7869aea7a94c16b523a82de1501;hp=848bc84d320943a06fa15e225c88ec49246c919a;hpb=7f0e4a65d1b425f2fa58fc7cce8296c1a6c52c2f;p=clsql.git diff --git a/tests/test-fddl.lisp b/tests/test-fddl.lisp index 848bc84..e231592 100644 --- a/tests/test-fddl.lisp +++ b/tests/test-fddl.lisp @@ -3,63 +3,67 @@ ;;;; File: test-fddl.lisp ;;;; Author: Marcus Pearce ;;;; Created: 30/03/2004 -;;;; Updated: <04/04/2004 11:53:29 marcusp> -;;;; ====================================================================== -;;;; -;;;; Description ========================================================== -;;;; ====================================================================== +;;;; Updated: $Id$ ;;;; -;;;; Tests for the CLSQL-USQL Functional Data Definition Language +;;;; Tests for the CLSQL Functional Data Definition Language ;;;; (FDDL). -;;;; +;;;; +;;;; 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-usql-tests) +(in-package #:clsql-tests) -#.(usql:locally-enable-sql-reader-syntax) +#.(clsql:locally-enable-sql-reader-syntax) +(setq *rt-fddl* + '( + ;; list current tables (deftest :fddl/table/1 (apply #'values (sort (mapcar #'string-downcase - (usql:list-tables :owner *test-database-user*)) + (clsql:list-tables :owner *test-database-user*)) #'string>)) - "usql_object_v" "employee" "company") + "employee" "company") ;; create a table, test for its existence, drop it and test again (deftest :fddl/table/2 - (progn (usql:create-table [foo] + (progn (clsql:create-table [foo] '(([id] integer) ([height] float) ([name] (string 24)) ([comments] longchar))) (values - (usql:table-exists-p [foo] :owner *test-database-user*) + (clsql:table-exists-p [foo] :owner *test-database-user*) (progn - (usql:drop-table [foo] :if-does-not-exist :ignore) - (usql:table-exists-p [foo] :owner *test-database-user*)))) + (clsql:drop-table [foo] :if-does-not-exist :ignore) + (clsql:table-exists-p [foo] :owner *test-database-user*)))) t nil) ;; create a table, list its attributes and drop it (deftest :fddl/table/3 (apply #'values - (progn (usql:create-table [foo] + (progn (clsql:create-table [foo] '(([id] integer) ([height] float) ([name] (char 255)) ([comments] longchar))) (prog1 (sort (mapcar #'string-downcase - (usql:list-attributes [foo])) + (clsql:list-attributes [foo])) #'string<) - (usql:drop-table [foo] :if-does-not-exist :ignore)))) + (clsql:drop-table [foo] :if-does-not-exist :ignore)))) "comments" "height" "id" "name") (deftest :fddl/attributes/1 (apply #'values (sort (mapcar #'string-downcase - (usql:list-attributes [employee] + (clsql:list-attributes [employee] :owner *test-database-user*)) #'string<)) "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height" @@ -69,7 +73,7 @@ (apply #'values (sort (mapcar #'(lambda (a) (string-downcase (car a))) - (usql:list-attribute-types [employee] + (clsql:list-attribute-types [employee] :owner *test-database-user*)) #'string<)) "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height" @@ -77,22 +81,22 @@ ;; create a view, test for existence, drop it and test again (deftest :fddl/view/1 - (progn (usql:create-view [lenins-group] + (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 - (usql:view-exists-p [lenins-group] :owner *test-database-user*) + (clsql:view-exists-p [lenins-group] :owner *test-database-user*) (progn - (usql:drop-view [lenins-group] :if-does-not-exist :ignore) - (usql:view-exists-p [lenins-group] :owner *test-database-user*)))) + (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 (usql:create-view [lenins-group] + (progn (clsql:create-view [lenins-group] ;;not in sqlite ;;:column-list '([forename] [surname] [email]) :as [select [first-name] [last-name] [email] @@ -100,68 +104,62 @@ :where [= [managerid] 1]]) (prog1 (sort (mapcar #'string-downcase - (usql:list-attributes [lenins-group])) + (clsql:list-attributes [lenins-group])) #'string<) - (usql:drop-view [lenins-group] :if-does-not-exist :ignore))) + (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 (usql:create-view [lenins-group] + (progn (clsql:create-view [lenins-group] :as [select [first-name] [last-name] [email] :from [employee] :where [= [managerid] 1]]) (let ((result (list ;; Shouldn't exist - (usql:select [first-name] [last-name] [email] + (clsql:select [first-name] [last-name] [email] :from [lenins-group] :where [= [last-name] "Lenin"]) ;; Should exist - (car (usql:select [first-name] [last-name] [email] + (car (clsql:select [first-name] [last-name] [email] :from [lenins-group] :where [= [last-name] "Stalin"]))))) - (usql:drop-view [lenins-group] :if-does-not-exist :ignore) + (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 - (if (eql *test-database-type* :sqlite) - (values nil '(("Josef" "Stalin" "stalin@soviet.org"))) - (progn (usql:create-view [lenins-group] - :column-list '([forename] [surname] [email]) - :as [select [first-name] [last-name] [email] - :from [employee] - :where [= [managerid] 1]]) - (let ((result - (list - ;; Shouldn't exist - (usql:select [forename] [surname] [email] - :from [lenins-group] - :where [= [surname] "Lenin"]) - ;; Should exist - (car (usql:select [forename] [surname] [email] - :from [lenins-group] - :where [= [surname] "Stalin"]))))) - (usql:drop-view [lenins-group] :if-does-not-exist :ignore) - (apply #'values result)))) + (progn (clsql:create-view [lenins-group] + :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:drop-view [lenins-group] :if-does-not-exist :ignore) + (apply #'values result))) nil ("Josef" "Stalin" "stalin@soviet.org")) ;; create an index, test for existence, drop it and test again (deftest :fddl/index/1 - (progn (usql:create-index [bar] :on [employee] :attributes + (progn (clsql:create-index [bar] :on [employee] :attributes '([first-name] [last-name] [email]) :unique t) (values - (usql:index-exists-p [bar] :owner *test-database-user*) + (clsql:index-exists-p [bar] :owner *test-database-user*) (progn - (case *test-database-type* - (:mysql - (usql:drop-index [bar] :on [employee] - :if-does-not-exist :ignore)) - (t - (usql:drop-index [bar]:if-does-not-exist :ignore))) - (usql:view-exists-p [bar] :owner *test-database-user*)))) + (clsql:drop-index [bar] :on [employee] + :if-does-not-exist :ignore) + (clsql:index-exists-p [bar] :owner *test-database-user*)))) t nil) ;; create indexes with names as strings, symbols and in square brackets @@ -169,43 +167,65 @@ (let ((names '("foo" foo [foo])) (result '())) (dolist (name names) - (usql:create-index name :on [employee] :attributes '([emplid])) - (push (usql:index-exists-p name :owner *test-database-user*) result) - (case *test-database-type* - (:mysql - (usql:drop-index name :on [employee] :if-does-not-exist :ignore)) - (t (usql:drop-index name :if-does-not-exist :ignore)))) + (clsql:create-index name :on [employee] :attributes '([emplid])) + (push (clsql:index-exists-p name :owner *test-database-user*) result) + (clsql:drop-index name :on [employee] :if-does-not-exist :ignore)) (apply #'values result)) t t t) +;; test list-table-indexes +(deftest :fddl/index/3 + (progn + (clsql:create-index [bar] :on [employee] :attributes + '([last-name]) :unique nil) + (clsql:create-index [foo] :on [employee] :attributes + '([first-name]) :unique nil) + (values + + (sort + (mapcar + #'string-downcase + (clsql:list-table-indexes [employee] :owner *test-database-user*)) + #'string-lessp) + (sort (clsql:list-table-indexes [company] :owner *test-database-user*) + #'string-lessp) + (progn + (clsql:drop-index [bar] :on [employee]) + (clsql:drop-index [foo] :on [employee]) + t))) + + ("bar" "foo") nil t) + ;; create an sequence, test for existence, drop it and test again (deftest :fddl/sequence/1 - (progn (usql:create-sequence [foo]) + (progn (clsql:create-sequence [foo]) (values - (usql:sequence-exists-p [foo] :owner *test-database-user*) + (clsql:sequence-exists-p [foo] :owner *test-database-user*) (progn - (usql:drop-sequence [foo] :if-does-not-exist :ignore) - (usql:sequence-exists-p [foo] :owner *test-database-user*)))) + (clsql:drop-sequence [foo] :if-does-not-exist :ignore) + (clsql:sequence-exists-p [foo] :owner *test-database-user*)))) t nil) ;; create and increment a sequence (deftest :fddl/sequence/2 (let ((val1 nil)) - (usql:create-sequence [foo]) - (setf val1 (usql:sequence-next [foo])) + (clsql:create-sequence [foo]) + (setf val1 (clsql:sequence-next [foo])) (prog1 - (< val1 (usql:sequence-next [foo])) - (usql:drop-sequence [foo] :if-does-not-exist :ignore))) + (< val1 (clsql:sequence-next [foo])) + (clsql:drop-sequence [foo] :if-does-not-exist :ignore))) t) ;; explicitly set the value of a sequence (deftest :fddl/sequence/3 (progn - (usql:create-sequence [foo]) - (usql:set-sequence-position [foo] 5) + (clsql:create-sequence [foo]) + (clsql:set-sequence-position [foo] 5) (prog1 - (usql:sequence-next [foo]) - (usql:drop-sequence [foo] :if-does-not-exist :ignore))) + (clsql:sequence-next [foo]) + (clsql:drop-sequence [foo] :if-does-not-exist :ignore))) 6) -#.(usql:restore-sql-reader-syntax-state) \ No newline at end of file +)) + +#.(clsql:restore-sql-reader-syntax-state)