source: trunk/test/unit/HqlBuilderTests.groovy @ 642

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

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