/**
* Test that implicit schema creation of other schemas besides the initial default schema is still
* transactional.
*/
public void testOtherImplicitSchemaCreation() throws SQLException {
Connection c1 = openUserConnection("newuser");
c1.setAutoCommit(false);
Statement s1 = c1.createStatement();
// Will auto-create schema OTHERSCHEMA:
s1.executeUpdate("create table otherschema.t1(i int)");
s1.close();
JDBC.assertSingleValueResultSet(
c1.createStatement()
.executeQuery(
"select schemaname from sys.sysschemas " + "where schemaname='OTHERSCHEMA'"),
"OTHERSCHEMA");
c1.rollback();
JDBC.assertEmpty(
c1.createStatement()
.executeQuery(
"select schemaname from sys.sysschemas " + "where schemaname='OTHERSCHEMA'"));
c1.rollback();
c1.close();
}
/**
* Test that we recover from self locking in the auto-create nested transaction (cf solution for
* DERBY-48).
*/
public void testDerby48SelfLockingRecovery() throws SQLException {
Connection c1 = openUserConnection("newuser");
c1.setAutoCommit(false);
c1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement s1 = c1.createStatement();
// Set read locks in parent transaction
s1.executeQuery("select count(*) from sys.sysschemas");
// ..which conflicts with the auto-create in a subtransaction
// which will self-lock here, but should recover to try again
// in outer transaction:
s1.executeUpdate("create table t1(i int)");
JDBC.assertSingleValueResultSet(
s1.executeQuery("select schemaname from sys.sysschemas " + "where schemaname='NEWUSER'"),
"NEWUSER");
c1.rollback();
// Since the fallback does the auto-create of the schema in
// the outer transaction, a rollback will remove it:
JDBC.assertEmpty(s1.executeQuery("select * from sys.sysschemas where schemaname='NEWUSER'"));
c1.rollback();
}
/**
* Test that we do get to see the self locking in the auto-create nested transaction (cf solution
* for DERBY-48) when deadlock detection is on, i.e. 40XL2 (LOCK_TIMEOUT_LOG) rather than 40XL1
* (LOCK_TIMEOUT) happens.
*
* <p>After fix for DERBY-5564 LOCK_TIMEOUT will be returned whether diagnostics are on or not.
*/
public void testDerby48SelfLockingRecoveryDeadlockDetectionOn() throws SQLException {
Connection c1 = openUserConnection("newuser");
c1.setAutoCommit(false);
c1.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement s1 = c1.createStatement();
// Set read locks in parent transaction
s1.executeQuery("select count(*) from sys.sysschemas");
// ..which conflicts with the auto-create in a subtransaction
// which will self-lock here, but should throw now:
// in outer transaction:
try {
s1.executeUpdate("create table t1(i int)");
fail("Expected exception " + LOCK_TIMEOUT);
} catch (SQLException e) {
assertSQLState("Expected state: ", LOCK_TIMEOUT, e);
}
JDBC.assertEmpty(s1.executeQuery("select * from sys.sysschemas where schemaname='NEWUSER'"));
c1.rollback();
}
public void testDerby3043CheckConstraint() throws SQLException {
// Demonstrate the DERBY-3043 workaround: if the table name is
// schema-qualified, check constraints do not cause a problem,
// and the named schema is automatically created if it does
// not yet exist:
Connection c0 = openUserConnection("frogs");
Statement s0 = c0.createStatement();
JDBC.assertEmpty(s0.executeQuery("select * from sys.sysschemas where schemaname='FROGS'"));
JDBC.assertEmpty(s0.executeQuery("select * from sys.sysschemas where schemaname='NOSUCH'"));
// A simple example, which should work whether or not the
// DERBY-3043 fix is in place
s0.executeUpdate(
"create table frogs.users2(username varchar(16) " + "CHECK(LENGTH(username)>7))");
// Demonstrate that any schema is lazy-created, not just the
// default schema which matches the username:
s0.executeUpdate(
"create table nosuch.users(username varchar(16) " + "CHECK(LENGTH(username)>7))");
// Schemas FROGS and NOSUCH have been lazy-created:
JDBC.assertSingleValueResultSet(
s0.executeQuery("select schemaname from sys.sysschemas " + "where schemaname='FROGS'"),
"FROGS");
JDBC.assertSingleValueResultSet(
s0.executeQuery("select schemaname from sys.sysschemas " + "where schemaname='NOSUCH'"),
"NOSUCH");
c0.close();
// Now verify that the test cases from DERBY-3043 pass:
Connection c1 = openUserConnection("blogs");
Statement s1 = c1.createStatement();
// At the beginning, the schema 'blogs' does not exist.
JDBC.assertEmpty(s1.executeQuery("select * from sys.sysschemas where schemaname='BLOGS'"));
// Should work, but without the DERBY-3043 fix will get a
// "Schema blogs does not exist" error
s1.executeUpdate("create table users(username varchar(16) " + "CHECK(LENGTH(username)>7))");
// Another slightly more complicated example, which requires
// the DERBY-3043 fix again to work.
s1.executeUpdate(
"CREATE TABLE BLOGSCOM__BLOGS__USERS("
+ "PK INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,"
+ "username VARCHAR(16) NOT NULL "
+ " CONSTRAINT BLOGSCOM__BLOGS__USERS_UNIQUE_username UNIQUE "
+ " CONSTRAINT BLOGSCOM__BLOGS__USERS_PASSWORD_username "
+ " CHECK(LENGTH(username)>7),"
+ "password VARCHAR (32672) NOT NULL , "
+ "PRIMARY KEY(PK))");
// Schema BLOGS should have been lazy-created:
JDBC.assertSingleValueResultSet(
s1.executeQuery("select schemaname from sys.sysschemas " + "where schemaname='BLOGS'"),
"BLOGS");
c1.close();
}