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

Example 81 with Delete

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

the class TestMVTableEngine method testRollback.

private void testRollback() throws Exception {
    Connection conn;
    Statement stat;
    deleteDb(getTestName());
    String url = getTestName() + ";MV_STORE=TRUE";
    conn = getConnection(url);
    stat = conn.createStatement();
    stat.execute("create table test(id identity)");
    conn.setAutoCommit(false);
    stat.execute("insert into test values(1)");
    stat.execute("delete from test");
    conn.rollback();
    conn.close();
}

Example 82 with Delete

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

the class TestMVTableEngine method testSimple.

private void testSimple() throws Exception {
    deleteDb(getTestName());
    String dbName = getTestName() + ";MV_STORE=TRUE";
    Connection conn = getConnection(dbName);
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, name varchar)");
    stat.execute("insert into test values(1, 'Hello'), (2, 'World')");
    ResultSet rs = stat.executeQuery("select *, _rowid_ from test");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertEquals(1, rs.getInt(3));
    stat.execute("update test set name = 'Hello' where id = 1");
    if (!config.memory) {
        conn.close();
        conn = getConnection(dbName);
        stat = conn.createStatement();
    }
    rs = stat.executeQuery("select * from test order by id");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertTrue(rs.next());
    assertEquals(2, rs.getInt(1));
    assertEquals("World", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("create unique index idx_name on test(name)");
    rs = stat.executeQuery("select * from test " + "where name = 'Hello' order by name");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    try {
        stat.execute("insert into test(id, name) values(10, 'Hello')");
        fail();
    } catch (SQLException e) {
        assertEquals(e.toString(), ErrorCode.DUPLICATE_KEY_1, e.getErrorCode());
    }
    rs = stat.executeQuery("select min(id), max(id), " + "min(name), max(name) from test");
    rs.next();
    assertEquals(1, rs.getInt(1));
    assertEquals(2, rs.getInt(2));
    assertEquals("Hello", rs.getString(3));
    assertEquals("World", rs.getString(4));
    assertFalse(rs.next());
    stat.execute("delete from test where id = 2");
    rs = stat.executeQuery("select * from test order by id");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("alter table test add column firstName varchar");
    rs = stat.executeQuery("select * from test where name = 'Hello'");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    if (!config.memory) {
        conn.close();
        conn = getConnection(dbName);
        stat = conn.createStatement();
    }
    rs = stat.executeQuery("select * from test order by id");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    assertFalse(rs.next());
    stat.execute("truncate table test");
    rs = stat.executeQuery("select * from test order by id");
    assertFalse(rs.next());
    rs = stat.executeQuery("select count(*) from test");
    rs.next();
    assertEquals(0, rs.getInt(1));
    stat.execute("insert into test(id) select x from system_range(1, 3000)");
    rs = stat.executeQuery("select count(*) from test");
    rs.next();
    assertEquals(3000, rs.getInt(1));
    try {
        stat.execute("insert into test(id) values(1)");
        fail();
    } catch (SQLException e) {
        assertEquals(ErrorCode.DUPLICATE_KEY_1, e.getErrorCode());
    }
    stat.execute("delete from test");
    stat.execute("insert into test(id, name) values(-1, 'Hello')");
    rs = stat.executeQuery("select count(*) from test where id = -1");
    rs.next();
    assertEquals(1, rs.getInt(1));
    rs = stat.executeQuery("select count(*) from test where name = 'Hello'");
    rs.next();
    assertEquals(1, rs.getInt(1));
    conn.close();
}

Example 83 with Delete

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

the class TestMVTableEngine method testLobReuse.

private void testLobReuse() throws Exception {
    deleteDb(getTestName());
    Connection conn1 = getConnection(getTestName());
    Statement stat = conn1.createStatement();
    stat.execute("create table test(id identity primary key, lob clob)");
    byte[] buffer = new byte[8192];
    for (int i = 0; i < 20; i++) {
        Connection conn2 = getConnection(getTestName());
        stat = conn2.createStatement();
        stat.execute("insert into test(lob) select space(1025) from system_range(1, 10)");
        stat.execute("delete from test where random() > 0.5");
        ResultSet rs = conn2.createStatement().executeQuery("select lob from test");
        while (rs.next()) {
            InputStream is = rs.getBinaryStream(1);
            while (is.read(buffer) != -1) {
            // ignore
            }
        }
        conn2.close();
    }
    conn1.close();
}

Example 84 with Delete

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

the class TestMVTableEngine method testRollbackAfterCrash.

private void testRollbackAfterCrash() throws Exception {
    if (config.memory) {
        return;
    }
    Connection conn;
    Statement stat;
    deleteDb(getTestName());
    String url = getTestName() + ";MV_STORE=TRUE";
    String url2 = getTestName() + "2;MV_STORE=TRUE";
    conn = getConnection(url);
    stat = conn.createStatement();
    stat.execute("create table test(id int)");
    stat.execute("insert into test values(0)");
    stat.execute("set write_delay 0");
    conn.setAutoCommit(false);
    stat.execute("insert into test values(1)");
    stat.execute("shutdown immediately");
    JdbcUtils.closeSilently(conn);
    conn = getConnection(url);
    stat = conn.createStatement();
    ResultSet rs = stat.executeQuery("select row_count_estimate " + "from information_schema.tables where table_name='TEST'");
    rs.next();
    assertEquals(1, rs.getLong(1));
    stat.execute("drop table test");
    stat.execute("create table test(id int primary key, data clob)");
    stat.execute("insert into test values(1, space(10000))");
    conn.setAutoCommit(false);
    stat.execute("delete from test");
    stat.execute("checkpoint");
    stat.execute("shutdown immediately");
    JdbcUtils.closeSilently(conn);
    conn = getConnection(url);
    stat = conn.createStatement();
    rs = stat.executeQuery("select * from test");
    assertTrue(rs.next());
    stat.execute("drop all objects delete files");
    conn.close();
    conn = getConnection(url);
    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(200 * x) " + "from system_range(1, 10)");
    conn.setAutoCommit(false);
    stat.execute("delete from test where id > 5");
    stat.execute("backup to '" + getBaseDir() + "/" + getTestName() + ".zip'");
    conn.rollback();
    Restore.execute(getBaseDir() + "/" + getTestName() + ".zip", getBaseDir(), getTestName() + "2");
    Connection conn2;
    conn2 = getConnection(url2);
    conn.close();
    conn2.close();
}

Example 85 with Delete

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

the class TestMVTableEngine method testReferentialIntegrity.

private void testReferentialIntegrity() throws Exception {
    Connection conn;
    Statement stat;
    deleteDb(getTestName());
    conn = getConnection(getTestName() + ";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();
}