java.sql.Statement

Here are the examples of the java api class java.sql.Statement taken from open source projects.

1. OuterJoinTest#testDerby_4798_NPE()

Project: derby
Source File: OuterJoinTest.java
View license
/**
     * Test the queries reported in DERBY-4798 as giving null pointer
     * exceptions. Should fail with NPE before the fix went in.
     */
public void testDerby_4798_NPE() throws Exception {
    setAutoCommit(false);
    Statement st = createStatement();
    ResultSet rs = null;
    String[][] expRS;
    st.executeUpdate("create table t0(x0 int)");
    st.executeUpdate("create table t1(x1 int)");
    st.executeUpdate("create table t2(x2 int)");
    st.executeUpdate("create table t3(x3 int)");
    st.executeUpdate("create table t4(x4 int)");
    st.executeUpdate("insert into t4 values(0)");
    st.executeUpdate("insert into t4 values(1)");
    st.executeUpdate("insert into t4 values(2)");
    st.executeUpdate("insert into t4 values(3)");
    st.executeUpdate("create table t5(x5 int)");
    st.executeUpdate("insert into t5 values(0)");
    st.executeUpdate("insert into t5 values(1)");
    st.executeUpdate("insert into t5 values(2)");
    st.executeUpdate("insert into t5 values(3)");
    st.executeUpdate("insert into t5 values(4)");
    st.executeUpdate("create table t6(x6 int)");
    st.executeUpdate("insert into t6 values(0)");
    st.executeUpdate("insert into t6 values(1)");
    st.executeUpdate("insert into t6 values(2)");
    st.executeUpdate("insert into t6 values(3)");
    st.executeUpdate("insert into t6 values(4)");
    st.executeUpdate("insert into t6 values(5)");
    st.executeUpdate("create table t7(x7 int)");
    st.executeUpdate("insert into t7 values(0)");
    st.executeUpdate("insert into t7 values(1)");
    st.executeUpdate("insert into t7 values(2)");
    st.executeUpdate("insert into t7 values(3)");
    st.executeUpdate("insert into t7 values(4)");
    st.executeUpdate("insert into t7 values(5)");
    st.executeUpdate("insert into t7 values(6)");
    st.executeUpdate("insert into t0 values(1)");
    st.executeUpdate("insert into t1 values(2)");
    st.executeUpdate("insert into t0 values(3)");
    st.executeUpdate("insert into t1 values(3)");
    st.executeUpdate("insert into t2 values(4)");
    st.executeUpdate("insert into t0 values(5)");
    st.executeUpdate("insert into t2 values(5)");
    st.executeUpdate("insert into t1 values(6)");
    st.executeUpdate("insert into t2 values(6)");
    st.executeUpdate("insert into t0 values(7)");
    st.executeUpdate("insert into t1 values(7)");
    st.executeUpdate("insert into t2 values(7)");
    st.executeUpdate("insert into t3 values(8)");
    st.executeUpdate("insert into t0 values(9)");
    st.executeUpdate("insert into t3 values(9)");
    st.executeUpdate("insert into t1 values(10)");
    st.executeUpdate("insert into t3 values(10)");
    st.executeUpdate("insert into t0 values(11)");
    st.executeUpdate("insert into t1 values(11)");
    st.executeUpdate("insert into t3 values(11)");
    st.executeUpdate("insert into t2 values(12)");
    st.executeUpdate("insert into t3 values(12)");
    st.executeUpdate("insert into t0 values(13)");
    st.executeUpdate("insert into t2 values(13)");
    st.executeUpdate("insert into t3 values(13)");
    st.executeUpdate("insert into t1 values(14)");
    st.executeUpdate("insert into t2 values(14)");
    st.executeUpdate("insert into t3 values(14)");
    st.executeUpdate("insert into t0 values(15)");
    st.executeUpdate("insert into t1 values(15)");
    st.executeUpdate("insert into t2 values(15)");
    st.executeUpdate("insert into t3 values(15)");
    rs = st.executeQuery("SELECT t0.x0, " + "       t1.x1," + "       t2.x2," + "       t3.x3," + "       t4.x4," + "       t5.x5," + "       t6.x6," + "       t7.x7 " + "FROM         " + " ((t0                                                               " + "   LEFT OUTER JOIN ((t1                                             " + "                     LEFT OUTER JOIN (t2                            " + "                                      LEFT OUTER JOIN t3            " + "                                        ON t2.x2 = t3.x3 )          " + "                       ON t1.x1 = t2.x2 )                           " + "                    LEFT OUTER JOIN (t4                             " + "                                     INNER JOIN (t5                 " + "                                                 LEFT OUTER JOIN t6 " + "                                                   ON t5.x5 = t6.x6)" + "                                       ON t4.x4 = t5.x5 )           " + "                      ON t1.x1 = t5.x5 )                            " + "     ON t0.x0 = t5.x5 )                                             " + "  LEFT OUTER JOIN t7                                                " + "    ON t3.x3 = t7.x7 )                                              ");
    expRS = new String[][] { { "1", "1", null, null, null, null, null, null }, { "3", "3", "3", null, "3", "3", "3", null }, { "5", "5", null, null, null, null, null, null }, { "7", "7", null, null, null, null, null, null }, { "9", "9", null, null, null, null, null, null }, { "11", "11", null, null, null, null, null, null }, { "13", "13", null, null, null, null, null, null }, { "15", "15", null, null, null, null, null, null } };
    JDBC.assertFullResultSet(rs, expRS);
}

2. AlterTableTest#testAddIdentityColumn2()

Project: derby
Source File: AlterTableTest.java
View license
/**
     * Slight changes to testAddIdentityColumn.
     * <p>
     * Another version of testAddIdenityColumn that provides slightly 
     * different code path, found useful while debugging DERBY-6774.
     **/
public void testAddIdentityColumn2() throws SQLException {
    Statement s = createStatement();
    createTestObjects(s);
    commit();
    // Add an identity column, and verify that it is correctly identified
    // as one.
    s.execute("alter table t0 add column " + "id int generated always as identity");
    ResultSet rs = s.executeQuery("select * from t0");
    JDBC.assertColumnNames(rs, "C1", "ID");
    ResultSetMetaData rsmd = rs.getMetaData();
    assertTrue(rsmd.isAutoIncrement(2));
    assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
    rs.close();
    // Cannot set the value of an identity column that is GENERATED
    // ALWAYS AS.
    assertCompileError("42Z23", "insert into t0(c1, id) values (1, 1)");
    s.execute("insert into t0(c1, id) values (1, default)");
    s.execute("insert into t0(c1) values 2,3,4");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "1" }, { "2", "2" }, { "3", "3" }, { "4", "4" } });
    // Only one identity column is allowed per table.
    assertCompileError("428C1", "alter table t0 add column " + "id2 bigint generated always as identity");
    s.execute("create table table_with_identity(" + "id int generated always as identity)");
    assertCompileError("428C1", "alter table table_with_identity add column " + "id2 bigint generated always as identity");
    rollback();
    // Add an identity column to a non-empty table.
    s.execute("insert into t0 values 1,2,3,4,5");
    s.execute("alter table t0 add column " + "id int generated always as identity " + "(start with 100, increment by 5)");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "100" }, { "2", "105" }, { "3", "110" }, { "4", "115" }, { "5", "120" } });
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "125" }, { "2", "130" }, { "3", "135" } });
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    rollback();
    // Add an identity column that is generated by default.
    s.execute("alter table t0 add column " + "id int generated by default as identity");
    rs = s.executeQuery("select * from t0");
    JDBC.assertColumnNames(rs, "C1", "ID");
    rsmd = rs.getMetaData();
    assertTrue(rsmd.isAutoIncrement(2));
    assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
    rs.close();
    s.execute("insert into t0(c1) values 1,2,3");
    s.execute("insert into t0(c1, id) values (9, 10)");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "1" }, { "2", "2" }, { "3", "3" }, { "9", "10" } });
    rollback();
    // Add an identity column that is generated by default, to a
    // non-empty table.
    s.execute("insert into t0 values 1");
    s.execute("alter table t0 add column id int " + "generated by default as identity");
    JDBC.assertSingleValueResultSet(s.executeQuery("select id from t0"), "1");
    s.execute("insert into t0(c1) values 5,6,7");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 where c1 > 1 order by c1"), new String[][] { { "5", "2" }, { "6", "3" }, { "7", "4" } });
    rollback();
    // Cannot add an identity column without specifying type.
    assertCompileError("42XA9", "alter table t0 add column id " + "generated always as identity");
    // Generated identity values cannot grow beyond the limits of
    // the data type.
    s.execute("insert into t0 values 1,2,3,4");
    assertStatementError("2200H", s, "alter table t0 add column id smallint generated always as " + "identity (start with 30000, increment by 1000)");
    rollback();
    s.execute("insert into t0 values 1,2,3");
    s.execute("alter table t0 add column id smallint generated always as " + "identity (start with 30000, increment by 1000)");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "30000" }, { "2", "31000" }, { "3", "32000" } });
    assertStatementError("2200H", s, "insert into t0(c1) values 4");
    rollback();
    // Drop an identity column that was added with ALTER TABLE.
    s.execute("alter table t0 add column " + "id int generated always as identity");
    s.execute("insert into t0(c1) values 1");
    s.execute("alter table t0 drop column id");
    rs = s.executeQuery("select * from t0");
    JDBC.assertColumnNames(rs, "C1");
    JDBC.assertSingleValueResultSet(rs, "1");
    rollback();
    // Drop a table that contains an identity column added with ALTER TABLE.
    s.execute("alter table t0 add column " + "id int generated always as identity");
    s.execute("alter table t0 drop column id");
    s.execute("drop table t0");
    rollback();
    // Adding a primary key column as an identity column should be allowed.
    s.execute("alter table t0_1 add column id int primary key " + "generated always as identity");
    rollback();
}

3. AlterTableTest#testAddIdentityColumn3()

Project: derby
Source File: AlterTableTest.java
View license
/**
     * Slight changes to testAddIdentityColumn.
     * <p>
     * Another version of testAddIdenityColumn that provides slightly 
     * different code path, found useful while debugging DERBY-6774.
     **/
public void testAddIdentityColumn3() throws SQLException {
    Statement s = createStatement();
    createTestObjects(s);
    commit();
    // Add an identity column to a non-empty table.
    s.execute("insert into t0 values 1,2,3,4,5");
    s.execute("alter table t0 add column " + "id int generated always as identity " + "(start with 100, increment by 5)");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "100" }, { "2", "105" }, { "3", "110" }, { "4", "115" }, { "5", "120" } });
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "125" }, { "2", "130" }, { "3", "135" } });
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    rollback();
    // Add an identity column that is generated by default.
    s.execute("alter table t0 add column " + "id int generated by default as identity");
    ResultSet rs = s.executeQuery("select * from t0");
    JDBC.assertColumnNames(rs, "C1", "ID");
    ResultSetMetaData rsmd = rs.getMetaData();
    assertTrue(rsmd.isAutoIncrement(2));
    assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
    rs.close();
    s.execute("insert into t0(c1) values 1,2,3");
    s.execute("insert into t0(c1, id) values (9, 10)");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "1" }, { "2", "2" }, { "3", "3" }, { "9", "10" } });
    rollback();
    // Add an identity column that is generated by default, to a
    // non-empty table.
    s.execute("insert into t0 values 1");
    s.execute("alter table t0 add column id int " + "generated by default as identity");
    JDBC.assertSingleValueResultSet(s.executeQuery("select id from t0"), "1");
    s.execute("insert into t0(c1) values 5,6,7");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 where c1 > 1 order by c1"), new String[][] { { "5", "2" }, { "6", "3" }, { "7", "4" } });
    rollback();
    // Cannot add an identity column without specifying type.
    assertCompileError("42XA9", "alter table t0 add column id " + "generated always as identity");
    // Generated identity values cannot grow beyond the limits of
    // the data type.
    s.execute("insert into t0 values 1,2,3,4");
    assertStatementError("2200H", s, "alter table t0 add column id smallint generated always as " + "identity (start with 30000, increment by 1000)");
    rollback();
    s.execute("insert into t0 values 1,2,3");
    s.execute("alter table t0 add column id smallint generated always as " + "identity (start with 30000, increment by 1000)");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "30000" }, { "2", "31000" }, { "3", "32000" } });
    assertStatementError("2200H", s, "insert into t0(c1) values 4");
    rollback();
    // Drop an identity column that was added with ALTER TABLE.
    s.execute("alter table t0 add column " + "id int generated always as identity");
    s.execute("insert into t0(c1) values 1");
    s.execute("alter table t0 drop column id");
    rs = s.executeQuery("select * from t0");
    JDBC.assertColumnNames(rs, "C1");
    JDBC.assertSingleValueResultSet(rs, "1");
    rollback();
    // Drop a table that contains an identity column added with ALTER TABLE.
    s.execute("alter table t0 add column " + "id int generated always as identity");
    s.execute("alter table t0 drop column id");
    s.execute("drop table t0");
    rollback();
    // Adding a primary key column as an identity column should be allowed.
    s.execute("alter table t0_1 add column id int primary key " + "generated always as identity");
    rollback();
}

4. TestDbEndToEnd#testEndToEnd1()

View license
/**
   * Steps:
   * 1. admin create a new experimental database
   * 2. admin create a new production database, create table, load data
   * 3. admin grant privilege all@'experimental database' to usergroup1
   * 4. user create table, load data in experimental DB
   * 5. user create view based on table in experimental DB
   * 6. admin create table (same name) in production DB
   * 7. admin grant [email protected] to group
   *    admin grant [email protected] to group
   * 8. user load data from experimental table to production table
   */
