Search in sources :

Example 6 with ISQLTableJoin

use of com.servoy.j2db.query.ISQLTableJoin 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;
}
Also used : Placeholder(com.servoy.j2db.query.Placeholder) IFoundSetInternal(com.servoy.j2db.dataprocessing.IFoundSetInternal) ArrayList(java.util.ArrayList) SortColumn(com.servoy.j2db.dataprocessing.SortColumn) IQuerySort(com.servoy.j2db.query.IQuerySort) Relation(com.servoy.j2db.persistence.Relation) ISQLTableJoin(com.servoy.j2db.query.ISQLTableJoin) QueryColumn(com.servoy.j2db.query.QueryColumn) SortColumn(com.servoy.j2db.dataprocessing.SortColumn) Column(com.servoy.j2db.persistence.Column) IQuerySort(com.servoy.j2db.query.IQuerySort) QuerySort(com.servoy.j2db.query.QuerySort) CompareCondition(com.servoy.j2db.query.CompareCondition) QueryAggregate(com.servoy.j2db.query.QueryAggregate) FoundSetManager(com.servoy.j2db.dataprocessing.FoundSetManager) TablePlaceholderKey(com.servoy.j2db.query.TablePlaceholderKey) IDataServer(com.servoy.j2db.dataprocessing.IDataServer) SubSummaryFoundSet(com.servoy.j2db.dataprocessing.SubSummaryFoundSet) ISwingFoundSet(com.servoy.j2db.dataprocessing.ISwingFoundSet) FoundSet(com.servoy.j2db.dataprocessing.FoundSet) QuerySelect(com.servoy.j2db.query.QuerySelect) AggregateVariable(com.servoy.j2db.persistence.AggregateVariable) IApplication(com.servoy.j2db.IApplication) BaseQueryTable(com.servoy.base.query.BaseQueryTable) QueryColumn(com.servoy.j2db.query.QueryColumn) SubSummaryFoundSet(com.servoy.j2db.dataprocessing.SubSummaryFoundSet) IDataSet(com.servoy.j2db.dataprocessing.IDataSet) IQuerySelectValue(com.servoy.j2db.query.IQuerySelectValue)

Example 7 with ISQLTableJoin

use of com.servoy.j2db.query.ISQLTableJoin 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);
}
Also used : TablePlaceholderKey(com.servoy.j2db.query.TablePlaceholderKey) ArrayList(java.util.ArrayList) SafeArrayList(com.servoy.j2db.util.SafeArrayList) FlattenedSolution(com.servoy.j2db.FlattenedSolution) QuerySelect(com.servoy.j2db.query.QuerySelect) BaseQueryTable(com.servoy.base.query.BaseQueryTable) QueryTable(com.servoy.j2db.query.QueryTable) BaseQueryTable(com.servoy.base.query.BaseQueryTable) ISQLTableJoin(com.servoy.j2db.query.ISQLTableJoin) ITable(com.servoy.j2db.persistence.ITable) IQuerySelectValue(com.servoy.j2db.query.IQuerySelectValue) Pair(com.servoy.j2db.util.Pair)

Example 8 with ISQLTableJoin

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

the class JSDatabaseManager method convertFoundSet.

