Examples with DBAggregationSelectFunctionTemplate io.siddhi.core.aggregation.persistedaggregation.config.DBAggregationSelectFunctionTemplate used on opensource projects

Example 1 with DBAggregationSelectFunctionTemplate

use of io.siddhi.core.aggregation.persistedaggregation.config.DBAggregationSelectFunctionTemplate in project siddhi by wso2.

the class AggregationParser method generateDatabaseQuery.

private static String generateDatabaseQuery(List<ExpressionExecutor> expressionExecutors, DBAggregationQueryConfigurationEntry dbAggregationQueryConfigurationEntry, StreamDefinition incomingOutputStreamDefinition, boolean isDistributed, String shardID, boolean isProcessingOnExternalTime, Table aggregationTable, Table parentAggregationTable, List<Variable> groupByVariableList, TimePeriod.Duration duration) {
    DBAggregationSelectFunctionTemplate dbAggregationSelectFunctionTemplates = dbAggregationQueryConfigurationEntry.getRdbmsSelectFunctionTemplate();
    DBAggregationSelectQueryTemplate dbAggregationSelectQueryTemplate = dbAggregationQueryConfigurationEntry.getRdbmsSelectQueryTemplate();
    DBAggregationTimeConversionDurationMapping dbAggregationTimeConversionDurationMapping = dbAggregationQueryConfigurationEntry.getDbAggregationTimeConversionDurationMapping();
    List<Attribute> attributeList = incomingOutputStreamDefinition.getAttributeList();
    List<String> groupByColumnNames = new ArrayList<>();
    StringJoiner outerSelectColumnJoiner = new StringJoiner(", ");
    StringJoiner subSelectT1ColumnJoiner = new StringJoiner(", ", SQL_SELECT, " ");
    StringJoiner subSelectT2ColumnJoiner = new StringJoiner(", ");
    StringJoiner innerSelectT2ColumnJoiner = new StringJoiner(", ", SQL_SELECT, " ");
    StringJoiner onConditionBuilder = new StringJoiner(SQL_AND);
    StringJoiner subSelectT2OnConditionBuilder = new StringJoiner(SQL_AND);
    StringJoiner groupByQueryBuilder = new StringJoiner(", ");
    StringJoiner groupByT3QueryBuilder = new StringJoiner(", ");
    StringJoiner finalSelectQuery = new StringJoiner(" ");
    StringJoiner completeQuery = new StringJoiner(" ");
    StringJoiner insertIntoColumns = new StringJoiner(", ");
    StringBuilder filterQueryBuilder = new StringBuilder();
    StringBuilder insertIntoQueryBuilder = new StringBuilder();
    String innerFromClause = SQL_FROM + parentAggregationTable.getTableDefinition().getId();
    String innerWhereFilterClause;
    String groupByClause;
    String innerT2WhereCondition;
    StringJoiner innerT2Query = new StringJoiner(" ");
    StringJoiner subQueryT1 = new StringJoiner(" ");
    StringJoiner subQueryT2 = new StringJoiner(" ");
    attributeList.stream().forEach(attribute -> insertIntoColumns.add(attribute.getName()));
    int i = 0;
    insertIntoQueryBuilder.append(dbAggregationSelectQueryTemplate.getRecordInsertQuery().replace(PLACEHOLDER_TABLE_NAME, aggregationTable.getTableDefinition().getId()).replace(PLACEHOLDER_COLUMNS, insertIntoColumns.toString()));
    filterQueryBuilder.append(" (").append(AGG_START_TIMESTAMP_COL).append(" >= ?").append(" AND ").append(AGG_START_TIMESTAMP_COL).append(" < ? ").append(") ");
    if (isDistributed) {
        filterQueryBuilder.append(" AND ").append(AGG_SHARD_ID_COL).append(" = '").append(shardID).append("' ");
        groupByQueryBuilder.add(AGG_SHARD_ID_COL);
        groupByT3QueryBuilder.add(INNER_SELECT_QUERY_REF_T3 + "." + AGG_SHARD_ID_COL);
        innerSelectT2ColumnJoiner.add(AGG_SHARD_ID_COL);
        subSelectT2OnConditionBuilder.add(parentAggregationTable.getTableDefinition().getId() + "." + AGG_SHARD_ID_COL + EQUALS + INNER_SELECT_QUERY_REF_T3 + "." + AGG_SHARD_ID_COL);
        if (isProcessingOnExternalTime) {
            subSelectT1ColumnJoiner.add(AGG_SHARD_ID_COL);
        }
    }
    if (isProcessingOnExternalTime) {
        groupByVariableList.stream().forEach(variable -> {
            groupByColumnNames.add(variable.getAttributeName());
            groupByQueryBuilder.add(variable.getAttributeName());
            groupByT3QueryBuilder.add(INNER_SELECT_QUERY_REF_T3 + "." + variable.getAttributeName());
            onConditionBuilder.add(SUB_SELECT_QUERY_REF_T1 + "." + variable.getAttributeName() + EQUALS + SUB_SELECT_QUERY_REF_T2 + "." + variable.getAttributeName());
            subSelectT2OnConditionBuilder.add(parentAggregationTable.getTableDefinition().getId() + "." + variable.getAttributeName() + EQUALS + INNER_SELECT_QUERY_REF_T3 + "." + variable.getAttributeName());
        });
        innerT2WhereCondition = INNER_SELECT_QUERY_REF_T3 + "." + groupByVariableList.get(0).getAttributeName() + SQL_NOT_NULL;
        for (ExpressionExecutor expressionExecutor : expressionExecutors) {
            if (expressionExecutor instanceof VariableExpressionExecutor) {
                VariableExpressionExecutor variableExpressionExecutor = (VariableExpressionExecutor) expressionExecutor;
                if (variableExpressionExecutor.getAttribute().getName().equals(AGG_START_TIMESTAMP_COL)) {
                    outerSelectColumnJoiner.add(" ? " + SQL_AS + variableExpressionExecutor.getAttribute().getName());
                } else if (!variableExpressionExecutor.getAttribute().getName().equals(AGG_EXTERNAL_TIMESTAMP_COL)) {
                    if (groupByColumnNames.contains(variableExpressionExecutor.getAttribute().getName())) {
                        subSelectT2ColumnJoiner.add(INNER_SELECT_QUERY_REF_T3 + "." + variableExpressionExecutor.getAttribute().getName() + SQL_AS + variableExpressionExecutor.getAttribute().getName());
                        outerSelectColumnJoiner.add(SUB_SELECT_QUERY_REF_T1 + "." + variableExpressionExecutor.getAttribute().getName() + SQL_AS + attributeList.get(i).getName());
                        subSelectT1ColumnJoiner.add(variableExpressionExecutor.getAttribute().getName());
                        innerSelectT2ColumnJoiner.add(variableExpressionExecutor.getAttribute().getName());
                    } else {
                        subSelectT2ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getMaxFunction().replace(PLACEHOLDER_COLUMN, variableExpressionExecutor.getAttribute().getName()) + SQL_AS + variableExpressionExecutor.getAttribute().getName());
                        outerSelectColumnJoiner.add(SUB_SELECT_QUERY_REF_T2 + "." + variableExpressionExecutor.getAttribute().getName() + SQL_AS + attributeList.get(i).getName());
                    }
                }
            } else if (expressionExecutor instanceof IncrementalAggregateBaseTimeFunctionExecutor) {
                if (attributeList.get(i).getName().equals(AGG_EXTERNAL_TIMESTAMP_COL)) {
                    outerSelectColumnJoiner.add(SUB_SELECT_QUERY_REF_T1 + "." + AGG_EXTERNAL_TIMESTAMP_COL + SQL_AS + AGG_EXTERNAL_TIMESTAMP_COL);
                    subSelectT1ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getTimeConversionFunction().replace(PLACEHOLDER_COLUMN, AGG_EXTERNAL_TIMESTAMP_COL).replace(PLACEHOLDER_DURATION, dbAggregationTimeConversionDurationMapping.getDurationMapping(duration)) + SQL_AS + AGG_EXTERNAL_TIMESTAMP_COL);
                    subSelectT2ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getTimeConversionFunction().replace(PLACEHOLDER_COLUMN, AGG_EXTERNAL_TIMESTAMP_COL).replace(PLACEHOLDER_DURATION, dbAggregationTimeConversionDurationMapping.getDurationMapping(duration)) + SQL_AS + AGG_EXTERNAL_TIMESTAMP_COL);
                    onConditionBuilder.add(SUB_SELECT_QUERY_REF_T1 + "." + AGG_EXTERNAL_TIMESTAMP_COL + EQUALS + SUB_SELECT_QUERY_REF_T2 + "." + AGG_EXTERNAL_TIMESTAMP_COL);
                } else {
                    outerSelectColumnJoiner.add(" ? " + SQL_AS + attributeList.get(i).getName());
                }
            } else if (expressionExecutor instanceof MaxAttributeAggregatorExecutor) {
                if (attributeList.get(i).getName().equals(AGG_LAST_TIMESTAMP_COL)) {
                    innerSelectT2ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getMaxFunction().replace(PLACEHOLDER_COLUMN, attributeList.get(i).getName()) + SQL_AS + attributeList.get(i).getName());
                    subSelectT2ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getMaxFunction().replace(PLACEHOLDER_COLUMN, INNER_SELECT_QUERY_REF_T3 + "." + attributeList.get(i).getName()) + SQL_AS + attributeList.get(i).getName());
                    outerSelectColumnJoiner.add(SUB_SELECT_QUERY_REF_T2 + "." + attributeList.get(i).getName() + SQL_AS + attributeList.get(i).getName());
                    subSelectT2OnConditionBuilder.add(parentAggregationTable.getTableDefinition().getId() + "." + attributeList.get(i).getName() + EQUALS + INNER_SELECT_QUERY_REF_T3 + "." + attributeList.get(i).getName());
                } else {
                    outerSelectColumnJoiner.add(SUB_SELECT_QUERY_REF_T1 + "." + attributeList.get(i).getName() + SQL_AS + attributeList.get(i).getName());
                    subSelectT1ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getMaxFunction().replace(PLACEHOLDER_COLUMN, attributeList.get(i).getName()) + SQL_AS + attributeList.get(i).getName());
                }
            } else if (expressionExecutor instanceof MinAttributeAggregatorExecutor) {
                outerSelectColumnJoiner.add(SUB_SELECT_QUERY_REF_T1 + "." + attributeList.get(i).getName() + SQL_AS + attributeList.get(i).getName());
                subSelectT1ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getMinFunction().replace(PLACEHOLDER_COLUMN, attributeList.get(i).getName()) + SQL_AS + attributeList.get(i).getName());
            } else if (expressionExecutor instanceof ConstantExpressionExecutor) {
                outerSelectColumnJoiner.add(SUB_SELECT_QUERY_REF_T1 + "." + attributeList.get(i).getName() + SQL_AS + attributeList.get(i).getName());
            } else if (expressionExecutor instanceof SumAttributeAggregatorExecutor) {
                outerSelectColumnJoiner.add(SUB_SELECT_QUERY_REF_T1 + "." + attributeList.get(i).getName() + SQL_AS + attributeList.get(i).getName());
                subSelectT1ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getSumFunction().replace(PLACEHOLDER_COLUMN, attributeList.get(i).getName()) + SQL_AS + attributeList.get(i).getName());
            }
            i++;
        }
        groupByQueryBuilder.add(dbAggregationSelectFunctionTemplates.getTimeConversionFunction().replace(PLACEHOLDER_COLUMN, AGG_EXTERNAL_TIMESTAMP_COL).replace(PLACEHOLDER_DURATION, dbAggregationTimeConversionDurationMapping.getDurationMapping(duration)));
        groupByT3QueryBuilder.add(dbAggregationSelectFunctionTemplates.getTimeConversionFunction().replace(PLACEHOLDER_COLUMN, AGG_EXTERNAL_TIMESTAMP_COL).replace(PLACEHOLDER_DURATION, dbAggregationTimeConversionDurationMapping.getDurationMapping(duration)));
        groupByClause = dbAggregationSelectQueryTemplate.getGroupByClause().replace(PLACEHOLDER_COLUMNS, groupByQueryBuilder.toString());
        innerWhereFilterClause = dbAggregationSelectQueryTemplate.getWhereClause().replace(PLACEHOLDER_CONDITION, filterQueryBuilder.toString());
        innerT2Query.add(innerSelectT2ColumnJoiner.toString()).add(innerFromClause).add(innerWhereFilterClause).add(groupByClause);
        subQueryT1.add(subSelectT1ColumnJoiner.toString()).add(innerFromClause).add(innerWhereFilterClause).add(groupByClause);
        subQueryT2.add(dbAggregationSelectQueryTemplate.getSelectQueryWithInnerSelect().replace(PLACEHOLDER_SELECTORS, subSelectT2ColumnJoiner.toString()).replace(PLACEHOLDER_TABLE_NAME, parentAggregationTable.getTableDefinition().getId()).replace(PLACEHOLDER_INNER_QUERY_2, innerT2Query.toString()).replace(PLACEHOLDER_ON_CONDITION, subSelectT2OnConditionBuilder.toString()).replace(PLACEHOLDER_CONDITION, innerT2WhereCondition).replace(PLACEHOLDER_COLUMNS, groupByT3QueryBuilder.toString()));
        finalSelectQuery.add(dbAggregationSelectQueryTemplate.getJoinClause().replace(PLACEHOLDER_SELECTORS, outerSelectColumnJoiner.toString()).replace(PLACEHOLDER_FROM_CONDITION, subQueryT1.toString()).replace(PLACEHOLDER_INNER_QUERY_1, subQueryT2.toString()).replace(PLACEHOLDER_CONDITION, onConditionBuilder.toString()));
        completeQuery.add(insertIntoQueryBuilder.toString()).add(finalSelectQuery.toString());
    } else {
        for (ExpressionExecutor executor : expressionExecutors) {
            if (executor instanceof VariableExpressionExecutor) {
                VariableExpressionExecutor variableExpressionExecutor = (VariableExpressionExecutor) executor;
                if (variableExpressionExecutor.getAttribute().getName().equals(AGG_START_TIMESTAMP_COL)) {
                    subSelectT1ColumnJoiner.add("? " + SQL_AS + variableExpressionExecutor.getAttribute().getName());
                } else {
                    subSelectT1ColumnJoiner.add(variableExpressionExecutor.getAttribute().getName());
                    groupByQueryBuilder.add(variableExpressionExecutor.getAttribute().getName());
                }
            } else if (executor instanceof ConstantExpressionExecutor) {
                if (((ConstantExpressionExecutor) executor).getValue() != null) {
                    subSelectT1ColumnJoiner.add("'" + ((ConstantExpressionExecutor) executor).getValue() + "' " + SQL_AS + attributeList.get(i).getName());
                } else {
                    subSelectT1ColumnJoiner.add(attributeList.get(i).getName());
                }
            } else if (executor instanceof SumAttributeAggregatorExecutor) {
                subSelectT1ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getSumFunction().replace(PLACEHOLDER_COLUMN, attributeList.get(i).getName()).concat(SQL_AS).concat(attributeList.get(i).getName()));
            } else if (executor instanceof MinAttributeAggregatorExecutor) {
                subSelectT1ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getMinFunction().replace(PLACEHOLDER_COLUMN, attributeList.get(i).getName()).concat(SQL_AS).concat(attributeList.get(i).getName()));
            } else if (executor instanceof MaxAttributeAggregatorExecutor) {
                subSelectT1ColumnJoiner.add(dbAggregationSelectFunctionTemplates.getMaxFunction().replace(PLACEHOLDER_COLUMN, attributeList.get(i).getName()).concat(SQL_AS).concat(attributeList.get(i).getName()));
            }
            i++;
        }
        completeQuery.add(insertIntoQueryBuilder.toString()).add(subSelectT1ColumnJoiner.toString()).add(innerFromClause).add(SQL_WHERE + filterQueryBuilder).add(dbAggregationSelectQueryTemplate.getGroupByClause().replace(PLACEHOLDER_COLUMNS, groupByQueryBuilder.toString()));
    }
    return completeQuery.toString();
}