/**
   * Check that even though we have set schema to a user schema, the metadata queries get run with
   * compilation schema as SYS. DERBY-2946 Test added for 10.4.
   *
   * @throws SQLException
   */
  public void testMetaDataQueryRunInSYScompilationSchema() throws SQLException {
    // This test is for databases with territory based collation. That
    // feature was added in 10.3 codeline and hence there is no point in
    // doing any testing with pre-10.3 databases.
    if (!oldAtLeast(10, 3)) return;

    DataSource ds = JDBCDataSource.getDataSourceLogical("COLLATED_DB_10_3");

    switch (getPhase()) {
      case PH_CREATE:
        // create the database if it was not already created. Note the
        // JDBC url attributes.
        JDBCDataSource.setBeanProperty(
            ds, "ConnectionAttributes", "create=true;territory=no;collation=TERRITORY_BASED");
        ds.getConnection().close();
        break;

      case PH_SOFT_UPGRADE:
      case PH_POST_SOFT_UPGRADE:
      case PH_HARD_UPGRADE:
        Connection con = ds.getConnection();
        // First make the current schema as a user schema. And then run a
        // metadata query to make sure that it runs fine. If it does (which
        // is the expected behavior), then it will mean that the metadata
        // query is getting run with SYS as the compilation schema rather
        // than the current schema which is APP.
        Statement s = con.createStatement();
        s.execute("SET SCHEMA APP");

        DatabaseMetaData dmd = con.getMetaData();
        ResultSet rs = dmd.getTables(null, "APP", null, null);
        JDBC.assertDrainResults(rs);
        s.close();
        break;
    }
  }
  /**
   * Check that when hard-upgraded to 10.4 or later SQL roles can be declared if DB has
   * sqlAuthorization.
   *
   * @throws SQLException
   */
  public void disabledTill10_5_testSQLRoles() throws SQLException {
    // Do rudimentary sanity checking: that we can create and drop roles
    // when we are database owner. If so, we can presume SYS.SYSROLES
    // has been upgraded correctly.

    DataSource ds = JDBCDataSource.getDataSourceLogical("ROLES_10_4");
    String createRoleText = "create role foo";
    String dropRoleText = "drop role foo";
    Connection conn = null;
    Statement s = null;
    boolean supportSqlAuthorization = oldAtLeast(10, 2);

    JDBCDataSource.setBeanProperty(ds, "user", "garfield");
    JDBCDataSource.setBeanProperty(ds, "password", "theCat");

    switch (getPhase()) {
      case PH_CREATE:
        // create the database if it was not already created.
        JDBCDataSource.setBeanProperty(ds, "createDatabase", "create");
        conn = ds.getConnection();

        // Make the database have std security, and define
        // a database user for the database owner).
        CallableStatement cs = conn.prepareCall("call syscs_util.syscs_set_database_property(?,?)");

        cs.setString(1, "gemfirexd.authentication.required");
        cs.setString(2, "true");
        cs.execute();

        cs.setString(1, "gemfirexd.authentication.provider");
        cs.setString(2, "BUILTIN");
        cs.execute();

        cs.setString(1, "gemfirexd.sql-authorization");
        cs.setString(2, "true");
        cs.execute();

        cs.setString(1, "gemfirexd.distributedsystem.propertiesOnly");
        cs.setString(2, "true");
        cs.execute();

        cs.setString(1, "gemfirexd.user.garfield");
        cs.setString(2, "theCat");
        cs.execute();

        conn.close();

        JDBCDataSource.shutdownDatabase(ds);
        break;

      case PH_SOFT_UPGRADE:
        /* We can't always do soft upgrade, because when
         * sqlAuthorization is set and we are coming from a
         * pre-10.2 database, connecting will fail with a message
         * to hard upgrade before setting sqlAuthorization, so we
         * skip this step.
         */
        if (oldAtLeast(10, 2)) {
          // needs hard upgrade
          conn = ds.getConnection();
          s = conn.createStatement();

          assertStatementError("XCL47", s, createRoleText);
          conn.close();

          JDBCDataSource.shutdownDatabase(ds);
        }
        break;

      case PH_POST_SOFT_UPGRADE:
        conn = ds.getConnection();
        s = conn.createStatement();

        // syntax error
        assertStatementError("42X01", s, createRoleText);
        conn.close();

        JDBCDataSource.shutdownDatabase(ds);
        break;

      case PH_HARD_UPGRADE:
        JDBCDataSource.setBeanProperty(ds, "connectionAttributes", "upgrade=true");
        conn = ds.getConnection();
        s = conn.createStatement();

        // should work now
        try {
          s.execute(createRoleText);
        } catch (SQLException e) {
          fail("can't create role on hard upgrade");
        }

        s.execute(dropRoleText);
        conn.close();

        JDBCDataSource.clearStringBeanProperty(ds, "connectionAttributes");
        JDBCDataSource.shutdownDatabase(ds);
        break;
    }
  }