Examples with UpdateStatement herddb.model.commands.UpdateStatement used on opensource projects

Example 6 with UpdateStatement

use of herddb.model.commands.UpdateStatement in project herddb by diennea.

the class JSQLParserPlanner method buildUpdateStatement.

private ExecutionPlan buildUpdateStatement(String defaultTableSpace, Update update, boolean returnValues) throws StatementExecutionException {
    net.sf.jsqlparser.schema.Table table = update.getTable();
    // no alias for UPDATE!
    checkSupported(table.getAlias() == null);
    OpSchema tableSchema = getTableSchema(defaultTableSpace, table);
    TableSpaceManager tableSpaceManager = manager.getTableSpaceManager(tableSchema.tableSpace);
    AbstractTableManager tableManager = tableSpaceManager.getTableManager(tableSchema.name);
    Table tableImpl = tableManager.getTable();
    checkSupported(update.getSelect() == null);
    checkSupported(update.getJoins() == null);
    checkSupported(update.getOrderByElements() == null);
    checkSupported(update.getReturningExpressionList() == null);
    checkSupported(update.getStartJoins() == null || update.getStartJoins().isEmpty());
    List<Expression> projects = update.getExpressions();
    List<CompiledSQLExpression> expressions = new ArrayList<>(projects.size());
    int index = 0;
    List<String> updateColumnList = new ArrayList<>(projects.size());
    for (net.sf.jsqlparser.schema.Column column : update.getColumns()) {
        checkSupported(column.getTable() == null);
        String columnName = fixMySqlBackTicks(column.getColumnName().toLowerCase());
        checkSupported(!tableImpl.isPrimaryKeyColumn(columnName));
        updateColumnList.add(columnName);
        CompiledSQLExpression exp = SQLParserExpressionCompiler.compileExpression(projects.get(index), tableSchema);
        expressions.add(exp);
        index++;
    }
    RecordFunction function = new SQLRecordFunction(updateColumnList, tableImpl, expressions);
    Predicate where = null;
    if (update.getWhere() != null) {
        CompiledSQLExpression whereExpression = SQLParserExpressionCompiler.compileExpression(update.getWhere(), tableSchema);
        if (whereExpression != null) {
            SQLRecordPredicate sqlWhere = new SQLRecordPredicate(tableImpl, null, whereExpression);
            IndexUtils.discoverIndexOperations(tableSchema.tableSpace, whereExpression, tableImpl, sqlWhere, update, tableSpaceManager);
            where = sqlWhere;
        }
    }
    PlannerOp op = new SimpleUpdateOp(new UpdateStatement(tableSchema.tableSpace, tableSchema.name, null, function, where).setReturnValues(returnValues));
    return optimizePlan(op);
}

Example 7 with UpdateStatement

use of herddb.model.commands.UpdateStatement in project herddb by diennea.

the class CalcitePlanner method planUpdate.

