Examples with RowIterator org.knime.core.data.RowIterator used on opensource projects

Example 56 with RowIterator

use of org.knime.core.data.RowIterator in project knime-core by knime.

the class DBReaderImpl method createTable.

/**
 * Called from the database port to read the first n-number of rows.
 *
 * @param useDbRowId <code>true</code> if the KNIME row id should based on the db row id
 * @param cachedNoRows number of rows cached for data preview
 * @param cp {@link CredentialsProvider} providing user/password
 * @return buffered data table read from database
 * @throws SQLException if the connection could not be opened
 */
DataTable createTable(final boolean useDbRowId, final int cachedNoRows, final CredentialsProvider cp) throws SQLException {
    if (m_blobFactory == null) {
        m_blobFactory = new BinaryObjectCellFactory();
    }
    // retrieve connection
    // final Connection conn = initConnection(cp);
    final DatabaseQueryConnectionSettings dbConn = getQueryConnection();
    // synchronized (dbConn.syncConnection(conn)) {
    return dbConn.execute(cp, conn -> {
        // remember auto-commit flag
        final boolean autoCommit = conn.getAutoCommit();
        final Statement stmt = initStatement(cp, conn);
        try {
            final String[] oQueries = dbConn.getQuery().split(SQL_QUERY_SEPARATOR);
            if (cachedNoRows < 0) {
                int fetchsize = (DatabaseConnectionSettings.FETCH_SIZE != null) ? DatabaseConnectionSettings.FETCH_SIZE : -1;
                dbConn.getUtility().getStatementManipulator().setFetchSize(stmt, fetchsize);
            } else {
                final int hashAlias = System.identityHashCode(this);
                final int selectIdx = oQueries.length - 1;
                // replace last element in statement(s) with wrapped SQL
                oQueries[selectIdx] = "SELECT * FROM (" + oQueries[selectIdx] + ") table_" + hashAlias;
                try {
                    // bugfix 2925: may fail, e.g. on sqlite
                    stmt.setMaxRows(cachedNoRows);
                } catch (SQLException ex) {
                    Throwable cause = ExceptionUtils.getRootCause(ex);
                    if (cause == null) {
                        cause = ex;
                    }
                    LOGGER.warn("Can't set max rows on statement, reason: " + cause.getMessage(), ex);
                }
            }
            // execute all except the last query
            for (int i = 0; i < oQueries.length - 1; i++) {
                LOGGER.debug("Executing SQL statement as execute: " + oQueries[i]);
                stmt.execute(oQueries[i]);
            }
            final String lastQuery = oQueries[oQueries.length - 1];
            LOGGER.debug("Executing SQL statement as executeQuery: " + lastQuery);
            final ResultSet result = stmt.executeQuery(lastQuery);
            LOGGER.debug("Reading meta data from database ResultSet...");
            m_spec = createTableSpec(result.getMetaData());
            LOGGER.debug("Parsing database ResultSet...");
            // final DBRowIterator dbIt = createRowIterator(useDbRowId, result);
            final RowIterator it = createDBRowIterator(m_spec, dbConn, m_blobFactory, useDbRowId, result);
            DataContainer buf = new DataContainer(m_spec);
            while (it.hasNext()) {
                buf.addRowToTable(it.next());
            }
            buf.close();
            return buf.getTable();
        } finally {
            if (stmt != null) {
                if (!conn.getAutoCommit()) {
                    conn.commit();
                }
                DatabaseConnectionSettings.setAutoCommit(conn, autoCommit);
                stmt.close();
            }
        }
    });
}

Example 57 with RowIterator

use of org.knime.core.data.RowIterator in project knime-core by knime.

the class DBReaderImpl method loopTable.

/**
 * @since 3.2
 */
