Examples with ExecutionPlan herddb.model.ExecutionPlan used on opensource projects

Example 1 with ExecutionPlan

use of herddb.model.ExecutionPlan in project herddb by diennea.

the class SQLPlanner method translate.

@Override
public TranslatedQuery translate(String defaultTableSpace, String query, List<Object> parameters, boolean scan, boolean allowCache, boolean returnValues, int maxRows) throws StatementExecutionException {
    if (parameters == null) {
        parameters = Collections.emptyList();
    }
    query = rewriteExecuteSyntax(query);
    String cacheKey = "scan:" + scan + ",defaultTableSpace:" + defaultTableSpace + ",query:" + query + ",returnValues:" + returnValues + ",maxRows:" + maxRows;
    if (allowCache) {
        ExecutionPlan cached = cache.get(cacheKey);
        if (cached != null) {
            return new TranslatedQuery(cached, new SQLStatementEvaluationContext(query, parameters));
        }
    }
    net.sf.jsqlparser.statement.Statement stmt = parseStatement(query);
    if (!isCachable(stmt)) {
        allowCache = false;
    }
    ExecutionPlan executionPlan = plan(defaultTableSpace, stmt, scan, returnValues, maxRows);
    if (allowCache) {
        cache.put(cacheKey, executionPlan);
    }
    return new TranslatedQuery(executionPlan, new SQLStatementEvaluationContext(query, parameters));
}

Example 2 with ExecutionPlan

use of herddb.model.ExecutionPlan in project herddb by diennea.

the class SQLPlanner method buildInsertStatement.

