Examples with Sequence org.h2.schema.Sequence used on opensource projects

Example 46 with Sequence

use of org.h2.schema.Sequence 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 47 with Sequence

use of org.h2.schema.Sequence in project h2database by h2database.

the class TestTriggersConstraints method testTrigger.

private void testTrigger(final String sourceLang) throws SQLException {
    final String callSeq = "call seq.nextval";
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("DROP TABLE IF EXISTS TEST");
    stat.execute("create sequence seq");
    stat.execute("create table test(id int primary key)");
    assertSingleValue(stat, callSeq, 1);
    conn.setAutoCommit(false);
    Trigger t = new org.h2.test.db.TestTriggersConstraints.TestTriggerAlterTable();
    t.close();
    if ("java".equals(sourceLang)) {
        String triggerClassName = this.getClass().getName() + "." + TestTriggerAlterTable.class.getSimpleName();
        stat.execute("create trigger test_upd before insert on test " + "as $$org.h2.api.Trigger create() " + "{ return new " + triggerClassName + "(); } $$");
    } else if ("javascript".equals(sourceLang)) {
        String triggerClassName = this.getClass().getName() + "." + TestTriggerAlterTable.class.getSimpleName();
        final String body = "//javascript\n" + "new Packages." + triggerClassName + "();";
        stat.execute("create trigger test_upd before insert on test as $$" + body + " $$");
    } else {
        stat.execute("create trigger test_upd before insert on test call \"" + TestTriggerAlterTable.class.getName() + "\"");
    }
    stat.execute("insert into test values(1)");
    assertSingleValue(stat, callSeq, 3);
    stat.execute("alter table test add column name varchar");
    assertSingleValue(stat, callSeq, 4);
    stat.execute("drop sequence seq");
    stat.execute("drop table test");
    conn.close();
}