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

Example 11 with TablePlaceholderKey

use of com.servoy.j2db.query.TablePlaceholderKey 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 TablePlaceholderKey

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

the class FoundSet method loadByQuery.

public boolean loadByQuery(IQueryBuilder query) throws ServoyException {
    // check if this query is on our base table
    if (!Utils.stringSafeEquals(getDataSource(), query.getDataSource())) {
        throw new RepositoryException(// $NON-NLS-1$//$NON-NLS-2$
        "Cannot load foundset with query based on another table (" + getDataSource() + " != " + query.getDataSource() + ')').setContext(this.toString());
    }
    // makes a clone
    QuerySelect sqlSelect = ((QBSelect) query).build();
    if (sqlSelect.getColumns() == null) {
        // no columns, add pk
        // note that QBSelect.build() already returns a clone
        Iterator<Column> pkIt = ((Table) getTable()).getRowIdentColumns().iterator();
        if (!pkIt.hasNext()) {
            throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { getTable().getName() }).setContext(this.toString());
        }
        while (pkIt.hasNext()) {
            Column c = pkIt.next();
            sqlSelect.addColumn(c.queryColumn(sqlSelect.getTable()));
        }
    }
    Placeholder dynamicPKplaceholder = sqlSelect.getPlaceholder(new TablePlaceholderKey(sqlSelect.getTable(), SQLGenerator.PLACEHOLDER_FOUNDSET_PKS));
    if (dynamicPKplaceholder != null && dynamicPKplaceholder.isSet() && dynamicPKplaceholder.getValue() instanceof Object[]) {
        // loading from saved query, dynamic pk was replaced by array in serialization, make dynamic again
        dynamicPKplaceholder.setValue(new DynamicPkValuesArray(getSQLSheet().getTable().getRowIdentColumns(), SQLGenerator.createPKValuesDataSet(getSQLSheet().getTable().getRowIdentColumns(), (Object[][]) dynamicPKplaceholder.getValue())));
    }
    if (sqlSelect.getSorts() == null) {
        // query does not define sort, use last sorts
        fsm.getSQLGenerator().addSorts(sqlSelect, sqlSelect.getTable(), this, sheet.getTable(), lastSortColumns == null ? defaultSort : lastSortColumns, true, false);
    } else {
        // try to determine the SortColumns from the query-sort
        lastSortColumns = determineSortColumns(sqlSelect);
    }
    return loadByQuery(addFilterconditions(sqlSelect, foundSetFilters));
}

Example 13 with TablePlaceholderKey

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

Example 14 with TablePlaceholderKey

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

the class RowManager method rollbackFromDB.

/**
 * Rollback data from db, return whether the data was found
 * @param row
 * @param doFires
 * @param mode
 * @return
 * @throws ServoyException
 */
boolean rollbackFromDB(Row row, boolean doFires, Row.ROLLBACK_MODE mode) throws ServoyException {
    if (!row.existInDB()) {
        return false;
    }
    Object[] pk = row.getPK();
    QuerySelect select = (QuerySelect) AbstractBaseQuery.deepClone(sheet.getSQL(SQLSheet.SELECT));
    if (!select.setPlaceholderValue(new TablePlaceholderKey(select.getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY), pk)) {
        Debug.error(new RuntimeException(// $NON-NLS-1$
        "Could not set placeholder " + new TablePlaceholderKey(select.getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY) + " in query " + select + // $NON-NLS-1$//$NON-NLS-2$
        "-- continuing"));
    }
    IDataSet formdata;
    try {
        String transaction_id = null;
        GlobalTransaction gt = fsm.getGlobalTransaction();
        if (gt != null) {
            transaction_id = gt.getTransactionID(sheet.getServerName());
        }
        formdata = fsm.getDataServer().performQuery(fsm.getApplication().getClientID(), sheet.getServerName(), transaction_id, select, null, fsm.getTableFilterParams(sheet.getServerName(), select), false, 0, 1, false);
    } catch (RemoteException e) {
        throw new RepositoryException(e);
    }
    // construct Rows
    boolean found = formdata.getRowCount() >= 1;
    if (found) {
        row.setRollbackData(formdata.getRow(0), mode);
    }
    if (doFires)
        fireNotifyChange(null, row, row.getPKHashKey(), null, found ? RowEvent.UPDATE : RowEvent.DELETE);
    return found;
}

Example 15 with TablePlaceholderKey

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

the class RowManager method getRows.

