testviewasterisks.java

纯Java的数据库

        ResultSet res = m_connection.getMetaData().getColumns(null, null,
            tableName, "%");

        while (res.next()) {
            assertEquals(
                "unexpected column name in table \"" + tableName
                + "\" at position "
                + (res.getRow() - 1), res.getString(
                    "COLUMN_NAME"), columnNames[res.getRow() - 1]);
        }

        res.previous();
        assertEquals("not enough columns in table \"" + tableName + "\"",
                     columnNames.length, res.getRow());
    }

    /**
     *  checks views selecting an asterisk from a table, in different flavours
     */
    private void checkSimpleViews() throws SQLException {

        // ................................................................
        // SELECT *
        checkViewTranslationAndContent(
            "S1", null, "SELECT * FROM ABC",
            "SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
            "ABC");

        // adding a column to "ABC" should succeed, and still leave the view with the columns
        // before the addition
        executeStatement("ALTER TABLE ABC ADD COLUMN D VARCHAR(50)");
        ensureTableColumns("ABC", new String[] {
            "ID","A", "B", "C", "D"
        });
        ensureTableColumns("S1", new String[] {
            "ID", "A", "B", "C"
        });

        // dropping the column which is not part of the view should be possible
        executeStatement("ALTER TABLE ABC DROP COLUMN D");

        // dropping another column which *is* part of the view shouldn't
        executeStatement("ALTER TABLE ABC DROP COLUMN C",
                         Trace.COLUMN_IS_REFERENCED);

        // finally, dropping the column with CASCADE should work, and also drop the view
        //executeStatement("ALTER TABLE ABC DROP COLUMN C CASCADE");
        // DROP COLUMN c CASCADE not implemented, yet
        // ................................................................
        // same as S1, but this time add a LIMIT clause to the statement
        checkViewTranslationAndContent(
            "S2", null, "SELECT LIMIT 0 2 * FROM ABC ORDER BY ID",
            "SELECT LIMIT 0 2  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC ORDER BY ID",
            "ABC");

        // ................................................................
        // same as S1, but this time add a TOP clause to the statement
        checkViewTranslationAndContent(
            "S3", null, "SELECT TOP 2 * FROM ABC ORDER BY ID",
            "SELECT TOP 2  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC ORDER BY ID",
            "ABC");

        // ................................................................
        // same as S1, but this time add a DISTINCT clause to the statement
        checkViewTranslationAndContent(
            "S4", null, "SELECT DISTINCT * FROM ABC",
            "SELECT DISTINCT ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
            "ABC");

        // ................................................................
        // same as S1, but this time qualifying the asterisk
        checkViewTranslationAndContent(
            "S5", null, "SELECT ABC.* FROM ABC",
            "SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
            "ABC");

        // ................................................................
        // same as S5, but this time also giving the table an alias
        checkViewTranslationAndContent(
            "S6", null, "SELECT \"A\".* FROM ABC AS A",
            "SELECT  \"A\".ID,\"A\".A,\"A\".B,\"A\".C  FROM ABC AS A", "ABC");

        // ................................................................
        // same as S1, but bracket the SELECT definition
        checkViewTranslationAndContent(
            "S7", null, "( SELECT * FROM ABC )",
            "( SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC )",
            "ABC");
    }

    /**
     *  checks views selecting an asterisk plus existing columns
     */
    private void checkAsterisksCombined() throws SQLException {

        // ................................................................
        checkViewTranslationAndContent(
            "C1", null, "SELECT *, A AS \"a2\" FROM ABC",
            "SELECT  ABC.ID,ABC.A,ABC.B,ABC.C , A AS \"a2\" FROM ABC",
            new Object[][] {
            new Object[] {
                new Integer(1), "a", "b", "c", "a"
            }, new Object[] {
                new Integer(2), "d", "e", "f", "d"
            }
        });

        // ................................................................
        checkViewTranslationAndContent(
            "C2", null, "SELECT B AS \"b2\", * FROM ABC",
            "SELECT B AS \"b2\", ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
            new Object[][] {
            new Object[] {
                "b", new Integer(1), "a", "b", "c"
            }, new Object[] {
                "e", new Integer(2), "d", "e", "f"
            }
        });
    }

    /**
     *  checks views selecting asterisks from multiple tables
     */
    private void checkMultipleTables() throws SQLException {

        // ................................................................
        checkViewTranslationAndContent(
            "M1", null, "SELECT * FROM TABLE_A, TABLE_B",
            "SELECT  TABLE_A.ID_A,TABLE_A.NAME_A,TABLE_B.ID_B,TABLE_B.NAME_B  FROM TABLE_A, TABLE_B",
            new Object[][] {
            new Object[] {
                new Integer(1), "first A", new Integer(1), "first B"
            }, new Object[] {
                new Integer(1), "first A", new Integer(2), "second B"
            }, new Object[] {
                new Integer(2), "second A", new Integer(1), "first B"
            }, new Object[] {
                new Integer(2), "second A", new Integer(2), "second B"
            }
        });

        // ................................................................
        checkViewTranslationAndContent(
            "M2", null, "SELECT TABLE_B.*, TABLE_A.* FROM TABLE_A, TABLE_B",
            "SELECT  TABLE_B.ID_B,TABLE_B.NAME_B , TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A, TABLE_B",
            new Object[][] {
            new Object[] {
                new Integer(1), "first B", new Integer(1), "first A"
            }, new Object[] {
                new Integer(2), "second B", new Integer(1), "first A"
            }, new Object[] {
                new Integer(1), "first B", new Integer(2), "second A"
            }, new Object[] {
                new Integer(2), "second B", new Integer(2), "second A"
            }
        });

        // ................................................................
        checkViewTranslationAndContent(
            "M3", null, "SELECT \"TABLE_A\".* FROM TABLE_A, TABLE_B",
            "SELECT  TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A, TABLE_B",
            new Object[][] {
            new Object[] {
                new Integer(1), "first A"
            }, new Object[] {
                new Integer(1), "first A"
            }, new Object[] {
                new Integer(2), "second A"
            }, new Object[] {
                new Integer(2), "second A"
            }
        });
    }

    /**
     *  checks views selecting from sub selects
     */
    private void checkSubSelects() throws SQLException {

        // ................................................................
        checkViewTranslationAndContent(
            "Q1", null, "SELECT * FROM ( SELECT * FROM ABC )",
            "SELECT  ID,A,B,C  FROM ( SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC )",
            null);

        // ................................................................
        checkViewTranslationAndContent(
            "Q2", null,
            "SELECT * FROM ( SELECT * FROM TABLE_A ), ( SELECT * FROM TABLE_B )",
            "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 )",
            null);

        // ................................................................
        checkViewTranslationAndContent(
            "Q3", null, "SELECT A.* FROM ( SELECT * FROM TABLE_A ) AS A",
            "SELECT  \"A\".ID_A,\"A\".NAME_A  FROM ( SELECT  TABLE_A.ID_A,TABLE_A.NAME_A  FROM TABLE_A ) AS A",
            null);

        // ................................................................
        checkViewTranslationAndContent(
            "Q4", null,
            "SELECT A.*, B.* FROM ( SELECT * FROM TABLE_A ) AS A, ( SELECT * FROM TABLE_B ) AS B",
            "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", null);
    }

    /**
     *  checks views which are defined using a column list
     */
    private void checkColumnLists() throws SQLException {

        // just to ensure the column count handling is as expected, else below tests might be useless
        executeStatement("CREATE VIEW IMPOSSIBLE (\"A\") AS SELECT * FROM ABC",
                         Trace.COLUMN_COUNT_DOES_NOT_MATCH);

        // ................................................................
        // not that it should make any difference to S1, but who knows
        checkViewTranslationAndContent("L1", new String[] {
            "C1", "C2", "C3", "C4"
        }, "SELECT * FROM ABC",
           "SELECT  ABC.ID,ABC.A,ABC.B,ABC.C  FROM ABC",
           "ABC");
    }

    /**
     *  checks views based on other views
     */
    private void checkViewsOnViews() throws SQLException {

        // ................................................................
        // not that it should make any difference whether we SELECT FROM a table or view, but who knows
        checkViewTranslationAndContent(
            "V1", null, "SELECT * FROM S1",
            "SELECT  \"S1\".ID,\"S1\".A,\"S1\".B,\"S1\".C  FROM S1", "L1");
    }

    /**
     *  checks views based on a UNION statement
     */
    private void checkUnionViews() throws SQLException {

        checkViewTranslationAndContent(
            "U1", null, "SELECT * FROM TABLE_A UNION SELECT * FROM TABLE_B",
            "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",
            new Object[][] {
            new Object[] {
                new Integer(1), "first A"
            }, new Object[] {
                new Integer(1), "first B"
            }, new Object[] {
                new Integer(2), "second A"
            }, new Object[] {
                new Integer(2), "second B"
            }
        });
        checkViewTranslationAndContent(
            "U2", null,
            "SELECT * FROM ( SELECT * FROM TABLE_A UNION SELECT * FROM TABLE_B )",
            "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 )",
            new Object[][] {
            new Object[] {
                new Integer(1), "first A"
            }, new Object[] {
                new Integer(1), "first B"
            }, new Object[] {
                new Integer(2), "second A"
            }, new Object[] {
                new Integer(2), "second B"
            }
        });
    }

    /**
     *  main test method of this class
     */
    public void test() {

        try {
            checkSimpleViews();
            checkAsterisksCombined();
            checkMultipleTables();
            checkSubSelects();
            checkColumnLists();
            checkViewsOnViews();
            checkUnionViews();
        } catch (SQLException ex) {
            fail(ex.toString());
        }
    }

    /**
     *  entry point to run the test directly
     */
    public static void main(String[] argv) {
        runWithResult(TestViewAsterisks.class, "test");
    }
}