@Test
public void testEndToEnd1() throws Exception {
    String tableName1 = "tb_1";
    String tableName2 = "tb_2";
    String viewName1 = "view_1";
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    // 1
    statement.execute("DROP DATABASE IF EXISTS " + DB1 + " CASCADE");
    statement.execute("CREATE DATABASE " + DB1);
    // 2
    statement.execute("DROP DATABASE IF EXISTS " + DB2 + " CASCADE");
    statement.execute("CREATE DATABASE " + DB2);
    statement.execute("USE " + DB2);
    statement.execute("DROP TABLE IF EXISTS " + DB2 + "." + tableName1);
    statement.execute("DROP TABLE IF EXISTS " + DB2 + "." + tableName2);
    statement.execute("create table " + DB2 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("create table " + DB2 + "." + tableName2 + " (under_col int comment 'the under column', value string)");
    statement.execute("load data local inpath '" + dataFile.getPath() + "' into table " + tableName2);
    // 3
    statement.execute("CREATE ROLE all_db1");
    statement.execute("GRANT ALL ON DATABASE " + DB1 + " TO ROLE all_db1");
    statement.execute("CREATE ROLE select_tb1");
    statement.execute("CREATE ROLE insert_tb1");
    statement.execute("CREATE ROLE insert_tb2");
    statement.execute("CREATE ROLE data_uri");
    statement.execute("USE " + DB2);
    statement.execute("GRANT INSERT ON TABLE " + tableName1 + " TO ROLE insert_tb1");
    statement.execute("GRANT INSERT ON TABLE " + tableName2 + " TO ROLE insert_tb2");
    statement.execute("GRANT ALL ON URI 'file://" + dataDir.getPath() + "' TO ROLE data_uri");
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("GRANT SELECT ON TABLE " + tableName1 + " TO ROLE select_tb1");
    statement.execute("GRANT ROLE all_db1, select_tb1, insert_tb1, insert_tb2, data_uri TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    // 4
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("load data local inpath '" + dataFile.getPath() + "' into table " + tableName1);
    // 5
    statement.execute("CREATE VIEW " + viewName1 + " (value) AS SELECT value from " + tableName1 + " LIMIT 10");
    statement.close();
    connection.close();
    // 7
    connection = context.createConnection(ADMIN1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.close();
    connection.close();
    // 8
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB2);
    statement.execute("INSERT OVERWRITE TABLE " + DB2 + "." + tableName2 + " SELECT * FROM " + DB1 + "." + tableName1);
    statement.close();
    connection.close();
}

5. TestDbEndToEnd#testEndToEnd1()

Project: sentry
Source File: TestDbEndToEnd.java
View license
/**
   * Steps:
   * 1. admin create a new experimental database
   * 2. admin create a new production database, create table, load data
   * 3. admin grant privilege all@'experimental database' to usergroup1
   * 4. user create table, load data in experimental DB
   * 5. user create view based on table in experimental DB
   * 6. admin create table (same name) in production DB
   * 7. admin grant [email protected] to group
   *    admin grant [email protected] to group
   * 8. user load data from experimental table to production table
   */
@Test
public void testEndToEnd1() throws Exception {
    String tableName1 = "tb_1";
    String tableName2 = "tb_2";
    String viewName1 = "view_1";
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    // 1
    statement.execute("DROP DATABASE IF EXISTS " + DB1 + " CASCADE");
    statement.execute("CREATE DATABASE " + DB1);
    // 2
    statement.execute("DROP DATABASE IF EXISTS " + DB2 + " CASCADE");
    statement.execute("CREATE DATABASE " + DB2);
    statement.execute("USE " + DB2);
    statement.execute("DROP TABLE IF EXISTS " + DB2 + "." + tableName1);
    statement.execute("DROP TABLE IF EXISTS " + DB2 + "." + tableName2);
    statement.execute("create table " + DB2 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("create table " + DB2 + "." + tableName2 + " (under_col int comment 'the under column', value string)");
    statement.execute("load data local inpath '" + dataFile.getPath() + "' into table " + tableName2);
    // 3
    statement.execute("CREATE ROLE all_db1");
    statement.execute("GRANT ALL ON DATABASE " + DB1 + " TO ROLE all_db1");
    statement.execute("CREATE ROLE select_tb1");
    statement.execute("CREATE ROLE insert_tb1");
    statement.execute("CREATE ROLE insert_tb2");
    statement.execute("CREATE ROLE data_uri");
    statement.execute("USE " + DB2);
    statement.execute("GRANT INSERT ON TABLE " + tableName1 + " TO ROLE insert_tb1");
    statement.execute("GRANT INSERT ON TABLE " + tableName2 + " TO ROLE insert_tb2");
    statement.execute("GRANT ALL ON URI 'file://" + dataDir.getPath() + "' TO ROLE data_uri");
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("GRANT SELECT ON TABLE " + tableName1 + " TO ROLE select_tb1");
    statement.execute("GRANT ROLE all_db1, select_tb1, insert_tb1, insert_tb2, data_uri TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    // 4
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("load data local inpath '" + dataFile.getPath() + "' into table " + tableName1);
    // 5
    statement.execute("CREATE VIEW " + viewName1 + " (value) AS SELECT value from " + tableName1 + " LIMIT 10");
    statement.close();
    connection.close();
    // 7
    connection = context.createConnection(ADMIN1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.close();
    connection.close();
    // 8
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB2);
    statement.execute("INSERT OVERWRITE TABLE " + DB2 + "." + tableName2 + " SELECT * FROM " + DB1 + "." + tableName1);
    statement.close();
    connection.close();
}

6. TestDbEndToEnd#testEndToEnd1()

Project: sentry
Source File: TestDbEndToEnd.java
View license
/**
   * Steps:
   * 1. admin create a new experimental database
   * 2. admin create a new production database, create table, load data
   * 3. admin grant privilege all@'experimental database' to usergroup1
   * 4. user create table, load data in experimental DB
   * 5. user create view based on table in experimental DB
   * 6. admin create table (same name) in production DB
   * 7. admin grant [email protected] to group
   *    admin grant [email protected] to group
   * 8. user load data from experimental table to production table
   */
@Test
public void testEndToEnd1() throws Exception {
    String tableName1 = "tb_1";
    String tableName2 = "tb_2";
    String viewName1 = "view_1";
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    // 1
    statement.execute("DROP DATABASE IF EXISTS " + DB1 + " CASCADE");
    statement.execute("CREATE DATABASE " + DB1);
    // 2
    statement.execute("DROP DATABASE IF EXISTS " + DB2 + " CASCADE");
    statement.execute("CREATE DATABASE " + DB2);
    statement.execute("USE " + DB2);
    statement.execute("DROP TABLE IF EXISTS " + DB2 + "." + tableName1);
    statement.execute("DROP TABLE IF EXISTS " + DB2 + "." + tableName2);
    statement.execute("create table " + DB2 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("create table " + DB2 + "." + tableName2 + " (under_col int comment 'the under column', value string)");
    statement.execute("load data local inpath '" + dataFile.getPath() + "' into table " + tableName2);
    // 3
    statement.execute("CREATE ROLE all_db1");
    statement.execute("GRANT ALL ON DATABASE " + DB1 + " TO ROLE all_db1");
    statement.execute("CREATE ROLE select_tb1");
    statement.execute("CREATE ROLE insert_tb1");
    statement.execute("CREATE ROLE insert_tb2");
    statement.execute("CREATE ROLE data_uri");
    statement.execute("USE " + DB2);
    statement.execute("GRANT INSERT ON TABLE " + tableName1 + " TO ROLE insert_tb1");
    statement.execute("GRANT INSERT ON TABLE " + tableName2 + " TO ROLE insert_tb2");
    statement.execute("GRANT ALL ON URI 'file://" + dataDir.getPath() + "' TO ROLE data_uri");
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("GRANT SELECT ON TABLE " + tableName1 + " TO ROLE select_tb1");
    statement.execute("GRANT ROLE all_db1, select_tb1, insert_tb1, insert_tb2, data_uri TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    // 4
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.execute("load data local inpath '" + dataFile.getPath() + "' into table " + tableName1);
    // 5
    statement.execute("CREATE VIEW " + viewName1 + " (value) AS SELECT value from " + tableName1 + " LIMIT 10");
    statement.close();
    connection.close();
    // 7
    connection = context.createConnection(ADMIN1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB1);
    statement.execute("DROP TABLE IF EXISTS " + DB1 + "." + tableName1);
    statement.execute("create table " + DB1 + "." + tableName1 + " (under_col int comment 'the under column', value string)");
    statement.close();
    connection.close();
    // 8
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("USE " + DB2);
    statement.execute("INSERT OVERWRITE TABLE " + DB2 + "." + tableName2 + " SELECT * FROM " + DB1 + "." + tableName1);
    statement.close();
    connection.close();
}

7. OuterJoinTest#testdDerby2930()

Project: derby
Source File: OuterJoinTest.java
View license
public void testdDerby2930() throws Exception {
    // bug 2930, cross join under outer join
    Statement st = createStatement();
    ResultSet rs = null;
    SQLWarning sqlWarn = null;
    String[][] expRS;
    String[] expColNames;
    createTestObjects(st);
    st.executeUpdate("CREATE TABLE properties (" + "	name VARCHAR(50)," + "	value VARCHAR(200))");
    st.executeUpdate(" INSERT INTO properties VALUES ('businessName', " + "'Cloud 9 Cafe')");
    st.executeUpdate(" INSERT INTO properties VALUES " + "('lastReservationDate', '2001-12-31')");
    st.executeUpdate(" CREATE TABLE inventory (" + "	itemno INT NOT NULL PRIMARY KEY," + "	capacity INT" + ")");
    st.executeUpdate("INSERT INTO inventory VALUES (1, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (2, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (3, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (4, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (5, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (6, 4)");
    st.executeUpdate("INSERT INTO inventory VALUES (7, 4)");
    st.executeUpdate("INSERT INTO inventory VALUES (8, 4)");
    st.executeUpdate("INSERT INTO inventory VALUES (9, 4)");
    st.executeUpdate("INSERT INTO inventory VALUES (10, 4)");
    st.executeUpdate(" CREATE TABLE timeslots (slot TIME NOT NULL PRIMARY KEY)");
    st.executeUpdate("INSERT INTO timeslots VALUES('17:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('17:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('18:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('18:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('19:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('19:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('20:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('20:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('21:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('21:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('22:00:00')");
    st.executeUpdate(" CREATE TABLE reservations (" + "	itemno INT CONSTRAINT inventory_fk REFERENCES inventory," + "	slot TIME CONSTRAINT timeslots_fk REFERENCES timeslots," + "	resdate DATE NOT NULL," + "	name VARCHAR(100) NOT NULL," + "	quantity INT," + "	CONSTRAINT reservations_u UNIQUE(name, resdate))");
    st.executeUpdate(" INSERT INTO reservations VALUES(6, '17:00:00', " + "'2000-07-13', 'Williams', 4)");
    st.executeUpdate(" INSERT INTO reservations VALUES(7, '17:00:00', " + "'2000-07-13', 'Johnson',  4)");
    st.executeUpdate(" INSERT INTO reservations VALUES(8, '17:00:00', " + "'2000-07-13', 'Allen',    3)");
    st.executeUpdate(" INSERT INTO reservations VALUES(9, '17:00:00', " + "'2000-07-13', 'Dexmier',  4)");
    st.executeUpdate(" INSERT INTO reservations VALUES(1, '17:30:00', " + "'2000-07-13', 'Gates', 	 2)");
    st.executeUpdate(" INSERT INTO reservations VALUES(2, '17:30:00', " + "'2000-07-13', 'McNealy',  2)");
    st.executeUpdate(" INSERT INTO reservations VALUES(3, '17:30:00', " + "'2000-07-13', 'Hoffman',  1)");
    st.executeUpdate(" INSERT INTO reservations VALUES(4, '17:30:00', " + "'2000-07-13', 'Sippl',    2)");
    st.executeUpdate(" INSERT INTO reservations VALUES(6, '17:30:00', " + "'2000-07-13', 'Yang',     4)");
    st.executeUpdate(" INSERT INTO reservations VALUES(7, '17:30:00', " + "'2000-07-13', 'Meyers',   4)");
    rs = st.executeQuery(" select max(name), max(resdate) from inventory join " + "timeslots on inventory.capacity is not null " + "left outer join reservations on inventory.itemno = " + "reservations.itemno and reservations.slot = timeslots.slot");
    expColNames = new String[] { "1", "2" };
    JDBC.assertColumnNames(rs, expColNames);
    expRS = new String[][] { { "Yang", "2000-07-13" } };
    JDBC.assertFullResultSet(rs, expRS, true);
    rs = st.executeQuery(" select max(name), max(resdate) from inventory join " + "timeslots on inventory.capacity is not null " + "left outer join reservations on inventory.itemno = " + "reservations.itemno and reservations.slot = timeslots.slot");
    rs.next();
    // This causes the warning to be generated now.
    if (usingEmbedded()) {
        sqlWarn = rs.getWarnings();
        assertNotNull("Expected warning but found none", sqlWarn);
        //Warning 01003:Null values were eliminated from the
        //argument of a column function
        assertSQLState("01003", sqlWarn);
    }
    assertEquals("Yang", rs.getString(1));
    assertEquals("2000-07-13", rs.getString(2));
}

8. OuterJoinTest#testdDerby2931()

Project: derby
Source File: OuterJoinTest.java
View license
public void testdDerby2931() throws Exception {
    // bug 2931, cross join under outer join
    Statement st = createStatement();
    ResultSet rs = null;
    String[][] expRS;
    String[] expColNames;
    createTestObjects(st);
    st.executeUpdate("CREATE TABLE properties (" + "	name VARCHAR(50)," + "	value VARCHAR(200))");
    st.executeUpdate(" INSERT INTO properties VALUES ('businessName', " + "'Cloud 9 Cafe')");
    st.executeUpdate(" INSERT INTO properties VALUES " + "('lastReservationDate', '2001-12-31')");
    st.executeUpdate(" CREATE TABLE inventory (" + "	itemno INT NOT NULL PRIMARY KEY," + "	capacity INT" + ")");
    st.executeUpdate("INSERT INTO inventory VALUES (1, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (2, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (3, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (4, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (5, 2)");
    st.executeUpdate("INSERT INTO inventory VALUES (6, 4)");
    st.executeUpdate("INSERT INTO inventory VALUES (7, 4)");
    st.executeUpdate("INSERT INTO inventory VALUES (8, 4)");
    st.executeUpdate("INSERT INTO inventory VALUES (9, 4)");
    st.executeUpdate("INSERT INTO inventory VALUES (10, 4)");
    st.executeUpdate(" CREATE TABLE timeslots (slot TIME NOT NULL PRIMARY KEY)");
    st.executeUpdate("INSERT INTO timeslots VALUES('17:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('17:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('18:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('18:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('19:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('19:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('20:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('20:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('21:00:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('21:30:00')");
    st.executeUpdate("INSERT INTO timeslots VALUES('22:00:00')");
    st.executeUpdate(" CREATE TABLE reservations (" + "	itemno INT CONSTRAINT inventory_fk REFERENCES inventory," + "	slot TIME CONSTRAINT timeslots_fk REFERENCES timeslots," + "	resdate DATE NOT NULL," + "	name VARCHAR(100) NOT NULL," + "	quantity INT," + "	CONSTRAINT reservations_u UNIQUE(name, resdate))");
    st.executeUpdate(" INSERT INTO reservations VALUES(6, '17:00:00', " + "'2000-07-13', 'Williams', 4)");
    st.executeUpdate(" INSERT INTO reservations VALUES(7, '17:00:00', " + "'2000-07-13', 'Johnson',  4)");
    st.executeUpdate(" INSERT INTO reservations VALUES(8, '17:00:00', " + "'2000-07-13', 'Allen',    3)");
    st.executeUpdate(" INSERT INTO reservations VALUES(9, '17:00:00', " + "'2000-07-13', 'Dexmier',  4)");
    st.executeUpdate(" INSERT INTO reservations VALUES(1, '17:30:00', " + "'2000-07-13', 'Gates', 	 2)");
    st.executeUpdate(" INSERT INTO reservations VALUES(2, '17:30:00', " + "'2000-07-13', 'McNealy',  2)");
    st.executeUpdate(" INSERT INTO reservations VALUES(3, '17:30:00', " + "'2000-07-13', 'Hoffman',  1)");
    st.executeUpdate(" INSERT INTO reservations VALUES(4, '17:30:00', " + "'2000-07-13', 'Sippl',    2)");
    st.executeUpdate(" INSERT INTO reservations VALUES(6, '17:30:00', " + "'2000-07-13', 'Yang',     4)");
    st.executeUpdate(" INSERT INTO reservations VALUES(7, '17:30:00', " + "'2000-07-13', 'Meyers',   4)");
    // this query should return values from the 'slot' column 
    // (type date) but it seems to be returning integers!
    rs = st.executeQuery("select max(timeslots.slot) from inventory inner " + "join timeslots on inventory.capacity is not null " + "left outer join reservations on inventory.capacity " + "= reservations.itemno and reservations.slot = timeslots.slot");
    expColNames = new String[] { "1" };
    JDBC.assertColumnNames(rs, expColNames);
    expRS = new String[][] { { "22:00:00" } };
    JDBC.assertFullResultSet(rs, expRS, true);
}

9. SpaceTableTest#testReservedSpace()

Project: derby
Source File: SpaceTableTest.java
View license
public void testReservedSpace() throws SQLException, InterruptedException {
    Statement stmt = createStatement();
    // first ensure we're using the default.
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.pageSize', '4096')");
    setAutoCommit(false);
    // no reserved space set - default
    stmt.executeUpdate("create table foo_int (a int)");
    stmt.executeUpdate("create table foo_char (a char(100))");
    stmt.executeUpdate("create table foo_varchar (a varchar(32000))");
    // let the foo_longxxx get created at 32K (the default for long types)
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.pageSize', NULL)");
    stmt.executeUpdate("create table foo_longvarchar (a long varchar)");
    stmt.executeUpdate("create table foo_longvarbinary " + "(a long varchar for bit data)");
    // Back to 4K
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.pageSize', '4096')");
    stmt.executeUpdate("create table foo_bit (a char(100) for bit data)");
    stmt.executeUpdate("create table foo_varbinary " + "(a varchar(1000) for bit data)");
    JDBC.assertFullResultSet(doSpaceTableSelect2(), new String[][] { { "FOO_BIT", "4096" }, { "FOO_CHAR", "4096" }, { "FOO_INT", "4096" }, { "FOO_LONGVARBINARY", "32768" }, { "FOO_LONGVARCHAR", "32768" }, { "FOO_VARBINARY", "4096" }, { "FOO_VARCHAR", "4096" } });
    dropFooTables(stmt);
    // test with 65K reserved space
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.ReservedSpace', '65')");
    stmt.executeUpdate("create table foo_int (a int)");
    stmt.executeUpdate("create table foo_char (a char(100))");
    stmt.executeUpdate("create table foo_varchar (a varchar(32000))");
    // let the foo_longxxx get created at 32K (the default for long types)
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.pageSize', NULL)");
    stmt.executeUpdate("create table foo_longvarchar (a long varchar)");
    stmt.executeUpdate("create table foo_longvarbinary " + "(a long varchar for bit data)");
    // Back to 4K
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.pageSize', '4096')");
    stmt.executeUpdate("create table foo_bit (a char(100) for bit data)");
    stmt.executeUpdate("create table foo_varbinary " + "(a varchar(1000) for bit data)");
    // reset the reserved space to default (by setting property to NULL)
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.ReservedSpace', NULL)");
    JDBC.assertFullResultSet(doSpaceTableSelect2(), new String[][] { { "FOO_BIT", "4096" }, { "FOO_CHAR", "4096" }, { "FOO_INT", "4096" }, { "FOO_LONGVARBINARY", "32768" }, { "FOO_LONGVARCHAR", "32768" }, { "FOO_VARBINARY", "4096" }, { "FOO_VARCHAR", "4096" } });
    dropFooTables(stmt);
    // 8K pagesize
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.pageSize', '8192')");
    stmt.executeUpdate("create table foo_int (a int)");
    stmt.executeUpdate("create table foo_char (a char(100))");
    stmt.executeUpdate("create table foo_varchar (a varchar(32000))");
    stmt.executeUpdate("create table foo_longvarchar (a long varchar)");
    stmt.executeUpdate("create table foo_longvarbinary " + "(a long varchar for bit data)");
    stmt.executeUpdate("create table foo_bit (a char(100) for bit data)");
    stmt.executeUpdate("create table foo_varbinary " + "(a varchar(1000) for bit data)");
    JDBC.assertFullResultSet(doSpaceTableSelect2(), new String[][] { { "FOO_BIT", "8192" }, { "FOO_CHAR", "8192" }, { "FOO_INT", "8192" }, { "FOO_LONGVARBINARY", "8192" }, { "FOO_LONGVARCHAR", "8192" }, { "FOO_VARBINARY", "8192" }, { "FOO_VARCHAR", "8192" } });
    dropFooTables(stmt);
    // test with commit after setting pageSize at 4096
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" + "'derby.storage.pageSize', '4096')");
    commit();
    stmt.executeUpdate("create table foo_int (a int)");
    stmt.executeUpdate("create table foo_char (a char(100))");
    stmt.executeUpdate("create table foo_varchar (a varchar(32000))");
    stmt.executeUpdate("create table foo_longvarchar (a long varchar)");
    stmt.executeUpdate("create table foo_longvarbinary " + "(a long varchar for bit data)");
    stmt.executeUpdate("create table foo_bit (a char(100) for bit data)");
    stmt.executeUpdate("create table foo_varbinary " + "(a varchar(1000) for bit data)");
    JDBC.assertFullResultSet(doSpaceTableSelect2(), new String[][] { { "FOO_BIT", "4096" }, { "FOO_CHAR", "4096" }, { "FOO_INT", "4096" }, { "FOO_LONGVARBINARY", "4096" }, { "FOO_LONGVARCHAR", "4096" }, { "FOO_VARBINARY", "4096" }, { "FOO_VARCHAR", "4096" } });
    commit();
    dropFooTables(stmt);
    commit();
}

10. ResultSetTest#setUp()

Project: pgjdbc
Source File: ResultSetTest.java
View license
protected void setUp() throws Exception {
    con = TestUtil.openDB();
    Statement stmt = con.createStatement();
    TestUtil.createTable(con, "testrs", "id integer");
    stmt.executeUpdate("INSERT INTO testrs VALUES (1)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (2)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (3)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (4)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (6)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (9)");
    TestUtil.createTable(con, "teststring", "a text");
    stmt.executeUpdate("INSERT INTO teststring VALUES ('12345')");
    TestUtil.createTable(con, "testint", "a int");
    stmt.executeUpdate("INSERT INTO testint VALUES (12345)");
    TestUtil.createTable(con, "testbool", "a boolean");
    // TestUtil.createTable(con, "testbit", "a bit");
    TestUtil.createTable(con, "testboolstring", "a varchar(30)");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('true')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('false')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('t')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('f')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('1.0')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('0.0')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('TRUE')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('this is not true')");
    TestUtil.createTable(con, "testnumeric", "a numeric");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('1.0')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('0.0')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-1.0')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('1.2')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-2.5')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('99999.2')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('99999')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-99999.2')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-99999')");
    // Integer.MaxValue
    stmt.execute("INSERT INTO testnumeric VALUES('2147483647')");
    // Integer.MinValue
    stmt.execute("INSERT INTO testnumeric VALUES('-2147483648')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('2147483648')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-2147483649')");
    // Long.MaxValue
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('9223372036854775807')");
    // Long.MinValue
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-9223372036854775808')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('9223372036854775808')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-9223372036854775809')");
    TestUtil.createTable(con, "testpgobject", "id integer NOT NULL, d date, PRIMARY KEY (id)");
    stmt.execute("INSERT INTO testpgobject VALUES(1, '2010-11-3')");
    stmt.close();
}

11. ResultSetTest#before()

Project: pgjdbc-ng
Source File: ResultSetTest.java
View license
@Before
public void before() throws Exception {
    con = TestUtil.openDB();
    Statement stmt = con.createStatement();
    TestUtil.createTable(con, "testrs", "id integer");
    stmt.executeUpdate("INSERT INTO testrs VALUES (1)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (2)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (3)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (4)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (6)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (9)");
    TestUtil.createTable(con, "teststring", "a text");
    stmt.executeUpdate("INSERT INTO teststring VALUES ('12345')");
    TestUtil.createTable(con, "testbytes", "a bytea");
    stmt.executeUpdate("INSERT INTO testbytes VALUES (convert_to('12345', 'UTF8'))");
    TestUtil.createTable(con, "testint", "a int");
    stmt.executeUpdate("INSERT INTO testint VALUES (12345)");
    TestUtil.createTable(con, "testbool", "a boolean");
    // TestUtil.createTable(con, "testbit", "a bit");
    TestUtil.createTable(con, "testboolstring", "a varchar(30)");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('true')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('false')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('t')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('f')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('1.0')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('0.0')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('TRUE')");
    stmt.executeUpdate("INSERT INTO testboolstring VALUES('this is not true')");
    TestUtil.createTable(con, "testnumeric", "a numeric");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('1.0')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('0.0')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-1.0')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('1.2')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-2.5')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('99999.2')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('99999')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-99999.2')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-99999')");
    // Integer.MaxValue
    stmt.execute("INSERT INTO testnumeric VALUES('2147483647')");
    // Integer.MinValue
    stmt.execute("INSERT INTO testnumeric VALUES('-2147483648')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('2147483648')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-2147483649')");
    // Long.MaxValue
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('9223372036854775807')");
    // Long.MinValue
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-9223372036854775808')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('9223372036854775808')");
    stmt.executeUpdate("INSERT INTO testnumeric VALUES('-9223372036854775809')");
    stmt.close();
}

12. UpdateStatisticsTest#testDERBY5681()

Project: derby
Source File: UpdateStatisticsTest.java
View license
/**
     * Fixed DERBY-5681(When a foreign key constraint on a table is dropped,
     *  the associated statistics row for the conglomerate is not removed.)
     * @throws Exception
     */
public void testDERBY5681() throws Exception {
    // Helper object to obtain information about index statistics.
    IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
    Statement s = createStatement();
    //Test - primary key constraint
    s.executeUpdate("CREATE TABLE TEST_TAB_1 (c11 int not null," + "c12 int not null, c13 int)");
    stats.assertNoStatsTable("TEST_TAB_1");
    //Insert data into table with no constraint and there will be no stat
    // for that table at this point
    s.executeUpdate("INSERT INTO TEST_TAB_1 VALUES(1,1,1),(2,2,2)");
    s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_1', null)");
    stats.assertNoStatsTable("TEST_TAB_1");
    // Add primary key constraint to the table. With DERBY-3790 this won't
    // create a statistics entry, since the key consist of single column.
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "ADD CONSTRAINT TEST_TAB_1_PK_1 " + "PRIMARY KEY (c11)");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "DROP CONSTRAINT TEST_TAB_1_PK_1");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_1', null)");
    stats.assertNoStatsTable("TEST_TAB_1");
    //Add the primary key constraint back since it will be used by the next
    // test to create foreign key constraint
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "ADD CONSTRAINT TEST_TAB_1_PK_1 " + "PRIMARY KEY (c11)");
    stats.assertNoStatsTable("TEST_TAB_1");
    //Test - unique key constraint
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "ADD CONSTRAINT TEST_TAB_1_UNQ_1 " + "UNIQUE (c12)");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "DROP CONSTRAINT TEST_TAB_1_UNQ_1");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "DROP CONSTRAINT TEST_TAB_1_PK_1");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "ADD CONSTRAINT TEST_TAB_1_PK_1 " + "PRIMARY KEY (c11)");
    stats.assertNoStatsTable("TEST_TAB_1");
    //Test - non-unique index
    s.executeUpdate("CREATE INDEX TEST_TAB_1_NUNQ_1 ON TEST_TAB_1(c12)");
    stats.assertTableStats("TEST_TAB_1", 1);
    s.executeUpdate("DROP INDEX TEST_TAB_1_NUNQ_1");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "DROP CONSTRAINT TEST_TAB_1_PK_1");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "ADD CONSTRAINT TEST_TAB_1_PK_1 " + "PRIMARY KEY (c11)");
    stats.assertNoStatsTable("TEST_TAB_1");
    //Test - unique key constraint on nullable column & non-nullable column
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "ADD CONSTRAINT TEST_TAB_1_UNQ_2 " + "UNIQUE (c12, c13)");
    stats.assertTableStats("TEST_TAB_1", 2);
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "DROP CONSTRAINT TEST_TAB_1_UNQ_2");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "DROP CONSTRAINT TEST_TAB_1_PK_1");
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_1 " + "ADD CONSTRAINT TEST_TAB_1_PK_1 " + "PRIMARY KEY (c11)");
    stats.assertNoStatsTable("TEST_TAB_1");
    //Test - foreign key but no primary key constraint
    s.executeUpdate("CREATE TABLE TEST_TAB_3 (c31 int not null)");
    s.executeUpdate("INSERT INTO TEST_TAB_3 VALUES(1),(2)");
    s.executeUpdate("ALTER TABLE TEST_TAB_3 " + "ADD CONSTRAINT TEST_TAB_3_FK_1 " + "FOREIGN KEY(c31) REFERENCES TEST_TAB_1(c11)");
    stats.assertTableStats("TEST_TAB_3", 1);
    s.executeUpdate("ALTER TABLE TEST_TAB_3 " + "DROP CONSTRAINT TEST_TAB_3_FK_1");
    stats.assertNoStatsTable("TEST_TAB_3");
    //Test - foreign key and primary key constraint
    s.executeUpdate("CREATE TABLE TEST_TAB_2 (c21 int not null)");
    s.executeUpdate("INSERT INTO TEST_TAB_2 VALUES(1),(2)");
    s.executeUpdate("ALTER TABLE TEST_TAB_2 " + "ADD CONSTRAINT TEST_TAB_2_PK_1 " + "PRIMARY KEY (c21)");
    stats.assertNoStatsTable("TEST_TAB_2");
    // DERBY-5702 Add a foreign key constraint and now we should find one
    // row of statistics for TEST_TAB_2 (for the foreign key constraint).
    s.executeUpdate("ALTER TABLE TEST_TAB_2 " + "ADD CONSTRAINT TEST_TAB_2_FK_1 " + "FOREIGN KEY(c21) REFERENCES TEST_TAB_1(c11)");
    //DERBY-5702 Like primary key earlier, adding foreign key constraint
    // didn't automatically add a statistics row for it. Have to run update
    // statistics manually to get a row added for it's stat
    stats.assertNoStatsTable("TEST_TAB_2");
    s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
    stats.assertTableStats("TEST_TAB_2", 1);
    //Number of statistics row for TEST_TAB_1 will remain unchanged since
    // it has only primary key defined on it
    stats.assertNoStatsTable("TEST_TAB_1");
    s.executeUpdate("ALTER TABLE TEST_TAB_2 " + "DROP CONSTRAINT TEST_TAB_2_FK_1");
    //Dropping the foreign key constraint should cause the statistics row
    // for TEST_TAB_2 to be dropped as well.
    stats.assertNoStatsTable("TEST_TAB_2");
    s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
    stats.assertNoStatsTable("TEST_TAB_2");
    s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
    //After DERBY-4115 is implemented, we will see no statistics 
    // for TEST_TAB_2 after calling SYSCS_DROP_STATISTICS on it.
    stats.assertNoStatsTable("TEST_TAB_2");
    s.execute("drop table TEST_TAB_2");
    s.execute("drop table TEST_TAB_1");
    stats.release();
}

