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  
23  import org.apache.commons.configuration.BaseConfiguration;
24  import org.apache.commons.configuration.Configuration;
25  
26  import org.apache.torque.BaseTestCase;
27  import org.apache.torque.TorqueException;
28  import org.apache.torque.adapter.DBFactory;
29  
30  /***
31   * Test class for Criteria.
32   *
33   * @author <a href="mailto:celkins@scardini.com">Christopher Elkins</a>
34   * @author <a href="mailto:sam@neurogrid.com">Sam Joseph</a>
35   * @version $Id: CriteriaTest.java 280326 2005-09-12 12:27:27Z seade $
36   */
37  public class CriteriaTest extends BaseTestCase
38  {
39  
40      /*** The criteria to use in the test. */
41      private Criteria c;
42  
43      /***
44       * Creates a new instance.
45       *
46       * @param name the name of the test to run
47       */
48      public CriteriaTest(String name)
49      {
50          super(name);
51      }
52  
53      /***
54       * Initializes the criteria.
55       */
56      public void setUp()
57      {
58          super.setUp();
59          c = new Criteria();
60      }
61  
62      /***
63       * Test basic adding of strings.
64       */
65      public void testAddString()
66      {
67          final String table = "myTable";
68          final String column = "myColumn";
69          final String value = "myValue";
70  
71          // Add the string
72          c.add(table, column, (Object) value);
73  
74          // Verify that the key exists
75          assertTrue(c.containsKey(table, column));
76  
77          // Verify that what we get out is what we put in
78          assertTrue(c.getString(table, column).equals(value));
79      }
80  
81      /***
82       * test various properties of Criterion and nested criterion
83       */
84      public void testNestedCriterion()
85      {
86          final String table2 = "myTable2";
87          final String column2 = "myColumn2";
88          final String value2 = "myValue2";
89  
90          final String table3 = "myTable3";
91          final String column3 = "myColumn3";
92          final String value3 = "myValue3";
93  
94          final String table4 = "myTable4";
95          final String column4 = "myColumn4";
96          final String value4 = "myValue4";
97  
98          final String table5 = "myTable5";
99          final String column5 = "myColumn5";
100         final String value5 = "myValue5";
101 
102         Criteria.Criterion crit2 =
103             c.getNewCriterion(table2, column2, (Object) value2, Criteria.EQUAL);
104         Criteria.Criterion crit3 =
105             c.getNewCriterion(table3, column3, (Object) value3, Criteria.EQUAL);
106         Criteria.Criterion crit4 =
107             c.getNewCriterion(table4, column4, (Object) value4, Criteria.EQUAL);
108         Criteria.Criterion crit5 =
109             c.getNewCriterion(table5, column5, (Object) value5, Criteria.EQUAL);
110 
111         crit2.and(crit3).or(crit4.and(crit5));
112         String expect =
113             "((myTable2.myColumn2='myValue2' "
114                 + "AND myTable3.myColumn3='myValue3') "
115             + "OR (myTable4.myColumn4='myValue4' "
116                 + "AND myTable5.myColumn5='myValue5'))";
117         String result = crit2.toString();
118         assertEquals(expect, result);
119 
120         Criteria.Criterion crit6 =
121             c.getNewCriterion(table2, column2, (Object) value2, Criteria.EQUAL);
122         Criteria.Criterion crit7 =
123             c.getNewCriterion(table3, column3, (Object) value3, Criteria.EQUAL);
124         Criteria.Criterion crit8 =
125             c.getNewCriterion(table4, column4, (Object) value4, Criteria.EQUAL);
126         Criteria.Criterion crit9 =
127             c.getNewCriterion(table5, column5, (Object) value5, Criteria.EQUAL);
128 
129         crit6.and(crit7).or(crit8).and(crit9);
130         expect =
131             "(((myTable2.myColumn2='myValue2' "
132                     + "AND myTable3.myColumn3='myValue3') "
133                 + "OR myTable4.myColumn4='myValue4') "
134                     + "AND myTable5.myColumn5='myValue5')";
135         result = crit6.toString();
136         assertEquals(expect, result);
137 
138         // should make sure we have tests for all possibilities
139 
140         Criteria.Criterion[] crita = crit2.getAttachedCriterion();
141 
142         assertEquals(crit2, crita[0]);
143         assertEquals(crit3, crita[1]);
144         assertEquals(crit4, crita[2]);
145         assertEquals(crit5, crita[3]);
146 
147         List tables = crit2.getAllTables();
148 
149         assertEquals(crit2.getTable(), tables.get(0));
150         assertEquals(crit3.getTable(), tables.get(1));
151         assertEquals(crit4.getTable(), tables.get(2));
152         assertEquals(crit5.getTable(), tables.get(3));
153 
154         // simple confirmations that equality operations work
155         assertTrue(crit2.hashCode() == crit2.hashCode());
156         assertEquals(crit2.toString(), crit2.toString());
157     }
158 
159     /***
160      * Tests &lt;= and =&gt;.
161      */
162     public void testBetweenCriterion()
163     {
164         Criteria.Criterion cn1 =
165             c.getNewCriterion(
166                 "INVOICE.COST",
167                 new Integer(1000),
168                 Criteria.GREATER_EQUAL);
169         Criteria.Criterion cn2 =
170             c.getNewCriterion(
171                 "INVOICE.COST",
172                 new Integer(5000),
173                 Criteria.LESS_EQUAL);
174         c.add(cn1.and(cn2));
175         String expect =
176             "SELECT  FROM INVOICE WHERE "
177             + "(INVOICE.COST>=1000 AND INVOICE.COST<=5000)";
178         String result = null;
179         try
180         {
181             result = BasePeer.createQueryString(c);
182         }
183         catch (TorqueException e)
184         {
185             fail("TorqueException thrown in BasePeer.createQueryString()");
186         }
187 
188         assertEquals(expect, result);
189     }
190 
191     /***
192      * Verify that AND and OR criterion are nested correctly.
193      */
194     public void testPrecedence()
195     {
196         Criteria.Criterion cn1 =
197             c.getNewCriterion("INVOICE.COST", "1000", Criteria.GREATER_EQUAL);
198         Criteria.Criterion cn2 =
199             c.getNewCriterion("INVOICE.COST", "2000", Criteria.LESS_EQUAL);
200         Criteria.Criterion cn3 =
201             c.getNewCriterion("INVOICE.COST", "8000", Criteria.GREATER_EQUAL);
202         Criteria.Criterion cn4 =
203             c.getNewCriterion("INVOICE.COST", "9000", Criteria.LESS_EQUAL);
204         c.add(cn1.and(cn2));
205         c.or(cn3.and(cn4));
206 
207         String expect =
208             "SELECT  FROM INVOICE WHERE "
209             + "((INVOICE.COST>='1000' AND INVOICE.COST<='2000') "
210             + "OR (INVOICE.COST>='8000' AND INVOICE.COST<='9000'))";
211 
212         String result = null;
213         try
214         {
215             result = BasePeer.createQueryString(c);
216         }
217         catch (TorqueException e)
218         {
219             fail("TorqueException thrown in BasePeer.createQueryString()");
220         }
221 
222         assertEquals(expect, result);
223     }
224 
225     /***
226      * Test Criterion.setIgnoreCase().
227      * As the output is db specific the test just prints the result to
228      * System.out
229      */
230     public void testCriterionIgnoreCase()
231     {
232         Criteria myCriteria = new Criteria();
233 
234         Criteria.Criterion expected = myCriteria.getNewCriterion(
235                 "TABLE.COLUMN", (Object)"FoObAr", Criteria.LIKE);
236         Criteria.Criterion result = expected.setIgnoreCase(true);
237         assertEquals("Criterion mis-match after calling setIgnoreCase(true)",
238                      expected.toString(), result.toString());
239     }
240 
241     /***
242      * Test that true is evaluated correctly.
243      */
244     public void testBoolean()
245     {
246         Criteria c = new Criteria().add("TABLE.COLUMN", true);
247 
248         String expect = "SELECT  FROM TABLE WHERE TABLE.COLUMN=1";
249 
250         String result = null;
251         try
252         {
253             result = BasePeer.createQueryString(c);
254         }
255         catch (TorqueException e)
256         {
257             fail("TorqueException thrown in BasePeer.createQueryString()");
258         }
259 
260         assertEquals(expect, result);
261 
262         // test the postgresql variation
263         c = new Criteria();
264         Criteria.Criterion cc =
265             c.getNewCriterion("TABLE.COLUMN", Boolean.TRUE, Criteria.EQUAL);
266 
267         Configuration conf = new BaseConfiguration();
268         conf.addProperty("driver", "org.postgresql.Driver");
269         try
270         {
271             cc.setDB(DBFactory.create("org.postgresql.Driver"));
272         }
273         catch (Exception e)
274         {
275             fail("Exception thrown in DBFactory");
276         }
277 
278         assertEquals("TABLE.COLUMN=TRUE", cc.toString());
279     }
280 
281     /***
282      * testcase for addDate()
283      */
284     public void testAddDate()
285     {
286         Criteria c = new Criteria();
287         c.addDate("TABLE.DATE_COLUMN", 2003, 0, 22);
288 
289         String expect = "SELECT  FROM TABLE WHERE TABLE.DATE_COLUMN='20030122000000'";
290 
291         String result = null;
292         try
293         {
294             result = BasePeer.createQueryString(c);
295         }
296         catch (TorqueException e)
297         {
298             e.printStackTrace();
299             fail("TorqueException thrown in BasePeer.createQueryString()");
300         }
301         assertEquals(expect, result);
302     }
303 
304     /***
305      * testcase for add(Date)
306      */
307     public void testDateAdd()
308     {
309         Calendar cal = Calendar.getInstance();
310         cal.set(2003, 0, 22, 0, 0, 0);
311         Date date = cal.getTime();
312         Criteria c = new Criteria();
313         c.add("TABLE.DATE_COLUMN", date);
314 
315         String expect = "SELECT  FROM TABLE WHERE TABLE.DATE_COLUMN='20030122000000'";
316 
317         String result = null;
318         try
319         {
320             result = BasePeer.createQueryString(c);
321         }
322         catch (TorqueException e)
323         {
324             e.printStackTrace();
325             fail("TorqueException thrown in BasePeer.createQueryString()");
326         }
327         assertEquals(expect, result);
328     }
329 
330     public void testCurrentDate()
331     {
332         Criteria c = new Criteria()
333                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
334                 .add("TABLE.TIME_COLUMN", Criteria.CURRENT_TIME);
335 
336         String expect = "SELECT  FROM TABLE WHERE TABLE.TIME_COLUMN=CURRENT_TIME AND TABLE.DATE_COLUMN=CURRENT_DATE";
337 
338         String result = null;
339         try
340         {
341             result = BasePeer.createQueryString(c);
342         }
343         catch (TorqueException e)
344         {
345             e.printStackTrace();
346             fail("TorqueException thrown in BasePeer.createQueryString()");
347         }
348 
349         assertEquals(expect,result);
350     }
351 
352     public void testCountAster()
353     {
354         Criteria c = new Criteria()
355                 .addSelectColumn("COUNT(*)")
356                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
357                 .add("TABLE.TIME_COLUMN", Criteria.CURRENT_TIME);
358 
359         String expect = "SELECT COUNT(*) FROM TABLE WHERE TABLE.TIME_COLUMN=CURRENT_TIME AND TABLE.DATE_COLUMN=CURRENT_DATE";
360 
361         String result = null;
362         try
363         {
364             result = BasePeer.createQueryString(c);
365         }
366         catch (TorqueException e)
367         {
368             e.printStackTrace();
369             fail("TorqueException thrown in BasePeer.createQueryString()");
370         }
371 
372         assertEquals(expect,result);
373 
374     }
375 
376     /***
377      * This test case has been written to try out the fix applied to resolve
378      * TRQS73 - i.e. ensuring that Criteria.toString() does not alter any limit
379      * or offset that may be stored in the Criteria object.  This testcase
380      * could actually pass without the fix if the database in use does not
381      * support native limits and offsets.
382      */
383     public void testCriteriaToStringOffset()
384     {
385         Criteria c = new Criteria()
386                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
387                 .setOffset(3)
388                 .setLimit(5);
389 
390         String toStringExpect = "Criteria:: TABLE.DATE_COLUMN<=>TABLE.DATE_COLUMN=CURRENT_DATE:  "
391                 + "\nCurrent Query SQL (may not be complete or applicable): "
392                 + "SELECT  FROM TABLE WHERE TABLE.DATE_COLUMN=CURRENT_DATE LIMIT 3, 5";
393 
394         String cString = c.toString();
395         //System.out.println(cString);
396         assertEquals(toStringExpect, cString);
397 
398         // Note that this is intentially the same as above as the behaviour is
399         // only observed on subsequent invocations of toString().
400         cString = c.toString();
401         //System.out.println(cString);
402         assertEquals(toStringExpect, cString);
403     }
404 
405     /***
406      * This test case has been written to try out the fix applied to resolve
407      * TRQS73 - i.e. ensuring that Criteria.toString() does not alter any limit
408      * or offset that may be stored in the Criteria object.  This testcase
409      * could actually pass without the fix if the database in use does not
410      * support native limits and offsets.
411      */
412     public void testCriteriaToStringLimit()
413     {
414         Criteria c = new Criteria()
415                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
416                 .setLimit(5);
417 
418         String toStringExpect = "Criteria:: TABLE.DATE_COLUMN<=>TABLE.DATE_COLUMN=CURRENT_DATE:  "
419                 + "\nCurrent Query SQL (may not be complete or applicable): "
420                 + "SELECT  FROM TABLE WHERE TABLE.DATE_COLUMN=CURRENT_DATE LIMIT 5";
421 
422         String cString = c.toString();
423         //System.out.println(cString);
424         assertEquals(toStringExpect, cString);
425 
426         // Note that this is intentially the same as above as the behaviour is
427         // only observed on subsequent invocations of toString().
428         cString = c.toString();
429         //System.out.println(cString);
430         assertEquals(toStringExpect, cString);
431     }
432 
433     /***
434      * This test case verifies if the Criteria.LIKE comparison type will
435      * get replaced through Criteria.EQUAL if there are no SQL wildcards
436      * in the given value.
437      */
438     public void testLikeWithoutWildcards()
439     {
440         Criteria c = new Criteria();
441         c.add("TABLE.COLUMN", (Object) "no wildcards", Criteria.LIKE);
442 
443         String expect = "SELECT  FROM TABLE WHERE TABLE.COLUMN = 'no wildcards'";
444 
445         String result = null;
446         try
447         {
448             result = BasePeer.createQueryString(c);
449         }
450         catch (TorqueException e)
451         {
452             e.printStackTrace();
453             fail("TorqueException thrown in BasePeer.createQueryString()");
454         }
455 
456         assertEquals(expect, result);
457     }
458 
459     /***
460      * This test case verifies if the Criteria.NOT_LIKE comparison type will
461      * get replaced through Criteria.NOT_EQUAL if there are no SQL wildcards
462      * in the given value.
463      */
464     public void testNotLikeWithoutWildcards()
465     {
466         Criteria c = new Criteria();
467         c.add("TABLE.COLUMN", (Object) "no wildcards", Criteria.NOT_LIKE);
468 
469         String firstExpect = "SELECT  FROM TABLE WHERE TABLE.COLUMN != 'no wildcards'";
470         String secondExpect = "SELECT  FROM TABLE WHERE TABLE.COLUMN <> 'no wildcards'";
471 
472         String result = null;
473         try
474         {
475             result = BasePeer.createQueryString(c);
476         }
477         catch (TorqueException e)
478         {
479             e.printStackTrace();
480             fail("TorqueException thrown in BasePeer.createQueryString()");
481         }
482 
483         assertTrue(result.equals(firstExpect) || result.equals(secondExpect));
484     }
485 
486 //    /***
487 //     * Very basic test that serialization works.
488 //     */
489 //    public void testSerialization()
490 //    {
491 //        // Hashtable works:
492 //        Hashtable h = new Hashtable();
493 //        h.put("Author.NAME", "author");
494 //        Hashtable hClone = (Hashtable) SerializationUtils.clone(h);
495 //        assertEquals(h, hClone);
496 //
497 //        // Criterion works (even though Bloch strongly recommends against inner
498 //        // classes implementing Serializable):
499 //        Criterion cn = c.new Criterion("Author.NAME", "author");
500 //        Criterion cnClone = (Criterion) SerializationUtils.clone(cn);
501 //        assertEquals(cn, cnClone);
502 //
503 //        // Criteria DOES NOT WORK - i.e. it isn't actually Serializable at present.
504 //        // http://mail-archives.apache.org/mod_mbox/db-torque-dev/200404.mbox/%3CF4DFE8EDB932F641A9407CFB5CA599C3F17DF1@e2kmtl1.internal.sungard.corp%3E
505 //        c.add("Author.NAME", "author");
506 //        Criteria cClone = (Criteria) SerializationUtils.clone(c);
507 //        assertEquals(c, cClone);
508 //    }
509 
510     /***
511      * test for TRQS25
512      */
513 /*
514  *    public void testCriteriaAndString()
515  *    {
516  *        Criteria c = new Criteria()
517  *                .add("TABLE.COLUMN1", "string")
518  *                .and("TABLE.COLUMN2", "string", Criteria.LIKE);
519  *    }
520  */
521 }