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

Example 21 with IQuerySelectValue

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

the class QBJoin method createColumn.

@Override
protected QBColumn createColumn(String name) throws RepositoryException {
    ITableReference foreignTableReference = join.getForeignTableReference();
    if (foreignTableReference instanceof TableExpression) {
        Table joinTable = getRoot().getTable(foreignTableReference.getTable().getDataSource());
        if (joinTable == null) {
            throw new RepositoryException("Cannot find column '" + name + "' in data source '" + foreignTableReference.getTable().getDataSource() + "'");
        }
        Column col = joinTable.getColumn(name);
        if (col == null) {
            throw new RepositoryException("Cannot find column '" + name + "' in data source '" + foreignTableReference.getTable().getDataSource() + "'");
        }
        return new QBColumn(getRoot(), this, new QueryColumn(getQueryTable(), col.getID(), col.getSQLName(), col.getType(), col.getLength(), col.getScale(), col.getNativeTypename(), col.getFlags(), false));
    } else if (foreignTableReference instanceof DerivedTable) {
        QuerySelect query = ((DerivedTable) foreignTableReference).getQuery();
        for (IQuerySelectValue qcol : query.getColumns()) {
            if (name.equals(qcol.getAliasOrName()) || (qcol.getColumn() != null && name.equals(generateNormalizedNonReservedOSName(qcol.getColumn().getName())))) {
                return new QBColumn(getRoot(), this, new QueryColumn(foreignTableReference.getTable(), generateNormalizedNonReservedOSName(name)));
            }
        }
    }
    throw new RepositoryException("Cannot find column '" + name + "' in source '" + foreignTableReference + "'");
}

Example 22 with IQuerySelectValue

use of com.servoy.j2db.query.IQuerySelectValue 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 23 with IQuerySelectValue

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

the class FoundSet method omitState.

public boolean omitState(int[] rows) {
    if (sheet.getTable() == null) {
        getFoundSetManager().getApplication().reportJSWarning("ommit fails because of an invalid table");
        return false;
    }
    List<IRecordInternal> recordsToOmit = new ArrayList<IRecordInternal>();
    boolean success = true;
    for (int row : rows) {
        if (row < 0 || row >= getSize()) {
            success = false;
            getFoundSetManager().getApplication().reportJSWarning("ommit fails because of an invalid index " + row);
            continue;
        }
        IRecordInternal state = getRecord(row);
        if (state != null && fsm.getEditRecordList().stopEditing(false, state) == ISaveConstants.STOPPED) {
            recordsToOmit.add(state);
        } else {
            getFoundSetManager().getApplication().reportJSWarning("Record not omitted for index " + row + " because could not stop the edit on it " + state);
            success = false;
        }
    }
    if (recordsToOmit.size() > 0) {
        if (omittedPKs == null)
            omittedPKs = new BufferedDataSet();
        for (IRecordInternal dsState : recordsToOmit) {
            omittedPKs.addRow(dsState.getPK());
        }
        QuerySelect sqlSelect = pksAndRecords.getQuerySelectForModification();
        // replace the OMIT condition, keep sort (could be custom sort, different from lastSortColumns)
        List<IQuerySelectValue> pkQueryColumns = sqlSelect.getColumns();
        sqlSelect.setCondition(SQLGenerator.CONDITION_OMIT, SQLGenerator.createSetConditionFromPKs(IBaseSQLCondition.NOT_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), sheet.getTable().getRowIdentColumns(), omittedPKs));
        synchronized (pksAndRecords) {
            pksAndRecords.setPksAndQuery(pksAndRecords.getPks(), pksAndRecords.getDbIndexLastPk(), sqlSelect, true);
        }
        for (IRecordInternal dsState : recordsToOmit) {
            removeRecordInternalEx(dsState, pksAndRecords.getCachedRecords().indexOf(dsState));
        }
    }
    return success;
}

Example 24 with IQuerySelectValue

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

the class FoundSetManager method getViewFoundSet.