private PlannerOp planUpdate(EnumerableTableModify dml, boolean returnValues) {
    PlannerOp input = convertRelNode(dml.getInput(), null, false, false);
    List<String> updateColumnList = dml.getUpdateColumnList();
    List<RexNode> sourceExpressionList = dml.getSourceExpressionList();
    final String tableSpace = dml.getTable().getQualifiedName().get(0);
    final String tableName = dml.getTable().getQualifiedName().get(1);
    final TableImpl tableImpl = (TableImpl) dml.getTable().unwrap(org.apache.calcite.schema.Table.class);
    Table table = tableImpl.tableManager.getTable();
    List<CompiledSQLExpression> expressionsForValue = new ArrayList<>(sourceExpressionList.size());
    List<CompiledSQLExpression> expressionsForKey = new ArrayList<>(sourceExpressionList.size());
    List<String> updateColumnListInValue = new ArrayList<>(updateColumnList.size());
    List<String> updateColumnListInPk = new ArrayList<>();
    for (int i = 0; i < updateColumnList.size(); i++) {
        String columnName = updateColumnList.get(i);
        boolean isPk = table.isPrimaryKeyColumn(columnName);
        RexNode node = sourceExpressionList.get(i);
        CompiledSQLExpression exp = SQLExpressionCompiler.compileExpression(node);
        if (isPk) {
            updateColumnListInPk.add(columnName);
            expressionsForKey.add(exp);
        } else {
            updateColumnListInValue.add(columnName);
            expressionsForValue.add(exp);
        }
    }
    if (expressionsForKey.isEmpty()) {
        // standard UPDATE, we are not updating any column in the PK
        RecordFunction function = new SQLRecordFunction(updateColumnListInValue, table, expressionsForValue);
        UpdateStatement update = null;
        if (input instanceof TableScanOp) {
            update = new UpdateStatement(tableSpace, tableName, null, function, null);
        } else if (input instanceof FilterOp) {
            FilterOp filter = (FilterOp) input;
            if (filter.getInput() instanceof TableScanOp) {
                SQLRecordPredicate pred = new SQLRecordPredicate(table, null, filter.getCondition());
                update = new UpdateStatement(tableSpace, tableName, null, function, pred);
            }
        } else if (input instanceof ProjectOp) {
            ProjectOp proj = (ProjectOp) input;
            if (proj.getInput() instanceof TableScanOp) {
                update = new UpdateStatement(tableSpace, tableName, null, function, null);
            } else if (proj.getInput() instanceof FilterOp) {
                FilterOp filter = (FilterOp) proj.getInput();
                if (filter.getInput() instanceof TableScanOp) {
                    SQLRecordPredicate pred = new SQLRecordPredicate(table, null, filter.getCondition());
                    update = new UpdateStatement(tableSpace, tableName, null, function, pred);
                }
            } else if (proj.getInput() instanceof FilteredTableScanOp) {
                FilteredTableScanOp filter = (FilteredTableScanOp) proj.getInput();
                Predicate pred = filter.getPredicate();
                update = new UpdateStatement(tableSpace, tableName, null, function, pred);
            } else if (proj.getInput() instanceof BindableTableScanOp) {
                BindableTableScanOp filter = (BindableTableScanOp) proj.getInput();
                ScanStatement scan = filter.getStatement();
                if (scan.getComparator() == null && scan.getLimits() == null && scan.getTableDef() != null) {
                    Predicate pred = scan.getPredicate();
                    update = new UpdateStatement(tableSpace, tableName, null, function, pred);
                }
            }
        }
        if (update != null) {
            return new SimpleUpdateOp(update.setReturnValues(returnValues));
        } else {
            return new UpdateOp(tableSpace, tableName, input, returnValues, function);
        }
    } else {
        // bad stuff ! we are updating the PK, we need to transform this to a sequence of delete and inserts
        // ReplaceOp won't execute the two statements atomically
        RecordFunction functionForValue = new SQLRecordFunction(updateColumnListInValue, table, expressionsForValue);
        SQLRecordKeyFunction functionForKey = new SQLRecordKeyFunction(updateColumnListInPk, expressionsForKey, table);
        return new ReplaceOp(tableSpace, tableName, input, returnValues, functionForKey, functionForValue);
    }
}

Example 8 with UpdateStatement

use of herddb.model.commands.UpdateStatement in project herddb by diennea.

the class SimpleCDCTest method testBasicCaptureDataChange.

