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

Example 6 with TablePlaceholderKey

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

the class RelatedFoundSet method createRelatedFoundSets.

/**
 * Create multiple related foundsets in one call to the data server.
 *
 * @param factory
 * @param app
 * @param parents same length as whereArsgLists
 * @param relation
 * @param sheet
 * @param whereArsgLists
 * @param defaultSortColumns
 * @return
 * @throws ServoyException
 */
public static IFoundSetInternal[] createRelatedFoundSets(IFoundSetFactory factory, IFoundSetManagerInternal app, IRecordInternal[] parents, Relation relation, SQLSheet sheet, Object[][] whereArsgLists, List<SortColumn> defaultSortColumns) throws ServoyException {
    if (sheet == null) {
        // $NON-NLS-1$
        throw new IllegalArgumentException(app.getApplication().getI18NMessage("servoy.foundSet.error.sqlsheet"));
    }
    FoundSetManager fsm = (FoundSetManager) app;
    List<SortColumn> sortColumns;
    if (defaultSortColumns == null || defaultSortColumns.size() == 0) {
        sortColumns = sheet.getDefaultPKSort();
    } else {
        sortColumns = defaultSortColumns;
    }
    QuerySelect cleanSelect = fsm.getSQLGenerator().getPKSelectSqlSelect(fsm.getScopesScopeProvider(), sheet.getTable(), null, null, true, null, sortColumns, false);
    QuerySelect relationSelect = (QuerySelect) sheet.getRelatedSQLDescription(relation.getName()).getSQLQuery();
    // don't select all columns in pk select
    cleanSelect.setColumns(AbstractBaseQuery.relinkTable(relationSelect.getTable(), cleanSelect.getTable(), relationSelect.getColumnsClone()));
    // copy the where (is foreign where)
    cleanSelect.setCondition(SQLGenerator.CONDITION_RELATION, AbstractBaseQuery.relinkTable(relationSelect.getTable(), cleanSelect.getTable(), relationSelect.getConditionClone(SQLGenerator.CONDITION_RELATION)));
    TablePlaceholderKey placeHolderKey = SQLGenerator.createRelationKeyPlaceholderKey(cleanSelect.getTable(), relation.getName());
    // all queries
    QuerySelect[] sqlSelects = new QuerySelect[whereArsgLists.length];
    // all aggregates
    QuerySelect[] aggregateSelects = new QuerySelect[whereArsgLists.length];
    List<Integer> queryIndex = new ArrayList<Integer>(whereArsgLists.length);
    Map<Integer, Row> cachedRows = new HashMap<Integer, Row>();
    List<QueryData> queryDatas = new ArrayList<QueryData>(whereArsgLists.length);
    String transactionID = fsm.getTransactionID(sheet);
    String clientID = fsm.getApplication().getClientID();
    ArrayList<TableFilter> sqlFilters = fsm.getTableFilterParams(sheet.getServerName(), cleanSelect);
    for (int i = 0; i < whereArsgLists.length; i++) {
        Object[] whereArgs = whereArsgLists[i];
        if (whereArgs == null || whereArgs.length == 0) {
            // $NON-NLS-1$
            throw new IllegalArgumentException(app.getApplication().getI18NMessage("servoy.relatedfoundset.error.noFK") + relation.getName());
        }
        QuerySelect sqlSelect;
        if (i == whereArsgLists.length - 1) {
            // the last one, use the template, no clone needed
            sqlSelect = cleanSelect;
        } else {
            sqlSelect = AbstractBaseQuery.deepClone(cleanSelect);
        }
        if (!sqlSelect.setPlaceholderValue(placeHolderKey, whereArgs)) {
            // $NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$
            Debug.error(new RuntimeException("Could not set placeholder " + placeHolderKey + " in query " + sqlSelect + "-- continuing"));
        }
        sqlSelects[i] = sqlSelect;
        // Check for non-empty where-arguments, joins on null-conditions are not allowed (similar to FK constraints in databases)
        if (!whereArgsIsEmpty(whereArgs)) {
            Row cachedRow = null;
            if (relation.isFKPKRef(fsm.getApplication().getFlattenedSolution())) {
                // optimize for FK->PK relation, if the data is already cached, do not query
                RowManager rowManager = fsm.getRowManager(relation.getForeignDataSource());
                if (rowManager != null) {
                    cachedRow = rowManager.getCachedRow(whereArgs).getLeft();
                }
            }
            if (cachedRow != null) {
                if (Debug.tracing()) {
                    // $NON-NLS-1$
                    Debug.trace(Thread.currentThread().getName() + ": Found cached FK record");
                }
                cachedRows.put(Integer.valueOf(i), cachedRow);
            } else if (!parents[i].existInDataSource() && !fsm.config.loadRelatedRecordsIfParentIsNew() && relation.hasPKFKCondition(fsm.getApplication().getFlattenedSolution())) {
            /*
					 * Optimize for init of related foundsets on a parent record that is new and where the relation includes equal conditions for all the parent
					 * rowIdentifier columns
					 *
					 * In this case no query has to be made to the DB to fetch existing records, as there wouldn't be any.
					 */
            } else {
                ISQLSelect selectStatement = AbstractBaseQuery.deepClone((ISQLSelect) sqlSelect);
                // Note: put a clone of sqlSelect in the queryDatas list, we will compress later over multiple queries using pack().
                // Clone is needed because packed queries may not be save to manipulate.
                SQLStatement trackingInfo = null;
                if (fsm.getEditRecordList().hasAccess(sheet.getTable(), IRepository.TRACKING_VIEWS)) {
                    trackingInfo = new SQLStatement(ISQLActionTypes.SELECT_ACTION, sheet.getServerName(), sheet.getTable().getName(), null, null);
                    trackingInfo.setTrackingData(sheet.getColumnNames(), new Object[][] {}, new Object[][] {}, fsm.getApplication().getUserUID(), fsm.getTrackingInfo(), fsm.getApplication().getClientID());
                }
                queryDatas.add(new QueryData(selectStatement, sqlFilters, !sqlSelect.isUnique(), 0, fsm.config.initialRelatedChunkSize(), IDataServer.RELATION_QUERY, trackingInfo));
                queryIndex.add(Integer.valueOf(i));
                QuerySelect aggregateSelect = FoundSet.getAggregateSelect(sheet, sqlSelect);
                if (aggregateSelect != null) {
                    // Note: see note about clone above.
                    queryDatas.add(new QueryData(AbstractBaseQuery.deepClone((ISQLSelect) aggregateSelect), fsm.getTableFilterParams(sheet.getServerName(), aggregateSelect), false, 0, 1, IDataServer.AGGREGATE_QUERY, null));
                    // same index for aggregates
                    queryIndex.add(Integer.valueOf(i));
                    aggregateSelects[i] = aggregateSelect;
                }
            }
        }
    }
    IDataSet[] dataSets = null;
    if (queryDatas.size() > 0) {
        try {
            // pack is safe here because queryDatas contains only cloned ISQLSelect objects
            QueryData[] qDatas = queryDatas.toArray(new QueryData[queryDatas.size()]);
            AbstractBaseQuery.acceptVisitor(qDatas, new PackVisitor());
            int size = 0;
            if (// trace the message size
            Debug.tracing()) {
                try {
                    ByteArrayOutputStream bs = new ByteArrayOutputStream();
                    ObjectOutputStream os = new ObjectOutputStream(bs);
                    os.writeObject(qDatas);
                    os.close();
                    size = bs.size();
                } catch (Exception e) {
                    Debug.trace(e);
                }
            }
            long time = System.currentTimeMillis();
            dataSets = fsm.getDataServer().performQuery(clientID, sheet.getServerName(), transactionID, qDatas);
            if (Debug.tracing()) {
                Debug.trace(// $NON-NLS-1$ //$NON-NLS-2$
                Thread.currentThread().getName() + ": Relation query: " + relation.getName() + " with: " + qDatas.length + " queries,query size: " + size + ",time: " + (System.currentTimeMillis() - time) + // $NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$
                "ms");
            }
        } catch (RepositoryException re) {
            testException(clientID, re);
            throw re;
        } catch (RemoteException e) {
            testException(clientID, e.getCause());
            throw new RepositoryException(e);
        }
    }
    IFoundSetInternal[] foundsets = new RelatedFoundSet[whereArsgLists.length];
    int d = 0;
    for (int i = 0; i < whereArsgLists.length; i++) {
        IDataSet data;
        IDataSet aggregateData = null;
        int index = (d >= queryIndex.size()) ? -1 : queryIndex.get(d).intValue();
        if (index == i) {
            // regular query
            data = dataSets[d++];
            // optionally followed by aggregate
            index = (d >= queryIndex.size()) ? -1 : queryIndex.get(d).intValue();
            if (index == i) {
                // aggregate
                aggregateData = dataSets[d++];
            }
        } else {
            data = new BufferedDataSet();
            Row row = cachedRows.get(Integer.valueOf(i));
            if (row != null) {
                // cached
                data.addRow(row.getRawColumnData());
            }
        // else whereArgsIsEmpty
        }
        foundsets[i] = factory.createRelatedFoundSet(data, sqlSelects[i], app, parents[i], relation.getName(), sheet, sortColumns, aggregateSelects[i], aggregateData);
        if (aggregateData != null && foundsets[i] instanceof FoundSet) {
            ((FoundSet) foundsets[i]).fillAggregates(aggregateSelects[i], aggregateData);
        }
    }
    if (d != queryIndex.size()) {
        // $NON-NLS-1$
        throw new RepositoryException("Related query parameters out of sync " + d + '/' + queryIndex.size());
    }
    return foundsets;
}