private ExecutionPlan buildInsertStatement(String defaultTableSpace, Insert s, boolean returnValues) throws StatementExecutionException {
    String tableSpace = s.getTable().getSchemaName();
    String tableName = s.getTable().getName();
    if (tableSpace == null) {
        tableSpace = defaultTableSpace;
    }
    TableSpaceManager tableSpaceManager = manager.getTableSpaceManager(tableSpace);
    if (tableSpaceManager == null) {
        throw new StatementExecutionException("no such tablespace " + tableSpace + " here at " + manager.getNodeId());
    }
    AbstractTableManager tableManager = tableSpaceManager.getTableManager(tableName);
    if (tableManager == null) {
        throw new StatementExecutionException("no such table " + tableName + " in tablespace " + tableSpace);
    }
    Table table = tableManager.getTable();
    ItemsList itemlist = s.getItemsList();
    if (itemlist instanceof ExpressionList) {
        int index = 0;
        List<Expression> keyValueExpression = new ArrayList<>();
        List<String> keyExpressionToColumn = new ArrayList<>();
        List<CompiledSQLExpression> valuesExpressions = new ArrayList<>();
        List<net.sf.jsqlparser.schema.Column> valuesColumns = new ArrayList<>();
        ExpressionList list = (ExpressionList) itemlist;
        if (s.getColumns() != null) {
            for (net.sf.jsqlparser.schema.Column c : s.getColumns()) {
                Column column = table.getColumn(c.getColumnName());
                if (column == null) {
                    throw new StatementExecutionException("no such column " + c.getColumnName() + " in table " + tableName + " in tablespace " + tableSpace);
                }
                Expression expression;
                try {
                    expression = list.getExpressions().get(index++);
                } catch (IndexOutOfBoundsException badQuery) {
                    throw new StatementExecutionException("bad number of VALUES in INSERT clause");
                }
                if (table.isPrimaryKeyColumn(column.name)) {
                    keyExpressionToColumn.add(column.name);
                    keyValueExpression.add(expression);
                }
                valuesColumns.add(c);
                valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, expression));
            }
        } else {
            for (Column column : table.columns) {
                Expression expression = list.getExpressions().get(index++);
                if (table.isPrimaryKeyColumn(column.name)) {
                    keyExpressionToColumn.add(column.name);
                    keyValueExpression.add(expression);
                }
                valuesColumns.add(new net.sf.jsqlparser.schema.Column(column.name));
                valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, expression));
            }
        }
        RecordFunction keyfunction;
        if (keyValueExpression.isEmpty() && table.auto_increment) {
            keyfunction = new AutoIncrementPrimaryKeyRecordFunction();
        } else {
            if (keyValueExpression.size() != table.primaryKey.length) {
                throw new StatementExecutionException("you must set a value for the primary key (expressions=" + keyValueExpression.size() + ")");
            }
            keyfunction = new SQLRecordKeyFunction(table, keyExpressionToColumn, keyValueExpression);
        }
        RecordFunction valuesfunction = new SQLRecordFunction(table, valuesColumns, valuesExpressions);
        try {
            return ExecutionPlan.simple(new InsertStatement(tableSpace, tableName, keyfunction, valuesfunction).setReturnValues(returnValues));
        } catch (IllegalArgumentException err) {
            throw new StatementExecutionException(err);
        }
    } else if (itemlist instanceof MultiExpressionList) {
        if (returnValues) {
            throw new StatementExecutionException("cannot 'return values' on multi-values insert");
        }
        MultiExpressionList multilist = (MultiExpressionList) itemlist;
        List<InsertStatement> inserts = new ArrayList<>();
        for (ExpressionList list : multilist.getExprList()) {
            List<Expression> keyValueExpression = new ArrayList<>();
            List<String> keyExpressionToColumn = new ArrayList<>();
            List<CompiledSQLExpression> valuesExpressions = new ArrayList<>();
            List<net.sf.jsqlparser.schema.Column> valuesColumns = new ArrayList<>();
            int index = 0;
            if (s.getColumns() != null) {
                for (net.sf.jsqlparser.schema.Column c : s.getColumns()) {
                    Column column = table.getColumn(c.getColumnName());
                    if (column == null) {
                        throw new StatementExecutionException("no such column " + c.getColumnName() + " in table " + tableName + " in tablespace " + tableSpace);
                    }
                    Expression expression;
                    try {
                        expression = list.getExpressions().get(index++);
                    } catch (IndexOutOfBoundsException badQuery) {
                        throw new StatementExecutionException("bad number of VALUES in INSERT clause");
                    }
                    if (table.isPrimaryKeyColumn(column.name)) {
                        keyExpressionToColumn.add(column.name);
                        keyValueExpression.add(expression);
                    }
                    valuesColumns.add(c);
                    valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, expression));
                }
            } else {
                for (Column column : table.columns) {
                    Expression expression = list.getExpressions().get(index++);
                    if (table.isPrimaryKeyColumn(column.name)) {
                        keyExpressionToColumn.add(column.name);
                        keyValueExpression.add(expression);
                    }
                    valuesColumns.add(new net.sf.jsqlparser.schema.Column(column.name));
                    valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, expression));
                }
            }
            RecordFunction keyfunction;
            if (keyValueExpression.isEmpty() && table.auto_increment) {
                keyfunction = new AutoIncrementPrimaryKeyRecordFunction();
            } else {
                if (keyValueExpression.size() != table.primaryKey.length) {
                    throw new StatementExecutionException("you must set a value for the primary key (expressions=" + keyValueExpression.size() + ")");
                }
                keyfunction = new SQLRecordKeyFunction(table, keyExpressionToColumn, keyValueExpression);
            }
            RecordFunction valuesfunction = new SQLRecordFunction(table, valuesColumns, valuesExpressions);
            InsertStatement insert = new InsertStatement(tableSpace, tableName, keyfunction, valuesfunction);
            inserts.add(insert);
        }
        try {
            return ExecutionPlan.multiInsert(inserts);
        } catch (IllegalArgumentException err) {
            throw new StatementExecutionException(err);
        }
    } else {
        List<Expression> keyValueExpression = new ArrayList<>();
        List<String> keyExpressionToColumn = new ArrayList<>();
        List<CompiledSQLExpression> valuesExpressions = new ArrayList<>();
        List<net.sf.jsqlparser.schema.Column> valuesColumns = new ArrayList<>();
        Select select = s.getSelect();
        ExecutionPlan datasource = buildSelectStatement(defaultTableSpace, select, true, -1);
        if (s.getColumns() == null) {
            throw new StatementExecutionException("for INSERT ... SELECT you have to declare the columns to be filled in (use INSERT INTO TABLE(c,c,c,) SELECT .....)");
        }
        IntHolder holder = new IntHolder(1);
        for (net.sf.jsqlparser.schema.Column c : s.getColumns()) {
            Column column = table.getColumn(c.getColumnName());
            if (column == null) {
                throw new StatementExecutionException("no such column " + c.getColumnName() + " in table " + tableName + " in tablespace " + tableSpace);
            }
            JdbcParameter readFromResultSetAsJdbcParameter = new JdbcParameter();
            readFromResultSetAsJdbcParameter.setIndex(holder.value++);
            if (table.isPrimaryKeyColumn(column.name)) {
                keyExpressionToColumn.add(column.name);
                keyValueExpression.add(readFromResultSetAsJdbcParameter);
            }
            valuesColumns.add(c);
            valuesExpressions.add(SQLExpressionCompiler.compileExpression(null, readFromResultSetAsJdbcParameter));
        }
        RecordFunction keyfunction;
        if (keyValueExpression.isEmpty() && table.auto_increment) {
            keyfunction = new AutoIncrementPrimaryKeyRecordFunction();
        } else {
            if (keyValueExpression.size() != table.primaryKey.length) {
                throw new StatementExecutionException("you must set a value for the primary key (expressions=" + keyValueExpression.size() + ")");
            }
            keyfunction = new SQLRecordKeyFunction(table, keyExpressionToColumn, keyValueExpression);
        }
        RecordFunction valuesfunction = new SQLRecordFunction(table, valuesColumns, valuesExpressions);
        try {
            return ExecutionPlan.dataManipulationFromSelect(new InsertStatement(tableSpace, tableName, keyfunction, valuesfunction).setReturnValues(returnValues), datasource);
        } catch (IllegalArgumentException err) {
            throw new StatementExecutionException(err);
        }
    }
}

