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

Example 11 with CompareCondition

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

the class PartNode method process.

public List<DataRendererDefinition> process(FormPreviewPanel fpp, FoundSet fs, Table table, QuerySelect sqlString) throws Exception {
    // Selection model must be in print mode to be able to set the selection to -1  . Otherwise is not allowed by the selectionModel
    ((ISwingFoundSet) fs).getSelectionModel().hideSelectionForPrinting();
    // this is needed because we must keep sql the same in foundset during printing
    FoundSet rootSet = (FoundSet) fs.copy(false);
    foundSets.add(rootSet);
    IApplication app = fpp.getApplication();
    // retval
    List<DataRendererDefinition> list = new ArrayList<DataRendererDefinition>();
    if (part != null && (part.getPartType() == Part.LEADING_SUBSUMMARY || part.getPartType() == Part.TRAILING_SUBSUMMARY || isLeadingAndTrailingSubsummary)) {
        QuerySelect newSQLString = AbstractBaseQuery.deepClone(sqlString);
        IDataServer server = app.getDataServer();
        // build the sql parts  based on sort columns
        ArrayList<IQuerySelectValue> selectCols = new ArrayList<IQuerySelectValue>();
        ArrayList<QueryColumn> groupbyCols = new ArrayList<QueryColumn>();
        ArrayList<QuerySort> sortbyCols = new ArrayList<QuerySort>();
        for (SortColumn element : sortColumns) {
            BaseQueryTable queryTable = sqlString.getTable();
            Relation[] relations = element.getRelations();
            if (relations != null) {
                for (Relation relation : relations) {
                    ISQLTableJoin join = (ISQLTableJoin) sqlString.getJoin(queryTable, relation.getName());
                    if (join == null) {
                        // $NON-NLS-1$ //$NON-NLS-2$
                        Debug.log("Missing relation " + relation.getName() + " in join condition for form on table " + table.getName());
                    } else {
                        queryTable = join.getForeignTable();
                    }
                }
            }
            Column column = (Column) element.getColumn();
            QueryColumn queryColumn = column.queryColumn(queryTable);
            selectCols.add(queryColumn);
            groupbyCols.add(queryColumn);
            sortbyCols.add(new QuerySort(queryColumn, element.getSortOrder() == SortColumn.ASCENDING, fs.getFoundSetManager().getSortOptions(column)));
        }
        // make sql
        for (AggregateVariable ag : allAggregates) {
            selectCols.add(new QueryAggregate(ag.getType(), new QueryColumn(newSQLString.getTable(), -1, ag.getColumnNameToAggregate(), ag.getDataProviderType(), ag.getLength(), 0, null, ag.getFlags()), ag.getName()));
        }
        newSQLString.setColumns(selectCols);
        newSQLString.setGroupBy(groupbyCols);
        ArrayList<IQuerySort> oldSort = newSQLString.getSorts();
        // fix the sort (if columns not are selected of used in groupby they cannot be used in sort)
        newSQLString.setSorts(sortbyCols);
        FoundSetManager foundSetManager = ((FoundSetManager) app.getFoundSetManager());
        String transaction_id = foundSetManager.getTransactionID(table.getServerName());
        IDataSet data = server.performQuery(app.getClientID(), table.getServerName(), transaction_id, newSQLString, null, foundSetManager.getTableFilterParams(table.getServerName(), newSQLString), false, 0, foundSetManager.config.pkChunkSize() * 4, IDataServer.PRINT_QUERY);
        // create a new FoundSet with 'data' and with right 'table', 'where','whereArgs'
        SubSummaryFoundSet newSet = new SubSummaryFoundSet(app.getFoundSetManager(), rootSet, sortColumns, allAggregates, data, table);
        // restore the sort for child body parts
        newSQLString.setSorts(oldSort);
        // make new where for use in sub queries
        for (QuerySort sortbyCol : sortbyCols) {
            QueryColumn sc = (QueryColumn) (sortbyCol).getColumn();
            newSQLString.addCondition(SQLGenerator.CONDITION_SEARCH, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, sc, new Placeholder(new TablePlaceholderKey(sc.getTable(), '#' + sc.getName()))));
        }
        int count = newSet.getSize();
        for (int ii = 0; ii < count; ii++) {
            // make copy for setting sort column
            QuerySelect newSQLStringCopy = AbstractBaseQuery.deepClone(newSQLString);
            // handle the child first, this puts the rootset in the right state! for use of related(!) fields in the subsums
            // THIS is EXTREMELY important for correct printing, see also SubSummaryFoundSet.queryForRelatedFoundSet
            List<DataRendererDefinition> childRetval = null;
            IFoundSetInternal curLeafFoundSet = null;
            if (child != null) {
                for (int i = 0; i < sortbyCols.size(); i++) {
                    QueryColumn sc = (QueryColumn) (sortbyCols.get(i)).getColumn();
                    TablePlaceholderKey placeholderKey = new TablePlaceholderKey(sc.getTable(), '#' + sc.getName());
                    if (!newSQLStringCopy.setPlaceholderValue(placeholderKey, data.getRow(ii)[i])) {
                        Debug.error(// $NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$
                        new RuntimeException("Could not set placeholder " + placeholderKey + " in query " + newSQLStringCopy + "-- continuing"));
                    }
                }
                childRetval = child.process(fpp, rootSet, table, newSQLStringCopy);
                curLeafFoundSet = child.getCurrentLeafFoundSet();
            }
            SubSummaryFoundSet.PrintState state = (SubSummaryFoundSet.PrintState) newSet.getRecord(ii);
            state.setDelegate(curLeafFoundSet);
            if (part.getPartType() == Part.LEADING_SUBSUMMARY) {
                state.doAggregatesLookup();
                list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, state));
            }
            if (childRetval != null) {
                list.addAll(childRetval);
            }
            if (isLeadingAndTrailingSubsummary) {
                state.doAggregatesLookup();
                list.add(new DataRendererDefinition(fpp, renderParent, second_part, second_renderer, state));
            } else if (part.getPartType() == Part.TRAILING_SUBSUMMARY) {
                state.doAggregatesLookup();
                list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, state));
            }
        }
    } else // for handeling (virtual) body part
    {
        rootSet.browseAll(sqlString);
        int count = app.getFoundSetManager().getFoundSetCount(rootSet);
        for (int ii = 0; ii < count; ii++) {
            currentLeafFoundSet = rootSet;
            list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, rootSet, ii));
        }
    }
    return list;
}

