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

Example 46 with Delete

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

the class TestLob method testDelete.

private void testDelete() throws Exception {
    if (config.memory || config.mvStore) {
        return;
    }
    deleteDb("lob");
    Connection conn;
    Statement stat;
    conn = getConnection("lob");
    stat = conn.createStatement();
    stat.execute("create table test(id int primary key, name clob)");
    stat.execute("insert into test values(1, space(10000))");
    assertSingleValue(stat, "select count(*) from information_schema.lob_data", 1);
    stat.execute("insert into test values(2, space(10000))");
    assertSingleValue(stat, "select count(*) from information_schema.lob_data", 1);
    stat.execute("delete from test where id = 1");
    assertSingleValue(stat, "select count(*) from information_schema.lob_data", 1);
    stat.execute("insert into test values(3, space(10000))");
    assertSingleValue(stat, "select count(*) from information_schema.lob_data", 1);
    stat.execute("insert into test values(4, space(10000))");
    assertSingleValue(stat, "select count(*) from information_schema.lob_data", 1);
    stat.execute("delete from test where id = 2");
    assertSingleValue(stat, "select count(*) from information_schema.lob_data", 1);
    stat.execute("delete from test where id = 3");
    assertSingleValue(stat, "select count(*) from information_schema.lob_data", 1);
    stat.execute("delete from test");
    conn.close();
    conn = getConnection("lob");
    stat = conn.createStatement();
    assertSingleValue(stat, "select count(*) from information_schema.lob_data", 0);
    stat.execute("drop table test");
    conn.close();
}

Example 47 with Delete

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

the class TestDeadlock method testDeadlockInFulltextSearch.

private void testDeadlockInFulltextSearch() throws SQLException {
    deleteDb("deadlock");
    String url = "deadlock";
    Connection conn, conn2;
    conn = getConnection(url);
    conn2 = getConnection(url);
    final Statement stat = conn.createStatement();
    Statement stat2 = conn2.createStatement();
    stat.execute("create alias if not exists ft_init for " + "\"org.h2.fulltext.FullText.init\"");
    stat.execute("call ft_init()");
    stat.execute("create table test(id int primary key, name varchar)");
    stat.execute("call ft_create_index('PUBLIC', 'TEST', null)");
    Task t = new Task() {

        @Override
        public void call() throws Exception {
            while (!stop) {
                stat.executeQuery("select * from test");
            }
        }
    };
    t.execute();
    long start = System.nanoTime();
    while (System.nanoTime() - start < TimeUnit.SECONDS.toNanos(1)) {
        stat2.execute("insert into test values(1, 'Hello')");
        stat2.execute("delete from test");
    }
    t.get();
    conn2.close();
    conn.close();
    conn = getConnection(url);
    conn.createStatement().execute("drop all objects");
    conn.close();
}

Example 48 with Delete

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

the class TestSequence method testConcurrentCreate.

private void testConcurrentCreate() throws Exception {
    deleteDb("sequence");
    final String url = getURL("sequence;MULTI_THREADED=1;LOCK_TIMEOUT=2000", true);
    Connection conn = getConnection(url);
    Task[] tasks = new Task[2];
    try {
        Statement stat = conn.createStatement();
        stat.execute("create table dummy(id bigint primary key)");
        stat.execute("create table test(id bigint primary key)");
        stat.execute("create sequence test_seq cache 2");
        for (int i = 0; i < tasks.length; i++) {
            final int x = i;
            tasks[i] = new Task() {

                @Override
                public void call() throws Exception {
                    try (Connection conn = getConnection(url)) {
                        PreparedStatement prep = conn.prepareStatement("insert into test(id) values(next value for test_seq)");
                        PreparedStatement prep2 = conn.prepareStatement("delete from test");
                        while (!stop) {
                            prep.execute();
                            if (Math.random() < 0.01) {
                                prep2.execute();
                            }
                            if (Math.random() < 0.01) {
                                createDropTrigger(conn);
                            }
                        }
                    }
                }

                private void createDropTrigger(Connection conn) throws Exception {
                    String triggerName = "t_" + x;
                    Statement stat = conn.createStatement();
                    stat.execute("create trigger " + triggerName + " before insert on dummy call \"" + TriggerTest.class.getName() + "\"");
                    stat.execute("drop trigger " + triggerName);
                }
            }.execute();
        }
        Thread.sleep(1000);
        for (Task t : tasks) {
            t.get();
        }
    } finally {
        for (Task t : tasks) {
            t.join();
        }
        conn.close();
    }
}