Example 3 with ExecutionPlan

use of herddb.model.ExecutionPlan in project herddb by diennea.

the class SQLPlanner method plan.

@Override
public ExecutionPlan plan(String defaultTableSpace, net.sf.jsqlparser.statement.Statement stmt, boolean scan, boolean returnValues, int maxRows) {
    verifyJdbcParametersIndexes(stmt);
    ExecutionPlan result;
    if (stmt instanceof CreateTable) {
        result = ExecutionPlan.simple(buildCreateTableStatement(defaultTableSpace, (CreateTable) stmt));
    } else if (stmt instanceof CreateIndex) {
        result = ExecutionPlan.simple(buildCreateIndexStatement(defaultTableSpace, (CreateIndex) stmt));
    } else if (stmt instanceof Insert) {
        result = buildInsertStatement(defaultTableSpace, (Insert) stmt, returnValues);
    } else if (stmt instanceof Delete) {
        result = buildDeleteStatement(defaultTableSpace, (Delete) stmt, returnValues);
    } else if (stmt instanceof Update) {
        result = buildUpdateStatement(defaultTableSpace, (Update) stmt, returnValues);
    } else if (stmt instanceof Select) {
        result = buildSelectStatement(defaultTableSpace, (Select) stmt, scan, maxRows);
    } else if (stmt instanceof Execute) {
        result = ExecutionPlan.simple(buildExecuteStatement(defaultTableSpace, (Execute) stmt));
    } else if (stmt instanceof Alter) {
        result = ExecutionPlan.simple(buildAlterStatement(defaultTableSpace, (Alter) stmt));
    } else if (stmt instanceof Drop) {
        result = ExecutionPlan.simple(buildDropStatement(defaultTableSpace, (Drop) stmt));
    } else if (stmt instanceof Truncate) {
        result = ExecutionPlan.simple(buildTruncateStatement(defaultTableSpace, (Truncate) stmt));
    } else {
        return null;
    }
    return result;
}

Example 4 with ExecutionPlan

use of herddb.model.ExecutionPlan in project herddb by diennea.

the class JSQLParserPlanner method translate.

