Examples with Delete org.h2.command.dml.Delete used on opensource projects

Example 91 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class TestStatement method testSavepoint.

private void testSavepoint() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
    conn.setAutoCommit(false);
    stat.execute("INSERT INTO TEST VALUES(0, 'Hi')");
    Savepoint savepoint1 = conn.setSavepoint();
    int id1 = savepoint1.getSavepointId();
    assertThrows(ErrorCode.SAVEPOINT_IS_UNNAMED, savepoint1).getSavepointName();
    stat.execute("DELETE FROM TEST");
    conn.rollback(savepoint1);
    stat.execute("UPDATE TEST SET NAME='Hello'");
    Savepoint savepoint2a = conn.setSavepoint();
    Savepoint savepoint2 = conn.setSavepoint();
    conn.releaseSavepoint(savepoint2a);
    assertThrows(ErrorCode.SAVEPOINT_IS_INVALID_1, savepoint2a).getSavepointId();
    int id2 = savepoint2.getSavepointId();
    assertTrue(id1 != id2);
    stat.execute("UPDATE TEST SET NAME='Hallo' WHERE NAME='Hello'");
    Savepoint savepointTest = conn.setSavepoint("Joe's");
    assertTrue(savepointTest.toString().endsWith("name=Joe's"));
    stat.execute("DELETE FROM TEST");
    assertEquals(savepointTest.getSavepointName(), "Joe's");
    assertThrows(ErrorCode.SAVEPOINT_IS_NAMED, savepointTest).getSavepointId();
    conn.rollback(savepointTest);
    conn.commit();
    ResultSet rs = stat.executeQuery("SELECT NAME FROM TEST");
    rs.next();
    String name = rs.getString(1);
    assertEquals(name, "Hallo");
    assertFalse(rs.next());
    assertThrows(ErrorCode.SAVEPOINT_IS_INVALID_1, conn).rollback(savepoint2);
    stat.execute("DROP TABLE TEST");
    conn.setAutoCommit(true);
}

Example 92 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class TestIndex method testNonUniqueHashIndex.

private void testNonUniqueHashIndex() throws SQLException {
    reconnect();
    stat.execute("create memory table test(id bigint, data bigint)");
    stat.execute("create hash index on test(id)");
    Random rand = new Random(1);
    PreparedStatement prepInsert = conn.prepareStatement("insert into test values(?, ?)");
    PreparedStatement prepDelete = conn.prepareStatement("delete from test where id=?");
    PreparedStatement prepSelect = conn.prepareStatement("select count(*) from test where id=?");
    HashMap<Long, Integer> map = new HashMap<>();
    for (int i = 0; i < 1000; i++) {
        long key = rand.nextInt(10) * 1000000000L;
        Integer r = map.get(key);
        int result = r == null ? 0 : (int) r;
        if (rand.nextBoolean()) {
            prepSelect.setLong(1, key);
            ResultSet rs = prepSelect.executeQuery();
            rs.next();
            assertEquals(result, rs.getInt(1));
        } else {
            if (rand.nextBoolean()) {
                prepInsert.setLong(1, key);
                prepInsert.setInt(2, rand.nextInt());
                prepInsert.execute();
                map.put(key, result + 1);
            } else {
                prepDelete.setLong(1, key);
                prepDelete.execute();
                map.put(key, 0);
            }
        }
    }
    stat.execute("drop table test");
    conn.close();
}

Example 93 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class TestTriggersConstraints method testTriggerAdapter.

private void testTriggerAdapter() throws SQLException {
    Connection conn;
    Statement stat;
    conn = getConnection("trigger");
    stat = conn.createStatement();
    stat.execute("drop table if exists test");
    stat.execute("create table test(id int, c clob, b blob)");
    stat.execute("create table message(name varchar)");
    stat.execute("create trigger test_insert before insert, update, delete on test " + "for each row call \"" + TestTriggerAdapter.class.getName() + "\"");
    stat.execute("insert into test values(1, 'hello', 'abcd')");
    ResultSet rs;
    rs = stat.executeQuery("select * from test");
    rs.next();
    assertEquals(10, rs.getInt(1));
    stat.execute("update test set id = 2");
    rs = stat.executeQuery("select * from test");
    rs.next();
    assertEquals(20, rs.getInt(1));
    stat.execute("delete from test");
    rs = stat.executeQuery("select * from message");
    assertTrue(rs.next());
    assertEquals("+1;", rs.getString(1));
    assertTrue(rs.next());
    assertEquals("-10;+2;", rs.getString(1));
    assertTrue(rs.next());
    assertEquals("-20;", rs.getString(1));
    assertFalse(rs.next());
    stat.execute("drop table test, message");
    conn.close();
}

Example 94 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class TestTriggersConstraints method testConstraints.

private void testConstraints() throws SQLException {
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    stat.execute("create table test(id int primary key, parent int)");
    stat.execute("alter table test add constraint test_parent_id " + "foreign key(parent) references test (id) on delete cascade");
    stat.execute("insert into test select x, x/2 from system_range(0, 100)");
    stat.execute("delete from test");
    assertSingleValue(stat, "select count(*) from test", 0);
    stat.execute("drop table test");
    conn.close();
}

Example 95 with Delete

use of org.h2.command.dml.Delete in project h2database by h2database.

the class TestSpatial method testIndexTransaction.

private void testIndexTransaction() throws SQLException {
    // Check session management in index
    deleteDb("spatial");
    try (Connection conn = getConnection(URL)) {
        conn.setAutoCommit(false);
        Statement stat = conn.createStatement();
        createTestTable(stat);
        Savepoint sp = conn.setSavepoint();
        // Remove a row but do not commit
        stat.execute("delete from roads where idRoad=9");
        stat.execute("delete from roads where idRoad=7");
        // Check if index is updated
        ResultSet rs = stat.executeQuery("select idArea, COUNT(idRoad) roadCount " + "from area, roads " + "where area.the_geom && roads.the_geom " + "GROUP BY idArea ORDER BY idArea");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt("idArea"));
        assertEquals(3, rs.getInt("roadCount"));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt("idArea"));
        assertEquals(4, rs.getInt("roadCount"));
        assertTrue(rs.next());
        assertEquals(3, rs.getInt("idArea"));
        assertEquals(1, rs.getInt("roadCount"));
        assertTrue(rs.next());
        assertEquals(4, rs.getInt("idArea"));
        assertEquals(1, rs.getInt("roadCount"));
        assertTrue(rs.next());
        assertEquals(5, rs.getInt("idArea"));
        assertEquals(2, rs.getInt("roadCount"));
        assertTrue(rs.next());
        assertEquals(6, rs.getInt("idArea"));
        assertEquals(1, rs.getInt("roadCount"));
        assertFalse(rs.next());
        rs.close();
        conn.rollback(sp);
        // Check if the index is restored
        testRoadAndArea(stat);
    }
}