Example 7 with TablePlaceholderKey

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

the class RowManager method deleteRow.

void deleteRow(IRowListener src, Row r, boolean tracking, boolean partOfBiggerDelete) throws ServoyException {
    // $NON-NLS-1$
    if (r.getRowManager() != this)
        throw new IllegalArgumentException("I'm not the row manager from row");
    // prevent it processed by any update, changed is false now
    r.flagExistInDB();
    if (!partOfBiggerDelete) {
        QueryDelete sqlDelete = AbstractBaseQuery.deepClone((QueryDelete) sheet.getSQLDescription(SQLSheet.DELETE).getSQLQuery());
        Object[] pk = r.getPK();
        if (!sqlDelete.setPlaceholderValue(new TablePlaceholderKey(sqlDelete.getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY), pk)) {
            Debug.error(new RuntimeException(// $NON-NLS-1$
            "Could not set placeholder " + new TablePlaceholderKey(sqlDelete.getTable(), SQLGenerator.PLACEHOLDER_PRIMARY_KEY) + " in query " + sqlDelete + // $NON-NLS-1$ //$NON-NLS-2$
            "-- continuing"));
        }
        IDataSet pks = new BufferedDataSet();
        pks.addRow(pk);
        ISQLStatement[] stats_a = new ISQLStatement[1];
        String tid = null;
        GlobalTransaction gt = fsm.getGlobalTransaction();
        if (gt != null) {
            tid = gt.getTransactionID(sheet.getServerName());
        }
        SQLStatement statement = new SQLStatement(ISQLActionTypes.DELETE_ACTION, sheet.getServerName(), sheet.getTable().getName(), pks, tid, sqlDelete, fsm.getTableFilterParams(sheet.getServerName(), sqlDelete));
        // check that 1 record is deleted
        statement.setExpectedUpdateCount(1);
        stats_a[0] = statement;
        if (tracking) {
            statement.setTrackingData(sheet.getColumnNames(), r.getRawColumnData() != null ? new Object[][] { r.getRawColumnData() } : null, null, fsm.getApplication().getUserUID(), fsm.getTrackingInfo(), fsm.getApplication().getClientID());
        }
        try {
            Object[] results = fsm.getDataServer().performUpdates(fsm.getApplication().getClientID(), stats_a);
            for (int i = 0; results != null && i < results.length; i++) {
                if (results[i] instanceof ServoyException) {
                    throw (ServoyException) results[i];
                }
            }
        } catch (RemoteException e) {
            throw new RepositoryException(e);
        }
        SoftReferenceWithData<Row, Pair<Map<String, List<CalculationDependency>>, CalculationDependencyData>> removed;
        synchronized (this) {
            removed = pkRowMap.remove(r.getPKHashKey());
        }
        fireDependingCalcs(removed, null, null);
    } else {
        synchronized (this) {
            pkRowMap.remove(r.getPKHashKey());
        }
    }
    fireNotifyChange(src, r, r.getPKHashKey(), null, RowEvent.DELETE);
}

