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
28 (sort (mapcar #'string-downcase
29 (clsql:list-tables :owner *test-database-user*))
31 "company" "employee" "type_table")
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/attributes/1
65 (mapcar #'string-downcase
66 (clsql:list-attributes [employee]
67 :owner *test-database-user*))
69 "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height"
70 "last_name" "managerid" "married")
72 (deftest :fddl/attributes/2
75 (mapcar #'(lambda (a) (string-downcase (car a)))
76 (clsql:list-attribute-types [employee]
77 :owner *test-database-user*))
79 "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height"
80 "last_name" "managerid" "married")
82 ;; create a view, test for existence, drop it and test again
84 (progn (clsql:create-view [lenins-group]
85 :as [select [first-name] [last-name] [email]
87 :where [= [managerid] 1]])
89 (clsql:view-exists-p [lenins-group] :owner *test-database-user*)
91 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
92 (clsql:view-exists-p [lenins-group] :owner *test-database-user*))))
95 ;; create a view, list its attributes and drop it
96 (when (clsql-base-sys:db-type-has-views? *test-database-underlying-type*)
98 (progn (clsql:create-view [lenins-group]
99 :as [select [first-name] [last-name] [email]
101 :where [= [managerid] 1]])
103 (sort (mapcar #'string-downcase
104 (clsql:list-attributes [lenins-group]))
106 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)))
107 ("email" "first_name" "last_name")))
109 ;; create a view, select stuff from it and drop it
110 (deftest :fddl/view/3
111 (progn (clsql:create-view [lenins-group]
112 :as [select [first-name] [last-name] [email]
114 :where [= [managerid] 1]])
118 (clsql:select [first-name] [last-name] [email]
120 :where [= [last-name] "Lenin"])
122 (car (clsql:select [first-name] [last-name] [email]
124 :where [= [last-name] "Stalin"])))))
125 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
126 (apply #'values result)))
127 nil ("Josef" "Stalin" "stalin@soviet.org"))
129 (deftest :fddl/view/4
130 (progn (clsql:create-view [lenins-group]
131 :column-list '([forename] [surname] [email])
132 :as [select [first-name] [last-name] [email]
134 :where [= [managerid] 1]])
138 (clsql:select [forename] [surname] [email]
140 :where [= [surname] "Lenin"])
142 (car (clsql:select [forename] [surname] [email]
144 :where [= [surname] "Stalin"])))))
145 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
146 (apply #'values result)))
147 nil ("Josef" "Stalin" "stalin@soviet.org"))
149 ;; create an index, test for existence, drop it and test again
150 (deftest :fddl/index/1
151 (progn (clsql:create-index [bar] :on [employee] :attributes
152 '([first-name] [last-name] [email]) :unique t)
154 (clsql:index-exists-p [bar] :owner *test-database-user*)
156 (clsql:drop-index [bar] :on [employee]
157 :if-does-not-exist :ignore)
158 (clsql:index-exists-p [bar] :owner *test-database-user*))))
161 ;; create indexes with names as strings, symbols and in square brackets
162 (deftest :fddl/index/2
163 (let ((names '("foo" foo [foo]))
166 (clsql:create-index name :on [employee] :attributes '([emplid]))
167 (push (clsql:index-exists-p name :owner *test-database-user*) result)
168 (clsql:drop-index name :on [employee] :if-does-not-exist :ignore))
169 (apply #'values result))
172 ;; test list-table-indexes
173 (deftest :fddl/index/3
175 (clsql:create-table [i3test] '(([a] (string 10))
177 (clsql:create-index [foo] :on [i3test] :attributes
179 (clsql:create-index [bar] :on [i3test] :attributes
182 (clsql:table-exists-p [i3test])
183 (clsql:index-exists-p [foo])
184 (clsql:index-exists-p [bar])
188 (clsql:list-table-indexes [i3test] :owner *test-database-user*))
190 (sort (clsql:list-table-indexes [company] :owner *test-database-user*)
193 (clsql:drop-index [bar] :on [i3test])
194 (clsql:drop-index [foo] :on [i3test])
195 (clsql:drop-table [i3test])
197 t t t ("bar" "foo") nil t)
199 ;; create an sequence, test for existence, drop it and test again
200 (deftest :fddl/sequence/1
201 (progn (clsql:create-sequence [foo])
203 (clsql:sequence-exists-p [foo] :owner *test-database-user*)
205 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
206 (clsql:sequence-exists-p [foo] :owner *test-database-user*))))
209 ;; create and increment a sequence
210 (deftest :fddl/sequence/2
212 (clsql:create-sequence [foo])
213 (setf val1 (clsql:sequence-next [foo]))
215 (< val1 (clsql:sequence-next [foo]))
216 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
219 ;; explicitly set the value of a sequence
220 (deftest :fddl/sequence/3
222 (clsql:create-sequence [foo])
223 (clsql:set-sequence-position [foo] 5)
225 (clsql:sequence-next [foo])
226 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
231 #.(clsql:restore-sql-reader-syntax-state)