TestRewrite2SQL


1   /**
2    * MEDOR: Middleware Enabling Distributed Object Requests
3    *
4    * Copyright (C) 2001-2005 France Telecom R&D
5    * Contact: alexandre.lefebvre@rd.francetelecom.com
6    *
7    * This library is free software; you can redistribute it and/or
8    * modify it under the terms of the GNU Lesser General Public
9    * License as published by the Free Software Foundation; either
10   * version 2.1 of the License, or (at your option) any later version.
11   *
12   * This library is distributed in the hope that it will be useful,
13   * but WITHOUT ANY WARRANTY; without even the implied warranty of
14   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
15   * Lesser General Public License for more details.
16   *
17   * You should have received a copy of the GNU Lesser General Public
18   * License along with this library; if not, write to the Free Software
19   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
20   *
21   * Initial developers: M. Alia, S. Chassande-Barrioz, A. Lefebvre
22   */
23  
24  package org.objectweb.medor.query.rdb.lib;
25  
26  import junit.framework.Test;
27  import junit.framework.TestSuite;
28  import junit.textui.TestRunner;
29  import org.objectweb.jorm.type.api.PTypeSpace;
30  import org.objectweb.jorm.mapper.rdb.adapter.BasicRdbAdapter;
31  import org.objectweb.jorm.mapper.rdb.adapter.MysqlAdapter;
32  import org.objectweb.jorm.mapper.rdb.adapter.PostgresAdapter;
33  import org.objectweb.jorm.mapper.rdb.adapter.OracleAdapter;
34  import org.objectweb.medor.filter.api.FieldOperand;
35  import org.objectweb.medor.expression.api.ParameterOperand;
36  import org.objectweb.medor.expression.api.Expression;
37  import org.objectweb.medor.expression.api.Operand;
38  import org.objectweb.medor.expression.lib.BasicOperand;
39  import org.objectweb.medor.expression.lib.And;
40  import org.objectweb.medor.filter.lib.BasicFieldOperand;
41  import org.objectweb.medor.expression.lib.Abs;
42  import org.objectweb.medor.expression.lib.BasicParameterOperand;
43  import org.objectweb.medor.expression.lib.Concat;
44  import org.objectweb.medor.expression.lib.Equal;
45  import org.objectweb.medor.expression.lib.FirstLocate;
46  import org.objectweb.medor.expression.lib.Greater;
47  import org.objectweb.medor.expression.lib.Sqrt;
48  import org.objectweb.medor.filter.lib.InCollection;
49  import org.objectweb.medor.expression.lib.Lower;
50  import org.objectweb.medor.filter.lib.MemberOf;
51  import org.objectweb.medor.expression.lib.Mod;
52  import org.objectweb.medor.expression.lib.Substring;
53  import org.objectweb.medor.filter.jorm.lib.IsNullPName;
54  import org.objectweb.medor.lib.Log;
55  import org.objectweb.medor.lib.TestMedorHelper;
56  import org.objectweb.medor.query.api.OrderField;
57  import org.objectweb.medor.query.lib.BasicOrderField;
58  import org.objectweb.medor.query.rdb.api.QualifiedTable;
59  import org.objectweb.medor.query.rdb.api.RdbExpField;
60  import org.objectweb.medor.query.rdb.api.RdbExpQueryLeaf;
61  import org.objectweb.medor.type.lib.PTypeSpaceMedor;
62  import org.objectweb.util.monolog.api.BasicLevel;
63  
64  import java.util.ArrayList  ;
65  import java.util.Collection  ;
66  
67  public class TestRewrite2SQL extends TestMedorHelper {
68  
69      /**
70       * main method to launch the tests manually
71       */
72      public static void main(String  [] args) {
73          TestRunner.run(suite());
74      }
75  
76      /**
77       * This method creates a TestSuite object with the current tests
78       */
79      public static Test suite() {
80          return new TestSuite(TestRewrite2SQL.class);
81      }
82  
83      public TestRewrite2SQL() {
84          super("TestRewrite2SQL", Log.MEDOR_PREFIX + ".query.rdb.lib.rewrite2sql");
85      }
86  
87      public TestRewrite2SQL(String   testName) {
88          super(testName, Log.MEDOR_PREFIX + ".query.rdb.lib.rewrite2sql");
89      }
90  
91      protected void setUp() {
92      }
93  
94      protected void tearDown() {
95      }
96  
97      public void testAliasesSimple() {
98          try {
99              //create qualified tables
100             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
101             QualifiedTable t2 = new BasicQualifiedTable("t2", "al2");
102             QualifiedTable[] tables = {t1, t2};
103             //create a new leaf
104             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
105             //add fields
106             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
107             RdbExpField f2 = ql.addRdbField("f2", null, "col2", t2);
108             //create a filter to be added to the query leaf
109             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
110             FieldOperand fieldOp2 = new BasicFieldOperand(f2);
111             BasicOperand valueL = new BasicOperand(1013);
112             BasicOperand valueR = new BasicOperand("coucou");
113             Expression left = new Lower(fieldOp1, valueL);
114             Expression right = new Greater(fieldOp2, valueR);
115             Expression filter = new And(left, right);
116             //assign the filter to the query leaf
117             ql.setQueryFilter(filter);
118             logger.log(BasicLevel.DEBUG, "testAliasesSimple");
119             //compute the SQL query
120             logger.log(BasicLevel.DEBUG, ql.getSqlRequest(null,
121                     false, false));
122             assertEquals("Bad sql request",
123                 "SELECT t1.col1, al2.col2 FROM t1, t2 as al2 WHERE ((t1.col1 < 1013) AND (al2.col2 > 'coucou'))",
124                 ql.getSqlRequest(null, false, false));
125         } catch (Exception   e) {
126             e.printStackTrace();
127             fail(e.getMessage());
128         }
129     }
130 
131     public void testDistinct() {
132         try {
133             //create qualified tables
134             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
135             QualifiedTable t2 = new BasicQualifiedTable("t2", "al2");
136             QualifiedTable[] tables = {t1, t2};
137             //create a new leaf
138             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
139             ql.setDistinct(true);
140             //add fields
141             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
142             RdbExpField f2 = ql.addRdbField("f2", null, "col2", t2);
143             //create a filter to be added to the query leaf
144             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
145             FieldOperand fieldOp2 = new BasicFieldOperand(f2);
146             BasicOperand valueL = new BasicOperand(1013);
147             BasicOperand valueR = new BasicOperand("coucou");
148             Expression left = new Lower(fieldOp1, valueL);
149             Expression right = new Greater(fieldOp2, valueR);
150             Expression filter = new And(left, right);
151             //assigne the filter to the query leaf
152             ql.setQueryFilter(filter);
153             logger.log(BasicLevel.DEBUG, "testDistinct");
154             //computes the SQL query
155             logger.log(BasicLevel.DEBUG,
156                     ql.getSqlRequest(null, false, false));
157             assertEquals("Bad sql request",
158                 "SELECT DISTINCT t1.col1, al2.col2 FROM t1, t2 as al2 WHERE ((t1.col1 < 1013) AND (al2.col2 > 'coucou'))",
159                 ql.getSqlRequest(null, false, false));
160         } catch (Exception   e) {
161             fail(e.getMessage());
162         }
163     }
164 
165     public void testOrderBy() {
166         try {
167             //create qualified tables
168             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
169             QualifiedTable t2 = new BasicQualifiedTable("t2", "al2");
170             QualifiedTable[] tables = {t1, t2};
171             //create a new leaf
172             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
173             ql.setDistinct(true);
174             //add fields
175             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
176             RdbExpField f2 = ql.addRdbField("f2", null, "col2", t2);
177             //create a filter to be added to the query leaf
178             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
179             FieldOperand fieldOp2 = new BasicFieldOperand(f2);
180             BasicOperand valueL = new BasicOperand(1013);
181             BasicOperand valueR = new BasicOperand("coucou");
182             Expression left = new Lower(fieldOp1, valueL);
183             Expression right = new Greater(fieldOp2, valueR);
184             Expression filter = new And(left, right);
185             //assign the filter to the query leaf
186             ql.setQueryFilter(filter);
187             //add an OrderBy Field
188             OrderField of1 = new BasicOrderField(f1, true);
189             OrderField of2 = new BasicOrderField(f2);
190             ql.setOrderBy(new OrderField[] {of1, of2});
191             logger.log(BasicLevel.DEBUG, "testOrderBy");
192             //compute the SQL query
193             logger.log(BasicLevel.DEBUG,
194                     ql.getSqlRequest(null, false, false));
195             assertEquals("Bad sql request",
196                 "SELECT DISTINCT t1.col1, al2.col2 FROM t1, t2 as al2 WHERE ((t1.col1 < 1013) AND (al2.col2 > 'coucou')) ORDER BY t1.col1 DESC, al2.col2 ASC",
197                 ql.getSqlRequest(null, false, false));
198         } catch (Exception   e) {
199             fail(e.getMessage());
200         }
201     }
202 
203     public void testOrderByDistinct() {
204         try {
205             //create qualified tables
206             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
207             QualifiedTable t2 = new BasicQualifiedTable("t2", "al2");
208             QualifiedTable[] tables = {t1, t2};
209             //create a new leaf
210             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
211             ql.setDistinct(true);
212             //add fields
213             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
214             ql.removeRdbField(f1);
215             RdbExpField f2 = ql.addRdbField("f2", null, "col2", t2);
216             //create a filter to be added to the query leaf
217             FieldOperand fieldOp2 = new BasicFieldOperand(f2);
218             BasicOperand valueL = new BasicOperand(1013);
219             BasicOperand valueR = new BasicOperand("coucou");
220             Expression filter = new Greater(fieldOp2, valueR);
221             //assign the filter to the query leaf
222             ql.setQueryFilter(filter);
223             //add an OrderBy Field
224             OrderField of1 = new BasicOrderField(f1, true);
225             OrderField of2 = new BasicOrderField(f2);
226             ql.setOrderBy(new OrderField[] {of1, of2});
227             logger.log(BasicLevel.DEBUG, "testOrderBy");
228             //compute the SQL query
229             logger.log(BasicLevel.DEBUG,
230                     ql.getSqlRequest(null, false, false));
231             assertEquals("Bad sql request",
232                 "SELECT DISTINCT al2.col2, t1.col1 FROM t1, t2 as al2 WHERE (al2.col2 > 'coucou') ORDER BY t1.col1 DESC, al2.col2 ASC",
233                 ql.getSqlRequest(null, false, false));
234         } catch (Exception   e) {
235             fail(e.getMessage());
236         }
237     }
238     public void testMemberOfParam() {
239         try {
240             //create qualified tables
241             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
242             QualifiedTable t2 = new BasicQualifiedTable("t2", "al2");
243             QualifiedTable[] table1 = {t1};
244             QualifiedTable[] table2 = {t2};
245             //create a new leaf
246             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, table1, "");
247             RdbExpQueryLeaf q2 = new BasicRdbExpQueryLeaf(null, table2, "");
248             //add fields
249             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
250             RdbExpField f2 = q2.addRdbField("f2", null, "col2", t2);
251             //create a filter to be added to the query leaf
252             FieldOperand fieldOp2 = new BasicFieldOperand(f2);
253             BasicOperand value = new BasicOperand(1013);
254             ArrayList   al1 = new ArrayList  ();
255             ArrayList   al2 = new ArrayList  ();
256             al1.add(value);
257             al2.add(fieldOp2);
258             Expression filter = new MemberOf(al1, al2);
259             //assign the filter to the query leaf
260             ql.setQueryFilter(filter);
261             logger.log(BasicLevel.DEBUG, "testMemberOfParam");
262             //compute the SQL query
263             logger.log(BasicLevel.DEBUG,
264                     ql.getSqlRequest(null, false, false));
265             assertEquals("Bad sql request",
266                 "SELECT t1.col1 FROM t1 WHERE (1013 IN (SELECT al2.col2 FROM t2 as al2))",
267                 ql.getSqlRequest(null, false, false));
268         } catch (Exception   e) {
269             e.printStackTrace(System.out);
270             fail(e.getMessage());
271         }
272     }
273 
274     public void testMemberOfTwo() {
275         try {
276             //create qualified tables
277             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
278             QualifiedTable t2 = new BasicQualifiedTable("t2", "al2");
279             QualifiedTable[] table1 = {t1};
280             QualifiedTable[] table2 = {t2};
281             //create a new leaf
282             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, table1, "");
283             RdbExpQueryLeaf q2 = new BasicRdbExpQueryLeaf(null, table2, "");
284             //add fields
285             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
286             RdbExpField f1a = ql.addRdbField("f1a", null, "col1a", t1);
287             RdbExpField f1b = ql.addRdbField("f1b", null, "col1b", t1);
288             RdbExpField f2a = q2.addRdbField("f2a", null, "col2a", t2);
289             RdbExpField f2b = q2.addRdbField("f2b", null, "col2b", t2);
290             //create a filter to be added to the query leaf
291             FieldOperand fieldOp1a = new BasicFieldOperand(f1a);
292             FieldOperand fieldOp1b = new BasicFieldOperand(f1b);
293             FieldOperand fieldOp2a = new BasicFieldOperand(f2a);
294             FieldOperand fieldOp2b = new BasicFieldOperand(f2b);
295             ArrayList   al1 = new ArrayList  ();
296             ArrayList   al2 = new ArrayList  ();
297             al1.add(fieldOp1a);
298             al1.add(fieldOp1b);
299             al2.add(fieldOp2a);
300             al2.add(fieldOp2b);
301             Expression filter = new MemberOf(al1, al2);
302             //assign the filter to the query leaf
303             ql.setQueryFilter(filter);
304             logger.log(BasicLevel.DEBUG, "testMemberOfTwo");
305             //compute the SQL query
306             logger.log(BasicLevel.DEBUG,
307                     ql.getSqlRequest(null, false, false));
308             assertEquals("Bad sql request",
309                 "SELECT t1.col1, t1.col1a, t1.col1b FROM t1 WHERE (t1.col1a, t1.col1b IN (SELECT al2.col2a, al2.col2b FROM t2 as al2))",
310                 ql.getSqlRequest(null, false, false));
311         } catch (Exception   e) {
312             e.printStackTrace(System.out);
313             fail(e.getMessage());
314         }
315     }
316 
317     public void testAliasesWithParam() {
318         try {
319             //create qualified tables
320             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
321             QualifiedTable t2 = new BasicQualifiedTable("t2", "al2");
322             QualifiedTable[] tables = {t1, t2};
323             //create a new leaf
324             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
325             //add fields
326             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
327             RdbExpField f2 = ql.addRdbField("f2", null, "col2", t2);
328             //create a filter to be added to the query leaf
329             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
330             FieldOperand fieldOp2 = new BasicFieldOperand(f2);
331             BasicOperand valueL = new BasicOperand(1013);
332             BasicOperand valueR = new BasicOperand("coucou");
333             Expression left = new Lower(fieldOp1, valueL);
334             Expression right = new Greater(fieldOp2, valueR);
335             Expression filter = new And(left, right);
336             //assign the filter to the query leaf
337             ql.setQueryFilter(filter);
338             ParameterOperand fieldOp3 =
339                 new BasicParameterOperand(PTypeSpaceMedor.STRING, "x");
340             Expression left2 = new Lower(fieldOp3, valueL);
341             filter = new And(left2, right);
342             ql.setQueryFilter(filter);
343             ql.addGroupBy(f2);
344             logger.log(BasicLevel.DEBUG, "testAliasesWithParam");
345             //compute the SQL query
346             logger.log(BasicLevel.DEBUG,
347                     ql.getSqlRequest(null, false, false));
348             assertEquals("Bad sql request",
349                 "SELECT t1.col1, al2.col2 FROM t1, t2 as al2 WHERE ((? < 1013) AND (al2.col2 > 'coucou')) GROUP BY al2.col2",
350                 ql.getSqlRequest(null, false, false));
351         } catch (Exception   e) {
352             fail(e.getMessage());
353         }
354     }
355 
356     public void testMod() {
357         try {
358             //create qualified tables
359             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
360             QualifiedTable t2 = new BasicQualifiedTable("t2", "al2");
361             QualifiedTable[] tables = {t1, t2};
362             //create a new leaf
363             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
364             //add fields
365             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
366             RdbExpField f2 = ql.addRdbField("f2", null, "col2", t2);
367             //create a filter to be added to the query leaf
368             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
369             FieldOperand fieldOp2 = new BasicFieldOperand(f2);
370             BasicOperand valueL = new BasicOperand(1013);
371             Expression mod = new Mod(fieldOp1, valueL);
372             Expression right = new Lower(fieldOp2, mod);
373             ParameterOperand paramOp3 =
374                 new BasicParameterOperand(PTypeSpaceMedor.INT, "x");
375             Expression mod2 = new Mod(paramOp3, fieldOp1);
376             Expression left = new Greater(fieldOp2, mod2);
377 
378             Expression filter = new And(left, right);
379             //assign the filter to the query leaf
380             ql.setQueryFilter(filter);
381             ql.setRdbAdapter(new MysqlAdapter());
382 
383             logger.log(BasicLevel.DEBUG, "testAliasesWithParam");
384             //compute the SQL query
385             logger.log(BasicLevel.DEBUG,
386                     ql.getSqlRequest(null, false, false));
387             assertEquals("Bad sql request",
388                 "SELECT t1.col1, al2.col2 FROM t1, t2 as al2 WHERE ((al2.col2 > {fn mod(?, t1.col1)}) AND (al2.col2 < {fn mod(t1.col1, 1013)}))",
389                 ql.getSqlRequest(null, false, false));
390         } catch (Exception   e) {
391             fail(e.getMessage());
392         }
393     }
394 
395         public void testSubstring() {
396         try {
397             //create qualified tables
398             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
399             QualifiedTable[] tables = {t1};
400             //create a new leaf
401             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
402             //add fields
403             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
404             //create a filter to be added to the query leaf
405             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
406             BasicOperand valueFrom = new BasicOperand(12);
407             BasicOperand valueTo = new BasicOperand(124);
408             BasicOperand valueComp = new BasicOperand("toto");
409             Expression exp = new Substring(fieldOp1, valueFrom, valueTo);
410             Expression filter = new Equal(exp, valueComp);
411             //assign the filter to the query leaf
412             ql.setQueryFilter(filter);
413             ql.setRdbAdapter(new OracleAdapter());
414             logger.log(BasicLevel.DEBUG, "testSubstring");
415             logger.log(BasicLevel.DEBUG,
416                     ql.getSqlRequest(null, false, false));
417             assertEquals("Bad sql request",
418                 "SELECT t1.col1 FROM t1 WHERE ({fn substring(t1.col1, 12, 124)} = 'toto')",
419                 ql.getSqlRequest(null, false, false));
420         } catch (Exception   e) {
421             fail(e.getMessage());
422         }
423     }
424 
425         public void testLocate() {
426             try {
427                 //create qualified tables
428                 QualifiedTable t1 = new BasicQualifiedTable("t1", null);
429                 QualifiedTable[] tables = {t1};
430                 //create a new leaf
431                 RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
432                 //add fields
433                 RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
434                 //create a filter to be added to the query leaf
435                 FieldOperand fieldOp1 = new BasicFieldOperand(f1);
436                 BasicOperand valueComp = new BasicOperand("toto");
437                 Expression exp = new FirstLocate(valueComp, fieldOp1);
438                 Expression filter = new And(
439                         new Equal(exp, new Abs(fieldOp1)),
440                         new Equal(new Mod(fieldOp1, new BasicOperand(2)), new Sqrt(fieldOp1))
441                                 );
442                 //assign the filter to the query leaf
443                 ql.setQueryFilter(filter);
444                 ql.setRdbAdapter(new OracleAdapter());
445                 logger.log(BasicLevel.DEBUG, "testSubstring");
446                 logger.log(BasicLevel.DEBUG,
447                         ql.getSqlRequest(null, false, false));
448                 assertEquals("Bad sql request",
449                     "SELECT t1.col1 FROM t1 WHERE ((instr(t1.col1, 'toto') = {fn abs(t1.col1)}) AND ({fn mod(t1.col1, 2)} = {fn sqrt(t1.col1)}))",
450                     ql.getSqlRequest(null, false, false));
451             } catch (Exception   e) {
452                 fail(e.getMessage());
453             }
454         }
455 
456         public void testConcat() {
457         try {
458             //create qualified tables
459             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
460             QualifiedTable[] tables = {t1};
461             //create a new leaf
462             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
463             //add fields
464             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
465             //create a filter to be added to the query leaf
466             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
467             BasicOperand valueTo = new BasicOperand("to");
468             BasicOperand valueComp = new BasicOperand("toto");
469             Expression exp = new Concat(fieldOp1, valueTo);
470             Expression filter = new Equal(exp, valueComp);
471             //assign the filter to the query leaf
472             ql.setRdbAdapter(new BasicRdbAdapter());
473             ql.setQueryFilter(filter);
474             logger.log(BasicLevel.DEBUG, "testSubstring");
475             //compute the SQL query
476             logger.log(BasicLevel.DEBUG,
477                     ql.getSqlRequest(null, false, false));
478             assertEquals("Bad sql request",
479                 "SELECT t1.col1 FROM t1 WHERE ({fn concat(t1.col1, 'to')} = 'toto')",
480                 ql.getSqlRequest(null, false, false));
481         } catch (Exception   e) {
482             fail(e.getMessage());
483         }
484     }
485 
486     public void testConcatConcat() {
487         try {
488             //create qualified tables
489             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
490             QualifiedTable[] tables = {t1};
491             //create a new leaf
492             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
493             //add fields
494             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
495             RdbExpField f2 = ql.addRdbField("f2", null, "col2", t1);
496             //create a filter to be added to the query leaf
497             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
498             FieldOperand fieldOp2 = new BasicFieldOperand(f2);
499             BasicOperand valueTo = new BasicOperand("to");
500             BasicOperand valueComp = new BasicOperand("toto");
501             Expression exp1 = new Concat(fieldOp1, valueTo);
502             Expression exp = new Concat(exp1, fieldOp2);
503             Expression filter = new Equal(exp, valueComp);
504             //assign the filter to the query leaf
505             ql.setQueryFilter(filter);
506             logger.log(BasicLevel.DEBUG, "testSubstring");
507             logger.log(BasicLevel.DEBUG,
508                     ql.getSqlRequest(null, false, false));
509             assertEquals("Bad sql request",
510                 "SELECT t1.col1, t1.col2 FROM t1 WHERE (concat(concat(t1.col1, 'to'), t1.col2) = 'toto')",
511                 ql.getSqlRequest(null,false, false));
512         } catch (Exception   e) {
513             fail(e.getMessage());
514         }
515     }
516 
517     public void testEmptyCollection() {
518         testEmptyCollection(false);
519     }
520     public void testParamEmptyCollection() {
521         testEmptyCollection(true);
522     }
523     public void testEmptyCollection(boolean isParameter) {
524         try {
525             //create qualified tables
526             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
527             QualifiedTable[] tables = {t1};
528             //create a new leaf
529             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
530             //add fields
531             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
532             //create a filter to be added to the query leaf
533             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
534             Collection col = new ArrayList  ();
535             Operand colOp = null;
536             if (isParameter)
537                 colOp = new BasicParameterOperand(PTypeSpaceMedor.COLLECTION, "param");
538             else
539                 colOp = new BasicOperand(col, PTypeSpaceMedor.COLLECTION);
540             Expression filter = new InCollection(fieldOp1, colOp, PTypeSpace.STRING);
541             ;
542             //assign the filter to the query leaf
543             ql.setQueryFilter(filter);
544             //computes the SQL query
545             String   sqlrequest = null;
546             logger.log(BasicLevel.DEBUG, "testEmptyCollection");
547             if (isParameter) {
548                 ParameterOperand po = new BasicParameterOperand(
549                     PTypeSpaceMedor.COLLECTION, "param", col);
550                 sqlrequest = ql.getSqlRequest(new ParameterOperand[] {po},
551                         false, false);
552             } else
553                 sqlrequest = ql.getSqlRequest(null, false, false);
554             fail("Empty collection not detected");
555         } catch (Exception   e) {
556             assertEquals("detected", "Empty collection for InCollection operator", e.getMessage());
557             logger.log(BasicLevel.DEBUG, "Empty collection detected");
558         }
559     }
560 
561     public void testCollOfStrings() {
562         testCollOfStrings(false);
563     }
564     public void testParamCollOfStrings() {
565         testCollOfStrings(true);
566     }
567     public void testCollOfStrings(boolean isParameter) {
568         try {
569             //create qualified tables
570             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
571             QualifiedTable[] tables = {t1};
572             //create a new leaf
573             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
574             //add fields
575             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
576             //create a filter to be added to the query leaf
577             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
578             Collection col = new ArrayList  ();
579             col.add("value1");
580             col.add("val2");
581             Operand colOp = null;
582             if (isParameter)
583                 colOp = new BasicParameterOperand(PTypeSpaceMedor.COLLECTION, "param");
584             else
585                 colOp = new BasicOperand(col, PTypeSpaceMedor.COLLECTION);
586             Expression filter = new InCollection(fieldOp1, colOp, PTypeSpace.STRING);
587             //assign the filter to the query leaf
588             ql.setQueryFilter(filter);
589             logger.log(BasicLevel.DEBUG, "Collection of Strings");
590             //compute the SQL query
591             String   sqlrequest = null;
592             if (isParameter) {
593                 ParameterOperand po = new BasicParameterOperand(
594                     PTypeSpaceMedor.COLLECTION, "param", col);
595                 sqlrequest = ql.getSqlRequest(new ParameterOperand[] {po},
596                         false, false);
597             } else
598                 sqlrequest = ql.getSqlRequest(null, false, false);
599             logger.log(BasicLevel.DEBUG, sqlrequest);
600             assertEquals("Bad sql request", sqlrequest,
601                 "SELECT t1.col1 FROM t1 WHERE (t1.col1 IN ('value1', 'val2'))");
602         } catch (Exception   e) {
603             fail(e.getMessage());
604         }
605     }
606 
607     public void testCollOfFloats() {
608         testCollOfFloats(false);
609     }
610     public void testParamCollOfFloats() {
611         testCollOfFloats(true);
612     }
613     public void testCollOfFloats(boolean isParameter) {
614         try {
615             //create qualified tables
616             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
617             QualifiedTable[] tables = {t1};
618             //create a new leaf
619             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
620             //add fields
621             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
622             //create a filter to be added to the query leaf
623             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
624             Collection col = new ArrayList  ();
625             col.add(new Float  (123.34));
626             col.add(new Float  (345.23));
627             col.add(new BasicParameterOperand(PTypeSpaceMedor.FLOAT, "p1"));
628             Operand colOp = null;
629             if (isParameter)
630                 colOp = new BasicParameterOperand(PTypeSpaceMedor.COLLECTION, "param");
631             else
632                 colOp = new BasicOperand(col, PTypeSpaceMedor.COLLECTION);
633             Expression filter = new InCollection(fieldOp1, colOp, PTypeSpace.FLOAT);
634             //assign the filter to the query leaf
635             ql.setQueryFilter(filter);
636             logger.log(BasicLevel.DEBUG, "Collection of floats");
637             //compute the SQL query
638             String   sqlrequest = null;
639             if (isParameter) {
640                 ParameterOperand po = new BasicParameterOperand(
641                     PTypeSpaceMedor.COLLECTION, "param", col);
642                 sqlrequest = ql.getSqlRequest(new ParameterOperand[] {po},
643                         false, false);
644             } else
645                 sqlrequest = ql.getSqlRequest(null, false, false);
646             logger.log(BasicLevel.DEBUG, sqlrequest);
647             assertEquals("Bad sql request",
648                 "SELECT t1.col1 FROM t1 WHERE (t1.col1 IN (123.34, 345.23, ?))",
649                     sqlrequest);
650             //testing evaluation
651             /*
652             IndexesGenerator ig = new IndexesGenerator();
653             QueryTree qoptim = ig.transform(ql);
654             new MedorEvaluator(qoptim, 0);
655             */
656 
657         } catch (Exception   e) {
658             fail(e.getMessage());
659         }
660     }
661 
662         public void testIsNullPName() {
663         try {
664             //create qualified tables
665             QualifiedTable t1 = new BasicQualifiedTable("t1", null);
666             QualifiedTable[] tables = {t1};
667             //create a new leaf
668             RdbExpQueryLeaf ql = new BasicRdbExpQueryLeaf(null, tables, "");
669             //add fields
670             RdbExpField f1 = ql.addRdbField("f1", null, "col1", t1);
671             //create a filter to be added to the query leaf
672             FieldOperand fieldOp1 = new BasicFieldOperand(f1);
673             BasicOperand valueL = new BasicOperand(1013);
674             Expression left = new Lower(fieldOp1, valueL);
675             Expression filter = new And(left, new IsNullPName("param1"));
676             //assign the filter to the query leaf
677             ql.setQueryFilter(filter);
678             ql.setRdbAdapter(new PostgresAdapter());
679             logger.log(BasicLevel.DEBUG, "testIsNullPName: Postgres");
680             //compute the SQL query
681             logger.log(BasicLevel.DEBUG,
682                     ql.getSqlRequest(null, false, false));
683             assertEquals("Bad sql request",
684                 "SELECT t1.col1 FROM t1 WHERE ((t1.col1 < 1013) AND (? = TRUE))",
685                 ql.getSqlRequest(null, false, false));
686             ql.setRdbAdapter(new OracleAdapter());
687             ql.resetSqlRequest();
688             logger.log(BasicLevel.DEBUG, "testIsNullPName: Oracle");
689             //compute the SQL query
690             logger.log(BasicLevel.DEBUG,
691                     ql.getSqlRequest(null, false, false));
692             assertEquals("Bad sql request",
693                 "SELECT t1.col1 FROM t1 WHERE ((t1.col1 < 1013) AND (? = 1))",
694                 ql.getSqlRequest(null, false, false));
695         } catch (Exception   e) {
696             e.printStackTrace();
697             fail(e.getMessage());
698         }
699     }
700 }
701
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags