/*
 *
 * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SysDiagVTIMappingTest
 *
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an 
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, 
 * either express or implied. See the License for the specific 
 * language governing permissions and limitations under the License.
 */
package org.apache.derbyTesting.functionTests.tests.lang;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Locale;
import java.util.Properties;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.SecurityManagerSetup;
import org.apache.derbyTesting.junit.SupportFilesSetup;
import org.apache.derbyTesting.junit.SystemPropertyTestSetup;
import org.apache.derbyTesting.junit.TestConfiguration;

public final class SysDiagVTIMappingTest extends BaseJDBCTestCase {

    // Name of the log file to use when testing VTIs that expect one.
    private static final String testLogFile = "sys_vti_test_derby.tstlog";

    private static  final   String[]    ALL_SPACE_TABLE_COLUMNS =
    {
        "CONGLOMERATENAME", "ISINDEX", "NUMALLOCATEDPAGES", "NUMFREEPAGES",
        "NUMUNFILLEDPAGES", "PAGESIZE", "ESTIMSPACESAVING", "TABLEID"
    };

    private static  final   String  BAD_FROM_LIST_JOIN = "42ZB7";

    /**
     * Public constructor required for running test as standalone JUnit.
     */
    public SysDiagVTIMappingTest(String name)
    {
        super(name);
    }

    public static Test suite()
    {
        BaseTestSuite suite =
            new BaseTestSuite("Diagnostic VTI Table Mappings");

        Test    defaultSetup = TestConfiguration.defaultSuite( SysDiagVTIMappingTest.class );

        // turn on statement logging so there will be something in the error log
        // to run these vtis against
        Properties sysprops = new Properties();
        sysprops.put( "derby.language.logStatementText", "true" );
        Test    verboseTest = new SystemPropertyTestSetup ( defaultSetup, sysprops );

        suite.addTest( verboseTest );

         /* Some of the VTIs that are tested in this class require a derby.log
         * file.  We have a test log file stored in the tests/lang directory,
         * and since the VTIs are going to try to read it, the test log file
         * must be in a directory for which Derby has read access.  By
         * using a SupportFilesSetup wrapper, we copy the test log file to
         * the "extin" directory, which has the required permissions.
         */
        return SecurityManagerSetup.noSecurityManager(
            new SupportFilesSetup(suite,
                new String [] {
                    "functionTests/tests/lang/" + testLogFile
                }));
    }

    public void setUp() throws Exception
    {
        Statement stmt = createStatement();
        stmt.execute("create table app.t1 (i int, c varchar(10))");
        stmt.execute("insert into app.t1 values (1, 'one'), "
            + "(2, 'two'), (4, 'four')");
        stmt.close();
    }

    public void tearDown() throws Exception
    {
        Statement stmt = createStatement();
        stmt.execute("drop table app.t1");
        stmt.close();
        super.tearDown();
    }

    /**
     * We use the SpaceTable VTI as our primary test VTI to verify that
     * that VTI table mappings in general are working as expected.  So
     * this method does a lot more than the other VTI test methods;
     * the other test methods just do sanity checks to make sure that
     * the mapping from "SYSCS_DIAG.<vti_table>" to the actual VTI
     * class names in question is working correctly.
     */
    public void testSpaceTable() throws Exception
    {
        Statement st = createStatement();
        st.executeUpdate("set schema APP");
        
        // Should fail because SPACE_TABLE is not defined in APP 
        // schema.
        
        assertStatementError("42ZB4", st,
            "select * from TABLE(SPACE_TABLE('APP')) x");
        
        assertStatementError("42ZB4", st,
            "select * from TABLE(APP.SPACE_TABLE('APP', 'T1')) x");
        
        // Should fail due to extra "TABLE" keyword.
        
        assertStatementError("42X01", st,
            "select * from TABLE TABLE(SYSCS_DIAG.SPACE_TABLE('T1')) x");
        
        assertStatementError("42X01", st,
            "select * from TABLE TABLE (select * from t1) x");
        
        // Should fail because the specified schema does not exist.
        
        assertStatementError("42Y07", st,
            "select * from TABLE(SYSCS_DIAG.SPACE_TABLE('T1', 'APP')) x");
        
        assertStatementError("42Y07", st,
            "select * from "
            + "TABLE(SYSCS_DIAG.SPACE_TABLE('NOTTHERE', 'T1')) x");
        
        // Should fail because SPACE_TABLE is not defined in APP schema.
        
        st.executeUpdate("set schema SYSCS_DIAG");
        assertStatementError("42ZB4", st,
            "select * from TABLE(APP.SPACE_TABLE('APP', 'T1')) x");
        
        // All remaining test cases in this method should succeed.
        
        st.executeUpdate("set schema APP");

        // get table id
        ResultSet   rs1 = st.executeQuery
            (
             "select t.tableid from sys.systables t, sys.sysschemas s\n" +
             "where t.schemaid = s.schemaid\n" +
             "and s.schemaname = 'APP'\n" +
             "and t.tablename = 'T1'"
             );
        rs1.next();
        String      tableID = rs1.getString( 1 );
        rs1.close();

        // These should all return 1 row for APP.T1.
        
        // Two-argument direct call.
        ResultSet rs = st.executeQuery(
            "select * from TABLE(SYSCS_DIAG.SPACE_TABLE('APP', 'T1')) x");
        
        String [] expColNames = ALL_SPACE_TABLE_COLUMNS;
        JDBC.assertColumnNames(rs, expColNames);
        
        String [][] expRS = new String [][]
        {
            {"T1", "0", "1", "0", "1", "4096", "0", tableID}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // Single-argument direct execution.
        rs = st.executeQuery(
            " select * from TABLE(SYSCS_DIAG.SPACE_TABLE('T1')) x");
        
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"T1", "0", "1", "0", "1", "4096", "0", tableID}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // Two-argument prepare and execute.
        PreparedStatement pSt = prepareStatement(
            "select * from TABLE(SYSCS_DIAG.SPACE_TABLE(?, ?)) x");

        pSt.setString(1, "APP");
        pSt.setString(2, "T1");

        rs = pSt.executeQuery();
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"T1", "0", "1", "0", "1", "4096", "0", tableID}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // Single-argument prepare and execute.
        pSt = prepareStatement(
            "select * from TABLE(SYSCS_DIAG.SPACE_TABLE(?)) x");

        pSt.setString(1, "T1");

        rs = pSt.executeQuery();
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"T1", "0", "1", "0", "1", "4096", "0", tableID}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // Statment should succeed but there will be no rows 
        // because the tables do not exist.
        
        rs = st.executeQuery(
            "select * from TABLE(SYSCS_DIAG.SPACE_TABLE('APP')) x");
        
        JDBC.assertColumnNames(rs, expColNames);
        JDBC.assertDrainResults(rs, 0);
        
        rs = st.executeQuery(
            "select * from TABLE(SYSCS_DIAG.SPACE_TABLE('APP', "
            + "'NOTTHERE')) x");
        
        JDBC.assertColumnNames(rs, expColNames);
        JDBC.assertDrainResults(rs, 0);
        
        rs = st.executeQuery(
            "select * from "
            + "TABLE(SYSCS_DIAG.SPACE_TABLE('SYSCS_DIAG', 'NOTTHERE')) x");
        
        JDBC.assertColumnNames(rs, expColNames);
        JDBC.assertDrainResults(rs, 0);
        
        // Should see zero rows since LOCK_TABLE does not exist as 
        // an actual base table (it's another VTI).
        
        rs = st.executeQuery(
            "select * from "
            + "TABLE(SYSCS_DIAG.SPACE_TABLE('SYSCS_DIAG', 'LOCK_TABLE')) x");
        
        JDBC.assertColumnNames(rs, expColNames);
        JDBC.assertDrainResults(rs, 0);
        
        // Similar tests but in the SYSCS_DIAG schema, in which 
        // case the schema-name for SPACE_TABLE should not be required.
        
        st.executeUpdate("set schema syscs_diag");
        
        // Should see 1 row for APP.T1.
        
        pSt = prepareStatement(
            "select * from TABLE(SPACE_TABLE(?, ?)) x");
        
        pSt.setString(1, "APP");
        pSt.setString(2, "T1");

        rs = pSt.executeQuery();
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"T1", "0", "1", "0", "1", "4096", "0", tableID}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // Should see zero rows since T1 does not exist within 
        // schema "SYSCS_DIAG".
        
        pSt = prepareStatement("select * from TABLE(SPACE_TABLE(?)) x");
        pSt.setString(1, "T1");

        rs = pSt.executeQuery();
        JDBC.assertColumnNames(rs, expColNames);
        JDBC.assertDrainResults(rs, 0);
        
        // Should see zero rows since LOCK_TABLE does not exist as 
        // an actual base table (it's another VTI).
        
        rs = st.executeQuery(
            "select * from TABLE(SPACE_TABLE('LOCK_TABLE')) x");
        
        JDBC.assertColumnNames(rs, expColNames);
        JDBC.assertDrainResults(rs, 0);
        
        // Simple check to ensure that we we can join with the VTI.
        
        st.executeUpdate("set schema app");
        rs = st.executeQuery(
            "select cast (conglomeratename as varchar(30)), t1.* from"
            + "  TABLE(SYSCS_DIAG.SPACE_TABLE('APP', 'T1')) x,"
            + "  t1"
            + " where x.conglomeratename is not null");
        
        expColNames = new String [] {"1", "I", "C"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"T1", "1", "one"},
            {"T1", "2", "two"},
            {"T1", "4", "four"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);

        // Make sure old way of calling still works until it is 
        // deprecated.
        
        st.executeUpdate("set schema APP");
        rs = st.executeQuery(
            "SELECT * FROM NEW org.apache.derby.diag.SpaceTable('T1') as x");
        
        JDBC.assertColumnNames(rs, ALL_SPACE_TABLE_COLUMNS);
        
        expRS = new String [][]
        {
            {"T1", "0", "1", "0", "1", "4096", "0", tableID}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            "SELECT * FROM NEW "
            + "org.apache.derby.diag.SpaceTable('APP', 'T1') as x");
        
        JDBC.assertColumnNames(rs, ALL_SPACE_TABLE_COLUMNS);
        
        expRS = new String [][]
        {
            {"T1", "0", "1", "0", "1", "4096", "0", tableID}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);

        // verify the syntax showcased in the Reference Guide section on SPACE_TABLE
        // (after the changes introduced by DERBY-5554)

        rs = st.executeQuery
            (
             "select t2.*\n" +
             "    from\n" +
             "        sys.systables systabs,\n" +
             "        table (syscs_diag.space_table()) as t2\n" +
             "    where systabs.tabletype = 'T'\n" +
             "    and systabs.tableid = t2.tableid\n"
             );        
        JDBC.assertColumnNames(rs, ALL_SPACE_TABLE_COLUMNS);
        JDBC.assertFullResultSet(rs, expRS, true);

        rs = st.executeQuery
            (
             "select t1.*\n" +
             "    from\n" +
             "        sys.systables systabs1,\n" +
             "        table (syscs_diag.space_table( systabs1.tablename )) as t1\n" +
             "    where systabs1.tabletype = 'T'\n" +
             "union\n" +
             "select t2.*\n" +
             "    from\n" +
             "        sys.systables systabs2,\n" +
             "        table (syscs_diag.space_table( systabs2.tablename )) as t2\n" +
             "    where systabs2.tabletype = 'T'\n"
             );        
        JDBC.assertColumnNames(rs, ALL_SPACE_TABLE_COLUMNS);
        JDBC.assertFullResultSet(rs, expRS, true);

        // verify the fix to DERBY-5554: joins to VTIs in the FROM list still
        // work when there are more than 1 base tables in the FROM list and
        // they join in the WHERE clause
        
        rs = st.executeQuery
            (
             "select t2.*\n" +
             "    from\n" +
             "        sys.systables systabs,\n" +
             "        table (syscs_diag.space_table(systabs.tablename)) as t2,\n" +
             "        sys.sysconglomerates syscgs\n" +
             "    where systabs.tabletype = 'T' and systabs.tableid = syscgs.tableid\n"
             );        
        JDBC.assertColumnNames(rs, ALL_SPACE_TABLE_COLUMNS);
        JDBC.assertFullResultSet(rs, expRS, true);

        rs = st.executeQuery
            (
             "select t2.*\n" +
             "    from\n" +
             "        sys.sysconglomerates syscgs,\n" +
             "        table (syscs_diag.space_table(systabs.tablename)) as t2,\n" +
             "        sys.systables systabs\n" +
             "    where systabs.tabletype = 'T' and systabs.tableid = syscgs.tableid\n"
             );        
        JDBC.assertColumnNames(rs, ALL_SPACE_TABLE_COLUMNS);
        JDBC.assertFullResultSet(rs, expRS, true);

        // Now do some sanity checking to make sure SPACE_TABLE cannot be
        // used in any illegal ways.        

        checkIllegalUses(st, "space_table", "SpaceTable",
            "('APP', 'T1')", "conglomeratename");

        // Clean-up.
        getConnection().rollback();
        st.close();
    }

    /**
     * Test the 0-arg constructor of the SPACE_TABLE vti. See DERBY-5554.
     */
    public void testSpaceTable_0argConstructor() throws Exception
    {
        Statement st = createStatement();
        ResultSet   rs = st.executeQuery
            (
             "select conglomeratename, isindex, pagesize, tableid\n" +
             "from table(syscs_diag.space_table()) x\n" +
             "where conglomeratename like 'SYS%'\n" +
             "order by conglomeratename"
             );

        String[]  columnNames =
        {
            "CONGLOMERATENAME", "ISINDEX", "PAGESIZE", "TABLEID"
        };
        JDBC.assertColumnNames( rs, columnNames );
        
        String[][]  expRS = new String [][]
        {
            { "SYSALIASES", "0", "4096", "c013800d-00d7-ddbd-08ce-000a0a411400" },
            { "SYSALIASES_INDEX1", "1", "4096", "c013800d-00d7-ddbd-08ce-000a0a411400" },
            { "SYSALIASES_INDEX2", "1", "4096", "c013800d-00d7-ddbd-08ce-000a0a411400" },
            { "SYSALIASES_INDEX3", "1", "4096", "c013800d-00d7-ddbd-08ce-000a0a411400" },
            { "SYSCHECKS", "0", "4096", "80000056-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCHECKS_INDEX1", "1", "4096", "80000056-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCOLPERMS", "0", "4096", "286cc01e-0103-0e39-b8e7-00000010f010" },
            { "SYSCOLPERMS_INDEX1", "1", "4096", "286cc01e-0103-0e39-b8e7-00000010f010" },
            { "SYSCOLPERMS_INDEX2", "1", "4096", "286cc01e-0103-0e39-b8e7-00000010f010" },
            { "SYSCOLPERMS_INDEX3", "1", "4096", "286cc01e-0103-0e39-b8e7-00000010f010" },
            { "SYSCOLUMNS", "0", "4096", "8000001e-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCOLUMNS_INDEX1", "1", "4096", "8000001e-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCOLUMNS_INDEX2", "1", "4096", "8000001e-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCONGLOMERATES", "0", "4096", "80000010-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCONGLOMERATES_INDEX1", "1", "4096", "80000010-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCONGLOMERATES_INDEX2", "1", "4096", "80000010-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCONGLOMERATES_INDEX3", "1", "4096", "80000010-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCONSTRAINTS", "0", "4096", "8000002f-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCONSTRAINTS_INDEX1", "1", "4096", "8000002f-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCONSTRAINTS_INDEX2", "1", "4096", "8000002f-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSCONSTRAINTS_INDEX3", "1", "4096", "8000002f-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSDEPENDS", "0", "4096", "8000003e-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSDEPENDS_INDEX1", "1", "4096", "8000003e-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSDEPENDS_INDEX2", "1", "4096", "8000003e-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSDUMMY1", "0", "4096", "c013800d-00f8-5b70-bea3-00000019ed88" },
            { "SYSFILES", "0", "4096", "80000000-00d3-e222-873f-000a0a0b1900" },
            { "SYSFILES_INDEX1", "1", "4096", "80000000-00d3-e222-873f-000a0a0b1900" },
            { "SYSFILES_INDEX2", "1", "4096", "80000000-00d3-e222-873f-000a0a0b1900" },
            { "SYSFOREIGNKEYS", "0", "4096", "8000005b-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSFOREIGNKEYS_INDEX1", "1", "4096", "8000005b-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSFOREIGNKEYS_INDEX2", "1", "4096", "8000005b-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSKEYS", "0", "4096", "80000039-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSKEYS_INDEX1", "1", "4096", "80000039-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSPERMS", "0", "4096", "9810800c-0121-c5e1-a2f5-00000043e718" },
            { "SYSPERMS_INDEX1", "1", "4096", "9810800c-0121-c5e1-a2f5-00000043e718" },
            { "SYSPERMS_INDEX2", "1", "4096", "9810800c-0121-c5e1-a2f5-00000043e718" },
            { "SYSPERMS_INDEX3", "1", "4096", "9810800c-0121-c5e1-a2f5-00000043e718" },
            { "SYSROLES", "0", "4096", "e03f4017-0115-382c-08df-ffffe275b270" },
            { "SYSROLES_INDEX1", "1", "4096", "e03f4017-0115-382c-08df-ffffe275b270" },
            { "SYSROLES_INDEX2", "1", "4096", "e03f4017-0115-382c-08df-ffffe275b270" },
            { "SYSROLES_INDEX3", "1", "4096", "e03f4017-0115-382c-08df-ffffe275b270" },
            { "SYSROUTINEPERMS", "0", "4096", "2057c01b-0103-0e39-b8e7-00000010f010" },
            { "SYSROUTINEPERMS_INDEX1", "1", "4096", "2057c01b-0103-0e39-b8e7-00000010f010" },
            { "SYSROUTINEPERMS_INDEX2", "1", "4096", "2057c01b-0103-0e39-b8e7-00000010f010" },
            { "SYSROUTINEPERMS_INDEX3", "1", "4096", "2057c01b-0103-0e39-b8e7-00000010f010" },
            { "SYSSCHEMAS", "0", "4096", "80000022-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSSCHEMAS_INDEX1", "1", "4096", "80000022-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSSCHEMAS_INDEX2", "1", "4096", "80000022-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSSEQUENCES", "0", "4096", "9810800c-0121-c5e2-e794-00000043e718" },
            { "SYSSEQUENCES_INDEX1", "1", "4096", "9810800c-0121-c5e2-e794-00000043e718" },
            { "SYSSEQUENCES_INDEX2", "1", "4096", "9810800c-0121-c5e2-e794-00000043e718" },
            { "SYSSTATEMENTS", "0", "4096", "80000000-00d1-15f7-ab70-000a0a0b1500" },
            { "SYSSTATEMENTS_INDEX1", "1", "4096", "80000000-00d1-15f7-ab70-000a0a0b1500" },
            { "SYSSTATEMENTS_INDEX2", "1", "4096", "80000000-00d1-15f7-ab70-000a0a0b1500" },
            { "SYSSTATISTICS", "0", "4096", "f81e0010-00e3-6612-5a96-009e3a3b5e00" },
            { "SYSSTATISTICS_INDEX1", "1", "4096", "f81e0010-00e3-6612-5a96-009e3a3b5e00" },
            { "SYSTABLEPERMS", "0", "4096", "b8450018-0103-0e39-b8e7-00000010f010" },
            { "SYSTABLEPERMS_INDEX1", "1", "4096", "b8450018-0103-0e39-b8e7-00000010f010" },
            { "SYSTABLEPERMS_INDEX2", "1", "4096", "b8450018-0103-0e39-b8e7-00000010f010" },
            { "SYSTABLEPERMS_INDEX3", "1", "4096", "b8450018-0103-0e39-b8e7-00000010f010" },
            { "SYSTABLES", "0", "4096", "80000018-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSTABLES_INDEX1", "1", "4096", "80000018-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSTABLES_INDEX2", "1", "4096", "80000018-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSTRIGGERS", "0", "4096", "c013800d-00d7-c025-4809-000a0a411200" },
            { "SYSTRIGGERS_INDEX1", "1", "4096", "c013800d-00d7-c025-4809-000a0a411200" },
            { "SYSTRIGGERS_INDEX2", "1", "4096", "c013800d-00d7-c025-4809-000a0a411200" },
            { "SYSTRIGGERS_INDEX3",  "1", "4096", "c013800d-00d7-c025-4809-000a0a411200" },
            { "SYSUSERS", "0", "4096", "9810800c-0134-14a5-40c1-000004f61f90" },
            { "SYSUSERS_INDEX1", "1", "4096", "9810800c-0134-14a5-40c1-000004f61f90" },
            { "SYSVIEWS", "0", "4096", "8000004d-00d0-fd77-3ed8-000a0a0b1900" },
            { "SYSVIEWS_INDEX1", "1", "4096", "8000004d-00d0-fd77-3ed8-000a0a0b1900" },
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
    }
    
    /**
     * Verify that you can't join diagnostic VTIs to one another in the FROM list. See DERBY-5554.
     */
    public void test_vti2vtiJoinInFromList() throws Exception
    {
        Connection  conn = getConnection();
        Statement st = createStatement();

        // joins to real tables are ok
        conn.prepareStatement
            (
             "select t1.*\n" +
             "from \n" +
             "    sys.systables systabs,\n" +
             "    table ( syscs_diag.space_table( systabs.tablename ) ) as t1\n" +
             "where systabs.tabletype = 'T'\n"
             );
        conn.prepareStatement
            (
             "select t1.*\n" +
             "from \n" +
             "    table ( syscs_diag.space_table( systabs.tablename ) ) as t1,\n" +
             "    sys.systables systabs\n" +
             "where systabs.tabletype = 'T'\n"
             );

        // can't join VTIs to one another in the FROM list
        assertStatementError
            (
             BAD_FROM_LIST_JOIN,
             st,
             "select t1.*, t2.*\n" +
             "from \n" +
             "    sys.systables systabs,\n" +
             "    table ( syscs_diag.space_table( systabs.tablename ) ) as t1,\n" +
             "    table ( syscs_diag.space_table( t1.conglomeratename ) ) as t2\n" +
             "where systabs.tabletype = 'T'\n"
             );
        assertStatementError
            (
             "42X04",
             st,
             "select t1.*, t2.*\n" +
             "from \n" +
             "    sys.systables systabs,\n" +
             "    table ( syscs_diag.space_table( t1.conglomeratename ) ) as t2,\n" +
             "    table ( syscs_diag.space_table( systabs.tablename ) ) as t1\n" +
             "where systabs.tabletype = 'T'\n"
             );

    }
    
    /**
     * Just run a couple of sanity checks to makes sure the table
     * mapping for org.apache.derby.diag.StatementDuration() works
     * correctly and fails where it is supposed to.
     */
    public void testStatementDuration() throws Exception
    {
        Statement st = createStatement();
        st.executeUpdate("set schema APP");

        // Do a simple check to make sure the VTI mapping works.
        
        java.net.URL logURL = SupportFilesSetup.getReadOnlyURL(testLogFile);
        String vtiArg = "('" + logURL.getFile() + "')";
        ResultSet rs = st.executeQuery(
            "select * from "
            + "TABLE(SYSCS_DIAG.STATEMENT_DURATION" + vtiArg + ") x");
        
        String [] expColNames = new String [] {
            "TS", "THREADID", "XID", "LCCID", "LOGTEXT", "DURATION"};

        JDBC.assertColumnNames(rs, expColNames);
        String [][] expRS = new String [][]
        {
            {"2006-12-15 16:14:58.280 GMT", "main,5,main", "1111", "0",
                "(DATABASE = ugh), (DRDAID = null), Cleanup action starting",
                "0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);

        // Make sure old way of calling still works until it is 
        // deprecated.
        
        rs = st.executeQuery(
            "SELECT * FROM NEW "
            + "org.apache.derby.diag.StatementDuration" + vtiArg + " as x");
        
        expColNames = new String [] {
            "TS", "THREADID", "XID", "LCCID", "LOGTEXT", "DURATION"};

        JDBC.assertColumnNames(rs, expColNames);
        expRS = new String [][]
        {
            {"2006-12-15 16:14:58.280 GMT", "main,5,main", "1111", "0",
                "(DATABASE = ugh), (DRDAID = null), Cleanup action starting",
                "0"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);

        // And finally, do the usual checks for invalid uses.

        checkIllegalUses(st, "statement_duration", "StatementDuration",
            vtiArg, "logtext");
    }

    /**
     * Just run a couple of sanity checks to makes sure the table
     * mapping for org.apache.derby.diag.ErrorLogReader() works
     * correctly and fails where it is supposed to.
     */
    public void testErrorLogReader() throws Exception
    {
        Statement st = createStatement();
        st.executeUpdate("set schema APP");

        // Do a simple check to make sure the VTI mapping works.
        
        java.net.URL logURL = SupportFilesSetup.getReadOnlyURL(testLogFile);
        String vtiArg = "('" + logURL.getFile() + "')";
        ResultSet rs = st.executeQuery(
            " select * from "
            + "TABLE(SYSCS_DIAG.ERROR_LOG_READER" + vtiArg + ") x");
        
        String [] expColNames = new String [] {
            "TS", "THREADID", "XID", "LCCID", "DATABASE", "DRDAID", "LOGTEXT"};

        JDBC.assertColumnNames(rs, expColNames);
        String [][] expRS = new String [][]
        {
            {"2006-12-15 16:14:58.280 GMT", "main,5,main", "1111", "0", "ugh",
                "null", "Cleanup action starting"},
            {"2006-12-15 16:14:58.280 GMT", "main,5,main", "1111", "0", "ugh",
                "null", "Failed Statement is: select * from oops"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);

        // Make sure old way of calling still works until it is 
        // deprecated.
        
        rs = st.executeQuery(
            "SELECT * FROM NEW" +
            " org.apache.derby.diag.ErrorLogReader" + vtiArg + " as x");
        
        expColNames = new String [] {
            "TS", "THREADID", "XID", "LCCID", "DATABASE", "DRDAID", "LOGTEXT"};

        JDBC.assertColumnNames(rs, expColNames);
        expRS = new String [][]
        {
            {"2006-12-15 16:14:58.280 GMT", "main,5,main", "1111", "0", "ugh",
                "null", "Cleanup action starting"},
            {"2006-12-15 16:14:58.280 GMT", "main,5,main", "1111", "0", "ugh",
                "null", "Failed Statement is: select * from oops"}
        };

        JDBC.assertFullResultSet(rs, expRS, true);

        // And finally, do the usual checks for invalid uses.

        checkIllegalUses(st, "error_log_reader", "ErrorLogReader",
            vtiArg, "logtext");
    }

    /**
     * Tests to make sure that attempts to use the TABLE constructor
     * with things other than the VTI diagnostic table functions
     * do not work (with the exception of SELECT and VALUES queries,
     * which should work as normal).
     */
    public void testInvalidTableFunctions() throws Exception
    {
        // Sanity check: make sure SELECT and VALUES clauses still  work.
        
        Statement st = createStatement();
        st.executeUpdate("set schema APP");

        ResultSet rs = st.executeQuery(
            "select * from table (select * from t1) x");
        
        String [] expColNames = new String [] {"I", "C"};
        JDBC.assertColumnNames(rs, expColNames);
        
        String [][] expRS = new String [][]
        {
            {"1", "one"},
            {"2", "two"},
            {"4", "four"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        rs = st.executeQuery(
            " select * from table (values (1, 2), (2, 3)) x");
        
        expColNames = new String [] {"1", "2"};
        JDBC.assertColumnNames(rs, expColNames);
        
        expRS = new String [][]
        {
            {"1", "2"},
            {"2", "3"}
        };
        
        JDBC.assertFullResultSet(rs, expRS, true);
        
        // Use of TABLE constructor with regular tables should fail.

        assertStatementError("42X01", st, "select * from table (app.t1) x");
        assertStatementError("42ZB4", st, "select * from table (app.t1()) x");

        // Use of TABLE constructor with VTI tables (as opposed to VTI
        // table *functions*) should fail.

        assertStatementError("42X01", st,
            "select * from table (syscs_diag.lock_table) x");

        assertStatementError("42Y03", st,
            "select * from table (syscs_diag.lock_table()) x");

        assertStatementError("42X01", st,
            "select * from table (syscs_diag.transaction_table) x");

        assertStatementError("42Y03", st,
            "select * from table (syscs_diag.transaction_table()) x");

        assertStatementError("42X01", st,
            "select * from table (syscs_diag.statement_cache) x");

        assertStatementError("42Y03", st,
            "select * from table (syscs_diag.statement_cache()) x");

        assertStatementError("42X01", st,
            "select * from table (syscs_diag.error_messages) x");

        assertStatementError("42Y03", st,
            "select * from table (syscs_diag.error_messages()) x");

        // Clean-up.
        getConnection().rollback();
        st.close();
    }

    /**
     * Test that diagnostic VTIs will work correctly when an ORDER BY
     * clause appears and/or sort elimination occurs.  DERBY-2805.
     */
    public void testOrderBy() throws SQLException
    {
        Statement st = createStatement();
        st.executeUpdate("set schema APP");

        // Create a single testing table for this fixture only.

        st.execute("create table ob_t1 (i int, c char(250))");
        st.execute("create index i_ix on ob_t1 (i)");
        st.execute("create index c_ix on ob_t1 (c desc)");

        /* Several queries to make sure ORDER BY actually takes effect.
         * First execute with just the ORDER BY, then execute with the
         * ORDER BY *and* a DISTINCT. The latter leads to sort elimination
         * but should still run without error and return the same results
         * (prior to the fix for DERBY-2805 the sort elimination would
         * lead to an ASSERT failure with sane builds).
         */

        String [][] expRS = new String [][]
        {
            {"C_IX", "1", "0"},
            {"I_IX", "1", "0"},
            {"OB_T1", "0", "0"},
        };

        ResultSet rs = st.executeQuery(
            "select CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
            "table(syscs_diag.space_table('OB_T1')) X order by 1");

        JDBC.assertFullResultSet(rs, expRS);

        rs = st.executeQuery(
            "select distinct CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
            "table(syscs_diag.space_table('OB_T1')) X order by 1");

        JDBC.assertFullResultSet(rs, expRS);

        expRS = new String [][]
        {
            {"OB_T1", "0", "0"},
            {"C_IX", "1", "0"},
            {"I_IX", "1", "0"},
        };

        rs = st.executeQuery(
            "select CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
            "table(syscs_diag.space_table('OB_T1')) X order by 2, 1");

        JDBC.assertFullResultSet(rs, expRS);

        rs = st.executeQuery(
            "select distinct CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
            "table(syscs_diag.space_table('OB_T1')) X order by 2, 1");

        JDBC.assertFullResultSet(rs, expRS);
        expRS = new String [][]
        {
            {"OB_T1", "0", "0"},
            {"I_IX", "1", "0"},
            {"C_IX", "1", "0"},
        };

        rs = st.executeQuery(
            "select CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
            "table(syscs_diag.space_table('OB_T1')) X order by 2, 1 desc");

        JDBC.assertFullResultSet(rs, expRS);

        rs = st.executeQuery(
            "select distinct CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
            "table(syscs_diag.space_table('OB_T1')) X order by 2, 1 desc");

        JDBC.assertFullResultSet(rs, expRS);

        // Cleanup.

        st.execute("drop table ob_t1");
        st.close();
    }

    /**
     * Test that diagnostic VTIs will work correctly when they
     * are invoked in a subquery with correlated references to
     * outer query blocks.  DERBY-3138.
     */
    public void testCorrelatedReferences() throws SQLException
    {
        Statement   st = createStatement();
        String      [][] expRS = new String [][] {};

        ResultSet rs = st.executeQuery
            (
             "select s.schemaname, t.tableName\n" +
             "from sys.sysschemas s, sys.systables t\n" +
             "where t.schemaid=s.schemaid\n" +
             "and exists\n" +
             "(\n" +
             "  select vti.*\n" +
             "  from table( syscs_diag.space_table( s.schemaname, t.tableName ) ) as vti\n" +
             "  where vti.numfreepages < -1\n" +
             ")\n"
             );
        
        JDBC.assertFullResultSet(rs, expRS);

        rs.close();
        st.close();
    }

    /**
     * Basic sanity test for SYSCS_DIAG.CONTAINED_ROLES. See also the
     * tools/ij_show_roles.sql test for a test that actually defines and uses
     * roles with this VTI.
     */
    public void testContainedRoles() throws SQLException
    {
        Statement   st = createStatement();

        // 2-arg version
        ResultSet rs = st.executeQuery
            ("select * from table(syscs_diag.contained_roles(null, 0))t");

        JDBC.assertEmpty(rs);

        // 1-arg version
        rs = st.executeQuery
            ("select * from table(syscs_diag.contained_roles(null))t");

        JDBC.assertEmpty(rs);

        rs.close();
        st.close();
    }

    /**
     * Test date formatting in the vtis which read the error log. This attempts
     * to keep us from breaking these vtis if the format of logged timestamps
     * changes. See DERBY-5391.
     */
    public  void    test_5391() throws Exception
    {
        Statement   st = createStatement();

        ResultSet   rs1 = st.executeQuery( "select * from table (syscs_diag.error_log_reader( )) as t1" );
        vetTimestamp( rs1 );
        rs1.close();

        ResultSet   rs2 = st.executeQuery( "select * from table (syscs_diag.statement_duration()) as t1" );
        vetTimestamp( rs2 );
        rs2.close();

        st.close();
    }
    private void    vetTimestamp( ResultSet rs ) throws Exception
    {
        assertTrue( rs.next() );

        String  timestampString = rs.getString( 1 ).trim();

        SimpleDateFormat sdf =
            new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
        Timestamp   timestamp = new Timestamp( sdf.parse( timestampString ).getTime() );

        println( timestamp.toString() );
    }

    /* All statements in this method should fail because a VTI table-
     * mapping that takes arguments can only be used as part of the TABLE 
     * constructor.  Any other uses of, or attempts to modify, such a
     * VTI table should throw an appropriate error.
     */
    private void checkIllegalUses(Statement st, String vtiTableName,
        String vtiMethodName, String args, String colName) throws SQLException
    {
        assertStatementError("42X05", st,
            "select * from syscs_diag." + vtiTableName);
        
        assertStatementError("42X01", st,
            "select * from syscs_diag." + vtiTableName + args + " x");
        
        assertStatementError("42X01", st,
            "select * from (syscs_diag." + vtiTableName + args + ") x");
        
        assertStatementError("42Y55", st,
            "drop table syscs_diag." + vtiTableName);
        
        assertStatementError("42X01", st,
            "drop table syscs_diag." + vtiTableName + args);
        
        assertStatementError("42X62", st,
            "drop function syscs_diag." + vtiTableName);
        
        assertStatementError("42X01", st,
            "drop function syscs_diag." + vtiTableName + args);
        
        assertStatementError("42X62", st,
            "alter table syscs_diag." + vtiTableName + " add column bad int");
        
        assertStatementError("42X01", st,
            "alter table syscs_diag." + vtiTableName + args
            + " add column bad int");
        
        assertStatementError("42X05", st,
            "update syscs_diag." + vtiTableName
            + " set " + colName + " = NULL");
        
        assertStatementError("42X01", st,
            "update syscs_diag." + vtiTableName + args + " set "
            + colName + "  = NULL");
        
        assertStatementError("42X05", st,
            "delete from syscs_diag." + vtiTableName + " where 1 = 1");
        
        assertStatementError("42X01", st,
            "delete from syscs_diag." + vtiTableName + args + " where 1 = 1");
        
        assertStatementError("42X05", st,
            "insert into syscs_diag." + vtiTableName + " values('bad')");
        
        assertStatementError("42X05", st,
            "insert into syscs_diag." + vtiTableName + " (" + colName
            + ") values('bad')");
        
        assertStatementError("42X01", st,
            "insert into syscs_diag." + vtiTableName + args
            + " values('bad')");
        
        CallableStatement cSt = prepareCall(
            "call SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, 1)");
        cSt.setString(1, "SYSCS_DIAG");
        cSt.setString(2, vtiTableName.toUpperCase());

        assertStatementError("42X62", cSt);
        
        cSt = prepareCall(
            "call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?, ?, 1, 1, 1)");
        cSt.setString(1, "SYSCS_DIAG");
        cSt.setString(2, vtiTableName.toUpperCase());
        assertStatementError("42Y55", cSt);

        assertStatementError("42X08", st,
            "update new org.apache.derby.diag." + vtiMethodName + args
            + " set " + colName + " = NULL");
        
        assertStatementError("42X08", st,
            "delete from new org.apache.derby.diag." + vtiMethodName + args
            + " where 1 = 0");

        // Simple check to verify same restrictions hold true if current
        // schema is "SYSCS_DIAG".

        st.execute("set schema syscs_diag");
        assertStatementError("42X01", st,
            "select * from " + vtiTableName + args + " x");
        
        assertStatementError("42X01", st,
            "select * from (" + vtiTableName + args + ") x");

        st.execute("set schema app");
    }
}