testviewasterisks.java

纯Java的数据库

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


package org.hsqldb.test;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import junit.framework.AssertionFailedError;

import org.hsqldb.Trace;

/**
 *  is a test which verifies the functionality of replacing asterisks in VIEW statements with column
 *  lists.
 *
 *  During investigating http://www.openoffice.org/issues/show_bug.cgi?id=78296 (an issue raised
 *  in OpenOffice.org, which integrates HSQLDB), it rose that views did not behave to the standard
 *  in that they don't always reflect the table column list at the time of creation of the view.
 *
 *  This was changed so that when you CREATE a VIEW, then any asterisks in the constituting
 *  statement are replaced with their column lists.
 *
 *  This test verifies this functionality in a number of different flavours.
 *
 * @author [e-mail]
 */
public class TestViewAsterisks extends TestBase {

    java.sql.Statement  m_statement;
    java.sql.Connection m_connection;

    /** Creates a new instance of TestViewAsterisks */
    public TestViewAsterisks(String testName) {
        super(testName, null, false);
    }

    /**
     * creates the database tables needed for the test
     */
    private void setupDatabase() {

        try {
            m_connection = newConnection();
            m_statement  = m_connection.createStatement();

            executeStatement("DROP TABLE IF EXISTS ABC CASCADE");
            executeStatement("DROP TABLE IF EXISTS TABLE_A CASCADE");
            executeStatement("DROP TABLE IF EXISTS TABLE_B CASCADE");
            executeStatement(
                "CREATE TABLE ABC (ID INTEGER NOT NULL PRIMARY KEY, A VARCHAR(50), B VARCHAR(50), C VARCHAR(50))");
            executeStatement("INSERT INTO ABC VALUES (1, 'a', 'b', 'c')");
            executeStatement("INSERT INTO ABC VALUES (2, 'd', 'e', 'f')");
            executeStatement(
                "CREATE TABLE TABLE_A (ID_A INTEGER NOT NULL PRIMARY KEY, NAME_A VARCHAR(50))");
            executeStatement("INSERT INTO TABLE_A VALUES (1, 'first A')");
            executeStatement("INSERT INTO TABLE_A VALUES (2, 'second A')");
            executeStatement(
                "CREATE TABLE TABLE_B (ID_B INTEGER NOT NULL PRIMARY KEY, NAME_B VARCHAR(50))");
            executeStatement("INSERT INTO TABLE_B VALUES (1, 'first B')");
            executeStatement("INSERT INTO TABLE_B VALUES (2, 'second B')");
        } catch (SQLException ex) {
            fail(ex.toString());
        }
    }

    public void setUp() {
        super.setUp();
        setupDatabase();
    }

    protected void tearDown() {
        executeStatement("SHUTDOWN");
        super.tearDown();
    }

    /**
     * executes a given m_statement
     *
     *  <p>Basically, this method calls <code>m_statement.execute(sql)</code>,
     *  but wraps any <code>SQLException</code>s into a JUnit error.
     */
    private void executeStatement(String sql) {
        executeStatement(sql, 0);
    }

    private void executeStatement(String sql, int expectedVendorCode) {

        try {
            m_statement.execute(sql);
            assertTrue(
                "executing\n  " + sql
                + "\nwas expected to fail, but it didn't", expectedVendorCode
                    == 0);
        } catch (SQLException ex) {
            if (expectedVendorCode == 0) {
                fail(ex.toString());
            }

            assertEquals(
                "executing\n  " + sql
                + "\ndid not result in the expected error", expectedVendorCode, -ex
                    .getErrorCode());
        }
    }

    /**
     *  creates a view with the given name, the given constituting statement, and an optional column list
     *
     *  @param viewName
     *      specifies the name of the view to create
     *  @param columnList
     *      list of names of the columns of the view, will be specified in the CREATE VIEW statement. Might be <code>null</code>.
     *  @param viewStatement
     *      the statement of the view
     */
    private void createView(String viewName, String[] columnList,
                            String viewStatement) throws SQLException {

        StringBuffer colList = new StringBuffer();

        if (columnList != null) {
            colList.append(" (");

            for (int i = 0; i < columnList.length; ++i) {
                colList.append('"').append(columnList[i]).append('"');

                if (i < columnList.length - 1) {
                    colList.append(',');
                }
            }

            colList.append(")");
        }

        executeStatement("CREATE VIEW " + viewName + colList.toString()
                         + " AS " + viewStatement);

        if (columnList != null) {
            ensureTableColumns(viewName, columnList);
        }
    }