@SuppressWarnings("resource")
@Override
public BufferedDataTableRowOutput loopTable(final ExecutionContext exec, final CredentialsProvider cp, final RowInput data, final long rowCount, final boolean failIfException, final boolean appendInputColumns, final boolean includeEmptyResults, final boolean retainAllColumns, final String... columns) throws Exception {
    if (m_blobFactory == null) {
        m_blobFactory = new BinaryObjectCellFactory();
    }
    final DatabaseQueryConnectionSettings dbConn = getQueryConnection();
    return getQueryConnection().execute(cp, conn -> {
        /* Get the selected timezone */
        final TimeZone timezone = dbConn.getTimeZone();
        /* Get the input table spec */
        final DataTableSpec inSpec = data.getDataTableSpec();
        /* Create PreparedStatement */
        final String query = dbConn.getQuery();
        LOGGER.debug("Executing SQL preparedStatement as execute: " + query);
        /* Initialize the error table */
        final UniqueNameGenerator errorGenerator = new UniqueNameGenerator(inSpec);
        final DataColumnSpec errorColSpec = errorGenerator.newColumn(DEF_ERROR_COL_NAME, StringCell.TYPE);
        final DataTableSpec errorSpec = new DataTableSpec(inSpec, new DataTableSpec(errorColSpec));
        m_errorContainer = exec.createDataContainer(errorSpec);
        DataTableSpec dbSpec = new DataTableSpec();
        BufferedDataTableRowOutput output = null;
        exec.setMessage("Start reading rows from database...");
        try (final PreparedStatement stmt = conn.prepareStatement(query)) {
            long inDataCounter = 1;
            long rowIdCounter = 0;
            DataRow row;
            while ((row = data.poll()) != null) {
                exec.checkCanceled();
                if (rowCount > 0) {
                    exec.setProgress(1.0 * inDataCounter / rowCount, "Row " + "#" + inDataCounter + " of " + rowCount);
                } else {
                    exec.setProgress("Writing Row " + "#" + inDataCounter);
                }
                final DataCell[] inCells = new DataCell[columns.length];
                for (int i = 0; i < columns.length; i++) {
                    final int dbIdx = i + 1;
                    final int colIdx = inSpec.findColumnIndex(columns[i]);
                    final DataColumnSpec colSpec = inSpec.getColumnSpec(colIdx);
                    inCells[i] = row.getCell(colIdx);
                    fillStatement(stmt, dbIdx, colSpec, inCells[i], timezone, null);
                }
                try (final ResultSet result = stmt.executeQuery()) {
                    /* In the first iteration, create the out DataTableSpec and BufferedDataTableRowOutput */
                    if (output == null) {
                        dbSpec = createTableSpec(result.getMetaData());
                        if (appendInputColumns) {
                            // Create out DataTableSpec for input table
                            final DataTableSpec newInSpec;
                            if (retainAllColumns) {
                                newInSpec = inSpec;
                            } else {
                                final DataColumnSpec[] inColSpecs = new DataColumnSpec[columns.length];
                                for (int i = 0; i < inColSpecs.length; i++) {
                                    inColSpecs[i] = inSpec.getColumnSpec(columns[i]);
                                }
                                newInSpec = new DataTableSpec(inColSpecs);
                            }
                            // Create DataTableSpec for database columns, rename if necessary
                            final UniqueNameGenerator generator = new UniqueNameGenerator(newInSpec);
                            final DataColumnSpec[] dbColSpecs = new DataColumnSpec[dbSpec.getNumColumns()];
                            for (int i = 0; i < dbColSpecs.length; i++) {
                                final DataColumnSpec colSpec = dbSpec.getColumnSpec(i);
                                dbColSpecs[i] = generator.newColumn(colSpec.getName(), colSpec.getType());
                            }
                            dbSpec = new DataTableSpec(dbColSpecs);
                            m_spec = new DataTableSpec(newInSpec, dbSpec);
                        } else {
                            m_spec = dbSpec;
                        }
                        output = new BufferedDataTableRowOutput(exec.createDataContainer(m_spec));
                    }
                    /* Iterate over the result of the database query and put it into the output table*/
                    final RowIterator dbRowIterator = createDBRowIterator(dbSpec, dbConn, m_blobFactory, false, result, rowIdCounter);
                    boolean hasDbRow = false;
                    while (dbRowIterator.hasNext()) {
                        hasDbRow = true;
                        final DataRow dbRow = dbRowIterator.next();
                        if (appendInputColumns) {
                            final DataRow inRow;
                            if (retainAllColumns) {
                                inRow = new DefaultRow(dbRow.getKey(), row);
                            } else {
                                inRow = new DefaultRow(dbRow.getKey(), inCells);
                            }
                            final JoinedRow joinedRow = new JoinedRow(inRow, dbRow);
                            output.push(joinedRow);
                        } else {
                            output.push(dbRow);
                        }
                        rowIdCounter++;
                    }
                    /* Append columns using MissingCell if no result is returned */
                    if (!hasDbRow && appendInputColumns && includeEmptyResults) {
                        final DataCell[] cells = new DataCell[dbSpec.getNumColumns()];
                        Arrays.fill(cells, DataType.getMissingCell());
                        final RowKey rowKey = RowKey.createRowKey(rowIdCounter);
                        final DataRow emptyDbRows = new DefaultRow(rowKey, cells);
                        final DataRow inRow;
                        if (retainAllColumns) {
                            inRow = new DefaultRow(rowKey, row);
                        } else {
                            inRow = new DefaultRow(rowKey, inCells);
                        }
                        final JoinedRow joinedRow = new JoinedRow(inRow, emptyDbRows);
                        output.push(joinedRow);
                        rowIdCounter++;
                    }
                    inDataCounter++;
                } catch (SQLException ex) {
                    LOGGER.debug("SQLException: " + ex.getMessage());
                    if (!failIfException) {
                        if (output == null) {
                            throw new SQLException(ex);
                        }
                        final AppendedColumnRow appendedRow = new AppendedColumnRow(row, new StringCell(ex.getMessage()));
                        m_errorContainer.addRowToTable(appendedRow);
                    } else {
                        throw new SQLException(ex);
                    }
                }
            }
        } finally {
            data.close();
            if (output == null) {
                output = new BufferedDataTableRowOutput(exec.createDataContainer(inSpec));
            }
            output.close();
            if (m_errorContainer != null) {
                m_errorContainer.close();
            }
        }
        return output;
    });
}

