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

Example 11 with DBAggregationFunction

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

the class DBGroupByNodeModel2 method createOutSpec.

/**
 * @param inSpec Spec of the input table
 * @param manipulator
 * @param checkRetrieveMetadata
 * @return Spec of the output table
 * @throws InvalidSettingsException if settings do not match the input specification
 */
private DataTableSpec createOutSpec(final DataTableSpec inSpec, final DatabaseConnectionSettings settings, final String query, final StatementManipulator manipulator, final boolean ignoreExceptions) throws InvalidSettingsException {
    // Try get spec from database
    try {
        DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(settings, query);
        DBReader conn = querySettings.getUtility().getReader(querySettings);
        return conn.getDataTableSpec(getCredentialsProvider());
    } catch (SQLException e) {
        NodeLogger.getLogger(getClass()).info("Could not determine table spec from database, trying to guess now", e);
        if (!ignoreExceptions) {
            throw new InvalidSettingsException("Error in automatically build sql statement: " + e.getMessage());
        }
    // Otherwise guess spec
    }
    final List<DataColumnSpec> colSpecs = new ArrayList<>();
    // Add all group by columns
    for (String col : m_groupByCols.getIncludeList()) {
        final DataColumnSpec columnSpec = inSpec.getColumnSpec(col);
        if (columnSpec == null) {
            throw new InvalidSettingsException("Group column '" + col + "' not found in input table");
        }
        colSpecs.add(columnSpec);
    }
    if (m_addCountStar.getBooleanValue()) {
        colSpecs.add(new DataColumnSpecCreator(manipulator.getValidColumnName(m_countStarColName.getStringValue()), LongCell.TYPE).createSpec());
    }
    final ColumnNamePolicy columnNamePolicy = ColumnNamePolicy.getPolicy4Label(m_columnNamePolicy.getStringValue());
    // Add aggregated columns
    for (int i = 0; i < m_aggregationFunction2Use.size(); i++) {
        final DBColumnAggregationFunctionRow row = m_aggregationFunction2Use.get(i);
        final String col = row.getColumnSpec().getName();
        final String methodId = row.getFunction().getId();
        if (inSpec.getColumnSpec(col) == null) {
            throw new InvalidSettingsException("Column '" + col + "' for aggregation function " + row.getFunction().getLabel() + " does not exist");
        }
        final DatabaseUtility databaseUtility = settings.getUtility();
        final DBAggregationFunction function = databaseUtility.getAggregationFunction(methodId);
        // Get type of column after aggregation
        final DataType type = function.getType(inSpec.getColumnSpec(col).getType());
        colSpecs.add(new DataColumnSpecCreator(manipulator.getValidColumnName(generateColumnName(columnNamePolicy, row)), type).createSpec());
    }
    return new DataTableSpec(colSpecs.toArray(new DataColumnSpec[colSpecs.size()]));
}

Example 12 with DBAggregationFunction

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

the class DBAggregationFunctionProvider method getHTMLDescription.

/**
 * @return the HTML String that lists all available aggregation methods
 * and their description as a definition list.
 */
private StringBuilder getHTMLDescription() {
    final List<DBAggregationFunction> functions = getFunctions(true);
    final StringBuilder buf = new StringBuilder();
    buf.append("<dl>");
    buf.append("\n");
    for (final DBAggregationFunction function : functions) {
        buf.append("<dt><b>");
        buf.append(function.getLabel());
        buf.append("</b></dt>");
        buf.append("\n");
        buf.append("<dd>");
        buf.append(StringEscapeUtils.escapeHtml(function.getDescription()));
        buf.append("</dd>");
        buf.append("\n");
    }
    // close the last definition list
    buf.append("</dl>");
    return buf;
}

Example 13 with DBAggregationFunction

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

the class DBAggregationFunctionProvider method getCompatibleFunctions.

/**
 * {@inheritDoc}
 */
@Override
public List<DBAggregationFunction> getCompatibleFunctions(final DataType type, final boolean sorted) {
    final Collection<DBAggregationFunction> aggregationFunctions = m_utility.getAggregationFunctions();
    final List<DBAggregationFunction> compatible = new ArrayList<>(aggregationFunctions.size());
    for (DBAggregationFunction function : aggregationFunctions) {
        if (type == null || function.isCompatible(type)) {
            compatible.add(function);
        }
    }
    if (sorted) {
        Collections.sort(compatible, DBAggregationFunctionLabelComparator.ASC);
    }
    return compatible;
}

Example 14 with DBAggregationFunction

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

the class DBGroupByNodeModel2 method configure.

/**
 * {@inheritDoc}
 */
