001: package test.org.mandarax.jdbc;
002:
003: /*
004: * Copyright (C) 1999-2004 <a href="mailto:mandarax@jbdietrich.com">Jens Dietrich</a>
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation; either
009: * version 2 of the License, or (at your option) any later version.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: * You should have received a copy of the GNU Lesser General Public
017: * License along with this library; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: */
020:
021: import junit.framework.TestSuite;
022:
023: /**
024: * A test suite for queries.
025: * @author <A HREF="mailto:mandarax@jbdietrich.com">Jens Dietrich</A>
026: * @version 3.3.2 <29 December 2004>
027: * @since 3.0
028: */
029:
030: public class QueryTests2 implements TestURLs, Constants {
031:
032: /**
033: * Launch the test suite. See TestRunner for interpretation
034: * of command line parameters.
035: * @see test.org.mandarax.testsupport.TestRunner
036: * @param args parameters
037: */
038: public static void main(String[] args) {
039: test.org.mandarax.testsupport.TestRunner.run(QueryTests2.class,
040: args);
041: }
042:
043: /**
044: * Get the test suite.
045: * @return a test suite
046: */
047: public static TestSuite suite() {
048:
049: TestSuite suite = new TestSuite(
050: "SQL Query test suite 2 - uses a memory kb");
051: /* test kb:
052: "Tom", "Meier", "01/01/1960", 80, 1.80;
053: "John", "Meier", "01/01/1960", 90, 1.90;
054: "Jim", "Smith", "01/01/1970", 100, 1.90;
055: "Jack", "Smith", "01/01/1970", 60, 1.60;
056: "Tom", "Taylor", "01/01/1980", 70, 1.70;
057: "John", "Meijer", "01/01/1980", 80, 1.80;
058: "Jim", "Mejer", "01/01/1990", 50, 1.50;
059: */
060: addSimpleEqualTests(suite);
061: addSimpleLikeTests(suite);
062: addSimpleCompareTests(suite);
063: addANDTests(suite);
064: addORTests(suite);
065: addNOTTests(suite);
066: addComplexConditionTests(suite);
067: addOrderByTests(suite);
068: addGroupByTests(suite);
069: addHavingTests(suite);
070: addCountTests(suite);
071: addAdvancedTests(suite);
072: return suite;
073: }
074:
075: /**
076: * Convert a record.
077: */
078: static Object[] convert(String name, String fname, String dob,
079: int weight, double size) {
080: try {
081: return new Object[] { name, fname, parse(dob),
082: new Integer(weight), new Double(size) };
083: } catch (Exception x) {
084: x.printStackTrace();
085: return null;
086: }
087: }
088:
089: /**
090: * Parse a string and return it as SQL date.
091: * @param txt a data string
092: * @return a sql date instance
093: */
094: public static java.sql.Date parse(String txt) {
095: try {
096: java.util.Date d = df.parse(txt);
097: return new java.sql.Date(d.getTime());
098: } catch (Exception x) {
099: return null;
100: }
101: }
102:
103: /**
104: * Add tests with one condition using = as operator.
105: * @param suite a test suite.
106: */
107: static void addSimpleEqualTests(TestSuite suite) {
108: suite
109: .addTest(new QueryTestCase5("select * from people",
110: new Object[][] {
111: convert("Tom", "Meier", "01/01/1960",
112: 80, 1.80),
113: convert("John", "Meier", "01/01/1960",
114: 90, 1.90),
115: convert("Jim", "Smith", "01/01/1970",
116: 100, 1.90),
117: convert("Jack", "Smith", "01/01/1970",
118: 60, 1.60),
119: convert("Tom", "Taylor", "01/01/1980",
120: 70, 1.70),
121: convert("John", "Meijer", "01/01/1980",
122: 80, 1.80),
123: convert("Jim", "Mejer", "01/01/1990",
124: 50, 1.50) }));
125: suite
126: .addTest(new QueryTestCase5(
127: "select * from people where first_name='Tom'",
128: new Object[][] {
129: convert("Tom", "Meier", "01/01/1960",
130: 80, 1.80),
131: convert("Tom", "Taylor", "01/01/1980",
132: 70, 1.70) }));
133: suite
134: .addTest(new QueryTestCase5(
135: "select * from people where name='Meier'",
136: new Object[][] {
137: convert("Tom", "Meier", "01/01/1960",
138: 80, 1.80),
139: convert("John", "Meier", "01/01/1960",
140: 90, 1.90) }));
141: suite
142: .addTest(new QueryTestCase5(
143: "select * from people where weight=80",
144: new Object[][] {
145: convert("Tom", "Meier", "01/01/1960",
146: 80, 1.80),
147: convert("John", "Meijer", "01/01/1980",
148: 80, 1.80) }));
149:
150: suite
151: .addTest(new QueryTestCase5(
152: "select * from people where size=1.80",
153: new Object[][] {
154: convert("Tom", "Meier", "01/01/1960",
155: 80, 1.80),
156: convert("John", "Meijer", "01/01/1980",
157: 80, 1.80) }));
158: suite.addTest(new QueryTestCase5(
159: // date literal will be converted by the driver to a java.sql.Date instance !
160: "select * from people where dob='1990-01-01'",
161: new Object[][] { convert("Jim", "Mejer", "01/01/1990",
162: 50, 1.50) }));
163: }
164:
165: /**
166: * Add tests with one condition using <,>,<= and >= operators.
167: * @param suite a test suite.
168: */
169: static void addSimpleCompareTests(TestSuite suite) {
170:
171: suite
172: .addTest(new QueryTestCase5("select * from people",
173: new Object[][] {
174: convert("Tom", "Meier", "01/01/1960",
175: 80, 1.80),
176: convert("John", "Meier", "01/01/1960",
177: 90, 1.90),
178: convert("Jim", "Smith", "01/01/1970",
179: 100, 1.90),
180: convert("Jack", "Smith", "01/01/1970",
181: 60, 1.60),
182: convert("Tom", "Taylor", "01/01/1980",
183: 70, 1.70),
184: convert("John", "Meijer", "01/01/1980",
185: 80, 1.80),
186: convert("Jim", "Mejer", "01/01/1990",
187: 50, 1.50) }));
188: suite
189: .addTest(new QueryTestCase5(
190: "select * from people where first_name>'John'",
191: new Object[][] {
192: convert("Tom", "Meier", "01/01/1960",
193: 80, 1.80),
194: convert("Tom", "Taylor", "01/01/1980",
195: 70, 1.70) }));
196: suite
197: .addTest(new QueryTestCase5(
198: "select * from people where first_name>='John'",
199: new Object[][] {
200: convert("Tom", "Meier", "01/01/1960",
201: 80, 1.80),
202: convert("John", "Meier", "01/01/1960",
203: 90, 1.90),
204: convert("Tom", "Taylor", "01/01/1980",
205: 70, 1.70),
206: convert("John", "Meijer", "01/01/1980",
207: 80, 1.80) }));
208: suite
209: .addTest(new QueryTestCase5(
210: "select * from people where first_name<'John'",
211: new Object[][] {
212: convert("Jim", "Smith", "01/01/1970",
213: 100, 1.90),
214: convert("Jack", "Smith", "01/01/1970",
215: 60, 1.60),
216: convert("Jim", "Mejer", "01/01/1990",
217: 50, 1.50) }));
218: suite
219: .addTest(new QueryTestCase5(
220: "select * from people where first_name<='John'",
221: new Object[][] {
222: convert("John", "Meier", "01/01/1960",
223: 90, 1.90),
224: convert("Jim", "Smith", "01/01/1970",
225: 100, 1.90),
226: convert("Jack", "Smith", "01/01/1970",
227: 60, 1.60),
228: convert("John", "Meijer", "01/01/1980",
229: 80, 1.80),
230: convert("Jim", "Mejer", "01/01/1990",
231: 50, 1.50) }));
232: suite
233: .addTest(new QueryTestCase5(
234: "select * from people where weight<80",
235: new Object[][] {
236: convert("Jack", "Smith", "01/01/1970",
237: 60, 1.60),
238: convert("Tom", "Taylor", "01/01/1980",
239: 70, 1.70),
240: convert("Jim", "Mejer", "01/01/1990",
241: 50, 1.50) }));
242: suite
243: .addTest(new QueryTestCase5(
244: "select * from people where weight<=80",
245: new Object[][] {
246: convert("Tom", "Meier", "01/01/1960",
247: 80, 1.80),
248: convert("Jack", "Smith", "01/01/1970",
249: 60, 1.60),
250: convert("Tom", "Taylor", "01/01/1980",
251: 70, 1.70),
252: convert("John", "Meijer", "01/01/1980",
253: 80, 1.80),
254: convert("Jim", "Mejer", "01/01/1990",
255: 50, 1.50) }));
256: suite
257: .addTest(new QueryTestCase5(
258: "select * from people where weight>80",
259: new Object[][] {
260: convert("John", "Meier", "01/01/1960",
261: 90, 1.90),
262: convert("Jim", "Smith", "01/01/1970",
263: 100, 1.90) }));
264: suite
265: .addTest(new QueryTestCase5(
266: "select * from people where weight>=80",
267: new Object[][] {
268: convert("Tom", "Meier", "01/01/1960",
269: 80, 1.80),
270: convert("John", "Meier", "01/01/1960",
271: 90, 1.90),
272: convert("Jim", "Smith", "01/01/1970",
273: 100, 1.90),
274: convert("John", "Meijer", "01/01/1980",
275: 80, 1.80) }));
276: suite
277: .addTest(new QueryTestCase5(
278: "select * from people where size<1.80",
279: new Object[][] {
280: convert("Jack", "Smith", "01/01/1970",
281: 60, 1.60),
282: convert("Tom", "Taylor", "01/01/1980",
283: 70, 1.70),
284: convert("Jim", "Mejer", "01/01/1990",
285: 50, 1.50) }));
286: suite
287: .addTest(new QueryTestCase5(
288: "select * from people where size<=1.80",
289: new Object[][] {
290: convert("Tom", "Meier", "01/01/1960",
291: 80, 1.80),
292: convert("Jack", "Smith", "01/01/1970",
293: 60, 1.60),
294: convert("Tom", "Taylor", "01/01/1980",
295: 70, 1.70),
296: convert("John", "Meijer", "01/01/1980",
297: 80, 1.80),
298: convert("Jim", "Mejer", "01/01/1990",
299: 50, 1.50) }));
300: suite
301: .addTest(new QueryTestCase5(
302: "select * from people where size>1.80",
303: new Object[][] {
304: convert("John", "Meier", "01/01/1960",
305: 90, 1.90),
306: convert("Jim", "Smith", "01/01/1970",
307: 100, 1.90) }));
308: suite
309: .addTest(new QueryTestCase5(
310: "select * from people where size>=1.80",
311: new Object[][] {
312: convert("Tom", "Meier", "01/01/1960",
313: 80, 1.80),
314: convert("John", "Meier", "01/01/1960",
315: 90, 1.90),
316: convert("Jim", "Smith", "01/01/1970",
317: 100, 1.90),
318: convert("John", "Meijer", "01/01/1980",
319: 80, 1.80) }));
320: suite
321: .addTest(new QueryTestCase5(
322: "select * from people where dob<'1970-01-01'",
323: new Object[][] {
324: convert("Tom", "Meier", "01/01/1960",
325: 80, 1.80),
326: convert("John", "Meier", "01/01/1960",
327: 90, 1.90) }));
328: suite
329: .addTest(new QueryTestCase5(
330: "select * from people where dob<='1970-01-01'",
331: new Object[][] {
332: convert("Tom", "Meier", "01/01/1960",
333: 80, 1.80),
334: convert("John", "Meier", "01/01/1960",
335: 90, 1.90),
336: convert("Jim", "Smith", "01/01/1970",
337: 100, 1.90),
338: convert("Jack", "Smith", "01/01/1970",
339: 60, 1.60) }));
340: suite
341: .addTest(new QueryTestCase5(
342: "select * from people where dob>'1970-01-01'",
343: new Object[][] {
344: convert("Tom", "Taylor", "01/01/1980",
345: 70, 1.70),
346: convert("John", "Meijer", "01/01/1980",
347: 80, 1.80),
348: convert("Jim", "Mejer", "01/01/1990",
349: 50, 1.50) }));
350: suite
351: .addTest(new QueryTestCase5(
352: "select * from people where dob>='1970-01-01'",
353: new Object[][] {
354: convert("Jim", "Smith", "01/01/1970",
355: 100, 1.90),
356: convert("Jack", "Smith", "01/01/1970",
357: 60, 1.60),
358: convert("Tom", "Taylor", "01/01/1980",
359: 70, 1.70),
360: convert("John", "Meijer", "01/01/1980",
361: 80, 1.80),
362: convert("Jim", "Mejer", "01/01/1990",
363: 50, 1.50) }));
364: }
365:
366: /**
367: * Add tests with one condition using the like operator.
368: * @param suite a test suite.
369: */
370: static void addSimpleLikeTests(TestSuite suite) {
371: suite
372: .addTest(new QueryTestCase5(
373: "select * from people where name like 'Mei%'",
374: new Object[][] {
375: convert("Tom", "Meier", "01/01/1960",
376: 80, 1.80),
377: convert("John", "Meier", "01/01/1960",
378: 90, 1.90),
379: convert("John", "Meijer", "01/01/1980",
380: 80, 1.80) }));
381: suite
382: .addTest(new QueryTestCase5(
383: "select * from people where name like '%i%'",
384: new Object[][] {
385: convert("Tom", "Meier", "01/01/1960",
386: 80, 1.80),
387: convert("John", "Meier", "01/01/1960",
388: 90, 1.90),
389: convert("Jim", "Smith", "01/01/1970",
390: 100, 1.90),
391: convert("Jack", "Smith", "01/01/1970",
392: 60, 1.60),
393: convert("John", "Meijer", "01/01/1980",
394: 80, 1.80) }));
395: suite
396: .addTest(new QueryTestCase5(
397: "select * from people where name like '%er'",
398: new Object[][] {
399: convert("Tom", "Meier", "01/01/1960",
400: 80, 1.80),
401: convert("John", "Meier", "01/01/1960",
402: 90, 1.90),
403: convert("John", "Meijer", "01/01/1980",
404: 80, 1.80),
405: convert("Jim", "Mejer", "01/01/1990",
406: 50, 1.50) }));
407: suite
408: .addTest(new QueryTestCase5(
409: "select * from people where name like '%'",
410: new Object[][] {
411: convert("Tom", "Meier", "01/01/1960",
412: 80, 1.80),
413: convert("John", "Meier", "01/01/1960",
414: 90, 1.90),
415: convert("Jim", "Smith", "01/01/1970",
416: 100, 1.90),
417: convert("Jack", "Smith", "01/01/1970",
418: 60, 1.60),
419: convert("Tom", "Taylor", "01/01/1980",
420: 70, 1.70),
421: convert("John", "Meijer", "01/01/1980",
422: 80, 1.80),
423: convert("Jim", "Mejer", "01/01/1990",
424: 50, 1.50) }));
425: suite.addTest(new QueryTestCase5(
426: "select * from people where name like '\\%'",
427: new Object[][] {}));
428: suite
429: .addTest(new QueryTestCase5(
430: "select * from people where name like 'Me_er'",
431: new Object[][] {
432: convert("Tom", "Meier", "01/01/1960",
433: 80, 1.80),
434: convert("John", "Meier", "01/01/1960",
435: 90, 1.90),
436: convert("Jim", "Mejer", "01/01/1990",
437: 50, 1.50) }));
438: }
439:
440: /**
441: * Add tests with compound conditions connected by AND.
442: * @param suite a test suite.
443: */
444: static void addANDTests(TestSuite suite) {
445: suite
446: .addTest(new QueryTestCase5(
447: "select * from people where name='Meier' and first_name='Tom'",
448: new Object[][] { convert("Tom", "Meier",
449: "01/01/1960", 80, 1.80) }));
450: suite
451: .addTest(new QueryTestCase5(
452: "select * from people where name like 'M%' and first_name = 'John'",
453: new Object[][] {
454: convert("John", "Meier", "01/01/1960",
455: 90, 1.90),
456: convert("John", "Meijer", "01/01/1980",
457: 80, 1.80) }));
458: suite
459: .addTest(new QueryTestCase5(
460: "select * from people where name like 'M%' and first_name = 'John' and weight=90",
461: new Object[][] { convert("John", "Meier",
462: "01/01/1960", 90, 1.90) }));
463: }
464:
465: /**
466: * Add tests with compound conditions connected by OR.
467: * @param suite a test suite.
468: */
469: static void addORTests(TestSuite suite) {
470: suite
471: .addTest(new QueryTestCase5(
472: "select * from people where name='Meier' or first_name='Tom'",
473: new Object[][] {
474: convert("Tom", "Meier", "01/01/1960",
475: 80, 1.80),
476: convert("John", "Meier", "01/01/1960",
477: 90, 1.90),
478: convert("Tom", "Taylor", "01/01/1980",
479: 70, 1.70)
480:
481: }));
482: suite
483: .addTest(new QueryTestCase5(
484: "select * from people where name='Meier' or first_name='John' or weight=100 ",
485: new Object[][] {
486: convert("Tom", "Meier", "01/01/1960",
487: 80, 1.80),
488: convert("John", "Meier", "01/01/1960",
489: 90, 1.90),
490: convert("Jim", "Smith", "01/01/1970",
491: 100, 1.90),
492: convert("John", "Meijer", "01/01/1980",
493: 80, 1.80) }));
494: }
495:
496: /**
497: * Add tests with negated conditions.
498: * @param suite a test suite.
499: */
500: static void addNOTTests(TestSuite suite) {
501:
502: suite
503: .addTest(new QueryTestCase5(
504: "select * from people where not (name='Meier')",
505: new Object[][] {
506: convert("Jim", "Smith", "01/01/1970",
507: 100, 1.90),
508: convert("Jack", "Smith", "01/01/1970",
509: 60, 1.60),
510: convert("Tom", "Taylor", "01/01/1980",
511: 70, 1.70),
512: convert("John", "Meijer", "01/01/1980",
513: 80, 1.80),
514: convert("Jim", "Mejer", "01/01/1990",
515: 50, 1.50) }));
516: suite
517: .addTest(new QueryTestCase5(
518: "select * from people where not name='Meier'",
519: new Object[][] {
520: convert("Jim", "Smith", "01/01/1970",
521: 100, 1.90),
522: convert("Jack", "Smith", "01/01/1970",
523: 60, 1.60),
524: convert("Tom", "Taylor", "01/01/1980",
525: 70, 1.70),
526: convert("John", "Meijer", "01/01/1980",
527: 80, 1.80),
528: convert("Jim", "Mejer", "01/01/1990",
529: 50, 1.50) }));
530: suite
531: .addTest(new QueryTestCase5(
532: "select * from people where not (name='Meier' and first_name='John')",
533: new Object[][] {
534: convert("Tom", "Meier", "01/01/1960",
535: 80, 1.80),
536: convert("Jim", "Smith", "01/01/1970",
537: 100, 1.90),
538: convert("Jack", "Smith", "01/01/1970",
539: 60, 1.60),
540: convert("Tom", "Taylor", "01/01/1980",
541: 70, 1.70),
542: convert("John", "Meijer", "01/01/1980",
543: 80, 1.80),
544: convert("Jim", "Mejer", "01/01/1990",
545: 50, 1.50) }));
546: }
547:
548: /**
549: * Add tests with complex (nested) conditions.
550: * @param suite a test suite.
551: */
552: static void addComplexConditionTests(TestSuite suite) {
553: suite
554: .addTest(new QueryTestCase5(
555: "select * from people where (name = 'Meier' and first_name='John') or (name = 'Smith' and first_name='Jim')",
556: new Object[][] {
557: convert("John", "Meier", "01/01/1960",
558: 90, 1.90),
559: convert("Jim", "Smith", "01/01/1970",
560: 100, 1.90) }));
561: suite
562: .addTest(new QueryTestCase5(
563: "select * from people where (name = 'Meier' or name='Smith') and (first_name = 'Jim' or first_name='John')",
564: new Object[][] {
565: convert("John", "Meier", "01/01/1960",
566: 90, 1.90),
567: convert("Jim", "Smith", "01/01/1970",
568: 100, 1.90) }));
569: suite
570: .addTest(new QueryTestCase5(
571: "select * from people where (name = 'Meier' or name='Smith') and not (first_name = 'Jim' or first_name='John')",
572: new Object[][] {
573: convert("Tom", "Meier", "01/01/1960",
574: 80, 1.80),
575: convert("Jack", "Smith", "01/01/1970",
576: 60, 1.60) }));
577: }
578:
579: /**
580: * Add tests with an order by clause.
581: * @param suite a test suite.
582: */
583: static void addOrderByTests(TestSuite suite) {
584:
585: suite
586: .addTest(new QueryTestCase5(
587: "select * from people order by first_name",
588: new Object[][] {
589: convert("Jack", "Smith", "01/01/1970",
590: 60, 1.60),
591: convert("Jim", "Smith", "01/01/1970",
592: 100, 1.90),
593: convert("Jim", "Mejer", "01/01/1990",
594: 50, 1.50),
595: convert("John", "Meier", "01/01/1960",
596: 90, 1.90),
597: convert("John", "Meijer", "01/01/1980",
598: 80, 1.80),
599: convert("Tom", "Meier", "01/01/1960",
600: 80, 1.80),
601: convert("Tom", "Taylor", "01/01/1980",
602: 70, 1.70) }));
603: suite
604: .addTest(new QueryTestCase5(
605: "select * from people order by first_name,name",
606: new Object[][] {
607: convert("Jack", "Smith", "01/01/1970",
608: 60, 1.60),
609: convert("Jim", "Mejer", "01/01/1990",
610: 50, 1.50),
611: convert("Jim", "Smith", "01/01/1970",
612: 100, 1.90),
613: convert("John", "Meier", "01/01/1960",
614: 90, 1.90),
615: convert("John", "Meijer", "01/01/1980",
616: 80, 1.80),
617: convert("Tom", "Meier", "01/01/1960",
618: 80, 1.80),
619: convert("Tom", "Taylor", "01/01/1980",
620: 70, 1.70) }));
621: suite
622: .addTest(new QueryTestCase5(
623: "select * from people order by first_name asc,name asc",
624: new Object[][] {
625: convert("Jack", "Smith", "01/01/1970",
626: 60, 1.60),
627: convert("Jim", "Mejer", "01/01/1990",
628: 50, 1.50),
629: convert("Jim", "Smith", "01/01/1970",
630: 100, 1.90),
631: convert("John", "Meier", "01/01/1960",
632: 90, 1.90),
633: convert("John", "Meijer", "01/01/1980",
634: 80, 1.80),
635: convert("Tom", "Meier", "01/01/1960",
636: 80, 1.80),
637: convert("Tom", "Taylor", "01/01/1980",
638: 70, 1.70) }));
639:
640: suite
641: .addTest(new QueryTestCase5(
642: "select * from people order by first_name asc,name desc",
643: new Object[][] {
644: convert("Jack", "Smith", "01/01/1970",
645: 60, 1.60),
646: convert("Jim", "Smith", "01/01/1970",
647: 100, 1.90),
648: convert("Jim", "Mejer", "01/01/1990",
649: 50, 1.50),
650: convert("John", "Meijer", "01/01/1980",
651: 80, 1.80),
652: convert("John", "Meier", "01/01/1960",
653: 90, 1.90),
654: convert("Tom", "Taylor", "01/01/1980",
655: 70, 1.70),
656: convert("Tom", "Meier", "01/01/1960",
657: 80, 1.80) }));
658:
659: suite
660: .addTest(new QueryTestCase5(
661: "select * from people where name='Meier' order by first_name",
662: new Object[][] {
663: convert("John", "Meier", "01/01/1960",
664: 90, 1.90),
665: convert("Tom", "Meier", "01/01/1960",
666: 80, 1.80) }));
667: }
668:
669: /**
670: * Add tests with a group by clause.
671: * @param suite a test suite.
672: */
673: static void addGroupByTests(TestSuite suite) {
674:
675: suite
676: .addTest(new QueryTestCase5(
677: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name",
678: new Object[][] {
679: convert("Tom", "Taylor", "01/01/1980",
680: 80, 1.80),
681: convert("John", "Meijer", "01/01/1980",
682: 90, 1.90),
683: convert("Jim", "Smith", "01/01/1990",
684: 100, 1.90),
685: convert("Jack", "Smith", "01/01/1970",
686: 60, 1.60) }, true));
687: suite
688: .addTest(new QueryTestCase5(
689: "select first_name,MIN(name),MIN(dob),MIN(weight),MIN(size) from people group by first_name",
690: new Object[][] {
691: convert("Tom", "Meier", "01/01/1960",
692: 70, 1.70),
693: convert("John", "Meier", "01/01/1960",
694: 80, 1.80),
695: convert("Jim", "Mejer", "01/01/1970",
696: 50, 1.50),
697: convert("Jack", "Smith", "01/01/1970",
698: 60, 1.60) }, true));
699: suite
700: .addTest(new QueryTestCase5(
701: "select first_name,MIN(name),MIN(dob),SUM(weight),SUM(size) from people group by first_name",
702: new Object[][] {
703: convert("Tom", "Meier", "01/01/1960",
704: 150, 3.50),
705: convert("John", "Meier", "01/01/1960",
706: 170, 3.70),
707: convert("Jim", "Mejer", "01/01/1970",
708: 150, 3.40),
709: convert("Jack", "Smith", "01/01/1970",
710: 60, 1.60) }, true));
711: suite
712: .addTest(new QueryTestCase5(
713: "select first_name,name,MIN(dob),MIN(weight),MIN(size) from people group by name,first_name",
714: new Object[][] {
715: convert("Tom", "Meier", "01/01/1960",
716: 80, 1.80),
717: convert("John", "Meier", "01/01/1960",
718: 90, 1.90),
719: convert("Jim", "Smith", "01/01/1970",
720: 100, 1.90),
721: convert("Jack", "Smith", "01/01/1970",
722: 60, 1.60),
723: convert("Tom", "Taylor", "01/01/1980",
724: 70, 1.70),
725: convert("John", "Meijer", "01/01/1980",
726: 80, 1.80),
727: convert("Jim", "Mejer", "01/01/1990",
728: 50, 1.50) }, true));
729: }
730:
731: /**
732: * Add tests with a group by and a having clause.
733: * @param suite a test suite.
734: */
735: static void addHavingTests(TestSuite suite) {
736:
737: suite
738: .addTest(new QueryTestCase5(
739: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name having MAX(size)=1.90",
740: new Object[][] {
741: convert("Jim", "Smith", "01/01/1990",
742: 100, 1.90),
743: convert("John", "Meijer", "01/01/1980",
744: 90, 1.90) }, true));
745: suite
746: .addTest(new QueryTestCase5(
747: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name having MAX(size)>1.80",
748: new Object[][] {
749: convert("Jim", "Smith", "01/01/1990",
750: 100, 1.90),
751: convert("John", "Meijer", "01/01/1980",
752: 90, 1.90) }, true));
753: suite
754: .addTest(new QueryTestCase5(
755: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name having MAX(size)>1.80 and first_name='Jim'",
756: new Object[][] { convert("Jim", "Smith",
757: "01/01/1990", 100, 1.90) }, true));
758: suite
759: .addTest(new QueryTestCase5(
760: "select first_name,MIN(name),MIN(dob),MIN(weight),MIN(size) from people group by first_name having MIN(name) like 'Me%'",
761: new Object[][] {
762: convert("Tom", "Meier", "01/01/1960",
763: 70, 1.70),
764: convert("John", "Meier", "01/01/1960",
765: 80, 1.80),
766: convert("Jim", "Mejer", "01/01/1970",
767: 50, 1.50) }, true));
768: suite
769: .addTest(new QueryTestCase5(
770: "select first_name,MIN(name),MIN(dob),SUM(weight),SUM(size) from people group by first_name having SUM(weight)>150",
771: new Object[][] { convert("John", "Meier",
772: "01/01/1960", 170, 3.70) }, true));
773: }
774:
775: /**
776: * Add tests with complex combinations of different clauses (e.g., group by and order by, where and having).
777: * @param suite a test suite.
778: */
779: static void addAdvancedTests(TestSuite suite) {
780: suite
781: .addTest(new QueryTestCase5(
782: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name having MAX(size)>1.80 order by max(name)",
783: new Object[][] {
784: convert("John", "Meijer", "01/01/1980",
785: 90, 1.90),
786: convert("Jim", "Smith", "01/01/1990",
787: 100, 1.90) }, false));
788: suite
789: .addTest(new QueryTestCase5(
790: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name having MAX(size)>1.80 order by max(name) desc",
791: new Object[][] {
792: convert("Jim", "Smith", "01/01/1990",
793: 100, 1.90),
794: convert("John", "Meijer", "01/01/1980",
795: 90, 1.90) }, false));
796: suite
797: .addTest(new QueryTestCase5(
798: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people group by first_name having MAX(size)>1.80 order by first_name",
799: new Object[][] {
800: convert("Jim", "Smith", "01/01/1990",
801: 100, 1.90),
802: convert("John", "Meijer", "01/01/1980",
803: 90, 1.90) }, false));
804: suite
805: .addTest(new QueryTestCase5(
806: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people where name!='Taylor' group by first_name",
807: new Object[][] {
808: convert("Tom", "Meier", "01/01/1960",
809: 80, 1.80),
810: convert("John", "Meijer", "01/01/1980",
811: 90, 1.90),
812: convert("Jim", "Smith", "01/01/1990",
813: 100, 1.90),
814: convert("Jack", "Smith", "01/01/1970",
815: 60, 1.60) }, true));
816: suite
817: .addTest(new QueryTestCase5(
818: "select first_name,MAX(name),MAX(dob),MAX(weight),MAX(size) from people where name!='Taylor' group by first_name having MAX(name)='Smith' order by first_name",
819: new Object[][] {
820: convert("Jim", "Smith", "01/01/1990",
821: 100, 1.90),
822: convert("Jack", "Smith", "01/01/1970",
823: 60, 1.60) }, true));
824: }
825:
826: /**
827: * Add tests for counting.
828: * @param suite a test suite.
829: */
830: static void addCountTests(TestSuite suite) {
831: suite
832: .addTest(new QueryTestCase5(
833: "select first_name,name,dob,count(*),size from people",
834: new Object[][] {
835: convert("Tom", "Meier", "01/01/1960",
836: 7, 1.80),
837: convert("John", "Meier", "01/01/1960",
838: 7, 1.90),
839: convert("Jim", "Smith", "01/01/1970",
840: 7, 1.90),
841: convert("Jack", "Smith", "01/01/1970",
842: 7, 1.60),
843: convert("Tom", "Taylor", "01/01/1980",
844: 7, 1.70),
845: convert("John", "Meijer", "01/01/1980",
846: 7, 1.80),
847: convert("Jim", "Mejer", "01/01/1990",
848: 7, 1.50) }));
849: suite
850: .addTest(new QueryTestCase5(
851: "select first_name,MAX(name),MAX(dob),COUNT(*),MAX(size) from people group by first_name",
852: new Object[][] {
853: convert("Tom", "Taylor", "01/01/1980",
854: 2, 1.80),
855: convert("John", "Meijer", "01/01/1980",
856: 2, 1.90),
857: convert("Jim", "Smith", "01/01/1990",
858: 2, 1.90),
859: convert("Jack", "Smith", "01/01/1970",
860: 1, 1.60) }, true));
861: suite.addTest(new QueryTestCase6("select COUNT(*) from people",
862: 7));
863: suite.addTest(new QueryTestCase6(
864: "select COUNT(*) from people where name='Meier'", 2));
865:
866: }
867:
868: }
|