Java > Open Source Codes > org > mmbase > storage > search > implementation > database > BasicSqlHandlerTest


1 package org.mmbase.storage.search.implementation.database;
2
3 import junit.framework.*;
4
5 import org.mmbase.module.core.*;
6 import org.mmbase.module.corebuilders.*;
7 import org.mmbase.storage.search.*;
8 import org.mmbase.storage.search.implementation.*;
9 import org.mmbase.bridge.Field;
10 import java.util.*;
11
12 /**
13  * JUnit tests.
14  *
15  * @author Rob van Maris
16  * @version $Revision: 1.12 $
17  */
18 public class BasicSqlHandlerTest extends TestCase {
19
20     /** Test instance. */
21     private BasicSqlHandler instance;
22
23     /** Disallowed values map. */
24     private Map disallowedValues = null;
25
26     /** Prefix applied to buildernames to create tablenames. */
27     private String prefix = null;
28
29     /** MMBase query. */
30     private MMBase mmbase = null;
31
32     /** Images builder, used as builder example. */
33     private MMObjectBuilder images = null;
34
35     /** News builder, used as builder example. */
36     private MMObjectBuilder news = null;
37
38     /** Insrel builder, used as relation builder example. */
39     private InsRel insrel = null;
40
41     public BasicSqlHandlerTest(java.lang.String testName) {
42         super(testName);
43     }
44
45     public static void main(java.lang.String [] args) {
46         junit.textui.TestRunner.run(suite());
47         System.exit(0);
48     }
49
50     /**
51      * Sets up before each test.
52      */
53     public void setUp() throws Exception {
54         MMBaseContext.init();
55         mmbase = MMBase.getMMBase();
56         images = mmbase.getBuilder("images");
57         insrel = mmbase.getInsRel();
58         news = mmbase.getBuilder("news");
59
60         // Disallowed fields map.
61 disallowedValues = new HashMap();
62         disallowedValues.put("table", "m_table");
63         disallowedValues.put("TABLE", "m_table");
64         instance = new BasicSqlHandler();
65
66         prefix = mmbase.getBaseName() + "_";
67     }
68
69     /**
70      * Tears down after each test.
71      */
72     public void tearDown() throws Exception {}
73
74     /** Test of init method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
75     public void testInit() throws Exception {
76         // Same as:
77 testToSqlString();
78         testToSql();
79     }
80
81     /** Test of toSqlString method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
82     public void testToSqlString() throws Exception {
83         assertTrue(BasicSqlHandler.toSqlString(null) == null);
84         assertTrue(BasicSqlHandler.toSqlString("'").equals("''"));
85         assertTrue(BasicSqlHandler.toSqlString("'''''").equals("''''''''''"));
86         assertTrue(BasicSqlHandler.toSqlString("AsDf'").equals("AsDf''"));
87         assertTrue(BasicSqlHandler.toSqlString("AsDf'jkl").equals("AsDf''jkl"));
88         assertTrue(BasicSqlHandler.toSqlString("AsDf'jkl'").equals("AsDf''jkl''"));
89         assertTrue(BasicSqlHandler.toSqlString("'AsDf'jkl").equals("''AsDf''jkl"));
90         assertTrue(BasicSqlHandler.toSqlString("qwerty").equals("qwerty"));
91     }
92
93     /** Test of toSql method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
94     public void testToSql() throws Exception {
95         BasicSearchQuery query = new BasicSearchQuery();
96
97        // Query without step, should throw IllegalStateException.
98 try {
99             instance.toSql(query, instance);
100             fail("Query without step, should throw IllegalStateException.");
101         } catch (IllegalStateException e) {};
102
103         BasicStep step1 = query.addStep(images).setAlias(null);
104
105         // Query without field, should throw IllegalStateException.
106 try {
107             instance.toSql(query, instance);
108             fail("Query without field, should throw IllegalStateException.");
109         } catch (IllegalStateException e) {};
110
111         FieldDefs imagesTitle = images.getField("title");
112         FieldDefs insrelRNumber = insrel.getField("rnumber");
113         FieldDefs newsTitle = news.getField("title");
114
115         // Query with one step (default alias) and one field (default alias).
116 BasicStepField field1a
117             = query.addField(step1, imagesTitle).setAlias(null);
118         String strSql = instance.toSql(query, instance);
119         assertTrue(strSql, strSql.equalsIgnoreCase("SELECT TITLE FROM " + prefix + "images IMAGES"));
120
121         // Set step alias.
122 step1.setAlias("i");
123         strSql = instance.toSql(query, instance);
124         assertTrue(strSql, strSql.equalsIgnoreCase("SELECT TITLE FROM " + prefix + "images I"));
125
126         // Set field alias.
127 field1a.setAlias("imageTitle");
128         strSql = instance.toSql(query, instance);
129         assertTrue(strSql, strSql.equalsIgnoreCase("SELECT TITLE AS IMAGETITLE FROM " + prefix + "images I"));
130
131         // Add second field (null alias).
132 FieldDefs imagesNumber = images.getField("number");
133         BasicStepField field1b
134             = query.addField(step1, imagesNumber).setAlias(null);
135         strSql = instance.toSql(query, instance);
136         assertTrue(strSql, strSql.equalsIgnoreCase("SELECT TITLE AS IMAGETITLE,NUMBER FROM " + prefix + "images I"));
137
138         // Set alias for second field.
139 field1b.setAlias("imageNumber");
140         strSql = instance.toSql(query, instance);
141         assertTrue(strSql, strSql.equalsIgnoreCase("SELECT TITLE AS IMAGETITLE,NUMBER AS IMAGENUMBER FROM " + prefix + "images I"));
142
143         // Set distinct true.
144 query.setDistinct(true);
145         strSql = instance.toSql(query, instance);
146         assertTrue(strSql, strSql.equalsIgnoreCase(
147         "SELECT DISTINCT TITLE AS IMAGETITLE,"
148         + "NUMBER AS IMAGENUMBER "
149         + "FROM " + prefix + "images I"));
150
151         // Add sortorder (default direction).
152 BasicSortOrder sortOrder1a = query.addSortOrder(field1a);
153         strSql = instance.toSql(query, instance);
154         assertTrue(strSql, strSql.equalsIgnoreCase(
155         "SELECT DISTINCT TITLE AS IMAGETITLE,"
156         + "NUMBER AS IMAGENUMBER "
157         + "FROM " + prefix + "images I "
158         + "ORDER BY TITLE ASC"));
159
160         // Set constraint.
161 Constraint constraint1 = new BasicFieldValueConstraint(field1a, "abd");
162         query.setConstraint(constraint1);
163         strSql = instance.toSql(query, instance);
164         assertTrue(strSql, strSql.equalsIgnoreCase(
165         "SELECT DISTINCT TITLE AS IMAGETITLE,"
166         + "NUMBER AS IMAGENUMBER "
167         + "FROM " + prefix + "images I "
168         + "WHERE TITLE='abd' "
169         + "ORDER BY TITLE ASC"));
170
171         // Set composite constraint.
172 Constraint constraint2
173         = new BasicFieldValueConstraint(field1b, new Integer (123));
174         BasicCompositeConstraint constraint3
175             = new BasicCompositeConstraint(CompositeConstraint.LOGICAL_AND)
176                 .addChild(constraint1)
177                 .addChild(constraint2);
178         query.setConstraint(constraint3);
179         strSql = instance.toSql(query, instance);
180         assertTrue(strSql, strSql.equalsIgnoreCase(
181         "SELECT DISTINCT TITLE AS IMAGETITLE,"
182         + "NUMBER AS IMAGENUMBER "
183         + "FROM " + prefix + "images I "
184         + "WHERE TITLE='abd' AND NUMBER=123 "
185         + "ORDER BY TITLE ASC"));
186
187         // Set sortorder direction.
188 sortOrder1a.setDirection(SortOrder.ORDER_DESCENDING);
189         strSql = instance.toSql(query, instance);
190         assertTrue(strSql, strSql.equalsIgnoreCase(
191         "SELECT DISTINCT TITLE AS IMAGETITLE,"
192         + "NUMBER AS IMAGENUMBER "
193         + "FROM " + prefix + "images I "
194         + "WHERE TITLE='abd' AND NUMBER=123 "
195         + "ORDER BY TITLE DESC"));
196
197         // Set sortorder direction.
198 sortOrder1a.setDirection(SortOrder.ORDER_ASCENDING);
199         strSql = instance.toSql(query, instance);
200         assertTrue(strSql, strSql.equalsIgnoreCase(
201         "SELECT DISTINCT TITLE AS IMAGETITLE,"
202         + "NUMBER AS IMAGENUMBER "
203         + "FROM " + prefix + "images I "
204         + "WHERE TITLE='abd' AND NUMBER=123 "
205         + "ORDER BY TITLE ASC"));
206
207         // Set distinct false.
208 query.setDistinct(false);
209         strSql = instance.toSql(query, instance);
210         assertTrue(strSql, strSql.equalsIgnoreCase(
211         "SELECT TITLE AS IMAGETITLE,"
212         + "NUMBER AS IMAGENUMBER "
213         + "FROM " + prefix + "images I "
214         + "WHERE TITLE='abd' AND NUMBER=123 "
215         + "ORDER BY TITLE ASC"));
216
217         // Add node constraint for first step (one node).
218 step1.addNode(123);
219         strSql = instance.toSql(query, instance);
220         assertTrue(strSql, strSql.equalsIgnoreCase(
221         "SELECT TITLE AS IMAGETITLE,"
222         + "NUMBER AS IMAGENUMBER "
223         + "FROM " + prefix + "images I "
224         + "WHERE NUMBER=123 "
225         + "AND (TITLE='abd' AND NUMBER=123) "
226         + "ORDER BY TITLE ASC"));
227
228
229
230
231         // Add second node to node constraint.
232 step1.addNode(456);
233         strSql = instance.toSql(query, instance);
234         assertTrue(strSql, strSql.equalsIgnoreCase(
235         "SELECT TITLE AS IMAGETITLE,"
236         + "NUMBER AS IMAGENUMBER "
237         + "FROM " + prefix + "images I "
238         + "WHERE NUMBER IN (123,456) "
239         + "AND (TITLE='abd' AND NUMBER=123) "
240         + "ORDER BY TITLE ASC"));
241
242         // Add relationstep (default directionality).
243 BasicRelationStep step2 = query.addRelationStep(insrel,news);
244         BasicStep step3 = (BasicStep) step2.getNext();
245         strSql = instance.toSql(query, instance);
246         assertTrue(strSql, strSql.equalsIgnoreCase(
247         "SELECT I.TITLE AS IMAGETITLE,"
248         + "I.NUMBER AS IMAGENUMBER "
249         + "FROM " + prefix + "images I," + prefix + "insrel INSREL,"
250         + prefix + "news NEWS "
251         + "WHERE I.NUMBER IN (123,456) "
252         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
253         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
254         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
255         + "ORDER BY I.TITLE ASC"));
256
257         // Set aliases on step2 and stap3.
258 step2.setAlias("insrel");
259         step3.setAlias("news");
260
261         // Set role.
262 step2.setRole(new Integer (890));
263         strSql = instance.toSql(query, instance);
264         assertTrue(strSql, strSql.equalsIgnoreCase(
265         "SELECT I.TITLE AS IMAGETITLE,"
266         + "I.NUMBER AS IMAGENUMBER "
267         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
268         + "WHERE I.NUMBER IN (123,456) "
269         + "AND (((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
270         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
271         + "AND INSREL.rnumber=890) "
272         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
273         + "ORDER BY I.TITLE ASC"));
274
275         // Set directionality for relationstep to DESTINATION.
276 step2.setDirectionality(RelationStep.DIRECTIONS_DESTINATION);
277         strSql = instance.toSql(query, instance);
278         assertTrue(strSql, strSql.equalsIgnoreCase(
279         "SELECT I.TITLE AS IMAGETITLE,"
280         + "I.NUMBER AS IMAGENUMBER "
281         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
282         + "WHERE I.NUMBER IN (123,456) "
283         + "AND (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER "
284         + "AND INSREL.rnumber=890) "
285         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
286         + "ORDER BY I.TITLE ASC"));
287
288         // Set checkedDirectionality to true.
289 step2.setCheckedDirectionality(true);
290         strSql = instance.toSql(query, instance);
291         assertTrue(strSql, strSql.equalsIgnoreCase(
292         "SELECT I.TITLE AS IMAGETITLE,"
293         + "I.NUMBER AS IMAGENUMBER "
294         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
295         + "WHERE I.NUMBER IN (123,456) "
296         + "AND (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER "
297         + "AND INSREL.rnumber=890) "
298         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
299         + "ORDER BY I.TITLE ASC"));
300
301         // Set directionality for relationstep to SOURCE,
302 // set checkedDirectionality to false.
303 step2.setDirectionality(RelationStep.DIRECTIONS_SOURCE);
304         step2.setCheckedDirectionality(false);
305         strSql = instance.toSql(query, instance);
306         assertTrue(strSql, strSql.equalsIgnoreCase(
307         "SELECT I.TITLE AS IMAGETITLE,"
308         + "I.NUMBER AS IMAGENUMBER "
309         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
310         + "WHERE I.NUMBER IN (123,456) "
311         + "AND (I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER "
312         + "AND INSREL.rnumber=890) "
313         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
314         + "ORDER BY I.TITLE ASC"));
315
316         // Set checkedDirectionality to true.
317 step2.setCheckedDirectionality(true);
318         strSql = instance.toSql(query, instance);
319         assertTrue(strSql, strSql.equalsIgnoreCase(
320         "SELECT I.TITLE AS IMAGETITLE,"
321         + "I.NUMBER AS IMAGENUMBER "
322         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
323         + "WHERE I.NUMBER IN (123,456) "
324         + "AND (I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER "
325         + "AND INSREL.dir<>1 "
326         + "AND INSREL.rnumber=890) "
327         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
328         + "ORDER BY I.TITLE ASC"));
329
330         // Set directionality for relationstep to BOTH,
331 // set checkedDirectionality to false.
332 step2.setDirectionality(RelationStep.DIRECTIONS_BOTH);
333         step2.setCheckedDirectionality(false);
334         strSql = instance.toSql(query, instance);
335         assertTrue(strSql, strSql.equalsIgnoreCase(
336         "SELECT I.TITLE AS IMAGETITLE,"
337         + "I.NUMBER AS IMAGENUMBER "
338         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
339         + "WHERE I.NUMBER IN (123,456) "
340         + "AND (((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
341         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
342         + "AND INSREL.rnumber=890) "
343         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
344         + "ORDER BY I.TITLE ASC"));
345
346         // Set checkedDirectionality to true.
347 step2.setCheckedDirectionality(true);
348         strSql = instance.toSql(query, instance);
349         assertTrue(strSql, strSql.equalsIgnoreCase(
350         "SELECT I.TITLE AS IMAGETITLE,"
351         + "I.NUMBER AS IMAGENUMBER "
352         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
353         + "WHERE I.NUMBER IN (123,456) "
354         + "AND (((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER "
355         + "AND INSREL.dir<>1) "
356         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
357         + "AND INSREL.rnumber=890) "
358         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
359         + "ORDER BY I.TITLE ASC"));
360
361         // Reset role and checkedDirectionality.
362 step2.setRole(null);
363         step2.setCheckedDirectionality(false);
364         strSql = instance.toSql(query, instance);
365         assertTrue(strSql, strSql.equalsIgnoreCase(
366         "SELECT I.TITLE AS IMAGETITLE,"
367         + "I.NUMBER AS IMAGENUMBER "
368         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
369         + "WHERE I.NUMBER IN (123,456) "
370         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
371         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
372         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
373         + "ORDER BY I.TITLE ASC"));
374
375         // Add field for relationstep.
376 StepField field2a = query.addField(step2, insrelRNumber).setAlias(null);
377         strSql = instance.toSql(query, instance);
378         assertTrue(strSql, strSql.equalsIgnoreCase(
379         "SELECT I.TITLE AS IMAGETITLE,"
380         + "I.NUMBER AS IMAGENUMBER,"
381         + "INSREL.rnumber "
382         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
383         + "WHERE I.NUMBER IN (123,456) "
384         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
385         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
386         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
387         + "ORDER BY I.TITLE ASC"));
388
389         // Add field for third step.
390 StepField field3a = query.addField(step3, newsTitle).setAlias(null);
391         strSql = instance.toSql(query, instance);
392         assertTrue(strSql, strSql.equalsIgnoreCase(
393         "SELECT I.TITLE AS IMAGETITLE,"
394         + "I.NUMBER AS IMAGENUMBER,"
395         + "INSREL.rnumber,"
396         + "NEWS.TITLE "
397         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
398         + "WHERE I.NUMBER IN (123,456) "
399         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
400         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
401         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
402         + "ORDER BY I.TITLE ASC"));
403
404         // Add second sortorder
405 query.addSortOrder(field3a);
406         strSql = instance.toSql(query, instance);
407         assertTrue(strSql, strSql.equalsIgnoreCase(
408         "SELECT I.TITLE AS IMAGETITLE,"
409         + "I.NUMBER AS IMAGENUMBER,"
410         + "INSREL.rnumber,"
411         + "NEWS.TITLE "
412         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
413         + "WHERE I.NUMBER IN (123,456) "
414         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
415         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
416         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
417         + "ORDER BY I.TITLE ASC,NEWS.TITLE ASC"));
418
419         // Add third sortorder.
420 query.addSortOrder(field2a);
421         strSql = instance.toSql(query, instance);
422         assertTrue(strSql, strSql.equalsIgnoreCase(
423         "SELECT I.TITLE AS IMAGETITLE,"
424         + "I.NUMBER AS IMAGENUMBER,"
425         + "INSREL.rnumber,"
426         + "NEWS.TITLE "
427         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
428         + "WHERE I.NUMBER IN (123,456) "
429         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
430         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
431         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
432         + "ORDER BY I.TITLE ASC,NEWS.TITLE ASC,INSREL.rnumber ASC"));
433
434         // Add node constraint for second step (relation step).
435 step2.addNode(789);
436         strSql = instance.toSql(query, instance);
437         assertTrue(strSql, strSql.equalsIgnoreCase(
438         "SELECT I.TITLE AS IMAGETITLE,"
439         + "I.NUMBER AS IMAGENUMBER,"
440         + "INSREL.rnumber,"
441         + "NEWS.TITLE "
442         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
443         + "WHERE I.NUMBER IN (123,456) AND INSREL.NUMBER=789 "
444         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
445         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
446         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
447         + "ORDER BY I.TITLE ASC,NEWS.TITLE ASC,INSREL.rnumber ASC"));
448
449         // Aggregated query.
450 query = new BasicSearchQuery(true);
451         step1 = query.addStep(images).setAlias(null);
452         query.addAggregatedField(
453                 step1, imagesTitle, AggregatedField.AGGREGATION_TYPE_COUNT)
454                     .setAlias(null);
455         strSql = instance.toSql(query, instance);
456         assertTrue(strSql, strSql.equalsIgnoreCase(
457         "SELECT COUNT(TITLE) "
458         + "FROM " + prefix + "images IMAGES"));
459
460         // Distinct keyword avoided in aggregating query.
461 query.setDistinct(true);
462         strSql = instance.toSql(query, instance);
463         assertTrue(strSql, strSql.equalsIgnoreCase(
464         "SELECT COUNT(TITLE) "
465         + "FROM " + prefix + "images IMAGES"));
466     }
467
468
469     /** Test of appendQueryBodyToSql method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
470     public void testAppendQueryBodyToSql() throws Exception {
471         BasicSearchQuery query = new BasicSearchQuery();
472
473         FieldDefs imagesTitle = images.getField("title");
474         FieldDefs insrelRNumber = insrel.getField("rnumber");
475         FieldDefs newsTitle = news.getField("title");
476         StringBuffer sb = new StringBuffer ();
477         BasicStep step1 = query.addStep(images).setAlias(null);
478
479         // Query with one step (null alias) and one field (null alias).
480 BasicStepField field1a
481             = query.addField(step1, imagesTitle).setAlias(null);
482         instance.appendQueryBodyToSql(sb, query, instance);
483         String strSql = sb.toString();
484         assertTrue(strSql, strSql.equalsIgnoreCase(
485         "TITLE "
486         + "FROM " + prefix + "images IMAGES"));
487
488         // Set step alias.
489 step1.setAlias("i");
490         sb.setLength(0);
491         instance.appendQueryBodyToSql(sb, query, instance);
492         strSql = sb.toString();
493         assertTrue(strSql, strSql.equalsIgnoreCase(
494         "TITLE "
495         + "FROM " + prefix + "images I"));
496
497         // Set field alias.
498 field1a.setAlias("imageTitle");
499         sb.setLength(0);
500         instance.appendQueryBodyToSql(sb, query, instance);
501         strSql = sb.toString();
502         assertTrue(strSql, strSql.equalsIgnoreCase(
503         "TITLE AS IMAGETITLE "
504         + "FROM " + prefix + "images I"));
505
506         // Add second field (default alias).
507 FieldDefs imagesNumber = images.getField("number");
508         BasicStepField field1b
509             = query.addField(step1, imagesNumber).setAlias(null);
510         sb.setLength(0);
511         instance.appendQueryBodyToSql(sb, query, instance);
512         strSql = sb.toString();
513         assertTrue(strSql, strSql.equalsIgnoreCase(
514         "TITLE AS IMAGETITLE,"
515         + "NUMBER "
516         + "FROM " + prefix + "images I"));
517
518         // Set alias for second field.
519 field1b.setAlias("IMAGENUMBER");
520         sb.setLength(0);
521         instance.appendQueryBodyToSql(sb, query, instance);
522         strSql = sb.toString();
523         assertTrue(strSql, strSql.equalsIgnoreCase(
524         "TITLE AS IMAGETITLE,"
525         + "NUMBER AS IMAGENUMBER "
526         + "FROM " + prefix + "images I"));
527
528         // Set distinct true.
529 query.setDistinct(true);
530         sb.setLength(0);
531         instance.appendQueryBodyToSql(sb, query, instance);
532         strSql = sb.toString();
533         assertTrue(strSql, strSql.equalsIgnoreCase(
534         "TITLE AS IMAGETITLE,"
535         + "NUMBER AS IMAGENUMBER "
536         + "FROM " + prefix + "images I"));
537
538         // Add sortorder (default direction).
539 BasicSortOrder sortOrder1a = query.addSortOrder(field1a);
540         sb.setLength(0);
541         instance.appendQueryBodyToSql(sb, query, instance);
542         strSql = sb.toString();
543         assertTrue(strSql, strSql.equalsIgnoreCase(
544         "TITLE AS IMAGETITLE,"
545         + "NUMBER AS IMAGENUMBER "
546         + "FROM " + prefix + "images I "
547         + "ORDER BY TITLE ASC"));
548
549         // Set constraint.
550 Constraint constraint1 = new BasicFieldValueConstraint(field1a, "abd");
551         query.setConstraint(constraint1);
552         sb.setLength(0);
553         instance.appendQueryBodyToSql(sb, query, instance);
554         strSql = sb.toString();
555         assertTrue(strSql, strSql.equalsIgnoreCase(
556         "TITLE AS IMAGETITLE,"
557         + "NUMBER AS IMAGENUMBER "
558         + "FROM " + prefix + "images I "
559         + "WHERE TITLE='abd' "
560         + "ORDER BY TITLE ASC"));
561
562         // Set composite constraint.
563 Constraint constraint2
564         = new BasicFieldValueConstraint(field1b, new Integer (123));
565         BasicCompositeConstraint constraint3
566             = new BasicCompositeConstraint(CompositeConstraint.LOGICAL_AND)
567                 .addChild(constraint1)
568                 .addChild(constraint2);
569         query.setConstraint(constraint3);
570         sb.setLength(0);
571         instance.appendQueryBodyToSql(sb, query, instance);
572         strSql = sb.toString();
573         assertTrue(strSql, strSql.equalsIgnoreCase(
574         "TITLE AS IMAGETITLE,"
575         + "NUMBER AS IMAGENUMBER "
576         + "FROM " + prefix + "images I "
577         + "WHERE TITLE='abd' AND NUMBER=123 "
578         + "ORDER BY TITLE ASC"));
579
580         // Set sortorder direction.
581 sortOrder1a.setDirection(SortOrder.ORDER_DESCENDING);
582         sb.setLength(0);
583         instance.appendQueryBodyToSql(sb, query, instance);
584         strSql = sb.toString();
585         assertTrue(strSql, strSql.equalsIgnoreCase(
586         "TITLE AS IMAGETITLE,"
587         + "NUMBER AS IMAGENUMBER "
588         + "FROM " + prefix + "images I "
589         + "WHERE TITLE='abd' AND NUMBER=123 "
590         + "ORDER BY TITLE DESC"));
591
592         // Set sortorder direction.
593 sortOrder1a.setDirection(SortOrder.ORDER_ASCENDING);
594         sb.setLength(0);
595         instance.appendQueryBodyToSql(sb, query, instance);
596         strSql = sb.toString();
597         assertTrue(strSql, strSql.equalsIgnoreCase(
598         "TITLE AS IMAGETITLE,"
599         + "NUMBER AS IMAGENUMBER "
600         + "FROM " + prefix + "images I "
601         + "WHERE TITLE='abd' AND NUMBER=123 "
602         + "ORDER BY TITLE ASC"));
603
604         // Set distinct false.
605 query.setDistinct(false);
606         sb.setLength(0);
607         instance.appendQueryBodyToSql(sb, query, instance);
608         strSql = sb.toString();
609         assertTrue(strSql, strSql.equalsIgnoreCase(
610         "TITLE AS IMAGETITLE,"
611         + "NUMBER AS IMAGENUMBER "
612         + "FROM " + prefix + "images I "
613         + "WHERE TITLE='abd' AND NUMBER=123 "
614         + "ORDER BY TITLE ASC"));
615
616         // Add node constraint for first step (one node).
617 step1.addNode(123);
618         sb.setLength(0);
619         instance.appendQueryBodyToSql(sb, query, instance);
620         strSql = sb.toString();
621         assertTrue(strSql, strSql.equalsIgnoreCase(
622         "TITLE AS IMAGETITLE,"
623         + "NUMBER AS IMAGENUMBER "
624         + "FROM " + prefix + "images I "
625         + "WHERE NUMBER=123 "
626         + "AND (TITLE='abd' AND NUMBER=123) "
627         + "ORDER BY TITLE ASC"));
628
629         // Add second node to node constraint.
630 step1.addNode(456);
631         sb.setLength(0);
632         instance.appendQueryBodyToSql(sb, query, instance);
633         strSql = sb.toString();
634         assertTrue(strSql, strSql.equalsIgnoreCase(
635         "TITLE AS IMAGETITLE,"
636         + "NUMBER AS IMAGENUMBER "
637         + "FROM " + prefix + "images I "
638         + "WHERE NUMBER IN (123,456) "
639         + "AND (TITLE='abd' AND NUMBER=123) "
640         + "ORDER BY TITLE ASC"));
641
642         // Add relationstep (default directionality).
643 BasicRelationStep step2 = (BasicRelationStep)
644             query.addRelationStep(insrel, news).setAlias("insrel");
645         BasicStep step3 = (BasicStep) step2.getNext();
646         step3.setAlias("news");
647         sb.setLength(0);
648         instance.appendQueryBodyToSql(sb, query, instance);
649         strSql = sb.toString();
650         assertTrue(strSql, strSql.equalsIgnoreCase(
651         "I.TITLE AS IMAGETITLE,"
652         + "I.NUMBER AS IMAGENUMBER "
653         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
654         + "WHERE I.NUMBER IN (123,456) "
655         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
656         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
657         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
658         + "ORDER BY I.TITLE ASC"));
659
660         // Set directionality for relationstep to DESTINATION.
661 step2.setDirectionality(RelationStep.DIRECTIONS_DESTINATION);
662         sb.setLength(0);
663         instance.appendQueryBodyToSql(sb, query, instance);
664         strSql = sb.toString();
665         assertTrue(strSql, strSql.equalsIgnoreCase(
666         "I.TITLE AS IMAGETITLE,"
667         + "I.NUMBER AS IMAGENUMBER "
668         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
669         + "WHERE I.NUMBER IN (123,456) "
670         + "AND (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER) "
671         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
672         + "ORDER BY I.TITLE ASC"));
673
674         // Set directionality for relationstep to SOURCE.
675 step2.setDirectionality(RelationStep.DIRECTIONS_SOURCE);
676         sb.setLength(0);
677         instance.appendQueryBodyToSql(sb, query, instance);
678         strSql = sb.toString();
679         assertTrue(strSql, strSql.equalsIgnoreCase(
680         "I.TITLE AS IMAGETITLE,"
681         + "I.NUMBER AS IMAGENUMBER "
682         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
683         + "WHERE I.NUMBER IN (123,456) "
684         + "AND (I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
685         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
686         + "ORDER BY I.TITLE ASC"));
687
688         // Set directionality for relationstep to BOTH.
689 step2.setDirectionality(RelationStep.DIRECTIONS_BOTH);
690         sb.setLength(0);
691         instance.appendQueryBodyToSql(sb, query, instance);
692         strSql = sb.toString();
693         assertTrue(strSql, strSql.equalsIgnoreCase(
694         "I.TITLE AS IMAGETITLE,"
695         + "I.NUMBER AS IMAGENUMBER "
696         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
697         + "WHERE I.NUMBER IN (123,456) "
698         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
699         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
700         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
701         + "ORDER BY I.TITLE ASC"));
702
703         // Add field for relationstep.
704 StepField field2a = query.addField(step2, insrelRNumber).setAlias(null);
705         sb.setLength(0);
706         instance.appendQueryBodyToSql(sb, query, instance);
707         strSql = sb.toString();
708         assertTrue(strSql, strSql.equalsIgnoreCase(
709         "I.TITLE AS IMAGETITLE,"
710         + "I.NUMBER AS IMAGENUMBER,"
711         + "INSREL.rnumber "
712         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
713         + "WHERE I.NUMBER IN (123,456) "
714         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
715         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
716         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
717         + "ORDER BY I.TITLE ASC"));
718
719         // Add field for third step.
720 StepField field3a = query.addField(step3, newsTitle).setAlias(null);
721         sb.setLength(0);
722         instance.appendQueryBodyToSql(sb, query, instance);
723         strSql = sb.toString();
724         assertTrue(strSql, strSql.equalsIgnoreCase(
725         "I.TITLE AS IMAGETITLE,"
726         + "I.NUMBER AS IMAGENUMBER,"
727         + "INSREL.rnumber,"
728         + "NEWS.TITLE "
729         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
730         + "WHERE I.NUMBER IN (123,456) "
731         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
732         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
733         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
734         + "ORDER BY I.TITLE ASC"));
735
736         // Add second sortorder
737 query.addSortOrder(field3a);
738         sb.setLength(0);
739         instance.appendQueryBodyToSql(sb, query, instance);
740         strSql = sb.toString();
741         assertTrue(strSql, strSql.equalsIgnoreCase(
742         "I.TITLE AS IMAGETITLE,"
743         + "I.NUMBER AS IMAGENUMBER,"
744         + "INSREL.rnumber,"
745         + "NEWS.TITLE "
746         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
747         + "WHERE I.NUMBER IN (123,456) "
748         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
749         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
750         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
751         + "ORDER BY I.TITLE ASC,NEWS.TITLE ASC"));
752
753         // Add third sortorder.
754 query.addSortOrder(field2a);
755         sb.setLength(0);
756         instance.appendQueryBodyToSql(sb, query, instance);
757         strSql = sb.toString();
758         assertTrue(strSql, strSql.equalsIgnoreCase(
759         "I.TITLE AS IMAGETITLE,"
760         + "I.NUMBER AS IMAGENUMBER,"
761         + "INSREL.rnumber,"
762         + "NEWS.TITLE "
763         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
764         + "WHERE I.NUMBER IN (123,456) "
765         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
766         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
767         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
768         + "ORDER BY I.TITLE ASC,NEWS.TITLE ASC,INSREL.rnumber ASC"));
769
770         // Add node constraint for second step (relation step).
771 step2.addNode(789);
772         sb.setLength(0);
773         instance.appendQueryBodyToSql(sb, query, instance);
774         strSql = sb.toString();
775         assertTrue(strSql, strSql.equalsIgnoreCase(
776         "I.TITLE AS IMAGETITLE,"
777         + "I.NUMBER AS IMAGENUMBER,"
778         + "INSREL.rnumber,"
779         + "NEWS.TITLE "
780         + "FROM " + prefix + "images I," + prefix + "insrel INSREL," + prefix + "news NEWS "
781         + "WHERE I.NUMBER IN (123,456) AND INSREL.NUMBER=789 "
782         + "AND ((I.NUMBER=INSREL.DNUMBER AND NEWS.NUMBER=INSREL.SNUMBER) "
783         + "OR (I.NUMBER=INSREL.SNUMBER AND NEWS.NUMBER=INSREL.DNUMBER)) "
784         + "AND (I.TITLE='abd' AND I.NUMBER=123) "
785         + "ORDER BY I.TITLE ASC,NEWS.TITLE ASC,INSREL.rnumber ASC"));
786
787         // Aggregated query.
788 query = new BasicSearchQuery(true);
789         step1 = query.addStep(images).setAlias(null);
790         BasicAggregatedField field4a
791             = (BasicAggregatedField) query.addAggregatedField(
792                 step1, imagesTitle, AggregatedField.AGGREGATION_TYPE_COUNT)
793                     .setAlias(null);
794         sb.setLength(0);
795         instance.appendQueryBodyToSql(sb, query, instance);
796         strSql = sb.toString();
797         assertTrue(strSql, strSql.equalsIgnoreCase(
798         "COUNT(TITLE) " + "FROM " + prefix + "images IMAGES"));
799
800         field4a.setAggregationType(
801         AggregatedField.AGGREGATION_TYPE_COUNT_DISTINCT);
802         sb.setLength(0);
803         instance.appendQueryBodyToSql(sb, query, instance);
804         strSql = sb.toString();
805         assertTrue(strSql, strSql.equalsIgnoreCase(
806         "COUNT(DISTINCT TITLE) "
807         + "FROM " + prefix + "images IMAGES"));
808
809         field4a.setAggregationType(AggregatedField.AGGREGATION_TYPE_MIN);
810         sb.setLength(0);
811         instance.appendQueryBodyToSql(sb, query, instance);
812         strSql = sb.toString();
813         assertTrue(strSql, strSql.equalsIgnoreCase(
814         "MIN(TITLE) "
815         + "FROM " + prefix + "images IMAGES"));
816
817         field4a.setAggregationType(AggregatedField.AGGREGATION_TYPE_MAX);
818         sb.setLength(0);
819         instance.appendQueryBodyToSql(sb, query, instance);
820         strSql = sb.toString();
821         assertTrue(strSql, strSql.equalsIgnoreCase(
822         "MAX(TITLE) "
823         + "FROM " + prefix + "images IMAGES"));
824
825         field4a.setAlias("maxTitle");
826         sb.setLength(0);
827         instance.appendQueryBodyToSql(sb, query, instance);
828         strSql = sb.toString();
829         assertTrue(strSql, strSql.equalsIgnoreCase(
830         "MAX(TITLE) AS maxTitle "
831         + "FROM " + prefix + "images IMAGES"));
832
833         BasicAggregatedField field4b
834             = (BasicAggregatedField) query.addAggregatedField(
835                 step1, imagesNumber, AggregatedField.AGGREGATION_TYPE_COUNT)
836                     .setAlias(null);
837         sb.setLength(0);
838         instance.appendQueryBodyToSql(sb, query, instance);
839         strSql = sb.toString();
840         assertTrue(strSql, strSql.equalsIgnoreCase(
841         "MAX(TITLE) AS maxTitle,"
842         + "COUNT(NUMBER) "
843         + "FROM " + prefix + "images IMAGES"));
844
845         field4b.setAggregationType(AggregatedField.AGGREGATION_TYPE_GROUP_BY);
846         sb.setLength(0);
847         instance.appendQueryBodyToSql(sb, query, instance);
848         strSql = sb.toString();
849         assertTrue(strSql, strSql.equalsIgnoreCase(
850         "MAX(TITLE) AS maxTitle,"
851         + "NUMBER "
852         + "FROM " + prefix + "images IMAGES "
853         + "GROUP BY NUMBER"));
854
855         field4b.setAlias("IMAGENUMBER");
856         sb.setLength(0);
857         instance.appendQueryBodyToSql(sb, query, instance);
858         strSql = sb.toString();
859         assertTrue(strSql, strSql.equalsIgnoreCase(
860         "MAX(TITLE) AS maxTitle,"
861         + "NUMBER AS IMAGENUMBER "
862         + "FROM " + prefix + "images IMAGES "
863         + "GROUP BY IMAGENUMBER"));
864     }
865
866     /** Test of appendConstraintToSql method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
867     public void testAppendConstraintToSql() {
868
869         BasicSearchQuery query = new BasicSearchQuery();
870         StringBuffer sb = new StringBuffer ();
871         BasicStep step1 = query.addStep(images);
872         step1.setAlias(null);
873         FieldDefs imagesTitle = images.getField("title");
874         StepField field1 = query.addField(step1, imagesTitle);
875         FieldDefs imagesNumber = images.getField("number");
876         StepField field2 = query.addField(step1, imagesNumber);
877         BasicStep step2 = query.addStep(news);
878         step2.setAlias(null);
879         FieldDefs newsNumber = news.getField("number");
880         StepField field3 = query.addField(step2, newsNumber);
881         FieldDefs newsTitle = news.getField("title");
882         StepField field4 = query.addField(step2, newsTitle);
883
884         // Test for BasicFieldNullConstraint
885 BasicFieldNullConstraint constraint1
886         = new BasicFieldNullConstraint(field1);
887
888         sb.setLength(0);
889         instance.appendConstraintToSql(sb, constraint1, query, false, false);
890         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
891         "IMAGES.TITLE IS NULL"));
892
893         sb.setLength(0);
894         instance.appendConstraintToSql(sb, constraint1, query, false, true);
895         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
896         "IMAGES.TITLE IS NULL"));
897
898         sb.setLength(0);
899         instance.appendConstraintToSql(sb, constraint1, query, true, false);
900         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
901         "IMAGES.TITLE IS NOT NULL"));
902
903         sb.setLength(0);
904         constraint1.setInverse(true); // Set inverse.
905 instance.appendConstraintToSql(sb, constraint1, query, true, false);
906         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
907         "IMAGES.TITLE IS NULL"));
908
909         sb.setLength(0);
910         instance.appendConstraintToSql(sb, constraint1, query, false, false);
911         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
912         "IMAGES.TITLE IS NOT NULL"));
913
914         // Test for BasicFieldValueInConstraint (String).
915 BasicFieldValueInConstraint constraint2
916         = new BasicFieldValueInConstraint(field1);
917
918         // Empty values list, should throw IllegalStateException.
919 sb.setLength(0);
920         try {
921             instance.appendConstraintToSql(sb, constraint2, query, false, false);
922             fail("Empty values list, should throw IllegalStateException.");
923         } catch (IllegalStateException e) {}
924
925         sb.setLength(0);
926         constraint2.addValue("AsDf"); // Add first value.
927 instance.appendConstraintToSql(sb, constraint2, query, false, false);
928         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
929         "IMAGES.TITLE='AsDf'"));
930
931         sb.setLength(0);
932         constraint2.setCaseSensitive(false); // Case insensiteve
933 instance.appendConstraintToSql(sb, constraint2, query, false, false);
934         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
935         "LOWER(IMAGES.TITLE)='asdf'"));
936
937         sb.setLength(0);
938         constraint2.addValue("qWeR"); // Add second value.
939 instance.appendConstraintToSql(sb, constraint2, query, false, false);
940         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
941         "LOWER(IMAGES.TITLE) IN ('asdf','qwer')"));
942
943         sb.setLength(0);
944         constraint2.setCaseSensitive(true); // Case sensiteve
945 instance.appendConstraintToSql(sb, constraint2, query, false, false);
946         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
947         "IMAGES.TITLE IN ('AsDf','qWeR')"));
948
949         sb.setLength(0);
950         instance.appendConstraintToSql(sb, constraint2, query, false, true);
951         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
952         "IMAGES.TITLE IN ('AsDf','qWeR')"));
953
954         sb.setLength(0);
955         instance.appendConstraintToSql(sb, constraint2, query, true, false);
956         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
957         "IMAGES.TITLE NOT IN ('AsDf','qWeR')"));
958
959         sb.setLength(0);
960         constraint2.setInverse(true); // Set inverse.
961 instance.appendConstraintToSql(sb, constraint2, query, true, false);
962         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
963         "IMAGES.TITLE IN ('AsDf','qWeR')"));
964
965         sb.setLength(0);
966         instance.appendConstraintToSql(sb, constraint2, query, false, false);
967         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
968         "IMAGES.TITLE NOT IN ('AsDf','qWeR')"));
969
970         // Test for BasicFieldValueBetweenConstraint (String)
971 BasicFieldValueBetweenConstraint constraint2a
972             = new BasicFieldValueBetweenConstraint(field1, "AsDf", "jkLM");
973
974         sb.setLength(0);
975         instance.appendConstraintToSql(sb, constraint2a, query, false, false);
976         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
977             "IMAGES.TITLE BETWEEN 'AsDf' AND 'jkLM'"));
978
979         sb.setLength(0);
980         constraint2a.setInverse(true); // Set inverse.
981 instance.appendConstraintToSql(sb, constraint2a, query, false, false);
982         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
983             "IMAGES.TITLE NOT BETWEEN 'AsDf' AND 'jkLM'"));
984
985         sb.setLength(0);
986         instance.appendConstraintToSql(sb, constraint2a, query, true, false);
987         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
988             "IMAGES.TITLE BETWEEN 'AsDf' AND 'jkLM'"));
989
990         sb.setLength(0);
991         constraint2a.setCaseSensitive(false); // Set case insensitive.
992 instance.appendConstraintToSql(sb, constraint2a, query, true, false);
993         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
994             "LOWER(IMAGES.TITLE) BETWEEN 'asdf' AND 'jklm'"));
995
996         // Test for BasicFieldValueInConstraint (integer).
997 BasicFieldValueInConstraint constraint3 = new BasicFieldValueInConstraint(field2);
998
999         // Empty values list, should throw IllegalStateException.
1000 sb.setLength(0);
1001        try {
1002            instance.appendConstraintToSql(sb, constraint3, query, false, false);
1003            fail("Empty values list, should throw IllegalStateException.");
1004        } catch (IllegalStateException e) {}
1005
1006        sb.setLength(0);
1007        constraint3.addValue(new Integer (1234)); // Add first value.
1008
1009        instance.appendConstraintToSql(sb, constraint3, query, false, false);
1010
1011        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("IMAGES.NUMBER=1234"));
1012
1013        sb.setLength(0);
1014        constraint3.addValue(new Integer (5678)); // Add second value.
1015 instance.appendConstraintToSql(sb, constraint3, query, false, false);
1016        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("IMAGES.NUMBER IN (1234,5678)"));
1017
1018        sb.setLength(0);
1019        instance.appendConstraintToSql(sb, constraint3, query, false, true);
1020        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("IMAGES.NUMBER IN (1234,5678)"));
1021
1022        sb.setLength(0);
1023        instance.appendConstraintToSql(sb, constraint3, query, true, false);
1024        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("IMAGES.NUMBER NOT IN (1234,5678)"));
1025
1026        sb.setLength(0);
1027        constraint3.setInverse(true); // Set inverse.
1028 instance.appendConstraintToSql(sb, constraint3, query, true, false);
1029        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("IMAGES.NUMBER IN (1234,5678)"));
1030
1031        sb.setLength(0);
1032        instance.appendConstraintToSql(sb, constraint3, query, false, false);
1033        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1034        "IMAGES.NUMBER NOT IN (1234,5678)"));
1035
1036        sb.setLength(0);
1037        constraint3.setCaseSensitive(false); // case insensitive, ignored
1038 instance.appendConstraintToSql(sb, constraint3, query, false, false);
1039        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1040        "IMAGES.NUMBER NOT IN (1234,5678)"));
1041
1042        // Test for BasicFieldValueBetweenConstraint (integer)
1043 BasicFieldValueBetweenConstraint constraint3a
1044            = new BasicFieldValueBetweenConstraint(
1045                field2, new Integer (123), new Double (456.0));
1046
1047        sb.setLength(0);
1048        instance.appendConstraintToSql(sb, constraint3a, query, false, false);
1049        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1050            "IMAGES.NUMBER BETWEEN 123 AND 456"));
1051
1052        sb.setLength(0);
1053        constraint3a.setInverse(true); // Set inverse.
1054 instance.appendConstraintToSql(sb, constraint3a, query, false, false);
1055        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1056            "IMAGES.NUMBER NOT BETWEEN 123 AND 456"));
1057
1058        sb.setLength(0);
1059        instance.appendConstraintToSql(sb, constraint3a, query, true, false);
1060        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1061            "IMAGES.NUMBER BETWEEN 123 AND 456"));
1062
1063        sb.setLength(0);
1064        constraint3a.setCaseSensitive(false); // Set case insensitive, must be ignored.
1065 instance.appendConstraintToSql(sb, constraint3a, query, true, false);
1066        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1067            "IMAGES.NUMBER BETWEEN 123 AND 456"));
1068
1069        // Test for BasicFieldValueConstraint (string).
1070 BasicFieldValueConstraint constraint6
1071        = new BasicFieldValueConstraint(field1, "qWeRtY");
1072
1073        sb.setLength(0);
1074        instance.appendConstraintToSql(sb, constraint6, query, false, false);
1075        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1076        "IMAGES.TITLE='qWeRtY'"));
1077
1078        sb.setLength(0);
1079        constraint6.setOperator(FieldCompareConstraint.LESS);
1080        instance.appendConstraintToSql(sb, constraint6, query, false, false);
1081        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1082        "IMAGES.TITLE<'qWeRtY'"));
1083
1084        sb.setLength(0);
1085        constraint6.setOperator(FieldCompareConstraint.LESS_EQUAL);
1086        instance.appendConstraintToSql(sb, constraint6, query, false, false);
1087        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1088        "IMAGES.TITLE<='qWeRtY'"));
1089
1090        sb.setLength(0);
1091        constraint6.setOperator(FieldCompareConstraint.EQUAL);
1092        instance.appendConstraintToSql(sb, constraint6, query, false, false);
1093        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1094        "IMAGES.TITLE='qWeRtY'"));
1095
1096        sb.setLength(0);
1097        constraint6.setOperator(FieldCompareConstraint.NOT_EQUAL);
1098        instance.appendConstraintToSql(sb, constraint6, query, false, false);
1099        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1100        "IMAGES.TITLE<>'qWeRtY'"));
1101
1102        sb.setLength(0);
1103        constraint6.setOperator(FieldCompareConstraint.GREATER);
1104        instance.appendConstraintToSql(sb, constraint6, query, false, false);
1105        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1106        "IMAGES.TITLE>'qWeRtY'"));
1107
1108        sb.setLength(0);
1109        constraint6.setOperator(FieldCompareConstraint.GREATER_EQUAL);
1110        instance.appendConstraintToSql(sb, constraint6, query, false, false);
1111        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1112        "IMAGES.TITLE>='qWeRtY'"));
1113
1114        sb.setLength(0);
1115        constraint6.setOperator(FieldCompareConstraint.LIKE);
1116        instance.appendConstraintToSql(sb, constraint6, query, false, false);
1117        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1118        "IMAGES.TITLE LIKE 'qWeRtY'"));
1119
1120        sb.setLength(0);
1121        constraint6.setInverse(true); // set inverse
1122 instance.appendConstraintToSql(sb, constraint6, query, false, false);
1123        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1124        "IMAGES.TITLE NOT LIKE 'qWeRtY'"));
1125
1126        sb.setLength(0);
1127        instance.appendConstraintToSql(sb, constraint6, query, true, false);
1128        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1129        "IMAGES.TITLE LIKE 'qWeRtY'"));
1130
1131        sb.setLength(0);
1132        constraint6.setCaseSensitive(false); // case insensitive
1133 instance.appendConstraintToSql(sb, constraint6, query, true, false);
1134        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1135        "LOWER(IMAGES.TITLE) LIKE 'qwerty'"));
1136
1137        // Test for BasicFieldValueConstraint (integer).
1138 BasicFieldValueConstraint constraint7
1139        = new BasicFieldValueConstraint(field2, new Integer (9876));
1140
1141        sb.setLength(0);
1142        instance.appendConstraintToSql(sb, constraint7, query, false, false);
1143        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1144        "IMAGES.NUMBER=9876"));
1145
1146        sb.setLength(0);
1147        constraint7.setOperator(FieldCompareConstraint.LESS);
1148        instance.appendConstraintToSql(sb, constraint7, query, false, false);
1149        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1150        "IMAGES.NUMBER<9876"));
1151
1152        sb.setLength(0);
1153        constraint7.setOperator(FieldCompareConstraint.LESS_EQUAL);
1154        instance.appendConstraintToSql(sb, constraint7, query, false, false);
1155        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1156        "IMAGES.NUMBER<=9876"));
1157
1158        sb.setLength(0);
1159        constraint7.setOperator(FieldCompareConstraint.EQUAL);
1160        instance.appendConstraintToSql(sb, constraint7, query, false, false);
1161        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1162        "IMAGES.NUMBER=9876"));
1163
1164        sb.setLength(0);
1165        constraint7.setOperator(FieldCompareConstraint.NOT_EQUAL);
1166        instance.appendConstraintToSql(sb, constraint7, query, false, false);
1167        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1168        "IMAGES.NUMBER<>9876"));
1169
1170        sb.setLength(0);
1171        constraint7.setOperator(FieldCompareConstraint.GREATER);
1172        instance.appendConstraintToSql(sb, constraint7, query, false, false);
1173        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1174        "IMAGES.NUMBER>9876"));
1175
1176        sb.setLength(0);
1177        constraint7.setOperator(FieldCompareConstraint.GREATER_EQUAL);
1178        instance.appendConstraintToSql(sb, constraint7, query, false, false);
1179        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1180        "IMAGES.NUMBER>=9876"));
1181
1182        sb.setLength(0);
1183        constraint7.setInverse(true); // set inverse
1184 instance.appendConstraintToSql(sb, constraint7, query, false, false);
1185        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1186        "NOT (IMAGES.NUMBER>=9876)"));
1187
1188        sb.setLength(0);
1189        instance.appendConstraintToSql(sb, constraint7, query, true, false);
1190        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1191        "IMAGES.NUMBER>=9876"));
1192
1193        sb.setLength(0);
1194        constraint7.setCaseSensitive(false); // case insensitive, ignored
1195 instance.appendConstraintToSql(sb, constraint7, query, true, false);
1196        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1197        "IMAGES.NUMBER>=9876"));
1198
1199        // Test for BasicCompareFieldsConstraint (integer)
1200 BasicCompareFieldsConstraint constraint8
1201        = new BasicCompareFieldsConstraint(field2, field3);
1202
1203        sb.setLength(0);
1204        instance.appendConstraintToSql(sb, constraint8, query, false, false);
1205        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1206        "IMAGES.NUMBER=NEWS.NUMBER"));
1207
1208        sb.setLength(0);
1209        constraint8.setOperator(FieldCompareConstraint.LESS);
1210        instance.appendConstraintToSql(sb, constraint8, query, false, false);
1211        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1212        "IMAGES.NUMBER<NEWS.NUMBER"));
1213
1214        sb.setLength(0);
1215        constraint8.setOperator(FieldCompareConstraint.LESS_EQUAL);
1216        instance.appendConstraintToSql(sb, constraint8, query, false, false);
1217        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1218        "IMAGES.NUMBER<=NEWS.NUMBER"));
1219
1220        sb.setLength(0);
1221        constraint8.setOperator(FieldCompareConstraint.EQUAL);
1222        instance.appendConstraintToSql(sb, constraint8, query, false, false);
1223        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1224        "IMAGES.NUMBER=NEWS.NUMBER"));
1225
1226        sb.setLength(0);
1227        constraint8.setOperator(FieldCompareConstraint.NOT_EQUAL);
1228        instance.appendConstraintToSql(sb, constraint8, query, false, false);
1229        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1230        "IMAGES.NUMBER<>NEWS.NUMBER"));
1231
1232        sb.setLength(0);
1233        constraint8.setOperator(FieldCompareConstraint.GREATER);
1234        instance.appendConstraintToSql(sb, constraint8, query, false, false);
1235        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1236        "IMAGES.NUMBER>NEWS.NUMBER"));
1237
1238        sb.setLength(0);
1239        constraint8.setOperator(FieldCompareConstraint.GREATER_EQUAL);
1240        instance.appendConstraintToSql(sb, constraint8, query, false, false);
1241        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1242        "IMAGES.NUMBER>=NEWS.NUMBER"));
1243
1244        sb.setLength(0);
1245        constraint8.setInverse(true); // set inverse
1246 instance.appendConstraintToSql(sb, constraint8, query, false, false);
1247        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1248        "NOT (IMAGES.NUMBER>=NEWS.NUMBER)"));
1249
1250        sb.setLength(0);
1251        instance.appendConstraintToSql(sb, constraint8, query, true, false);
1252        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1253        "IMAGES.NUMBER>=NEWS.NUMBER"));
1254
1255        sb.setLength(0);
1256        constraint8.setCaseSensitive(false); // case insensitive, ignored
1257 instance.appendConstraintToSql(sb, constraint8, query, true, false);
1258        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1259        "IMAGES.NUMBER>=NEWS.NUMBER"));
1260
1261        // Test for BasicCompareFieldsConstraint (string)
1262 BasicCompareFieldsConstraint constraint9 =
1263        new BasicCompareFieldsConstraint(field1, field4);
1264
1265        sb.setLength(0);
1266        instance.appendConstraintToSql(sb, constraint9, query, false, false);
1267        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1268        "IMAGES.TITLE=NEWS.TITLE"));
1269
1270        sb.setLength(0);
1271        constraint9.setOperator(FieldCompareConstraint.LESS);
1272        instance.appendConstraintToSql(sb, constraint9, query, false, false);
1273        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1274        "IMAGES.TITLE<NEWS.TITLE"));
1275
1276        sb.setLength(0);
1277        constraint9.setOperator(FieldCompareConstraint.GREATER);
1278        instance.appendConstraintToSql(sb, constraint9, query, false, false);
1279        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1280        "IMAGES.TITLE>NEWS.TITLE"));
1281
1282        sb.setLength(0);
1283        constraint9.setInverse(true); // set inverse
1284 instance.appendConstraintToSql(sb, constraint9, query, false, false);
1285        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1286        "NOT (IMAGES.TITLE>NEWS.TITLE)"));
1287
1288        sb.setLength(0);
1289        instance.appendConstraintToSql(sb, constraint9, query, true, false);
1290        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1291        "IMAGES.TITLE>NEWS.TITLE"));
1292
1293        sb.setLength(0);
1294        constraint9.setCaseSensitive(false); // case insensitive
1295 instance.appendConstraintToSql(sb, constraint9, query, true, false);
1296        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1297        "LOWER(IMAGES.TITLE)>LOWER(NEWS.TITLE)"));
1298
1299        // Test for composite constraint.
1300 BasicCompositeConstraint constraint10 =
1301        new BasicCompositeConstraint(CompositeConstraint.LOGICAL_AND);
1302
1303        sb.setLength(0);
1304        try {
1305            // Composite constraint, should throw IllegalArgumentException.
1306 instance.appendConstraintToSql(sb, constraint10, query, false, false);
1307            fail("Composite constraint, should throw IllegalArgumentException.");
1308        } catch (IllegalArgumentException e) {}
1309
1310        // Test for LegacyConstraint.
1311 BasicLegacyConstraint constraint11
1312            = new BasicLegacyConstraint("a=b AND c=d");
1313        sb.setLength(0);
1314        instance.appendConstraintToSql(sb, constraint11, query, false, false);
1315        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1316        "a=b AND c=d"));
1317
1318        sb.setLength(0);
1319        instance.appendConstraintToSql(sb, constraint11, query, true, false);
1320        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1321        "NOT (a=b AND c=d)"));
1322
1323        sb.setLength(0);
1324        instance.appendConstraintToSql(sb, constraint11, query, false, true);
1325        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1326        "(a=b AND c=d)"));
1327
1328        sb.setLength(0);
1329        instance.appendConstraintToSql(sb, constraint11, query, true, true);
1330        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1331        "NOT (a=b AND c=d)"));
1332
1333        // Empty LegacyConstraint.
1334 sb.setLength(0);
1335        constraint11.setConstraint(" ");
1336        instance.appendConstraintToSql(sb, constraint11, query, true, true);
1337        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(""));
1338
1339        sb.setLength(0);
1340        instance.appendConstraintToSql(sb, constraint11, query, true, false);
1341        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(""));
1342
1343        sb.setLength(0);
1344        instance.appendConstraintToSql(sb, constraint11, query, false, true);
1345        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(""));
1346
1347        sb.setLength(0);
1348        instance.appendConstraintToSql(sb, constraint11, query, false, false);
1349        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(""));
1350    }
1351
1352    /** Test of getSupportLevel(int,SearchQuery), of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
1353    public void testGetSupportLevel() throws Exception {
1354        BasicSearchQuery query = new BasicSearchQuery();
1355
1356        // Support max number only when set to default (= -1).
1357 assertTrue(instance.getSupportLevel(SearchQueryHandler.FEATURE_MAX_NUMBER, query)
1358        == SearchQueryHandler.SUPPORT_OPTIMAL);
1359        query.setMaxNumber(100);
1360        assertTrue(instance.getSupportLevel(SearchQueryHandler.FEATURE_MAX_NUMBER, query)
1361        == SearchQueryHandler.SUPPORT_NONE);
1362        query.setMaxNumber(-1);
1363        assertTrue(instance.getSupportLevel(SearchQueryHandler.FEATURE_MAX_NUMBER, query)
1364        == SearchQueryHandler.SUPPORT_OPTIMAL);
1365
1366        // Support offset only when set to default (= 0).
1367 assertTrue(instance.getSupportLevel(SearchQueryHandler.FEATURE_OFFSET, query)
1368        == SearchQueryHandler.SUPPORT_OPTIMAL);
1369        query.setOffset(100);
1370        assertTrue(instance.getSupportLevel(SearchQueryHandler.FEATURE_OFFSET, query)
1371        == SearchQueryHandler.SUPPORT_NONE);
1372        query.setOffset(0);
1373        assertTrue(instance.getSupportLevel(SearchQueryHandler.FEATURE_OFFSET, query)
1374        == SearchQueryHandler.SUPPORT_OPTIMAL);
1375    }
1376
1377    /** Test of getSupportLevel(Constraint,SearchQuery), of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
1378    public void testGetSupportLevel2() throws Exception {
1379        // Should return basic support level of constraint.
1380 SearchQuery query = new BasicSearchQuery();
1381        Constraint constraint = new TestConstraint(SearchQueryHandler.SUPPORT_NONE);
1382        assertTrue(instance.getSupportLevel(constraint, query) == SearchQueryHandler.SUPPORT_NONE);
1383        constraint = new TestConstraint(SearchQueryHandler.SUPPORT_WEAK);
1384        assertTrue(instance.getSupportLevel(constraint, query) == SearchQueryHandler.SUPPORT_WEAK);
1385        constraint = new TestConstraint(SearchQueryHandler.SUPPORT_NORMAL);
1386        assertTrue(instance.getSupportLevel(constraint, query) == SearchQueryHandler.SUPPORT_NORMAL);
1387        constraint = new TestConstraint(SearchQueryHandler.SUPPORT_OPTIMAL);
1388        assertTrue(instance.getSupportLevel(constraint, query) == SearchQueryHandler.SUPPORT_OPTIMAL);
1389    }
1390
1391    /** Test of getAllowedValue method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
1392    public void testGetAllowedValue() {
1393        Set entries = disallowedValues.entrySet();
1394        Iterator iEntries = entries.iterator();
1395        while (iEntries.hasNext()) {
1396            Map.Entry entry = (Map.Entry) iEntries.next();
1397            String disallowedValue = (String ) entry.getKey();
1398            String allowedValue = (String ) entry.getValue();
1399
1400            // Disallowed value.
1401 assertTrue(instance.getAllowedValue(disallowedValue) + " was expected to equal " + allowedValue,
1402                       instance.getAllowedValue(disallowedValue).equalsIgnoreCase(allowedValue));
1403
1404            // Allowed values.
1405 assertTrue(instance.getAllowedValue(allowedValue) + " was expected to equal " + allowedValue,
1406                       instance.getAllowedValue(allowedValue).equalsIgnoreCase(allowedValue));
1407            allowedValue += "_must_be_allowed_as_well";
1408            assertTrue(instance.getAllowedValue(allowedValue).equalsIgnoreCase(allowedValue));
1409        }
1410
1411        try {
1412            // Null value, should throw IllegalArgumentException.
1413 instance.getAllowedValue(null);
1414            fail("Null value, shoul throw IllegalArgumentException.");
1415        } catch (IllegalArgumentException e) {}
1416    }
1417
1418    /** Test of appendCompositeConstraintToSql method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
1419    public void testAppendCompositeConstraintToSql() throws Exception {
1420        BasicSearchQuery query = new BasicSearchQuery();
1421        StringBuffer sb = new StringBuffer ();
1422        Step step1 = query.addStep(images).setAlias(null);
1423        FieldDefs imagesNumber = images.getField("number");
1424        StepField field1 = query.addField(step1, imagesNumber);
1425        Step step2 = query.addStep(news).setAlias(null);
1426        FieldDefs newsNumber = news.getField("number");
1427        StepField field2 = query.addField(step2, newsNumber);
1428
1429        BasicFieldValueConstraint constraint1
1430        = new BasicFieldValueConstraint(field1, new Integer (9876));
1431        constraint1.setOperator(FieldCompareConstraint.LESS);
1432        constraint1.setOperator(FieldCompareConstraint.GREATER);
1433        constraint1.setInverse(true); // set inverse
1434
1435        BasicCompareFieldsConstraint constraint2
1436        = new BasicCompareFieldsConstraint(field1, field2);
1437        constraint2.setOperator(FieldCompareConstraint.GREATER);
1438        constraint2.setInverse(true); // set inverse
1439
1440        // Test for BasicCompareFieldsConstraint
1441 BasicCompositeConstraint compositeConstraint =
1442        new BasicCompositeConstraint(CompositeConstraint.LOGICAL_AND);
1443
1444        try {
1445            // Empty composite constraint, should throw IllegalStateException.
1446 instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1447            query, false, false, instance);
1448            fail("Empty composite constraint, should throw IllegalStateException.");
1449        } catch (IllegalStateException e) {}
1450
1451        sb.setLength(0);
1452        compositeConstraint.addChild(constraint2); // Add first child constraint.
1453 instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1454        query, false, false, instance);
1455        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1456        "NOT (IMAGES.NUMBER>NEWS.NUMBER)"));
1457
1458        sb.setLength(0);
1459        instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1460        query, true, false, instance);
1461        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1462        "IMAGES.NUMBER>NEWS.NUMBER"));
1463
1464        sb.setLength(0);
1465        instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1466        query, true, true, instance);
1467        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1468        "IMAGES.NUMBER>NEWS.NUMBER"));
1469
1470
1471        sb.setLength(0);
1472        compositeConstraint.addChild(constraint1); // Add second child constraint.
1473 instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1474        query, false, false, instance);
1475        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1476        "NOT (IMAGES.NUMBER>NEWS.NUMBER) AND NOT (IMAGES.NUMBER>9876)"));
1477
1478        sb.setLength(0);
1479        instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1480        query, true, false, instance);
1481        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1482        "IMAGES.NUMBER>NEWS.NUMBER OR IMAGES.NUMBER>9876"));
1483
1484        sb.setLength(0);
1485        instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1486        query, true, true, instance);
1487        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1488        "(IMAGES.NUMBER>NEWS.NUMBER OR IMAGES.NUMBER>9876)"));
1489
1490        sb.setLength(0);
1491        instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1492        query, false, true, instance);
1493         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1494        "(NOT (IMAGES.NUMBER>NEWS.NUMBER) AND NOT (IMAGES.NUMBER>9876))"));
1495
1496        sb.setLength(0);
1497        constraint1.setInverse(false); // Set second child not inverse.
1498 instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1499        query, false, false, instance);
1500         assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1501        "NOT (IMAGES.NUMBER>NEWS.NUMBER) AND IMAGES.NUMBER>9876"));
1502
1503        sb.setLength(0);
1504        instance.appendCompositeConstraintToSql(sb, compositeConstraint,
1505        query, true, false, instance);
1506        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1507        "IMAGES.NUMBER>NEWS.NUMBER OR NOT (IMAGES.NUMBER>9876)"));
1508
1509        // Composite with compositeConstraint as childs.
1510 sb.setLength(0);
1511        BasicCompositeConstraint composite2 =
1512            new BasicCompositeConstraint(CompositeConstraint.LOGICAL_AND)
1513                .addChild(compositeConstraint);
1514        instance.appendCompositeConstraintToSql(
1515            sb, composite2, query, false, false, instance);
1516        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1517        "NOT (IMAGES.NUMBER>NEWS.NUMBER) AND IMAGES.NUMBER>9876"));
1518
1519        // Composite with compositeConstraint as childs.
1520 sb.setLength(0);
1521        composite2.addChild(compositeConstraint);
1522        instance.appendCompositeConstraintToSql(
1523            sb, composite2, query, false, false, instance);
1524        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(
1525        "(NOT (IMAGES.NUMBER>NEWS.NUMBER) AND IMAGES.NUMBER>9876) AND "
1526        + "(NOT (IMAGES.NUMBER>NEWS.NUMBER) AND IMAGES.NUMBER>9876)"));
1527    }
1528
1529    /** Test of appendFieldValue method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
1530    public void testAppendFieldValue() {
1531        StringBuffer sb = new StringBuffer ();
1532        instance.appendFieldValue(sb, "asd EFG", false, Field.TYPE_STRING);
1533        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("'asd EFG'"));
1534
1535        sb.setLength(0);
1536        instance.appendFieldValue(sb, "asd EFG", true, Field.TYPE_STRING);
1537        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("'asd efg'"));
1538
1539        sb.setLength(0);
1540        instance.appendFieldValue(sb, "asd EFG", false, Field.TYPE_XML);
1541        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("'asd EFG'"));
1542
1543        sb.setLength(0);
1544        instance.appendFieldValue(sb, "asd EFG", true, Field.TYPE_XML);
1545        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("'asd efg'"));
1546
1547        sb.setLength(0);
1548        instance.appendFieldValue(sb, "123.0", true, Field.TYPE_DOUBLE);
1549        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("123.0"));
1550
1551        sb.setLength(0);
1552        instance.appendFieldValue(sb, new Double (123.45), false,
1553            Field.TYPE_DOUBLE);
1554        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("123.45"));
1555
1556        sb.setLength(0);
1557        instance.appendFieldValue(sb, new Double (123.0), false,
1558            Field.TYPE_DOUBLE);
1559        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("123"));
1560    }
1561
1562    public static Test suite() {
1563        TestSuite suite = new TestSuite(BasicSqlHandlerTest.class);
1564
1565        return suite;
1566    }
1567
1568    /** Test of useLower method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
1569    public void testUseLower() {
1570        // Should always return true.
1571 assertTrue(instance.useLower(null));
1572    }
1573
1574    /** Test of appendLikeOperator method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
1575    public void testAppendLikeOperator() {
1576        // Should always append " LIKE ".
1577 StringBuffer sb = new StringBuffer ();
1578        instance.appendLikeOperator(sb, true);
1579        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(" LIKE "));
1580
1581        sb.setLength(0);
1582        instance.appendLikeOperator(sb, false);
1583        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase(" LIKE "));
1584    }
1585
1586    /** Test of appendField method, of class org.mmbase.storage.search.implementation.database.BasicSqlHandler. */
1587    public void testAppendField() {
1588        BasicSearchQuery query = new BasicSearchQuery();
1589        BasicStep step = query.addStep(images);
1590        images.getField("number");
1591
1592        StringBuffer sb = new StringBuffer ();
1593        instance.appendField(sb, step, "number", false);
1594        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("NUMBER"));
1595
1596        sb.setLength(0);
1597        instance.appendField(sb, step, "number", true);
1598        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("IMAGES.NUMBER"));
1599
1600        sb.setLength(0);
1601        step.setAlias("IMAGENUMBER");
1602        instance.appendField(sb, step, "number", true);
1603        assertTrue(sb.toString(), sb.toString().equalsIgnoreCase("IMAGENUMBER.NUMBER"));
1604    }
1605
1606}
1607

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags