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

Example 26 with Merge

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

the class MergeUsing method isTargetRowFound.

private boolean isTargetRowFound() {
    ResultInterface rows = targetMatchQuery.query(0);
    int countTargetRowsFound = 0;
    Value[] targetRowIdValue = null;
    while (rows.next()) {
        countTargetRowsFound++;
        targetRowIdValue = rows.currentRow();
        // throw and exception if we have processed this _ROWID_ before...
        if (targetRowidsRemembered.containsKey(targetRowIdValue[0])) {
            throw DbException.get(ErrorCode.DUPLICATE_KEY_1, "Merge using ON column expression, " + "duplicate _ROWID_ target record already updated, deleted or inserted:_ROWID_=" + targetRowIdValue[0].toString() + ":in:" + targetTableFilter.getTable() + ":conflicting source row number:" + targetRowidsRemembered.get(targetRowIdValue[0]));
        } else {
            // remember the source column values we have used before (they
            // are the effective ON clause keys
            // and should not be repeated
            targetRowidsRemembered.put(targetRowIdValue[0], sourceQueryRowNumber);
        }
    }
    rows.close();
    if (countTargetRowsFound > 1) {
        throw DbException.get(ErrorCode.DUPLICATE_KEY_1, "Duplicate key updated " + countTargetRowsFound + " rows at once, only 1 expected:_ROWID_=" + targetRowIdValue[0].toString() + ":in:" + targetTableFilter.getTable() + ":conflicting source row number:" + targetRowidsRemembered.get(targetRowIdValue[0]));
    }
    return countTargetRowsFound > 0;
}

Example 27 with Merge

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

the class Merge method update.

@Override
public int update() {
    int count;
    session.getUser().checkRight(targetTable, Right.INSERT);
    session.getUser().checkRight(targetTable, Right.UPDATE);
    setCurrentRowNumber(0);
    GeneratedKeys generatedKeys = session.getGeneratedKeys();
    if (!valuesExpressionList.isEmpty()) {
        // process values in list
        count = 0;
        generatedKeys.initialize(targetTable);
        for (int x = 0, size = valuesExpressionList.size(); x < size; x++) {
            setCurrentRowNumber(x + 1);
            generatedKeys.nextRow();
            Expression[] expr = valuesExpressionList.get(x);
            Row newRow = targetTable.getTemplateRow();
            for (int i = 0, len = columns.length; i < len; i++) {
                Column c = columns[i];
                int index = c.getColumnId();
                Expression e = expr[i];
                if (e != null) {
                    // e can be null (DEFAULT)
                    try {
                        Value v = c.convert(e.getValue(session));
                        newRow.setValue(index, v);
                        if (e instanceof SequenceValue) {
                            generatedKeys.add(c);
                        }
                    } catch (DbException ex) {
                        throw setRow(ex, count, getSQL(expr));
                    }
                }
            }
            merge(newRow);
            count++;
        }
    } else {
        // process select data for list
        query.setNeverLazy(true);
        ResultInterface rows = query.query(0);
        count = 0;
        targetTable.fire(session, Trigger.UPDATE | Trigger.INSERT, true);
        targetTable.lock(session, true, false);
        while (rows.next()) {
            count++;
            generatedKeys.nextRow();
            Value[] r = rows.currentRow();
            Row newRow = targetTable.getTemplateRow();
            setCurrentRowNumber(count);
            for (int j = 0; j < columns.length; j++) {
                Column c = columns[j];
                int index = c.getColumnId();
                try {
                    Value v = c.convert(r[j]);
                    newRow.setValue(index, v);
                } catch (DbException ex) {
                    throw setRow(ex, count, getSQL(r));
                }
            }
            merge(newRow);
        }
        rows.close();
        targetTable.fire(session, Trigger.UPDATE | Trigger.INSERT, false);
    }
    return count;
}

Example 28 with Merge

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

the class Parser method parseExplain.

private Explain parseExplain() {
    Explain command = new Explain(session);
    if (readIf("ANALYZE")) {
        command.setExecuteCommand(true);
    } else {
        if (readIf("PLAN")) {
            readIf("FOR");
        }
    }
    if (isToken("SELECT") || isToken("FROM") || isToken("(") || isToken("WITH")) {
        Query query = parseSelect();
        query.setNeverLazy(true);
        command.setCommand(query);
    } else if (readIf("DELETE")) {
        command.setCommand(parseDelete());
    } else if (readIf("UPDATE")) {
        command.setCommand(parseUpdate());
    } else if (readIf("INSERT")) {
        command.setCommand(parseInsert());
    } else if (readIf("MERGE")) {
        command.setCommand(parseMerge());
    } else {
        throw getSyntaxError();
    }
    return command;
}

Example 29 with Merge

use of org.h2.command.dml.Merge 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();
}

Example 30 with Merge

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

the class TestMvccMultiThreaded method testConcurrentMerge.

private void testConcurrentMerge() throws Exception {
    deleteDb(getTestName());
    int len = 3;
    final Connection[] connList = new Connection[len];
    for (int i = 0; i < len; i++) {
        Connection conn = getConnection(getTestName() + ";MVCC=TRUE;LOCK_TIMEOUT=500");
        connList[i] = conn;
    }
    Connection conn = connList[0];
    conn.createStatement().execute("create table test(id int primary key, name varchar)");
    Task[] tasks = new Task[len];
    final boolean[] stop = { false };
    for (int i = 0; i < len; i++) {
        final Connection c = connList[i];
        c.setAutoCommit(false);
        tasks[i] = new Task() {

            @Override
            public void call() throws Exception {
                while (!stop) {
                    c.createStatement().execute("merge into test values(1, 'x')");
                    c.commit();
                    Thread.sleep(1);
                }
            }
        };
        tasks[i].execute();
    }
    Thread.sleep(1000);
    stop[0] = true;
    for (int i = 0; i < len; i++) {
        tasks[i].get();
    }
    for (int i = 0; i < len; i++) {
        connList[i].close();
    }
    deleteDb(getTestName());
}