@SuppressWarnings("nls")
@Override
public ViewFoundSet getViewFoundSet(String name, QBSelect query, boolean register) {
    if (query.getQuery().getColumns() == null || query.getQuery().getColumns().size() == 0) {
        throw new RuntimeException("Can't create a ViewFoundset with name: " + name + " and query  " + query + " that has no columns");
    }
    String dataSource = DataSourceUtils.createViewDataSource(name);
    ViewFoundSet vfs = new ViewFoundSet(dataSource, query.build(), application.getFoundSetManager(), config.pkChunkSize());
    // if this datasource defintion is created already in the developer then we need to check if the query columns are correctly matching it.
    ServoyJSONObject columnsDef = null;
    Iterator<TableNode> tblIte = application.getFlattenedSolution().getTableNodes(dataSource);
    while (tblIte.hasNext() && columnsDef == null) {
        TableNode tn = tblIte.next();
        columnsDef = tn.getColumns();
        if (columnsDef != null) {
            TableDef def = DatabaseUtils.deserializeTableInfo(columnsDef);
            for (ColumnInfoDef col : def.columnInfoDefSet) {
                IQuerySelectValue selectValue = getSelectvalue(query, col.name);
                if (selectValue == null) {
                    Debug.error("Column " + col.name + " of type " + col.columnType.toString() + " defined in view datasource '" + dataSource + "' was not found in the provided query.");
                    return null;
                }
                BaseColumnType columnType = selectValue.getColumnType();
                // relax the mapping on default Servoy types
                if (columnType != null && Column.mapToDefaultType(columnType.getSqlType()) != Column.mapToDefaultType(col.columnType.getSqlType())) {
                    Debug.error("Column type for column '" + col.name + " of type " + col.columnType.toString() + "' defined in view datasource '" + dataSource + "' does not match the one " + columnType + " provided in the query.");
                    return null;
                }
            }
        }
    }
    registerViewFoundSet(vfs, !register);
    return vfs;
}

Example 25 with IQuerySelectValue

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

the class RelatedValueList method createRelatedValuelistQuery.

public static Pair<QuerySelect, BaseQueryTable> createRelatedValuelistQuery(IServiceProvider application, ValueList valueList, Relation[] relations, IRecordInternal parentState) throws ServoyException {
    if (parentState == null) {
        return null;
    }
    FoundSetManager foundSetManager = (FoundSetManager) application.getFoundSetManager();
    SQLGenerator sqlGenerator = foundSetManager.getSQLGenerator();
    IGlobalValueEntry scopesScopeProvider = foundSetManager.getScopesScopeProvider();
    SQLSheet childSheet = sqlGenerator.getCachedTableSQLSheet(relations[0].getPrimaryDataSource());
    // this returns quickly if it already has a sheet for that relation, but optimize further?
    sqlGenerator.makeRelatedSQL(childSheet, relations[0]);
    QuerySelect select = AbstractBaseQuery.deepClone((QuerySelect) childSheet.getRelatedSQLDescription(relations[0].getName()).getSQLQuery());
    Object[] relationWhereArgs = foundSetManager.getRelationWhereArgs(parentState, relations[0], false);
    if (relationWhereArgs == null) {
        return null;
    }
    TablePlaceholderKey placeHolderKey = SQLGenerator.createRelationKeyPlaceholderKey(select.getTable(), relations[0].getName());
    if (!select.setPlaceholderValue(placeHolderKey, relationWhereArgs)) {
        // $NON-NLS-1$//$NON-NLS-2$
        Debug.error(new RuntimeException("Could not set relation placeholder " + placeHolderKey + " in query " + select));
        return null;
    }
    FlattenedSolution fs = application.getFlattenedSolution();
    BaseQueryTable lastTable = select.getTable();
    ITable foreignTable = fs.getTable(relations[0].getForeignDataSource());
    for (int i = 1; i < relations.length; i++) {
        foreignTable = fs.getTable(relations[i].getForeignDataSource());
        ISQLTableJoin join = SQLGenerator.createJoin(application.getFlattenedSolution(), relations[i], lastTable, new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema()), true, scopesScopeProvider);
        select.addJoin(join);
        lastTable = join.getForeignTable();
    }
    List<SortColumn> defaultSort = foundSetManager.getSortColumns(relations[relations.length - 1].getForeignDataSource(), valueList.getSortOptions());
    foundSetManager.getSQLGenerator().addSorts(select, lastTable, scopesScopeProvider, foreignTable, defaultSort, true, true);
    int showValues = valueList.getShowDataProviders();
    int returnValues = valueList.getReturnDataProviders();
    int total = (showValues | returnValues);
    ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
    if ((total & 1) != 0) {
        columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID1()));
    }
    if ((total & 2) != 0) {
        columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID2()));
    }
    if ((total & 4) != 0) {
        columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID3()));
    }
    select.setColumns(columns);
    // not allowed in all situations
    select.setDistinct(false);
    return new Pair<QuerySelect, BaseQueryTable>(select, lastTable);
}