source: trunk/test/unit/net/kromhouts/HqlBuilderTests.groovy @ 681

Last change on this file since 681 was 643, checked in by gav, 14 years ago

SVN move HqlBuilder to net.kromhouts package.
File size: 15.0 KB
Line 
1/* Copyright 2010 the original author or authors.
2 *
3 * Licensed under the Apache License, Version 2.0 (the "License");
4 * you may not use this file except in compliance with the License.
5 * You may obtain a copy of the License at
6 *
7 *      http://www.apache.org/licenses/LICENSE-2.0
8 *
9 * Unless required by applicable law or agreed to in writing, software
10 * distributed under the License is distributed on an "AS IS" BASIS,
11 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12 * See the License for the specific language governing permissions and
13 * limitations under the License.
14 */
15
16package net.kromhouts
17
18import org.apache.commons.logging.LogFactory
19
20/**
21 * Unit tests for HqlBuilder class.
22 * GroovyTestCase is used so that class does not depend on Grails as it may be useful outside of Grails.
23 *
24 * @author Gavin Kromhout
25 * @version DraftB
26 *
27 */
28public class HqlBuilderTests extends GroovyTestCase {
29
30    def n = '\n'
31    def t = '\t'
32    def savedMetaClass
33
34    protected void setUp() {
35        super.setUp()
36        savedMetaClass = HqlBuilder.metaClass
37        def emc = new ExpandoMetaClass(HqlBuilder, true, true)
38        emc.log = LogFactory.getLog(getClass())
39        emc.initialize()
40        GroovySystem.metaClassRegistry.setMetaClass(HqlBuilder, emc)
41    }
42
43    protected void tearDown() {
44        GroovySystem.metaClassRegistry.removeMetaClass(HqlBuilder)
45        GroovySystem.metaClassRegistry.setMetaClass(HqlBuilder, savedMetaClass)
46        super.tearDown()
47    }
48
49    void testBasicUsage() {
50
51        def q = new HqlBuilder().query {
52            select 'count(distinct book)'
53            from 'Book as book'
54            where 'book.id > 100'
55        }
56
57        assert q.query == 'select count(distinct book) from Book as book where book.id > 100'
58
59        q.select = 'distinct book'
60        assert q.query == 'select distinct book from Book as book where book.id > 100'
61        assert q.printFormattedQuery == 'select distinct book \nfrom Book as book \nwhere book.id > 100'
62
63    } // testBasicUsage()
64
65    void testBasicUsageAlternateForm() {
66
67        def q = new HqlBuilder()
68
69        q {
70            select 'distinct book'
71            from 'Book as book'
72            where 'book.id > 100'
73        }
74
75        assert q.query == 'select distinct book from Book as book where book.id > 100'
76
77    } // testBasicUsageAlternateForm()
78
79    void testPaginateParams() {
80
81        def q = new HqlBuilder(max: 99, offset: 11).query {
82            select 'distinct book'
83            from 'Book as book'
84            where 'book.id > 100'
85        }
86
87        assert q.max == 99
88        assert q.offset == 11
89
90    } // testPaginateParams()
91
92    void testPaginateParamsAlternateForm() {
93
94        def q = new HqlBuilder().query {
95            max = 99
96            offset = 11
97            select 'distinct book'
98            from 'Book as book'
99            where 'book.id > 100'
100        }
101
102        assert q.max == 99
103        assert q.offset == 11
104
105    } // testPaginateParamsAlternateForm()
106
107    void testNamedParams() {
108        def startId = 13
109        def endId = 23
110
111        def q = new HqlBuilder().query {
112            namedParams.startId = startId
113            select 'distinct book'
114            from 'Book as book'
115            where 'book.id > :startId'
116                and 'book.id < :endId'
117        }
118
119        q.namedParams.endId = endId
120
121        assert q.namedParams.startId == startId
122        assert q.namedParams.endId == endId
123
124    } // testNamedParams()
125
126    void testMultipleTerms() {
127
128        def q = new HqlBuilder().query {
129            select 'book.name',
130                        'type.name'
131            from 'Book as book',
132                    'left join book.group as group',
133                    'left join group.type as type'
134            where "book.name like '%Ned%'",
135                        'group = :group'
136        }
137
138        def expectedQuery = /select book.name, type.name from Book as book left join book.group as group left join group.type as type/
139        expectedQuery +=  / where book.name like '%Ned%' and group = :group/
140        assert q.query == expectedQuery
141
142        def expectedPrintFormat = /select book.name, ${n}${t}type.name/
143        expectedPrintFormat += / ${n}from Book as book ${n}${t}left join book.group as group ${n}${t}left join group.type as type/
144        expectedPrintFormat += / ${n}where book.name like '%Ned%' ${n}${t}and group = :group/
145        assert q.printFormattedQuery == expectedPrintFormat
146
147    } // testMultipleTerms()
148
149    void testMultipleTermsAlternateForm() {
150
151        def q = new HqlBuilder().query {
152            select 'book.name' // Create clause and append arg to clause's term list.
153            select 'type.name' // Method arg is appended to existing clause's term list.
154            from 'Book as book'
155            left 'join book.group as group'
156            left 'left join group.type as type' // 'left join' has to be repeated since left is an existing clause.
157            where(/book.name like '%Ned%'/) // Slashy string literals have to be protected when calling a method.
158            where 'group = :group'
159        }
160
161        def expectedQuery = /select book.name, type.name from Book as book left join book.group as group left join group.type as type/
162        expectedQuery +=  / where book.name like '%Ned%' and group = :group/
163        assert q.query == expectedQuery
164
165        def expectedPrintFormat = /select book.name, ${n}${t}type.name/
166        expectedPrintFormat += / ${n}from Book as book ${n}left join book.group as group ${n}${t}left join group.type as type/
167        expectedPrintFormat += / ${n}where book.name like '%Ned%' ${n}${t}and group = :group/
168        assert q.printFormattedQuery == expectedPrintFormat
169
170    } // testMultipleTermsAlternateForm()
171
172    void testPlaceHolder() {
173
174        def q = new HqlBuilder().query {
175            select 'distinct book'
176            from 'Book as book'
177            where  // Place holder as propertyMissing call.
178            order 'by book.name asc'
179        }
180
181        // Assign to place holder which is in the middle of the query string.
182        q.where = /book.name like '%Ned%'/
183
184        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/
185
186    } // testPlaceHolder()
187
188    void testPlaceHolderAlternateForm() {
189
190        def q = new HqlBuilder().query {
191            select 'distinct book'
192            from 'Book as book'
193            where '' // Place holder as method call, tests for nulls when also using append method call bellow.
194            order 'by book.name asc'
195        }
196
197        // Append to place holder which is in the middle of the query string.
198        q.where(/book.name like '%Ned%'/)
199
200        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/
201
202    } // testPlaceHolderAlternateForm()
203
204    void testClauseRemoval() {
205
206        def q = new HqlBuilder().query {
207            select 'count(distinct book)'
208            from 'Book as book'
209            where = /book.name like '%Ned%'/  // Slashy string literals don't need protecting when assigning.
210            order 'by book.name asc'
211        }
212
213        q.order = null // Remove clause.
214        assert q.query == /select count(distinct book) from Book as book where book.name like '%Ned%'/
215
216    } // testClauseRemoval()
217
218    void testClauseRemovalAlternateForm() {
219
220        def q = new HqlBuilder().query {
221            select 'count(distinct book)'
222            from 'Book as book'
223            where = /book.name like '%Ned%'/  // Slashy string literals don't need protecting when assigning.
224            order 'by book.name asc'
225        }
226
227        q.removeClause('order') // Remove clause, alternate form.
228        assert q.query == /select count(distinct book) from Book as book where book.name like '%Ned%'/
229
230    } // testClauseRemovalAlternateForm()
231
232    void testLogicalBuilder() {
233
234        def q = new HqlBuilder().query {
235            from 'Book as book'
236            where "book.name like '%Ned%'"
237                or "book.onSpecial = true"
238        }
239
240        assert q.query == /from Book as book where book.name like '%Ned%' or book.onSpecial = true/
241
242    } // testLogicalBuilder()
243
244    void testLogicalBuilderNesting() {
245
246        def q = new HqlBuilder().query {
247            from 'Book as book'
248            where "book.name like '%Ned%'"
249                or {
250                    where "book.onSpecial = true"
251                    and 'book.inStock = true'
252                }
253        }
254
255        assert q.query == /from Book as book where book.name like '%Ned%' or ( book.onSpecial = true and book.inStock = true )/
256
257    } // testLogicalBuilderNesting()
258
259    void testLogicalBuilderNestingLoop() {
260        def range = 1..2
261
262        def q = new HqlBuilder().query {
263            from 'Book as book'
264            where 'book.inStock = true'
265                and {
266                    range.each {
267                        or "book.id = $it"
268                    }
269                }
270        }
271
272        assert q.query == /from Book as book where book.inStock = true and ( book.id = 1 or book.id = 2 )/
273
274    } // testLogicalBuilderNestingLoop()
275
276    void testWhereClosure() {
277
278        def q = new HqlBuilder().query {
279            from 'Book as book'
280            where {
281                and 'book.id = 1'
282            }
283        }
284
285        // Only 1 expression so no brackets.
286        assert q.query == /from Book as book where book.id = 1/
287
288    } // testWhereClosure()
289
290    void testWhereClosureAlternate() {
291
292        def q = new HqlBuilder().query {
293            from 'Book as book'
294        }
295
296        q.where {
297            and 'book.id = 1',
298                    'book.inStock = true'
299        }
300
301        // More than 1 expression so brackets are included.
302        assert q.query == /from Book as book where ( book.id = 1 and book.inStock = true )/
303
304    } // testWhereClosureAlternate()
305
306// This is very likely to be a common usage error as it may seem like a natural way to write the where clause.
307// Is it possible to intercept the String & GString constructors just inside the closure and call where 'book.id = 1'?
308// Perhaps by internally creating a new Closure and using something like this:
309// http://groovy.codehaus.org/JN3515-Interception ???
310// Or is it possible to examine each statment of a closure?
311    void testWhereClosureWithNewString() {
312
313        def q = new HqlBuilder().query {
314            from 'Book as book'
315            where {
316                'book.id = 1' // This statement is missing a method call and hence will simply be excluded.
317                and 'book.inStock = true'
318            }
319        }
320
321        // Would be nice if the first case was true.
322        assertFalse q.query == /from Book as book where ( book.id = 1 and book.inStock = true )/
323        assert q.query == /from Book as book where book.inStock = true/
324
325    } // testSelectWhereClosureWithNewString()
326
327    void testWithConditionals() {
328        def y = true
329        def n = false
330
331        def q = new HqlBuilder().query {
332            select 'distinct book'
333            from 'Book as book'
334            if(y)
335                where(/book.name like '%Ned%'/)
336            if(n)
337                order ''
338            else
339                order 'by book.name asc'
340        }
341
342        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/
343
344    } // testWithConditionals()
345
346    void testSelectWithLooping() {
347        def selections = ['id', 'name', 'description']
348
349        def q = new HqlBuilder().query {
350            for(s in selections) {
351                select "book.${s}"
352            }
353            from 'Book as book'
354        }
355
356        assert q.query == /select book.id, book.name, book.description from Book as book/
357
358    } // testSelectWithLooping()
359
360    void testWhereWithLooping() {
361        def range = 1..3
362
363        def q = new HqlBuilder().query {
364            from 'Book as book'
365            where 'book.inStock = true'
366                range.each {
367                    or "book.id = $it"
368                }
369        }
370
371        assert q.query == /from Book as book where book.inStock = true or book.id = 1 or book.id = 2 or book.id = 3/
372
373    } // testWhereWithLooping()
374
375    void testWhereDirectlyWithLoops() {
376        def range = 1..3
377
378        def q = new HqlBuilder().query {
379            from 'Book as book'
380            where
381                range.each {
382                    or "book.id = $it"
383                }
384        }
385
386        assert q.query == /from Book as book where book.id = 1 or book.id = 2 or book.id = 3/
387
388    } // testWhereDirectlyWithLoops()
389
390    void testWhereNodeWithLoops() {
391        def range = 1..3
392
393        def q = new HqlBuilder().query {
394            from 'Book as book'
395            where {
396                range.each {
397                    or "book.id = $it"
398                }
399            }
400        }
401
402        assert q.query == /from Book as book where ( book.id = 1 or book.id = 2 or book.id = 3 )/
403
404    } // testWhereNodeWithLoops()
405
406    void testOrderByMultipleTerms() {
407
408        def q = new HqlBuilder().query {
409            from 'Book as book'
410            where 'book.id > 100'
411            order 'by book.name asc',
412                        'book.id desc'
413        }
414
415        assert q.query == 'from Book as book where book.id > 100 order by book.name asc, book.id desc'
416
417        assert q.printFormattedQuery == 'from Book as book \nwhere book.id > 100 \norder by book.name asc, \n\tbook.id desc'
418
419    } // testOrderByMultipleTerms()
420
421    void testGroupByMultipleTerms() {
422
423        def q = new HqlBuilder().query {
424            from 'Book as book'
425            where 'book.id > 100'
426            group 'by book.name asc',
427                        'book.id desc'
428        }
429
430        assert q.query == 'from Book as book where book.id > 100 group by book.name asc, book.id desc'
431
432        assert q.printFormattedQuery == 'from Book as book \nwhere book.id > 100 \ngroup by book.name asc, \n\tbook.id desc'
433
434    } // testGroupByMultipleTerms()
435
436    void testUpdate() {
437        def q = new HqlBuilder().query {
438            update 'Book b'
439            set 'b.name = :newName',
440                'b.inStock = true'
441            where 'b.name = :oldName'
442        }
443
444        assert q.query == 'update Book b set b.name = :newName, b.inStock = true where b.name = :oldName'
445
446        assert q.printFormattedQuery == 'update Book b \nset b.name = :newName, \n\tb.inStock = true \nwhere b.name = :oldName'
447
448    } // testUpdate()
449
450    void testDelete() {
451        def q = new HqlBuilder().query {
452            delete 'Book b'
453            where 'b.name = :oldName'
454        }
455
456        assert q.query == 'delete Book b where b.name = :oldName'
457
458        assert q.printFormattedQuery == 'delete Book b \nwhere b.name = :oldName'
459
460    } // testDelete()
461
462    void testInsertInto() {
463        def q = new HqlBuilder(debug:true).query {
464            insert 'into ArchiveBook (id, name)'
465            select 'b.id',
466                        'b.name'
467            from 'Book b'
468            where 'b.name = :oldName'
469        }
470
471        assert q.query == 'insert into ArchiveBook (id, name) select b.id, b.name from Book b where b.name = :oldName'
472
473        assert q.printFormattedQuery == 'insert into ArchiveBook (id, name) \nselect b.id, \n\tb.name \nfrom Book b \nwhere b.name = :oldName'
474
475    } // testInsertInto()
476
477} // end class
Note: See TracBrowser for help on using the repository browser.