KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > hsqldb > test > TestSubselect


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */

30
31
32 package org.hsqldb.test;
33
34 import java.io.IOException JavaDoc;
35 import java.sql.Connection JavaDoc;
36 import java.sql.DriverManager JavaDoc;
37 import java.sql.ResultSet JavaDoc;
38 import java.sql.SQLException JavaDoc;
39 import java.sql.Statement JavaDoc;
40
41 import junit.framework.TestCase;
42
43 /**
44  * Test cases for HSQL subselects.
45  *
46  * @author David Moles Apr 30, 2002
47  */

48
49 // fredt@users - modified to remove dependecy on DBUnit
50
public class TestSubselect extends TestCase {
51
52     //------------------------------------------------------------
53
// Class variables
54
//------------------------------------------------------------
55
private static final String JavaDoc databaseDriver = "org.hsqldb.jdbcDriver";
56     private static final String JavaDoc databaseURL =
57         "jdbc:hsqldb:/hsql/test/subselect";
58     private static final String JavaDoc databaseUser = "sa";
59     private static final String JavaDoc databasePassword = "";
60
61     //------------------------------------------------------------
62
// Instance variables
63
//------------------------------------------------------------
64
private Connection JavaDoc jdbcConnection;
65
66     //------------------------------------------------------------
67
// Constructors
68
//------------------------------------------------------------
69

70     /**
71      * Constructs a new SubselectTest.
72      */

73     public TestSubselect(String JavaDoc s) {
74         super(s);
75     }
76
77     //------------------------------------------------------------
78
// Class methods
79
//------------------------------------------------------------
80
protected static Connection JavaDoc getJDBCConnection() throws SQLException JavaDoc {
81         return DriverManager.getConnection(databaseURL, databaseUser,
82                                            databasePassword);
83     }
84
85     protected void setUp() throws Exception JavaDoc {
86
87         TestSelf.deleteDatabase("/hsql/test/subselect");
88         Class.forName(databaseDriver);
89
90         jdbcConnection = getJDBCConnection();
91
92         createDataset();
93     }
94
95     protected void tearDown() throws Exception JavaDoc {
96
97         super.tearDown();
98         jdbcConnection.close();
99
100         jdbcConnection = null;
101     }
102
103     void createDataset() throws SQLException JavaDoc {
104
105         Statement JavaDoc statement = jdbcConnection.createStatement();
106
107         statement.execute("drop table colors if exists; "
108                           + "drop table sizes if exists; "
109                           + "drop table fruits if exists; "
110                           + "drop table trees if exists; ");
111         statement.execute(
112             "create table colors(id int, val char); "
113             + "insert into colors values(1,'red'); "
114             + "insert into colors values(2,'green'); "
115             + "insert into colors values(3,'orange'); "
116             + "insert into colors values(4,'indigo'); "
117             + "create table sizes(id int, val char); "
118             + "insert into sizes values(1,'small'); "
119             + "insert into sizes values(2,'medium'); "
120             + "insert into sizes values(3,'large'); "
121             + "insert into sizes values(4,'odd'); "
122             + "create table fruits(id int, name char, color_id int); "
123             + "insert into fruits values(1, 'golden delicious',2); "
124             + "insert into fruits values(2, 'macintosh',1); "
125             + "insert into fruits values(3, 'red delicious',1); "
126             + "insert into fruits values(4, 'granny smith',2); "
127             + "insert into fruits values(5, 'tangerine',4); "
128             + "create table trees(id int, name char, fruit_id int, size_id int); "
129             + "insert into trees values(1, 'small golden delicious tree',1,1); "
130             + "insert into trees values(2, 'large macintosh tree',2,3); "
131             + "insert into trees values(3, 'large red delicious tree',3,3); "
132             + "insert into trees values(4, 'small red delicious tree',3,1); "
133             + "insert into trees values(5, 'medium granny smith tree',4,2); ");
134         statement.close();
135     }
136
137     //------------------------------------------------------------
138
// Helper methods
139
//------------------------------------------------------------
140
private static void compareResults(String JavaDoc sql, String JavaDoc[] expected,
141                                        Connection JavaDoc jdbcConnection)
142                                        throws SQLException JavaDoc {
143
144         Statement JavaDoc statement = jdbcConnection.createStatement();
145         ResultSet JavaDoc results = statement.executeQuery(sql);
146         int rowCount = 0;
147
148         while (results.next()) {
149             assertTrue("Statement <" + sql + "> returned too many rows.",
150                        (rowCount < expected.length));
151             assertEquals("Statement <" + sql + "> returned wrong value.",
152                          expected[rowCount], results.getString(1));
153
154             rowCount++;
155         }
156
157         assertEquals("Statement <" + sql
158                      + "> returned wrong number of rows.", expected.length,
159                          rowCount);
160     }
161
162     //------------------------------------------------------------
163
// Test methods
164
//------------------------------------------------------------
165

166     /**
167      * This test is basically a sanity check of the data set.
168      */

169     public void testSimpleJoin() throws SQLException JavaDoc {
170
171         String JavaDoc sql =
172             "select trees.id, trees.name, sizes.val, fruits.name, colors.val"
173             + " from trees, sizes, fruits, colors"
174             + " where trees.size_id = sizes.id"
175             + " and trees.fruit_id = fruits.id"
176             + " and fruits.color_id = colors.id" + " order by 1";
177         int expectedRows = 5;
178         String JavaDoc[] expectedTrees = new String JavaDoc[] {
179             "small golden delicious tree", "large macintosh tree",
180             "large red delicious tree", "small red delicious tree",
181             "medium granny smith tree"
182         };
183         String JavaDoc[] expectedSizes = new String JavaDoc[] {
184             "small", "large", "large", "small", "medium"
185         };
186         String JavaDoc[] expectedFruits = new String JavaDoc[] {
187             "golden delicious", "macintosh", "red delicious", "red delicious",
188             "granny smith"
189         };
190         String JavaDoc[] expectedColors = new String JavaDoc[] {
191             "green", "red", "red", "red", "green"
192         };
193         Statement JavaDoc statement = jdbcConnection.createStatement();
194         ResultSet JavaDoc results = statement.executeQuery(sql);
195         String JavaDoc[] trees = new String JavaDoc[expectedRows];
196         String JavaDoc[] fruits = new String JavaDoc[expectedRows];
197         String JavaDoc[] sizes = new String JavaDoc[expectedRows];
198         String JavaDoc[] colors = new String JavaDoc[expectedRows];
199         int rowCount = 0;
200
201         while (results.next()) {
202             assertTrue("Statement <" + sql + "> returned too many rows.",
203                        (rowCount <= expectedRows));
204             assertEquals("Statement <" + sql
205                          + "> returned rows in wrong order.", (1 + rowCount),
206                              results.getInt(1));
207             assertEquals("Statement <" + sql + "> returned wrong value.",
208                          expectedTrees[rowCount], results.getString(2));
209             assertEquals("Statement <" + sql + "> returned wrong value.",
210                          expectedSizes[rowCount], results.getString(3));
211             assertEquals("Statement <" + sql + "> returned wrong value.",
212                          expectedFruits[rowCount], results.getString(4));
213             assertEquals("Statement <" + sql + "> returned wrong value.",
214                          expectedColors[rowCount], results.getString(5));
215
216             rowCount++;
217         }
218
219         assertEquals("Statement <" + sql
220                      + "> returned wrong number of rows.", expectedRows,
221                          rowCount);
222     }
223
224     /**
225      * Inner select with where clause in outer select having column with same name as where clause in inner select
226      */

227     public void testWhereClausesColliding() throws SQLException JavaDoc {
228
229         String JavaDoc sql =
230             "select name from fruits where id in (select fruit_id from trees where id < 3) order by name";
231         String JavaDoc[] expected = new String JavaDoc[] {
232             "golden delicious", "macintosh"
233         };
234
235         compareResults(sql, expected, jdbcConnection);
236     }
237
238     /**
239      * As above, with table aliases.
240      */

241     public void testWhereClausesCollidingWithAliases() throws SQLException JavaDoc {
242
243         String JavaDoc sql =
244             "select a.name from fruits a where a.id in (select b.fruit_id from trees b where b.id < 3) order by name";
245         String JavaDoc[] expected = new String JavaDoc[] {
246             "golden delicious", "macintosh"
247         };
248
249         compareResults(sql, expected, jdbcConnection);
250     }
251
252     /**
253      * Inner select with two tables having columns with the same name, one of which is referred to in the
254      * subselect, the other of which is not used in the query (both FRUITS and TREES have NAME column,
255      * but we're only selecting FRUITS.NAME and we're not referring to TREES.NAME at all).
256      */

257     public void testHiddenCollision() throws SQLException JavaDoc {
258
259         String JavaDoc sql =
260             "select name from fruits where id in (select fruit_id from trees) order by name";
261         String JavaDoc[] expected = new String JavaDoc[] {
262             "golden delicious", "granny smith", "macintosh", "red delicious"
263         };
264
265         compareResults(sql, expected, jdbcConnection);
266     }
267
268     /**
269      * As above, with table aliases.
270      */

271     public void testHiddenCollisionWithAliases() throws SQLException JavaDoc {
272
273         String JavaDoc sql =
274             "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name";
275         String JavaDoc[] expected = new String JavaDoc[] {
276             "golden delicious", "granny smith", "macintosh", "red delicious"
277         };
278
279         compareResults(sql, expected, jdbcConnection);
280     }
281
282     /**
283      * Inner select with where clause in outer select having column with same name as select clause in inner select
284      */

285     public void testWhereSelectColliding() throws SQLException JavaDoc {
286
287         // Yes, this is a nonsensical query
288
String JavaDoc sql =
289             "select val from colors where id in (select id from trees where fruit_id = 3) order by val";
290         String JavaDoc[] expected = new String JavaDoc[] {
291             "indigo", "orange"
292         };
293
294         compareResults(sql, expected, jdbcConnection);
295     }
296
297     /**
298      * As above, with aliases.
299      */

300     public void testWhereSelectCollidingWithAliases() throws SQLException JavaDoc {
301
302         // Yes, this is a nonsensical query
303
String JavaDoc sql =
304             "select a.val from colors a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.val";
305         String JavaDoc[] expected = new String JavaDoc[] {
306             "indigo", "orange"
307         };
308
309         compareResults(sql, expected, jdbcConnection);
310     }
311
312     /**
313      * Inner select involving same table
314      */

315     public void testSameTable() throws SQLException JavaDoc {
316
317         String JavaDoc sql =
318             "select name from trees where id in (select id from trees where fruit_id = 3) order by name";
319         String JavaDoc[] expected = new String JavaDoc[] {
320             "large red delicious tree", "small red delicious tree"
321         };
322
323         compareResults(sql, expected, jdbcConnection);
324     }
325
326     /**
327      * As above with aliases.
328      */

329     public void testSameTableWithAliases() throws SQLException JavaDoc {
330
331         String JavaDoc sql =
332             "select a.name from trees a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.name";
333         String JavaDoc[] expected = new String JavaDoc[] {
334             "large red delicious tree", "small red delicious tree"
335         };
336
337         compareResults(sql, expected, jdbcConnection);
338     }
339
340     /**
341      * Inner select involving same table as one of two joined tables in outer select
342      */

343     public void testSameTableWithJoin() throws SQLException JavaDoc {
344
345         String JavaDoc sql =
346             "select sizes.val from trees, sizes where sizes.id = trees.size_id and trees.id in (select id from trees where fruit_id = 3) order by sizes.val";
347         String JavaDoc[] expected = new String JavaDoc[] {
348             "large", "small"
349         };
350
351         compareResults(sql, expected, jdbcConnection);
352     }
353
354     /**
355      * Tests two subselects, anded.
356      */

357     public void testAndedSubselects() throws SQLException JavaDoc {
358
359         String JavaDoc sql =
360             "select name from trees where size_id in (select id from sizes where val = 'large') and fruit_id in (select id from fruits where color_id = 1) order by name";
361         String JavaDoc[] expected = new String JavaDoc[] {
362             "large macintosh tree", "large red delicious tree"
363         };
364
365         compareResults(sql, expected, jdbcConnection);
366     }
367
368     /**
369      * Test nested subselects.
370      */

371     public void testNestedSubselects() throws SQLException JavaDoc {
372
373         String JavaDoc sql =
374             "select name from trees where fruit_id in (select id from fruits where color_id in (select id from colors where val = 'red')) order by name";
375         String JavaDoc[] expected = new String JavaDoc[] {
376             "large macintosh tree", "large red delicious tree",
377             "small red delicious tree"
378         };
379
380         compareResults(sql, expected, jdbcConnection);
381     }
382
383     /**
384      * Inner select with "not in" in outer select where clause.
385      */

386     public void testNotIn() throws SQLException JavaDoc {
387
388         String JavaDoc sql =
389             "select name from fruits where id not in (select fruit_id from trees) order by name";
390         String JavaDoc[] expected = new String JavaDoc[]{ "tangerine" };
391
392         compareResults(sql, expected, jdbcConnection);
393     }
394
395     /**
396      * Inner select with "not in" in outer select where clause and same table in inner select where clause.
397      */

398     public void testNotInSameTableAndColumn() throws SQLException JavaDoc {
399
400         String JavaDoc sql =
401             "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name";
402         String JavaDoc[] expected = new String JavaDoc[] {
403             "macintosh", "red delicious"
404         };
405
406         compareResults(sql, expected, jdbcConnection);
407     }
408
409     /**
410      * Inner select reusing alias names from outer select, but using them for different tables
411      */

412     public void testAliasScope() throws SQLException JavaDoc {
413
414         String JavaDoc sql =
415             "select a.val, b.name from sizes a, trees b where a.id = b.size_id and b.id in (select a.id from trees a, fruits b where a.fruit_id = b.id and b.name='red delicious') order by a.val";
416         String JavaDoc[] expectedSizes = new String JavaDoc[] {
417             "large", "small"
418         };
419         String JavaDoc[] expectedTrees = new String JavaDoc[] {
420             "large red delicious tree", "small red delicious tree"
421         };
422
423         assertEquals(
424             "Programmer error: expected arrays should be of equal length.",
425             expectedSizes.length, expectedTrees.length);
426
427         Statement JavaDoc statement = jdbcConnection.createStatement();
428         ResultSet JavaDoc results = statement.executeQuery(sql);
429         int rowCount = 0;
430
431         while (results.next()) {
432             assertTrue("Statement <" + sql + "> returned too many rows.",
433                        (rowCount < expectedSizes.length));
434             assertEquals("Statement <" + sql + "> returned wrong value.",
435                          expectedSizes[rowCount], results.getString(1));
436             assertEquals("Statement <" + sql + "> returned wrong value.",
437                          expectedTrees[rowCount], results.getString(2));
438
439             rowCount++;
440         }
441
442         assertEquals(
443             "Statement <" + sql + "> returned wrong number of rows.",
444             expectedSizes.length, rowCount);
445     }
446
447     //------------------------------------------------------------
448
// Main program
449
//------------------------------------------------------------
450
public static void main(String JavaDoc[] args) throws IOException JavaDoc {
451         junit.swingui.TestRunner.run(TestSubselect.class);
452     }
453 }
454
Popular Tags