    /**
     *  retrieves the statement which defines a given view
     */
    private String getViewStatement(String viewName) throws SQLException {

        ResultSet res = m_statement.executeQuery(
            "SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.SYSTEM_VIEWS WHERE TABLE_NAME = '"
            + viewName + "'");

        res.next();

        String statement = res.getString(1);

        return statement;
    }

    /**
     *  ensures two tables (or views, that is) have the same content
     */
    private void ensureEqualContent(String tableNameLHS,
                                    String tableNameRHS) throws SQLException {

        ResultSet lhs = m_statement.executeQuery("SELECT * FROM \""
            + tableNameLHS + "\"");
        ResultSet rhs = m_statement.executeQuery("SELECT * FROM \""
            + tableNameRHS + "\"");
        ResultSetMetaData meta = lhs.getMetaData();

        while (lhs.next() && rhs.next()) {
            for (int col = 1; col <= meta.getColumnCount(); ++col) {
                assertEquals("table content does not match: cp. "
                             + tableNameLHS + "-" + tableNameRHS + ", row "
                             + lhs.getRow() + ", col "
                             + col, lhs.getObject(col), rhs.getObject(col));
            }
        }

        // lhs should be after last, rhs still on last
        assertTrue("row count does not match: " + tableNameLHS + "-"
                   + tableNameRHS, lhs.isAfterLast() && rhs.isLast());
    }

    /**
     *  ensures the content of a given table matches a given object array's content
     */
    private void ensureTableContent(String tableName,
                                    Object[][] tableData) throws SQLException {

        ResultSet lhs = m_statement.executeQuery("SELECT * FROM \""
            + tableName + "\"");
        ResultSetMetaData meta     = lhs.getMetaData();
        int               colCount = meta.getColumnCount();

        while (lhs.next()) {
            int row = lhs.getRow();

            assertEquals(colCount, tableData[row - 1].length);

            for (int col = 1; col <= colCount; ++col) {
                assertEquals(
                    "unexpected table content in " + tableName + " (row "
                    + row + ", col " + col + ")", tableData[row - 1][col - 1],
                                                  lhs.getObject(col));
            }
        }
    }

    /**
     *  creates a view with a given name and statement, ensures that it's statement is translated as expected, and ensures
     *  that the content of the view is as expected
     *
     *  @param viewName
     *      the name of the to-be-created view
     *  @param columnNames
     *      the names of the columns of the view, as to be specified in the CREATE VIEW statement. Might be null,
     *      in this case the view will be created without an explicit column list
     *  @param viewStatement
     *      the statement of the to-be-created view
     *  @param expectedTranslatedStatement
     *      the expected statement of the view, after it has been implicitly translated by HSQL. If the actual
     *      statement after creation does not match this expected statement, this is a failure condition which
     *      results in a AssertionFailedError being thrown.
     *  @param expectedContent
     *      the expected content of the view. If this is <code>null</code>, it is ignored. Else, if it is a
     *      string, it is interpreted as name of the table which must have the same content as a view. If
     *      it's no string either, it must be a two-dimensional Object array specifying the expected content.
     */
    private void checkViewTranslationAndContent(String viewName,
            String[] columnList, String viewStatement,
            String expectedTranslatedStatement,
            Object expectedContent) throws SQLException {

        createView(viewName, columnList, viewStatement);

        String actualTranslatedStatement = getViewStatement(viewName);

        if (!actualTranslatedStatement.equals(expectedTranslatedStatement)) {
            StringBuffer message = new StringBuffer();

            message.append(viewName).append(
                "'s statement not translated as expected\n");
            message.append("original statement:\n  ").append(
                viewStatement).append('\n');
            message.append("expected translated statement:\n  ").append(
                expectedTranslatedStatement).append('\n');
            message.append("actual translated statement:\n  ").append(
                actualTranslatedStatement).append('\n');

            throw new AssertionFailedError(message.toString());
        }

        if (expectedContent != null) {
            if (expectedContent.getClass().equals(String.class)) {
                ensureEqualContent(viewName, (String) expectedContent);
            } else {
                ensureTableContent(viewName, (Object[][]) expectedContent);
            }
        }
    }

    /**
     *  ensures that a given table has columns with a given name
     */
    private void ensureTableColumns(String tableName,
                                    String[] columnNames) throws SQLException {