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"))
39 ;; list current tables
40 (deftest :fddl/table/1
41 (with-dataset *ds-fddl*
42 (sort (mapcar #'string-downcase
47 ;; create a table, test for its existence, drop it and test again
48 (deftest :fddl/table/2
49 (progn (clsql:create-table [foo]
53 ([comments] longchar)))
55 (clsql:table-exists-p [foo])
57 (clsql:drop-table [foo] :if-does-not-exist :ignore)
58 (clsql:table-exists-p [foo]))))
61 ;; create a table, list its attributes and drop it
62 (deftest :fddl/table/3
64 (progn (clsql:create-table [foo]
68 ([comments] longchar)))
70 (sort (mapcar #'string-downcase
71 (clsql:list-attributes [foo]))
73 (clsql:drop-table [foo] :if-does-not-exist :ignore))))
74 "comments" "height" "id" "name")
76 (deftest :fddl/table/4
78 (clsql:table-exists-p "MyMixedCase")
80 (clsql:create-table "MyMixedCase" '(([a] integer)))
81 (clsql:table-exists-p "MyMixedCase"))
83 (clsql:drop-table "MyMixedCase")
84 (clsql:table-exists-p "MyMixedCase")))
87 (deftest :fddl/table/5
90 (clsql:create-table "MyMixedCase" '(([a] integer)))
91 (clsql:execute-command "insert into \"MyMixedCase\" values (5)")
92 (clsql:insert-records :into "MyMixedCase" :values '(6))
93 (clsql:select [a] :from "MyMixedCase" :order-by '((a :asc))))
94 (clsql:drop-table "MyMixedCase"))
97 (deftest :fddl/table/6
99 (clsql:table-exists-p [foo])
101 (let ((*backend-warning-behavior*
102 (if (member *test-database-type*
103 '(:postgresql :postgresql-socket))
106 (case *test-database-underlying-type*
107 (:mssql (clsql:create-table [foo]
108 '(([bar] integer :not-null :primary-key)
109 ([baz] string :not-null :unique))))
110 (t (clsql:create-table [foo]
111 '(([bar] integer :not-null :unique :primary-key)
112 ([baz] string :not-null :unique))))))
113 (clsql:table-exists-p [foo]))
115 (clsql:drop-table [foo])
116 (clsql:table-exists-p [foo])))
119 (deftest :fddl/table/7
121 (clsql:table-exists-p [foo])
123 (let ((*backend-warning-behavior*
124 (if (member *test-database-type*
125 '(:postgresql :postgresql-socket))
128 (clsql:create-table [foo] '(([bar] integer :not-null)
129 ([baz] string :not-null))
130 :constraints '("UNIQUE (bar,baz)"
131 "PRIMARY KEY (bar)")))
132 (clsql:table-exists-p [foo]))
134 (clsql:drop-table [foo])
135 (clsql:table-exists-p [foo])))
138 (deftest :fddl/attributes/1
140 (with-dataset *ds-fddl*
142 (mapcar #'string-downcase
143 (clsql:list-attributes [alpha] ))
147 (deftest :fddl/attributes/2
148 (with-dataset *ds-fddl*
151 (mapcar #'(lambda (a) (string-downcase (car a)))
152 (clsql:list-attribute-types [alpha]))
156 ;; Attribute types are vendor specific so need to test a range
157 (deftest :fddl/attributes/3
158 (with-dataset *ds-fddl*
159 (and (member (clsql:attribute-type [a] [alpha]) '(:int :integer :int4 :number)) t))
162 (deftest :fddl/attributes/4
163 (with-dataset *ds-fddl*
164 (multiple-value-bind (type length scale nullable)
165 (clsql:attribute-type [c] [alpha])
166 (values (clsql-sys:in type :varchar :varchar2) length scale nullable)))
169 (deftest :fddl/attributes/5
170 (with-dataset *ds-fddl*
171 (and (member (clsql:attribute-type [d] [alpha]) '(:datetime :timestamp :date)) t))
174 (deftest :fddl/attributes/6
175 (with-dataset *ds-fddl*
176 (and (member (clsql:attribute-type [f] [alpha]) '(:float :float8 :number)) t))
179 (deftest :fddl/attributes/7
180 (with-dataset *ds-bigint*
181 (and (member (clsql:attribute-type [t_bigint] [TYPE_BIGINT]) '(:bigint :int8)) t))
185 ;; create a view, test for existence, drop it and test again
186 (deftest :fddl/view/1
187 (with-dataset *ds-fddl*
188 (progn (clsql:create-view [v1]
189 :as [select [a] [c] [d]
193 (clsql:view-exists-p [v1])
195 (clsql:drop-view [v1] :if-does-not-exist :ignore)
196 (clsql:view-exists-p [v1])))))
199 ;; create a view, list its attributes and drop it
200 (deftest :fddl/view/2
201 (with-dataset *ds-fddl*
202 (progn (clsql:create-view [v1]
203 :as [select [a] [c] [d]
207 (sort (mapcar #'string-downcase
208 (clsql:list-attributes [v1]))
210 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
213 ;; create a view, select stuff from it and drop it
214 (deftest :fddl/view/3
215 (with-dataset *ds-fddl*
217 (clsql:create-view [v1]
218 :as [select [a] [c] [d]
225 (clsql:select [a] [c]
229 (car (clsql:select [a] [c]
231 :where [= [a] 1])))))
233 (apply #'values result))
234 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
237 (deftest :fddl/view/4
238 (with-dataset *ds-fddl*
240 (clsql:create-view [v1]
241 :column-list '([x] [y] [z])
242 :as [select [a] [c] [d]
248 (sort (mapcar #'string-downcase
249 (clsql:list-attributes [v1]))
252 (clsql:select [x] [y]
256 (car (clsql:select [x] [y]
258 :where [= [x] 1])))))
260 (apply #'values result))
261 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
262 ("x" "y" "z") nil (1 "asdf"))
264 ;; create an index, test for existence, drop it and test again
265 (deftest :fddl/index/1
266 (with-dataset *ds-fddl*
267 (progn (clsql:create-index [bar] :on [alpha] :attributes
268 '([a] [c]) :unique t)
270 (clsql:index-exists-p [bar] )
272 (clsql:drop-index [bar] :on [alpha]
273 :if-does-not-exist :ignore)
274 (clsql:index-exists-p [bar])))))
277 ;; create indexes with names as strings, symbols and in square brackets
278 (deftest :fddl/index/2
279 (with-dataset *ds-fddl*
280 (let ((names '("foo" foo [foo]))
283 (clsql:create-index name :on [alpha] :attributes '([a]))
284 (push (clsql:index-exists-p name ) result)
285 (clsql:drop-index name :on [alpha] :if-does-not-exist :ignore))
286 (apply #'values result)))
289 ;; test list-indexes with keyword :ON
290 (deftest :fddl/index/3
292 (clsql:create-table [i3test] '(([a] (string 10))
294 (clsql:create-index [foo] :on [i3test] :attributes
296 (clsql:create-index [bar] :on [i3test] :attributes
299 (clsql:table-exists-p [i3test])
300 (clsql:index-exists-p [foo])
301 (clsql:index-exists-p [bar])
305 (clsql:list-indexes :on [i3test]))
308 (clsql:drop-index [bar] :on [i3test])
309 (clsql:drop-index [foo] :on [i3test])
310 (clsql:drop-table [i3test])
312 t t t ("bar" "foo") t)
314 ;; create an sequence, test for existence, drop it and test again
315 (deftest :fddl/sequence/1
316 (progn (clsql:create-sequence [foo])
318 (clsql:sequence-exists-p [foo])
320 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
321 (clsql:sequence-exists-p [foo]))))
324 ;; create and increment a sequence
325 (deftest :fddl/sequence/2
327 (clsql:create-sequence [foo])
328 (setf val1 (clsql:sequence-next [foo]))
330 (< val1 (clsql:sequence-next [foo]))
331 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
334 ;; explicitly set the value of a sequence
335 (deftest :fddl/sequence/3
337 (clsql:create-sequence [foo])
338 (clsql:set-sequence-position [foo] 5)
340 (clsql:sequence-next [foo])
341 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
346 (deftest :fddl/owner/1
347 (with-dataset *ds-fddl*
349 ;; user tables are an improper subset of all tables
350 (= (length (intersection (clsql:list-tables :owner nil)
351 (clsql:list-tables :owner :all)
353 (length (clsql:list-tables :owner nil)))
354 ;; user tables are a proper subset of all tables
355 (> (length (clsql:list-tables :owner :all))
356 (length (clsql:list-tables :owner nil)))))
359 (deftest :fddl/owner/table
360 (with-dataset *ds-fddl*
362 (clsql-sys:table-exists-p [alpha])
363 (clsql-sys:table-exists-p [alpha] :owner *test-database-user*)
364 (clsql-sys:table-exists-p [alpha] :owner *test-false-database-user*)))
367 (deftest :fddl/owner/attributes
368 (with-dataset *ds-fddl*
370 (length (clsql-sys:list-attributes [alpha]))
371 (length (clsql-sys:list-attributes [alpha] :owner *test-database-user*))
372 (length (clsql-sys:list-attributes [alpha] :owner *test-false-database-user*))))
375 (deftest :fddl/owner/attribute-types
376 (with-dataset *ds-fddl*
378 (length (clsql:list-attribute-types [alpha]))
379 (length (clsql:list-attribute-types [alpha] :owner *test-database-user*))
380 (length (clsql:list-attribute-types [alpha] :owner *test-false-database-user*))))
383 (deftest :fddl/owner/index
384 (with-dataset *ds-fddl*
385 (progn (clsql:create-index [bar] :on [alpha]
386 :attributes '([a] [c]))
388 (clsql:index-exists-p [bar] )
389 (clsql:index-exists-p [bar] :owner *test-database-user*)
390 (clsql:index-exists-p [bar] :owner *test-false-database-user*)
392 (length (clsql-sys:list-indexes :on [alpha]))
393 (length (clsql-sys:list-indexes :on [alpha] :owner *test-database-user*))
394 (length (clsql-sys:list-indexes :on [alpha] :owner *test-false-database-user*))
396 (clsql:drop-index [bar] :on [alpha]
397 :if-does-not-exist :ignore)
398 (clsql:index-exists-p [bar] :owner *test-database-user*))
399 (clsql:index-exists-p [bar] ))))
404 (deftest :fddl/owner/sequence
405 (progn (clsql:create-sequence [foo])
407 (clsql:sequence-exists-p [foo])
408 (clsql:sequence-exists-p [foo] :owner *test-database-user*)
409 (clsql:sequence-exists-p [foo] :owner *test-false-database-user*)
412 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
413 (clsql:sequence-exists-p [foo] ))))
418 (deftest :fddl/cache-table-queries/1
419 (with-dataset *ds-fddl*
421 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*))
423 (clsql:cache-table-queries "ALPHA" :action t)
424 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*)))
426 (clsql:list-attribute-types "ALPHA")
431 (clsql-sys::attribute-cache clsql:*default-database*))))))
433 (clsql:cache-table-queries "ALPHA" :action :flush)
434 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*)))))
435 (nil (t nil) t (t nil)))
439 #.(clsql:restore-sql-reader-syntax-state)