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

Example 1 with DatabaseQueryConnectionSettings

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

the class DBGroupByNodeModel method createDbOutSpec.

/**
 * @param inSpec Spec of the input database object
 * @param checkRetrieveMetadata true if the retrieveMetadataInConfigure settings should be respected,
 *            <code>false</code> if the metadata should be retrieved in any case (for execute)
 * @return Spec of the output database object
 * @throws InvalidSettingsException If the current settings are invalid
 */
private DatabasePortObjectSpec createDbOutSpec(final DatabasePortObjectSpec inSpec, final boolean checkRetrieveMetadata) throws InvalidSettingsException {
    if (m_groupByCols.getIncludeList().isEmpty() && m_aggregatedColumns.length == 0) {
        throw new InvalidSettingsException("Please select at least one group or aggregation column");
    }
    DatabaseQueryConnectionSettings connection = inSpec.getConnectionSettings(getCredentialsProvider());
    String newQuery = createQuery(connection.getQuery(), connection.getUtility().getStatementManipulator());
    connection = createDBQueryConnection(inSpec, newQuery);
    if (checkRetrieveMetadata && !connection.getRetrieveMetadataInConfigure()) {
        return null;
    }
    DataTableSpec tableSpec = createOutSpec(inSpec.getDataTableSpec(), connection, newQuery, checkRetrieveMetadata);
    return new DatabasePortObjectSpec(tableSpec, connection.createConnectionModel());
}

Example 2 with DatabaseQueryConnectionSettings

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

the class DBGroupByNodeModel method createOutSpec.

/**
 * @param inSpec Spec of the input table
 * @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 boolean ignoreExceptions) throws InvalidSettingsException {
    // Try get spec from database
    try {
        DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(settings, query);
        DatabaseReaderConnection conn = new DatabaseReaderConnection(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
    }
    List<DataColumnSpec> colSpecs = new ArrayList<>();
    // Add all group by columns
    for (String col : m_groupByCols.getIncludeList()) {
        colSpecs.add(inSpec.getColumnSpec(col));
    }
    // Add aggregated columns
    for (int i = 0; i < m_aggregatedColumns.length; i++) {
        String col = m_aggregatedColumns[i];
        String method = m_aggregationMethods[i];
        if (inSpec.getColumnSpec(col) == null) {
            throw new InvalidSettingsException("Column '" + col + "' in aggregation " + method + " does not exist");
        }
        final DatabaseUtility databaseUtility = settings.getUtility();
        final DBAggregationFunction function = databaseUtility.getAggregationFunction(method);
        // Get type of column after aggregation
        DataType type = function.getType(inSpec.getColumnSpec(col).getType());
        colSpecs.add(new DataColumnSpecCreator(generateColumnName(col, method), type).createSpec());
    }
    return new DataTableSpec(colSpecs.toArray(new DataColumnSpec[colSpecs.size()]));
}

Example 3 with DatabaseQueryConnectionSettings

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

the class DBPivotNodeDialog method loadSettingsFrom.

/**
 * {@inheritDoc}
 */
@Override
protected void loadSettingsFrom(final NodeSettingsRO settings, final PortObjectSpec[] specs) throws NotConfigurableException {
    if (specs == null || specs.length < 1 || specs[0] == null) {
        throw new NotConfigurableException("No input connection found.");
    }
    final DatabasePortObjectSpec dbspec = (DatabasePortObjectSpec) specs[0];
    final DataTableSpec spec = dbspec.getDataTableSpec();
    try {
        final DatabaseQueryConnectionSettings connectionSettings = dbspec.getConnectionSettings(null);
        m_columnNamePolicy.loadSettingsFrom(settings);
        final String dbIdentifier = connectionSettings.getDatabaseIdentifier();
        m_aggregationPanel.loadSettingsFrom(settings, dbIdentifier, spec);
        final DBAggregationFunctionProvider functionProvider = new DBAggregationFunctionProvider(connectionSettings.getUtility());
        m_descriptionTab.removeAll();
        final GridBagConstraints c = new GridBagConstraints();
        c.anchor = GridBagConstraints.CENTER;
        c.fill = GridBagConstraints.BOTH;
        c.weightx = 1;
        c.weighty = 1;
        m_descriptionTab.add(functionProvider.getDescriptionPane(), c);
    } catch (final InvalidSettingsException e) {
        throw new NotConfigurableException(e.getMessage());
    }
    m_groupCol.loadSettingsFrom(settings, new DataTableSpec[] { spec });
    m_pivotCol.loadSettingsFrom(settings, new DataTableSpec[] { spec });
    columnsChanged();
}

