X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=tests%2Ftest-fddl.lisp;h=83eada9f1074e699b6e584d90360be9e884be957;hp=446c2f24078b3b6ffb033d3a005ee4740d081d95;hb=151c009059521769a44ec35dfdceb86d5373af99;hpb=4f42e6bf168107ce0ffd9ed0ca0316d00301b11d diff --git a/tests/test-fddl.lisp b/tests/test-fddl.lisp index 446c2f2..83eada9 100644 --- a/tests/test-fddl.lisp +++ b/tests/test-fddl.lisp @@ -1,32 +1,43 @@ ;;;; -*- 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 ("CREATE TABLE ALPHA (A integer, B integer, C varchar (30), d date, f float)" - "CREATE TABLE BRAVO (jack integer, jill integer)")) - (:sqldata "ALPHA" "A,B,C,d,f" - "1,1,'asdf','2010-01-01',3.14" - "2,1,'blarg','2012-12-21',0.1") + (:setup (lambda () + (create-table [alpha] '(([a] integer) + ([c] (varchar 30)) + ([d] date) + ([f] float))) + (create-table [bravo] '(([foo] integer) + ([bar] integer))))) + (:sqldata "ALPHA" "A,C,D,F" + "1,'asdf','2010-01-01',3.14" + "2,'blarg','2012-12-21',0.1" + "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* '( @@ -34,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")) @@ -46,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 @@ -134,19 +145,18 @@ (with-dataset *ds-fddl* (sort (mapcar #'string-downcase - (clsql:list-attributes [alpha] :owner *test-database-user*)) + (clsql:list-attributes [alpha] )) #'string<))) - "a" "b" "c" "d" "f") + "a" "c" "d" "f") (deftest :fddl/attributes/2 (with-dataset *ds-fddl* (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" "b" "c" "d" "f") + "a" "c" "d" "f") ;; Attribute types are vendor specific so need to test a range (deftest :fddl/attributes/3 @@ -158,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 @@ -176,26 +188,34 @@ (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 (with-dataset *ds-fddl* - (progn (clsql:create-view [v1] - :as [select [a] [b] [c] - :from [alpha] - :where [= [a] 1]]) - (values - (clsql:view-exists-p [v1] :owner *test-database-user*) - (progn - (clsql:drop-view [v1] :if-does-not-exist :ignore) - (clsql:view-exists-p [v1] :owner *test-database-user*))))) + (progn (clsql:create-view [v1] + :as [select [a] [c] [d] + :from [alpha] + :where [= [a] 1]]) + (values + (clsql:view-exists-p [v1]) + (progn + (clsql:drop-view [v1] :if-does-not-exist :ignore) + (clsql:view-exists-p [v1]))))) t nil) ;; create a view, list its attributes and drop it (deftest :fddl/view/2 (with-dataset *ds-fddl* (progn (clsql:create-view [v1] - :as [select [a] [b] [c] + :as [select [a] [c] [d] :from [alpha] :where [= [a] 1]]) (unwind-protect @@ -203,67 +223,70 @@ (clsql:list-attributes [v1])) #'string<) (clsql:drop-view [v1] :if-does-not-exist :ignore)))) - ("a" "b" "c")) + ("a" "c" "d")) ;; create a view, select stuff from it and drop it (deftest :fddl/view/3 (with-dataset *ds-fddl* (progn (clsql:create-view [v1] - :as [select [a] [b] [c] + :as [select [a] [c] [d] :from [alpha] :where [= [a] 1]]) (unwind-protect (let ((result (list ;; Shouldn't exist - (clsql:select [a] [b] [c] + (clsql:select [a] [c] :from [v1] :where [= [a] -1]) ;; Should exist - (car (clsql:select [a] [b] [c] + (car (clsql:select [a] [c] :from [v1] :where [= [a] 1]))))) (apply #'values result)) (clsql:drop-view [v1] :if-does-not-exist :ignore)))) - nil (1 1 "asdf")) + nil (1 "asdf")) (deftest :fddl/view/4 (with-dataset *ds-fddl* (progn (clsql:create-view [v1] :column-list '([x] [y] [z]) - :as [select [a] [b] [c] + :as [select [a] [c] [d] :from [alpha] :where [= [a] 1]]) (unwind-protect (let ((result (list + (sort (mapcar #'string-downcase + (clsql:list-attributes [v1])) + #'string<) ;; Shouldn't exist - (clsql:select [x] [y] [z] + (clsql:select [x] [y] :from [v1] :where [= [x] -1]) ;; Should exist - (car (clsql:select [x] [y] [z] + (car (clsql:select [x] [y] :from [v1] :where [= [x] 1]))))) (apply #'values result)) (clsql:drop-view [v1] :if-does-not-exist :ignore)))) - nil (1 1 "asdf")) + ("x" "y" "z") nil (1 "asdf")) ;; create an index, test for existence, drop it and test again (deftest :fddl/index/1 (with-dataset *ds-fddl* (progn (clsql:create-index [bar] :on [alpha] :attributes - '([a] [b] [c]) :unique t) + '([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 @@ -273,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) @@ -294,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]) @@ -307,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 @@ -348,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 @@ -368,5 +450,3 @@ (nil (t nil) t (t nil))) )) - -#.(clsql:restore-sql-reader-syntax-state)