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)
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])
178 '(:datetime :timestamp :date :smalldatetime)) t))
181 (deftest :fddl/attributes/6
182 (with-dataset *ds-fddl*
183 (and (member (clsql:attribute-type [f] [alpha]) '(:float :float8 :number)) t))
186 (deftest :fddl/attributes/7
187 (with-dataset *ds-bigint*
188 (and (member (clsql:attribute-type [t_bigint] [TYPE_BIGINT]) '(:bigint :int8)) t))
191 (deftest :fddl/attributes/8
192 ;;this is mostly from sqlite3 sending back
193 (with-dataset *ds-fddl-parsing-oddities*
195 (clsql-sys:in (clsql:attribute-type [a] [atest]) :varchar :varchar2)
196 (clsql-sys:in (clsql:attribute-type [b] [atest]) :varchar :varchar2)))
200 ;; create a view, test for existence, drop it and test again
201 (deftest :fddl/view/1
202 (with-dataset *ds-fddl*
203 (progn (clsql:create-view [v1]
204 :as [select [a] [c] [d]
208 (clsql:view-exists-p [v1])
210 (clsql:drop-view [v1] :if-does-not-exist :ignore)
211 (clsql:view-exists-p [v1])))))
214 ;; create a view, list its attributes and drop it
215 (deftest :fddl/view/2
216 (with-dataset *ds-fddl*
217 (progn (clsql:create-view [v1]
218 :as [select [a] [c] [d]
222 (sort (mapcar #'string-downcase
223 (clsql:list-attributes [v1]))
225 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
228 ;; create a view, select stuff from it and drop it
229 (deftest :fddl/view/3
230 (with-dataset *ds-fddl*
232 (clsql:create-view [v1]
233 :as [select [a] [c] [d]
240 (clsql:select [a] [c]
244 (car (clsql:select [a] [c]
246 :where [= [a] 1])))))
248 (apply #'values result))
249 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
252 (deftest :fddl/view/4
253 (with-dataset *ds-fddl*
255 (clsql:create-view [v1]
256 :column-list '([x] [y] [z])
257 :as [select [a] [c] [d]
263 (sort (mapcar #'string-downcase
264 (clsql:list-attributes [v1]))
267 (clsql:select [x] [y]
271 (car (clsql:select [x] [y]
273 :where [= [x] 1])))))
275 (apply #'values result))
276 (clsql:drop-view [v1] :if-does-not-exist :ignore))))
277 ("x" "y" "z") nil (1 "asdf"))
279 ;; create an index, test for existence, drop it and test again
280 (deftest :fddl/index/1
281 (with-dataset *ds-fddl*
282 (progn (clsql:create-index [bar] :on [alpha] :attributes
283 '([a] [c]) :unique t)
285 (clsql:index-exists-p [bar] )
287 (clsql:drop-index [bar] :on [alpha]
288 :if-does-not-exist :ignore)
289 (clsql:index-exists-p [bar])))))
292 ;; create indexes with names as strings, symbols and in square brackets
293 (deftest :fddl/index/2
294 (with-dataset *ds-fddl*
295 (let ((names '("foo" foo [foo]))
298 (clsql:create-index name :on [alpha] :attributes '([a]))
299 (push (clsql:index-exists-p name ) result)
300 (clsql:drop-index name :on [alpha] :if-does-not-exist :ignore))
301 (apply #'values result)))
304 ;; test list-indexes with keyword :ON
305 (deftest :fddl/index/3
307 (clsql:create-table [i3test] '(([a] (string 10))
309 (clsql:create-index [foo] :on [i3test] :attributes
311 (clsql:create-index [bar] :on [i3test] :attributes
314 (clsql:table-exists-p [i3test])
315 (clsql:index-exists-p [foo])
316 (clsql:index-exists-p [bar])
320 (clsql:list-indexes :on [i3test]))
323 (clsql:drop-index [bar] :on [i3test])
324 (clsql:drop-index [foo] :on [i3test])
325 (clsql:drop-table [i3test])
327 t t t ("bar" "foo") t)
329 ;; create an sequence, test for existence, drop it and test again
330 (deftest :fddl/sequence/1
331 (progn (clsql:create-sequence [foo])
333 (clsql:sequence-exists-p [foo])
335 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
336 (clsql:sequence-exists-p [foo]))))
339 ;; create and increment a sequence
340 (deftest :fddl/sequence/2
342 (clsql:create-sequence [foo])
343 (setf val1 (clsql:sequence-next [foo]))
345 (< val1 (clsql:sequence-next [foo]))
346 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
349 ;; explicitly set the value of a sequence
350 (deftest :fddl/sequence/3
352 (clsql:create-sequence [foo])
353 (clsql:set-sequence-position [foo] 5)
355 (clsql:sequence-next [foo])
356 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
361 (deftest :fddl/owner/1
362 (with-dataset *ds-fddl*
364 ;; user tables are an improper subset of all tables
365 (= (length (intersection (clsql:list-tables :owner nil)
366 (clsql:list-tables :owner :all)
368 (length (clsql:list-tables :owner nil)))
369 ;; user tables are a proper subset of all tables
370 (> (length (clsql:list-tables :owner :all))
371 (length (clsql:list-tables :owner nil)))))
374 (deftest :fddl/owner/table
375 (with-dataset *ds-fddl*
377 (clsql-sys:table-exists-p [alpha])
378 (clsql-sys:table-exists-p [alpha] :owner *test-database-user*)
379 (clsql-sys:table-exists-p [alpha] :owner *test-false-database-user*)))
382 (deftest :fddl/owner/attributes
383 (with-dataset *ds-fddl*
385 (length (clsql-sys:list-attributes [alpha]))
386 (length (clsql-sys:list-attributes [alpha] :owner *test-database-user*))
387 (length (clsql-sys:list-attributes [alpha] :owner *test-false-database-user*))))
390 (deftest :fddl/owner/attribute-types
391 (with-dataset *ds-fddl*
393 (length (clsql:list-attribute-types [alpha]))
394 (length (clsql:list-attribute-types [alpha] :owner *test-database-user*))
395 (length (clsql:list-attribute-types [alpha] :owner *test-false-database-user*))))
398 (deftest :fddl/owner/index
399 (with-dataset *ds-fddl*
400 (progn (clsql:create-index [bar] :on [alpha]
401 :attributes '([a] [c]))
403 (clsql:index-exists-p [bar] )
404 (clsql:index-exists-p [bar] :owner *test-database-user*)
405 (clsql:index-exists-p [bar] :owner *test-false-database-user*)
407 (length (clsql-sys:list-indexes :on [alpha]))
408 (length (clsql-sys:list-indexes :on [alpha] :owner *test-database-user*))
409 (length (clsql-sys:list-indexes :on [alpha] :owner *test-false-database-user*))
411 (clsql:drop-index [bar] :on [alpha]
412 :if-does-not-exist :ignore)
413 (clsql:index-exists-p [bar] :owner *test-database-user*))
414 (clsql:index-exists-p [bar] ))))
419 (deftest :fddl/owner/sequence
420 (progn (clsql:create-sequence [foo])
422 (clsql:sequence-exists-p [foo])
423 (clsql:sequence-exists-p [foo] :owner *test-database-user*)
424 (clsql:sequence-exists-p [foo] :owner *test-false-database-user*)
427 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
428 (clsql:sequence-exists-p [foo] ))))
433 (deftest :fddl/cache-table-queries/1
434 (with-dataset *ds-fddl*
436 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*))
438 (clsql:cache-table-queries "ALPHA" :action t)
439 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*)))
441 (clsql:list-attribute-types "ALPHA")
446 (clsql-sys::attribute-cache clsql:*default-database*))))))
448 (clsql:cache-table-queries "ALPHA" :action :flush)
449 (gethash "ALPHA" (clsql-sys::attribute-cache clsql:*default-database*)))))
450 (nil (t nil) t (t nil)))
454 #.(clsql:restore-sql-reader-syntax-state)