@Override
public TranslatedQuery translate(String defaultTableSpace, String query, List<Object> parameters, boolean scan, boolean allowCache, boolean returnValues, int maxRows) throws StatementExecutionException {
    ensureDefaultTableSpaceBootedLocally(defaultTableSpace);
    if (parameters == null) {
        parameters = Collections.emptyList();
    }
    /*
         * Strips out leading comments
         */
    int idx = SQLUtils.findQueryStart(query);
    if (idx != -1) {
        query = query.substring(idx);
    }
    query = rewriteExecuteSyntax(query);
    if (query.startsWith("ALTER TABLE") && query.contains("ADD FOREIGN KEY")) {
        // jsqlparser does not support unnamed foreign keys in "ALTER TABLE"
        query = query.replace("ADD FOREIGN KEY", "ADD CONSTRAINT generate_unnamed FOREIGN KEY");
    }
    if (query.startsWith("EXPLAIN ")) {
        query = query.substring("EXPLAIN ".length());
        net.sf.jsqlparser.statement.Statement stmt = parseStatement(query);
        if (!isCachable(stmt)) {
            allowCache = false;
        }
        ExecutionPlan queryExecutionPlan = plan(defaultTableSpace, stmt, scan, returnValues, maxRows);
        PlannerOp finalPlan = queryExecutionPlan.originalRoot;
        ValuesOp values = new ValuesOp(manager.getNodeId(), new String[] { "name", "value" }, new Column[] { column("name", ColumnTypes.STRING), column("value", ColumnTypes.STRING) }, java.util.Arrays.asList(java.util.Arrays.asList(new ConstantExpression("query", ColumnTypes.NOTNULL_STRING), new ConstantExpression(query, ColumnTypes.NOTNULL_STRING)), java.util.Arrays.asList(new ConstantExpression("logicalplan", ColumnTypes.NOTNULL_STRING), new ConstantExpression(stmt + "", ColumnTypes.NOTNULL_STRING)), java.util.Arrays.asList(new ConstantExpression("plan", ColumnTypes.NOTNULL_STRING), new ConstantExpression(finalPlan + "", ColumnTypes.NOTNULL_STRING)), java.util.Arrays.asList(new ConstantExpression("finalplan", ColumnTypes.NOTNULL_STRING), // same as "plan"
        new ConstantExpression(finalPlan.optimize(), ColumnTypes.NOTNULL_STRING))));
        ExecutionPlan executionPlan = ExecutionPlan.simple(new SQLPlannedOperationStatement(values), values);
        return new TranslatedQuery(executionPlan, new SQLStatementEvaluationContext(query, parameters, false, false));
    }
    if (query.startsWith("SHOW")) {
        return calculateShowCreateTable(query, defaultTableSpace, parameters, manager);
    }
    String cacheKey = "scan:" + scan + ",defaultTableSpace:" + defaultTableSpace + ",query:" + query + ",returnValues:" + returnValues + ",maxRows:" + maxRows;
    try {
        boolean forceAcquireWriteLock;
        if (// this looks very hacky
        query.endsWith(" FOR UPDATE") && query.substring(0, 6).toLowerCase().equals("select")) {
            forceAcquireWriteLock = true;
            query = query.substring(0, query.length() - " FOR UPDATE".length());
        } else {
            forceAcquireWriteLock = false;
        }
        if (allowCache) {
            ExecutionPlan cached = cache.get(cacheKey);
            if (cached != null) {
                return new TranslatedQuery(cached, new SQLStatementEvaluationContext(query, parameters, forceAcquireWriteLock, false));
            }
        }
        if (query.startsWith(TABLE_CONSISTENCY_COMMAND)) {
            ExecutionPlan executionPlan = ExecutionPlan.simple(JSQLParserPlanner.this.queryConsistencyCheckStatement(defaultTableSpace, query, parameters));
            return new TranslatedQuery(executionPlan, new SQLStatementEvaluationContext(query, parameters, false, false));
        }
        if (query.startsWith(TABLESPACE_CONSISTENCY_COMMAND)) {
            ExecutionPlan executionPlan = ExecutionPlan.simple(JSQLParserPlanner.this.queryConsistencyCheckStatement(query));
            return new TranslatedQuery(executionPlan, new SQLStatementEvaluationContext(query, parameters, false, false));
        }
        net.sf.jsqlparser.statement.Statement stmt = parseStatement(query);
        if (!isCachable(stmt)) {
            allowCache = false;
        }
        ExecutionPlan executionPlan = plan(defaultTableSpace, stmt, scan, returnValues, maxRows);
        if (LOG.isLoggable(DUMP_QUERY_LEVEL)) {
            LOG.log(DUMP_QUERY_LEVEL, "Query: {0} --HerdDB Plan\n{1}", new Object[] { query, executionPlan.mainStatement });
        }
        if (allowCache) {
            cache.put(cacheKey, executionPlan);
        }
        return new TranslatedQuery(executionPlan, new SQLStatementEvaluationContext(query, parameters, forceAcquireWriteLock, false));
    } catch (StatementNotSupportedException err) {
        if (fallback == null) {
            throw new StatementExecutionException("I am sorry, I cannot plan SQL \"" + query + "\" with simple jSQLParser planner," + " consider setting " + ServerConfiguration.PROPERTY_PLANNER_TYPE + "=" + ServerConfiguration.PLANNER_TYPE_AUTO, err);
        }
        TranslatedQuery res = fallback.translate(defaultTableSpace, query, parameters, scan, allowCache, returnValues, maxRows);
        if (allowCache) {
            // cache plan from Calcite, not need to try jSQLParser again
            cache.put(cacheKey, res.plan);
        }
        return res;
    }
}