@Test
public void testBasicCaptureDataChange() throws Exception {
    ServerConfiguration serverconfig_1 = newServerConfigurationWithAutoPort(folder.newFolder().toPath());
    serverconfig_1.set(ServerConfiguration.PROPERTY_NODEID, "server1");
    serverconfig_1.set(ServerConfiguration.PROPERTY_MODE, ServerConfiguration.PROPERTY_MODE_CLUSTER);
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_ADDRESS, testEnv.getAddress());
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_PATH, testEnv.getPath());
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_SESSIONTIMEOUT, testEnv.getTimeout());
    ClientConfiguration client_configuration = new ClientConfiguration(folder.newFolder().toPath());
    client_configuration.set(ClientConfiguration.PROPERTY_MODE, ServerConfiguration.PROPERTY_MODE_CLUSTER);
    client_configuration.set(ClientConfiguration.PROPERTY_ZOOKEEPER_ADDRESS, testEnv.getAddress());
    client_configuration.set(ClientConfiguration.PROPERTY_ZOOKEEPER_PATH, testEnv.getPath());
    client_configuration.set(ClientConfiguration.PROPERTY_ZOOKEEPER_SESSIONTIMEOUT, testEnv.getTimeout());
    try (Server server_1 = new Server(serverconfig_1)) {
        server_1.start();
        server_1.waitForStandaloneBoot();
        Table table = Table.builder().name("t1").column("c", ColumnTypes.INTEGER).column("d", ColumnTypes.INTEGER).primaryKey("c").build();
        server_1.getManager().executeStatement(new CreateTableStatement(table), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 1, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 2, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        long tx = TestUtils.beginTransaction(server_1.getManager(), TableSpace.DEFAULT);
        server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 3, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), new TransactionContext(tx));
        server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 4, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), new TransactionContext(tx));
        List<ChangeDataCapture.Mutation> mutations = new ArrayList<>();
        try (final ChangeDataCapture cdc = new ChangeDataCapture(server_1.getManager().getTableSpaceManager(TableSpace.DEFAULT).getTableSpaceUUID(), client_configuration, new ChangeDataCapture.MutationListener() {

            @Override
            public void accept(ChangeDataCapture.Mutation mutation) {
                LOG.log(Level.INFO, "mutation " + mutation);
                assertTrue(mutation.getTimestamp() > 0);
                assertNotNull(mutation.getLogSequenceNumber());
                assertNotNull(mutation.getTable());
                mutations.add(mutation);
            }
        }, LogSequenceNumber.START_OF_TIME, new InMemoryTableHistoryStorage())) {
            cdc.start();
            cdc.run();
            // we are missing the last entry, because it is not confirmed yet on BookKeeper at this point
            // also the mutations in the transaction are not visible
            assertEquals(3, mutations.size());
            // commit the transaction
            TestUtils.commitTransaction(server_1.getManager(), TableSpace.DEFAULT, tx);
            server_1.getManager().executeUpdate(new UpdateStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 4, "d", 2), null), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            cdc.run();
            assertEquals(5, mutations.size());
            server_1.getManager().executeStatement(new AlterTableStatement(Arrays.asList(Column.column("e", ColumnTypes.INTEGER)), Collections.emptyList(), Collections.emptyList(), null, table.name, TableSpace.DEFAULT, null, Collections.emptyList(), Collections.emptyList()), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            cdc.run();
            assertEquals(6, mutations.size());
            // transaction to be rolled back
            long tx2 = TestUtils.beginTransaction(server_1.getManager(), TableSpace.DEFAULT);
            server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 30, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), new TransactionContext(tx2));
            server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 31, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), new TransactionContext(tx2));
            TestUtils.roolbackTransaction(server_1.getManager(), TableSpace.DEFAULT, tx2);
            // nothing is to be sent to CDC
            cdc.run();
            assertEquals(7, mutations.size());
            server_1.getManager().executeUpdate(new DeleteStatement(TableSpace.DEFAULT, "t1", Bytes.from_int(1), null), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            cdc.run();
            assertEquals(7, mutations.size());
            // close the server...close the ledger, now we can read the last mutation
            server_1.close();
            cdc.run();
            assertEquals(8, mutations.size());
            int i = 0;
            assertEquals(ChangeDataCapture.MutationType.CREATE_TABLE, mutations.get(i++).getMutationType());
            assertEquals(ChangeDataCapture.MutationType.INSERT, mutations.get(i++).getMutationType());
            assertEquals(ChangeDataCapture.MutationType.INSERT, mutations.get(i++).getMutationType());
            assertEquals(ChangeDataCapture.MutationType.INSERT, mutations.get(i++).getMutationType());
            assertEquals(ChangeDataCapture.MutationType.INSERT, mutations.get(i++).getMutationType());
            assertEquals(ChangeDataCapture.MutationType.UPDATE, mutations.get(i++).getMutationType());
            assertEquals(ChangeDataCapture.MutationType.ALTER_TABLE, mutations.get(i++).getMutationType());
            assertEquals(ChangeDataCapture.MutationType.DELETE, mutations.get(i++).getMutationType());
        }
    }
}

