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)
25 ;; list current tables
26 (deftest :fddl/table/1
27 (sort (mapcar #'string-downcase
28 (clsql:list-tables :owner *test-database-user*))
30 ("addr" "big" "company" "ea_join" "employee" "node" "setting"
31 "subloc" "theme" "type_bigint" "type_table" "user"))
33 ;; create a table, test for its existence, drop it and test again
34 (deftest :fddl/table/2
35 (progn (clsql:create-table [foo]
39 ([comments] longchar)))
41 (clsql:table-exists-p [foo] :owner *test-database-user*)
43 (clsql:drop-table [foo] :if-does-not-exist :ignore)
44 (clsql:table-exists-p [foo] :owner *test-database-user*))))
47 ;; create a table, list its attributes and drop it
48 (deftest :fddl/table/3
50 (progn (clsql:create-table [foo]
54 ([comments] longchar)))
56 (sort (mapcar #'string-downcase
57 (clsql:list-attributes [foo]))
59 (clsql:drop-table [foo] :if-does-not-exist :ignore))))
60 "comments" "height" "id" "name")
62 (deftest :fddl/table/4
64 (clsql:table-exists-p "MyMixedCase")
66 (clsql:create-table "MyMixedCase" '(([a] integer)))
67 (clsql:table-exists-p "MyMixedCase"))
69 (clsql:drop-table "MyMixedCase")
70 (clsql:table-exists-p "MyMixedCase")))
73 (deftest :fddl/table/5
76 (clsql:create-table "MyMixedCase" '(([a] integer)))
77 (clsql:execute-command "insert into \"MyMixedCase\" values (5)")
78 (clsql:insert-records :into "MyMixedCase" :values '(6))
79 (clsql:select [a] :from "MyMixedCase" :order-by '((a :asc))))
80 (clsql:drop-table "MyMixedCase"))
83 (deftest :fddl/table/6
85 (clsql:table-exists-p [foo])
87 (let ((*backend-warning-behavior*
88 (if (member *test-database-type*
89 '(:postgresql :postgresql-socket))
92 (case *test-database-underlying-type*
93 (:mssql (clsql:create-table [foo]
94 '(([bar] integer :not-null :primary-key)
95 ([baz] string :not-null :unique))))
96 (t (clsql:create-table [foo]
97 '(([bar] integer :not-null :unique :primary-key)
98 ([baz] string :not-null :unique))))))
99 (clsql:table-exists-p [foo]))
101 (clsql:drop-table [foo])
102 (clsql:table-exists-p [foo])))
105 (deftest :fddl/table/7
107 (clsql:table-exists-p [foo])
109 (let ((*backend-warning-behavior*
110 (if (member *test-database-type*
111 '(:postgresql :postgresql-socket))
114 (clsql:create-table [foo] '(([bar] integer :not-null)
115 ([baz] string :not-null))
116 :constraints '("UNIQUE (bar,baz)"
117 "PRIMARY KEY (bar)")))
118 (clsql:table-exists-p [foo]))
120 (clsql:drop-table [foo])
121 (clsql:table-exists-p [foo])))
124 (deftest :fddl/attributes/1
127 (mapcar #'string-downcase
128 (clsql:list-attributes [employee]
129 :owner *test-database-user*))
131 "bd_utime" "birthday" "ecompanyid" "email" "emplid" "first_name" "groupid" "height"
132 "last_name" "managerid" "married")
134 (deftest :fddl/attributes/2
137 (mapcar #'(lambda (a) (string-downcase (car a)))
138 (clsql:list-attribute-types [employee]
139 :owner *test-database-user*))
141 "bd_utime" "birthday" "ecompanyid" "email" "emplid" "first_name" "groupid" "height"
142 "last_name" "managerid" "married")
144 ;; Attribute types are vendor specific so need to test a range
145 (deftest :fddl/attributes/3
146 (and (member (clsql:attribute-type [emplid] [employee]) '(:int :integer :int4 :number)) t)
149 (deftest :fddl/attributes/4
150 (multiple-value-bind (type length scale nullable)
151 (clsql:attribute-type [first-name] [employee])
152 (values (clsql-sys:in type :varchar :varchar2) length scale nullable))
155 (deftest :fddl/attributes/5
156 (and (member (clsql:attribute-type [birthday] [employee]) '(:datetime :timestamp :date)) t)
159 (deftest :fddl/attributes/6
160 (and (member (clsql:attribute-type [height] [employee]) '(:float :float8 :number)) t)
163 (deftest :fddl/attributes/7
164 (and (member (clsql:attribute-type [bd_utime] [employee]) '(:bigint :int8 :char)) t)
168 ;; create a view, test for existence, drop it and test again
169 (deftest :fddl/view/1
170 (progn (clsql:create-view [lenins-group]
171 :as [select [first-name] [last-name] [email]
173 :where [= [managerid] 1]])
175 (clsql:view-exists-p [lenins-group] :owner *test-database-user*)
177 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
178 (clsql:view-exists-p [lenins-group] :owner *test-database-user*))))
181 ;; create a view, list its attributes and drop it
182 (when (clsql-sys:db-type-has-views? *test-database-underlying-type*)
183 (deftest :fddl/view/2
184 (progn (clsql:create-view [lenins-group]
185 :as [select [first-name] [last-name] [email]
187 :where [= [managerid] 1]])
189 (sort (mapcar #'string-downcase
190 (clsql:list-attributes [lenins-group]))
192 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)))
193 ("email" "first_name" "last_name")))
195 ;; create a view, select stuff from it and drop it
196 (deftest :fddl/view/3
197 (progn (clsql:create-view [lenins-group]
198 :as [select [first-name] [last-name] [email]
200 :where [= [managerid] 1]])
204 (clsql:select [first-name] [last-name] [email]
206 :where [= [last-name] "Lenin"])
208 (car (clsql:select [first-name] [last-name] [email]
210 :where [= [last-name] "Stalin"])))))
211 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
212 (apply #'values result)))
213 nil ("Josef" "Stalin" "stalin@soviet.org"))
215 (deftest :fddl/view/4
216 (progn (clsql:create-view [lenins-group]
217 :column-list '([forename] [surname] [email])
218 :as [select [first-name] [last-name] [email]
220 :where [= [managerid] 1]])
224 (clsql:select [forename] [surname] [email]
226 :where [= [surname] "Lenin"])
228 (car (clsql:select [forename] [surname] [email]
230 :where [= [surname] "Stalin"])))))
231 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
232 (apply #'values result)))
233 nil ("Josef" "Stalin" "stalin@soviet.org"))
235 ;; create an index, test for existence, drop it and test again
236 (deftest :fddl/index/1
237 (progn (clsql:create-index [bar] :on [employee] :attributes
238 '([first-name] [last-name] [email]) :unique t)
240 (clsql:index-exists-p [bar] :owner *test-database-user*)
242 (clsql:drop-index [bar] :on [employee]
243 :if-does-not-exist :ignore)
244 (clsql:index-exists-p [bar] :owner *test-database-user*))))
247 ;; create indexes with names as strings, symbols and in square brackets
248 (deftest :fddl/index/2
249 (let ((names '("foo" foo [foo]))
252 (clsql:create-index name :on [employee] :attributes '([last-name]))
253 (push (clsql:index-exists-p name :owner *test-database-user*) result)
254 (clsql:drop-index name :on [employee] :if-does-not-exist :ignore))
255 (apply #'values result))
258 ;; test list-indexes with keyword :ON
259 (deftest :fddl/index/3
261 (clsql:create-table [i3test] '(([a] (string 10))
263 (clsql:create-index [foo] :on [i3test] :attributes
265 (clsql:create-index [bar] :on [i3test] :attributes
268 (clsql:table-exists-p [i3test])
269 (clsql:index-exists-p [foo])
270 (clsql:index-exists-p [bar])
274 (clsql:list-indexes :on [i3test] :owner *test-database-user*))
277 (clsql:drop-index [bar] :on [i3test])
278 (clsql:drop-index [foo] :on [i3test])
279 (clsql:drop-table [i3test])
281 t t t ("bar" "foo") t)
283 ;; create an sequence, test for existence, drop it and test again
284 (deftest :fddl/sequence/1
285 (progn (clsql:create-sequence [foo])
287 (clsql:sequence-exists-p [foo] :owner *test-database-user*)
289 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
290 (clsql:sequence-exists-p [foo] :owner *test-database-user*))))
293 ;; create and increment a sequence
294 (deftest :fddl/sequence/2
296 (clsql:create-sequence [foo])
297 (setf val1 (clsql:sequence-next [foo]))
299 (< val1 (clsql:sequence-next [foo]))
300 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
303 ;; explicitly set the value of a sequence
304 (deftest :fddl/sequence/3
306 (clsql:create-sequence [foo])
307 (clsql:set-sequence-position [foo] 5)
309 (clsql:sequence-next [foo])
310 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
314 (let ((rows (clsql:select [*] :from [big] :order-by [i] :field-names nil)))
319 (rest rows (cdr rest)))
320 ((= i (length rows)) t)
322 (int (first (car rest)))
323 (bigint (second (car rest))))
324 (when (and (or (eq *test-database-type* :oracle)
325 (and (eq *test-database-type* :odbc)
326 (eq *test-database-underlying-type* :postgresql)))
328 (setf bigint (parse-integer bigint)))
329 (unless (and (eql int index)
330 (eql bigint (truncate max index)))
334 (deftest :fddl/owner/1
336 ;; user tables are an improper subset of all tables
337 (= (length (intersection (clsql:list-tables :owner nil)
338 (clsql:list-tables :owner :all)
340 (length (clsql:list-tables :owner nil)))
341 ;; user tables are a proper subset of all tables
342 (> (length (clsql:list-tables :owner :all))
343 (length (clsql:list-tables :owner nil))))
346 (deftest :fddl/cache-table-queries/1
348 (gethash "EMPLOYEE" (clsql-sys::attribute-cache clsql:*default-database*))
350 (clsql:cache-table-queries "EMPLOYEE" :action t)
351 (gethash "EMPLOYEE" (clsql-sys::attribute-cache clsql:*default-database*)))
353 (clsql:list-attribute-types "EMPLOYEE")
358 (clsql-sys::attribute-cache clsql:*default-database*))))))
360 (clsql:cache-table-queries "EMPLOYEE" :action :flush)
361 (gethash "EMPLOYEE" (clsql-sys::attribute-cache clsql:*default-database*))))
362 (nil (t nil) t (t nil)))
366 #.(clsql:restore-sql-reader-syntax-state)