Examples with PivotColumnNameGenerator org.knime.core.node.port.database.pivoting.PivotColumnNameGenerator used on opensource projects

Example 1 with PivotColumnNameGenerator

use of org.knime.core.node.port.database.pivoting.PivotColumnNameGenerator in project knime-core by knime.

the class DBPivotNodeModel method createQuery.

private String createQuery(final DatabaseQueryConnectionSettings connectionSettings, final DataTableSpec dataTableSpec, final ExecutionMonitor exec) throws SQLException, CanceledExecutionException {
    final StatementManipulator manipulator = connectionSettings.getUtility().getStatementManipulator();
    final String query = connectionSettings.getQuery();
    exec.setMessage("Getting pivot values.");
    ExecutionMonitor subExec = exec.createSubProgress(0.7);
    final List<String> pivotColumns = m_pivotCols.getIncludeList();
    final Map<DataColumnSpec, Set<Object>> pivotElements = connectionSettings.execute(getCredentialsProvider(), conn -> {
        int counter = 1;
        final Map<DataColumnSpec, Set<Object>> pivotMap = new LinkedHashMap<>();
        for (String pivotColumn : pivotColumns) {
            subExec.setProgress(counter / (double) pivotColumns.size(), "Fetching unique values for column " + pivotColumn + ". There are " + (pivotColumns.size() - counter) + " columns left.");
            DataColumnSpec columnSpec = dataTableSpec.getColumnSpec(pivotColumn);
            final String valueQuery = "SELECT DISTINCT " + manipulator.quoteIdentifier(pivotColumn) + " FROM (" + query + ") T";
            try (ResultSet valueSet = conn.createStatement().executeQuery(valueQuery)) {
                exec.checkCanceled();
                final Set<Object> vals = new HashSet<>();
                while (valueSet.next()) {
                    final Object dbVal = valueSet.getObject(1);
                    if (!valueSet.wasNull()) {
                        vals.add(dbVal);
                    }
                }
                pivotMap.put(columnSpec, vals);
                counter++;
            }
        }
        return pivotMap;
    });
    exec.setProgress(0.8, "Getting aggregation methods and columns.");
    List<String> groupByColumns = m_groupByCols.getIncludeList();
    final List<Pair<String, DBAggregationFunction>> aggValues = new LinkedList<>();
    for (int i = 0; i < m_aggregationFunction2Use.size(); i++) {
        exec.checkCanceled();
        final DBColumnAggregationFunctionRow aggregationFunction = m_aggregationFunction2Use.get(i);
        String colName = aggregationFunction.getColumnSpec().getName();
        DBAggregationFunction function = aggregationFunction.getFunction();
        aggValues.add(new Pair<>(colName, function));
    }
    final ColumnNamePolicy pivotColPoliciy = ColumnNamePolicy.getPolicy4Label(m_columnNamePolicy.getStringValue());
    PivotColumnNameGenerator pivotColName = new PivotColumnNameGenerator() {

        @Override
        public String createColumnName(final String columnName, final DBAggregationFunction function, final List<Object> pivotValues) {
            String vals = "";
            Iterator<Object> iterator = pivotValues.iterator();
            while (iterator.hasNext()) {
                vals = vals + iterator.next() + "_";
            }
            vals = vals.substring(0, vals.length() - 1);
            String method = function.getColumnName();
            switch(pivotColPoliciy) {
                case KEEP_ORIGINAL_NAME:
                    return vals + "+" + columnName;
                case AGGREGATION_METHOD_COLUMN_NAME:
                    return vals + "+" + method + "(" + columnName + ")";
                case COLUMN_NAME_AGGREGATION_METHOD:
                    return vals + "+" + columnName + " (" + method + ")";
                default:
                    throw new IllegalStateException("Unhandled column naming policy: " + pivotColPoliciy);
            }
        }
    };
    exec.setProgress(0.9, "Creating query.");
    exec.checkCanceled();
    return manipulator.getPivotStatement(query, groupByColumns, pivotElements, aggValues, pivotColName);
}