@Override
protected PortObjectSpec[] configure(final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    final DatabasePortObjectSpec dbSpec = (DatabasePortObjectSpec) inSpecs[0];
    final DataTableSpec tableSpec = dbSpec.getDataTableSpec();
    final DatabaseQueryConnectionSettings connection = dbSpec.getConnectionSettings(null);
    final String dbIdentifier = connection.getDatabaseIdentifier();
    final List<DBColumnAggregationFunctionRow> columnFunctions = DBColumnAggregationFunctionRow.loadFunctions(m_settings, DBGroupByNodeModel2.CFG_AGGREGATION_FUNCTIONS, dbIdentifier, tableSpec);
    final ArrayList<DBColumnAggregationFunctionRow> invalidColAggrs = new ArrayList<>(1);
    final Set<String> usedColNames = new HashSet<>(tableSpec.getNumColumns());
    usedColNames.addAll(m_groupByCols.getIncludeList());
    m_aggregationFunction2Use.clear();
    for (DBColumnAggregationFunctionRow row : columnFunctions) {
        final DataColumnSpec columnSpec = row.getColumnSpec();
        final DataColumnSpec inputSpec = tableSpec.getColumnSpec(columnSpec.getName());
        final AggregationFunction function = row.getFunction();
        if (inputSpec == null || !inputSpec.getType().equals(columnSpec.getType())) {
            invalidColAggrs.add(row);
            continue;
        }
        if (function instanceof InvalidAggregationFunction) {
            throw new InvalidSettingsException(((InvalidAggregationFunction) function).getErrorMessage());
        }
        if (function.hasOptionalSettings()) {
            try {
                function.configure(tableSpec);
            } catch (InvalidSettingsException e) {
                throw new InvalidSettingsException("Exception in aggregation function " + function.getLabel() + " of column " + row.getColumnSpec().getName() + ": " + e.getMessage());
            }
        }
        usedColNames.add(row.getColumnSpec().getName());
        m_aggregationFunction2Use.add(row);
    }
    final List<DBPatternAggregationFunctionRow> patternFunctions = DBPatternAggregationFunctionRow.loadFunctions(m_settings, CFG_PATTERN_AGGREGATION_FUNCTIONS, dbIdentifier, tableSpec);
    if (tableSpec.getNumColumns() > usedColNames.size() && !patternFunctions.isEmpty()) {
        for (final DataColumnSpec spec : tableSpec) {
            if (!usedColNames.contains(spec.getName())) {
                for (final DBPatternAggregationFunctionRow patternFunction : patternFunctions) {
                    final Pattern pattern = patternFunction.getRegexPattern();
                    final DBAggregationFunction function = patternFunction.getFunction();
                    if (pattern != null && pattern.matcher(spec.getName()).matches() && function.isCompatible(spec.getType())) {
                        final DBColumnAggregationFunctionRow row = new DBColumnAggregationFunctionRow(spec, patternFunction.getFunction());
                        m_aggregationFunction2Use.add(row);
                        usedColNames.add(spec.getName());
                    }
                }
            }
        }
    }
    final List<DBDataTypeAggregationFunctionRow> typeFunctions = DBDataTypeAggregationFunctionRow.loadFunctions(m_settings, CFG_TYPE_AGGREGATION_FUNCTIONS, dbIdentifier, tableSpec);
    // check if some columns are left
    if (tableSpec.getNumColumns() > usedColNames.size() && !typeFunctions.isEmpty()) {
        for (final DataColumnSpec spec : tableSpec) {
            if (!usedColNames.contains(spec.getName())) {
                final DataType dataType = spec.getType();
                for (final DBDataTypeAggregationFunctionRow typeAggregator : typeFunctions) {
                    if (typeAggregator.isCompatibleType(dataType)) {
                        final DBColumnAggregationFunctionRow row = new DBColumnAggregationFunctionRow(spec, typeAggregator.getFunction());
                        m_aggregationFunction2Use.add(row);
                        usedColNames.add(spec.getName());
                    }
                }
            }
        }
    }
    if (m_groupByCols.getIncludeList().isEmpty() && m_aggregationFunction2Use.isEmpty() && !m_addCountStar.getBooleanValue()) {
        throw new InvalidSettingsException("Please select at least one group or aggregation function or the " + "COUNT(*) option.");
    }
    if (!invalidColAggrs.isEmpty()) {
        setWarningMessage(invalidColAggrs.size() + " aggregation functions ignored due to incompatible columns.");
    }
    return new PortObjectSpec[] { createDbOutSpec(dbSpec, true) };
}

Example 15 with DBAggregationFunction

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

the class DBColumnAggregationFunctionRow 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 DBColumnAggregationFunctionRow}s
 * @throws InvalidSettingsException if the settings are invalid
 */
public static List<DBColumnAggregationFunctionRow> 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<DBColumnAggregationFunctionRow> colAggrList = new ArrayList<>(settingsKeys.size());
    for (String settingsKey : settingsKeys) {
        final NodeSettingsRO cfg = root.getNodeSettings(settingsKey);
        final String colName = cfg.getString(CNFG_COL_NAMES);
        final DataType colType = cfg.getDataType(CNFG_COL_TYPES);
        final DataColumnSpec colSpec = new DataColumnSpecCreator(colName, colType).createSpec();
        DBAggregationFunction function = AbstractDBAggregationFunctionRow.loadFunction(tableSpec, functionProvider, cfg);
        final DBColumnAggregationFunctionRow aggrFunctionRow = new DBColumnAggregationFunctionRow(colSpec, function);
        colAggrList.add(aggrFunctionRow);
    }
    return colAggrList;
}