X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=tests%2Ftest-fddl.lisp;h=f5527c773f7cce25f3ff017cc3f6c40cddb7f28f;hp=ef79615b903f7e3edb9824f121ed82b2420bd7d3;hb=d9f41af62750c622945bb17b622a39689ee5b840;hpb=5068697a98c10224f3a3e0a7125ba64cf3d3b4fb diff --git a/tests/test-fddl.lisp b/tests/test-fddl.lisp index ef79615..f5527c7 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). @@ -21,14 +21,13 @@ (setq *rt-fddl* '( - + ;; list current tables (deftest :fddl/table/1 - (apply #'values - (sort (mapcar #'string-downcase - (clsql:list-tables :owner *test-database-user*)) - #'string>)) - "employee" "company") + (sort (mapcar #'string-downcase + (clsql:list-tables :owner *test-database-user*)) + #'string<) + ("addr" "big" "company" "ea_join" "employee" "type_bigint" "type_table")) ;; create a table, test for its existence, drop it and test again (deftest :fddl/table/2 @@ -59,14 +58,72 @@ (clsql:drop-table [foo] :if-does-not-exist :ignore)))) "comments" "height" "id" "name") +(deftest :fddl/table/4 + (values + (clsql:table-exists-p "MyMixedCase") + (progn + (clsql:create-table "MyMixedCase" '(([a] integer))) + (clsql:table-exists-p "MyMixedCase")) + (progn + (clsql:drop-table "MyMixedCase") + (clsql:table-exists-p "MyMixedCase"))) + nil t nil) + +(deftest :fddl/table/5 + (prog1 + (progn + (clsql:create-table "MyMixedCase" '(([a] integer))) + (clsql:execute-command "insert into MyMixedCase values (5)") + (clsql:insert-records :into "MyMixedCase" :values '(6)) + (clsql:select [a] :from "MyMixedCase" :order-by '((a :asc)))) + (clsql:drop-table "MyMixedCase")) + ((5) (6))) + +(deftest :fddl/table/6 + (values + (clsql:table-exists-p [foo]) + (progn + (let ((*backend-warning-behavior* + (if (member *test-database-type* + '(:postgresql :postgresql-socket)) + :ignore + :warn))) + (clsql:create-table [foo] + '(([bar] integer :not-null :unique :primary-key) + ([baz] string :not-null :unique)))) + (clsql:table-exists-p [foo])) + (progn + (clsql:drop-table [foo]) + (clsql:table-exists-p [foo]))) + nil t nil) + +(deftest :fddl/table/7 + (values + (clsql:table-exists-p [foo]) + (progn + (let ((*backend-warning-behavior* + (if (member *test-database-type* + '(:postgresql :postgresql-socket)) + :ignore + :warn))) + (clsql:create-table [foo] '(([bar] integer :not-null) + ([baz] string :not-null)) + :constraints '("UNIQUE (bar,baz)" + "PRIMARY KEY (bar)"))) + (clsql:table-exists-p [foo])) + (progn + (clsql:drop-table [foo]) + (clsql:table-exists-p [foo]))) + nil t nil) + (deftest :fddl/attributes/1 (apply #'values (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" + "bd_utime" "birthday" "ecompanyid" "email" "emplid" "first_name" "groupid" "height" "last_name" "managerid" "married") (deftest :fddl/attributes/2 @@ -76,80 +133,98 @@ (clsql:list-attribute-types [employee] :owner *test-database-user*)) #'string<)) - "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height" + "bd_utime" "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 :number)) t) + t) + +(deftest :fddl/attributes/4 + (multiple-value-bind (type length scale nullable) + (clsql:attribute-type [first-name] [employee]) + (values (clsql-sys:in type :varchar :varchar2) length scale nullable)) + t 30 nil 1) + +(deftest :fddl/attributes/5 + (and (member (clsql:attribute-type [birthday] [employee]) '(:datetime :timestamp :date)) t) + t) + +(deftest :fddl/attributes/6 + (and (member (clsql:attribute-type [height] [employee]) '(:float :float8 :number)) t) + t) + +(deftest :fddl/attributes/7 + (and (member (clsql:attribute-type [bd_utime] [employee]) '(:bigint :int8 :char)) 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 - (if (eql *test-database-type* :sqlite) - (values nil '(("Josef" "Stalin" "stalin@soviet.org"))) - (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)))) + (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 @@ -159,13 +234,9 @@ (values (clsql:index-exists-p [bar] :owner *test-database-user*) (progn - (case *test-database-type* - (:mysql - (clsql:drop-index [bar] :on [employee] - :if-does-not-exist :ignore)) - (t - (clsql:drop-index [bar]:if-does-not-exist :ignore))) - (clsql: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 @@ -173,15 +244,37 @@ (let ((names '("foo" foo [foo])) (result '())) (dolist (name names) - (clsql:create-index name :on [employee] :attributes '([emplid])) + (clsql:create-index name :on [employee] :attributes '([last-name])) (push (clsql:index-exists-p name :owner *test-database-user*) result) - (case *test-database-type* - (:mysql - (clsql:drop-index name :on [employee] :if-does-not-exist :ignore)) - (t (clsql:drop-index name :if-does-not-exist :ignore)))) + (clsql:drop-index name :on [employee] :if-does-not-exist :ignore)) (apply #'values result)) t t t) +;; test list-indexes with keyword :ON +(deftest :fddl/index/3 + (progn + (clsql:create-table [i3test] '(([a] (string 10)) + ([b] integer))) + (clsql:create-index [foo] :on [i3test] :attributes + '([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-indexes :on [i3test] :owner *test-database-user*)) + #'string-lessp) + (progn + (clsql:drop-index [bar] :on [i3test]) + (clsql:drop-index [foo] :on [i3test]) + (clsql:drop-table [i3test]) + t))) + t t t ("bar" "foo") t) + ;; create an sequence, test for existence, drop it and test again (deftest :fddl/sequence/1 (progn (clsql:create-sequence [foo]) @@ -212,6 +305,57 @@ (clsql:drop-sequence [foo] :if-does-not-exist :ignore))) 6) -)) +(deftest :fddl/big/1 + (let ((rows (clsql:select [*] :from [big] :field-names nil))) + (values + (length rows) + (do ((i 0 (1+ i)) + (max (expt 2 60)) + (rest rows (cdr rest))) + ((= i (length rows)) t) + (let ((index (1+ i)) + (int (first (car rest))) + (bigint (second (car rest)))) + (when (and (or (eq *test-database-type* :oracle) + (and (eq *test-database-type* :odbc) + (eq *test-database-underlying-type* :postgresql))) + (stringp bigint)) + (setf bigint (parse-integer bigint))) + (unless (and (eql int index) + (eql bigint (truncate max index))) + (return nil)))))) + 555 t) + +(deftest :fddl/owner/1 + (and + ;; user tables are an improper subset of all tables + (= (length (intersection (clsql:list-tables :owner nil) + (clsql:list-tables :owner :all) + :test #'string=)) + (length (clsql:list-tables :owner nil))) + ;; user tables are a proper subset of all tables + (> (length (clsql:list-tables :owner :all)) + (length (clsql:list-tables :owner nil)))) + t) + +(deftest :fddl/cache-table-queries/1 + (list + (gethash "EMPLOYEE" (clsql-sys::attribute-cache clsql:*default-database*)) + (progn + (clsql:cache-table-queries "EMPLOYEE" :action t) + (gethash "EMPLOYEE" (clsql-sys::attribute-cache clsql:*default-database*))) + (progn + (clsql:list-attribute-types "EMPLOYEE") + (not + (null + (cadr + (gethash "EMPLOYEE" + (clsql-sys::attribute-cache clsql:*default-database*)))))) + (progn + (clsql:cache-table-queries "EMPLOYEE" :action :flush) + (gethash "EMPLOYEE" (clsql-sys::attribute-cache clsql:*default-database*)))) + (nil (t nil) t (t nil))) + + )) #.(clsql:restore-sql-reader-syntax-state)