Examples with DBAggregationFunction org.knime.core.node.port.database.aggregation.DBAggregationFunction used on opensource projects

Example 21 with DBAggregationFunction

use of org.knime.core.node.port.database.aggregation.DBAggregationFunction in project knime-core by knime.

the class DBDataTypeAggregationFunctionPanel method getMethods4SelectedItems.

/**
 * @return a label list of all supported methods for the currently
 * selected rows
 */
protected List<DBAggregationFunction> getMethods4SelectedItems() {
    final int[] selectedRowIdxs = getSelectedRows();
    final Set<DataType> types = new HashSet<>(selectedRowIdxs.length);
    for (final int rowIdx : selectedRowIdxs) {
        final DBDataTypeAggregationFunctionRow row = getTableModel().getRow(rowIdx);
        types.add(row.getDataType());
    }
    final DataType superType = CollectionCellFactory.getElementType(types.toArray(new DataType[0]));
    final List<DBAggregationFunction> list = getTableModel().getAggregationFunctionProvider().getCompatibleFunctions(superType, true);
    return list;
}

Example 22 with DBAggregationFunction

use of org.knime.core.node.port.database.aggregation.DBAggregationFunction in project knime-core by knime.

the class DBDataTypeAggregationFunctionRow method loadFunctions.

/**
 * Loads the functions and handles invalid aggregation functions graceful.
 * @param settings {@link NodeSettingsRO}
 * @param key the config key
 * @param dbIdentifier the {@link AggregationFunctionProvider}
 * @param tableSpec the input {@link DataTableSpec}
 * @return {@link List} of {@link DBDataTypeAggregationFunctionRow}s
 * @throws InvalidSettingsException if the settings are invalid
 */
public static List<DBDataTypeAggregationFunctionRow> loadFunctions(final NodeSettingsRO settings, final String key, final String dbIdentifier, final DataTableSpec tableSpec) throws InvalidSettingsException {
    if (key == null || key.isEmpty()) {
        throw new IllegalArgumentException("key must not be null");
    }
    if (dbIdentifier == null || dbIdentifier.isEmpty()) {
        throw new IllegalArgumentException("dbIdentifier must not be empty");
    }
    if (settings == null || !settings.containsKey(key)) {
        return Collections.EMPTY_LIST;
    }
    final DatabaseUtility utility = DatabaseUtility.getUtility(dbIdentifier);
    DBAggregationFunctionProvider functionProvider = new DBAggregationFunctionProvider(utility);
    final NodeSettingsRO root = settings.getNodeSettings(key);
    final Set<String> settingsKeys = root.keySet();
    final List<DBDataTypeAggregationFunctionRow> colAggrList = new ArrayList<>(settingsKeys.size());
    for (String settingsKey : settingsKeys) {
        final NodeSettingsRO cfg = root.getNodeSettings(settingsKey);
        final DataType dataType = cfg.getDataType(CNFG_DATA_TYPE);
        DBAggregationFunction function = AbstractDBAggregationFunctionRow.loadFunction(tableSpec, functionProvider, cfg);
        final DBDataTypeAggregationFunctionRow aggrFunctionRow = new DBDataTypeAggregationFunctionRow(dataType, function);
        colAggrList.add(aggrFunctionRow);
    }
    return colAggrList;
}

Example 23 with DBAggregationFunction

use of org.knime.core.node.port.database.aggregation.DBAggregationFunction in project knime-core by knime.

the class DBDataTypeAggregationFunctionTableModel method updateFunction.

/**
 * @param row the row to update
 * @param function the {@link DBDataTypeAggregationFunctionRow} to use
 */
private void updateFunction(final int row, final AggregationFunction function) {
    final DBDataTypeAggregationFunctionRow old = getRow(row);
    if (old.getFunction().getId().equals(function.getId())) {
        // check if the method has changed
        return;
    }
    // create a new operator each time it is updated to guarantee that
    // each column has its own operator instance
    final DBAggregationFunction cloneFunction = getAggregationFunctionProvider().getFunction(function.getId());
    final DBDataTypeAggregationFunctionRow newRow = new DBDataTypeAggregationFunctionRow(old.getDataType(), cloneFunction);
    newRow.setValid(old.isValid());
    updateRow(row, newRow);
}

Example 24 with DBAggregationFunction

use of org.knime.core.node.port.database.aggregation.DBAggregationFunction 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);
}

Example 25 with DBAggregationFunction

use of org.knime.core.node.port.database.aggregation.DBAggregationFunction in project knime-core by knime.

the class CasePivotStatementGenerator method createInnerStatement.

/**
 * {@inheritDoc}
 */
@Override
protected StringBuilder createInnerStatement(final StatementManipulator sm, final String tableName, final List<String> groupByColumnsList, final List<PivotData> pivotDataQueries, final List<Pair<String, DBAggregationFunction>> aggValues, final PivotColumnNameGenerator pivotColGenerator) {
    StringBuilder innerStatementBuilder = new StringBuilder();
    for (PivotData pivotData : pivotDataQueries) {
        StringBuilder caseStatement = new StringBuilder();
        for (Pair<String, DBAggregationFunction> entry : aggValues) {
            String columnName = entry.getFirst();
            DBAggregationFunction aggMethod = entry.getSecond();
            caseStatement.append(aggMethod.getSQLFragment4SubQuery(sm, "T", "CASE WHEN " + pivotData.getQuery() + " THEN " + sm.quoteIdentifier(columnName) + " END"));
            caseStatement.append(" ");
            caseStatement.append(sm.quoteIdentifier(pivotColGenerator.createColumnName(columnName, aggMethod, pivotData.getValues())));
            caseStatement.append(", ");
        }
        innerStatementBuilder.append(caseStatement);
    }
    StringBuilder innerStatement = new StringBuilder();
    innerStatement.append(innerStatementBuilder.substring(0, innerStatementBuilder.length() - 2));
    innerStatement.append(" FROM (" + tableName + ") T");
    return innerStatement;
}