Example 58 with RowIterator

use of org.knime.core.data.RowIterator in project knime-core by knime.

the class DBWriterImpl method updateTable.

/**
 * Create connection to update table in database.
 * @param data The data to write.
 * @param setColumns columns part of the SET clause
 * @param whereColumns columns part of the WHERE clause
 * @param updateStatus int array of length data#getRowCount; will be filled with
 *             update info from the database
 * @param table name of table to write
 * @param exec Used the cancel writing.
 * @param cp {@link CredentialsProvider} providing user/password
 * @param batchSize number of rows updated in one batch
 * @return error string or null, if non
 * @throws Exception if connection could not be established
 */
@Override
public final String updateTable(final String schema, final String table, final BufferedDataTable data, final String[] setColumns, final String[] whereColumns, final int[] updateStatus, final ExecutionMonitor exec, final CredentialsProvider cp, final int batchSize) throws Exception {
    final DatabaseConnectionSettings conSettings = getDatabaseConnectionSettings();
    // synchronized (conSettings.syncConnection(conn)) {
    return conSettings.execute(cp, conn -> {
        exec.setMessage("Start updating rows in database...");
        final DataTableSpec spec = data.getDataTableSpec();
        final String updateStmt = createUpdateStatement(table, setColumns, whereColumns);
        // problems writing more than 13 columns. the prepare statement
        // ensures that we can set the columns directly row-by-row, the
        // database will handle the commit
        long rowCount = data.size();
        int cnt = 1;
        int errorCnt = 0;
        int allErrors = 0;
        // count number of rows added to current batch
        int curBatchSize = 0;
        // selected timezone
        final TimeZone timezone = conSettings.getTimeZone();
        LOGGER.debug("Executing SQL statement as prepareStatement: " + updateStmt);
        final PreparedStatement stmt = conn.prepareStatement(updateStmt);
        // remember auto-commit flag
        final boolean autoCommit = conn.getAutoCommit();
        DatabaseConnectionSettings.setAutoCommit(conn, false);
        try {
            for (RowIterator it = data.iterator(); it.hasNext(); cnt++) {
                exec.checkCanceled();
                exec.setProgress(1.0 * cnt / rowCount, "Row " + "#" + cnt);
                final DataRow row = it.next();
                // SET columns
                for (int i = 0; i < setColumns.length; i++) {
                    final int dbIdx = i + 1;
                    final int columnIndex = spec.findColumnIndex(setColumns[i]);
                    final DataColumnSpec cspec = spec.getColumnSpec(columnIndex);
                    final DataCell cell = row.getCell(columnIndex);
                    fillStatement(stmt, dbIdx, cspec, cell, timezone, null);
                }
                // WHERE columns
                for (int i = 0; i < whereColumns.length; i++) {
                    final int dbIdx = i + 1 + setColumns.length;
                    final int columnIndex = spec.findColumnIndex(whereColumns[i]);
                    final DataColumnSpec cspec = spec.getColumnSpec(columnIndex);
                    final DataCell cell = row.getCell(columnIndex);
                    fillStatement(stmt, dbIdx, cspec, cell, timezone, null);
                }
                // if batch mode
                if (batchSize > 1) {
                    // a new row will be added
                    stmt.addBatch();
                }
                curBatchSize++;
                // if batch size equals number of row in batch or input table at end
                if ((curBatchSize == batchSize) || !it.hasNext()) {
                    curBatchSize = 0;
                    try {
                        // write batch
                        if (batchSize > 1) {
                            int[] status = stmt.executeBatch();
                            for (int i = 0; i < status.length; i++) {
                                updateStatus[cnt - status.length + i] = status[i];
                            }
                        } else {
                            // or write single row
                            int status = stmt.executeUpdate();
                            updateStatus[cnt - 1] = status;
                        }
                    } catch (Throwable t) {
                        // anyway.
                        if (!conn.getAutoCommit()) {
                            conn.commit();
                        }
                        allErrors++;
                        if (errorCnt > -1) {
                            final String errorMsg;
                            if (batchSize > 1) {
                                errorMsg = "Error while updating rows #" + (cnt - batchSize) + " - #" + cnt + ", reason: " + t.getMessage();
                            } else {
                                errorMsg = "Error while updating row #" + cnt + " (" + row.getKey() + "), reason: " + t.getMessage();
                            }
                            exec.setMessage(errorMsg);
                            if (errorCnt++ < 10) {
                                LOGGER.warn(errorMsg);
                            } else {
                                errorCnt = -1;
                                LOGGER.warn(errorMsg + " - more errors...", t);
                            }
                        }
                    } finally {
                        // clear batch if in batch mode
                        if (batchSize > 1) {
                            stmt.clearBatch();
                        }
                    }
                }
            }
            if (!conn.getAutoCommit()) {
                conn.commit();
            }
            if (allErrors == 0) {
                return null;
            } else {
                return "Errors \"" + allErrors + "\" updating " + rowCount + " rows.";
            }
        } finally {
            DatabaseConnectionSettings.setAutoCommit(conn, autoCommit);
            stmt.close();
        }
    });
}

