Examples with ISQLCondition com.servoy.j2db.query.ISQLCondition used on opensource projects

Example 6 with ISQLCondition

use of com.servoy.j2db.query.ISQLCondition in project servoy-client by Servoy.

the class FoundSet method performFind.

/**
 * Execute the find sql, returns the number of records found, returns -1 when the call was blocked by a trigger
 * @param clearLastResult
 * @param reduceSearch
 * @param clearIfZero
 * @param cancelFind
 * @param returnInvalidRangeConditions
 * @return
 * @throws ServoyException
 */
public int performFind(boolean clearLastResult, boolean reduceSearch, boolean clearIfZero, boolean cancelFind, List<String> returnInvalidRangeConditions) throws // perform the find
ServoyException {
    int numberOfFindStates = getSize();
    if (cancelFind) {
        // ignore find states
        pksAndRecords.setPks(null, 0);
        setSelectedIndex(-1);
    } else {
        try {
            if (!executeFoundsetTriggerBreakOnFalse(new Object[] { Boolean.valueOf(clearLastResult), Boolean.valueOf(reduceSearch) }, PROPERTY_ONSEARCHMETHODID, true)) {
                // $NON-NLS-1$
                Debug.trace("Foundset search was denied by onSearchFoundset method");
                // blocked
                return -1;
            }
        } catch (ServoyException e) {
            Debug.error(e);
            // blocked
            return -1;
        }
        if (clearLastResult)
            removeLastFound();
        setSelectedIndex(numberOfFindStates > 0 ? 0 : -1);
    }
    try {
        QuerySelect findSqlSelect = getCurrentStateQuery(reduceSearch, false);
        if (returnInvalidRangeConditions != null) {
            ISQLCondition sqlCondition = findSqlSelect.getCondition(SQLGenerator.CONDITION_SEARCH);
            returnInvalidRangeConditions.addAll(AbstractBaseQuery.getInvalidRangeConditions(sqlCondition));
        }
        // cache pks
        String transaction_id = fsm.getTransactionID(sheet);
        long time = System.currentTimeMillis();
        IDataSet findPKs = null;
        try {
            findPKs = performQuery(transaction_id, findSqlSelect, getRowIdentColumnTypes(), 0, fsm.config.pkChunkSize(), IDataServer.FIND_BROWSER_QUERY);
        } catch (RemoteException e) {
            throw new RepositoryException(e);
        }
        if (Debug.tracing()) {
            Debug.trace(// $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
            "Find executed, time: " + (System.currentTimeMillis() - time) + " thread: " + Thread.currentThread().getName() + ", sql: " + findSqlSelect.toString());
        }
        IFoundSetChanges changes = null;
        if (findPKs.getRowCount() == 0) {
            if (clearIfZero) {
                changes = pksAndRecords.setPksAndQuery(null, 0, findSqlSelect);
                clearInternalState(true);
                setSelectedIndex(-1);
            }
        } else {
            fireSelectionAdjusting();
            changes = pksAndRecords.setPksAndQuery(findPKs, findPKs.getRowCount(), findSqlSelect);
            clearInternalState(true);
            // notify about aggregate change,because the find has cleared them all.
            fireAggregateChangeWithEvents(null);
        }
        initialized = true;
        fireDifference(numberOfFindStates, getSize(), changes);
        if (getSelectedIndex() == -1 && getSize() > 0) {
            setSelectedIndex(0);
        }
        int nfound = findPKs.getRowCount();
        try {
            executeFoundsetTrigger(null, PROPERTY_ONAFTERSEARCHMETHODID, false);
        } catch (ServoyException e) {
            Debug.error(e);
        }
        return nfound;
    } catch (ServoyException e) {
        pksAndRecords.setPks(null, 0);
        clearInternalState(true);
        setSelectedIndex(-1);
        fireDifference(numberOfFindStates, 0, null);
        throw e;
    }
}

Example 7 with ISQLCondition

use of com.servoy.j2db.query.ISQLCondition in project servoy-client by Servoy.

the class JSDatabaseManager method js_mergeRecords.

// strongly recommended to use a transaction
// currently does not support compound pks
/**
 * Merge records from the same foundset, updates entire datamodel (via foreign type on columns) with destination
 * record pk, deletes source record. Do use a transaction!
 *
 * This function is very handy in situations where duplicate data exists. It allows you to merge the two records
 * and move all related records in one go. Say the source_record is "Ikea" and the combined_destination_record is "IKEA", the
 * "Ikea" record is deleted and all records related to it (think of contacts and orders, for instance) will be related
 * to the "IKEA" record.
 *
 * The function takes an optional array of column names. If provided, the data in the named columns will be copied
 * from source_record to combined_destination_record.
 *
 * Note that it is essential for both records to originate from the same foundset, as shown in the sample code.
 *
 * @sample databaseManager.mergeRecords(foundset.getRecord(1),foundset.getRecord(2));
 *
 * @param sourceRecord The source JSRecord to copy from.
 * @param combinedDestinationRecord The target/destination JSRecord to copy into.
 * @param columnNames The column names array that should be copied.
 *
 * @return true if the records could me merged.
 */
