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

Example 6 with StatementManipulator

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

the class DBColumnRenameNodeModel method createDBOutSpec.

private DatabasePortObjectSpec createDBOutSpec(final DatabasePortObjectSpec inSpec) throws InvalidSettingsException {
    if (m_config == null) {
        throw new InvalidSettingsException("No configuration available");
    }
    final DataTableSpec tableSpec = inSpec.getDataTableSpec();
    final DataTableSpec outSpec = m_config.getNewSpec(tableSpec);
    final List<String> missingColumnNames = m_config.getMissingColumnNames();
    if (missingColumnNames != null && !missingColumnNames.isEmpty()) {
        setWarningMessage("The following columns are configured but no longer exist: " + ConvenienceMethods.getShortStringFrom(missingColumnNames, 5));
    }
    DatabaseQueryConnectionSettings conn = inSpec.getConnectionSettings(getCredentialsProvider());
    final StatementManipulator statementManipulator = conn.getUtility().getStatementManipulator();
    String newQuery = createQuery(conn.getQuery(), tableSpec, statementManipulator);
    conn = createDBQueryConnection(inSpec, newQuery);
    return new DatabasePortObjectSpec(outSpec, conn.createConnectionModel());
}

Example 7 with StatementManipulator

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

the class DBColumnFilterNodeModel method createQuery.

private String createQuery(final DatabaseQueryConnectionSettings conn, final DataTableSpec resultSpec) {
    final String query = conn.getQuery();
    final StatementManipulator manipulator = conn.getUtility().getStatementManipulator();
    final StringBuilder buf = new StringBuilder();
    final String[] queries = query.split(DBReader.SQL_QUERY_SEPARATOR);
    for (int i = 0; i < queries.length - 1; i++) {
        buf.append(queries[i]);
        buf.append(DBReader.SQL_QUERY_SEPARATOR);
    }
    // build SELECT statement
    buf.append("SELECT ");
    if (m_filter.getExcludeList().isEmpty()) {
        super.setWarningMessage("All columns retained.");
        // selects all columns
        buf.append("*");
    } else {
        int i = 0;
        for (final DataColumnSpec colSpec : resultSpec) {
            final String colName = colSpec.getName();
            if (i++ > 0) {
                buf.append(",");
            }
            buf.append(manipulator.quoteColumn(colName));
        }
    }
    final String selectQuery = queries[queries.length - 1];
    buf.append(" FROM (" + selectQuery + ") table_" + System.identityHashCode(this));
    return buf.toString();
}

Example 8 with StatementManipulator

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

the class DBConnectionWriterNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws CanceledExecutionException, Exception {
    DatabasePortObject dbObj = (DatabasePortObject) inData[0];
    exec.setProgress("Opening database connection...");
    String tableName = m_tableName.getStringValue();
    DatabaseQueryConnectionSettings conn = dbObj.getConnectionSettings(getCredentialsProvider());
    CredentialsProvider cp = getCredentialsProvider();
    final StatementManipulator statementManipulator = conn.getUtility().getStatementManipulator();
    try {
        // use the statement manipulator to create the drop table statement
        conn.execute(statementManipulator.dropTable(tableName, false), cp);
    } catch (Exception e) {
    // suppress exception thrown when table does not exist in database
    }
    String[] stmts = statementManipulator.createTableAsSelect(tableName, conn.getQuery());
    for (final String stmt : stmts) {
        conn.execute(stmt, cp);
    }
    return new BufferedDataTable[0];
}

Example 9 with StatementManipulator

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

the class DBJoinerNodeModel method createDbOutSpec.

/**
 * @param inSpecLeft Spec of the left database object
 * @param inSpecRight Spec of the right database object
 * @return Spec of the output database object
 * @throws InvalidSettingsException If the current settings are invalid
 */
