1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; ======================================================================
3 ;;;; File: test-fddl.lisp
4 ;;;; Author: Marcus Pearce <m.t.pearce@city.ac.uk>
5 ;;;; Created: 30/03/2004
7 ;;;; ======================================================================
9 ;;;; Description ==========================================================
10 ;;;; ======================================================================
12 ;;;; Tests for the CLSQL Functional Data Definition Language
15 ;;;; ======================================================================
17 (in-package #:clsql-tests)
19 #.(clsql:locally-enable-sql-reader-syntax)
24 ;; list current tables
25 (deftest :fddl/table/1
27 (sort (mapcar #'string-downcase
28 (clsql:list-tables :owner *test-database-user*))
30 "employee" "company" "clsql_object_v")
32 ;; create a table, test for its existence, drop it and test again
33 (deftest :fddl/table/2
34 (progn (clsql:create-table [foo]
38 ([comments] longchar)))
40 (clsql:table-exists-p [foo] :owner *test-database-user*)
42 (clsql:drop-table [foo] :if-does-not-exist :ignore)
43 (clsql:table-exists-p [foo] :owner *test-database-user*))))
46 ;; create a table, list its attributes and drop it
47 (deftest :fddl/table/3
49 (progn (clsql:create-table [foo]
53 ([comments] longchar)))
55 (sort (mapcar #'string-downcase
56 (clsql:list-attributes [foo]))
58 (clsql:drop-table [foo] :if-does-not-exist :ignore))))
59 "comments" "height" "id" "name")
61 (deftest :fddl/attributes/1
64 (mapcar #'string-downcase
65 (clsql:list-attributes [employee]
66 :owner *test-database-user*))
68 "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height"
69 "last_name" "managerid" "married")
71 (deftest :fddl/attributes/2
74 (mapcar #'(lambda (a) (string-downcase (car a)))
75 (clsql:list-attribute-types [employee]
76 :owner *test-database-user*))
78 "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height"
79 "last_name" "managerid" "married")
81 ;; create a view, test for existence, drop it and test again
83 (progn (clsql:create-view [lenins-group]
85 ;;:column-list '([forename] [surname] [email])
86 :as [select [first-name] [last-name] [email]
88 :where [= [managerid] 1]])
90 (clsql:view-exists-p [lenins-group] :owner *test-database-user*)
92 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
93 (clsql:view-exists-p [lenins-group] :owner *test-database-user*))))
96 ;; create a view, list its attributes and drop it
98 (progn (clsql:create-view [lenins-group]
100 ;;:column-list '([forename] [surname] [email])
101 :as [select [first-name] [last-name] [email]
103 :where [= [managerid] 1]])
105 (sort (mapcar #'string-downcase
106 (clsql:list-attributes [lenins-group]))
108 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)))
109 ("email" "first_name" "last_name"))
111 ;; create a view, select stuff from it and drop it
112 (deftest :fddl/view/3
113 (progn (clsql:create-view [lenins-group]
114 :as [select [first-name] [last-name] [email]
116 :where [= [managerid] 1]])
120 (clsql:select [first-name] [last-name] [email]
122 :where [= [last-name] "Lenin"])
124 (car (clsql:select [first-name] [last-name] [email]
126 :where [= [last-name] "Stalin"])))))
127 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
128 (apply #'values result)))
129 nil ("Josef" "Stalin" "stalin@soviet.org"))
132 (deftest :fddl/view/4
133 (if (eql *test-database-type* :sqlite)
134 (values nil '(("Josef" "Stalin" "stalin@soviet.org")))
135 (progn (clsql:create-view [lenins-group]
136 :column-list '([forename] [surname] [email])
137 :as [select [first-name] [last-name] [email]
139 :where [= [managerid] 1]])
143 (clsql:select [forename] [surname] [email]
145 :where [= [surname] "Lenin"])
147 (car (clsql:select [forename] [surname] [email]
149 :where [= [surname] "Stalin"])))))
150 (clsql:drop-view [lenins-group] :if-does-not-exist :ignore)
151 (apply #'values result))))
152 nil ("Josef" "Stalin" "stalin@soviet.org"))
154 ;; create an index, test for existence, drop it and test again
155 (deftest :fddl/index/1
156 (progn (clsql:create-index [bar] :on [employee] :attributes
157 '([first-name] [last-name] [email]) :unique t)
159 (clsql:index-exists-p [bar] :owner *test-database-user*)
161 (case *test-database-type*
163 (clsql:drop-index [bar] :on [employee]
164 :if-does-not-exist :ignore))
166 (clsql:drop-index [bar]:if-does-not-exist :ignore)))
167 (clsql:view-exists-p [bar] :owner *test-database-user*))))
170 ;; create indexes with names as strings, symbols and in square brackets
171 (deftest :fddl/index/2
172 (let ((names '("foo" foo [foo]))
175 (clsql:create-index name :on [employee] :attributes '([emplid]))
176 (push (clsql:index-exists-p name :owner *test-database-user*) result)
177 (case *test-database-type*
179 (clsql:drop-index name :on [employee] :if-does-not-exist :ignore))
180 (t (clsql:drop-index name :if-does-not-exist :ignore))))
181 (apply #'values result))
184 ;; create an sequence, test for existence, drop it and test again
185 (deftest :fddl/sequence/1
186 (progn (clsql:create-sequence [foo])
188 (clsql:sequence-exists-p [foo] :owner *test-database-user*)
190 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)
191 (clsql:sequence-exists-p [foo] :owner *test-database-user*))))
194 ;; create and increment a sequence
195 (deftest :fddl/sequence/2
197 (clsql:create-sequence [foo])
198 (setf val1 (clsql:sequence-next [foo]))
200 (< val1 (clsql:sequence-next [foo]))
201 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
204 ;; explicitly set the value of a sequence
205 (deftest :fddl/sequence/3
207 (clsql:create-sequence [foo])
208 (clsql:set-sequence-position [foo] 5)
210 (clsql:sequence-next [foo])
211 (clsql:drop-sequence [foo] :if-does-not-exist :ignore)))
216 #.(clsql:restore-sql-reader-syntax-state)