Example 8 with TablePlaceholderKey

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

the class SQLGenerator method createTableSQL.

private SQLSheet createTableSQL(String dataSource, boolean cache) throws ServoyException {
    if (dataSource == null) {
        return createNoTableSQL(cache);
    }
    Table table = (Table) application.getFoundSetManager().getTable(dataSource);
    if (table == null) {
        // $NON-NLS-1$
        throw new RepositoryException("Cannot create sql: table not found for data source '" + dataSource + '\'');
    }
    SQLSheet retval = new SQLSheet(application, table.getServerName(), table);
    // never remove this line, due to recursive behaviour, register a state when immediately!
    if (cache)
        cachedDataSourceSQLSheets.put(dataSource, retval);
    QueryTable queryTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
    QuerySelect select = new QuerySelect(queryTable);
    QueryDelete delete = new QueryDelete(queryTable);
    QueryInsert insert = new QueryInsert(queryTable);
    QueryUpdate update = new QueryUpdate(queryTable);
    List<Column> columns = new ArrayList<Column>();
    Iterator<Column> it1 = table.getColumns().iterator();
    while (it1.hasNext()) {
        Column c = it1.next();
        ColumnInfo ci = c.getColumnInfo();
        if (ci != null && ci.isExcluded()) {
            continue;
        }
        columns.add(c);
    }
    List<String> requiredDataProviderIDs = new ArrayList<String>();
    Iterator<Column> pks = table.getRowIdentColumns().iterator();
    if (!pks.hasNext()) {
        throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { table.getName() });
    }
    List<QueryColumn> pkQueryColumns = new ArrayList<QueryColumn>();
    while (pks.hasNext()) {
        Column column = pks.next();
        if (!columns.contains(column))
            columns.add(column);
        requiredDataProviderIDs.add(column.getDataProviderID());
        pkQueryColumns.add(column.queryColumn(queryTable));
    }
    Iterator<Column> it2 = columns.iterator();
    select.setColumns(makeQueryColumns(it2, queryTable, insert));
    SetCondition pkSelect = new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), new Placeholder(new TablePlaceholderKey(queryTable, PLACEHOLDER_PRIMARY_KEY)), true);
    select.setCondition(CONDITION_SEARCH, pkSelect);
    delete.setCondition(deepClone(pkSelect));
    update.setCondition(deepClone(pkSelect));
    // fill dataprovider map
    List<String> dataProviderIDsDilivery = new ArrayList<String>();
    for (Column col : columns) {
        dataProviderIDsDilivery.add(col.getDataProviderID());
    }
    retval.addSelect(select, dataProviderIDsDilivery, requiredDataProviderIDs, null);
    retval.addDelete(delete, requiredDataProviderIDs);
    retval.addInsert(insert, dataProviderIDsDilivery);
    retval.addUpdate(update, dataProviderIDsDilivery, requiredDataProviderIDs);
    // related stuff
    createAggregates(retval, queryTable);
    return retval;
}

