コード例 #1
0
  /**
   * 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();
  }
コード例 #2
0
  /**
   * 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();
  }
コード例 #3
0
  /**
   * 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();
  }
コード例 #4
0
  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();
  }