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

Example 86 with Delete

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

the class TestTransactionStore method testSingleConnection.

private void testSingleConnection() {
    MVStore s = MVStore.open(null);
    TransactionStore ts = new TransactionStore(s);
    ts.init();
    Transaction tx;
    TransactionMap<String, String> m;
    // add, rollback
    tx = ts.begin();
    m = tx.openMap("test");
    m.put("1", "Hello");
    assertEquals("Hello", m.get("1"));
    m.put("2", "World");
    assertEquals("World", m.get("2"));
    tx.rollback();
    tx = ts.begin();
    m = tx.openMap("test");
    assertNull(m.get("1"));
    assertNull(m.get("2"));
    // add, commit
    tx = ts.begin();
    m = tx.openMap("test");
    m.put("1", "Hello");
    m.put("2", "World");
    assertEquals("Hello", m.get("1"));
    assertEquals("World", m.get("2"));
    tx.commit();
    tx = ts.begin();
    m = tx.openMap("test");
    assertEquals("Hello", m.get("1"));
    assertEquals("World", m.get("2"));
    // update+delete+insert, rollback
    tx = ts.begin();
    m = tx.openMap("test");
    m.put("1", "Hallo");
    m.remove("2");
    m.put("3", "!");
    assertEquals("Hallo", m.get("1"));
    assertNull(m.get("2"));
    assertEquals("!", m.get("3"));
    tx.rollback();
    tx = ts.begin();
    m = tx.openMap("test");
    assertEquals("Hello", m.get("1"));
    assertEquals("World", m.get("2"));
    assertNull(m.get("3"));
    // update+delete+insert, commit
    tx = ts.begin();
    m = tx.openMap("test");
    m.put("1", "Hallo");
    m.remove("2");
    m.put("3", "!");
    assertEquals("Hallo", m.get("1"));
    assertNull(m.get("2"));
    assertEquals("!", m.get("3"));
    tx.commit();
    tx = ts.begin();
    m = tx.openMap("test");
    assertEquals("Hallo", m.get("1"));
    assertNull(m.get("2"));
    assertEquals("!", m.get("3"));
    ts.close();
    s.close();
}

Example 87 with Delete

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

the class TestTransactionStore method testMultiStatement.

/**
 * Tests behavior when used for a sequence of SQL statements. Each statement
 * uses a savepoint. Within a statement, changes by the statement itself are
 * not seen; the change is only seen when the statement finished.
 * <p>
 * Update statements that change the key of multiple rows may use delete/add
 * pairs to do so (they don't need to first delete all entries and then
 * re-add them). Trying to add multiple values for the same key is not
 * allowed (an update statement that would result in a duplicate key).
 */
private void testMultiStatement() {
    MVStore s = MVStore.open(null);
    TransactionStore ts = new TransactionStore(s);
    ts.init();
    Transaction tx;
    TransactionMap<String, String> m;
    long startUpdate;
    tx = ts.begin();
    // start of statement
    // create table test
    startUpdate = tx.setSavepoint();
    m = tx.openMap("test");
    m.setSavepoint(startUpdate);
    // start of statement
    // insert into test(id, name) values(1, 'Hello'), (2, 'World')
    startUpdate = tx.setSavepoint();
    m.setSavepoint(startUpdate);
    assertTrue(m.trySet("1", "Hello", true));
    assertTrue(m.trySet("2", "World", true));
    // not seen yet (within the same statement)
    assertNull(m.get("1"));
    assertNull(m.get("2"));
    // start of statement
    startUpdate = tx.setSavepoint();
    // now we see the newest version
    m.setSavepoint(startUpdate);
    assertEquals("Hello", m.get("1"));
    assertEquals("World", m.get("2"));
    // update test set primaryKey = primaryKey + 1
    // (this is usually a tricky case)
    assertEquals("Hello", m.get("1"));
    assertTrue(m.trySet("1", null, true));
    assertTrue(m.trySet("2", "Hello", true));
    assertEquals("World", m.get("2"));
    // already updated by this statement, so it has no effect
    // but still returns true because it was changed by this transaction
    assertTrue(m.trySet("2", null, true));
    assertTrue(m.trySet("3", "World", true));
    // not seen within this statement
    assertEquals("Hello", m.get("1"));
    assertEquals("World", m.get("2"));
    assertNull(m.get("3"));
    // start of statement
    startUpdate = tx.setSavepoint();
    m.setSavepoint(startUpdate);
    // select * from test
    assertNull(m.get("1"));
    assertEquals("Hello", m.get("2"));
    assertEquals("World", m.get("3"));
    // start of statement
    startUpdate = tx.setSavepoint();
    m.setSavepoint(startUpdate);
    // update test set id = 1
    // should fail: duplicate key
    assertTrue(m.trySet("2", null, true));
    assertTrue(m.trySet("1", "Hello", true));
    assertTrue(m.trySet("3", null, true));
    assertFalse(m.trySet("1", "World", true));
    tx.rollbackToSavepoint(startUpdate);
    startUpdate = tx.setSavepoint();
    m.setSavepoint(startUpdate);
    assertNull(m.get("1"));
    assertEquals("Hello", m.get("2"));
    assertEquals("World", m.get("3"));
    tx.commit();
    ts.close();
    s.close();
}

Example 88 with Delete

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

the class TestTransactionStore method testCompareWithPostgreSQL.

private void testCompareWithPostgreSQL() throws Exception {
    ArrayList<Statement> statements = New.arrayList();
    ArrayList<Transaction> transactions = New.arrayList();
    ArrayList<TransactionMap<Integer, String>> maps = New.arrayList();
    int connectionCount = 3, opCount = 1000, rowCount = 10;
    try {
        Class.forName("org.postgresql.Driver");
        for (int i = 0; i < connectionCount; i++) {
            Connection conn = DriverManager.getConnection("jdbc:postgresql:test?loggerLevel=OFF", "sa", "sa");
            statements.add(conn.createStatement());
        }
    } catch (Exception e) {
        // database not installed - ok
        return;
    }
    statements.get(0).execute("drop table if exists test cascade");
    statements.get(0).execute("create table test(id int primary key, name varchar(255))");
    MVStore s = MVStore.open(null);
    TransactionStore ts = new TransactionStore(s);
    ts.init();
    for (int i = 0; i < connectionCount; i++) {
        Statement stat = statements.get(i);
        // 100 ms to avoid blocking (the test is single threaded)
        stat.execute("set statement_timeout to 100");
        Connection c = stat.getConnection();
        c.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        c.setAutoCommit(false);
        Transaction transaction = ts.begin();
        transactions.add(transaction);
        TransactionMap<Integer, String> map;
        map = transaction.openMap("test");
        maps.add(map);
    }
    StringBuilder buff = new StringBuilder();
    Random r = new Random(1);
    try {
        for (int i = 0; i < opCount; i++) {
            int connIndex = r.nextInt(connectionCount);
            Statement stat = statements.get(connIndex);
            Transaction transaction = transactions.get(connIndex);
            TransactionMap<Integer, String> map = maps.get(connIndex);
            if (transaction == null) {
                transaction = ts.begin();
                map = transaction.openMap("test");
                transactions.set(connIndex, transaction);
                maps.set(connIndex, map);
                // read all data, to get a snapshot
                ResultSet rs = stat.executeQuery("select * from test order by id");
                buff.append(i).append(": [" + connIndex + "]=");
                int size = 0;
                while (rs.next()) {
                    buff.append(' ');
                    int k = rs.getInt(1);
                    String v = rs.getString(2);
                    buff.append(k).append(':').append(v);
                    assertEquals(v, map.get(k));
                    size++;
                }
                buff.append('\n');
                if (size != map.sizeAsLong()) {
                    assertEquals(size, map.sizeAsLong());
                }
            }
            int x = r.nextInt(rowCount);
            int y = r.nextInt(rowCount);
            buff.append(i).append(": [" + connIndex + "]: ");
            ResultSet rs = null;
            switch(r.nextInt(7)) {
                case 0:
                    buff.append("commit");
                    stat.getConnection().commit();
                    transaction.commit();
                    transactions.set(connIndex, null);
                    break;
                case 1:
                    buff.append("rollback");
                    stat.getConnection().rollback();
                    transaction.rollback();
                    transactions.set(connIndex, null);
                    break;
                case 2:
                    // insert or update
                    String old = map.get(x);
                    if (old == null) {
                        buff.append("insert " + x + "=" + y);
                        if (map.tryPut(x, "" + y)) {
                            stat.execute("insert into test values(" + x + ", '" + y + "')");
                        } else {
                            buff.append(" -> row was locked");
                        // the statement would time out in PostgreSQL
                        // TODO test sometimes if timeout occurs
                        }
                    } else {
                        buff.append("update " + x + "=" + y + " (old:" + old + ")");
                        if (map.tryPut(x, "" + y)) {
                            int c = stat.executeUpdate("update test set name = '" + y + "' where id = " + x);
                            assertEquals(1, c);
                        } else {
                            buff.append(" -> row was locked");
                        // the statement would time out in PostgreSQL
                        // TODO test sometimes if timeout occurs
                        }
                    }
                    break;
                case 3:
                    buff.append("delete " + x);
                    try {
                        int c = stat.executeUpdate("delete from test where id = " + x);
                        if (c == 1) {
                            map.remove(x);
                        } else {
                            assertNull(map.get(x));
                        }
                    } catch (SQLException e) {
                        assertNotNull(map.get(x));
                        assertFalse(map.tryRemove(x));
                        // PostgreSQL needs to rollback
                        buff.append(" -> rollback");
                        stat.getConnection().rollback();
                        transaction.rollback();
                        transactions.set(connIndex, null);
                    }
                    break;
                case 4:
                case 5:
                case 6:
                    rs = stat.executeQuery("select * from test where id = " + x);
                    String expected = rs.next() ? rs.getString(2) : null;
                    buff.append("select " + x + "=" + expected);
                    assertEquals("i:" + i, expected, map.get(x));
                    break;
            }
            buff.append('\n');
        }
    } catch (Exception e) {
        e.printStackTrace();
        fail(buff.toString());
    }
    for (Statement stat : statements) {
        stat.getConnection().close();
    }
    ts.close();
    s.close();
}