Example 9 with TablePlaceholderKey

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

the class PKDataSet method pksToBeUpdated.

private void pksToBeUpdated() {
    if (transactionListener == null && pksAndRecordsHolder != null && pksAndRecordsHolder.hasDynamicPlaceholder()) {
        // check if I am within a transaction
        GlobalTransaction globalTransaction = pksAndRecordsHolder.getFoundSet().getFoundSetManager().getGlobalTransaction();
        if (globalTransaction != null) {
            // add a listener for rollback to restore pks on rollback so that query returns deleted records
            DynamicPkValuesArray dynArray = getDynamicPkValuesArray();
            if (dynArray != null) {
                final DynamicPkValuesArray pksBeforeTransaction = dynArray.clone();
                globalTransaction.addTransactionEndListener(transactionListener = new ITransactable() {

                    public void processPostRollBack() {
                        // restore old pks from before transaction, so that pks deleted in the transaction are still found
                        if (pksAndRecordsHolder.hasDynamicPlaceholder()) {
                            // query still has the pk set condition, set the condition back to the pk set from before the transaction
                            pksAndRecordsHolder.getQuerySelectForReading().acceptVisitor(new PlaceHolderSetter(new TablePlaceholderKey(pksAndRecordsHolder.getQuerySelectForReading().getTable(), SQLGenerator.PLACEHOLDER_FOUNDSET_PKS), pksBeforeTransaction));
                        }
                        transactionListener = null;
                    }

                    public void processPostCommit() {
                        transactionListener = null;
                    }
                });
            }
        }
    }
}

Example 10 with TablePlaceholderKey

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

the class PKDataSet method getDynamicPkValuesArray.

private DynamicPkValuesArray getDynamicPkValuesArray() {
    if (pksAndRecordsHolder != null && pksAndRecordsHolder.hasDynamicPlaceholder() && pksAndRecordsHolder.getFoundSet() != null && !pksAndRecordsHolder.getFoundSet().isInFindMode()) {
        Placeholder placeholder = pksAndRecordsHolder.getQuerySelectForReading().getPlaceholder(new TablePlaceholderKey(pksAndRecordsHolder.getQuerySelectForReading().getTable(), SQLGenerator.PLACEHOLDER_FOUNDSET_PKS));
        Object value = placeholder.getValue();
        if (value instanceof DynamicPkValuesArray) {
            return (DynamicPkValuesArray) value;
        }
    }
    return null;
}