13. TestPageStoreCoverage#testTrim()

View license
private void testTrim() throws SQLException {
    Connection conn;
    conn = getConnection(URL);
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, name varchar)");
    stat.execute("create index idx_name on test(name, id)");
    stat.execute("insert into test " + "select x, x || space(10) from system_range(1, 20)");
    stat.execute("create table test2(id int primary key, name varchar)");
    stat.execute("create index idx_test2_name on test2(name, id)");
    stat.execute("insert into test2 " + "select x, x || space(10) from system_range(1, 20)");
    stat.execute("create table test3(id int primary key, name varchar)");
    stat.execute("create index idx_test3_name on test3(name, id)");
    stat.execute("insert into test3 " + "select x, x || space(3) from system_range(1, 3)");
    stat.execute("delete from test");
    stat.execute("checkpoint");
    stat.execute("checkpoint sync");
    stat.execute("shutdown compact");
    conn.close();
    conn = getConnection(URL);
    conn.createStatement().execute("drop all objects");
    conn.close();
}

14. DistinctTest#testDistinctInInsert()

Project: derby
Source File: DistinctTest.java
View license
public void testDistinctInInsert() throws SQLException {
    Statement s = createStatement();
    //create a table similar to t
    s.execute("delete from t");
    s.execute("create table insert_test (i int, s smallint, r real, f float, d date, t time, ts timestamp, c char(10), v varchar(20))");
    s.execute("insert into t values (1, 2, 3, 4, '1992-01-01', '19:01:01', '1992-01-01 19:01:01.000', 'hello', 'planet')");
    s.execute("insert into t values (1, 2, 3, 4, '1992-01-01', '19:01:01', '1992-01-01 19:01:01.000', 'hello', 'planet')");
    s.execute("insert into t values (2, 1, 4, 3, '1992-01-01', '19:01:01', '1992-01-01 19:01:01.000', 'hello', 'planet')");
    s.execute("insert into insert_test select distinct * from t");
    assertRowCount(2, s.executeQuery("select * from insert_test"));
    s.execute("delete from insert_test");
    s.execute("insert into insert_test select distinct * from t union select * from t");
    assertRowCount(2, s.executeQuery("select * from insert_test"));
    s.execute("delete from insert_test");
    s.execute("drop table insert_test");
    s.execute("delete from t");
    s.close();
}

15. SynonymTest#testSynonymsInSearchedDeleteDERBY4110()

Project: derby
Source File: SynonymTest.java
View license
/**
     * Test that synonyms are dereferenced properly for a searched DELETE.
     *
     * This test verifies that DERBY-4110 is fixed.
     */
public void testSynonymsInSearchedDeleteDERBY4110() throws SQLException {
    Statement st = createStatement();
    st.executeUpdate("create schema test1");
    st.executeUpdate("create schema test2");
    st.executeUpdate("create table test1.t1 ( id bigint not null )");
    st.executeUpdate("insert into test1.t1 values (1),(2)");
    st.executeUpdate("create synonym test2.t1 for test1.t1");
    st.executeUpdate("create unique index idx4110 on test1.t1 (id)");
    st.executeUpdate("set schema test2");
    // DERBY-4110 here
    st.executeUpdate("delete from t1 where id = 2");
    st.executeUpdate("drop synonym test2.t1");
    st.executeUpdate("drop table test1.t1");
    st.executeUpdate("drop schema test2 restrict");
    st.executeUpdate("drop schema test1 restrict");
}

16. GrantRevokeDDLTest#testAlterTablePrivilegesIntace()

Project: derby
Source File: GrantRevokeDDLTest.java
View license
// DERBY-5044 During alter table drop column, we recompile all the 
//  dependent trigger's action plans to see if they are dependent
//  on the column being dropped. The database may have been created
//  with authorization on and hence different actions might require
//  relevant privileges. This test will ensure that during the
//  recompile of trigger action, we will not loose the privilege
//  requirements for the triggers
public void testAlterTablePrivilegesIntace() throws Exception {
    Connection user1Connection = openUserConnection("user1");
    Statement st_user1Connection = user1Connection.createStatement();
    st_user1Connection.executeUpdate("create table user1.t11 (c111 int, c112 int, c113 int)");
    st_user1Connection.executeUpdate("create table user1.t12 (c121 int, c122 int)");
    st_user1Connection.executeUpdate("create table user1.t13 (c131 int, c132 int)");
    st_user1Connection.executeUpdate(" insert into user1.t11 values(1,2,3)");
    st_user1Connection.executeUpdate(" grant trigger on user1.t12 to user2");
    st_user1Connection.executeUpdate(" grant update(c112, c113) on user1.t11 to user2");
    st_user1Connection.executeUpdate(" grant select on user1.t11 to user2");
    st_user1Connection.executeUpdate(" grant insert on user1.t13 to user2");
    Connection user2Connection = openUserConnection("user2");
    Statement st_user2Connection = user2Connection.createStatement();
    st_user2Connection.executeUpdate("create trigger tr1t12 after insert on user1.t12 " + "for each row mode db2sql " + "update user1.t11 set c112=222");
    st_user2Connection.executeUpdate("create trigger tr2t12 after insert on user1.t12 " + "for each row mode db2sql " + "insert into user1.t13(c131, c132) " + "select c111, c113 from user1.t11");
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t11"), new String[][] { { "1", "2", "3" } });
    JDBC.assertEmpty(st_user1Connection.executeQuery(" select * from user1.t13"));
    st_user1Connection.executeUpdate(" insert into user1.t12 values(91,91)");
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t11"), new String[][] { { "1", "222", "3" } });
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t13"), new String[][] { { "1", "3" } });
    st_user1Connection.executeUpdate("delete from user1.t11");
    st_user1Connection.executeUpdate("delete from user1.t13");
    st_user1Connection.executeUpdate(" insert into user1.t11 values(1,2,3)");
    assertStatementError("X0Y25", st_user1Connection, "alter table t11 drop column c112 restrict");
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t11"), new String[][] { { "1", "2", "3" } });
    JDBC.assertEmpty(st_user1Connection.executeQuery(" select * from user1.t13"));
    st_user1Connection.executeUpdate(" insert into user1.t12 values(92,92)");
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t11"), new String[][] { { "1", "222", "3" } });
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t13"), new String[][] { { "1", "3" } });
    st_user1Connection.executeUpdate("delete from user1.t11");
    st_user1Connection.executeUpdate("delete from user1.t13");
    st_user1Connection.executeUpdate(" insert into user1.t11 values(1,2,3)");
    st_user1Connection.executeUpdate("alter table t11 drop column c112");
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t11"), new String[][] { { "1", "3" } });
    JDBC.assertEmpty(st_user1Connection.executeQuery(" select * from user1.t13"));
    st_user1Connection.executeUpdate(" insert into user1.t12 values(93,93)");
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t11"), new String[][] { { "1", "3" } });
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t13"), new String[][] { { "1", "3" } });
    st_user1Connection.executeUpdate("delete from user1.t11");
    st_user1Connection.executeUpdate("delete from user1.t13");
    st_user1Connection.executeUpdate(" insert into user1.t11 values(1,3)");
    st_user1Connection.executeUpdate("revoke insert on table user1.t13 from user2");
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t11"), new String[][] { { "1", "3" } });
    JDBC.assertEmpty(st_user1Connection.executeQuery(" select * from user1.t13"));
    st_user1Connection.executeUpdate(" insert into user1.t12 values(94,94)");
    JDBC.assertFullResultSet(st_user1Connection.executeQuery(" select * from user1.t11"), new String[][] { { "1", "3" } });
    JDBC.assertEmpty(st_user1Connection.executeQuery(" select * from user1.t13"));
    st_user1Connection.executeUpdate("drop table user1.t11");
    st_user1Connection.executeUpdate("drop table user1.t12");
    st_user1Connection.executeUpdate("drop table user1.t13");
    st_user1Connection.executeUpdate("drop schema user1 restrict");
    st_user2Connection.executeUpdate("drop schema user2 restrict");
}

17. CallableStatementTest#tearDown()

View license
@After
public void tearDown() throws Exception {
    Statement stmt = con.createStatement();
    TestUtil.dropTable(con, "int_table");
    stmt.execute("drop FUNCTION testspg__getString (varchar);");
    stmt.execute("drop FUNCTION testspg__getDouble (float);");
    stmt.execute("drop FUNCTION testspg__getVoid(float);");
    stmt.execute("drop FUNCTION testspg__getInt (int);");
    stmt.execute("drop FUNCTION testspg__getShort(int2)");
    stmt.execute("drop FUNCTION testspg__getNumeric (numeric);");
    stmt.execute("drop FUNCTION testspg__getNumericWithoutArg ();");
    stmt.execute("DROP FUNCTION testspg__getarray();");
    stmt.execute("DROP FUNCTION testspg__raisenotice();");
    stmt.execute("DROP FUNCTION testspg__insertInt(int);");
    stmt.close();
    TestUtil.closeDB(con);
}

18. TestCompatibility#testHsqlDb()

Project: ThriftyPaxos
Source File: TestCompatibility.java
View license
private void testHsqlDb() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("set mode hsqldb");
    testLog(Math.log(10), stat);
    stat.execute("DROP TABLE TEST IF EXISTS; " + "CREATE TABLE TEST(ID INT PRIMARY KEY); ");
    stat.execute("CALL CURRENT_TIME");
    stat.execute("CALL CURRENT_TIMESTAMP");
    stat.execute("CALL CURRENT_DATE");
    stat.execute("CALL SYSDATE");
    stat.execute("CALL TODAY");
    stat.execute("DROP TABLE TEST IF EXISTS");
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("INSERT INTO TEST VALUES(1)");
    PreparedStatement prep = conn.prepareStatement("SELECT LIMIT ? 1 ID FROM TEST");
    prep.setInt(1, 2);
    prep.executeQuery();
    stat.execute("DROP TABLE TEST IF EXISTS");
}

19. TestMVTableEngine#testReferentialIntegrity()

Project: ThriftyPaxos
Source File: TestMVTableEngine.java
View license
private void testReferentialIntegrity() throws Exception {
    FileUtils.deleteRecursive(getBaseDir(), true);
    Connection conn;
    Statement stat;
    conn = getConnection("mvstore;MV_STORE=TRUE");
    stat = conn.createStatement();
    stat.execute("create table test(id int, parent int " + "references test(id) on delete cascade)");
    stat.execute("insert into test values(0, 0)");
    stat.execute("delete from test");
    stat.execute("drop table test");
    stat.execute("create table parent(id int, name varchar)");
    stat.execute("create table child(id int, parentid int, " + "foreign key(parentid) references parent(id))");
    stat.execute("insert into parent values(1, 'mary'), (2, 'john')");
    stat.execute("insert into child values(10, 1), (11, 1), (20, 2), (21, 2)");
    stat.execute("update parent set name = 'marc' where id = 1");
    stat.execute("merge into parent key(id) values(1, 'marcy')");
    stat.execute("drop table parent, child");
    stat.execute("create table test(id identity, parent bigint, " + "foreign key(parent) references(id))");
    stat.execute("insert into test values(0, 0), (1, NULL), " + "(2, 1), (3, 3), (4, 3)");
    stat.execute("drop table test");
    stat.execute("create table parent(id int)");
    stat.execute("create table child(pid int)");
    stat.execute("insert into parent values(1)");
    stat.execute("insert into child values(2)");
    try {
        stat.execute("alter table child add constraint cp " + "foreign key(pid) references parent(id)");
        fail();
    } catch (SQLException e) {
        assertEquals(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1, e.getErrorCode());
    }
    stat.execute("update child set pid=1");
    stat.execute("drop table child, parent");
    stat.execute("create table parent(id int)");
    stat.execute("create table child(pid int)");
    stat.execute("insert into parent values(1)");
    stat.execute("insert into child values(2)");
    try {
        stat.execute("alter table child add constraint cp " + "foreign key(pid) references parent(id)");
        fail();
    } catch (SQLException e) {
        assertEquals(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_PARENT_MISSING_1, e.getErrorCode());
    }
    stat.execute("drop table child, parent");
    stat.execute("create table test(id identity, parent bigint, " + "foreign key(parent) references(id))");
    stat.execute("insert into test values(0, 0), (1, NULL), " + "(2, 1), (3, 3), (4, 3)");
    stat.execute("drop table test");
    stat.execute("create table parent(id int, x int)");
    stat.execute("insert into parent values(1, 2)");
    stat.execute("create table child(id int references parent(id)) as select 1");
    conn.close();
}

20. DeclareGlobalTempTableJavaTest#testConstraintsAllowedOnSESSIONPhysicalTables()

View license
/**
     * Test the constraints allowed on physical tables in SESSION schema.
     * 
     * @throws SQLException
     */
public void testConstraintsAllowedOnSESSIONPhysicalTables() throws SQLException {
    Statement s = createStatement();
    try {
        s.executeUpdate("CREATE SCHEMA SESSION");
    } catch (SQLException e) {
        assertSQLState("X0Y68", e);
    }
    // primary key constraints allowed on SESSION physical tables
    s.executeUpdate("CREATE TABLE SESSION.t1(c21 int not null, constraint pk primary key (c21))");
    // unique constraints allowed on SESSION physical tables
    s.executeUpdate("CREATE TABLE SESSION.t2(c21 int not null unique)");
    // check constraints allowed on SESSION physical tables
    s.executeUpdate("CREATE TABLE SESSION.t3(c21 int check (c21 > 0))");
    // foreign key constraints allowed on SESSION physical tables
    s.executeUpdate("CREATE TABLE t4(c11 int not null unique)");
    s.executeUpdate("CREATE TABLE SESSION.t5(c21 int references t4(c11))");
    // cleanUp
    s.executeUpdate("DROP TABLE SESSION.t1");
    s.executeUpdate("DROP TABLE SESSION.t2");
    s.executeUpdate("DROP TABLE SESSION.t3");
    s.executeUpdate("DROP TABLE SESSION.t5");
    s.executeUpdate("DROP TABLE t4");
}

21. DistinctTest#testBasicDistinct()

Project: derby
Source File: DistinctTest.java
View license
public void testBasicDistinct() throws SQLException {
    Statement s = createStatement();
    s.execute("create table userInt (u integer)");
    s.execute("insert into userInt values (123)");
    s.execute("insert into userInt values (123)");
    s.execute("insert into userInt values (456)");
    s.execute("insert into userInt values (null)");
    s.execute("create table sqlInt (i int not null)");
    s.execute("insert into sqlInt values(123)");
    assertRowCount(2, s.executeQuery("select distinct u from userInt where u is not null"));
    assertRowCount(3, s.executeQuery("select u from userInt where u is not null"));
    try {
        s.executeQuery("select distinct i from sqlInt where i = (select distinct u from userInt)");
    } catch (SQLException e) {
        assertSQLState("21000", e);
    }
    s.execute("drop table userInt");
    s.execute("drop table sqlInt");
    s.close();
}

22. TestDbEndToEnd#testNonDefault()

View license
@Test
public void testNonDefault() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("DROP DATABASE IF EXISTS " + DB1 + " CASCADE");
    statement.execute("CREATE database " + DB1);
    statement.execute("USE " + DB1);
    statement.execute("CREATE TABLE t1 (c1 string)");
    statement.execute("CREATE ROLE user_role");
    statement.execute("GRANT SELECT ON TABLE t1 TO ROLE user_role");
    statement.execute("GRANT ROLE user_role TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("SELECT * FROM " + DB1 + ".t1");
    statement.close();
    connection.close();
}

23. TestDbEndToEnd#testNonDefault()

Project: sentry
Source File: TestDbEndToEnd.java
View license
@Test
public void testNonDefault() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("DROP DATABASE IF EXISTS " + DB1 + " CASCADE");
    statement.execute("CREATE database " + DB1);
    statement.execute("USE " + DB1);
    statement.execute("CREATE TABLE t1 (c1 string)");
    statement.execute("CREATE ROLE user_role");
    statement.execute("GRANT SELECT ON TABLE t1 TO ROLE user_role");
    statement.execute("GRANT ROLE user_role TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("SELECT * FROM " + DB1 + ".t1");
    statement.close();
    connection.close();
}

24. TestGrantUserToRole#prepareTestData()

Project: sentry
Source File: TestGrantUserToRole.java
View license
private void prepareTestData() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("CREATE ROLE " + ROLENAME1);
    statement.execute("CREATE ROLE " + ROLENAME2);
    statement.execute("CREATE ROLE " + ROLENAME3);
    // grant role to groups and users as the following:
    statement.execute("GRANT ROLE " + ROLENAME1 + " TO GROUP " + USERGROUP1);
    statement.execute("GRANT ROLE " + ROLENAME2 + " TO GROUP " + USERGROUP2);
    statement.execute("GRANT ROLE " + ROLENAME3 + " TO USER " + USER2_1);
    statement.execute("GRANT ROLE " + ROLENAME2 + " TO USER " + USER3_1);
    statement.execute("GRANT ROLE " + ROLENAME2 + " TO USER " + USER4_1);
    statement.execute("GRANT ROLE " + ROLENAME3 + " TO USER " + USER4_1);
    statement.close();
    connection.close();
}

25. TestDbEndToEnd#testNonDefault()

Project: sentry
Source File: TestDbEndToEnd.java
View license
@Test
public void testNonDefault() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("DROP DATABASE IF EXISTS " + DB1 + " CASCADE");
    statement.execute("CREATE database " + DB1);
    statement.execute("USE " + DB1);
    statement.execute("CREATE TABLE t1 (c1 string)");
    statement.execute("CREATE ROLE user_role");
    statement.execute("GRANT SELECT ON TABLE t1 TO ROLE user_role");
    statement.execute("GRANT ROLE user_role TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    statement.execute("SELECT * FROM " + DB1 + ".t1");
    statement.close();
    connection.close();
}

26. TestLob#testLobVariable()

Project: ThriftyPaxos
Source File: TestLob.java
View license
private void testLobVariable() throws SQLException {
    deleteDb("lob");
    Connection conn = reconnect(null);
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT, DATA CLOB)");
    stat.execute("INSERT INTO TEST VALUES(1, SPACE(100000))");
    stat.execute("SET @TOTAL = SELECT DATA FROM TEST WHERE ID=1");
    stat.execute("DROP TABLE TEST");
    stat.execute("CALL @TOTAL LIKE '%X'");
    stat.execute("CREATE TABLE TEST(ID INT, DATA CLOB)");
    stat.execute("INSERT INTO TEST VALUES(1, @TOTAL)");
    stat.execute("INSERT INTO TEST VALUES(2, @TOTAL)");
    stat.execute("DROP TABLE TEST");
    stat.execute("CALL @TOTAL LIKE '%X'");
    conn.close();
}

27. AutoIncrementTest#testadditionalSysCol()

