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

Example 41 with Delete

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

the class TestLob method testLobNoClose.

private void testLobNoClose() throws Exception {
    if (config.networked) {
        return;
    }
    deleteDb("lob");
    Connection conn = reconnect(null);
    conn.createStatement().execute("CREATE TABLE TEST(ID IDENTITY, DATA CLOB)");
    conn.createStatement().execute("INSERT INTO TEST VALUES(1, SPACE(10000))");
    ResultSet rs = conn.createStatement().executeQuery("SELECT DATA FROM TEST");
    rs.next();
    SysProperties.lobCloseBetweenReads = true;
    Reader in = rs.getCharacterStream(1);
    in.read();
    conn.createStatement().execute("DELETE FROM TEST");
    SysProperties.lobCloseBetweenReads = false;
    conn.createStatement().execute("INSERT INTO TEST VALUES(1, SPACE(10000))");
    rs = conn.createStatement().executeQuery("SELECT DATA FROM TEST");
    rs.next();
    in = rs.getCharacterStream(1);
    in.read();
    conn.setAutoCommit(false);
    try {
        conn.createStatement().execute("DELETE FROM TEST");
        conn.commit();
        // DELETE does not fail in Linux, but in Windows
        // error("Error expected");
        // but reading afterwards should fail
        int len = 0;
        while (true) {
            int x = in.read();
            if (x < 0) {
                break;
            }
            len++;
        }
        in.close();
        if (len > 0) {
            // even if they are deleted
            if (System.getProperty("os.name").indexOf("Windows") > 0) {
                fail("Error expected; len=" + len);
            }
        }
    } catch (SQLException e) {
        assertKnownException(e);
    }
    conn.rollback();
    conn.close();
}

Example 42 with Delete

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

the class TestLob method testRemovedAfterTimeout.

private void testRemovedAfterTimeout() throws Exception {
    if (config.lazy) {
        return;
    }
    deleteDb("lob");
    final String url = getURL("lob;lob_timeout=50", true);
    Connection conn = getConnection(url);
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, data clob)");
    PreparedStatement prep = conn.prepareStatement("insert into test values(?, ?)");
    prep.setInt(1, 1);
    prep.setString(2, "aaa" + new String(new char[1024 * 16]).replace((char) 0, 'x'));
    prep.execute();
    prep.setInt(1, 2);
    prep.setString(2, "bbb" + new String(new char[1024 * 16]).replace((char) 0, 'x'));
    prep.execute();
    ResultSet rs = stat.executeQuery("select * from test order by id");
    rs.next();
    Clob c1 = rs.getClob(2);
    assertEquals("aaa", c1.getSubString(1, 3));
    rs.next();
    assertEquals("aaa", c1.getSubString(1, 3));
    rs.close();
    assertEquals("aaa", c1.getSubString(1, 3));
    stat.execute("delete from test");
    c1.getSubString(1, 3);
    // wait until it times out
    Thread.sleep(100);
    // start a new transaction, to be sure
    stat.execute("delete from test");
    assertThrows(SQLException.class, c1).getSubString(1, 3);
    conn.close();
}

Example 43 with Delete

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

the class TestLob method testLob.