Example 12 with CompareCondition

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

the class Messages method loadMessagesFromDatabaseRepositorySinglefilter.

private static void loadMessagesFromDatabaseRepositorySinglefilter(IServer server, Table table, String clientId, IDataServer dataServer, Properties properties, Properties localeProperties, Locale language, int loadingType, String searchKey, String searchText, Column filterColumn, String singleColumnValueFilter, IFoundSetManagerInternal fm) throws RemoteException, ServoyException {
    // $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
    Debug.trace("Loading messages from DB: Server: " + server.getName() + " Table: " + table.getName() + " Language: " + language);
    if (loadingType == ALL_LOCALES || loadingType == DEFAULT_LOCALE) {
        QueryTable messagesTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
        QuerySelect sql = new QuerySelect(messagesTable);
        // $NON-NLS-1$
        QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
        // $NON-NLS-1$
        QueryColumn msgVal = new QueryColumn(messagesTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
        // $NON-NLS-1$
        QueryColumn msgLang = new QueryColumn(messagesTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
        sql.addColumn(msgKey);
        sql.addColumn(msgVal);
        // $NON-NLS-1$
        String condMessages = "MESSAGES";
        sql.addCondition(condMessages, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR | IBaseSQLCondition.ORNULL_MODIFIER, msgLang, new QueryColumnValue("", null)));
        if (filterColumn != null) {
            QueryColumn columnFilter = filterColumn.queryColumn(messagesTable);
            CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, columnFilter, new QueryColumnValue(singleColumnValueFilter, null));
            sql.addCondition(condMessages, cc);
        }
        // Filter to only include records with the default (null) value for columns flagged as Tenant column
        for (Column column : table.getTenantColumns()) {
            CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, column.queryColumn(messagesTable), null);
            sql.addCondition("_svy_tenant_id_filter_" + column.getName(), cc);
        }
        if (searchKey != null || searchText != null) {
            QueryTable subselectTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
            QuerySelect subselect = new QuerySelect(subselectTable);
            // $NON-NLS-1$
            QueryColumn msgKeySub = new QueryColumn(subselectTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
            // $NON-NLS-1$
            QueryColumn msgValueSub = new QueryColumn(subselectTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
            // $NON-NLS-1$
            QueryColumn msgLangSub = new QueryColumn(subselectTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
            subselect.addColumn(msgKeySub);
            // $NON-NLS-1$
            String condSearch = "SEARCH";
            if (searchKey != null) {
                subselect.addCondition(condSearch, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, msgKeySub, new QueryColumnValue('%' + searchKey + '%', null)));
            }
            if (searchText != null) {
                subselect.addConditionOr(condSearch, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, msgValueSub, new QueryColumnValue('%' + searchText + '%', null)));
            }
            // $NON-NLS-1$
            String condLang = "LANGUAGE";
            subselect.addCondition(condLang, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLangSub, new QueryColumnValue(localeToString(language), null)));
            subselect.addConditionOr(condLang, // $NON-NLS-1$
            new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR | IBaseSQLCondition.ORNULL_MODIFIER, msgLangSub, new QueryColumnValue("", null)));
            sql.addCondition(condMessages, new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, new QueryColumn[] { msgKey }, subselect, true));
        }
        // $NON-NLS-1$
        if (Debug.tracing())
            Debug.trace("Loading messages from DB: SQL: " + sql);
        IDataSet set = dataServer.performQuery(clientId, server.getName(), null, sql, null, fm != null ? fm.getTableFilterParams(server.getName(), sql) : null, false, 0, Integer.MAX_VALUE, IDataServer.MESSAGES_QUERY);
        for (int i = 0; i < set.getRowCount(); i++) {
            Object[] row = set.getRow(i);
            if (row[0] != null && row[1] != null) {
                properties.setProperty((String) row[0], (String) row[1]);
            }
        }
    }
    if (loadingType == ALL_LOCALES || loadingType == SPECIFIED_LANGUAGE) {
        fillLocaleMessages(clientId, dataServer, table, server.getName(), filterColumn, singleColumnValueFilter, searchKey, searchText, language, localeProperties != null ? localeProperties : properties, SPECIFIED_LANGUAGE, fm);
    }
    if (loadingType == ALL_LOCALES || loadingType == SPECIFIED_LOCALE) {
        fillLocaleMessages(clientId, dataServer, table, server.getName(), filterColumn, singleColumnValueFilter, searchKey, searchText, language, localeProperties != null ? localeProperties : properties, SPECIFIED_LOCALE, fm);
    }
}

