1 ;;;; -*- Mode: LISP; Syntax: ANSI-Common-Lisp; Base: 10 -*-
2 ;;;; ======================================================================
3 ;;;; File: test-oodml.lisp
4 ;;;; Author: Marcus Pearce <m.t.pearce@city.ac.uk>
5 ;;;; Created: 01/04/2004
8 ;;;; Tests for the CLSQL Object Oriented 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 (deftest :oodml/select/1
26 (mapcar #'(lambda (e) (slot-value e 'last-name))
27 (clsql:select 'employee :order-by [last-name] :flatp t))
28 ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
29 "Stalin" "Trotsky" "Yeltsin"))
31 (deftest :oodml/select/2
32 (mapcar #'(lambda (e) (slot-value e 'name))
33 (clsql:select 'company :flatp t))
36 (deftest :oodml/select/3
37 (mapcar #'(lambda (e) (slot-value e 'companyid))
38 (clsql:select 'employee
39 :where [and [= [slot-value 'employee 'companyid]
40 [slot-value 'company 'companyid]]
41 [= [slot-value 'company 'name]
44 (1 1 1 1 1 1 1 1 1 1))
46 (deftest :oodml/select/4
48 (concatenate 'string (slot-value e 'first-name)
50 (slot-value e 'last-name)))
51 (clsql:select 'employee :where [= [slot-value 'employee 'first-name]
54 :order-by [last-name]))
55 ("Vladamir Lenin" "Vladamir Putin"))
57 (deftest :oodml/select/5
58 (length (clsql:select 'employee :where [married] :flatp t))
61 (deftest :oodml/select/6
62 (slot-value (caar (clsql:select 'employee :where [= 1 [emplid]])) 'married)
65 (deftest :oodml/select/7
66 (slot-value (caar (clsql:select 'employee :where [= 4 [emplid]])) 'married)
69 (deftest :oodml/select/8
70 (let ((a (caar (clsql:select 'address :where [= 1 [emplid]]))))
72 (slot-value a 'street-number)
73 (slot-value a 'street-name)
75 (slot-value a 'postal-code)))
76 10 "Park Place" "Leningrad" 123)
78 (deftest :oodml/select/9
79 (let ((a (caar (clsql:select 'address :where [= 2 [emplid]]))))
81 (slot-value a 'street-number)
82 (slot-value a 'street-name)
84 (slot-value a 'postal-code)))
87 (deftest :oodml/select/10
88 (mapcar #'(lambda (e) (slot-value e 'married))
89 (clsql:select 'employee :flatp t :order-by [emplid]))
90 (T T T NIL NIL NIL NIL NIL NIL NIL))
92 ;; tests update-records-from-instance
93 (deftest :oodml/update-records/1
96 (let ((lenin (car (clsql:select 'employee
97 :where [= [slot-value 'employee 'emplid]
105 (employee-email lenin))))
107 (setf (slot-value employee1 'first-name) "Dimitriy"
108 (slot-value employee1 'last-name) "Ivanovich"
109 (slot-value employee1 'email) "ivanovich@soviet.org")
110 (clsql:update-records-from-instance employee1)
111 (let ((lenin (car (clsql:select 'employee
112 :where [= [slot-value 'employee 'emplid]
120 (employee-email lenin))))
122 (setf (slot-value employee1 'first-name) "Vladamir"
123 (slot-value employee1 'last-name) "Lenin"
124 (slot-value employee1 'email) "lenin@soviet.org")
125 (clsql:update-records-from-instance employee1)
126 (let ((lenin (car (clsql:select 'employee
127 :where [= [slot-value 'employee 'emplid]
135 (employee-email lenin)))))
136 "Vladamir Lenin: lenin@soviet.org"
137 "Dimitriy Ivanovich: ivanovich@soviet.org"
138 "Vladamir Lenin: lenin@soviet.org")
140 ;; tests update-record-from-slot
141 (deftest :oodml/update-records/2
144 (car (clsql:select 'employee
145 :where [= [slot-value 'employee 'emplid] 1]
148 (setf (slot-value employee1 'email) "lenin-nospam@soviet.org")
149 (clsql:update-record-from-slot employee1 'email)
151 (car (clsql:select 'employee
152 :where [= [slot-value 'employee 'emplid] 1]
155 (setf (slot-value employee1 'email) "lenin@soviet.org")
156 (clsql:update-record-from-slot employee1 'email)
158 (car (clsql:select 'employee
159 :where [= [slot-value 'employee 'emplid] 1]
161 "lenin@soviet.org" "lenin-nospam@soviet.org" "lenin@soviet.org")
163 ;; tests update-record-from-slots
164 (deftest :oodml/update-records/3
166 (let ((lenin (car (clsql:select 'employee
167 :where [= [slot-value 'employee 'emplid]
175 (employee-email lenin)))
177 (setf (slot-value employee1 'first-name) "Dimitriy"
178 (slot-value employee1 'last-name) "Ivanovich"
179 (slot-value employee1 'email) "ivanovich@soviet.org")
180 (clsql:update-record-from-slots employee1 '(first-name last-name email))
181 (let ((lenin (car (clsql:select 'employee
182 :where [= [slot-value 'employee 'emplid]
190 (employee-email lenin))))
192 (setf (slot-value employee1 'first-name) "Vladamir"
193 (slot-value employee1 'last-name) "Lenin"
194 (slot-value employee1 'email) "lenin@soviet.org")
195 (clsql:update-record-from-slots employee1 '(first-name last-name email))
196 (let ((lenin (car (clsql:select 'employee
197 :where [= [slot-value 'employee 'emplid]
205 (employee-email lenin)))))
206 "Vladamir Lenin: lenin@soviet.org"
207 "Dimitriy Ivanovich: ivanovich@soviet.org"
208 "Vladamir Lenin: lenin@soviet.org")
210 ;; tests update-instance-from-records
211 (deftest :oodml/update-instance/1
214 (slot-value employee1 'first-name)
216 (slot-value employee1 'last-name)
218 (slot-value employee1 'email))
220 (clsql:update-records [employee]
221 :av-pairs '(([first-name] "Ivan")
222 ([last-name] "Petrov")
223 ([email] "petrov@soviet.org"))
224 :where [= [emplid] 1])
225 (clsql:update-instance-from-records employee1)
227 (slot-value employee1 'first-name)
229 (slot-value employee1 'last-name)
231 (slot-value employee1 'email)))
233 (clsql:update-records [employee]
234 :av-pairs '(([first-name] "Vladamir")
235 ([last-name] "Lenin")
236 ([email] "lenin@soviet.org"))
237 :where [= [emplid] 1])
238 (clsql:update-instance-from-records employee1)
240 (slot-value employee1 'first-name)
242 (slot-value employee1 'last-name)
244 (slot-value employee1 'email))))
245 "Vladamir Lenin: lenin@soviet.org"
246 "Ivan Petrov: petrov@soviet.org"
247 "Vladamir Lenin: lenin@soviet.org")
249 ;; tests update-slot-from-record
250 (deftest :oodml/update-instance/2
252 (slot-value employee1 'email)
254 (clsql:update-records [employee]
255 :av-pairs '(([email] "lenin-nospam@soviet.org"))
256 :where [= [emplid] 1])
257 (clsql:update-slot-from-record employee1 'email)
258 (slot-value employee1 'email))
260 (clsql:update-records [employee]
261 :av-pairs '(([email] "lenin@soviet.org"))
262 :where [= [emplid] 1])
263 (clsql:update-slot-from-record employee1 'email)
264 (slot-value employee1 'email)))
265 "lenin@soviet.org" "lenin-nospam@soviet.org" "lenin@soviet.org")
268 (deftest :oodml/do-query/1
270 (clsql:do-query ((e) [select 'employee :order-by [emplid]])
271 (push (slot-value e 'last-name) result))
273 ("Putin" "Yeltsin" "Gorbachev" "Chernenko" "Andropov" "Brezhnev" "Kruschev"
274 "Trotsky" "Stalin" "Lenin"))
276 (deftest :oodml/do-query/2
278 (clsql:do-query ((e c) [select 'employee 'company
279 :where [= [slot-value 'employee 'last-name]
281 (push (list (slot-value e 'last-name) (slot-value c 'name))
284 (("Lenin" "Widgets Inc.")))
286 (deftest :oodml/map-query/1
287 (clsql:map-query 'list #'last-name [select 'employee :order-by [emplid]])
288 ("Lenin" "Stalin" "Trotsky" "Kruschev" "Brezhnev" "Andropov" "Chernenko"
289 "Gorbachev" "Yeltsin" "Putin"))
291 (deftest :oodml/map-query/2
292 (clsql:map-query 'list #'(lambda (e c) (list (slot-value e 'last-name)
293 (slot-value c 'name)))
294 [select 'employee 'company :where [= [slot-value 'employee 'last-name]
296 (("Lenin" "Widgets Inc.")))
298 ;(deftest :oodml/iteration/3
299 ; (loop for (e) being the tuples in
300 ; [select 'employee :where [married] :order-by [emplid]]
301 ; collect (slot-value e 'last-name))
302 ; ("Lenin" "Stalin" "Trotsky"))
306 #.(clsql:restore-sql-reader-syntax-state)