private void testLob(boolean clob) throws Exception {
    deleteDb("lob");
    Connection conn = reconnect(null);
    conn = reconnect(conn);
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    PreparedStatement prep;
    ResultSet rs;
    long time;
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE " + (clob ? "CLOB" : "BLOB") + ")");
    int len = getSize(1, 1000);
    if (config.networked && config.big) {
        len = 100;
    }
    time = System.nanoTime();
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
    for (int i = 0; i < len; i += i + i + 1) {
        prep.setInt(1, i);
        int size = i * i;
        if (clob) {
            prep.setCharacterStream(2, getRandomReader(size, i), 0);
        } else {
            prep.setBinaryStream(2, getRandomStream(size, i), 0);
        }
        prep.execute();
    }
    trace("insert=" + TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - time));
    traceMemory();
    conn = reconnect(conn);
    time = System.nanoTime();
    prep = conn.prepareStatement("SELECT ID, VALUE FROM TEST");
    rs = prep.executeQuery();
    while (rs.next()) {
        int id = rs.getInt("ID");
        int size = id * id;
        if (clob) {
            Reader rt = rs.getCharacterStream(2);
            assertEqualReaders(getRandomReader(size, id), rt, -1);
            Object obj = rs.getObject(2);
            if (obj instanceof Clob) {
                obj = ((Clob) obj).getCharacterStream();
            }
            assertEqualReaders(getRandomReader(size, id), (Reader) obj, -1);
        } else {
            InputStream in = rs.getBinaryStream(2);
            assertEqualStreams(getRandomStream(size, id), in, -1);
            Object obj = rs.getObject(2);
            if (obj instanceof Blob) {
                obj = ((Blob) obj).getBinaryStream();
            }
            assertEqualStreams(getRandomStream(size, id), (InputStream) obj, -1);
        }
    }
    trace("select=" + TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - time));
    traceMemory();
    conn = reconnect(conn);
    time = System.nanoTime();
    prep = conn.prepareStatement("DELETE FROM TEST WHERE ID=?");
    for (int i = 0; i < len; i++) {
        prep.setInt(1, i);
        prep.executeUpdate();
    }
    trace("delete=" + TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - time));
    traceMemory();
    conn = reconnect(conn);
    conn.setAutoCommit(false);
    prep = conn.prepareStatement("INSERT INTO TEST VALUES(1, ?)");
    if (clob) {
        prep.setCharacterStream(1, getRandomReader(0, 0), 0);
    } else {
        prep.setBinaryStream(1, getRandomStream(0, 0), 0);
    }
    prep.execute();
    conn.rollback();
    prep.execute();
    conn.commit();
    conn.createStatement().execute("DELETE FROM TEST WHERE ID=1");
    conn.rollback();
    conn.createStatement().execute("DELETE FROM TEST WHERE ID=1");
    conn.commit();
    conn.createStatement().execute("DROP TABLE TEST");
    conn.close();
}

Example 44 with Delete

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

the class TestLob method testLobTransactions.

private void testLobTransactions(int spaceLen) throws SQLException {
    deleteDb("lob");
    Connection conn = reconnect(null);
    conn.createStatement().execute("CREATE TABLE TEST(ID IDENTITY, " + "DATA CLOB, DATA2 VARCHAR)");
    conn.setAutoCommit(false);
    Random random = new Random(0);
    int rows = 0;
    Savepoint sp = null;
    int len = getSize(100, 400);
    // config.traceTest = true;
    for (int i = 0; i < len; i++) {
        switch(random.nextInt(10)) {
            case 0:
                trace("insert " + i);
                conn.createStatement().execute("INSERT INTO TEST(DATA, DATA2) VALUES('" + i + "' || SPACE(" + spaceLen + "), '" + i + "')");
                rows++;
                break;
            case 1:
                if (rows > 0) {
                    int x = random.nextInt(rows);
                    trace("delete " + x);
                    conn.createStatement().execute("DELETE FROM TEST WHERE ID=" + x);
                }
                break;
            case 2:
                if (rows > 0) {
                    int x = random.nextInt(rows);
                    trace("update " + x);
                    conn.createStatement().execute("UPDATE TEST SET DATA='x' || DATA, " + "DATA2='x' || DATA2 WHERE ID=" + x);
                }
                break;
            case 3:
                if (rows > 0) {
                    trace("commit");
                    conn.commit();
                    sp = null;
                }
                break;
            case 4:
                if (rows > 0) {
                    trace("rollback");
                    conn.rollback();
                    sp = null;
                }
                break;
            case 5:
                trace("savepoint");
                sp = conn.setSavepoint();
                break;
            case 6:
                if (sp != null) {
                    trace("rollback to savepoint");
                    conn.rollback(sp);
                }
                break;
            case 7:
                if (rows > 0) {
                    trace("checkpoint");
                    conn.createStatement().execute("CHECKPOINT");
                    trace("shutdown immediately");
                    conn.createStatement().execute("SHUTDOWN IMMEDIATELY");
                    trace("shutdown done");
                    conn = reconnect(conn);
                    conn.setAutoCommit(false);
                    sp = null;
                }
                break;
            default:
        }
        ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
        while (rs.next()) {
            int id = rs.getInt("ID");
            String d1 = rs.getString("DATA").trim();
            String d2 = rs.getString("DATA2");
            assertEquals("id:" + id, d2, d1);
        }
    }
    conn.close();
}

Example 45 with Delete

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

the class TestLob method testCopyManyLobs.

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();
}