Example 49 with Delete

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

the class TestSpatial method testNullableGeometry.

private void testNullableGeometry() throws SQLException {
    deleteDb("spatial");
    Connection conn = getConnection(URL);
    Statement stat = conn.createStatement();
    stat.execute("create memory table test" + "(id int primary key, the_geom geometry)");
    stat.execute("create spatial index on test(the_geom)");
    stat.execute("insert into test values(1, null)");
    stat.execute("insert into test values(2, null)");
    stat.execute("delete from test where the_geom is null");
    stat.execute("insert into test values(1, null)");
    stat.execute("insert into test values(2, null)");
    stat.execute("insert into test values(3, " + "'POLYGON ((1000 2000, 1000 3000, 2000 3000, 1000 2000))')");
    stat.execute("insert into test values(4, null)");
    stat.execute("insert into test values(5, null)");
    stat.execute("insert into test values(6, " + "'POLYGON ((1000 3000, 1000 4000, 2000 4000, 1000 3000))')");
    ResultSet rs = stat.executeQuery("select * from test");
    int count = 0;
    while (rs.next()) {
        count++;
        int id = rs.getInt(1);
        if (id == 3 || id == 6) {
            assertNotNull(rs.getObject(2));
        } else {
            assertNull(rs.getObject(2));
        }
    }
    assertEquals(6, count);
    rs = stat.executeQuery("select * from test where the_geom is null");
    count = 0;
    while (rs.next()) {
        count++;
        assertNull(rs.getObject(2));
    }
    assertEquals(4, count);
    rs = stat.executeQuery("select * from test where the_geom is not null");
    count = 0;
    while (rs.next()) {
        count++;
        assertNotNull(rs.getObject(2));
    }
    assertEquals(2, count);
    rs = stat.executeQuery("select * from test " + "where intersects(the_geom, " + "'POLYGON ((1000 1000, 1000 2000, 2000 2000, 1000 1000))')");
    conn.close();
    if (!config.memory) {
        conn = getConnection(URL);
        stat = conn.createStatement();
        rs = stat.executeQuery("select * from test");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertNull(rs.getObject(2));
        conn.close();
    }
    deleteDb("spatial");
}

Example 50 with Delete

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

the class TestSpatial method testNullableGeometryDelete.

private void testNullableGeometryDelete() throws SQLException {
    deleteDb("spatial");
    Connection conn = getConnection(URL);
    Statement stat = conn.createStatement();
    stat.execute("create memory table test" + "(id int primary key, the_geom geometry)");
    stat.execute("create spatial index on test(the_geom)");
    stat.execute("insert into test values(1, null)");
    stat.execute("insert into test values(2, null)");
    stat.execute("insert into test values(3, null)");
    ResultSet rs = stat.executeQuery("select * from test order by id");
    while (rs.next()) {
        assertNull(rs.getObject(2));
    }
    stat.execute("delete from test where id = 1");
    stat.execute("delete from test where id = 2");
    stat.execute("delete from test where id = 3");
    stat.execute("insert into test values(4, null)");
    stat.execute("insert into test values(5, null)");
    stat.execute("insert into test values(6, null)");
    stat.execute("delete from test where id = 4");
    stat.execute("delete from test where id = 5");
    stat.execute("delete from test where id = 6");
    conn.close();
    deleteDb("spatial");
}