Changeset 642 for trunk/test/unit/HqlBuilderTests.groovy

Timestamp:

Sep 5, 2010, 8:06:53 PM (14 years ago)

Author:

gav

Message:

HqlBuilder and tests, draftB.

File:

• trunk/test/unit/HqlBuilderTests.groovy (modified) (6 diffs)

trunk/test/unit/HqlBuilderTests.groovy

@@ -21 +21 @@
  *
  * @author Gavin Kromhout
- * @version DraftA
+ * @version DraftB
  *
  */
@@ -27 +27 @@
 
     def n = '\n'
+    def t = '\t'
     def savedMetaClass
 
@@ -33 +34 @@
         savedMetaClass = HqlBuilder.metaClass
         def emc = new ExpandoMetaClass(HqlBuilder, true, true)
-        //emc.log = LogFactory.getLog(getClass())
+        emc.log = LogFactory.getLog(getClass())
         emc.initialize()
         GroovySystem.metaClassRegistry.setMetaClass(HqlBuilder, emc)
@@ -44 +45 @@
     }
 
-    void testSelectBasic() {
+    void testBasicUsage() {
 
         def q = new HqlBuilder().query {
@@ -58 +59 @@
         assert q.printFormattedQuery == 'select distinct book \nfrom Book as book \nwhere book.id > 100'
 
-    } // testSelectBasic()
-
-    void testSelectAdditional() {
-
-        def q = new HqlBuilder().query {
-            select 'distinct book'
-            from 'Book as book'
-            left 'join book.group as group',
+    } // 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.id > 100',
-                        'and group = :group'
-        }
-
-        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'
-
-    } // testSelectAdditional()
-
-    void testSelectAlternate() {
-
-        def q = new HqlBuilder()
-
-        q {
-            select 'distinct book'
-            from 'Book as book'
-            where(/book.name like '%Ned%'/) // Slashy string literals have to be protected when calling a function.
-            where 'and book.description like "Head"'
-        }
-
-        assert q.query == /select distinct book from Book as book where book.name like '%Ned%' and book.description like "Head"/
-        assert q.printFormattedQuery == /select distinct book ${n}from Book as book ${n}where book.name like '%Ned%' ${n}and book.description like "Head"/
-
-    } // testSelectAlternate()
-
-    void testSelectWithPlaceHolder() {
-
-        def q = new HqlBuilder().query {
-            select 'distinct book'
-            from 'Book as book'
-            where '' // Place holder.
+            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'
         }
 
-        // Insert to place holder which is in the middle of query string.
-        q.where = /book.name like '%Ned%'/  // Slashy string literals don't need protecting when assigning.
+        // 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/
 
-    } // testSelectWithPlaceHolder()
-
-    void testSelectWithClauseRemoval() {
+    } // 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 {
@@ -116 +209 @@
         }
 
-        q.order = null // Remove clause, since order by makes no sense when selecting a count ;-)
+        q.order = null // Remove clause.
         assert q.query == /select count(distinct book) from Book as book where book.name like '%Ned%'/
 
-    } // testSelectWithClauseRemoval()
+    } // 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