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

Example 11 with Placeholder

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

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

Example 13 with Placeholder

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

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

the class FoundSet method createRecord.

private IRecordInternal createRecord(Row rowData, boolean javascriptRecord) throws ApplicationException {
    if (findMode) {
        // limit to 200
        if (pksAndRecords.getCachedRecords().size() > fsm.config.pkChunkSize())
            return null;
        return new FindState(this);
    }
    if (javascriptRecord && !hasAccess(IRepository.INSERT)) {
        throw new ApplicationException(ServoyException.NO_CREATE_ACCESS, new Object[] { getTable().getName() });
    }
    if (rowData == null && relationName != null) {
        Relation relation = fsm.getApplication().getFlattenedSolution().getRelation(relationName);
        if (relation != null) {
            Placeholder ph = creationSqlSelect.getPlaceholder(SQLGenerator.createRelationKeyPlaceholderKey(creationSqlSelect.getTable(), relation.getName()));
            if (ph == null || !ph.isSet() || ph.getValue() == null || ((Object[]) ph.getValue()).length == 0) {
                Debug.trace(// $NON-NLS-1$
                "New record failed because related foundset had no parameters, or trying to make a new findstate when it is nested more then 2 deep");
                return null;
            }
            if (!relation.getAllowCreationRelatedRecords()) {
                ApplicationException applicationException = new ApplicationException(ServoyException.NO_RELATED_CREATE_ACCESS, new Object[] { relation.getName() });
                applicationException.setContext(this.toString());
                throw applicationException;
            }
        }
    }
    try {
        if (javascriptRecord && !executeFoundsetTriggerBreakOnFalse(null, PROPERTY_ONCREATEMETHODID, false)) {
            // $NON-NLS-1$
            Debug.trace("New record creation was denied by onCreateRecord method");
            return null;
        }
    } catch (ServoyException e) {
        Debug.error(e);
        return null;
    }
    IRecordInternal newRecord = null;
    if (rowData == null) {
        Object[] data = sheet.getNewRowData(fsm.getApplication(), this);
        newRecord = new Record(this, rowManager.createNotYetExistInDBRowObject(data, true));
        sheet.processCopyValues(newRecord);
    } else {
        newRecord = new Record(this, rowData);
    }
    try {
        if (javascriptRecord) {
            executeFoundsetTrigger(new Object[] { newRecord }, PROPERTY_ONAFTERCREATEMETHODID, false);
        }
    } catch (ServoyException e) {
        Debug.error(e);
    }
    return newRecord;
}

Example 15 with Placeholder

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