Example 13 with CompareCondition

use of com.servoy.j2db.query.CompareCondition 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;
}

Example 14 with CompareCondition

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

the class LookupListModel method fillDBColumnValues.

/**
 * @param txt
 * @throws Exception
 * @throws RepositoryException
 * @throws RemoteException
 */
private void fillDBColumnValues(String dataProviderID, String txt) throws ServoyException {
    QuerySelect sqlParts = AbstractBaseQuery.deepClone(creationSQLParts);
    sqlParts.clearCondition(SQLGenerator.CONDITION_SEARCH);
    if (// $NON-NLS-1$
    !"".equals(txt)) {
        sqlParts.setCondition(SQLGenerator.CONDITION_SEARCH, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, new QueryFunction(QueryFunctionType.upper, DBValueList.getQuerySelectValue(table, sqlParts.getTable(), dataProviderID), dataProviderID), txt.toUpperCase() + '%'));
    } else {
        sqlParts.clearCondition(SQLGenerator.CONDITION_SEARCH);
    }
    try {
        FoundSetManager foundSetManager = ((FoundSetManager) application.getFoundSetManager());
        String transaction_id = foundSetManager.getTransactionID(table.getServerName());
        ArrayList<TableFilter> tableFilterParams = foundSetManager.getTableFilterParams(table.getServerName(), sqlParts);
        if (// apply name as filter on column valuelist_name in creationSQLParts
        nameFilter != null) {
            if (tableFilterParams == null) {
                tableFilterParams = new ArrayList<TableFilter>();
            }
            tableFilterParams.add(nameFilter);
        }
        SQLStatement trackingInfo = null;
        if (foundSetManager.getEditRecordList().hasAccess(table, IRepository.TRACKING_VIEWS)) {
            trackingInfo = new SQLStatement(ISQLActionTypes.SELECT_ACTION, table.getServerName(), table.getName(), null, null);
            trackingInfo.setTrackingData(sqlParts.getColumnNames(), new Object[][] {}, new Object[][] {}, application.getUserUID(), foundSetManager.getTrackingInfo(), application.getClientID());
        }
        IDataSet set = application.getDataServer().performQuery(application.getClientID(), table.getServerName(), transaction_id, sqlParts, null, tableFilterParams, !sqlParts.isUnique(), 0, 100, IDataServer.VALUELIST_QUERY, trackingInfo);
        for (int i = 0; i < set.getRowCount(); i++) {
            Object[] row = set.getRow(i);
            if (// $NON-NLS-1$
            row[0] != null && !"".equals(row[0])) {
                alReal.add(row[0]);
            }
        }
        hadMoreRows = set.hadMoreRows();
    } catch (RemoteException e) {
        throw new RepositoryException(e);
    }
}