synchronized List<Row> getRows(IDataSet pks, int row, int sizeHint, boolean queryAll) throws ServoyException {
    List<Row> retval = new SafeArrayList<Row>();
    if (row >= pks.getRowCount())
        return retval;
    Object[] pk = pks.getRow(row);
    Row rowData = queryAll ? null : getCachedRow(pk).getLeft();
    if (rowData == null) {
        String transaction_id = null;
        GlobalTransaction gt = fsm.getGlobalTransaction();
        if (gt != null) {
            transaction_id = gt.getTransactionID(sheet.getServerName());
        }
        IDataSet formdata = null;
        QuerySelect select = (QuerySelect) sheet.getSQL(SQLSheet.SELECT);
        int maxRow = Math.min(row + sizeHint, pks.getRowCount());
        // get the PK array
        int ncols = pks.getColumnCount();
        int nvals = 0;
        @SuppressWarnings("unchecked") List<Object>[] valueLists = new List[ncols];
        for (int c = 0; c < ncols; c++) {
            valueLists[c] = new ArrayList<Object>();
        }
        for (int i = 0; i < maxRow - row; i++) {
            Object[] data = pks.getRow(row + i);
            if (data != null) {
                if (data.length != ncols) {
                    // $NON-NLS-1$
                    throw new RuntimeException("Inconsistent PK set width");
                }
                boolean add = true;
                for (int c = 0; add && c < ncols; c++) {
                    add = !(data[c] instanceof DbIdentValue);
                }
                if (add) {
                    nvals++;
                    for (int c = 0; c < ncols; c++) {
                        valueLists[c].add(data[c]);
                    }
                }
            }
        }
        Object[][] values = new Object[ncols][];
        for (int c = 0; c < ncols; c++) {
            values[c] = valueLists[c].toArray();
        }
        if (!select.setPlaceholderValue(new TablePlaceholderKey(select.getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY), values)) {
            Debug.error(new RuntimeException(// $NON-NLS-1$
            "Could not set placeholder " + new TablePlaceholderKey(select.getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY) + " in query " + select + // $NON-NLS-1$//$NON-NLS-2$
            "-- continuing"));
        }
        long time = System.currentTimeMillis();
        try {
            SQLStatement trackingInfo = null;
            if (fsm.getEditRecordList().hasAccess(sheet.getTable(), IRepository.TRACKING_VIEWS)) {
                trackingInfo = new SQLStatement(ISQLActionTypes.SELECT_ACTION, sheet.getServerName(), sheet.getTable().getName(), pks, null);
                trackingInfo.setTrackingData(sheet.getColumnNames(), new Object[][] {}, new Object[][] {}, fsm.getApplication().getUserUID(), fsm.getTrackingInfo(), fsm.getApplication().getClientID());
            }
            formdata = fsm.getDataServer().performQuery(fsm.getApplication().getClientID(), sheet.getServerName(), transaction_id, select, null, /* use types as reported by the db */
            fsm.getTableFilterParams(sheet.getServerName(), select), false, 0, nvals, IDataServer.FOUNDSET_LOAD_QUERY, trackingInfo);
            if (Debug.tracing()) {
                Debug.trace(// $NON-NLS-1$ //$NON-NLS-2$
                Thread.currentThread().getName() + ": getting RowData time: " + (System.currentTimeMillis() - time) + ", SQL: " + select.toString());
            }
        } catch (RemoteException e) {
            throw new RepositoryException(e);
        }
        // construct Rows
        for (int k = row; k < maxRow; k++) {
            String pkHash = createPKHashKey(pks.getRow(k));
            // reorder based on pk in mem,cannot do related sort icw SELECT_IN
            for (int r = 0; r < formdata.getRowCount(); r++) {
                Object[] columndata = formdata.getRow(r);
                rowData = getRowBasedonPKFromEntireColumnArray(columndata);
                if (pkHash.equals(createPKHashKey(rowData.getPK()))) {
                    retval.set(k - row, rowData);
                    break;
                }
            }
        }
        if (retval.size() < maxRow - row) {
            retval.set(maxRow - row - 1, null);
        }
    } else {
        retval.add(rowData);
        if (sizeHint > 1) {
            int maxRow = Math.min(row + fsm.config.chunkSize(), pks.getRowCount());
            for (int r = row + 1; r < maxRow; r++) {
                Object[] data = pks.getRow(r);
                Row r2 = getCachedRow(data).getLeft();
                if (r2 == null) {
                    // if there is no row te be found, just break and return the currently found retval.
                    break;
                }
                retval.add(r2);
            }
        }
    }
    return retval;
}