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

/* Copyright 2010 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

import org.apache.commons.logging.LogFactory

/**
 * Unit tests for HqlBuilder class.
 * GroovyTestCase is used so that class does not depend on Grails as it may be useful outside of Grails.
 *
 * @author Gavin Kromhout
 * @version DraftB
 *
 */
public class HqlBuilderTests extends GroovyTestCase {

    def n = '\n'
    def t = '\t'
    def savedMetaClass

    protected void setUp() {
        super.setUp()
        savedMetaClass = HqlBuilder.metaClass
        def emc = new ExpandoMetaClass(HqlBuilder, true, true)
        emc.log = LogFactory.getLog(getClass())
        emc.initialize()
        GroovySystem.metaClassRegistry.setMetaClass(HqlBuilder, emc)
    }

    protected void tearDown() {
        GroovySystem.metaClassRegistry.removeMetaClass(HqlBuilder)
        GroovySystem.metaClassRegistry.setMetaClass(HqlBuilder, savedMetaClass)
        super.tearDown()
    }

    void testBasicUsage() {

        def q = new HqlBuilder().query {
            select 'count(distinct book)'
            from 'Book as book'
            where 'book.id > 100'
        }

        assert q.query == 'select count(distinct book) from Book as book where book.id > 100'

        q.select = 'distinct book'
        assert q.query == 'select distinct book from Book as book where book.id > 100'
        assert q.printFormattedQuery == 'select distinct book \nfrom Book as book \nwhere book.id > 100'

    } // testBasicUsage()

    void testBasicUsageAlternateForm() {

        def q = new HqlBuilder()

        q {
            select 'distinct book'
            from 'Book as book'
            where 'book.id > 100'
        }

        assert q.query == 'select distinct book from Book as book where book.id > 100'

    } // testBasicUsageAlternateForm()

    void testPaginateParams() {

        def q = new HqlBuilder(max: 99, offset: 11).query {
            select 'distinct book'
            from 'Book as book'
            where 'book.id > 100'
        }

        assert q.max == 99
        assert q.offset == 11

    } // testPaginateParams()

    void testPaginateParamsAlternateForm() {

        def q = new HqlBuilder().query {
            max = 99
            offset = 11
            select 'distinct book'
            from 'Book as book'
            where 'book.id > 100'
        }

        assert q.max == 99
        assert q.offset == 11

    } // testPaginateParamsAlternateForm()

    void testNamedParams() {
        def startId = 13
        def endId = 23

        def q = new HqlBuilder().query {
            namedParams.startId = startId
            select 'distinct book'
            from 'Book as book'
            where 'book.id > :startId'
                and 'book.id < :endId'
        }

        q.namedParams.endId = endId

        assert q.namedParams.startId == startId
        assert q.namedParams.endId == endId

    } // testNamedParams()

    void testMultipleTerms() {

        def q = new HqlBuilder().query {
            select 'book.name',
                        'type.name'
            from 'Book as book',
                    'left join book.group as group',
                    'left join group.type as type'
            where "book.name like '%Ned%'",
                        'group = :group'
        }

        def expectedQuery = /select book.name, type.name from Book as book left join book.group as group left join group.type as type/
        expectedQuery +=  / where book.name like '%Ned%' and group = :group/
        assert q.query == expectedQuery

        def expectedPrintFormat = /select book.name, ${n}${t}type.name/
        expectedPrintFormat += / ${n}from Book as book ${n}${t}left join book.group as group ${n}${t}left join group.type as type/
        expectedPrintFormat += / ${n}where book.name like '%Ned%' ${n}${t}and group = :group/
        assert q.printFormattedQuery == expectedPrintFormat

    } // testMultipleTerms()

    void testMultipleTermsAlternateForm() {

        def q = new HqlBuilder().query {
            select 'book.name' // Create clause and append arg to clause's term list.
            select 'type.name' // Method arg is appended to existing clause's term list.
            from 'Book as book'
            left 'join book.group as group'
            left 'left join group.type as type' // 'left join' has to be repeated since left is an existing clause.
            where(/book.name like '%Ned%'/) // Slashy string literals have to be protected when calling a method.
            where 'group = :group'
        }

        def expectedQuery = /select book.name, type.name from Book as book left join book.group as group left join group.type as type/
        expectedQuery +=  / where book.name like '%Ned%' and group = :group/
        assert q.query == expectedQuery

        def expectedPrintFormat = /select book.name, ${n}${t}type.name/
        expectedPrintFormat += / ${n}from Book as book ${n}left join book.group as group ${n}${t}left join group.type as type/
        expectedPrintFormat += / ${n}where book.name like '%Ned%' ${n}${t}and group = :group/
        assert q.printFormattedQuery == expectedPrintFormat

    } // testMultipleTermsAlternateForm()

