1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; ======================================================================
3 ;;;; File: test-fddl.lisp
4 ;;;; Authors: Marcus Pearce <m.t.pearce@city.ac.uk> and Kevin Rosenberg
5 ;;;; Created: 30/03/2004
8 ;;;; Tests for the CLSQL Functional Data Definition Language
11 ;;;; This file is part of CLSQL.
13 ;;;; CLSQL users are granted the rights to distribute and use this software
14 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
15 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
16 ;;;; ======================================================================
18 (in-package #:clsql-tests)
20 #.(clsql:locally-enable-sql-reader-syntax)
22 (def-dataset *ds-fddl*
24 (create-table [alpha] '(([a] integer)
28 (create-table [bravo] '(([foo] integer)
30 (:sqldata "ALPHA" "A,C,D,F"
31 "1,'asdf','2010-01-01',3.14"
32 "2,'blarg','2012-12-21',0.1"
33 "3,'matey','1992-02-29',0.0")
34 (:cleanup "DROP TABLE ALPHA" "DROP TABLE BRAVO"))
36 (def-dataset *ds-fddl-parsing-oddities*
37 (:setup "CREATE TABLE ATEST (
40 (:cleanup "DROP TABLE ATEST"))
45 ;; list current tables
46 (deftest :fddl/table/1
47 (with-dataset *ds-fddl*
48 (sort (mapcar #'string-downcase
53 ;; create a table, test for its existence, drop it and test again
54 (deftest :fddl/table/2
55 (progn (clsql:create-table [foo]
59 ([comments] longchar)))
61 (clsql:table-exists-p [foo])
63 (clsql:drop-table [foo] :if-does-not-exist :ignore)
64 (clsql:table-exists-p [foo]))))
67 ;; create a table, list its attributes and drop it
68 (deftest :fddl/table/3
70 (progn (clsql:create-table [foo]
74 ([comments] longchar)))
76 (sort (mapcar #'string-downcase
77 (clsql:list-attributes [foo]))
79 (clsql:drop-table [foo] :if-does-not-exist :ignore))))
80 "comments" "height" "id" "name")
82 (deftest :fddl/table/4
84 (clsql:table-exists-p "MyMixedCase")
86 (clsql:create-table "MyMixedCase" '(([a] integer)))
87 (clsql:table-exists-p "MyMixedCase"))
89 (clsql:drop-table "MyMixedCase")
90 (clsql:table-exists-p "MyMixedCase")))
93 (deftest :fddl/table/5
96 (clsql:create-table "MyMixedCase" '(([a] integer)))
97 (clsql:execute-command "insert into \"MyMixedCase\" values (5)")
98 (clsql:insert-records :into "MyMixedCase" :values '(6))
99 (clsql:select [a] :from "MyMixedCase" :order-by '((a :asc))))
100 (clsql:drop-table "MyMixedCase"))
103 (deftest :fddl/table/6
105 (clsql:table-exists-p [foo])
107 (let ((*backend-warning-behavior*
108 (if (member *test-database-type*
109 '(:postgresql :postgresql-socket))
112 (case *test-database-underlying-type*
113 (:mssql (clsql:create-table [foo]
114 '(([bar] integer :not-null :primary-key)
115 ([baz] string :not-null :unique))))
116 (t (clsql:create-table [foo]
117 '(([bar] integer :not-null :unique :primary-key)
118 ([baz] string :not-null :unique))))))
119 (clsql:table-exists-p [foo]))
121 (clsql:drop-table [foo])
122 (clsql:table-exists-p [foo])))
125 (deftest :fddl/table/7
127 (clsql:table-exists-p [foo])
129 (let ((*backend-warning-behavior*
130 (if (member *test-database-type*
131 '(:postgresql :postgresql-socket))
134 (clsql:create-table [foo] '(([bar] integer :not-null)
135 ([baz] string :not-null))
136 :constraints '("UNIQUE (bar,baz)"
137 "PRIMARY KEY (bar)")))
138 (clsql:table-exists-p [foo]))
140 (clsql:drop-table [foo])
141 (clsql:table-exists-p [foo])))
144 (deftest :fddl/attributes/1
146 (with-dataset *ds-fddl*
148 (mapcar #'string-downcase
149 (clsql:list-attributes [alpha] ))
153 (deftest :fddl/attributes/2
154 (with-dataset *ds-fddl*
157 (mapcar #'(lambda (a) (string-downcase (car a)))
158 (clsql:list-attribute-types [alpha]))
162 ;; Attribute types are vendor specific so need to test a range
163 (deftest :fddl/attributes/3
164 (with-dataset *ds-fddl*
165 (and (member (clsql:attribute-type [a] [alpha]) '(:int :integer :int4 :number)) t))
168 (deftest :fddl/attributes/4
169 (with-dataset *ds-fddl*
170 (multiple-value-bind (type length scale nullable)
171 (clsql:attribute-type [c] [alpha])
172 (values (clsql-sys:in type :varchar :varchar2) length scale nullable)))
175 (deftest :fddl/attributes/5
176 (with-dataset *ds-fddl*
177 (and (member (clsql:attribute-type [d] [alpha]) '(:datetime :timestamp :date)) t))
180 (deftest :fddl/attributes/6
181 (with-dataset *ds-fddl*
182 (and (member (clsql:attribute-type [f] [alpha]) '(:float :float8 :number)) t))
185 (deftest :fddl/attributes/7
186 (with-dataset *ds-bigint*
187 (and (member (clsql:attribute-type [t_bigint] [TYPE_BIGINT]) '(:bigint :int8)) t))
190 (deftest :fddl/attributes/8
191 ;;this is mostly from sqlite3 sending back
192 (with-dataset *ds-fddl-parsing-oddities*
194 (clsql-sys:in (clsql:attribute-type [a] [atest]) :varchar :varchar2)
195 (clsql-sys:in (clsql:attribute-type [b] [atest]) :varchar :varchar2)))
199 ;; create a view, test for existence, drop it and test again
200 (deftest :fddl/view/1
201 (with-dataset *ds-fddl*
202 (progn (clsql:create-view [v1]
203 :as [select [a] [c] [d]
207 (clsql:view-exists-p [v1])
209 (clsql:drop-view [v1] :if-does-not-exist :ignore)
210 (clsql:view-exists-p [v1])))))
213 ;; create a view, list its attributes and drop it
214 (deftest :fddl/view/2
215 (with-dataset *ds-fddl*
216 (progn (clsql:create-view [v1]
217 :as [select [a] [c] [d]
221 (sort (mapcar #'string-downcase
222 (clsql:list-attributes [v1]))
224 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
227 ;; create a view, select stuff from it and drop it
228 (deftest :fddl/view/3
229 (with-dataset *ds-fddl*
231 (clsql:create-view [v1]
232 :as [select [a] [c] [d]
239 (clsql:select [a] [c]
243 (car (clsql:select [a] [c]
245 :where [= [a] 1])))))
247 (apply #'values result))
248 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
251 (deftest :fddl/view/4
252 (with-dataset *ds-fddl*
254 (clsql:create-view [v1]
255 :column-list '([x] [y] [z])
256 :as [select [a] [c] [d]
262 (sort (mapcar #'string-downcase
263 (clsql:list-attributes [v1]))
266 (clsql:select [x] [y]
270 (car (clsql:select [x] [y]
272 :where [= [x] 1])))))
274 (apply #'values result))
275 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
276 ("x" "y" "z") nil (1 "asdf"))
278 ;; create an index, test for existence, drop it and test again
279 (deftest :fddl/index/1
280 (with-dataset *ds-fddl*
281 (progn (clsql:create-index [bar] :on [alpha] :attributes
282 '([a] [c]) :unique t)
284 (clsql:index-exists-p [bar] )
286 (clsql:drop-index [bar] :on [alpha]
287 :if-does-not-exist :ignore)
288 (clsql:index-exists-p [bar])))))
291 ;; create indexes with names as strings, symbols and in square brackets
292 (deftest :fddl/index/2
293 (with-dataset *ds-fddl*
294 (let ((names '("foo" foo [foo]))
297 (clsql:create-index name :on [alpha] :attributes '([a]))
298 (push (clsql:index-exists-p name ) result)
299 (clsql:drop-index name :on [alpha] :if-does-not-exist :ignore))
300 (apply #'values result)))
303 ;; test list-indexes with keyword :ON
304 (deftest :fddl/index/3
306 (clsql:create-table [i3test] '(([a] (string 10))
308 (clsql:create-index [foo] :on [i3test] :attributes
310 (clsql:create-index [bar] :on [i3test] :attributes
313 (clsql:table-exists-p [i3test])
314 (clsql:index-exists-p [foo])
315 (clsql:index-exists-p [bar])
319 (clsql:list-indexes :on [i3test]))
322 (clsql:drop-index [bar] :on [i3test])
323 (clsql:drop-index [foo] :on [i3test])
324 (clsql:drop-table [i3test])
326 t t t ("bar" "foo") t)
328 ;; create an sequence, test for existence, drop it and test again
329 (deftest :fddl/sequence/1
330 (progn (clsql:create-sequence [foo])
332 (clsql:sequence-exists-p [foo])
334 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
335 (clsql:sequence-exists-p [foo]))))
338 ;; create and increment a sequence
339 (deftest :fddl/sequence/2
341 (clsql:create-sequence [foo])
342 (setf val1 (clsql:sequence-next [foo]))
344 (< val1 (clsql:sequence-next [foo]))
345 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
348 ;; explicitly set the value of a sequence
349 (deftest :fddl/sequence/3
351 (clsql:create-sequence [foo])
352 (clsql:set-sequence-position [foo] 5)
354 (clsql:sequence-next [foo])
355 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
360 (deftest :fddl/owner/1
361 (with-dataset *ds-fddl*
363 ;; user tables are an improper subset of all tables
364 (= (length (intersection (clsql:list-tables :owner nil)
365 (clsql:list-tables :owner :all)
367 (length (clsql:list-tables :owner nil)))
368 ;; user tables are a proper subset of all tables
369 (> (length (clsql:list-tables :owner :all))
370 (length (clsql:list-tables :owner nil)))))
373 (deftest :fddl/owner/table
374 (with-dataset *ds-fddl*
376 (clsql-sys:table-exists-p [alpha])
377 (clsql-sys:table-exists-p [alpha] :owner *test-database-user*)
378 (clsql-sys:table-exists-p [alpha] :owner *test-false-database-user*)))
381 (deftest :fddl/owner/attributes
382 (with-dataset *ds-fddl*
384 (length (clsql-sys:list-attributes [alpha]))
385 (length (clsql-sys:list-attributes [alpha] :owner *test-database-user*))
386 (length (clsql-sys:list-attributes [alpha] :owner *test-false-database-user*))))
389 (deftest :fddl/owner/attribute-types
390 (with-dataset *ds-fddl*
392 (length (clsql:list-attribute-types [alpha]))
393 (length (clsql:list-attribute-types [alpha] :owner *test-database-user*))
394 (length (clsql:list-attribute-types [alpha] :owner *test-false-database-user*))))
397 (deftest :fddl/owner/index
398 (with-dataset *ds-fddl*
399 (progn (clsql:create-index [bar] :on [alpha]
400 :attributes '([a] [c]))
402 (clsql:index-exists-p [bar] )
403 (clsql:index-exists-p [bar] :owner *test-database-user*)
404 (clsql:index-exists-p [bar] :owner *test-false-database-user*)
406 (length (clsql-sys:list-indexes :on [alpha]))
407 (length (clsql-sys:list-indexes :on [alpha] :owner *test-database-user*))
408 (length (clsql-sys:list-indexes :on [alpha] :owner *test-false-database-user*))
410 (clsql:drop-index [bar] :on [alpha]
411 :if-does-not-exist :ignore)
412 (clsql:index-exists-p [bar] :owner *test-database-user*))
413 (clsql:index-exists-p [bar] ))))
418 (deftest :fddl/owner/sequence
419 (progn (clsql:create-sequence [foo])
421 (clsql:sequence-exists-p [foo])
422 (clsql:sequence-exists-p [foo] :owner *test-database-user*)
423 (clsql:sequence-exists-p [foo] :owner *test-false-database-user*)
426 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
427 (clsql:sequence-exists-p [foo] ))))
432 (deftest :fddl/cache-table-queries/1
433 (with-dataset *ds-fddl*
435 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*))
437 (clsql:cache-table-queries "ALPHA" :action t)
438 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*)))
440 (clsql:list-attribute-types "ALPHA")
445 (clsql-sys::attribute-cache clsql:*default-database*))))))
447 (clsql:cache-table-queries "ALPHA" :action :flush)
448 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*)))))
449 (nil (t nil) t (t nil)))
453 #.(clsql:restore-sql-reader-syntax-state)