001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.ResultSet;
034: import java.sql.ResultSetMetaData;
035: import java.sql.SQLException;
036:
037: import junit.framework.AssertionFailedError;
038:
039: import org.hsqldb.Trace;
040:
041: /**
042: * is a test which verifies the functionality of replacing asterisks in VIEW statements with column
043: * lists.
044: *
045: * During investigating http://www.openoffice.org/issues/show_bug.cgi?id=78296 (an issue raised
046: * in OpenOffice.org, which integrates HSQLDB), it rose that views did not behave to the standard
047: * in that they don't always reflect the table column list at the time of creation of the view.
048: *
049: * This was changed so that when you CREATE a VIEW, then any asterisks in the constituting
050: * statement are replaced with their column lists.
051: *
052: * This test verifies this functionality in a number of different flavours.
053: *
054: * @author frank.schoenheit@sun.com
055: */
056: public class TestViewAsterisks extends TestBase {
057:
058: java.sql.Statement m_statement;
059: java.sql.Connection m_connection;
060:
061: /** Creates a new instance of TestViewAsterisks */
062: public TestViewAsterisks(String testName) {
063: super (testName, null, false);
064: }
065:
066: /**
067: * creates the database tables needed for the test
068: */
069: private void setupDatabase() {
070:
071: try {
072: m_connection = newConnection();
073: m_statement = m_connection.createStatement();
074:
075: executeStatement("DROP TABLE IF EXISTS ABC CASCADE");
076: executeStatement("DROP TABLE IF EXISTS TABLE_A CASCADE");
077: executeStatement("DROP TABLE IF EXISTS TABLE_B CASCADE");
078: executeStatement("CREATE TABLE ABC (ID INTEGER NOT NULL PRIMARY KEY, A VARCHAR(50), B VARCHAR(50), C VARCHAR(50))");
079: executeStatement("INSERT INTO ABC VALUES (1, 'a', 'b', 'c')");
080: executeStatement("INSERT INTO ABC VALUES (2, 'd', 'e', 'f')");
081: executeStatement("CREATE TABLE TABLE_A (ID_A INTEGER NOT NULL PRIMARY KEY, NAME_A VARCHAR(50))");
082: executeStatement("INSERT INTO TABLE_A VALUES (1, 'first A')");
083: executeStatement("INSERT INTO TABLE_A VALUES (2, 'second A')");
084: executeStatement("CREATE TABLE TABLE_B (ID_B INTEGER NOT NULL PRIMARY KEY, NAME_B VARCHAR(50))");
085: executeStatement("INSERT INTO TABLE_B VALUES (1, 'first B')");
086: executeStatement("INSERT INTO TABLE_B VALUES (2, 'second B')");
087: } catch (SQLException ex) {
088: fail(ex.toString());
089: }
090: }
091:
092: public void setUp() {
093: super .setUp();
094: setupDatabase();
095: }
096:
097: protected void tearDown() {
098: super .tearDown();
099: executeStatement("SHUTDOWN");
100: }
101:
102: /**
103: * executes a given m_statement
104: *
105: * <p>Basically, this method calls <code>m_statement.execute(sql)</code>,
106: * but wraps any <code>SQLException</code>s into a JUnit error.
107: */
108: private void executeStatement(String sql) {
109: executeStatement(sql, 0);
110: }
111:
112: private void executeStatement(String sql, int expectedVendorCode) {
113:
114: try {
115: m_statement.execute(sql);
116: assertTrue("executing\n " + sql
117: + "\nwas expected to fail, but it didn't",
118: expectedVendorCode == 0);
119: } catch (SQLException ex) {
120: if (expectedVendorCode == 0) {
121: fail(ex.toString());
122: }
123:
124: assertEquals("executing\n " + sql
125: + "\ndid not result in the expected error",
126: expectedVendorCode, -ex.getErrorCode());
127: }
128: }
129:
130: /**
131: * creates a view with the given name, the given constituting statement, and an optional column list
132: *
133: * @param viewName
134: * specifies the name of the view to create
135: * @param columnList
136: * list of names of the columns of the view, will be specified in the CREATE VIEW statement. Might be <code>null</code>.
137: * @param viewStatement
138: * the statement of the view
139: */
140: private void createView(String viewName, String[] columnList,
141: String viewStatement) throws SQLException {
142:
143: StringBuffer colList = new StringBuffer();
144:
145: if (columnList != null) {
146: colList.append(" (");
147:
148: for (int i = 0; i < columnList.length; ++i) {
149: colList.append('"').append(columnList[i]).append('"');
150:
151: if (i < columnList.length - 1) {
152: colList.append(',');
153: }
154: }
155:
156: colList.append(")");
157: }
158:
159: executeStatement("CREATE VIEW " + viewName + colList.toString()
160: + " AS " + viewStatement);
161:
162: if (columnList != null) {
163: ensureTableColumns(viewName, columnList);
164: }
165: }
166:
167: /**
168: * retrieves the statement which defines a given view
169: */
170: private String getViewStatement(String viewName)
171: throws SQLException {
172:
173: ResultSet res = m_statement
174: .executeQuery("SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.SYSTEM_VIEWS WHERE TABLE_NAME = '"
175: + viewName + "'");
176:
177: res.next();
178:
179: String statement = res.getString(1);
180:
181: return statement;
182: }
183:
184: /**
185: * ensures two tables (or views, that is) have the same content
186: */
187: private void ensureEqualContent(String tableNameLHS,
188: String tableNameRHS) throws SQLException {
189:
190: ResultSet lhs = m_statement.executeQuery("SELECT * FROM \""
191: + tableNameLHS + "\"");
192: ResultSet rhs = m_statement.executeQuery("SELECT * FROM \""
193: + tableNameRHS + "\"");
194: ResultSetMetaData meta = lhs.getMetaData();
195:
196: while (lhs.next() && rhs.next()) {
197: for (int col = 1; col <= meta.getColumnCount(); ++col) {
198: assertEquals("table content does not match: cp. "
199: + tableNameLHS + "-" + tableNameRHS + ", row "
200: + lhs.getRow() + ", col " + col, lhs
201: .getObject(col), rhs.getObject(col));
202: }
203: }
204:
205: // lhs should be after last, rhs still on last
206: assertTrue("row count does not match: " + tableNameLHS + "-"
207: + tableNameRHS, lhs.isAfterLast() && rhs.isLast());
208: }
209:
210: /**
211: * ensures the content of a given table matches a given object array's content
212: */
213: private void ensureTableContent(String tableName,
214: Object[][] tableData) throws SQLException {
215:
216: ResultSet lhs = m_statement.executeQuery("SELECT * FROM \""
217: + tableName + "\"");
218: ResultSetMetaData meta = lhs.getMetaData();
219: int colCount = meta.getColumnCount();
220:
221: while (lhs.next()) {
222: int row = lhs.getRow();
223:
224: assertEquals(colCount, tableData[row - 1].length);
225:
226: for (int col = 1; col <= colCount; ++col) {
227: assertEquals("unexpected table content in " + tableName
228: + " (row " + row + ", col " + col + ")",
229: tableData[row - 1][col - 1], lhs.getObject(col));
230: }
231: }
232: }
233:
234: /**
235: * creates a view with a given name and statement, ensures that it's statement is translated as expected, and ensures
236: * that the content of the view is as expected
237: *
238: * @param viewName
239: * the name of the to-be-created view
240: * @param columnNames
241: * the names of the columns of the view, as to be specified in the CREATE VIEW statement. Might be null,
242: * in this case the view will be created without an explicit column list
243: * @param viewStatement
244: * the statement of the to-be-created view
245: * @param expectedTranslatedStatement
246: * the expected statement of the view, after it has been implicitly translated by HSQL. If the actual
247: * statement after creation does not match this expected statement, this is a failure condition which
248: * results in a AssertionFailedError being thrown.
249: * @param expectedContent
250: * the expected content of the view. If this is <code>null</code>, it is ignored. Else, if it is a
251: * string, it is interpreted as name of the table which must have the same content as a view. If
252: * it's no string either, it must be a two-dimensional Object array specifying the expected content.
253: */
254: private void checkViewTranslationAndContent(String viewName,
255: String[] columnList, String viewStatement,
256: String expectedTranslatedStatement, Object expectedContent)
257: throws SQLException {
258:
259: createView(viewName, columnList, viewStatement);
260:
261: String actualTranslatedStatement = getViewStatement(viewName);
262:
263: if (!actualTranslatedStatement
264: .equals(expectedTranslatedStatement)) {
265: StringBuffer message = new StringBuffer();
266:
267: message.append(viewName).append(
268: "'s statement not translated as expected\n");
269: message.append("original statement:\n ").append(
270: viewStatement).append('\n');
271: message.append("expected translated statement:\n ")
272: .append(expectedTranslatedStatement).append('\n');
273: message.append("actual translated statement:\n ").append(
274: actualTranslatedStatement).append('\n');
275:
276: throw new AssertionFailedError(message.toString());
277: }
278:
279: if (expectedContent != null) {
280: if (expectedContent.getClass().equals(String.class)) {
281: ensureEqualContent(viewName, (String) expectedContent);
282: } else {
283: ensureTableContent(viewName,
284: (Object[][]) expectedContent);
285: }
286: }
287: }
288:
289: /**
290: * ensures that a given table has columns with a given name
291: */
292: private void ensureTableColumns(String tableName,
293: String[] columnNames) throws SQLException {
294:
295: ResultSet res = m_connection.getMetaData().getColumns(null,
296: null, tableName, "%");
297:
298: while (res.next()) {
299: assertEquals("unexpected column name in table \""
300: + tableName + "\" at position "
301: + (res.getRow() - 1), res.getString("COLUMN_NAME"),
302: columnNames[res.getRow() - 1]);
303: }
304:
305: res.previous();
306: assertEquals("not enough columns in table \"" + tableName
307: + "\"", columnNames.length, res.getRow());
308: }
309:
310: /**
311: * checks views selecting an asterisk from a table, in different flavours
312: */
313: private void checkSimpleViews() throws SQLException {
314:
315: // ................................................................
316: // SELECT *
317: checkViewTranslationAndContent(
318: "S1",
319: null,
320: "SELECT * FROM ABC",
321: "SELECT \"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC",
322: "ABC");
323:
324: // adding a column to "ABC" should succeed, and still leave the view with the columns
325: // before the addition
326: executeStatement("ALTER TABLE ABC ADD COLUMN D VARCHAR(50)");
327: ensureTableColumns("ABC", new String[] { "ID", "A", "B", "C",
328: "D" });
329: ensureTableColumns("S1", new String[] { "ID", "A", "B", "C" });
330:
331: // dropping the column which is not part of the view should be possible
332: executeStatement("ALTER TABLE ABC DROP COLUMN D");
333:
334: // dropping another column which *is* part of the view shouldn't
335: executeStatement("ALTER TABLE ABC DROP COLUMN C",
336: Trace.COLUMN_IS_REFERENCED);
337:
338: // finally, dropping the column with CASCADE should work, and also drop the view
339: //executeStatement("ALTER TABLE ABC DROP COLUMN C CASCADE");
340: // DROP COLUMN c CASCADE not implemented, yet
341: // ................................................................
342: // same as S1, but this time add a LIMIT clause to the statement
343: checkViewTranslationAndContent(
344: "S2",
345: null,
346: "SELECT LIMIT 0 2 * FROM ABC ORDER BY ID",
347: "SELECT LIMIT 0 2 \"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC ORDER BY ID",
348: "ABC");
349:
350: // ................................................................
351: // same as S1, but this time add a TOP clause to the statement
352: checkViewTranslationAndContent(
353: "S3",
354: null,
355: "SELECT TOP 2 * FROM ABC ORDER BY ID",
356: "SELECT TOP 2 \"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC ORDER BY ID",
357: "ABC");
358:
359: // ................................................................
360: // same as S1, but this time add a DISTINCT clause to the statement
361: checkViewTranslationAndContent(
362: "S4",
363: null,
364: "SELECT DISTINCT * FROM ABC",
365: "SELECT DISTINCT\"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC",
366: "ABC");
367:
368: // ................................................................
369: // same as S1, but this time qualifying the asterisk
370: checkViewTranslationAndContent(
371: "S5",
372: null,
373: "SELECT ABC.* FROM ABC",
374: "SELECT \"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC",
375: "ABC");
376:
377: // ................................................................
378: // same as S5, but this time also giving the table an alias
379: checkViewTranslationAndContent(
380: "S6",
381: null,
382: "SELECT \"A\".* FROM ABC AS A",
383: "SELECT \"A\".ID, \"A\".A, \"A\".B, \"A\".C FROM ABC AS A",
384: "ABC");
385:
386: // ................................................................
387: // same as S1, but bracket the SELECT definition
388: checkViewTranslationAndContent(
389: "S7",
390: null,
391: "( SELECT * FROM ABC )",
392: "( SELECT \"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC )",
393: "ABC");
394: }
395:
396: /**
397: * checks views selecting an asterisk plus existing columns
398: */
399: private void checkAsterisksCombined() throws SQLException {
400:
401: // ................................................................
402: checkViewTranslationAndContent(
403: "C1",
404: null,
405: "SELECT *, A AS \"a2\" FROM ABC",
406: "SELECT \"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C, A AS \"a2\" FROM ABC",
407: new Object[][] {
408: new Object[] { new Integer(1), "a", "b", "c",
409: "a" },
410: new Object[] { new Integer(2), "d", "e", "f",
411: "d" } });
412:
413: // ................................................................
414: checkViewTranslationAndContent(
415: "C2",
416: null,
417: "SELECT B AS \"b2\", * FROM ABC",
418: "SELECT B AS \"b2\",\"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC",
419: new Object[][] {
420: new Object[] { "b", new Integer(1), "a", "b",
421: "c" },
422: new Object[] { "e", new Integer(2), "d", "e",
423: "f" } });
424: }
425:
426: /**
427: * checks views selecting asterisks from multiple tables
428: */
429: private void checkMultipleTables() throws SQLException {
430:
431: // ................................................................
432: checkViewTranslationAndContent(
433: "M1",
434: null,
435: "SELECT * FROM TABLE_A, TABLE_B",
436: "SELECT \"TABLE_A\".ID_A, \"TABLE_A\".NAME_A, \"TABLE_B\".ID_B, \"TABLE_B\".NAME_B FROM TABLE_A, TABLE_B",
437: new Object[][] {
438: new Object[] { new Integer(1), "first A",
439: new Integer(1), "first B" },
440: new Object[] { new Integer(1), "first A",
441: new Integer(2), "second B" },
442: new Object[] { new Integer(2), "second A",
443: new Integer(1), "first B" },
444: new Object[] { new Integer(2), "second A",
445: new Integer(2), "second B" } });
446:
447: // ................................................................
448: checkViewTranslationAndContent(
449: "M2",
450: null,
451: "SELECT TABLE_B.*, TABLE_A.* FROM TABLE_A, TABLE_B",
452: "SELECT \"TABLE_B\".ID_B, \"TABLE_B\".NAME_B,\"TABLE_A\".ID_A, \"TABLE_A\".NAME_A FROM TABLE_A, TABLE_B",
453: new Object[][] {
454: new Object[] { new Integer(1), "first B",
455: new Integer(1), "first A" },
456: new Object[] { new Integer(2), "second B",
457: new Integer(1), "first A" },
458: new Object[] { new Integer(1), "first B",
459: new Integer(2), "second A" },
460: new Object[] { new Integer(2), "second B",
461: new Integer(2), "second A" } });
462:
463: // ................................................................
464: checkViewTranslationAndContent(
465: "M3",
466: null,
467: "SELECT TABLE_A.* FROM TABLE_A, TABLE_B",
468: "SELECT \"TABLE_A\".ID_A, \"TABLE_A\".NAME_A FROM TABLE_A, TABLE_B",
469: new Object[][] {
470: new Object[] { new Integer(1), "first A" },
471: new Object[] { new Integer(1), "first A" },
472: new Object[] { new Integer(2), "second A" },
473: new Object[] { new Integer(2), "second A" } });
474: }
475:
476: /**
477: * checks views selecting from sub selects
478: */
479: private void checkSubSelects() throws SQLException {
480:
481: // ................................................................
482: checkViewTranslationAndContent(
483: "Q1",
484: null,
485: "SELECT * FROM ( SELECT * FROM ABC )",
486: "SELECT ID, A, B, C FROM ( SELECT \"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC )",
487: null);
488:
489: // ................................................................
490: checkViewTranslationAndContent(
491: "Q2",
492: null,
493: "SELECT * FROM ( SELECT * FROM TABLE_A ), ( SELECT * FROM TABLE_B )",
494: "SELECT ID_A, NAME_A, ID_B, NAME_B FROM ( SELECT \"TABLE_A\".ID_A, \"TABLE_A\".NAME_A FROM TABLE_A ), ( SELECT \"TABLE_B\".ID_B, \"TABLE_B\".NAME_B FROM TABLE_B )",
495: null);
496:
497: // ................................................................
498: checkViewTranslationAndContent(
499: "Q3",
500: null,
501: "SELECT A.* FROM ( SELECT * FROM TABLE_A ) AS A",
502: "SELECT \"A\".ID_A, \"A\".NAME_A FROM ( SELECT \"TABLE_A\".ID_A, \"TABLE_A\".NAME_A FROM TABLE_A ) AS A",
503: null);
504:
505: // ................................................................
506: checkViewTranslationAndContent(
507: "Q4",
508: null,
509: "SELECT A.*, B.* FROM ( SELECT * FROM TABLE_A ) AS A, ( SELECT * FROM TABLE_B ) AS B",
510: "SELECT \"A\".ID_A, \"A\".NAME_A,\"B\".ID_B, \"B\".NAME_B FROM ( SELECT \"TABLE_A\".ID_A, \"TABLE_A\".NAME_A FROM TABLE_A ) AS A, ( SELECT \"TABLE_B\".ID_B, \"TABLE_B\".NAME_B FROM TABLE_B ) AS B",
511: null);
512: }
513:
514: /**
515: * checks views which are defined using a column list
516: */
517: private void checkColumnLists() throws SQLException {
518:
519: // just to ensure the column count handling is as expected, else below tests might be useless
520: executeStatement(
521: "CREATE VIEW IMPOSSIBLE (\"A\") AS SELECT * FROM ABC",
522: Trace.COLUMN_COUNT_DOES_NOT_MATCH);
523:
524: // ................................................................
525: // not that it should make any difference to S1, but who knows
526: checkViewTranslationAndContent(
527: "L1",
528: new String[] { "C1", "C2", "C3", "C4" },
529: "SELECT * FROM ABC",
530: "SELECT \"ABC\".ID, \"ABC\".A, \"ABC\".B, \"ABC\".C FROM ABC",
531: "ABC");
532: }
533:
534: /**
535: * checks views based on other views
536: */
537: private void checkViewsOnViews() throws SQLException {
538:
539: // ................................................................
540: // not that it should make any difference whether we SELECT FROM a table or view, but who knows
541: checkViewTranslationAndContent(
542: "V1",
543: null,
544: "SELECT * FROM S1",
545: "SELECT \"S1\".ID, \"S1\".A, \"S1\".B, \"S1\".C FROM S1",
546: "L1");
547: }
548:
549: /**
550: * checks views based on a UNION statement
551: */
552: private void checkUnionViews() throws SQLException {
553:
554: checkViewTranslationAndContent(
555: "U1",
556: null,
557: "SELECT * FROM TABLE_A UNION SELECT * FROM TABLE_B",
558: "SELECT \"TABLE_A\".ID_A, \"TABLE_A\".NAME_A FROM TABLE_A UNION SELECT \"TABLE_B\".ID_B, \"TABLE_B\".NAME_B FROM TABLE_B",
559: new Object[][] {
560: new Object[] { new Integer(1), "first A" },
561: new Object[] { new Integer(1), "first B" },
562: new Object[] { new Integer(2), "second A" },
563: new Object[] { new Integer(2), "second B" } });
564: checkViewTranslationAndContent(
565: "U2",
566: null,
567: "SELECT * FROM ( SELECT * FROM TABLE_A UNION SELECT * FROM TABLE_B )",
568: "SELECT ID_A, NAME_A FROM ( SELECT \"TABLE_A\".ID_A, \"TABLE_A\".NAME_A FROM TABLE_A UNION SELECT \"TABLE_B\".ID_B, \"TABLE_B\".NAME_B FROM TABLE_B )",
569: new Object[][] {
570: new Object[] { new Integer(1), "first A" },
571: new Object[] { new Integer(1), "first B" },
572: new Object[] { new Integer(2), "second A" },
573: new Object[] { new Integer(2), "second B" } });
574: }
575:
576: /**
577: * main test method of this class
578: */
579: public void test() {
580:
581: try {
582: checkSimpleViews();
583: checkAsterisksCombined();
584: checkMultipleTables();
585: checkSubSelects();
586: checkColumnLists();
587: checkViewsOnViews();
588: checkUnionViews();
589: } catch (SQLException ex) {
590: fail(ex.toString());
591: }
592: }
593:
594: /**
595: * entry point to run the test directly
596: */
597: public static void main(String[] argv) {
598: runWithResult(TestViewAsterisks.class, "test");
599: }
600: }
|