Example 59 with RowIterator

use of org.knime.core.data.RowIterator in project knime-core by knime.

the class TableContentModel method processHiLiteRequest.

// processHiLiteEvent(KeyEvent, boolean)
/**
 * Propagates the selection status of the <code>ListSelectionModel</code>
 * parameter to the <code>HiLiteHandler</code>. This method does nothing if
 * hilighting is disabled (according to the
 * <code>hasHiLiteHandler()</code> method) or nothing is selected in
 * <code>selModel</code>.
 *
 * @param selModel To get selection status from
 * @param isHiLite Flag to tell if selected rows are to highlight or
 *        unhilight
 * @throws NullPointerException if <code>selModel</code> is null
 */
private void processHiLiteRequest(final ListSelectionModel selModel, final boolean isHiLite) {
    if (selModel == null) {
        throw new NullPointerException("No selection model specified");
    }
    if (!hasHiLiteHandler() || selModel.isSelectionEmpty()) {
        return;
    }
    final int firstSelected = selModel.getMinSelectionIndex();
    final int lastSelected = selModel.getMaxSelectionIndex();
    final HashSet<RowKey> selectedSet = new HashSet<RowKey>();
    // if all selected rows are in cache
    if ((firstSelected >= m_rowCountOfInterestInIterator - getCacheSize()) && (lastSelected < m_rowCountOfInterestInIterator)) {
        // no new iteration necessary, simply traverse cache
        final int length = lastSelected - firstSelected;
        for (int i = 0; i <= length; i++) {
            int k = firstSelected + i;
            if (selModel.isSelectedIndex(k)) {
                DataRow row = getRow(k);
                selectedSet.add(row.getKey());
            }
        }
    } else {
        // iteration necessary: use new (private) iterator
        // TODO: check for correctness when m_showOnlyHilited is set
        final RowIterator it = getNewDataIterator();
        for (int i = 0; it.hasNext() && i <= lastSelected; i++) {
            RowKey key = it.next().getKey();
            if (i >= firstSelected && selModel.isSelectedIndex(i)) {
                selectedSet.add(key);
            }
        }
    }
    // end if-else
    assert (!selectedSet.isEmpty());
    // fire event according to mode
    if (isHiLite) {
        m_hiLiteHdl.fireHiLiteEvent(selectedSet);
    } else {
        m_hiLiteHdl.fireUnHiLiteEvent(selectedSet);
    }
}