    void testPlaceHolder() {

        def q = new HqlBuilder().query {
            select 'distinct book'
            from 'Book as book'
            where  // Place holder as propertyMissing call.
            order 'by book.name asc'
        }

        // Assign to place holder which is in the middle of the query string.
        q.where = /book.name like '%Ned%'/

        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/

    } // testPlaceHolder()

    void testPlaceHolderAlternateForm() {

        def q = new HqlBuilder().query {
            select 'distinct book'
            from 'Book as book'
            where '' // Place holder as method call, tests for nulls when also using append method call bellow.
            order 'by book.name asc'
        }

        // Append to place holder which is in the middle of the query string.
        q.where(/book.name like '%Ned%'/)

        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/

    } // testPlaceHolderAlternateForm()

    void testClauseRemoval() {

        def q = new HqlBuilder().query {
            select 'count(distinct book)'
            from 'Book as book'
            where = /book.name like '%Ned%'/  // Slashy string literals don't need protecting when assigning.
            order 'by book.name asc'
        }

        q.order = null // Remove clause.
        assert q.query == /select count(distinct book) from Book as book where book.name like '%Ned%'/

    } // testClauseRemoval()

    void testClauseRemovalAlternateForm() {

        def q = new HqlBuilder().query {
            select 'count(distinct book)'
            from 'Book as book'
            where = /book.name like '%Ned%'/  // Slashy string literals don't need protecting when assigning.
            order 'by book.name asc'
        }

        q.removeClause('order') // Remove clause, alternate form.
        assert q.query == /select count(distinct book) from Book as book where book.name like '%Ned%'/

    } // testClauseRemovalAlternateForm()

    void testLogicalBuilder() {

        def q = new HqlBuilder().query {
            from 'Book as book'
            where "book.name like '%Ned%'"
                or "book.onSpecial = true"
        }

        assert q.query == /from Book as book where book.name like '%Ned%' or book.onSpecial = true/

    } // testLogicalBuilder()

    void testLogicalBuilderNesting() {

        def q = new HqlBuilder().query {
            from 'Book as book'
            where "book.name like '%Ned%'"
                or {
                    where "book.onSpecial = true"
                    and 'book.inStock = true'
                }
        }

        assert q.query == /from Book as book where book.name like '%Ned%' or ( book.onSpecial = true and book.inStock = true )/

    } // testLogicalBuilderNesting()

    void testLogicalBuilderNestingLoop() {
        def range = 1..2

        def q = new HqlBuilder().query {
            from 'Book as book'
            where 'book.inStock = true'
                and {
                    range.each {
                        or "book.id = $it"
                    }
                }
        }

        assert q.query == /from Book as book where book.inStock = true and ( book.id = 1 or book.id = 2 )/

    } // testLogicalBuilderNestingLoop()

    void testWhereClosure() {

        def q = new HqlBuilder().query {
            from 'Book as book'
            where {
                and 'book.id = 1'
            }
        }

        // Only 1 expression so no brackets.
        assert q.query == /from Book as book where book.id = 1/

    } // testWhereClosure()

    void testWhereClosureAlternate() {

        def q = new HqlBuilder().query {
            from 'Book as book'
        }

        q.where {
            and 'book.id = 1',
                    'book.inStock = true'
        }

        // More than 1 expression so brackets are included.
        assert q.query == /from Book as book where ( book.id = 1 and book.inStock = true )/

    } // testWhereClosureAlternate()

// This is very likely to be a common usage error as it may seem like a natural way to write the where clause.
// Is it possible to intercept the String & GString constructors just inside the closure and call where 'book.id = 1'?
// Perhaps by internally creating a new Closure and using something like this:
// http://groovy.codehaus.org/JN3515-Interception ???
// Or is it possible to examine each statment of a closure?
    void testWhereClosureWithNewString() {

        def q = new HqlBuilder().query {
            from 'Book as book'
            where {
                'book.id = 1' // This statement is missing a method call and hence will simply be excluded.
                and 'book.inStock = true'
            }
        }

        // Would be nice if the first case was true.
        assertFalse q.query == /from Book as book where ( book.id = 1 and book.inStock = true )/
        assert q.query == /from Book as book where book.inStock = true/

    } // testSelectWhereClosureWithNewString()