Example 89 with Delete

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

the class TestOptimizations method testMinMaxCountOptimization.

private void testMinMaxCountOptimization(boolean memory) throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("create " + (memory ? "memory" : "") + " table test(id int primary key, value int)");
    stat.execute("create index idx_value_id on test(value, id);");
    int len = getSize(1000, 10000);
    HashMap<Integer, Integer> map = new HashMap<>();
    TreeSet<Integer> set = new TreeSet<>();
    Random random = new Random(1);
    for (int i = 0; i < len; i++) {
        if (i == len / 2) {
            if (!config.memory) {
                conn.close();
                conn = getConnection("optimizations");
                stat = conn.createStatement();
            }
        }
        switch(random.nextInt(10)) {
            case 0:
            case 1:
            case 2:
            case 3:
            case 4:
            case 5:
                if (random.nextInt(1000) == 1) {
                    stat.execute("insert into test values(" + i + ", null)");
                    map.put(i, null);
                } else {
                    int value = random.nextInt();
                    stat.execute("insert into test values(" + i + ", " + value + ")");
                    map.put(i, value);
                    set.add(value);
                }
                break;
            case 6:
            case 7:
            case 8:
                {
                    if (map.size() > 0) {
                        for (int j = random.nextInt(i), k = 0; k < 10; k++, j++) {
                            if (map.containsKey(j)) {
                                Integer x = map.remove(j);
                                if (x != null) {
                                    set.remove(x);
                                }
                                stat.execute("delete from test where id=" + j);
                            }
                        }
                    }
                    break;
                }
            case 9:
                {
                    ArrayList<Integer> list = new ArrayList<>(map.values());
                    int count = list.size();
                    Integer min = null, max = null;
                    if (count > 0) {
                        min = set.first();
                        max = set.last();
                    }
                    ResultSet rs = stat.executeQuery("select min(value), max(value), count(*) from test");
                    rs.next();
                    Integer minDb = (Integer) rs.getObject(1);
                    Integer maxDb = (Integer) rs.getObject(2);
                    int countDb = rs.getInt(3);
                    assertEquals(minDb, min);
                    assertEquals(maxDb, max);
                    assertEquals(countDb, count);
                    break;
                }
            default:
        }
    }
    conn.close();
}

Example 90 with Delete

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

the class TestPowerOff method testRun.

private int testRun(boolean init) throws SQLException {
    if (init) {
        Database.setInitialPowerOffCount(Integer.MAX_VALUE);
    }
    int state = 0;
    Connection conn = null;
    try {
        conn = getConnection(url);
        Statement stat = conn.createStatement();
        stat.execute("SET WRITE_DELAY 0");
        stat.execute("CREATE TABLE IF NOT EXISTS TEST" + "(ID INT PRIMARY KEY, NAME VARCHAR(255))");
        state = 1;
        conn.setAutoCommit(false);
        stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
        stat.execute("INSERT INTO TEST VALUES(2, 'World')");
        conn.commit();
        state = 2;
        stat.execute("UPDATE TEST SET NAME='Hallo' WHERE ID=1");
        stat.execute("UPDATE TEST SET NAME='Welt' WHERE ID=2");
        conn.commit();
        state = 3;
        stat.execute("DELETE FROM TEST WHERE ID=1");
        stat.execute("DELETE FROM TEST WHERE ID=2");
        conn.commit();
        state = 1;
        stat.execute("DROP TABLE TEST");
        state = 0;
        if (init) {
            maxPowerOffCount = Integer.MAX_VALUE - ((JdbcConnection) conn).getPowerOffCount();
        }
        conn.close();
    } catch (SQLException e) {
        if (e.getSQLState().equals("" + ErrorCode.DATABASE_IS_CLOSED)) {
        // this is ok
        } else {
            throw e;
        }
    }
    JdbcUtils.closeSilently(conn);
    return state;
}