1   package org.apache.torque.util;
2   
3   /*
4    * Copyright 2001-2005 The Apache Software Foundation.
5    *
6    * Licensed under the Apache License, Version 2.0 (the "License")
7    * you may not use this file except in compliance with the License.
8    * You may obtain a copy of the License at
9    *
10   *     http://www.apache.org/licenses/LICENSE-2.0
11   *
12   * Unless required by applicable law or agreed to in writing, software
13   * distributed under the License is distributed on an "AS IS" BASIS,
14   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15   * See the License for the specific language governing permissions and
16   * limitations under the License.
17   */
18  
19  import java.util.Calendar;
20  import java.util.Date;
21  import java.util.List;
22  import java.util.Map;
23  
24  import org.apache.commons.configuration.BaseConfiguration;
25  import org.apache.commons.configuration.Configuration;
26  import org.apache.commons.lang.SerializationUtils;
27  import org.apache.torque.BaseTestCase;
28  import org.apache.torque.TorqueException;
29  import org.apache.torque.adapter.DBFactory;
30  import org.apache.torque.util.Criteria.Criterion;
31  import org.apache.torque.util.Criteria.Join;
32  
33  /***
34   * Test class for Criteria.
35   *
36   * @author <a href="mailto:celkins@scardini.com">Christopher Elkins</a>
37   * @author <a href="mailto:sam@neurogrid.com">Sam Joseph</a>
38   * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a>
39   * @version $Id: CriteriaTest.java 332758 2005-11-12 11:15:21Z tfischer $
40   */
41  public class CriteriaTest extends BaseTestCase
42  {
43  
44      /*** The criteria to use in the test. */
45      private Criteria c;
46  
47      /***
48       * Creates a new instance.
49       *
50       * @param name the name of the test to run
51       */
52      public CriteriaTest(String name)
53      {
54          super(name);
55      }
56  
57      /***
58       * Initializes the criteria.
59       */
60      public void setUp()
61      {
62          super.setUp();
63          c = new Criteria();
64      }
65  
66      /***
67       * Test basic adding of strings.
68       */
69      public void testAddString()
70      {
71          final String table = "myTable";
72          final String column = "myColumn";
73          final String value = "myValue";
74  
75          // Add the string
76          c.add(table, column, (Object) value);
77  
78          // Verify that the key exists
79          assertTrue(c.containsKey(table, column));
80  
81          // Verify that what we get out is what we put in
82          assertTrue(c.getString(table, column).equals(value));
83      }
84  
85      /***
86       * test various properties of Criterion and nested criterion
87       */
88      public void testNestedCriterion()
89      {
90          final String table2 = "myTable2";
91          final String column2 = "myColumn2";
92          final String value2 = "myValue2";
93  
94          final String table3 = "myTable3";
95          final String column3 = "myColumn3";
96          final String value3 = "myValue3";
97  
98          final String table4 = "myTable4";
99          final String column4 = "myColumn4";
100         final String value4 = "myValue4";
101 
102         final String table5 = "myTable5";
103         final String column5 = "myColumn5";
104         final String value5 = "myValue5";
105 
106         Criteria.Criterion crit2 =
107             c.getNewCriterion(table2, column2, (Object) value2, Criteria.EQUAL);
108         Criteria.Criterion crit3 =
109             c.getNewCriterion(table3, column3, (Object) value3, Criteria.EQUAL);
110         Criteria.Criterion crit4 =
111             c.getNewCriterion(table4, column4, (Object) value4, Criteria.EQUAL);
112         Criteria.Criterion crit5 =
113             c.getNewCriterion(table5, column5, (Object) value5, Criteria.EQUAL);
114 
115         crit2.and(crit3).or(crit4.and(crit5));
116         String expect =
117             "((myTable2.myColumn2='myValue2' "
118                 + "AND myTable3.myColumn3='myValue3') "
119             + "OR (myTable4.myColumn4='myValue4' "
120                 + "AND myTable5.myColumn5='myValue5'))";
121         String result = crit2.toString();
122         assertEquals(expect, result);
123 
124         Criteria.Criterion crit6 =
125             c.getNewCriterion(table2, column2, (Object) value2, Criteria.EQUAL);
126         Criteria.Criterion crit7 =
127             c.getNewCriterion(table3, column3, (Object) value3, Criteria.EQUAL);
128         Criteria.Criterion crit8 =
129             c.getNewCriterion(table4, column4, (Object) value4, Criteria.EQUAL);
130         Criteria.Criterion crit9 =
131             c.getNewCriterion(table5, column5, (Object) value5, Criteria.EQUAL);
132 
133         crit6.and(crit7).or(crit8).and(crit9);
134         expect =
135             "(((myTable2.myColumn2='myValue2' "
136                     + "AND myTable3.myColumn3='myValue3') "
137                 + "OR myTable4.myColumn4='myValue4') "
138                     + "AND myTable5.myColumn5='myValue5')";
139         result = crit6.toString();
140         assertEquals(expect, result);
141 
142         // should make sure we have tests for all possibilities
143 
144         Criteria.Criterion[] crita = crit2.getAttachedCriterion();
145 
146         assertEquals(crit2, crita[0]);
147         assertEquals(crit3, crita[1]);
148         assertEquals(crit4, crita[2]);
149         assertEquals(crit5, crita[3]);
150 
151         List tables = crit2.getAllTables();
152 
153         assertEquals(crit2.getTable(), tables.get(0));
154         assertEquals(crit3.getTable(), tables.get(1));
155         assertEquals(crit4.getTable(), tables.get(2));
156         assertEquals(crit5.getTable(), tables.get(3));
157 
158         // simple confirmations that equality operations work
159         assertTrue(crit2.hashCode() == crit2.hashCode());
160         assertEquals(crit2.toString(), crit2.toString());
161     }
162 
163     /***
164      * Tests &lt;= and =&gt;.
165      */
166     public void testBetweenCriterion()
167     {
168         Criteria.Criterion cn1 =
169             c.getNewCriterion(
170                 "INVOICE.COST",
171                 new Integer(1000),
172                 Criteria.GREATER_EQUAL);
173         Criteria.Criterion cn2 =
174             c.getNewCriterion(
175                 "INVOICE.COST",
176                 new Integer(5000),
177                 Criteria.LESS_EQUAL);
178         c.add(cn1.and(cn2));
179         String expect =
180             "SELECT  FROM INVOICE WHERE "
181             + "(INVOICE.COST>=1000 AND INVOICE.COST<=5000)";
182         String result = null;
183         try
184         {
185             result = BasePeer.createQueryString(c);
186         }
187         catch (TorqueException e)
188         {
189             fail("TorqueException thrown in BasePeer.createQueryString()");
190         }
191 
192         assertEquals(expect, result);
193     }
194 
195     /***
196      * Verify that AND and OR criterion are nested correctly.
197      */
198     public void testPrecedence()
199     {
200         Criteria.Criterion cn1 =
201             c.getNewCriterion("INVOICE.COST", "1000", Criteria.GREATER_EQUAL);
202         Criteria.Criterion cn2 =
203             c.getNewCriterion("INVOICE.COST", "2000", Criteria.LESS_EQUAL);
204         Criteria.Criterion cn3 =
205             c.getNewCriterion("INVOICE.COST", "8000", Criteria.GREATER_EQUAL);
206         Criteria.Criterion cn4 =
207             c.getNewCriterion("INVOICE.COST", "9000", Criteria.LESS_EQUAL);
208         c.add(cn1.and(cn2));
209         c.or(cn3.and(cn4));
210 
211         String expect =
212             "SELECT  FROM INVOICE WHERE "
213             + "((INVOICE.COST>='1000' AND INVOICE.COST<='2000') "
214             + "OR (INVOICE.COST>='8000' AND INVOICE.COST<='9000'))";
215 
216         String result = null;
217         try
218         {
219             result = BasePeer.createQueryString(c);
220         }
221         catch (TorqueException e)
222         {
223             fail("TorqueException thrown in BasePeer.createQueryString()");
224         }
225 
226         assertEquals(expect, result);
227     }
228 
229     /***
230      * Test Criterion.setIgnoreCase().
231      * As the output is db specific the test just prints the result to
232      * System.out
233      */
234     public void testCriterionIgnoreCase()
235     {
236         Criteria myCriteria = new Criteria();
237 
238         Criteria.Criterion expected = myCriteria.getNewCriterion(
239                 "TABLE.COLUMN", (Object)"FoObAr", Criteria.LIKE);
240         Criteria.Criterion result = expected.setIgnoreCase(true);
241         assertEquals("Criterion mis-match after calling setIgnoreCase(true)",
242                      expected.toString(), result.toString());
243     }
244 
245     /***
246      * Test that true is evaluated correctly.
247      */
248     public void testBoolean()
249     {
250         Criteria c = new Criteria().add("TABLE.COLUMN", true);
251 
252         String expect = "SELECT  FROM TABLE WHERE TABLE.COLUMN=1";
253 
254         String result = null;
255         try
256         {
257             result = BasePeer.createQueryString(c);
258         }
259         catch (TorqueException e)
260         {
261             fail("TorqueException thrown in BasePeer.createQueryString()");
262         }
263 
264         assertEquals(expect, result);
265 
266         // test the postgresql variation
267         c = new Criteria();
268         Criteria.Criterion cc =
269             c.getNewCriterion("TABLE.COLUMN", Boolean.TRUE, Criteria.EQUAL);
270 
271         Configuration conf = new BaseConfiguration();
272         conf.addProperty("driver", "org.postgresql.Driver");
273         try
274         {
275             cc.setDB(DBFactory.create("org.postgresql.Driver"));
276         }
277         catch (Exception e)
278         {
279             fail("Exception thrown in DBFactory");
280         }
281 
282         assertEquals("TABLE.COLUMN=TRUE", cc.toString());
283     }
284 
285     /***
286      * testcase for addDate()
287      */
288     public void testAddDate()
289     {
290         Criteria c = new Criteria();
291         c.addDate("TABLE.DATE_COLUMN", 2003, 0, 22);
292 
293         String expect = "SELECT  FROM TABLE WHERE TABLE.DATE_COLUMN='20030122000000'";
294 
295         String result = null;
296         try
297         {
298             result = BasePeer.createQueryString(c);
299         }
300         catch (TorqueException e)
301         {
302             e.printStackTrace();
303             fail("TorqueException thrown in BasePeer.createQueryString()");
304         }
305         assertEquals(expect, result);
306     }
307 
308     /***
309      * testcase for add(Date)
310      */
311     public void testDateAdd()
312     {
313         Calendar cal = Calendar.getInstance();
314         cal.set(2003, 0, 22, 0, 0, 0);
315         Date date = cal.getTime();
316         Criteria c = new Criteria();
317         c.add("TABLE.DATE_COLUMN", date);
318 
319         String expect = "SELECT  FROM TABLE WHERE TABLE.DATE_COLUMN='20030122000000'";
320 
321         String result = null;
322         try
323         {
324             result = BasePeer.createQueryString(c);
325         }
326         catch (TorqueException e)
327         {
328             e.printStackTrace();
329             fail("TorqueException thrown in BasePeer.createQueryString()");
330         }
331         assertEquals(expect, result);
332     }
333 
334     public void testCurrentDate()
335     {
336         Criteria c = new Criteria()
337                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
338                 .add("TABLE.TIME_COLUMN", Criteria.CURRENT_TIME);
339 
340         String expect = "SELECT  FROM TABLE WHERE TABLE.TIME_COLUMN=CURRENT_TIME AND TABLE.DATE_COLUMN=CURRENT_DATE";
341 
342         String result = null;
343         try
344         {
345             result = BasePeer.createQueryString(c);
346         }
347         catch (TorqueException e)
348         {
349             e.printStackTrace();
350             fail("TorqueException thrown in BasePeer.createQueryString()");
351         }
352 
353         assertEquals(expect,result);
354     }
355 
356     public void testCountAster()
357     {
358         Criteria c = new Criteria()
359                 .addSelectColumn("COUNT(*)")
360                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
361                 .add("TABLE.TIME_COLUMN", Criteria.CURRENT_TIME);
362 
363         String expect = "SELECT COUNT(*) FROM TABLE WHERE TABLE.TIME_COLUMN=CURRENT_TIME AND TABLE.DATE_COLUMN=CURRENT_DATE";
364 
365         String result = null;
366         try
367         {
368             result = BasePeer.createQueryString(c);
369         }
370         catch (TorqueException e)
371         {
372             e.printStackTrace();
373             fail("TorqueException thrown in BasePeer.createQueryString()");
374         }
375 
376         assertEquals(expect,result);
377 
378     }
379 
380     /***
381      * This test case has been written to try out the fix applied to resolve
382      * TRQS73 - i.e. ensuring that Criteria.toString() does not alter any limit
383      * or offset that may be stored in the Criteria object.  This testcase
384      * could actually pass without the fix if the database in use does not
385      * support native limits and offsets.
386      */
387     public void testCriteriaToStringOffset()
388     {
389         Criteria c = new Criteria()
390                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
391                 .setOffset(3)
392                 .setLimit(5);
393 
394         String toStringExpect = "Criteria:: TABLE.DATE_COLUMN<=>TABLE.DATE_COLUMN=CURRENT_DATE:  "
395                 + "\nCurrent Query SQL (may not be complete or applicable): "
396                 + "SELECT  FROM TABLE WHERE TABLE.DATE_COLUMN=CURRENT_DATE LIMIT 3, 5";
397 
398         String cString = c.toString();
399         //System.out.println(cString);
400         assertEquals(toStringExpect, cString);
401 
402         // Note that this is intentially the same as above as the behaviour is
403         // only observed on subsequent invocations of toString().
404         cString = c.toString();
405         //System.out.println(cString);
406         assertEquals(toStringExpect, cString);
407     }
408 
409     /***
410      * This test case has been written to try out the fix applied to resolve
411      * TRQS73 - i.e. ensuring that Criteria.toString() does not alter any limit
412      * or offset that may be stored in the Criteria object.  This testcase
413      * could actually pass without the fix if the database in use does not
414      * support native limits and offsets.
415      */
416     public void testCriteriaToStringLimit()
417     {
418         Criteria c = new Criteria()
419                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
420                 .setLimit(5);
421 
422         String toStringExpect = "Criteria:: TABLE.DATE_COLUMN<=>TABLE.DATE_COLUMN=CURRENT_DATE:  "
423                 + "\nCurrent Query SQL (may not be complete or applicable): "
424                 + "SELECT  FROM TABLE WHERE TABLE.DATE_COLUMN=CURRENT_DATE LIMIT 5";
425 
426         String cString = c.toString();
427         //System.out.println(cString);
428         assertEquals(toStringExpect, cString);
429 
430         // Note that this is intentially the same as above as the behaviour is
431         // only observed on subsequent invocations of toString().
432         cString = c.toString();
433         //System.out.println(cString);
434         assertEquals(toStringExpect, cString);
435     }
436 
437     /***
438      * This test case verifies if the Criteria.LIKE comparison type will
439      * get replaced through Criteria.EQUAL if there are no SQL wildcards
440      * in the given value.
441      */
442     public void testLikeWithoutWildcards()
443     {
444         Criteria c = new Criteria();
445         c.add("TABLE.COLUMN", (Object) "no wildcards", Criteria.LIKE);
446 
447         String expect = "SELECT  FROM TABLE WHERE TABLE.COLUMN = 'no wildcards'";
448 
449         String result = null;
450         try
451         {
452             result = BasePeer.createQueryString(c);
453         }
454         catch (TorqueException e)
455         {
456             e.printStackTrace();
457             fail("TorqueException thrown in BasePeer.createQueryString()");
458         }
459 
460         assertEquals(expect, result);
461     }
462 
463     /***
464      * This test case verifies if the Criteria.NOT_LIKE comparison type will
465      * get replaced through Criteria.NOT_EQUAL if there are no SQL wildcards
466      * in the given value.
467      */
468     public void testNotLikeWithoutWildcards()
469     {
470         Criteria c = new Criteria();
471         c.add("TABLE.COLUMN", (Object) "no wildcards", Criteria.NOT_LIKE);
472 
473         String firstExpect = "SELECT  FROM TABLE WHERE TABLE.COLUMN != 'no wildcards'";
474         String secondExpect = "SELECT  FROM TABLE WHERE TABLE.COLUMN <> 'no wildcards'";
475 
476         String result = null;
477         try
478         {
479             result = BasePeer.createQueryString(c);
480         }
481         catch (TorqueException e)
482         {
483             e.printStackTrace();
484             fail("TorqueException thrown in BasePeer.createQueryString()");
485         }
486 
487         assertTrue(result.equals(firstExpect) || result.equals(secondExpect));
488     }
489 
490     /***
491      * Test that serialization works.
492      */
493     public void testSerialization()
494     {
495         c.setOffset(10);
496         c.setLimit(11);
497         c.setIgnoreCase(true);
498         c.setSingleRecord(true);
499         c.setCascade(true);
500         c.setDbName("myDB");
501         c.setAll();
502         c.setDistinct();
503         c.addSelectColumn("Author.NAME");
504         c.addSelectColumn("Author.AUTHOR_ID");
505         c.addDescendingOrderByColumn("Author.NAME");
506         c.addAscendingOrderByColumn("Author.AUTHOR_ID");
507         c.addAlias("Writer", "Author");
508         c.addAsColumn("AUTHOR_NAME", "Author.NAME");
509         c.addJoin("Author.AUTHOR_ID", "Book.AUTHOR_ID", Criteria.INNER_JOIN);
510         c.add("Author.NAME", (Object) "author%", Criteria.LIKE);
511 
512         // Some direct Criterion checks
513         Criterion cn = c.getCriterion("Author.NAME");
514         cn.setIgnoreCase(true);
515         assertEquals("author%", cn.getValue());
516         assertEquals(Criteria.LIKE, cn.getComparison());
517         Criterion cnDirectClone = (Criterion) SerializationUtils.clone(cn);
518         assertEquals(cn, cnDirectClone);
519 
520         // Clone the object
521         Criteria cClone = (Criteria) SerializationUtils.clone(c);
522 
523         // Check the clone
524         assertEquals(c.size(), cClone.size());
525         assertEquals(10, cClone.getOffset());
526         assertEquals(c.getOffset(), cClone.getOffset());
527         assertEquals(11, cClone.getLimit());
528         assertEquals(c.getLimit(), cClone.getLimit());
529         assertEquals(true, cClone.isIgnoreCase());
530         assertEquals(c.isIgnoreCase(), cClone.isIgnoreCase());
531         assertEquals(true, cClone.isSingleRecord());
532         assertEquals(c.isSingleRecord(), cClone.isSingleRecord());
533         assertEquals(true, cClone.isCascade());
534         assertEquals(c.isCascade(), cClone.isCascade());
535         assertEquals("myDB", cClone.getDbName());
536         assertEquals(c.getDbName(), cClone.getDbName());
537         List selectModifiersClone = cClone.getSelectModifiers();
538         assertTrue(selectModifiersClone.contains(Criteria.ALL.toString()));
539         assertTrue(selectModifiersClone.contains(Criteria.DISTINCT.toString()));
540         assertEquals(c.getSelectModifiers(), cClone.getSelectModifiers());
541         List selectColumnsClone = cClone.getSelectColumns();
542         assertTrue(selectColumnsClone.contains("Author.NAME"));
543         assertTrue(selectColumnsClone.contains("Author.AUTHOR_ID"));
544         assertEquals(c.getSelectColumns(), cClone.getSelectColumns());
545         List orderByColumnsClone = cClone.getOrderByColumns();
546         assertTrue(orderByColumnsClone.contains("Author.NAME DESC"));
547         assertTrue(orderByColumnsClone.contains("Author.AUTHOR_ID ASC"));
548         assertEquals(c.getOrderByColumns(), cClone.getOrderByColumns());
549         Map aliasesClone = cClone.getAliases();
550         assertTrue(aliasesClone.containsKey("Writer"));
551         assertEquals("Author", aliasesClone.get("Writer"));
552         assertEquals(c.getAliases(), cClone.getAliases());
553         Map asColumnsClone = cClone.getAsColumns();
554         assertTrue(asColumnsClone.containsKey("AUTHOR_NAME"));
555         assertEquals("Author.NAME", asColumnsClone.get("AUTHOR_NAME"));
556         assertEquals(c.getAsColumns(), cClone.getAsColumns());
557 
558         // Check Joins
559         List joinsClone = cClone.getJoins();
560         Join joinClone = (Join) joinsClone.get(0);
561         assertEquals("Author.AUTHOR_ID", joinClone.getLeftColumn());
562         assertEquals("Book.AUTHOR_ID", joinClone.getRightColumn());
563         assertEquals(Criteria.INNER_JOIN, joinClone.getJoinType());
564         assertEquals(c.getJoins(), cClone.getJoins());
565 
566         // Some Criterion checks
567         Criterion cnClone = cClone.getCriterion("Author.NAME");
568         assertEquals("author%", cnClone.getValue());
569         assertEquals(Criteria.LIKE, cnClone.getComparison());
570         assertEquals(cn.isIgnoreCase(), cnClone.isIgnoreCase());
571 
572         // Confirm that equals() checks all of the above.
573         assertEquals(c, cClone);
574 
575         // Check hashCode() too.
576         assertEquals(c.hashCode(), cClone.hashCode());
577     }
578 
579     /***
580      * test for TRQS25
581      */
582 /*
583  *    public void testCriteriaAndString()
584  *    {
585  *        Criteria c = new Criteria()
586  *                .add("TABLE.COLUMN1", "string")
587  *                .and("TABLE.COLUMN2", "string", Criteria.LIKE);
588  *    }
589  */
590 }