    void testWithConditionals() {
        def y = true
        def n = false

        def q = new HqlBuilder().query {
            select 'distinct book'
            from 'Book as book'
            if(y)
                where(/book.name like '%Ned%'/)
            if(n)
                order ''
            else
                order 'by book.name asc'
        }

        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' order by book.name asc/

    } // testWithConditionals()

    void testSelectWithLooping() {
        def selections = ['id', 'name', 'description']

        def q = new HqlBuilder().query {
            for(s in selections) {
                select "book.${s}"
            }
            from 'Book as book'
        }

        assert q.query == /select book.id, book.name, book.description from Book as book/

    } // testSelectWithLooping()

    void testWhereWithLooping() {
        def range = 1..3

        def q = new HqlBuilder().query {
            from 'Book as book'
            where 'book.inStock = true'
                range.each {
                    or "book.id = $it"
                }
        }

        assert q.query == /from Book as book where book.inStock = true or book.id = 1 or book.id = 2 or book.id = 3/

    } // testWhereWithLooping()

    void testWhereDirectlyWithLoops() {
        def range = 1..3

        def q = new HqlBuilder().query {
            from 'Book as book'
            where
                range.each {
                    or "book.id = $it"
                }
        }

        assert q.query == /from Book as book where book.id = 1 or book.id = 2 or book.id = 3/

    } // testWhereDirectlyWithLoops()

    void testWhereNodeWithLoops() {
        def range = 1..3

        def q = new HqlBuilder().query {
            from 'Book as book'
            where {
                range.each {
                    or "book.id = $it"
                }
            }
        }

        assert q.query == /from Book as book where ( book.id = 1 or book.id = 2 or book.id = 3 )/

    } // testWhereNodeWithLoops()

    void testOrderByMultipleTerms() {

        def q = new HqlBuilder().query {
            from 'Book as book'
            where 'book.id > 100'
            order 'by book.name asc',
                        'book.id desc'
        }

        assert q.query == 'from Book as book where book.id > 100 order by book.name asc, book.id desc'

        assert q.printFormattedQuery == 'from Book as book \nwhere book.id > 100 \norder by book.name asc, \n\tbook.id desc'

    } // testOrderByMultipleTerms()

    void testGroupByMultipleTerms() {

        def q = new HqlBuilder().query {
            from 'Book as book'
            where 'book.id > 100'
            group 'by book.name asc',
                        'book.id desc'
        }

        assert q.query == 'from Book as book where book.id > 100 group by book.name asc, book.id desc'

        assert q.printFormattedQuery == 'from Book as book \nwhere book.id > 100 \ngroup by book.name asc, \n\tbook.id desc'

    } // testGroupByMultipleTerms()

    void testUpdate() {
        def q = new HqlBuilder().query {
            update 'Book b'
            set 'b.name = :newName',
                'b.inStock = true'
            where 'b.name = :oldName'
        }

        assert q.query == 'update Book b set b.name = :newName, b.inStock = true where b.name = :oldName'

        assert q.printFormattedQuery == 'update Book b \nset b.name = :newName, \n\tb.inStock = true \nwhere b.name = :oldName'

    } // testUpdate()

    void testDelete() {
        def q = new HqlBuilder().query {
            delete 'Book b'
            where 'b.name = :oldName'
        }

        assert q.query == 'delete Book b where b.name = :oldName'

        assert q.printFormattedQuery == 'delete Book b \nwhere b.name = :oldName'

    } // testDelete()

    void testInsertInto() {
        def q = new HqlBuilder(debug:true).query {
            insert 'into ArchiveBook (id, name)'
            select 'b.id',
                        'b.name'
            from 'Book b'
            where 'b.name = :oldName'
        }

        assert q.query == 'insert into ArchiveBook (id, name) select b.id, b.name from Book b where b.name = :oldName'

        assert q.printFormattedQuery == 'insert into ArchiveBook (id, name) \nselect b.id, \n\tb.name \nfrom Book b \nwhere b.name = :oldName'

    } // testInsertInto()

} // end class