public FoundSet convertFoundSet(Object foundset, Object related) throws ServoyException {
    checkAuthorized();
    if (foundset instanceof FoundSet && ((FoundSet) foundset).getTable() != null) {
        FoundSet fs_old = (FoundSet) foundset;
        try {
            String relationName;
            if (related instanceof RelatedFoundSet) {
                relationName = ((RelatedFoundSet) related).getRelationName();
            } else if (related instanceof String) {
                relationName = (String) related;
            } else {
                // $NON-NLS-1$
                Debug.warn("convertFoundSet: invalid argument " + related);
                return null;
            }
            Relation relation = application.getFlattenedSolution().getRelation(relationName);
            if (relation == null || relation.isMultiServer() || fs_old.getTable() == null || !fs_old.getTable().equals(application.getFlattenedSolution().getTable(relation.getPrimaryDataSource()))) {
                // $NON-NLS-1$
                Debug.warn("convertFoundSet: cannot use relation " + relationName);
                return null;
            }
            ITable ft = application.getFlattenedSolution().getTable(relation.getForeignDataSource());
            FoundSet fs_new = (FoundSet) application.getFoundSetManager().getNewFoundSet(ft, null, application.getFoundSetManager().getDefaultPKSortColumns(ft.getDataSource()));
            QuerySelect sql = fs_old.getPksAndRecords().getQuerySelectForModification();
            SQLSheet sheet_new = fs_old.getSQLSheet().getRelatedSheet(relation, ((FoundSetManager) application.getFoundSetManager()).getSQLGenerator());
            if (sheet_new != null) {
                BaseQueryTable oldTable = sql.getTable();
                ISQLTableJoin join = (ISQLTableJoin) sql.getJoin(oldTable, relation.getName());
                if (join == null) {
                    join = SQLGenerator.createJoin(application.getFlattenedSolution(), relation, oldTable, new QueryTable(ft.getSQLName(), ft.getDataSource(), ft.getCatalog(), ft.getSchema()), true, fs_old);
                    sql.addJoin(join);
                }
                BaseQueryTable mainTable = join.getForeignTable();
                // invert the join
                sql.setTable(mainTable);
                // $NON-NLS-1$
                join.invert("INVERTED." + join.getName());
                // set the columns to be the PKs from the related table
                ArrayList<IQuerySelectValue> pkColumns = new ArrayList<IQuerySelectValue>();
                Iterator<Column> pks = sheet_new.getTable().getRowIdentColumns().iterator();
                while (pks.hasNext()) {
                    Column column = pks.next();
                    pkColumns.add(column.queryColumn(mainTable));
                }
                sql.setColumns(pkColumns);
                // sorting will be on the original columns, when distinct is set, this will conflict with the related pk columns
                sql.setDistinct(false);
                fs_new.setSQLSelect(sql);
                return fs_new;
            }
        } catch (Exception e) {
            Debug.error(e);
        }
    }
    return null;
}
Also used : ArrayList(java.util.ArrayList) IJSFoundSet(com.servoy.base.scripting.api.IJSFoundSet) QueryString(com.servoy.j2db.persistence.QueryString) QuerySelect(com.servoy.j2db.query.QuerySelect) BaseQueryTable(com.servoy.base.query.BaseQueryTable) QueryTable(com.servoy.j2db.query.QueryTable) ApplicationException(com.servoy.j2db.ApplicationException) RemoteException(java.rmi.RemoteException) SQLException(java.sql.SQLException) ServoyException(com.servoy.j2db.util.ServoyException) RepositoryException(com.servoy.j2db.persistence.RepositoryException) Relation(com.servoy.j2db.persistence.Relation) BaseQueryTable(com.servoy.base.query.BaseQueryTable) ISQLTableJoin(com.servoy.j2db.query.ISQLTableJoin) QueryColumn(com.servoy.j2db.query.QueryColumn) IBaseColumn(com.servoy.base.persistence.IBaseColumn) Column(com.servoy.j2db.persistence.Column) QBColumn(com.servoy.j2db.querybuilder.impl.QBColumn) ITable(com.servoy.j2db.persistence.ITable) IQuerySelectValue(com.servoy.j2db.query.IQuerySelectValue)

Aggregations

ISQLTableJoin (com.servoy.j2db.query.ISQLTableJoin)8 ArrayList (java.util.ArrayList)7 BaseQueryTable (com.servoy.base.query.BaseQueryTable)6 Column (com.servoy.j2db.persistence.Column)6 QueryColumn (com.servoy.j2db.query.QueryColumn)6 QueryTable (com.servoy.j2db.query.QueryTable)6 ITable (com.servoy.j2db.persistence.ITable)5 Relation (com.servoy.j2db.persistence.Relation)5 IQuerySelectValue (com.servoy.j2db.query.IQuerySelectValue)5 IColumn (com.servoy.j2db.persistence.IColumn)4 QuerySelect (com.servoy.j2db.query.QuerySelect)4 RepositoryException (com.servoy.j2db.persistence.RepositoryException)3 CompareCondition (com.servoy.j2db.query.CompareCondition)3 IQuerySort (com.servoy.j2db.query.IQuerySort)3 Placeholder (com.servoy.j2db.query.Placeholder)3 BaseQueryColumn (com.servoy.base.query.BaseQueryColumn)2 FlattenedSolution (com.servoy.j2db.FlattenedSolution)2 AggregateVariable (com.servoy.j2db.persistence.AggregateVariable)2 IRelation (com.servoy.j2db.persistence.IRelation)2 AndCondition (com.servoy.j2db.query.AndCondition)2