/**
* 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;
}
}