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
6 ;;;; Updated: <04/04/2004 11:53:29 marcusp>
7 ;;;; ======================================================================
9 ;;;; Description ==========================================================
10 ;;;; ======================================================================
12 ;;;; Tests for the CLSQL-USQL Functional Data Definition Language
15 ;;;; ======================================================================
17 (in-package :clsql-usql-tests)
19 #.(usql:locally-enable-sql-reader-syntax)
21 ;; list current tables
22 (deftest :fddl/table/1
24 (sort (mapcar #'string-downcase
25 (usql:list-tables :owner *test-database-user*))
27 "usql_object_v" "employee" "company")
29 ;; create a table, test for its existence, drop it and test again
30 (deftest :fddl/table/2
31 (progn (usql:create-table [foo]
35 ([comments] longchar)))
37 (usql:table-exists-p [foo] :owner *test-database-user*)
39 (usql:drop-table [foo] :if-does-not-exist :ignore)
40 (usql:table-exists-p [foo] :owner *test-database-user*))))
43 ;; create a table, list its attributes and drop it
44 (deftest :fddl/table/3
46 (progn (usql:create-table [foo]
50 ([comments] longchar)))
52 (sort (mapcar #'string-downcase
53 (usql:list-attributes [foo]))
55 (usql:drop-table [foo] :if-does-not-exist :ignore))))
56 "comments" "height" "id" "name")
58 (deftest :fddl/attributes/1
61 (mapcar #'string-downcase
62 (usql:list-attributes [employee]
63 :owner *test-database-user*))
65 "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height"
66 "last_name" "managerid" "married")
68 (deftest :fddl/attributes/2
71 (mapcar #'(lambda (a) (string-downcase (car a)))
72 (usql:list-attribute-types [employee]
73 :owner *test-database-user*))
75 "birthday" "companyid" "email" "emplid" "first_name" "groupid" "height"
76 "last_name" "managerid" "married")
78 ;; create a view, test for existence, drop it and test again
80 (progn (usql:create-view [lenins-group]
82 ;;:column-list '([forename] [surname] [email])
83 :as [select [first-name] [last-name] [email]
85 :where [= [managerid] 1]])
87 (usql:view-exists-p [lenins-group] :owner *test-database-user*)
89 (usql:drop-view [lenins-group] :if-does-not-exist :ignore)
90 (usql:view-exists-p [lenins-group] :owner *test-database-user*))))
93 ;; create a view, list its attributes and drop it
95 (progn (usql:create-view [lenins-group]
97 ;;:column-list '([forename] [surname] [email])
98 :as [select [first-name] [last-name] [email]
100 :where [= [managerid] 1]])
102 (sort (mapcar #'string-downcase
103 (usql:list-attributes [lenins-group]))
105 (usql:drop-view [lenins-group] :if-does-not-exist :ignore)))
106 ("email" "first_name" "last_name"))
108 ;; create a view, select stuff from it and drop it
109 (deftest :fddl/view/3
110 (progn (usql:create-view [lenins-group]
111 :as [select [first-name] [last-name] [email]
113 :where [= [managerid] 1]])
117 (usql:select [first-name] [last-name] [email]
119 :where [= [last-name] "Lenin"])
121 (car (usql:select [first-name] [last-name] [email]
123 :where [= [last-name] "Stalin"])))))
124 (usql:drop-view [lenins-group] :if-does-not-exist :ignore)
125 (apply #'values result)))
126 nil ("Josef" "Stalin" "stalin@soviet.org"))
129 (deftest :fddl/view/4
130 (if (eql *test-database-type* :sqlite)
131 (values nil '(("Josef" "Stalin" "stalin@soviet.org")))
132 (progn (usql:create-view [lenins-group]
133 :column-list '([forename] [surname] [email])
134 :as [select [first-name] [last-name] [email]
136 :where [= [managerid] 1]])
140 (usql:select [forename] [surname] [email]
142 :where [= [surname] "Lenin"])
144 (car (usql:select [forename] [surname] [email]
146 :where [= [surname] "Stalin"])))))
147 (usql:drop-view [lenins-group] :if-does-not-exist :ignore)
148 (apply #'values result))))
149 nil ("Josef" "Stalin" "stalin@soviet.org"))
151 ;; create an index, test for existence, drop it and test again
152 (deftest :fddl/index/1
153 (progn (usql:create-index [bar] :on [employee] :attributes
154 '([first-name] [last-name] [email]) :unique t)
156 (usql:index-exists-p [bar] :owner *test-database-user*)
158 (case *test-database-type*
160 (usql:drop-index [bar] :on [employee]
161 :if-does-not-exist :ignore))
163 (usql:drop-index [bar]:if-does-not-exist :ignore)))
164 (usql:view-exists-p [bar] :owner *test-database-user*))))
167 ;; create indexes with names as strings, symbols and in square brackets
168 (deftest :fddl/index/2
169 (let ((names '("foo" foo [foo]))
172 (usql:create-index name :on [employee] :attributes '([emplid]))
173 (push (usql:index-exists-p name :owner *test-database-user*) result)
174 (case *test-database-type*
176 (usql:drop-index name :on [employee] :if-does-not-exist :ignore))
177 (t (usql:drop-index name :if-does-not-exist :ignore))))
178 (apply #'values result))
181 ;; create an sequence, test for existence, drop it and test again
182 (deftest :fddl/sequence/1
183 (progn (usql:create-sequence [foo])
185 (usql:sequence-exists-p [foo] :owner *test-database-user*)
187 (usql:drop-sequence [foo] :if-does-not-exist :ignore)
188 (usql:sequence-exists-p [foo] :owner *test-database-user*))))
191 ;; create and increment a sequence
192 (deftest :fddl/sequence/2
194 (usql:create-sequence [foo])
195 (setf val1 (usql:sequence-next [foo]))
197 (< val1 (usql:sequence-next [foo]))
198 (usql:drop-sequence [foo] :if-does-not-exist :ignore)))
201 ;; explicitly set the value of a sequence
202 (deftest :fddl/sequence/3
204 (usql:create-sequence [foo])
205 (usql:set-sequence-position [foo] 5)
207 (usql:sequence-next [foo])
208 (usql:drop-sequence [foo] :if-does-not-exist :ignore)))
211 #.(usql:restore-sql-reader-syntax-state)