Example 60 with RowIterator

use of org.knime.core.data.RowIterator in project knime-core by knime.

the class TableContentModel method processHiLiteEvent.

/**
 * Translates hilight event and sets properties accordingly.
 *
 * @param e the event to evaluate.
 * @param isHiLite <code>true</code> for highlight request,
 *        <code>false</code> for an unhilight
 */
private void processHiLiteEvent(final KeyEvent e, final boolean isHiLite) {
    if (!hasData()) {
        return;
    }
    final int cacheSize = getCacheSize();
    final int oldRowCount = getRowCount();
    if (m_tableFilter.performsFiltering()) {
        /* what follows: run through the DataTable to the last
             * cached row, count the number of rows that have been
             * changed and add (or subtract, resp.) them from the global
             * row count
             */
        // counter - runs from 0 to m_rowCountOfInterest
        int c = 0;
        // #rows that changed up to m_rowCountOfInterest
        int changedCount = 0;
        Set<RowKey> keySet = e.keys();
        for (RowIterator it = getNewDataIterator(); it.hasNext() && c < m_rowCountOfInterest; ) {
            RowKey currentRowKey = it.next().getKey();
            boolean isNowOfInterest = m_tableFilter.matches(m_hiLiteHdl.isHiLit(currentRowKey));
            boolean hasChanged = keySet.contains(currentRowKey);
            changedCount += hasChanged ? 1 : 0;
            // was previously of interest?
            c += (hasChanged != isNowOfInterest) ? 1 : 0;
        }
        m_isRowCountOfInterestFinal = false;
        clearCache();
        boolean newRowsAdded;
        switch(m_tableFilter) {
            case HiliteOnly:
                newRowsAdded = isHiLite;
                break;
            case UnHiliteOnly:
                newRowsAdded = !isHiLite;
                break;
            default:
                throw new InternalError("Unexpected filter: " + m_tableFilter);
        }
        if (changedCount > 0) {
            assert (oldRowCount > 0);
            if (newRowsAdded) {
                fireTableRowsUpdated(0, oldRowCount - 1);
                m_rowCountOfInterest += changedCount;
                fireTableRowsInserted(oldRowCount, getRowCount() - 1);
            } else {
                m_rowCountOfInterest -= changedCount;
                m_rowCountOfInterest = Math.max(m_rowCountOfInterest, 0);
                fireTableRowsDeleted(m_rowCountOfInterest, oldRowCount - 1);
                fireTableRowsUpdated(0, getRowCount());
            }
        }
        // newRowsAdded is true (only more rows are added - check for that)
        if (oldRowCount == getRowCount() && newRowsAdded) {
            if (oldRowCount > 0) {
                // that would fail if there were 0 rows
                // move it to the last known
                getRow(oldRowCount - 1);
            }
            // are there new rows now?
            if (oldRowCount == getRowCount() && cacheNextRow()) {
                fireTableRowsInserted(oldRowCount, oldRowCount);
            }
        }
        return;
    }
    /* process event if it shows all rows */
    final Set<RowKey> s = e.keys();
    final int firstRowCached = firstRowCached();
    // remember first and last changed "i" (for event)
    int firstI = -1;
    int lastI = -1;
    // traverse all rows in cache and check if the rows' key is hilighted
    for (int i = 0; i < cacheSize; i++) {
        final int indexInCache = (firstRowCached + i) % cacheSize;
        final DataRow current = indexInCache >= 0 ? m_cachedRows[indexInCache] : null;
        if (current == null) {
            // last row, everything after is null
            break;
        }
        RowKey key = current.getKey();
        if (s.contains(key)) {
            // is newly hilighted
            if (firstI == -1) {
                firstI = indexInCache;
            }
            lastI = indexInCache;
            // wasn't previously set
            assert (isHiLite != m_hilitSet.get(indexInCache));
            m_hilitSet.set(indexInCache, isHiLite);
        }
    }
    if (lastI != -1) {
        // something has changed -> fire event
        assert (firstI != -1);
        fireRowsInCacheUpdated(firstI, lastI);
    }
}