Project: derby
Source File: AutoIncrementTest.java
View license
public void testadditionalSysCol() throws Exception {
    ResultSet rs;
    Statement s = createStatement();
    s.executeUpdate("insert into t1_1 (x) values (1)");
    rs = s.executeQuery("values IDENTITY_VAL_LOCAL()");
    String[][] expectedRows = new String[][] { { "1" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("insert into t1_1 (x) values (2)");
    rs = s.executeQuery("values IDENTITY_VAL_LOCAL()");
    expectedRows = new String[][] { { "2" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("insert into t2_1 (x) values (1)");
    rs = s.executeQuery("values IDENTITY_VAL_LOCAL()");
    expectedRows = new String[][] { { "0" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("insert into t1_2 (x) values (1),(2),(3),(4),(5)");
    s.executeUpdate("insert into t2_2 (x) values (1),(2),(3),(4),(5)");
    s.executeUpdate("insert into t3_2 (x) values (1),(2),(3),(4),(5)");
    rs = s.executeQuery("select a.x, s1, s2, s0 from t1_2 a join t2_2 b on a.x = b.x join t3_2 c on a.x = c.x order by a.x");
    expectedRows = new String[][] { { "1", "1", "2", "0" }, { "2", "2", "3", "1" }, { "3", "3", "4", "2" }, { "4", "4", "5", "3" }, { "5", "5", "6", "4" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs = s.executeQuery("values IDENTITY_VAL_LOCAL()");
    expectedRows = new String[][] { { "0" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("insert into trigtest (lvl) values (0)");
    s.executeUpdate("insert into trigtest (lvl) values (1),(2)");
    s.executeUpdate("insert into trigtest (lvl) values (3),(4)");
    s.executeUpdate("insert into trigtest (lvl) values (5),(6)");
    s.executeUpdate("insert into trigtest (lvl) values (7),(8)");
    rs = s.executeQuery("select * from trigtest order by s1");
    expectedRows = new String[][] { { "1", "0" }, { "2", "1" }, { "3", "2" }, { "4", "3" }, { "5", "4" }, { "6", "5" }, { "7", "6" }, { "8", "7" }, { "9", "8" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs = s.executeQuery("select count(*) from t1_2");
    expectedRows = new String[][] { { "5" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("delete from t1_2");
    s.executeUpdate("delete from t2_2");
    s.executeUpdate("delete from t3_2");
    s.executeUpdate("insert into t1_2 (x) values (1),(2),(3),(4),(5)");
    s.executeUpdate("insert into t2_2 (x) values (1),(2),(3),(4),(5)");
    s.executeUpdate("insert into t3_2 (x) values (1),(2),(3),(4),(5)");
    rs = s.executeQuery("select a.x, s1, s2, s0 from t1_2 a join t2_2 b on a.x = b.x join t3_2 c on a.x = c.x order by a.x");
    expectedRows = new String[][] { { "1", "6", "7", "5" }, { "2", "7", "8", "6" }, { "3", "8", "9", "7" }, { "4", "9", "10", "8" }, { "5", "10", "11", "9" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("insert into t1_2 (x) values (6)");
    s.executeUpdate("insert into t2_2 (x) values (6)");
    s.executeUpdate("insert into t3_2 (x) values (6)");
    rs = s.executeQuery("select a.x, s1, s2, s0 from t1_2  a join t2_2 b on a.x = b.x join t3_2 c on a.x = c.x order by a.x");
    expectedRows = new String[][] { { "1", "6", "7", "5" }, { "2", "7", "8", "6" }, { "3", "8", "9", "7" }, { "4", "9", "10", "8" }, { "5", "10", "11", "9" }, { "6", "11", "12", "10" } };
    rs = s.executeQuery("values IDENTITY_VAL_LOCAL()");
    expectedRows = new String[][] { { "10" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("delete from t1_2");
    s.executeUpdate("delete from t2_2");
    s.executeUpdate("delete from t3_2");
    s.executeUpdate("insert into t1_2 (x) values (1),(2),(3),(4),(5)");
    s.executeUpdate("insert into t2_2 (x) values (1),(2),(3),(4),(5)");
    s.executeUpdate("insert into t3_2 (x) values (1),(2),(3),(4),(5)");
    rs = s.executeQuery("select a.x, s1, s2, s0 from t1_2 a join t2_2 b on a.x = b.x join t3_2 c on a.x = c.x order by a.x");
    expectedRows = new String[][] { { "1", "12", "13", "11" }, { "2", "13", "14", "12" }, { "3", "14", "15", "13" }, { "4", "15", "16", "14" }, { "5", "16", "17", "15" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("insert into t1_2 (x) values (6)");
    s.executeUpdate("insert into t2_2 (x) values (6)");
    s.executeUpdate("insert into t3_2 (x) values (6)");
    rs = s.executeQuery("select a.x, s1, s2, s0 from t1_2 a join t2_2 b on a.x = b.x join t3_2 c on a.x = c.x order by a.x");
    expectedRows = new String[][] { { "1", "12", "13", "11" }, { "2", "13", "14", "12" }, { "3", "14", "15", "13" }, { "4", "15", "16", "14" }, { "5", "16", "17", "15" }, { "6", "17", "18", "16" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs = s.executeQuery("values IDENTITY_VAL_LOCAL()");
    expectedRows = new String[][] { { "16" } };
    JDBC.assertFullResultSet(rs, expectedRows);
}

28. ProcedureInTriggerTest#testTriggerReadsSql()

Project: derby
Source File: ProcedureInTriggerTest.java
View license
/**
     * Test triggers for procedures that READ SQL DATA
     * @throws SQLException
     */
public void testTriggerReadsSql() throws SQLException {
    Statement s = createStatement();
    //--- create a row in t1 for use in select in the procedure
    s.execute("insert into t1 values (1, 'one')");
    s.execute("create trigger after_stmt_trig_reads_sql AFTER insert on t2 for each STATEMENT call proc_reads_sql(1)");
    //--- insert 2 rows. check that trigger is fired - procedure should be called once
    selectRowsCount = 0;
    s.execute("insert into t2 values (1,2), (2,4)");
    checkAndResetSelectRowsCount(1);
    //--- check inserts are successful
    ResultSet rs = s.executeQuery("select * from t2");
    JDBC.assertFullResultSet(rs, new String[][] { { "1", "2" }, { "2", "4" } });
    s.execute("drop trigger after_stmt_trig_reads_sql");
    s.execute("create trigger before_row_trig_reads_sql no cascade BEFORE delete on t2 for each ROW call proc_reads_sql(1)");
    //--- delete 2 rows. check that trigger is fired - procedure should be called twice
    s.execute("delete from t2");
    checkAndResetSelectRowsCount(2);
    // --- check delete is successful
    rs = s.executeQuery("select * from t2");
    JDBC.assertEmpty(rs);
    s.execute("drop trigger before_row_trig_reads_sql");
    //--- empty t1
    s.execute("delete from t1");
    s.close();
}

29. TestColumnEndToEnd#testBasic()

View license
@Test
public void testBasic() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("CREATE database " + DB1);
    statement.execute("USE " + DB1);
    statement.execute("CREATE TABLE t1 (c1 string)");
    statement.execute("CREATE ROLE user_role");
    statement.execute("GRANT SELECT ON TABLE t1 TO ROLE user_role");
    statement.execute("GRANT ROLE user_role TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    context.assertSentryException(statement, "CREATE ROLE r2", SentryAccessDeniedException.class.getSimpleName());
    statement.execute("SELECT * FROM " + DB1 + ".t1");
    statement.close();
    connection.close();
}

30. AwtPoiPersistenceManager#createTables()

View license
/**
     * DB open created a new file, so let's create its tables.
     */
private void createTables() throws SQLException {
    Statement stmt = this.conn.createStatement();
    stmt.execute(DbConstants.DROP_METADATA_STATEMENT);
    stmt.execute(DbConstants.DROP_INDEX_STATEMENT);
    stmt.execute(DbConstants.DROP_DATA_STATEMENT);
    stmt.execute(DbConstants.DROP_CATEGORIES_STATEMENT);
    stmt.execute(DbConstants.CREATE_CATEGORIES_STATEMENT);
    stmt.execute(DbConstants.CREATE_DATA_STATEMENT);
    stmt.execute(DbConstants.CREATE_INDEX_STATEMENT);
    stmt.execute(DbConstants.CREATE_METADATA_STATEMENT);
    stmt.close();
}

31. CallableStmtTest#tearDown()

Project: pgjdbc
Source File: CallableStmtTest.java
View license
protected void tearDown() throws Exception {
    Statement stmt = con.createStatement();
    TestUtil.dropTable(con, "int_table");
    stmt.execute("drop FUNCTION testspg__getString (varchar);");
    stmt.execute("drop FUNCTION testspg__getDouble (float);");
    if (TestUtil.haveMinimumServerVersion(con, "7.3")) {
        stmt.execute("drop FUNCTION testspg__getVoid(float);");
    }
    stmt.execute("drop FUNCTION testspg__getInt (int);");
    stmt.execute("drop FUNCTION testspg__getShort(int2)");
    stmt.execute("drop FUNCTION testspg__getNumeric (numeric);");
    stmt.execute("drop FUNCTION testspg__getNumericWithoutArg ();");
    stmt.execute("DROP FUNCTION testspg__getarray();");
    stmt.execute("DROP FUNCTION testspg__raisenotice();");
    stmt.execute("DROP FUNCTION testspg__insertInt(int);");
    TestUtil.closeDB(con);
}

32. RefCursorTest#setUp()

Project: pgjdbc
Source File: RefCursorTest.java
View license
protected void setUp() throws Exception {
    // this is the same as the ResultSet setup.
    con = TestUtil.openDB();
    Statement stmt = con.createStatement();
    TestUtil.createTable(con, "testrs", "id integer primary key");
    stmt.executeUpdate("INSERT INTO testrs VALUES (1)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (2)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (3)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (4)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (6)");
    stmt.executeUpdate("INSERT INTO testrs VALUES (9)");
    // Create the functions.
    stmt.execute("CREATE OR REPLACE FUNCTION testspg__getRefcursor () RETURNS refcursor AS '" + "declare v_resset refcursor; begin open v_resset for select id from testrs order by id; " + "return v_resset; end;' LANGUAGE plpgsql;");
    stmt.execute("CREATE OR REPLACE FUNCTION testspg__getEmptyRefcursor () RETURNS refcursor AS '" + "declare v_resset refcursor; begin open v_resset for select id from testrs where id < 1 order by id; " + "return v_resset; end;' LANGUAGE plpgsql;");
    stmt.close();
    con.setAutoCommit(false);
}

33. TestColumnEndToEnd#testBasic()

Project: sentry
Source File: TestColumnEndToEnd.java
View license
@Test
public void testBasic() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("CREATE database " + DB1);
    statement.execute("USE " + DB1);
    statement.execute("CREATE TABLE t1 (c1 string)");
    statement.execute("CREATE ROLE user_role");
    statement.execute("GRANT SELECT ON TABLE t1 TO ROLE user_role");
    statement.execute("GRANT ROLE user_role TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    context.assertSentryException(statement, "CREATE ROLE r2", SentryAccessDeniedException.class.getSimpleName());
    statement.execute("SELECT * FROM " + DB1 + ".t1");
    statement.close();
    connection.close();
}

34. TestColumnEndToEnd#testBasic()

Project: sentry
Source File: TestColumnEndToEnd.java
View license
@Test
public void testBasic() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("CREATE database " + DB1);
    statement.execute("USE " + DB1);
    statement.execute("CREATE TABLE t1 (c1 string)");
    statement.execute("CREATE ROLE user_role");
    statement.execute("GRANT SELECT ON TABLE t1 TO ROLE user_role");
    statement.execute("GRANT ROLE user_role TO GROUP " + USERGROUP1);
    statement.close();
    connection.close();
    connection = context.createConnection(USER1_1);
    statement = context.createStatement(connection);
    context.assertSentryException(statement, "CREATE ROLE r2", SentryAccessDeniedException.class.getSimpleName());
    statement.execute("SELECT * FROM " + DB1 + ".t1");
    statement.close();
    connection.close();
}

35. TestCases#testConstraintReconnect()

Project: ThriftyPaxos
Source File: TestCases.java
View license
private void testConstraintReconnect() throws SQLException {
    trace("testConstraintReconnect");
    deleteDb("cases");
    Connection conn = getConnection("cases");
    Statement stat = conn.createStatement();
    stat.execute("drop table if exists parent");
    stat.execute("drop table if exists child");
    stat.execute("create table parent(id int)");
    stat.execute("create table child(c_id int, p_id int, " + "foreign key(p_id) references parent(id))");
    stat.execute("insert into parent values(1), (2)");
    stat.execute("insert into child values(1, 1)");
    stat.execute("insert into child values(2, 2)");
    stat.execute("insert into child values(3, 2)");
    stat.execute("delete from child");
    conn.close();
    conn = getConnection("cases");
    conn.close();
}

36. TestLob#testLobCopy()

Project: ThriftyPaxos
Source File: TestLob.java
View license
private void testLobCopy() throws SQLException {
    deleteDb("lob");
    Connection conn = reconnect(null);
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int, data clob)");
    stat.execute("insert into test values(1, space(1000));");
    stat.execute("insert into test values(2, space(10000));");
    stat.execute("create table test2(id int, data clob);");
    stat.execute("insert into test2 select * from test;");
    stat.execute("drop table test;");
    stat.execute("select * from test2;");
    stat.execute("update test2 set id=id;");
    stat.execute("select * from test2;");
    conn.close();
}

37. TestRunscript#testEncoding()

Project: ThriftyPaxos
Source File: TestRunscript.java
View license
private void testEncoding() throws SQLException {
    deleteDb("runscript");
    Connection conn;
    Statement stat;
    conn = getConnection("runscript");
    stat = conn.createStatement();
    stat.execute("create table \"tö\"(id int)");
    stat.execute("script to '" + getBaseDir() + "/backup.sql'");
    stat.execute("drop all objects");
    stat.execute("runscript from '" + getBaseDir() + "/backup.sql'");
    stat.execute("select * from \"tö\"");
    stat.execute("script to '" + getBaseDir() + "/backup.sql' charset 'UTF-8'");
    stat.execute("drop all objects");
    stat.execute("runscript from '" + getBaseDir() + "/backup.sql' charset 'UTF-8'");
    stat.execute("select * from \"tö\"");
    conn.close();
    FileUtils.delete(getBaseDir() + "/backup.sql");
}

38. TestTempTables#testTransactionalTemp()

Project: ThriftyPaxos
Source File: TestTempTables.java
View license
private void testTransactionalTemp() throws SQLException {
    deleteDb("tempTables");
    Connection conn = getConnection("tempTables");
    conn.setAutoCommit(false);
    Statement stat = conn.createStatement();
    ResultSet rs;
    stat.execute("create table test(id int primary key)");
    stat.execute("insert into test values(1)");
    stat.execute("commit");
    stat.execute("insert into test values(2)");
    stat.execute("create local temporary table temp(" + "id int primary key, name varchar, constraint x index(name)) transactional");
    stat.execute("insert into temp values(3, 'test')");
    stat.execute("rollback");
    rs = stat.executeQuery("select * from test");
    assertTrue(rs.next());
    assertFalse(rs.next());
    stat.execute("drop table test");
    stat.execute("drop table temp");
    conn.close();
}

39. TestStatement#testIdentityMerge()

Project: ThriftyPaxos
Source File: TestStatement.java
View license
private void testIdentityMerge() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("drop table if exists test1");
    stat.execute("create table test1(id identity, x int)");
    stat.execute("drop table if exists test2");
    stat.execute("create table test2(id identity, x int)");
    stat.execute("merge into test1(x) key(x) values(5)");
    ResultSet keys;
    keys = stat.getGeneratedKeys();
    keys.next();
    assertEquals(1, keys.getInt(1));
    stat.execute("insert into test2(x) values(10), (11), (12)");
    stat.execute("merge into test1(x) key(x) values(5)");
    keys = stat.getGeneratedKeys();
    assertFalse(keys.next());
    stat.execute("merge into test1(x) key(x) values(6)");
    keys = stat.getGeneratedKeys();
    keys.next();
    assertEquals(2, keys.getInt(1));
    stat.execute("drop table test1, test2");
}

40. TestPageStoreCoverage#testLongTransaction()

View license
private void testLongTransaction() throws SQLException {
    Connection conn;
    conn = getConnection(URL);
    Statement stat = conn.createStatement();
    stat.execute("create table test(id identity, name varchar)");
    conn.setAutoCommit(false);
    stat.execute("insert into test " + "select null, space(10) from system_range(1, 10)");
    Connection conn2;
    conn2 = getConnection(URL);
    Statement stat2 = conn2.createStatement();
    stat2.execute("checkpoint");
    // large transaction
    stat2.execute("create table test2(id identity, name varchar)");
    stat2.execute("create index idx_test2_name on test2(name)");
    stat2.execute("insert into test2 " + "select null, x || space(10000) from system_range(1, 100)");
    stat2.execute("drop table test2");
    conn2.close();
    stat.execute("drop table test");
    conn.close();
}

41. CheckToursDBTest#tearDown()

Project: derby
Source File: CheckToursDBTest.java
View license
/**
    * Tear-down the fixture by removing the tables
    * @throws Exception
    */
protected void tearDown() throws Exception {
    Statement st = createStatement();
    st.execute("DROP TABLE AIRLINES");
    st.execute("DROP TABLE CITIES");
    st.execute("DROP TABLE COUNTRIES");
    st.execute("DROP TABLE FLIGHTAVAILABILITY");
    st.execute("DROP TABLE FLIGHTS");
    st.execute("DROP TABLE MAPS");
    st.execute("DROP TABLE FLIGHTS_HISTORY");
    st.close();
    commit();
    super.tearDown();
}

42. AutoIncrementTest#testSchema()

Project: derby
Source File: AutoIncrementTest.java
View license
public void testSchema() throws Exception {
    //-- insert into multiple tables in different schema names with same tablename,column names
    //-- make sure 
    //-- lastAutoincrementValue shouldn't get confused.....
    ResultSet rs;
    Statement s = createStatement();
    s.executeUpdate("create schema BPP");
    s.executeUpdate("set schema BPP");
    s.executeUpdate("create table tab1schema (i int, a1 int generated always as identity (start with 100, increment by 1))");
    s.executeUpdate("create table tab2schema (i int, a2 bigint generated always as identity (start with 100, increment by -1))");
    s.executeUpdate("create table tab3schema (i int, a1 int generated always as identity (start with 100, increment by 2))");
    s.executeUpdate("create table tab4schema (i int, a2 smallint generated always as identity (start with 100, increment by -2))");
    s.executeUpdate("insert into APP.tab1schema (i) values (1)");
    s.executeUpdate("insert into APP.tab2schema (i) values (1)");
    s.executeUpdate("insert into APP.tab3schema (i) values (1)");
    s.executeUpdate("insert into APP.tab4schema (i) values (1)");
    s.executeUpdate("insert into tab1schema (i) values (1)");
    s.executeUpdate("insert into tab1schema (i) values (2)");
    s.executeUpdate("insert into tab2schema (i) values (1)");
    s.executeUpdate("insert into tab2schema (i) values (2)");
    s.executeUpdate("insert into tab3schema (i) values (1)");
    s.executeUpdate("insert into tab3schema (i) values (2)");
    s.executeUpdate("insert into tab4schema (i) values (1)");
    s.executeUpdate("insert into tab4schema (i) values (2)");
    rs = s.executeQuery("select a.i, a1, a2 from app.tab1schema a join app.tab2schema b on a.i = b.i");
    String[][] expectedRows = new String[][] { { "1", "-1", "1" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs = s.executeQuery("select a.i, a1, a2 from app.tab3schema a join app.tab4schema b on a.i = b.i");
    expectedRows = new String[][] { { "1", "0", "0" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs = s.executeQuery("select a.i, a1, a2 from tab1schema a join tab2schema b on a.i = b.i order by a.i");
    expectedRows = new String[][] { { "1", "100", "100" }, { "2", "101", "99" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs = s.executeQuery("select a1, a2, a.i from tab3schema a join tab4schema b on a.i = b.i order by a1");
    expectedRows = new String[][] { { "100", "100", "1" }, { "102", "98", "2" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs = s.executeQuery("values IDENTITY_VAL_LOCAL()");
    expectedRows = new String[][] { { "98" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("set schema APP");
    s.executeUpdate("drop table BPP.tab1schema");
    s.executeUpdate("drop table BPP.tab2schema");
    s.executeUpdate("drop table BPP.tab3schema");
    s.executeUpdate("drop table BPP.tab4schema");
    s.executeUpdate("drop schema BPP restrict");
    s.executeUpdate("insert into tab3 values null");
    rs = s.executeQuery("select * from tab1 order by s1");
    expectedRows = new String[][] { { "1", "1" }, { "2", "2" }, { "3", "3" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    vetSequenceState("TAB1", 4, 1, 1);
    s.executeUpdate("create table tab2 (lvl int, s1  bigint generated always as identity)");
    s.executeUpdate("create trigger tab1_after2 after insert on tab3 referencing new as newrow for each row insert into tab2 (lvl) values 1,2,3");
    s.executeUpdate("insert into tab3 values null");
    rs = s.executeQuery("select * from tab2 order by lvl");
    expectedRows = new String[][] { { "1", "1" }, { "2", "2" }, { "3", "3" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    vetSequenceState("TAB2", 4, 1, 1);
}

43. GeneratedColumnsTest#test_6361_compilationSchemaDeleted()

Project: derby
Source File: GeneratedColumnsTest.java
View license
/**
     * Verify that generated columns work even if the compilation schema at
     * the time of adding the generated columns is dropped. Regression test
     * case for DERBY-6361.
     */
public void test_6361_compilationSchemaDeleted() throws SQLException {
    setAutoCommit(false);
    Statement s = createStatement();
    s.execute("create schema d6361_s1");
    s.execute("create schema d6361_s2");
    // Create a generated column in a table that lives in schema S1.
    // Declare the generated column while the current schema is S2.
    // Create generated columns both with CREATE TABLE and with ALTER
    // TABLE so that we test both code paths.
    s.execute("set schema d6361_s2");
    s.execute("create table d6361_s1.t(x int, y generated always as (-x))");
    s.execute("alter table d6361_s1.t " + "add column z generated always as (x+1)");
    // Then drop the schema that the generated columns were added from.
    s.execute("set schema d6361_s1");
    s.execute("drop schema d6361_s2 restrict");
    // This statement used to fail with schema does not exist.
    s.execute("insert into t(x) values 1");
    JDBC.assertFullResultSet(s.executeQuery("select * from t"), new String[][] { { "1", "-1", "2" } });
}

44. HoldCursorTest#tearDown()

Project: derby
Source File: HoldCursorTest.java
View license
/**
	 * Drop tables for clean up
	 */
protected void tearDown() throws Exception {
    Statement stmt = createStatement();
    stmt.executeUpdate("DROP PROCEDURE MYPROC");
    stmt.executeUpdate("DROP TABLE T1");
    stmt.executeUpdate("DROP TABLE T2");
    stmt.executeUpdate("DROP TABLE testtable1");
    stmt.executeUpdate("DROP TABLE testtable2");
    stmt.executeUpdate("DROP TABLE BUG4385");
    stmt.close();
    stmt.close();
    commit();
    super.tearDown();
}

45. InsertTest#testDerby6786Case1()

Project: derby
Source File: InsertTest.java
View license
public void testDerby6786Case1() throws SQLException {
    Statement s = createStatement();
    s.execute("CREATE TABLE K1 (K VARCHAR(64), S DECIMAL)");
    s.execute("CREATE TABLE K2 (K VARCHAR(64), S DECIMAL)");
    s.execute("CREATE TABLE T  (S DECIMAL)");
    s.execute("INSERT INTO K1 VALUES ('110007', 224)");
    s.execute("INSERT INTO K2 VALUES ('110007', 361)");
    //Run just plain select
    derby6786QueriesToRun(s, "");
    //Run insert into ... select
    derby6786QueriesToRun(s, "insert into t ");
    s.execute("DROP   TABLE K1");
    s.execute("DROP   TABLE K2");
    s.execute("DROP   TABLE T");
}

46. InsertTest#testDerby6786InsertIntoSelectCase2()

Project: derby
Source File: InsertTest.java
View license
public void testDerby6786InsertIntoSelectCase2() throws SQLException {
    Statement s = createStatement();
    s.execute("CREATE TABLE K1 (K VARCHAR(64), S DECIMAL)");
    s.execute("CREATE TABLE K2 (K VARCHAR(64), S DECIMAL)");
    s.execute("CREATE TABLE T  (S DECIMAL)");
    s.execute("INSERT INTO K1 VALUES ('110007', 224)");
    s.execute("INSERT INTO K2 VALUES ('110007', null)");
    //Run just plain select
    derby6786QueriesToRun(s, "");
    //Run insert into ... select
    derby6786QueriesToRun(s, "insert into t ");
    s.execute("DROP   TABLE K1");
    s.execute("DROP   TABLE K2");
    s.execute("DROP   TABLE T");
}

47. InsertTest#zztestDerby6786InsertIntoSelectCase3()

Project: derby
Source File: InsertTest.java
View license
//DERBY-6786(NullPointerException in INSERT INTO statement with multiple subselects)
//Following test case has zz against its name and hence it won't run. 
// This is because some queries in the test can cause NPE. Once 
// DERBY-6786 is fixed, following test should be enabled by removing
// zz.
public void zztestDerby6786InsertIntoSelectCase3() throws SQLException {
    Statement s = createStatement();
    s.execute("CREATE TABLE K1 (K VARCHAR(64), S DECIMAL)");
    s.execute("CREATE TABLE K2 (K VARCHAR(64), S DECIMAL)");
    s.execute("CREATE TABLE T  (S DECIMAL)");
    s.execute("INSERT INTO K1 VALUES ('110007', 224)");
    s.execute("INSERT INTO K2 VALUES ('110019', null)");
    //Run just plain select
    derby6786QueriesToRun(s, "");
    //Run insert into ... select. Running insert will into can result into
    // NPE for some of the queries until DERBY-6786 is fixed.
    derby6786QueriesToRun(s, "insert into t ");
    s.execute("DROP   TABLE K1");
    s.execute("DROP   TABLE K2");
    s.execute("DROP   TABLE T");
}

48. ProcedureInTriggerTest#testTriggerContainsSql()

Project: derby
Source File: ProcedureInTriggerTest.java
View license
/**
     * Test CONTAINS SQL triggers (neither reads no writes data)
     * procedure does just a getConnection and that's it.
     * 
     * @throws SQLException
     */
public void testTriggerContainsSql() throws SQLException {
    Statement s = createStatement();
    s.execute("insert into t2 values (1,2), (2,4)");
    s.execute("create trigger after_row_trig_contains_sql AFTER update on t2 for each ROW call proc_contains_sql()");
    // --- update 2 rows. check that trigger is fired - procedure should be called twice
    s.execute("update t2 set x = x*2");
    checkAndResetGetConnectionProcCount(2);
    //--- check updates are successful
    ResultSet rs = s.executeQuery("select * from t2");
    JDBC.assertFullResultSet(rs, new String[][] { { "2", "2" }, { "4", "4" } });
    s.execute("create trigger before_stmt_trig_contains_sql no cascade BEFORE delete on t2 for each STATEMENT call proc_contains_sql()");
    //--- delete 2 rows. check that trigger is fired - procedure should be called once
    s.execute("delete from t2");
    checkAndResetGetConnectionProcCount(1);
    //--- check delete is successful
    rs = s.executeQuery("select * from t2");
    JDBC.assertEmpty(rs);
    s.execute("drop trigger after_row_trig_contains_sql");
    s.execute("drop trigger before_stmt_trig_contains_sql");
    s.close();
}

49. TriggerWhenClauseTest#testFunctionReadsSQLData()

Project: derby
Source File: TriggerWhenClauseTest.java
View license
/**
     * Test a WHEN clause that invokes a function declared with READ SQL DATA.
     */
public void testFunctionReadsSQLData() throws SQLException {
    Statement s = createStatement();
    s.execute("create function f(x varchar(10)) returns boolean " + "language java parameter style java external name '" + getClass().getName() + ".tableIsEmpty' reads sql data");
    s.execute("create table t1(x varchar(10))");
    s.execute("create table t2(x varchar(10))");
    s.execute("create table t3(x int)");
    s.execute("create table t4(x int)");
    s.execute("insert into t3 values 1");
    s.execute("create trigger tr after insert on t1 " + "referencing new as new for each row " + "when (f(new.x)) insert into t2 values new.x");
    s.execute("insert into t1 values 'T3', 'T4', 'T3', 'T4', 'T3', 'T4'");
    JDBC.assertFullResultSet(s.executeQuery("select x, count(x) from t2 group by x"), new String[][] { { "T4", "3" } });
}

50. UpdateStatisticsTest#testNoExclusiveLockOnTable()

Project: derby
Source File: UpdateStatisticsTest.java
View license
/**
     * Test that SYSCS_UPDATE_STATISTICS doesn't obtain exclusive locks on
     * the table or rows in the table (DERBY-4274).
     */
public void testNoExclusiveLockOnTable() throws SQLException {
    Statement s = createStatement();
    s.execute("create table t (x char(1))");
    s.execute("create index ti on t(x)");
    s.execute("insert into t values 'a','b','c','d'");
    setAutoCommit(false);
    s.execute("lock table t in share mode");
    Connection c2 = openDefaultConnection();
    Statement s2 = c2.createStatement();
    // This call used to time out because SYSCS_UPDATE_STATISTICS tried
    // to lock T exclusively.
    s2.execute("call syscs_util.syscs_update_statistics('APP', 'T', null)");
    s2.close();
    c2.close();
    s.execute("drop table t");
    commit();
}

51. ViewsTest#testSelectViewFromOtherSchemaWithNoDefaultSchema()

Project: derby
Source File: ViewsTest.java
View license
/**
    * DERBY-3270 Test that we can select from a view in another schema if the
    * default schema does not exist.
    *
    * @throws SQLException
    */
public void testSelectViewFromOtherSchemaWithNoDefaultSchema() throws SQLException {
    Connection conn = openDefaultConnection("joe", "joepass");
    Statement st = conn.createStatement();
    st.execute("create table mytable(a int)");
    st.execute("insert into mytable values (99)");
    st.execute("create view myview as select * from mytable");
    st.close();
    conn.close();
    Connection conn2 = openDefaultConnection("bill", "billpass");
    Statement st2 = conn2.createStatement();
    ResultSet rs = st2.executeQuery("SELECT * FROM JOE.MYVIEW");
    JDBC.assertFullResultSet(rs, new String[][] { { "99" } });
    st2.executeUpdate("drop view joe.myview");
    st2.executeUpdate("drop table joe.mytable");
    st2.close();
    conn2.close();
}

52. FullTextLucene#init()

Project: ThriftyPaxos
Source File: FullTextLucene.java
View license
/**
     * Initializes full text search functionality for this database. This adds
     * the following Java functions to the database:
     * <ul>
     * <li>FTL_CREATE_INDEX(schemaNameString, tableNameString,
     * columnListString)</li>
     * <li>FTL_SEARCH(queryString, limitInt, offsetInt): result set</li>
     * <li>FTL_REINDEX()</li>
     * <li>FTL_DROP_ALL()</li>
     * </ul>
     * It also adds a schema FTL to the database where bookkeeping information
     * is stored. This function may be called from a Java application, or by
     * using the SQL statements:
     *
     * <pre>
     * CREATE ALIAS IF NOT EXISTS FTL_INIT FOR
     *      "org.h2.fulltext.FullTextLucene.init";
     * CALL FTL_INIT();
     * </pre>
     *
     * @param conn the connection
     */
public static void init(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE SCHEMA IF NOT EXISTS " + SCHEMA);
    stat.execute("CREATE TABLE IF NOT EXISTS " + SCHEMA + ".INDEXES(SCHEMA VARCHAR, TABLE VARCHAR, " + "COLUMNS VARCHAR, PRIMARY KEY(SCHEMA, TABLE))");
    stat.execute("CREATE ALIAS IF NOT EXISTS FTL_CREATE_INDEX FOR \"" + FullTextLucene.class.getName() + ".createIndex\"");
    stat.execute("CREATE ALIAS IF NOT EXISTS FTL_DROP_INDEX FOR \"" + FullTextLucene.class.getName() + ".dropIndex\"");
    stat.execute("CREATE ALIAS IF NOT EXISTS FTL_SEARCH FOR \"" + FullTextLucene.class.getName() + ".search\"");
    stat.execute("CREATE ALIAS IF NOT EXISTS FTL_SEARCH_DATA FOR \"" + FullTextLucene.class.getName() + ".searchData\"");
    stat.execute("CREATE ALIAS IF NOT EXISTS FTL_REINDEX FOR \"" + FullTextLucene.class.getName() + ".reindex\"");
    stat.execute("CREATE ALIAS IF NOT EXISTS FTL_DROP_ALL FOR \"" + FullTextLucene.class.getName() + ".dropAll\"");
    try {
        getIndexAccess(conn);
    } catch (SQLException e) {
        throw convertException(e);
    }
}

53. TriggerPassData#main()

Project: ThriftyPaxos
Source File: TriggerPassData.java
View license
/**
     * This method is called when executing this sample application from the
     * command line.
     *
     * @param args the command line parameters
     */
public static void main(String... args) throws Exception {
    Class.forName("org.h2.Driver");
    Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("CREATE ALIAS TRIGGER_SET FOR \"" + TriggerPassData.class.getName() + ".setTriggerData\"");
    stat.execute("CREATE TRIGGER T1 " + "BEFORE INSERT ON TEST " + "FOR EACH ROW CALL \"" + TriggerPassData.class.getName() + "\"");
    stat.execute("CALL TRIGGER_SET('T1', 'Hello')");
    stat.execute("INSERT INTO TEST VALUES(1)");
    stat.execute("CALL TRIGGER_SET('T1', 'World')");
    stat.execute("INSERT INTO TEST VALUES(2)");
    stat.close();
    conn.close();
}

54. TestCases#testTruncateConstraintsDisabled()

Project: ThriftyPaxos
Source File: TestCases.java
View license
private void testTruncateConstraintsDisabled() throws SQLException {
    deleteDb("cases");
    Connection conn = getConnection("cases");
    Statement stat = conn.createStatement();
    stat.execute("create table parent(id identity) as select 0");
    stat.execute("create table child(id identity, " + "parent int references parent(id)) as select 0, 0");
    assertThrows(ErrorCode.CANNOT_TRUNCATE_1, stat).execute("truncate table parent");
    assertThrows(ErrorCode.REFERENTIAL_INTEGRITY_VIOLATED_CHILD_EXISTS_1, stat).execute("delete from parent");
    stat.execute("alter table parent set referential_integrity false");
    stat.execute("delete from parent");
    stat.execute("truncate table parent");
    stat.execute("alter table parent set referential_integrity true");
    assertThrows(ErrorCode.CANNOT_TRUNCATE_1, stat).execute("truncate table parent");
    stat.execute("set referential_integrity false");
    stat.execute("truncate table parent");
    conn.close();
}

55. TestCompatibility#testUniqueIndexOracle()

Project: ThriftyPaxos
Source File: TestCompatibility.java
View license
private void testUniqueIndexOracle() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("SET MODE ORACLE");
    stat.execute("create table t2(c1 int, c2 int)");
    stat.execute("create unique index i2 on t2(c1, c2)");
    stat.execute("insert into t2 values (null, 1)");
    assertThrows(ErrorCode.DUPLICATE_KEY_1, stat).execute("insert into t2 values (null, 1)");
    stat.execute("insert into t2 values (null, null)");
    stat.execute("insert into t2 values (null, null)");
    stat.execute("insert into t2 values (1, null)");
    assertThrows(ErrorCode.DUPLICATE_KEY_1, stat).execute("insert into t2 values (1, null)");
    stat.execute("DROP TABLE T2");
}

56. TestLinkedTable#testLinkOtherSchema()

Project: ThriftyPaxos
Source File: TestLinkedTable.java
View license
private static void testLinkOtherSchema() throws SQLException {
    org.h2.Driver.load();
    Connection ca = DriverManager.getConnection("jdbc:h2:mem:one", "sa", "sa");
    Connection cb = DriverManager.getConnection("jdbc:h2:mem:two", "sa", "sa");
    Statement sa = ca.createStatement();
    Statement sb = cb.createStatement();
    sa.execute("CREATE TABLE GOOD (X NUMBER)");
    sa.execute("CREATE SCHEMA S");
    sa.execute("CREATE TABLE S.BAD (X NUMBER)");
    sb.execute("CALL LINK_SCHEMA('G', '', " + "'jdbc:h2:mem:one', 'sa', 'sa', 'PUBLIC'); ");
    sb.execute("CALL LINK_SCHEMA('B', '', " + "'jdbc:h2:mem:one', 'sa', 'sa', 'S'); ");
    // OK
    sb.executeQuery("SELECT * FROM G.GOOD");
    // FAILED
    sb.executeQuery("SELECT * FROM B.BAD");
    ca.close();
    cb.close();
}

57. TestLinkedTable#testLinkTwoTables()

Project: ThriftyPaxos
Source File: TestLinkedTable.java
View license
private void testLinkTwoTables() throws SQLException {
    org.h2.Driver.load();
    Connection conn = DriverManager.getConnection("jdbc:h2:mem:one", "sa", "sa");
    Statement stat = conn.createStatement();
    stat.execute("CREATE SCHEMA Y");
    stat.execute("CREATE TABLE A( C INT)");
    stat.execute("INSERT INTO A VALUES(1)");
    stat.execute("CREATE TABLE Y.A (C INT)");
    stat.execute("INSERT INTO Y.A VALUES(2)");
    Connection conn2 = DriverManager.getConnection("jdbc:h2:mem:two");
    Statement stat2 = conn2.createStatement();
    stat2.execute("CREATE LINKED TABLE one('org.h2.Driver', " + "'jdbc:h2:mem:one', 'sa', 'sa', 'Y.A');");
    stat2.execute("CREATE LINKED TABLE two('org.h2.Driver', " + "'jdbc:h2:mem:one', 'sa', 'sa', 'PUBLIC.A');");
    ResultSet rs = stat2.executeQuery("SELECT * FROM one");
    rs.next();
    assertEquals(2, rs.getInt(1));
    rs = stat2.executeQuery("SELECT * FROM two");
    rs.next();
    assertEquals(1, rs.getInt(1));
    conn.close();
    conn2.close();
}

58. AlterTableTest#testAddIdentityColumn4()

Project: derby
Source File: AlterTableTest.java
View license
/**
     * Slight changes to testAddIdentityColumn.
     * <p>
     * Another version of testAddIdenityColumn that provides slightly 
     * different code path, found useful while debugging DERBY-6774.
     **/
public void testAddIdentityColumn4() throws SQLException {
    Statement s = createStatement();
    createTestObjects(s);
    commit();
    // Add an identity column to a empty table.
    s.execute("alter table t0 add column " + "id int generated always as identity " + "(start with 100, increment by 5)");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    s.execute("delete from t0");
    s.execute("insert into t0(c1) values 1,2,3,4,5,6,7,8,9");
    rollback();
    // Add an identity column that is generated by default.
    s.execute("alter table t0 add column " + "id int generated by default as identity");
    ResultSet rs = s.executeQuery("select * from t0");
    JDBC.assertColumnNames(rs, "C1", "ID");
    ResultSetMetaData rsmd = rs.getMetaData();
    assertTrue(rsmd.isAutoIncrement(2));
    assertEquals(ResultSetMetaData.columnNoNulls, rsmd.isNullable(2));
    rs.close();
    s.execute("insert into t0(c1) values 1,2,3");
    s.execute("insert into t0(c1, id) values (9, 10)");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 order by id"), new String[][] { { "1", "1" }, { "2", "2" }, { "3", "3" }, { "9", "10" } });
    rollback();
    // Add an identity column that is generated by default, to a
    // non-empty table.
    s.execute("insert into t0 values 1");
    s.execute("alter table t0 add column id int " + "generated by default as identity");
    JDBC.assertSingleValueResultSet(s.executeQuery("select id from t0"), "1");
    s.execute("insert into t0(c1) values 5,6,7");
    JDBC.assertFullResultSet(s.executeQuery("select * from t0 where c1 > 1 order by c1"), new String[][] { { "5", "2" }, { "6", "3" }, { "7", "4" } });
    rollback();
}

59. AlterTableTest#testDropConstraint()

Project: derby
Source File: AlterTableTest.java
View license
public void testDropConstraint() throws Exception {
    Statement st = createStatement();
    createTestObjects(st);
    // drop constraint negative tests drop non-existent constraint
    assertStatementError("42X86", st, "alter table t0 drop constraint notexists");
    // constraint/table mismatch
    assertStatementError("42X86", st, "alter table t1 drop constraint p1");
    // In DB2 compatibility mode, we can't add a nullable 
    // primary key
    assertStatementError("42831", st, "alter table t0_1 add constraint p2 primary key(c1)");
    assertStatementError("42X86", st, " alter table t0_1 drop constraint p2");
    // positive tests verify that we can add/drop/add/drop/... 
    // constraints
    st.executeUpdate("alter table t0_1 add column c2 int not null " + "constraint p2 primary key default 0");
    assertUpdateCount(st, 0, "delete from t0_1");
    st.executeUpdate("alter table t0_1 drop constraint p2");
    st.executeUpdate("alter table t0_1 add constraint p2 primary key(c2)");
    st.executeUpdate("alter table t0_1 drop constraint p2");
    st.executeUpdate("alter table t0_1 add constraint p2 primary key(c2)");
    // verify that constraint is still enforced
    st.executeUpdate("insert into t0_1 values (1,1)");
    assertStatementError("23505", st, "insert into t0_1 values (1,1)");
    // verify the consistency of the indexes on the user tables
    ResultSet rs = st.executeQuery("select tablename, " + "SYSCS_UTIL.SYSCS_CHECK_TABLE('" + DerbyConstants.TEST_DBO + "', tablename) from " + "sys.systables where tabletype = 'T' and tablename = 'T0_1'");
    String[] expColNames = { "TABLENAME", "2" };
    JDBC.assertColumnNames(rs, expColNames);
    String[][] expRS = { { "T0_1", "1" } };
    JDBC.assertFullResultSet(rs, expRS, true);
    // verify that alter table works after drop/recreate of table
    PreparedStatement pSt = prepareStatement("alter table t0_1 drop constraint p2");
    assertUpdateCount(pSt, 0);
    st.executeUpdate("drop table t0_1");
    st.executeUpdate(" create table t0_1 (c1 int, c2 int not null " + "constraint p2 primary key)");
    assertUpdateCount(pSt, 0);
    // do consistency check on scans, etc. values 
    // (org.apache.derbyTesting.functionTests.util.T_Consistency
    // Checker::countOpens()) verify the consistency of the 
    // indexes on the system catalogs
    rs = st.executeQuery("select tablename, " + "SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename) from " + "sys.systables where tabletype = 'S' and tablename " + "!= 'SYSDUMMY1' order by tablename");
    expRS = new String[][] { { "SYSALIASES", "1" }, { "SYSCHECKS", "1" }, { "SYSCOLPERMS", "1" }, { "SYSCOLUMNS", "1" }, { "SYSCONGLOMERATES", "1" }, { "SYSCONSTRAINTS", "1" }, { "SYSDEPENDS", "1" }, { "SYSFILES", "1" }, { "SYSFOREIGNKEYS", "1" }, { "SYSKEYS", "1" }, { "SYSPERMS", "1" }, { "SYSROLES", "1" }, { "SYSROUTINEPERMS", "1" }, { "SYSSCHEMAS", "1" }, { "SYSSEQUENCES", "1" }, { "SYSSTATEMENTS", "1" }, { "SYSSTATISTICS", "1" }, { "SYSTABLEPERMS", "1" }, { "SYSTABLES", "1" }, { "SYSTRIGGERS", "1" }, { "SYSUSERS", "1" }, { "SYSVIEWS", "1" } };
    JDBC.assertFullResultSet(rs, expRS, true);
    // verify the consistency of the indexes on the user tables
    rs = st.executeQuery("select tablename, " + "SYSCS_UTIL.SYSCS_CHECK_TABLE('" + DerbyConstants.TEST_DBO + "', tablename) from " + "sys.systables where tabletype = 'T'");
    expRS = new String[][] { { "T0", "1" }, { "T0_2", "1" }, { "T0_3", "1" }, { "T1", "1" }, { "T1_1", "1" }, { "T2", "1" }, { "T3", "1" }, { "T4", "1" }, { "T0_1", "1" } };
    JDBC.assertUnorderedResultSet(rs, expRS, true);
    // bugs 793
    st.executeUpdate("create table b793 (pn1 int not null constraint " + "named_primary primary key, pn2 int constraint " + "named_pn2 check (pn2 > 3))");
    st.executeUpdate("alter table b793 drop constraint named_primary");
    st.executeUpdate("drop table b793");
    // test that drop constraint removes backing indexes
    st.executeUpdate("drop table t1");
    st.executeUpdate(" create table t1(a int not null constraint t1_pri " + "primary key)");
    st.executeUpdate(" create table reft1(a int constraint t1_ref " + "references t1(a))");
    // count should be 2
    rs = st.executeQuery("select count(*) from sys.sysconglomerates c, " + "sys.systables t where c.tableid = t.tableid and " + "t.tablename = 'REFT1'");
    JDBC.assertSingleValueResultSet(rs, "2");
    st.executeUpdate("alter table reft1 drop constraint t1_ref");
    st.executeUpdate("alter table t1 drop constraint t1_pri");
    // count should be 1
    rs = st.executeQuery("select count(*) from sys.sysconglomerates c, " + "sys.systables t where c.tableid = t.tableid and " + "t.tablename = 'REFT1'");
    JDBC.assertSingleValueResultSet(rs, "1");
    st.executeUpdate("drop table reft1");
    // clean up
    st.executeUpdate("drop view v2");
    st.executeUpdate("drop view v1");
    st.executeUpdate("drop table t0");
    st.executeUpdate("drop table t0_1");
    st.executeUpdate("drop table t0_2");
    st.executeUpdate("drop table t0_3");
    st.executeUpdate("drop table t1");
    st.executeUpdate("drop table t1_1");
    st.executeUpdate("drop table t3");
    st.executeUpdate("drop table t4");
}

60. CurrentOfTest#setUp()

Project: derby
Source File: CurrentOfTest.java
View license
/**
     * Set the fixture up with tables t and s and insert 4 rows in table t.
     */
protected void setUp() throws SQLException {
    getConnection().setAutoCommit(false);
    Statement stmt = createStatement();
    stmt.executeUpdate("create table t (i int, c char(50))");
    stmt.executeUpdate("create table s (i int, c char(50))");
    stmt.executeUpdate("insert into t values (1956, 'hello world')");
    stmt.executeUpdate("insert into t values (456, 'hi yourself')");
    stmt.executeUpdate("insert into t values (180, 'rubber ducky')");
    stmt.executeUpdate("insert into t values (3, 'you are the one')");
    stmt.close();
    commit();
}

61. CursorTest#setUp()

Project: derby
Source File: CursorTest.java
View license
protected void setUp() throws SQLException {
    getConnection().setAutoCommit(false);
    Statement stmt = createStatement();
    stmt.executeUpdate("create table t (i int, c char(50))");
    stmt.executeUpdate("create table s (i int, c char(50))");
    stmt.executeUpdate("insert into t values (1956, 'hello world')");
    stmt.executeUpdate("insert into t values (456, 'hi yourself')");
    stmt.executeUpdate("insert into t values (180, 'rubber ducky')");
    stmt.executeUpdate("insert into t values (3, 'you are the one')");
    stmt.close();
    commit();
}

62. SynonymTest#testViewDependency()

Project: derby
Source File: SynonymTest.java
View license
/**
     * The test makes sure that we correctly throw dependency exception when
     * user requests to drop a synonym which has dependent objects on it. Once
     * the dependency is removed, we should be able to drop the synonym.
     * @throws SQLException
     */
public void testViewDependency() throws SQLException {
    Statement stmt = createStatement();
    stmt.executeUpdate("create synonym mySyn for sys.systables");
    stmt.executeUpdate("create view v1 as select * from mySyn");
    stmt.executeUpdate("create view v2 as select * from v1");
    // Drop synonym should fail since it is used in two views.
    assertStatementError("X0Y23", stmt, "drop synonym mySyn");
    stmt.executeUpdate("drop view v2");
    // fails still because of view v1's dependency
    assertStatementError("X0Y23", stmt, "drop synonym mySyn");
    stmt.executeUpdate("drop view v1");
    stmt.executeUpdate("drop synonym mySyn");
    stmt.close();
}

63. SystemCatalogTest#testViewsOfSystemCatalogs()

Project: derby
Source File: SystemCatalogTest.java
View license
/**
	 * Test that view creation is recorded in the system tables.
	 * 
	 * @throws SQLException
	 */
public void testViewsOfSystemCatalogs() throws SQLException {
    Statement s = createStatement();
    s.execute("create table t (i int, s smallint)");
    s.execute("create table uniquekey3 (c1 int not null, c2 int not null, constraint uniq3 unique(c2, c1))");
    s.execute("create view dummyview as select * from t, uniquekey3");
    ResultSet rs = s.executeQuery("select tablename from sys.systables t, sys.sysviews v where t.tableid = v.tableid order by tablename");
    JDBC.assertSingleValueResultSet(rs, "DUMMYVIEW");
    rs.close();
    s.execute("drop view dummyview");
    s.execute("drop table t");
    s.execute("drop table uniquekey3");
    s.close();
}

64. TriggerTest#testDerby4610WrongDataType()

Project: derby
Source File: TriggerTest.java
View license
/**
     * Regression test case for DERBY-4610, where a DELETE statement failed
     * because a trigger used the wrong meta-data and mixed up the data types.
     */
public void testDerby4610WrongDataType() throws SQLException {
    Statement s = createStatement();
    s.execute("create table testtable " + "(id integer, name varchar(20), primary key(id))");
    s.execute("create table testchild (" + "id integer constraint fk_id " + "references testtable on delete cascade, " + "ordernum int, primary key(id))");
    s.execute("create procedure testproc (str varchar(20)) " + "PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME '" + getClass().getName() + ".derby4610proc'");
    s.execute("create trigger testtabletrigger after delete on testtable " + "referencing old as old " + "for each row mode db2sql call testproc(char(old.id))");
    s.execute("create trigger testchildtrigger after delete on testchild " + "referencing old as old " + "for each row mode db2sql call testproc(char(old.ordernum))");
    s.execute("insert into testtable values (1, 'test1')");
    s.execute("insert into testchild values (1, 10)");
    // Used to fail with ERROR XCL12: An attempt was made to put a data
    // value of type 'java.lang.String' into a data value of type 'INTEGER'.
    assertUpdateCount(s, 1, "delete from testtable where id = 1");
}

65. UpdatableResultSetTest#createTable0WithTrigger()

Project: derby
Source File: UpdatableResultSetTest.java
View license
private void createTable0WithTrigger() throws SQLException {
    Statement stmt = createStatement();
    stmt.executeUpdate("create table table0WithTriggers " + "(c1 int, c2 bigint)");
    stmt.executeUpdate("create table deleteTriggerInsertIntoThisTable " + "(c1 int)");
    stmt.executeUpdate("create table updateTriggerInsertIntoThisTable " + "(c1 int)");
    stmt.executeUpdate("create trigger tr1 " + "after delete on table0WithTriggers for each statement " + "insert into deleteTriggerInsertIntoThisTable values (1)");
    stmt.executeUpdate("create trigger tr2 " + "after update on table0WithTriggers for each statement " + "insert into updateTriggerInsertIntoThisTable values (1)");
    stmt.executeUpdate("insert into table0WithTriggers " + "values (1, 1), (2, 2), (3, 3), (4, 4)");
    stmt.close();
}

66. UpdatableResultSetTest#createTable1WithTrigger()

Project: derby
Source File: UpdatableResultSetTest.java
View license
private void createTable1WithTrigger() throws SQLException {
    Statement stmt = createStatement();
    stmt.executeUpdate("create table table1WithTriggers " + "(c1 int, c2 bigint)");
    stmt.executeUpdate("create trigger tr3 " + "after delete on table1WithTriggers referencing old as old " + "for each row delete from table1WithTriggers " + "where c1=old.c1+1 or c1=old.c1-1");
    stmt.executeUpdate("create table table2WithTriggers " + "(c1 int, c2 bigint)");
    stmt.executeUpdate("create trigger tr4 after update of c2 " + "on table2WithTriggers for each statement " + "update table2WithTriggers set c1=1");
    stmt.executeUpdate("insert into table1WithTriggers values " + "(1, 1), (2, 2), (3, 3), (4, 4)");
    stmt.executeUpdate("insert into table2WithTriggers values " + "(1, 1), (2, 2), (3, 3), (4, 4)");
    stmt.close();
}

67. TestDatabaseProvider#testCornerCases()

View license
/**
   * Corner cases
   * @throws Exception
    7.1. Drop role which doesn't exist, throws SentryNoSuchObjectException
    7.2. Create role which already exists, throws SentryAlreadyExitsException
    7.3. Drop role when privileges mapping exists and create role with same name, old
    mappings should not exist
    7.4. Grant role, when role doesn't exist, throws SentryNoSuchObjectException
    7.5. Grant role when mapping exists, silently allows
    7.6. Grant multiple roles to a group
    7.7. Revoke role after role has been dropped, SentryNoSuchObjectException
    7.8. Revoke role from a group when mapping doesn't exist, silently allows
    7.9. Grant privilege to a role, privilege already exists, silently allows
    7.10. Grant privilege to a role, mapping already exists, silently allows
    7.11. Multiple privileges to a role
    7.12. Revoke privilege when privilege doesn't exist, silently allows
    7.13. Revoke privilege, when role doesn't exist, SentryNoSuchObjectException
    7.14. Revoke privilege when mapping doesn't exist, silently allows
    7.15. Drop role should remove role-group mapping
   */
@Test
public void testCornerCases() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("CREATE TABLE IF NOT EXISTS tab1(c1 string)");
    //Drop a role which does not exist
    context.assertSentryException(statement, "DROP ROLE role1", SentryNoSuchObjectException.class.getSimpleName());
    //Create a role which already exists
    statement.execute("CREATE ROLE role1");
    context.assertSentryException(statement, "CREATE ROLE role1", SentryAlreadyExistsException.class.getSimpleName());
    //Drop role when privileges mapping exists and create role with same name, old mappings should not exist
    //state: role1
    statement.execute("GRANT ROLE role1 TO GROUP " + USERGROUP1);
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    ResultSet resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    statement.execute("DROP ROLE role1");
    statement.execute("CREATE ROLE role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 0);
    //Grant role, when role doesn't exist
    //state: role1
    context.assertSentryException(statement, "GRANT role role2 TO GROUP " + USERGROUP1, SentryNoSuchObjectException.class.getSimpleName());
    //Grant multiple roles to a group
    //state: role1
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ROLE role2 to GROUP " + USERGROUP1);
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    resultSet = statement.executeQuery("SHOW ROLE GRANT GROUP " + USERGROUP1);
    assertResultSize(resultSet, 2);
    //Grant role when mapping exists
    //state: role1, role2 -> usergroup1
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    //Revoke role after role has been dropped
    //state: role1, role2 -> usergroup1
    statement.execute("DROP ROLE role2");
    context.assertSentryException(statement, "REVOKE role role2 from group " + USERGROUP1, SentryNoSuchObjectException.class.getSimpleName());
    //Revoke role from a group when mapping doesnt exist
    //state: role1 -> usergroup1
    statement.execute("REVOKE ROLE role1 from GROUP " + USERGROUP1);
    statement.execute("REVOKE ROLE role1 from GROUP " + USERGROUP1);
    //Grant privilege to a role, privilege already exists, mapping already exists
    //state: role1
    //TODO: Remove this comment SENTRY-181
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role2");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    resultSet = statement.executeQuery("SHOW GRANT ROLE role2");
    assertResultSize(resultSet, 1);
    //Multiple privileges to a role
    //state: role1,role2 -> grant all on server
    statement.execute("GRANT ALL ON TABLE tab1 to ROLE role2");
    statement.execute("GRANT ALL,INSERT ON TABLE tab1 to ROLE role2");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role2");
    assertResultSize(resultSet, 2);
    statement.execute("DROP role role2");
    //Revoke privilege when privilege doesnt exist
    //state: role1 -> grant all on server
    statement.execute("REVOKE ALL ON TABLE tab1 from role role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    int count = 0;
    //Verify we still have all on server
    while (resultSet.next()) {
        count++;
        assertThat(resultSet.getString(1), equalToIgnoringCase("*"));
        assertThat(resultSet.getString(2), equalToIgnoringCase(""));
        assertThat(resultSet.getString(7), equalToIgnoringCase("*"));
    }
    assertThat(count, is(1));
    //Revoke privilege, when role doesnt exist
    //state: role1 -> grant all on server
    context.assertSentryException(statement, "REVOKE ALL ON SERVER server1 from role role2", SentryNoSuchObjectException.class.getSimpleName());
    //Revoke privilege when privilege exists but mapping doesnt exist
    //state: role1 -> grant all on server
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ALL on TABLE tab1 to ROLE role2");
    statement.execute("REVOKE ALL on TABLE tab1 from Role role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    statement.execute("DROP role role2");
    //Drop role should remove role-group mapping
    //state: role1 -> grant all on server
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    statement.execute("DROP ROLE role1");
    resultSet = statement.executeQuery("SHOW ROLE GRANT GROUP " + USERGROUP1);
    assertResultSize(resultSet, 0);
}

68. TestCrossDbOps#testNegativeUserPrivileges()

View license
/**
   * Test Case 2.14 admin user create a new database DB_1 create TABLE_1 in DB_1
   * admin user grant INSERT to user1's group on TABLE_1 negative test case:
   * user1 try to do following on TABLE_1 will fail: --explain --analyze
   * --describe --describe function --show columns --show table status --show
   * table properties --show create table --show partitions --show indexes
   * --select * from TABLE_1.
   */
@Test
public void testNegativeUserPrivileges() throws Exception {
    Connection adminCon = context.createConnection(ADMIN1);
    Statement adminStmt = context.createStatement(adminCon);
    adminStmt.execute("use default");
    adminStmt.execute("CREATE DATABASE " + DB1);
    adminStmt.execute("create table " + DB1 + ".table_1 (id int)");
    adminStmt.execute("create table " + DB1 + ".table_2 (id int)");
    adminStmt.close();
    adminCon.close();
    // edit policy file
    policyFile.addRolesToGroup(USERGROUP1, "db1_tab1_insert", "db1_tab2_all").addPermissionsToRole("db1_tab2_all", "server=server1->db=" + DB1 + "->table=table_2").addPermissionsToRole("db1_tab1_insert", "server=server1->db=" + DB1 + "->table=table_1->action=insert").setUserGroupMapping(StaticUserGroup.getStaticMapping());
    writePolicyFile(policyFile);
    Connection userConn = context.createConnection(USER1_1);
    Statement userStmt = context.createStatement(userConn);
    context.assertAuthzException(userStmt, "select * from " + DB1 + ".table_1");
    userConn.close();
    userStmt.close();
}

69. ServerPreparedStmtTest#setUp()

Project: pgjdbc
Source File: ServerPreparedStmtTest.java
View license
protected void setUp() throws Exception {
    con = TestUtil.openDB();
    Statement stmt = con.createStatement();
    TestUtil.createTable(con, "testsps", "id integer, value boolean");
    stmt.executeUpdate("INSERT INTO testsps VALUES (1,'t')");
    stmt.executeUpdate("INSERT INTO testsps VALUES (2,'t')");
    stmt.executeUpdate("INSERT INTO testsps VALUES (3,'t')");
    stmt.executeUpdate("INSERT INTO testsps VALUES (4,'t')");
    stmt.executeUpdate("INSERT INTO testsps VALUES (6,'t')");
    stmt.executeUpdate("INSERT INTO testsps VALUES (9,'f')");
    stmt.close();
}

70. SchemaTest#setUp()

Project: pgjdbc
Source File: SchemaTest.java
View license
protected void setUp() throws Exception {
    _conn = TestUtil.openDB();
    Statement stmt = _conn.createStatement();
    try {
        stmt.execute("CREATE SCHEMA " + TestUtil.getUser());
        dropUserSchema = true;
    } catch (SQLException e) {
    }
    stmt.execute("CREATE SCHEMA schema1");
    stmt.execute("CREATE SCHEMA schema2");
    stmt.execute("CREATE SCHEMA \"schema 3\"");
    stmt.execute("CREATE SCHEMA \"schema \"\"4\"");
    stmt.execute("CREATE SCHEMA \"schema '5\"");
    stmt.execute("CREATE SCHEMA \"schema ,6\"");
    stmt.execute("CREATE SCHEMA \"UpperCase\"");
    TestUtil.createTable(_conn, "schema1.table1", "id integer");
    TestUtil.createTable(_conn, "schema2.table2", "id integer");
    TestUtil.createTable(_conn, "\"UpperCase\".table3", "id integer");
}

71. SchemaTest#tearDown()

Project: pgjdbc
Source File: SchemaTest.java
View license
protected void tearDown() throws SQLException {
    _conn.setSchema(null);
    Statement stmt = _conn.createStatement();
    if (dropUserSchema) {
        stmt.execute("DROP SCHEMA " + TestUtil.getUser() + " CASCADE");
    }
    stmt.execute("DROP SCHEMA schema1 CASCADE");
    stmt.execute("DROP SCHEMA schema2 CASCADE");
    stmt.execute("DROP SCHEMA \"schema 3\" CASCADE");
    stmt.execute("DROP SCHEMA \"schema \"\"4\" CASCADE");
    stmt.execute("DROP SCHEMA \"schema '5\" CASCADE");
    stmt.execute("DROP SCHEMA \"schema ,6\"");
    stmt.execute("DROP SCHEMA \"UpperCase\" CASCADE");
    TestUtil.closeDB(_conn);
}

72. TestDatabaseProvider#testCornerCases()

Project: sentry
Source File: TestDatabaseProvider.java
View license
/**
   * Corner cases
   * @throws Exception
    7.1. Drop role which doesn't exist, throws SentryNoSuchObjectException
    7.2. Create role which already exists, throws SentryAlreadyExitsException
    7.3. Drop role when privileges mapping exists and create role with same name, old
    mappings should not exist
    7.4. Grant role, when role doesn't exist, throws SentryNoSuchObjectException
    7.5. Grant role when mapping exists, silently allows
    7.6. Grant multiple roles to a group
    7.7. Revoke role after role has been dropped, SentryNoSuchObjectException
    7.8. Revoke role from a group when mapping doesn't exist, silently allows
    7.9. Grant privilege to a role, privilege already exists, silently allows
    7.10. Grant privilege to a role, mapping already exists, silently allows
    7.11. Multiple privileges to a role
    7.12. Revoke privilege when privilege doesn't exist, silently allows
    7.13. Revoke privilege, when role doesn't exist, SentryNoSuchObjectException
    7.14. Revoke privilege when mapping doesn't exist, silently allows
    7.15. Drop role should remove role-group mapping
   */
@Test
public void testCornerCases() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("CREATE TABLE IF NOT EXISTS tab1(c1 string)");
    //Drop a role which does not exist
    context.assertSentryException(statement, "DROP ROLE role1", SentryNoSuchObjectException.class.getSimpleName());
    //Create a role which already exists
    statement.execute("CREATE ROLE role1");
    context.assertSentryException(statement, "CREATE ROLE role1", SentryAlreadyExistsException.class.getSimpleName());
    //Drop role when privileges mapping exists and create role with same name, old mappings should not exist
    //state: role1
    statement.execute("GRANT ROLE role1 TO GROUP " + USERGROUP1);
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    ResultSet resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    statement.execute("DROP ROLE role1");
    statement.execute("CREATE ROLE role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 0);
    //Grant role, when role doesn't exist
    //state: role1
    context.assertSentryException(statement, "GRANT role role2 TO GROUP " + USERGROUP1, SentryNoSuchObjectException.class.getSimpleName());
    //Grant multiple roles to a group
    //state: role1
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ROLE role2 to GROUP " + USERGROUP1);
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    resultSet = statement.executeQuery("SHOW ROLE GRANT GROUP " + USERGROUP1);
    assertResultSize(resultSet, 2);
    //Grant role when mapping exists
    //state: role1, role2 -> usergroup1
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    //Revoke role after role has been dropped
    //state: role1, role2 -> usergroup1
    statement.execute("DROP ROLE role2");
    context.assertSentryException(statement, "REVOKE role role2 from group " + USERGROUP1, SentryNoSuchObjectException.class.getSimpleName());
    //Revoke role from a group when mapping doesnt exist
    //state: role1 -> usergroup1
    statement.execute("REVOKE ROLE role1 from GROUP " + USERGROUP1);
    statement.execute("REVOKE ROLE role1 from GROUP " + USERGROUP1);
    //Grant privilege to a role, privilege already exists, mapping already exists
    //state: role1
    //TODO: Remove this comment SENTRY-181
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role2");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    resultSet = statement.executeQuery("SHOW GRANT ROLE role2");
    assertResultSize(resultSet, 1);
    //Multiple privileges to a role
    //state: role1,role2 -> grant all on server
    statement.execute("GRANT ALL ON TABLE tab1 to ROLE role2");
    statement.execute("GRANT ALL,INSERT ON TABLE tab1 to ROLE role2");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role2");
    assertResultSize(resultSet, 2);
    statement.execute("DROP role role2");
    //Revoke privilege when privilege doesnt exist
    //state: role1 -> grant all on server
    statement.execute("REVOKE ALL ON TABLE tab1 from role role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    int count = 0;
    //Verify we still have all on server
    while (resultSet.next()) {
        count++;
        assertThat(resultSet.getString(1), equalToIgnoringCase("*"));
        assertThat(resultSet.getString(2), equalToIgnoringCase(""));
        assertThat(resultSet.getString(7), equalToIgnoringCase("*"));
    }
    assertThat(count, is(1));
    //Revoke privilege, when role doesnt exist
    //state: role1 -> grant all on server
    context.assertSentryException(statement, "REVOKE ALL ON SERVER server1 from role role2", SentryNoSuchObjectException.class.getSimpleName());
    //Revoke privilege when privilege exists but mapping doesnt exist
    //state: role1 -> grant all on server
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ALL on TABLE tab1 to ROLE role2");
    statement.execute("REVOKE ALL on TABLE tab1 from Role role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    statement.execute("DROP role role2");
    //Drop role should remove role-group mapping
    //state: role1 -> grant all on server
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    statement.execute("DROP ROLE role1");
    resultSet = statement.executeQuery("SHOW ROLE GRANT GROUP " + USERGROUP1);
    assertResultSize(resultSet, 0);
}

73. TestCrossDbOps#testNegativeUserPrivileges()

Project: sentry
Source File: TestCrossDbOps.java
View license
/**
   * Test Case 2.14 admin user create a new database DB_1 create TABLE_1 in DB_1
   * admin user grant INSERT to user1's group on TABLE_1 negative test case:
   * user1 try to do following on TABLE_1 will fail: --explain --analyze
   * --describe --describe function --show columns --show table status --show
   * table properties --show create table --show partitions --show indexes
   * --select * from TABLE_1.
   */
@Test
public void testNegativeUserPrivileges() throws Exception {
    Connection adminCon = context.createConnection(ADMIN1);
    Statement adminStmt = context.createStatement(adminCon);
    adminStmt.execute("use default");
    adminStmt.execute("CREATE DATABASE " + DB1);
    adminStmt.execute("create table " + DB1 + ".table_1 (id int)");
    adminStmt.execute("create table " + DB1 + ".table_2 (id int)");
    adminStmt.close();
    adminCon.close();
    // edit policy file
    policyFile.addRolesToGroup(USERGROUP1, "db1_tab1_insert", "db1_tab2_all").addPermissionsToRole("db1_tab2_all", "server=server1->db=" + DB1 + "->table=table_2").addPermissionsToRole("db1_tab1_insert", "server=server1->db=" + DB1 + "->table=table_1->action=insert").setUserGroupMapping(StaticUserGroup.getStaticMapping());
    writePolicyFile(policyFile);
    Connection userConn = context.createConnection(USER1_1);
    Statement userStmt = context.createStatement(userConn);
    context.assertAuthzException(userStmt, "select * from " + DB1 + ".table_1");
    userConn.close();
    userStmt.close();
}

74. TestDatabaseProvider#testCornerCases()

Project: sentry
Source File: TestDatabaseProvider.java
View license
/**
   * Corner cases
   * @throws Exception
    7.1. Drop role which doesn't exist, throws SentryNoSuchObjectException
    7.2. Create role which already exists, throws SentryAlreadyExitsException
    7.3. Drop role when privileges mapping exists and create role with same name, old
    mappings should not exist
    7.4. Grant role, when role doesn't exist, throws SentryNoSuchObjectException
    7.5. Grant role when mapping exists, silently allows
    7.6. Grant multiple roles to a group
    7.7. Revoke role after role has been dropped, SentryNoSuchObjectException
    7.8. Revoke role from a group when mapping doesn't exist, silently allows
    7.9. Grant privilege to a role, privilege already exists, silently allows
    7.10. Grant privilege to a role, mapping already exists, silently allows
    7.11. Multiple privileges to a role
    7.12. Revoke privilege when privilege doesn't exist, silently allows
    7.13. Revoke privilege, when role doesn't exist, SentryNoSuchObjectException
    7.14. Revoke privilege when mapping doesn't exist, silently allows
    7.15. Drop role should remove role-group mapping
   */
@Test
public void testCornerCases() throws Exception {
    Connection connection = context.createConnection(ADMIN1);
    Statement statement = context.createStatement(connection);
    statement.execute("CREATE TABLE IF NOT EXISTS tab1(c1 string)");
    //Drop a role which does not exist
    context.assertSentryException(statement, "DROP ROLE role1", SentryNoSuchObjectException.class.getSimpleName());
    //Create a role which already exists
    statement.execute("CREATE ROLE role1");
    context.assertSentryException(statement, "CREATE ROLE role1", SentryAlreadyExistsException.class.getSimpleName());
    //Drop role when privileges mapping exists and create role with same name, old mappings should not exist
    //state: role1
    statement.execute("GRANT ROLE role1 TO GROUP " + USERGROUP1);
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    ResultSet resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    statement.execute("DROP ROLE role1");
    statement.execute("CREATE ROLE role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 0);
    //Grant role, when role doesn't exist
    //state: role1
    context.assertSentryException(statement, "GRANT role role2 TO GROUP " + USERGROUP1, SentryNoSuchObjectException.class.getSimpleName());
    //Grant multiple roles to a group
    //state: role1
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ROLE role2 to GROUP " + USERGROUP1);
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    resultSet = statement.executeQuery("SHOW ROLE GRANT GROUP " + USERGROUP1);
    assertResultSize(resultSet, 2);
    //Grant role when mapping exists
    //state: role1, role2 -> usergroup1
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    //Revoke role after role has been dropped
    //state: role1, role2 -> usergroup1
    statement.execute("DROP ROLE role2");
    context.assertSentryException(statement, "REVOKE role role2 from group " + USERGROUP1, SentryNoSuchObjectException.class.getSimpleName());
    //Revoke role from a group when mapping doesnt exist
    //state: role1 -> usergroup1
    statement.execute("REVOKE ROLE role1 from GROUP " + USERGROUP1);
    statement.execute("REVOKE ROLE role1 from GROUP " + USERGROUP1);
    //Grant privilege to a role, privilege already exists, mapping already exists
    //state: role1
    //TODO: Remove this comment SENTRY-181
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role1");
    statement.execute("GRANT ALL ON SERVER server1 TO ROLE role2");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    resultSet = statement.executeQuery("SHOW GRANT ROLE role2");
    assertResultSize(resultSet, 1);
    //Multiple privileges to a role
    //state: role1,role2 -> grant all on server
    statement.execute("GRANT ALL ON TABLE tab1 to ROLE role2");
    statement.execute("GRANT ALL,INSERT ON TABLE tab1 to ROLE role2");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role2");
    assertResultSize(resultSet, 2);
    statement.execute("DROP role role2");
    //Revoke privilege when privilege doesnt exist
    //state: role1 -> grant all on server
    statement.execute("REVOKE ALL ON TABLE tab1 from role role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    int count = 0;
    //Verify we still have all on server
    while (resultSet.next()) {
        count++;
        assertThat(resultSet.getString(1), equalToIgnoringCase("*"));
        assertThat(resultSet.getString(2), equalToIgnoringCase(""));
        assertThat(resultSet.getString(7), equalToIgnoringCase("*"));
    }
    assertThat(count, is(1));
    //Revoke privilege, when role doesnt exist
    //state: role1 -> grant all on server
    context.assertSentryException(statement, "REVOKE ALL ON SERVER server1 from role role2", SentryNoSuchObjectException.class.getSimpleName());
    //Revoke privilege when privilege exists but mapping doesnt exist
    //state: role1 -> grant all on server
    statement.execute("CREATE ROLE role2");
    statement.execute("GRANT ALL on TABLE tab1 to ROLE role2");
    statement.execute("REVOKE ALL on TABLE tab1 from Role role1");
    resultSet = statement.executeQuery("SHOW GRANT ROLE role1");
    assertResultSize(resultSet, 1);
    statement.execute("DROP role role2");
    //Drop role should remove role-group mapping
    //state: role1 -> grant all on server
    statement.execute("GRANT ROLE role1 to GROUP " + USERGROUP1);
    statement.execute("DROP ROLE role1");
    resultSet = statement.executeQuery("SHOW ROLE GRANT GROUP " + USERGROUP1);
    assertResultSize(resultSet, 0);
}

75. TestCrossDbOps#testNegativeUserPrivileges()

Project: sentry
Source File: TestCrossDbOps.java
View license
/**
   * Test Case 2.14 admin user create a new database DB_1 create TABLE_1 in DB_1
   * admin user grant INSERT to user1's group on TABLE_1 negative test case:
   * user1 try to do following on TABLE_1 will fail: --explain --analyze
   * --describe --describe function --show columns --show table status --show
   * table properties --show create table --show partitions --show indexes
   * --select * from TABLE_1.
   */
@Test
public void testNegativeUserPrivileges() throws Exception {
    Connection adminCon = context.createConnection(ADMIN1);
    Statement adminStmt = context.createStatement(adminCon);
    adminStmt.execute("use default");
    adminStmt.execute("CREATE DATABASE " + DB1);
    adminStmt.execute("create table " + DB1 + ".table_1 (id int)");
    adminStmt.execute("create table " + DB1 + ".table_2 (id int)");
    adminStmt.close();
    adminCon.close();
    // edit policy file
    policyFile.addRolesToGroup(USERGROUP1, "db1_tab1_insert", "db1_tab2_all").addPermissionsToRole("db1_tab2_all", "server=server1->db=" + DB1 + "->table=table_2").addPermissionsToRole("db1_tab1_insert", "server=server1->db=" + DB1 + "->table=table_1->action=insert").setUserGroupMapping(StaticUserGroup.getStaticMapping());
    writePolicyFile(policyFile);
    Connection userConn = context.createConnection(USER1_1);
    Statement userStmt = context.createStatement(userConn);
    context.assertAuthzException(userStmt, "select * from " + DB1 + ".table_1");
    userConn.close();
    userStmt.close();
}

76. TestCases#testReferenceableIndexUsage()

Project: ThriftyPaxos
Source File: TestCases.java
View license
private void testReferenceableIndexUsage() throws SQLException {
    Connection conn = getConnection("cases");
    Statement stat = conn.createStatement();
    stat.execute("drop table if exists a, b");
    stat.execute("create table a(id int, x int) as select 1, 100");
    stat.execute("create index idx1 on a(id, x)");
    stat.execute("create table b(id int primary key, a_id int) as select 1, 1");
    stat.execute("alter table b add constraint x " + "foreign key(a_id) references a(id)");
    stat.execute("update a set x=200");
    stat.execute("drop table if exists a, b");
    conn.close();
}

77. TestCases#testInsertDeleteRollback()

Project: ThriftyPaxos
Source File: TestCases.java
View license
private void testInsertDeleteRollback() throws SQLException {
    deleteDb("cases");
    Connection conn = getConnection("cases");
    Statement stat = conn.createStatement();
    stat.execute("set cache_size 1");
    stat.execute("SET MAX_MEMORY_ROWS " + Integer.MAX_VALUE);
    stat.execute("SET MAX_MEMORY_UNDO " + Integer.MAX_VALUE);
    stat.execute("SET MAX_OPERATION_MEMORY " + Integer.MAX_VALUE);
    stat.execute("create table test(id identity)");
    conn.setAutoCommit(false);
    stat.execute("insert into test select x from system_range(1, 11)");
    stat.execute("delete from test");
    conn.rollback();
    conn.close();
}

78. TestLob#testCopyManyLobs()

Project: ThriftyPaxos
Source File: TestLob.java
View license
private void testCopyManyLobs() throws Exception {
    deleteDb("lob");
    Connection conn = getConnection("lob");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id identity, data clob) " + "as select 1, space(10000)");
    stat.execute("insert into test(id, data) select null, data from test");
    stat.execute("insert into test(id, data) select null, data from test");
    stat.execute("insert into test(id, data) select null, data from test");
    stat.execute("insert into test(id, data) select null, data from test");
    stat.execute("delete from test where id < 10");
    stat.execute("shutdown compact");
    conn.close();
}

79. TestPreparedStatement#testScopedGeneratedKey()

View license
private void testScopedGeneratedKey(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    Trigger t = new SequenceTrigger();
    stat.execute("create table test(id identity)");
    stat.execute("create sequence seq start with 1000");
    stat.execute("create trigger test_ins after insert on test call \"" + t.getClass().getName() + "\"");
    stat.execute("insert into test values(null)");
    ResultSet rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(1, rs.getLong(1));
    stat.execute("insert into test values(100)");
    rs = stat.getGeneratedKeys();
    rs.next();
    assertEquals(100, rs.getLong(1));
    stat.execute("drop sequence seq");
    stat.execute("drop table test");
}

80. TestPageStore#testCheckpoint()

Project: ThriftyPaxos
Source File: TestPageStore.java
View license
private void testCheckpoint() throws SQLException {
    deleteDb("pageStoreCheckpoint");
    Connection conn;
    conn = getConnection("pageStoreCheckpoint");
    Statement stat = conn.createStatement();
    stat.execute("create table test(data varchar)");
    stat.execute("create sequence seq");
    stat.execute("set max_log_size 1");
    conn.setAutoCommit(false);
    stat.execute("insert into test select space(1000) from system_range(1, 1000)");
    long before = System.currentTimeMillis();
    stat.execute("select nextval('SEQ') from system_range(1, 100000)");
    long after = System.currentTimeMillis();
    // it's hard to test - basically it shouldn't checkpoint too often
    if (after - before > 10000) {
        if (!config.reopen) {
            fail("Checkpoint took " + (after - before) + " ms");
        }
    }
    stat.execute("drop table test");
    stat.execute("drop sequence seq");
    conn.close();
}

81. BlobClob4BlobTest#setUp()

Project: derby
Source File: BlobClob4BlobTest.java
View license
/**
     * Set up the conection to the database.
     */
public void setUp() throws Exception {
    getConnection().setAutoCommit(false);
    // creating small tables then add large column - that way forcing table
    // to have default small page size, but have large rows.
    Statement stmt = createStatement();
    stmt.executeUpdate("CREATE TABLE testClob (b INT, c INT)");
    stmt.executeUpdate("ALTER TABLE testClob ADD COLUMN a CLOB(300K)");
    // multiple tests depend on small page size, make sure size is 4k
    checkSmallPageSize(stmt, "TESTCLOB");
    stmt.executeUpdate("CREATE TABLE testBlob (b INT)");
    stmt.executeUpdate("ALTER TABLE testBlob ADD COLUMN a blob(300k)");
    stmt.executeUpdate("ALTER TABLE testBlob ADD COLUMN crc32 BIGINT");
    // multiple tests depend on small page size, make sure size is 4k
    checkSmallPageSize(stmt, "TESTBLOB");
    stmt.close();
    commit();
}

82. DatabaseMetaDataTest#dropObjectsForKeysTests()

Project: derby
Source File: DatabaseMetaDataTest.java
View license
/**
     * Drop the database objects for get*Keys tests
     * @throws SQLException 
     */
private void dropObjectsForKeysTests() throws SQLException {
    getConnection().setAutoCommit(false);
    Statement s = createStatement();
    s.execute("drop table reftab2");
    s.execute("drop table reftab");
    commit();
    s.execute("drop view kv");
    s.execute("drop index u2");
    s.execute("drop index u1");
    s.execute("drop table kt1");
    commit();
    getConnection().setAutoCommit(true);
}

83. SavepointJdbc30Test#testRollbackSqlSavepointSameAsJdbc()

Project: derby
Source File: SavepointJdbc30Test.java
View license
/** Test 20 */
public void testRollbackSqlSavepointSameAsJdbc() throws SQLException {
    Connection con = getConnection();
    Statement s = createStatement();
    s.executeUpdate("DELETE FROM T1");
    con.commit();
    s.executeUpdate("SAVEPOINT s1 ON ROLLBACK RETAIN LOCKS ON ROLLBACK " + "RETAIN CURSORS");
    s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
    s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
    s.executeUpdate("INSERT INTO T1 VALUES(1,1)");
    // Rollback to SQL savepoint and should see changes rolledback
    s.execute("ROLLBACK TO SAVEPOINT s1");
    ResultSet rs1 = s.executeQuery("select count(*) from t1");
    rs1.next();
    assertEquals(rs1.getInt(1), 0);
    con.rollback();
}

84. AlterTableTest#testJira2371()

Project: derby
Source File: AlterTableTest.java
View license
// JIRA 2371: ensure that a non-numeric, non-autogenerated 
// column can have its default value modified:
public void testJira2371() throws Exception {
    Statement st = createStatement();
    st.executeUpdate("create table t2371 ( a varchar(10))");
    st.executeUpdate("alter table t2371 alter column a default 'my val'");
    st.executeUpdate("insert into t2371 (a) values ('hi')");
    st.executeUpdate("insert into t2371 (a) values (default)");
    st.executeUpdate("alter table t2371 alter column a default 'another'");
    st.executeUpdate("insert into t2371 (a) values (default)");
    JDBC.assertFullResultSet(st.executeQuery("select A from t2371"), new String[][] { { "hi" }, { "my val" }, { "another" } });
}

85. CheckConstraintTest#testJira2989()

Project: derby
Source File: CheckConstraintTest.java
View license
public void testJira2989() throws SQLException {
    Statement st = createStatement();
    setAutoCommit(false);
    st.executeUpdate("CREATE TABLE \"indicator\" (c CHAR(1) DEFAULT 'N')");
    st.executeUpdate(" ALTER TABLE  \"indicator\" ADD CONSTRAINT " + "my_constraint CHECK ((c IN ('Y','N')))");
    st.executeUpdate(" INSERT INTO  \"indicator\" VALUES ('N')");
    st.executeUpdate(" ALTER TABLE  \"indicator\" DROP CONSTRAINT my_constraint");
    st.executeUpdate(" DROP TABLE   \"indicator\"");
    getConnection().rollback();
    st.close();
}

86. DistinctTest#testDistinctManyNullRows()

Project: derby
Source File: DistinctTest.java
View license
public void testDistinctManyNullRows() throws SQLException {
    Statement s = createStatement();
    s.execute("delete from t");
    s.execute("insert into t values (1, 2, 3, 4, '1992-01-01', '19:01:01', '1992-01-01 19:01:01.000', 'hello', 'planet')");
    s.execute("insert into t values (1, 1, 3, 4, '1992-01-02', '19:01:01', '1992-01-01 19:01:01.000', 'goodbye', 'planet')");
    s.execute("insert into t (i) values (null)");
    s.execute("insert into t (i) values (null)");
    s.execute("insert into t (i) values (null)");
    int[] expectedRows = { 2, 3, 2, 2, 3, 2, 2, 3, 2, 3, 3, 3, 5, 5, 0, 2, 0, 2, 2, 2, 0, -2, -2, 3, 3, 3, 4, 4, 3, 2, 2, 2, 4, 2, 3, 3, 4 };
    checkDistinctRows(expectedRows);
}

87. DistinctTest#testDistinctPaddingInVarcharIgnored()

Project: derby
Source File: DistinctTest.java
View license
public void testDistinctPaddingInVarcharIgnored() throws SQLException {
    Statement s = createStatement();
    s.execute("create table v (v varchar(40))");
    s.execute("insert into v values ('hello')");
    s.execute("insert into v values ('hello   ')");
    s.execute("insert into v values ('hello      ')");
    assertRowCount(1, s.executeQuery("select distinct v from v"));
    JDBC.assertSingleValueResultSet(s.executeQuery("select {fn length(c)} from (select distinct v from v) as t(c)"), "5");
    s.execute("drop table v");
    s.close();
}

88. DistinctTest#testDistinctWithBigInt()

Project: derby
Source File: DistinctTest.java
View license
public void testDistinctWithBigInt() throws SQLException {
    Statement s = createStatement();
    s.execute("create table li (l bigint, i int)");
    s.execute("insert into li values(1, 1)");
    s.execute("insert into li values(1, 1)");
    s.execute("insert into li values(9223372036854775807, 2147483647)");
    assertRowCount(2, s.executeQuery("select distinct l from li"));
    assertRowCount(4, s.executeQuery("(select distinct l from li) union all (select distinct i from li) order by 1"));
    assertRowCount(3, s.executeQuery("select distinct l from li union select distinct i from li"));
    assertRowCount(3, s.executeQuery("select distinct l from (select l from li union all select i from li) a(l)"));
    s.execute("drop table li");
    s.close();
}

89. DropTableTest#testDropTableDropView()

Project: derby
Source File: DropTableTest.java
View license
public void testDropTableDropView() throws SQLException {
    // test drop view
    Statement st = createStatement();
    setAutoCommit(false);
    st.executeUpdate("create table t1(a int)");
    st.executeUpdate(" create view vt1 as select * from t1");
    st.executeUpdate(" create view vvt1 as select * from vt1");
    // these should fail
    assertStatementError("X0Y23", st, "drop view vt1");
    assertStatementError("42X01", st, " drop view vt1 restrict");
    assertStatementError("42X01", st, " drop view vt1 cascade");
    // Clean up.
    st.executeUpdate("drop view vvt1");
    // Clean up.
    st.executeUpdate("drop view  vt1");
    // Clean up.
    st.executeUpdate("drop table  t1");
}

90. GroupByTest#testOrNodeInHavingClause()

Project: derby
Source File: GroupByTest.java
View license
/**
     * DERBY-3257 check for correct number of rows returned with
     * or in having clause.
     *  
     * @throws SQLException
     */
public void testOrNodeInHavingClause() throws SQLException {
    Statement s = createStatement();
    s.executeUpdate("CREATE TABLE TAB ( ID VARCHAR(20), INFO VARCHAR(20))");
    s.executeUpdate("insert into TAB values  ('1', 'A')");
    s.executeUpdate("insert into TAB values  ('2', 'A')");
    s.executeUpdate("insert into TAB values  ('3', 'B')");
    s.executeUpdate("insert into TAB values  ('4', 'B')");
    ResultSet rs = s.executeQuery("SELECT t0.INFO, COUNT(t0.ID) FROM TAB t0 GROUP BY t0.INFO HAVING (t0.INFO = 'A' OR t0.INFO = 'B') AND t0.INFO IS NOT NULL");
    String[][] expectedRows = { { "A", "2" }, { "B", "2" } };
    JDBC.assertFullResultSet(rs, expectedRows);
    s.executeUpdate("DROP TABLE TAB");
}

91. LuceneSupportTest#setUp()

Project: derby
Source File: LuceneSupportTest.java
View license
//////////////////////////////////////////////////////////////
//
//  END TEST FOR MULTIPLE FIELDS
//
//////////////////////////////////////////////////////////////
protected void setUp() throws SQLException {
    CallableStatement cSt;
    Statement st = createStatement();
    try {
        st.executeUpdate("create schema lucenetest");
    } catch (Exception e) {
    }
    st.executeUpdate("set schema lucenetest");
    st.executeUpdate("create table titles (ID int generated always as identity primary key, ISBN varchar(16), PRINTISBN varchar(16), title varchar(1024), subtitle varchar(1024), author varchar(1024), series varchar(1024), publisher varchar(1024), collections varchar(128), collections2 varchar(128))");
    st.executeUpdate("insert into titles (ISBN, PRINTISBN, TITLE, SUBTITLE, AUTHOR, SERIES, PUBLISHER, COLLECTIONS, COLLECTIONS2) values ('9765087650324','9765087650324','The Grapes Of Wrath','The Great Depression in Oklahoma','John Steinbeck','Noble Winners','The Viking Press','National Book Award','Pulitzer Prize')");
    st.executeUpdate("insert into titles (ISBN, PRINTISBN, TITLE, SUBTITLE, AUTHOR, SERIES, PUBLISHER, COLLECTIONS, COLLECTIONS2) values ('6754278542987','6754278542987','Identical: Portraits of Twins','Best Photo Book 2012 by American Photo Magazine','Martin Schoeller','Portraits','teNeues','Photography','')");
    st.executeUpdate("insert into titles (ISBN, PRINTISBN, TITLE, SUBTITLE, AUTHOR, SERIES, PUBLISHER, COLLECTIONS, COLLECTIONS2) values ('2747583475882','2747583475882','Vines, Grapes, and Wines','The wine drinker''s guide to grape varieties','Jancis Robinson','Reference','Alfred A. Knopf','Wine','')");
    st.executeUpdate("insert into titles (ISBN, PRINTISBN, TITLE, SUBTITLE, AUTHOR, SERIES, PUBLISHER, COLLECTIONS, COLLECTIONS2) values ('4356123483483','4356123483483','A Tale of Two Cities','A fictional account of events leading up to the French revolution','Charles Dickens','Classics','Chapman & Hall','Fiction','Social Criticism')");
    cSt = prepareCall("call syscs_util.syscs_register_tool('luceneSupport',true)");
    assertUpdateCount(cSt, 0);
}

92. OLAPTest#testRollupReservedWord()

Project: derby
Source File: OLAPTest.java
View license
/**
      * Verify that ROLLUP can still be used as the name of a column or table.
      */
public void testRollupReservedWord() throws SQLException {
    Statement s = createStatement();
    s.executeUpdate("create table t_roll(rollup int, x int)");
    JDBC.assertEmpty(s.executeQuery("select rollup, sum(x) from t_roll group by rollup"));
    JDBC.assertEmpty(s.executeQuery("select count(*) from t_roll group by mod(rollup,x)"));
    JDBC.assertEmpty(s.executeQuery("select count(*) from t_roll group by mod(x,rollup)"));
    s.executeUpdate("create table rollup(a int, x int)");
    JDBC.assertEmpty(s.executeQuery("select a, x from rollup"));
    s.executeUpdate("insert into rollup(a,x) values(1,2)");
    JDBC.assertUnorderedResultSet(s.executeQuery("select a,sum(x) from rollup group by rollup(a)"), new String[][] { { "1", "2" }, { null, "2" } });
    s.executeUpdate("drop table rollup");
    s.executeUpdate("drop table t_roll");
    s.close();
}

93. OuterJoinTest#testDerby_4471a()

Project: derby
Source File: OuterJoinTest.java
View license
/**
     * Test that left outer join reordering works as expected.  This fixture is
     * the first repro mentioned for this issue, DERBY-4471. It checks for
     * correct results, and should fail prior to applying issue patch due to
     * erroneous reordering.
     * <p/>
     * The results asserted in these tests for DERBY-4471 are the same as Derby
     * gives without the reordering enabled and the same as Postgres yields.
     * <p/>
     * Note that the patch also opens up for correct reorderings of some
     * queries in the seen in the original lojreorder.sql test (converted to
     * JUnit as LojReorderTest in this patch) that did <em>not</em> happen
     * earlier, in addition to denying the wrong ones documented in DERBY-4471
     * and tested below. These new reorderings are tested, cf the comments in
     * LojReorderTest. Look for the string 4471.

     */
public void testDerby_4471a() throws SQLException {
    setAutoCommit(false);
    Statement s = createStatement();
    s.executeUpdate("create table r(c1 char(1))");
    s.executeUpdate("create table s(c1 char(1), c2 char(1))");
    s.executeUpdate("create table t(c1 char(1))");
    s.executeUpdate("insert into r values 'a'");
    s.executeUpdate("insert into s values ('b', default)");
    s.executeUpdate("insert into t values ('c')");
    ResultSet rs = s.executeQuery("select * from r left outer join (s left outer join t " + "                                 on s.c2=t.c1 or s.c2 is null)" + "                on r.c1=s.c1");
    JDBC.assertFullResultSet(rs, new String[][] { { "a", null, null, null } });
}

94. ReleaseCompileLocksTest#testReleaseCompileLocks()

Project: derby
Source File: ReleaseCompileLocksTest.java
View license
/*Fixtures*/
public void testReleaseCompileLocks() throws Exception {
    Statement stmt = createStatement();
    // but at least performing the call to locktable is performed.
    if (JDBC.vmSupportsJDBC3())
        JDBC.assertFullResultSet(stmt.executeQuery("select (dmlstatic()) from sys.systables where " + "CAST(tablename AS VARCHAR(128))= 'SYSCONGLOMERATES'"), new String[][] { { "1" } });
    else
        JDBC.assertFullResultSet(stmt.executeQuery("select count(*) from sys.systables where " + "CAST(tablename AS VARCHAR(128)) = 'SYSCONGLOMERATES'"), new String[][] { { "1" } });
    assertNoLocks(stmt);
    commit();
    stmt.execute("drop table t1");
    stmt.execute("create table t1 (s int)");
    commit();
    // but at least performing the call to locktable is performed.
    if (JDBC.vmSupportsJDBC3())
        JDBC.assertFullResultSet(stmt.executeQuery("select (insertstatic()) from sys.systables where " + "CAST(tablename AS VARCHAR(128)) = 'SYSCONGLOMERATES'"), new String[][] { { "1" } });
    else
        JDBC.assertFullResultSet(stmt.executeQuery("select count(*) from sys.systables where " + "CAST(tablename AS VARCHAR(128)) = 'SYSCONGLOMERATES'"), new String[][] { { "1" } });
    assertNoLocks(stmt);
    JDBC.assertEmpty(stmt.executeQuery("select * from t1"));
    stmt.execute("drop table t1");
    commit();
    assertNoLocks(stmt);
    commit();
    stmt.execute("create table test_tab (x int)");
    stmt.executeUpdate("insert into test_tab values (1)");
    commit();
    assertNoLocks(stmt);
    JDBC.assertSingleValueResultSet(stmt.executeQuery("select count(*) from sys.sysviews"), "0");
    assertNoLocks(stmt);
    stmt.execute("insert into test_tab values (2)");
    waitForPostCommit(stmt);
    ResultSet rs = stmt.executeQuery("select TYPE, MODE, TABLENAME, LOCKNAME, STATE from syscs_diag.lock_table order by 1");
    String expectedValues[][] = { { "ROW", "X", "TEST_TAB", "(1,8)", "GRANT" }, { "TABLE", "IX", "TEST_TAB", "Tablelock", "GRANT" } };
    JDBC.assertFullResultSet(rs, expectedValues);
    try {
        stmt.execute("drop table t1");
        fail("expected SQLException; table t should not exist");
    } catch (SQLException e) {
        assertSQLState("42Y55", e);
    }
    stmt.execute("create table t1 (x int)");
    commit();
    JDBC.assertEmpty(stmt.executeQuery("select * from t1"));
    Connection conn1 = openDefaultConnection();
    Statement stmt2 = conn1.createStatement();
    stmt2.execute("create table t2 (x int)");
    stmt2.execute("drop table t2");
    stmt2.close();
    conn1.commit();
    conn1.close();
    stmt.execute("drop table test_tab");
    stmt.execute("create table test_tab (x int)");
    stmt.execute("insert into test_tab values (1)");
    commit();
    PreparedStatement ps = prepareStatement("update test_tab set x=2 where x=?");
    ps.setCursorName("cursor1");
    ps.setInt(1, 1);
    assertNoLocks(stmt);
    ps.executeUpdate();
    waitForPostCommit(stmt);
    rs = stmt.executeQuery("select TYPE, MODE, TABLENAME, LOCKNAME, STATE from syscs_diag.lock_table order by 1");
    String expectedValues1[][] = { { "ROW", "X", "TEST_TAB", "(1,7)", "GRANT" }, { "TABLE", "IX", "TEST_TAB", "Tablelock", "GRANT" } };
    JDBC.assertFullResultSet(rs, expectedValues1);
    commit();
    stmt.execute("create table t (c1 int not null primary key, c2 int references t)");
    stmt.executeUpdate("insert into t values (1,1)");
    stmt.executeUpdate("insert into t values (2,1)");
    commit();
    ps = prepareStatement("select * from t where c1 = ? and c2 = ?");
    ps.setCursorName("ps");
    assertNoLocks(stmt);
    stmt.execute("create table x(c1 int)");
    stmt.execute("drop table x");
    commit();
    ps = prepareStatement("insert into t values (3,2)");
    ps.setCursorName("pi");
    assertNoLocks(stmt);
    commit();
    stmt.execute("create table x(c1 int)");
    stmt.execute("drop table x");
    commit();
    ps = prepareStatement("update t set c2 = c1, c1 = c2");
    ps.setCursorName("p1");
    assertNoLocks(stmt);
    commit();
    stmt.execute("create table x(c1 int)");
    stmt.execute("drop table x");
    commit();
    ps = prepareStatement("delete from t");
    ps.setCursorName("p1");
    assertNoLocks(stmt);
    commit();
    stmt.execute("create trigger update_of_t after update on t for each row values 2");
    stmt.execute("create trigger insert_of_t after insert on t for each row values 3");
    commit();
    ps = prepareStatement("update t set c2=2 where c1=2");
    ps.setCursorName("pu");
    assertNoLocks(stmt);
    commit();
    rs.close();
    ps.close();
    stmt.close();
}

95. RenameTableTest#testRenameWithCheckConstraintsOnIt()

Project: derby
Source File: RenameTableTest.java
View license
/**
     * RENAME TABLE should fail when check constraints on it.
     * 
     * @exception SQLException
     */
public void testRenameWithCheckConstraintsOnIt() throws SQLException {
    Statement s = createStatement();
    s.executeUpdate("create table tcheck (i int check(i>5))");
    assertStatementError("X0Y25", s, "rename table tcheck to tcheck1");
    s.executeUpdate("drop table tcheck");
    // - Rename should pass after dropping the check constriant
    s.executeUpdate("create table tcheck (i int, j int, constraint tcon check (i+j>2))");
    assertStatementError("X0Y25", s, "rename table tcheck to tcheck1");
    s.executeUpdate("alter table tcheck drop constraint tcon");
    s.executeUpdate("rename table tcheck to tcheck1");
    // select * from tcheck1;
    s.executeUpdate("drop table tcheck1");
}

96. SubqueryTest#testSubqueryInCOALESCE()

Project: derby
Source File: SubqueryTest.java
View license
/**
     * DERBY-1574: Subquery in COALESCE gives NPE due to 
     * preprocess not implemented for that node type
     */
public void testSubqueryInCOALESCE() throws Exception {
    Statement st = createStatement();
    ResultSet rs = null;
    String[][] expRS;
    String[] expColNames;
    st.executeUpdate("create table t1 (id int)");
    st.executeUpdate("create table t2 (i integer primary key, j int)");
    st.executeUpdate("insert into t1 values 1,2,3,4,5");
    st.executeUpdate("insert into t2 values (1,1),(2,4),(3,9),(4,16)");
    assertUpdateCount(st, 5, "update t1 set id = coalesce((select j from t2 " + "where t2.i=t1.id), 0)");
    rs = st.executeQuery("select * from t1");
    expColNames = new String[] { "ID" };
    JDBC.assertColumnNames(rs, expColNames);
    expRS = new String[][] { { "1" }, { "4" }, { "9" }, { "16" }, { "0" } };
    JDBC.assertFullResultSet(rs, expRS, true);
    st.executeUpdate("drop table t1");
    st.executeUpdate("drop table t2");
}

97. TriggerTest#testDerby6383StatementTriggerBugTst1()

Project: derby
Source File: TriggerTest.java
View license
/**
     * DERBY-6383(Update trigger defined on one column fires on update 
     * of other columns). This regression is caused by DERBY-4874(Trigger 
     * does not recognize new size of VARCHAR column expanded with 
     * ALTER TABLE. It fails with ERROR 22001: A truncation error was 
     * encountered trying to shrink VARCHAR)
     *  The regression is for Statement level triggers. The trigger
     *  gets fired for any column update rather than just the column
     *  specified in the UPDATE of column clause. Following test
     *  confirms that fix for DERBY-6383 fixes the issue.
     * 
     * @throws SQLException 
     * 
     */
public void testDerby6383StatementTriggerBugTst1() throws SQLException {
    Statement s = createStatement();
    s.executeUpdate("CREATE TABLE DERBY_6368_TAB1 (X INTEGER, Y INTEGER)");
    s.executeUpdate("CREATE TABLE DERBY_6368_TAB2 (X INTEGER, Y INTEGER)");
    s.executeUpdate("INSERT INTO  DERBY_6368_TAB1 VALUES(1, 2)");
    //Create statement trigger on a specific column "X" on DERBY_6368_TAB1
    s.executeUpdate("CREATE TRIGGER t1 AFTER UPDATE OF x " + "ON DERBY_6368_TAB1 REFERENCING old table AS old " + "INSERT INTO DERBY_6368_TAB2 SELECT * FROM old");
    assertTableRowCount("DERBY_6368_TAB2", 0);
    //Following should not fire the trigger since following UPDATE is on
    // column "Y" whereas trigger is defined on column "X"
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
    assertTableRowCount("DERBY_6368_TAB2", 0);
    //Create row trigger on a specific column "X" on DERBY_6368_TAB1
    s.executeUpdate("CREATE TRIGGER t2 AFTER UPDATE OF x " + "ON DERBY_6368_TAB1 REFERENCING old AS old_row " + "for each row " + "INSERT INTO DERBY_6368_TAB2(x) values(old_row.x)");
    //Following should not fire any trigger since following UPDATE is on
    // column "Y" whereas triggers are defined on column "X"
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
    assertTableRowCount("DERBY_6368_TAB2", 0);
    //Following should fire both triggers since following UPDATE is on
    // column "X" which has two triggers defined on it
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
    assertTableRowCount("DERBY_6368_TAB2", 2);
    //Create statement trigger at table level for DERBY_6368_TAB1
    s.executeUpdate("CREATE TRIGGER t3 AFTER UPDATE " + "ON DERBY_6368_TAB1 REFERENCING old table AS old " + "INSERT INTO DERBY_6368_TAB2 SELECT * FROM old");
    //Following should fire trigger t3 which is supposed to fire for
    // any column update
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
    assertTableRowCount("DERBY_6368_TAB2", 3);
    //Following should fire all the triggers since following UPDATE is on
    // column "X" which has two triggers defined on it
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
    assertTableRowCount("DERBY_6368_TAB2", 6);
    //Add a new column to table
    s.executeUpdate("ALTER TABLE DERBY_6368_TAB1 ADD COLUMN Z int");
    s.executeUpdate("ALTER TABLE DERBY_6368_TAB2 ADD COLUMN Z int");
    //Following should fire trigger t3 since any column update should fire
    // trigger t3
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET z = z + 1");
    assertTableRowCount("DERBY_6368_TAB2", 7);
    //Following should fire trigger t3 since any column update should fire
    // trigger t3
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
    assertTableRowCount("DERBY_6368_TAB2", 8);
    //Following should fire all the triggers since following UPDATE is on
    // column "X" which has two triggers defined on it
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
    assertTableRowCount("DERBY_6368_TAB2", 11);
    //drop statement trigger defined on specific column
    s.executeUpdate("drop TRIGGER T1");
    //Following should only fire trigger t3 since any column update should
    // fire trigger t3
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET y = y + 1");
    assertTableRowCount("DERBY_6368_TAB2", 12);
    //Following should fire triggers t2 and t3 since following UPDATE is on
    // column "X" which has row trigger defined on it and a statement 
    // trigger(at table level) defined on it
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
    assertTableRowCount("DERBY_6368_TAB2", 14);
    //Following should fire trigger t3 since any column update should fire
    // trigger t3
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET z = z + 1");
    assertTableRowCount("DERBY_6368_TAB2", 15);
    //Drop a column from the table. Following will drop trigger t3
    // because it depends on column being dropped. But trigger t2
    // will remain intact since it does not have dependency on
    // column being dropped. So only trigger left at this point
    // will be t2 after the following column drop
    s.executeUpdate("ALTER TABLE DERBY_6368_TAB1 DROP COLUMN Y");
    s.executeUpdate("ALTER TABLE DERBY_6368_TAB2 DROP COLUMN Y");
    //Following should fire triggers t2 since following UPDATE is on
    // column "X" which has row trigger defined on it
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET x = x + 1");
    assertTableRowCount("DERBY_6368_TAB2", 16);
    //Following should not fire trigger t2
    s.executeUpdate("UPDATE DERBY_6368_TAB1 SET z = z + 1");
    assertTableRowCount("DERBY_6368_TAB2", 16);
    //clean up after the test
    s.executeUpdate("drop table DERBY_6368_TAB1");
    s.executeUpdate("drop table DERBY_6368_TAB2");
}

98. TriggerTest#testDerby6663()

Project: derby
Source File: TriggerTest.java
View license
/**
     * Regression test case for DERBY-6663 (NPE when a trigger tries to
     * insert into a table with a foreign key).
     */
public void testDerby6663() throws SQLException {
    setAutoCommit(false);
    Statement s = createStatement();
    s.execute("create table d6663_t1(pk int primary key)");
    s.execute("create table d6663_t2(x int references d6663_t1)");
    s.execute("create table d6663_t3(y int)");
    s.execute("create trigger d6663_tr after insert on d6663_t3 " + "referencing new as new for each row " + "insert into d6663_t2 values new.y");
    // Used to fail with NPE instead of foreign key violation.
    assertStatementError(FOREIGN_KEY_VIOLATION, s, "insert into d6663_t3 values 1");
    // Verify that trigger executes successfully if there is no
    // foreign key violation.
    s.execute("insert into d6663_t1 values 1");
    s.execute("insert into d6663_t3 values 1");
    JDBC.assertSingleValueResultSet(s.executeQuery("select * from d6663_t2"), "1");
}

99. TriggerWhenClauseTest#testDerby6783_3()

Project: derby
Source File: TriggerWhenClauseTest.java
View license
/**
     * Derby6783_3 test, this test referes to different tables in
     * when clause and update clause.
    */
public void testDerby6783_3() throws SQLException {
    Statement s = createStatement();
    s.execute("CREATE TABLE tabDerby6783_3_1(FIELD1 VARCHAR(10)," + " FIELD2 DOUBLE)");
    s.execute("INSERT INTO tabDerby6783_3_1 VALUES ('helloworld', 5454567)");
    s.execute("CREATE TABLE tabDerby6783_3_2(FIELD3 NUMERIC (7,1))");
    s.execute("INSERT INTO tabDerby6783_3_2 VALUES (3.143)");
    s.execute("CREATE TRIGGER TRIGGER_3 AFTER UPDATE OF FIELD1 ON tabDerby6783_3_1" + " REFERENCING NEW AS newrow OLD AS oldrow" + " FOR EACH ROW WHEN (newrow.FIELD2 > 3000)" + " UPDATE tabDerby6783_3_2 SET FIELD3 = newrow.FIELD2 / 10");
    s.execute("UPDATE tabDerby6783_3_1 set FIELD1='hello'");
    JDBC.assertSingleValueResultSet(s.executeQuery("SELECT FIELD3 FROM tabDerby6783_3_2"), "545456.7");
}

100. LongColumnTest#testOneLongColumn()

Project: derby
Source File: LongColumnTest.java
View license
/**
     * test 1: one long column
     */
public void testOneLongColumn() throws SQLException {
    Statement st = createStatement();
    st.execute("create table testing (a varchar(8096))");
    st.execute("insert into testing values (PADSTRING('1 2 3 4 5 6 7 8 9 0', 8096))");
    st.execute("insert into testing values (PADSTRING('a b c d e f g h i j', 8096))");
    st.execute("insert into testing values (PADSTRING('11 22 33 44 55 66 77', 8096))");
    st.execute("insert into testing values (PADSTRING('aa bb cc dd ee ff gg', 8096))");
    ResultSet rs = st.executeQuery("select a from testing");
    JDBC.assertFullResultSet(rs, new String[][] { { "1 2 3 4 5 6 7 8 9 0" }, { "a b c d e f g h i j" }, { "11 22 33 44 55 66 77" }, { "aa bb cc dd ee ff gg" } });
    st.close();
}