Example 9 with UpdateStatement

use of herddb.model.commands.UpdateStatement in project herddb by diennea.

the class SimpleCDCTest method testBasicCaptureDataChangeWithRestart.

@Test
public void testBasicCaptureDataChangeWithRestart() throws Exception {
    ServerConfiguration serverconfig_1 = newServerConfigurationWithAutoPort(folder.newFolder().toPath());
    serverconfig_1.set(ServerConfiguration.PROPERTY_NODEID, "server1");
    serverconfig_1.set(ServerConfiguration.PROPERTY_MODE, ServerConfiguration.PROPERTY_MODE_CLUSTER);
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_ADDRESS, testEnv.getAddress());
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_PATH, testEnv.getPath());
    serverconfig_1.set(ServerConfiguration.PROPERTY_ZOOKEEPER_SESSIONTIMEOUT, testEnv.getTimeout());
    ClientConfiguration client_configuration = new ClientConfiguration(folder.newFolder().toPath());
    client_configuration.set(ClientConfiguration.PROPERTY_MODE, ServerConfiguration.PROPERTY_MODE_CLUSTER);
    client_configuration.set(ClientConfiguration.PROPERTY_ZOOKEEPER_ADDRESS, testEnv.getAddress());
    client_configuration.set(ClientConfiguration.PROPERTY_ZOOKEEPER_PATH, testEnv.getPath());
    client_configuration.set(ClientConfiguration.PROPERTY_ZOOKEEPER_SESSIONTIMEOUT, testEnv.getTimeout());
    try (Server server_1 = new Server(serverconfig_1)) {
        server_1.start();
        server_1.waitForStandaloneBoot();
        Table table = Table.builder().name("t1").column("c", ColumnTypes.INTEGER).column("d", ColumnTypes.INTEGER).primaryKey("c").build();
        server_1.getManager().executeStatement(new CreateTableStatement(table), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 1, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 2, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 3, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        server_1.getManager().executeUpdate(new InsertStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 4, "d", 2)), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        InMemoryTableHistoryStorage tableHistoryStorage = new InMemoryTableHistoryStorage();
        LogSequenceNumber currentPosition = LogSequenceNumber.START_OF_TIME;
        List<ChangeDataCapture.Mutation> mutations = new ArrayList<>();
        currentPosition = performOneCDCStep(client_configuration, server_1, tableHistoryStorage, currentPosition, mutations);
        // we are missing the last entry, because it is not confirmed yet on BookKeeper at this point
        assertEquals(4, mutations.size());
        server_1.getManager().executeUpdate(new UpdateStatement(TableSpace.DEFAULT, "t1", RecordSerializer.makeRecord(table, "c", 4, "d", 2), null), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        currentPosition = performOneCDCStep(client_configuration, server_1, tableHistoryStorage, currentPosition, mutations);
        assertEquals(5, mutations.size());
        server_1.getManager().executeStatement(new AlterTableStatement(Arrays.asList(Column.column("e", ColumnTypes.INTEGER)), Collections.emptyList(), Collections.emptyList(), null, table.name, TableSpace.DEFAULT, null, Collections.emptyList(), Collections.emptyList()), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        currentPosition = performOneCDCStep(client_configuration, server_1, tableHistoryStorage, currentPosition, mutations);
        assertEquals(6, mutations.size());
        server_1.getManager().executeUpdate(new DeleteStatement(TableSpace.DEFAULT, "t1", Bytes.from_int(1), null), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        currentPosition = performOneCDCStep(client_configuration, server_1, tableHistoryStorage, currentPosition, mutations);
        assertEquals(7, mutations.size());
        // close the server...close the ledger, now we can read the last mutation
        server_1.close();
        currentPosition = performOneCDCStep(client_configuration, server_1, tableHistoryStorage, currentPosition, mutations);
        assertEquals(8, mutations.size());
        int i = 0;
        assertEquals(ChangeDataCapture.MutationType.CREATE_TABLE, mutations.get(i++).getMutationType());
        assertEquals(ChangeDataCapture.MutationType.INSERT, mutations.get(i++).getMutationType());
        assertEquals(ChangeDataCapture.MutationType.INSERT, mutations.get(i++).getMutationType());
        assertEquals(ChangeDataCapture.MutationType.INSERT, mutations.get(i++).getMutationType());
        assertEquals(ChangeDataCapture.MutationType.INSERT, mutations.get(i++).getMutationType());
        assertEquals(ChangeDataCapture.MutationType.UPDATE, mutations.get(i++).getMutationType());
        assertEquals(ChangeDataCapture.MutationType.ALTER_TABLE, mutations.get(i++).getMutationType());
        assertEquals(ChangeDataCapture.MutationType.DELETE, mutations.get(i++).getMutationType());
    }
}

Example 10 with UpdateStatement

use of herddb.model.commands.UpdateStatement in project herddb by diennea.

the class SimpleRecoveryTest method rollbackUpdateTransactionOnRestart.

@Test
public void rollbackUpdateTransactionOnRestart() throws Exception {
    Path dataPath = folder.newFolder("data").toPath();
    Path logsPath = folder.newFolder("logs").toPath();
    Path metadataPath = folder.newFolder("metadata").toPath();
    Bytes key = Bytes.from_int(1234);
    Bytes value = Bytes.from_long(8888);
    Bytes value2 = Bytes.from_long(8889);
    Path tmoDir = folder.newFolder("tmoDir").toPath();
    String nodeId = "localhost";
    try (DBManager manager = new DBManager("localhost", new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath), tmoDir, null)) {
        manager.start();
        CreateTableSpaceStatement st1 = new CreateTableSpaceStatement("tblspace1", Collections.singleton(nodeId), nodeId, 1, 0, 0);
        manager.executeStatement(st1, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.waitForTablespace("tblspace1", 10000);
        Table table = Table.builder().tablespace("tblspace1").name("t1").column("id", ColumnTypes.STRING).column("name", ColumnTypes.STRING).primaryKey("id").build();
        CreateTableStatement st2 = new CreateTableStatement(table);
        manager.executeStatement(st2, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
        manager.checkpoint();
        InsertStatement insert = new InsertStatement("tblspace1", "t1", new Record(key, value));
        assertEquals(1, manager.executeUpdate(insert, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION).getUpdateCount());
        // transaction is not committed, and the leader dies, key appear again with the old value
        long tx = ((TransactionResult) manager.executeStatement(new BeginTransactionStatement("tblspace1"), StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION)).getTransactionId();
        UpdateStatement update = new UpdateStatement("tblspace1", "t1", new Record(key, value2), null);
        assertEquals(1, manager.executeUpdate(update, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), new TransactionContext(tx)).getUpdateCount());
    }
    try (DBManager manager = new DBManager("localhost", new FileMetadataStorageManager(metadataPath), new FileDataStorageManager(dataPath), new FileCommitLogManager(logsPath), tmoDir, null)) {
        manager.start();
        manager.waitForTablespace("tblspace1", 10000);
        {
            GetStatement get = new GetStatement("tblspace1", "t1", key, null, false);
            GetResult result = manager.get(get, StatementEvaluationContext.DEFAULT_EVALUATION_CONTEXT(), TransactionContext.NO_TRANSACTION);
            assertTrue(result.found());
            assertEquals(key, result.getRecord().key);
            assertEquals(value, result.getRecord().value);
        }
    }
}