Example 4 with DatabaseQueryConnectionSettings

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

the class DBSamplingNodeModel method createDbOutSpec.

/**
 * @param inSpec Spec of the input database object
 * @param exec The {@link ExecutionMonitor}
 * @param checkRetrieveMetadata true if the retrieveMetadataInConfigure settings should be respected,
 *            <code>false</code> if the metadata should be retrieved in any case (for execute)
 * @return Spec of the output database object
 * @throws InvalidSettingsException if the current settings are invalid
 * @throws CanceledExecutionException if execution is canceled
 */
private DatabasePortObjectSpec createDbOutSpec(final DatabasePortObjectSpec inSpec, final ExecutionMonitor exec, final boolean checkRetrieveMetadata) throws InvalidSettingsException, CanceledExecutionException {
    final DatabaseQueryConnectionSettings connectionSettings = inSpec.getConnectionSettings(getCredentialsProvider());
    final StatementManipulator statementManipulator = connectionSettings.getUtility().getStatementManipulator();
    try {
        // Connection connection = connectionSettings.createConnection(getCredentialsProvider());
        final String newQuery = connectionSettings.execute(getCredentialsProvider(), conn -> {
            return createQuery(conn, connectionSettings.getQuery(), statementManipulator, inSpec.getDataTableSpec(), exec);
        });
        DatabaseQueryConnectionSettings resultSettings = createDBQueryConnection(inSpec, newQuery);
        DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(resultSettings, newQuery);
        DatabaseReaderConnection conn = new DatabaseReaderConnection(querySettings);
        DataTableSpec tableSpec;
        exec.setMessage("Retrieving result specification.");
        tableSpec = conn.getDataTableSpec(getCredentialsProvider());
        return new DatabasePortObjectSpec(tableSpec, resultSettings.createConnectionModel());
    } catch (SQLException e1) {
        throw new InvalidSettingsException("Failure during query generation. Error: " + e1.getMessage(), e1);
    }
}

Example 5 with DatabaseQueryConnectionSettings

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

the class DBPivotNodeModel method createDbOutSpec.

/**
 * @param inSpec Spec of the input database object
 * @param exec The {@link ExecutionMonitor}
 * @return Spec of the output database object
 * @throws InvalidSettingsException if the current settings are invalid
 * @throws CanceledExecutionException if execution is canceled
 */
private DatabasePortObjectSpec createDbOutSpec(final DatabasePortObjectSpec inSpec, final ExecutionMonitor exec) throws InvalidSettingsException, CanceledExecutionException {
    DatabaseQueryConnectionSettings connectionSettings = inSpec.getConnectionSettings(getCredentialsProvider());
    final StatementManipulator statementManipulator = connectionSettings.getUtility().getStatementManipulator();
    try {
        // Connection connection = connectionSettings.createConnection(getCredentialsProvider());
        String newQuery = createQuery(connectionSettings, inSpec.getDataTableSpec(), exec);
        connectionSettings = createDBQueryConnection(inSpec, newQuery);
        DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(connectionSettings, newQuery);
        DBReader reader = querySettings.getUtility().getReader(querySettings);
        DataTableSpec tableSpec;
        exec.setMessage("Retrieving result specification.");
        tableSpec = reader.getDataTableSpec(getCredentialsProvider());
        return new DatabasePortObjectSpec(tableSpec, connectionSettings.createConnectionModel());
    } catch (SQLException e1) {
        throw new InvalidSettingsException("Failure during query generation. Error: " + e1.getMessage(), e1);
    }
}