1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; *************************************************************************
3 ;;;; FILE IDENTIFICATION
5 ;;;; Name: test-fddl.lisp
6 ;;;; Purpose: Tests for the CLSQL Functional Data Definition Language
7 ;;;; Authors: Marcus Pearce and Kevin M. Rosenberg
8 ;;;; Created: March 2004
10 ;;;; This file is part of CLSQL.
12 ;;;; CLSQL users are granted the rights to distribute and use this software
13 ;;;; as governed by the terms of the Lisp Lesser GNU Public License
14 ;;;; (http://opensource.franz.com/preamble.html), also known as the LLGPL.
15 ;;;; *************************************************************************
18 (in-package #:clsql-tests)
19 (clsql-sys:file-enable-sql-reader-syntax)
21 (def-dataset *ds-fddl*
23 (create-table [alpha] '(([a] integer)
27 (create-table [bravo] '(([foo] integer)
29 (:sqldata "ALPHA" "A,C,D,F"
30 "1,'asdf','2010-01-01',3.14"
31 "2,'blarg','2012-12-21',0.1"
32 "3,'matey','1992-02-29',0.0")
33 (:cleanup "DROP TABLE ALPHA" "DROP TABLE BRAVO"))
35 (def-dataset *ds-fddl-parsing-oddities*
36 (:setup "CREATE TABLE ATEST (
39 (:cleanup "DROP TABLE ATEST"))
44 ;; list current tables
45 (deftest :fddl/table/1
46 (with-dataset *ds-fddl*
47 (sort (mapcar #'string-downcase
52 ;; create a table, test for its existence, drop it and test again
53 (deftest :fddl/table/2
54 (progn (clsql:create-table [foo]
58 ([comments] longchar)))
60 (clsql:table-exists-p [foo])
62 (clsql:drop-table [foo] :if-does-not-exist :ignore)
63 (clsql:table-exists-p [foo]))))
66 ;; create a table, list its attributes and drop it
67 (deftest :fddl/table/3
69 (progn (clsql:create-table [foo]
73 ([comments] longchar)))
75 (sort (mapcar #'string-downcase
76 (clsql:list-attributes [foo]))
78 (clsql:drop-table [foo] :if-does-not-exist :ignore))))
79 "comments" "height" "id" "name")
81 (deftest :fddl/table/4
83 (clsql:table-exists-p "MyMixedCase")
85 (clsql:create-table "MyMixedCase" '(([a] integer)))
86 (clsql:table-exists-p "MyMixedCase"))
88 (clsql:drop-table "MyMixedCase")
89 (clsql:table-exists-p "MyMixedCase")))
92 (deftest :fddl/table/5
95 (clsql:create-table "MyMixedCase" '(([a] integer)))
96 (clsql:execute-command "insert into \"MyMixedCase\" values (5)")
97 (clsql:insert-records :into "MyMixedCase" :values '(6))
98 (clsql:select [a] :from "MyMixedCase" :order-by '((a :asc))))
99 (clsql:drop-table "MyMixedCase"))
102 (deftest :fddl/table/6
104 (clsql:table-exists-p [foo])
106 (let ((*backend-warning-behavior*
107 (if (member *test-database-type*
108 '(:postgresql :postgresql-socket))
111 (case *test-database-underlying-type*
112 (:mssql (clsql:create-table [foo]
113 '(([bar] integer :not-null :primary-key)
114 ([baz] string :not-null :unique))))
115 (t (clsql:create-table [foo]
116 '(([bar] integer :not-null :unique :primary-key)
117 ([baz] string :not-null :unique))))))
118 (clsql:table-exists-p [foo]))
120 (clsql:drop-table [foo])
121 (clsql:table-exists-p [foo])))
124 (deftest :fddl/table/7
126 (clsql:table-exists-p [foo])
128 (let ((*backend-warning-behavior*
129 (if (member *test-database-type*
130 '(:postgresql :postgresql-socket))
133 (clsql:create-table [foo] '(([bar] integer :not-null)
134 ([baz] string :not-null))
135 :constraints '("UNIQUE (bar,baz)"
136 "PRIMARY KEY (bar)")))
137 (clsql:table-exists-p [foo]))
139 (clsql:drop-table [foo])
140 (clsql:table-exists-p [foo])))
143 (deftest :fddl/attributes/1
145 (with-dataset *ds-fddl*
147 (mapcar #'string-downcase
148 (clsql:list-attributes [alpha] ))
152 (deftest :fddl/attributes/2
153 (with-dataset *ds-fddl*
156 (mapcar #'(lambda (a) (string-downcase (car a)))
157 (clsql:list-attribute-types [alpha]))
161 ;; Attribute types are vendor specific so need to test a range
162 (deftest :fddl/attributes/3
163 (with-dataset *ds-fddl*
164 (and (member (clsql:attribute-type [a] [alpha]) '(:int :integer :int4 :number)) t))
167 (deftest :fddl/attributes/4
168 (with-dataset *ds-fddl*
169 (multiple-value-bind (type length scale nullable)
170 (clsql:attribute-type [c] [alpha])
171 (values (clsql-sys:in type :varchar :varchar2) length scale nullable)))
174 (deftest :fddl/attributes/5
175 (with-dataset *ds-fddl*
176 (and (member (clsql:attribute-type [d] [alpha])
177 '(:datetime :timestamp :date :smalldatetime)) 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)))