KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > objectweb > medor > query > rdb > lib > 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 JavaDoc;
65 import java.util.Collection JavaDoc;
66
67 public class TestRewrite2SQL extends TestMedorHelper {
68
69     /**
70      * main method to launch the tests manually
71      */

72     public static void main(String JavaDoc[] 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc al1 = new ArrayList JavaDoc();
255             ArrayList JavaDoc al2 = new ArrayList JavaDoc();
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 JavaDoc 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 JavaDoc al1 = new ArrayList JavaDoc();
296             ArrayList JavaDoc al2 = new ArrayList JavaDoc();
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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc();
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 JavaDoc 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 JavaDoc 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 JavaDoc();
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 JavaDoc 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 JavaDoc 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 JavaDoc();
625             col.add(new Float JavaDoc(123.34));
626             col.add(new Float JavaDoc(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 JavaDoc 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 JavaDoc 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 JavaDoc e) {
696             e.printStackTrace();
697             fail(e.getMessage());
698         }
699     }
700 }
701
Popular Tags