public boolean js_mergeRecords(IRecordInternal sourceRecord, IRecordInternal combinedDestinationRecord, String[] columnNames) throws ServoyException {
    checkAuthorized();
    if (sourceRecord != null && combinedDestinationRecord != null) {
        FoundSetManager fsm = (FoundSetManager) application.getFoundSetManager();
        try {
            if (sourceRecord.getParentFoundSet() != combinedDestinationRecord.getParentFoundSet()) {
                return false;
            }
            Table mainTable = (Table) combinedDestinationRecord.getParentFoundSet().getTable();
            String mainTableForeignType = mainTable.getName();
            String transaction_id = fsm.getTransactionID(mainTable.getServerName());
            Object sourceRecordPK = null;
            Object combinedDestinationRecordPK = null;
            Column pkc = null;
            Iterator<Column> pk_it = mainTable.getRowIdentColumns().iterator();
            if (pk_it.hasNext()) {
                pkc = pk_it.next();
                sourceRecordPK = sourceRecord.getValue(pkc.getDataProviderID());
                if (sourceRecordPK == null)
                    sourceRecordPK = ValueFactory.createNullValue(pkc.getType());
                combinedDestinationRecordPK = combinedDestinationRecord.getValue(pkc.getDataProviderID());
                if (combinedDestinationRecordPK == null)
                    combinedDestinationRecordPK = ValueFactory.createNullValue(pkc.getType());
                // multipk not supported
                if (pk_it.hasNext())
                    return false;
            }
            List<SQLStatement> updates = new ArrayList<SQLStatement>();
            IServer server = application.getSolution().getServer(mainTable.getServerName());
            if (server != null) {
                Iterator<String> it = server.getTableNames(false).iterator();
                while (it.hasNext()) {
                    String tableName = it.next();
                    Table table = (Table) server.getTable(tableName);
                    // not supported
                    if (table.getRowIdentColumnsCount() > 1)
                        continue;
                    Iterator<Column> it2 = table.getColumns().iterator();
                    while (it2.hasNext()) {
                        Column c = it2.next();
                        if (c.getColumnInfo() != null) {
                            if (mainTableForeignType.equalsIgnoreCase(c.getColumnInfo().getForeignType())) {
                                // update table set foreigntypecolumn = combinedDestinationRecordPK where foreigntypecolumn = sourceRecordPK
                                QueryTable qTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
                                QueryUpdate qUpdate = new QueryUpdate(qTable);
                                QueryColumn qc = c.queryColumn(qTable);
                                qUpdate.addValue(qc, combinedDestinationRecordPK);
                                ISQLCondition condition = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, qc, sourceRecordPK);
                                qUpdate.setCondition(condition);
                                IDataSet pks = new BufferedDataSet();
                                // unknown number of records changed
                                pks.addRow(new Object[] { ValueFactory.createTableFlushValue() });
                                SQLStatement statement = new SQLStatement(ISQLActionTypes.UPDATE_ACTION, table.getServerName(), table.getName(), pks, transaction_id, qUpdate, fsm.getTableFilterParams(table.getServerName(), qUpdate));
                                updates.add(statement);
                            }
                        }
                    }
                }
            }
            IDataSet pks = new BufferedDataSet();
            pks.addRow(new Object[] { sourceRecordPK });
            QueryTable qTable = new QueryTable(mainTable.getSQLName(), mainTable.getDataSource(), mainTable.getCatalog(), mainTable.getSchema());
            QueryDelete qDelete = new QueryDelete(qTable);
            ISQLCondition condition = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkc.queryColumn(qTable), sourceRecordPK);
            qDelete.setCondition(condition);
            SQLStatement statement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, mainTable.getServerName(), mainTable.getName(), pks, transaction_id, qDelete, fsm.getTableFilterParams(mainTable.getServerName(), qDelete));
            // check that the row is really deleted
            statement.setExpectedUpdateCount(1);
            updates.add(statement);
            IFoundSetInternal sfs = sourceRecord.getParentFoundSet();
            if (combinedDestinationRecord.startEditing()) {
                if (columnNames != null) {
                    for (String element : columnNames) {
                        if (element == null)
                            continue;
                        if (sfs.getColumnIndex(element) >= 0) {
                            combinedDestinationRecord.setValue(element, sourceRecord.getValue(element));
                        }
                    }
                }
                fsm.getEditRecordList().stopEditing(true, combinedDestinationRecord);
            } else {
                return false;
            }
            Object[] results = fsm.getDataServer().performUpdates(fsm.getApplication().getClientID(), updates.toArray(new ISQLStatement[updates.size()]));
            for (int i = 0; results != null && i < results.length; i++) {
                if (results[i] instanceof ServoyException) {
                    throw (ServoyException) results[i];
                }
            }
            // sfs.deleteRecord(sfs.getRecordIndex(sourceRecord), true); not needed, will be flushed from memory in finally
            return true;
        } catch (Exception ex) {
            // $NON-NLS-1$
            application.handleException(// $NON-NLS-1$
            application.getI18NMessage("servoy.foundsetupdater.updateFailed"), new ApplicationException(ServoyException.SAVE_FAILED, ex));
        } finally {
            fsm.flushCachedDatabaseData(null);
        }
    }
    return false;
}