private DatabasePortObjectSpec createDbOutSpec(final DatabasePortObjectSpec inSpecLeft, final DatabasePortObjectSpec inSpecRight) throws InvalidSettingsException {
    DatabaseQueryConnectionSettings connectionLeft = inSpecLeft.getConnectionSettings(getCredentialsProvider());
    DatabaseQueryConnectionSettings connectionRight = inSpecRight.getConnectionSettings(getCredentialsProvider());
    StatementManipulator statementManipulator = connectionLeft.getUtility().getStatementManipulator();
    // Check first if connections are equal, otherwise abort
    checkIfDBConnectionsAreEqual(connectionLeft, connectionRight);
    // Calculate output columns
    Pair<Map<String, String>, Map<String, String>> columnMaps = createOutputColumnMaps(inSpecLeft.getDataTableSpec(), inSpecRight.getDataTableSpec(), statementManipulator);
    if (columnMaps.getFirst().size() + columnMaps.getSecond().size() < 1) {
        throw new InvalidSettingsException("No column selected");
    }
    // Create output query
    String newQuery = createQuery(connectionLeft.getQuery(), connectionRight.getQuery(), statementManipulator, columnMaps);
    // Create output spec
    DataTableSpec tableSpec = createOutSpec(inSpecLeft.getDataTableSpec(), inSpecRight.getDataTableSpec(), columnMaps, statementManipulator);
    DatabaseQueryConnectionSettings connection = createDBQueryConnection(inSpecLeft, newQuery);
    return new DatabasePortObjectSpec(tableSpec, connection.createConnectionModel());
}

Example 10 with StatementManipulator

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

the class DBAutoBinner method createPMMLPrepocDiscretize.

/**
 * This method creates a {@link PMMLPreprocDiscretize} object and is used in {@link DBAutoBinnerNodeModel}
 *
 * @param cp {@link CredentialsProvider}
 * @param connectionSettings {@link DatabaseQueryConnectionSettings}
 * @param dataTableSpec DataTableSpec of incoming {@link BufferedDataTable}
 * @return a {@link PMMLPreprocDiscretize} object containing required parameters for binning operation
 * @throws SQLException
 */
public PMMLPreprocDiscretize createPMMLPrepocDiscretize(final CredentialsProvider cp, final DatabaseQueryConnectionSettings connectionSettings, final DataTableSpec dataTableSpec) throws SQLException {
    final String query = connectionSettings.getQuery();
    final StatementManipulator statementManipulator = connectionSettings.getUtility().getStatementManipulator();
    AutoBinnerLearnSettings settings = getSettings();
    String[] includeCols = settings.getFilterConfiguration().applyTo(dataTableSpec).getIncludes();
    if (includeCols.length == 0) {
        return createDisretizeOp(new LinkedHashMap<>());
    }
    StringBuilder minMaxQuery = new StringBuilder();
    minMaxQuery.append("SELECT");
    for (int i = 0; i < includeCols.length; i++) {
        minMaxQuery.append(" MAX(" + statementManipulator.quoteIdentifier(includeCols[i]) + ") " + statementManipulator.quoteIdentifier("max_" + includeCols[i]) + ",");
        minMaxQuery.append(" MIN(" + statementManipulator.quoteIdentifier(includeCols[i]) + ") " + statementManipulator.quoteIdentifier("min_" + includeCols[i]));
        if (i < includeCols.length - 1) {
            minMaxQuery.append(",");
        }
    }
    minMaxQuery.append(" FROM (" + query + ") T");
    HashMap<String, Pair<Double, Double>> maxAndMin = connectionSettings.execute(cp, conn -> {
        double max = 0;
        double min = 0;
        HashMap<String, Pair<Double, Double>> maxMinMap = new LinkedHashMap<>();
        try (ResultSet valueSet = conn.createStatement().executeQuery(minMaxQuery.toString())) {
            while (valueSet.next()) {
                for (int i = 0; i < includeCols.length; i++) {
                    max = valueSet.getDouble("max_" + includeCols[i]);
                    min = valueSet.getDouble("min_" + includeCols[i]);
                    maxMinMap.put(includeCols[i], new Pair<Double, Double>(min, max));
                }
            }
        }
        return maxMinMap;
    });
    int number = settings.getBinCount();
    Map<String, double[]> edgesMap = new LinkedHashMap<>();
    for (Entry<String, Pair<Double, Double>> entry : maxAndMin.entrySet()) {
        double[] edges = AutoBinner.calculateBounds(number, entry.getValue().getFirst(), entry.getValue().getSecond());
        if (settings.getIntegerBounds()) {
            edges = AutoBinner.toIntegerBoundaries(edges);
        }
        edgesMap.put(entry.getKey(), edges);
    }
    return createDisretizeOp(edgesMap);
}