Example 5 with ExecutionPlan

use of herddb.model.ExecutionPlan in project herddb by diennea.

the class DBManager method executeJoinedScansPlan.

private StatementExecutionResult executeJoinedScansPlan(List<DataScanner> scanResults, StatementEvaluationContext context, TransactionContext transactionContext, ExecutionPlan plan) throws StatementExecutionException {
    try {
        List<Column> composedSchema = new ArrayList<>();
        for (DataScanner ds : scanResults) {
            composedSchema.addAll(Arrays.asList(ds.getSchema()));
        }
        Column[] finalSchema = new Column[composedSchema.size()];
        composedSchema.toArray(finalSchema);
        String[] finalSchemaFieldNames = Column.buildFieldNamesList(finalSchema);
        MaterializedRecordSet finalResultSet = recordSetFactory.createRecordSet(finalSchemaFieldNames, finalSchema);
        DataScannerJoinExecutor joinExecutor;
        if (plan.joinProjection != null) {
            if (plan.joinFilter != null) {
                TuplePredicate joinFilter = plan.joinFilter;
                joinExecutor = new DataScannerJoinExecutor(finalSchemaFieldNames, finalSchema, scanResults, t -> {
                    if (joinFilter.matches(t, context)) {
                        finalResultSet.add(plan.joinProjection.map(t, context));
                    }
                });
            } else {
                joinExecutor = new DataScannerJoinExecutor(finalSchemaFieldNames, finalSchema, scanResults, t -> {
                    finalResultSet.add(plan.joinProjection.map(t, context));
                });
            }
        } else {
            if (plan.joinFilter != null) {
                TuplePredicate joinFilter = plan.joinFilter;
                joinExecutor = new DataScannerJoinExecutor(finalSchemaFieldNames, finalSchema, scanResults, t -> {
                    if (joinFilter.matches(t, context)) {
                        finalResultSet.add(t);
                    }
                });
            } else {
                joinExecutor = new DataScannerJoinExecutor(finalSchemaFieldNames, finalSchema, scanResults, t -> {
                    finalResultSet.add(t);
                });
            }
        }
        joinExecutor.executeJoin();
        finalResultSet.writeFinished();
        finalResultSet.sort(plan.comparator);
        finalResultSet.applyLimits(plan.limits, context);
        return new ScanResult(transactionContext.transactionId, new SimpleDataScanner(transactionContext.transactionId, finalResultSet));
    } catch (DataScannerException err) {
        throw new StatementExecutionException(err);
    }
}