Changeset 642 for trunk/test/unit


Ignore:
Timestamp:
Sep 5, 2010, 8:06:53 PM (14 years ago)
Author:
gav
Message:

HqlBuilder and tests, draftB.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/test/unit/HqlBuilderTests.groovy

    r641 r642  
    2121 *
    2222 * @author Gavin Kromhout
    23  * @version DraftA
     23 * @version DraftB
    2424 *
    2525 */
     
    2727
    2828    def n = '\n'
     29    def t = '\t'
    2930    def savedMetaClass
    3031
     
    3334        savedMetaClass = HqlBuilder.metaClass
    3435        def emc = new ExpandoMetaClass(HqlBuilder, true, true)
    35         //emc.log = LogFactory.getLog(getClass())
     36        emc.log = LogFactory.getLog(getClass())
    3637        emc.initialize()
    3738        GroovySystem.metaClassRegistry.setMetaClass(HqlBuilder, emc)
     
    4445    }
    4546
    46     void testSelectBasic() {
     47    void testBasicUsage() {
    4748
    4849        def q = new HqlBuilder().query {
     
    5859        assert q.printFormattedQuery == 'select distinct book \nfrom Book as book \nwhere book.id > 100'
    5960
    60     } // testSelectBasic()
    61 
    62     void testSelectAdditional() {
    63 
    64         def q = new HqlBuilder().query {
    65             select 'distinct book'
    66             from 'Book as book'
    67             left 'join book.group as group',
     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',
    68131                    'left join group.type as type'
    69             where 'book.id > 100',
    70                         'and group = :group'
    71         }
    72 
    73         assert q.query == 'select distinct book from Book as book left join book.group as group left join group.type as type where book.id > 100 and group = :group'
    74 
    75     } // testSelectAdditional()
    76 
    77     void testSelectAlternate() {
    78 
    79         def q = new HqlBuilder()
    80 
    81         q {
    82             select 'distinct book'
    83             from 'Book as book'
    84             where(/book.name like '%Ned%'/) // Slashy string literals have to be protected when calling a function.
    85             where 'and book.description like "Head"'
    86         }
    87 
    88         assert q.query == /select distinct book from Book as book where book.name like '%Ned%' and book.description like "Head"/
    89         assert q.printFormattedQuery == /select distinct book ${n}from Book as book ${n}where book.name like '%Ned%' ${n}and book.description like "Head"/
    90 
    91     } // testSelectAlternate()
    92 
    93     void testSelectWithPlaceHolder() {
    94 
    95         def q = new HqlBuilder().query {
    96             select 'distinct book'
    97             from 'Book as book'
    98             where '' // Place holder.
     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.
    99176            order 'by book.name asc'
    100177        }
    101178
    102         // Insert to place holder which is in the middle of query string.
    103         q.where = /book.name like '%Ned%'/  // Slashy string literals don't need protecting when assigning.
     179        // Assign to place holder which is in the middle of the query string.
     180        q.where = /book.name like '%Ned%'/
    104181
    105182        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/
    106183
    107     } // testSelectWithPlaceHolder()
    108 
    109     void testSelectWithClauseRemoval() {
     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() {
    110203
    111204        def q = new HqlBuilder().query {
     
    116209        }
    117210
    118         q.order = null // Remove clause, since order by makes no sense when selecting a count ;-)
     211        q.order = null // Remove clause.
    119212        assert q.query == /select count(distinct book) from Book as book where book.name like '%Ned%'/
    120213
    121     } // testSelectWithClauseRemoval()
     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()
    122474
    123475} // end class
Note: See TracChangeset for help on using the changeset viewer.