Examples with BaseQueryTable com.servoy.base.query.BaseQueryTable used on opensource projects

Example 11 with BaseQueryTable

use of com.servoy.base.query.BaseQueryTable in project servoy-client by Servoy.

the class ValidatingDelegateDataServer method validateQuery.

protected void validateQuery(IVisitable query) {
    if (query != null) {
        final Set<String> datasources = new HashSet<>();
        query.acceptVisitor(new IVisitor() {

            @Override
            public Object visit(Object o) {
                if (o instanceof BaseQueryTable) {
                    String dataSource = ((BaseQueryTable) o).getDataSource();
                    if (dataSource != null) {
                        datasources.add(dataSource);
                    }
                }
                return o;
            }
        });
        if (datasources.size() > 1) {
            String singleServer = null;
            for (String datasource : datasources) {
                String serverName = DataSourceUtils.getDataSourceServerName(datasource);
                if (singleServer == null) {
                    singleServer = serverName;
                } else if (serverName != null && !singleServer.equals(serverName)) {
                    String msg = "Cannot perform multiserver-query '" + serverName + "'/'" + singleServer + "'";
                    application.reportJSError(msg, query);
                }
            }
        }
    }
}

Example 12 with BaseQueryTable

use of com.servoy.base.query.BaseQueryTable in project servoy-client by Servoy.

the class SQLGenerator method createExistsCondition.

public static ISQLCondition createExistsCondition(IDataProviderHandler flattenedSolution, QuerySelect sqlSelect, ISQLCondition condition, List<IRelation> relations, BaseQueryTable columnTable, IGlobalValueEntry provider, BaseQueryColumn[] pkQueryColumns) throws RepositoryException {
    // exists (select 1 from innermain join related1 ... join relatedn where innermain.pk = main.pk having aggregate(relatedn))
    if (relations.size() == 0) {
        // searching for aggregate in main table, does no make sense.. ignore in search
        return null;
    }
    QuerySelect existsSelect = new QuerySelect(new QueryTable(sqlSelect.getTable().getName(), sqlSelect.getTable().getDataSource(), sqlSelect.getTable().getCatalogName(), sqlSelect.getTable().getSchemaName()));
    existsSelect.addColumn(new QueryColumnValue(Integer.valueOf(1), null, true));
    // innermain.pk = main.pk
    QueryColumn[] innerPkColumns = new QueryColumn[pkQueryColumns.length];
    for (int p = 0; p < pkQueryColumns.length; p++) {
        BaseQueryColumn pk = pkQueryColumns[p];
        innerPkColumns[p] = new QueryColumn(existsSelect.getTable(), pk.getId(), pk.getName(), pk.getColumnType().getSqlType(), pk.getColumnType().getLength(), pk.getColumnType().getScale(), pk.getNativeTypename(), pk.getFlags(), pk.isIdentity());
        // group by on the inner pk, some dbs (hxtt dbf) require that
        existsSelect.addGroupBy(innerPkColumns[p]);
    }
    existsSelect.addCondition("AGGREGATE-SEARCH", new // $NON-NLS-1$
    SetCondition(// $NON-NLS-1$
    new int[] { IBaseSQLCondition.EQUALS_OPERATOR }, // $NON-NLS-1$
    innerPkColumns, pkQueryColumns, true));
    // add the joins
    BaseQueryTable prevTable = existsSelect.getTable();
    for (IRelation relation : relations) {
        ITable foreignTable = flattenedSolution.getTable(relation.getForeignDataSource());
        QueryTable foreignQtable = new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema());
        existsSelect.addJoin(createJoin(flattenedSolution, relation, prevTable, foreignQtable, true, provider));
        prevTable = foreignQtable;
    }
    existsSelect.addHaving(AbstractBaseQuery.relinkTable(columnTable, prevTable, condition));
    return new ExistsCondition(existsSelect, true);
}

Example 13 with BaseQueryTable

use of com.servoy.base.query.BaseQueryTable in project servoy-client by Servoy.

the class SQLGenerator method createConditionFromFindState.

private ISQLCondition createConditionFromFindState(FindState s, QuerySelect sqlSelect, IGlobalValueEntry provider, List<IQuerySelectValue> pkQueryColumns) throws RepositoryException {
    ISQLCondition and = null;
    List<RelatedFindState> relatedFindStates = s.createFindStateJoins(sqlSelect, Collections.<IRelation>emptyList(), sqlSelect.getTable(), provider);
    for (int i = 0; relatedFindStates != null && i < relatedFindStates.size(); i++) {
        RelatedFindState rfs = relatedFindStates.get(i);
        FindState state = rfs.getFindState();
        BaseQueryTable columnTable = rfs.getPrimaryTable();
        SQLSheet sheet = state.getParentFoundSet().getSQLSheet();
        Table table = sheet.getTable();
        Iterator<Map.Entry<String, Object>> it = state.getColumnData().entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> elem = it.next();
            final String dataProviderID = elem.getKey();
            Object raw = elem.getValue();
            if (raw == null)
                continue;
            int dataProviderType = -1;
            ConverterInfo columnConverterInfo = null;
            IColumnConverter columnConverter = null;
            IQuerySelectValue qCol = null;
            IColumn c = table.getColumn(dataProviderID);
            if (c != null) {
                dataProviderType = c.getDataProviderType();
                columnConverterInfo = sheet.getColumnConverterInfo(dataProviderID);
                if (columnConverterInfo != null) {
                    columnConverter = application.getFoundSetManager().getColumnConverterManager().getConverter(columnConverterInfo.converterName);
                    if (columnConverter instanceof ITypedColumnConverter) {
                        int convType = ((ITypedColumnConverter) columnConverter).getToObjectType(columnConverterInfo.props);
                        if (convType != Integer.MAX_VALUE) {
                            dataProviderType = mapToDefaultType(convType);
                        }
                    }
                }
                // a column
                qCol = ((Column) c).queryColumn(columnTable);
            } else {
                // not a column, check for aggregates
                Iterator<AggregateVariable> aggregateVariables = application.getFlattenedSolution().getAggregateVariables(sheet.getTable(), false);
                while (c == null && aggregateVariables.hasNext()) {
                    AggregateVariable agg = aggregateVariables.next();
                    if (dataProviderID.equals(agg.getDataProviderID())) {
                        // found aggregate
                        c = agg;
                    }
                }
                if (c != null) {
                    dataProviderType = c.getDataProviderType();
                    Map<String, QuerySelect> aggregates = sheet.getAggregates();
                    if (aggregates != null) {
                        QuerySelect aggregateSelect = aggregates.get(dataProviderID);
                        if (aggregateSelect != null) {
                            qCol = ((List<IQuerySelectValue>) AbstractBaseQuery.relinkTable(aggregateSelect.getTable(), columnTable, aggregateSelect.getColumnsClone())).get(0);
                        }
                    }
                }
            }
            if (qCol == null) {
                // not a column and not an aggregate
                // $NON-NLS-1$ //$NON-NLS-2$
                Debug.log("Ignoring search on unknown/unsupported data provider '" + dataProviderID + "'");
                continue;
            }
            ParsedFormat format = state.getFormat(dataProviderID);
            String formatString = null;
            if (format != null) {
                formatString = format.getEditFormat();
                if (formatString == null) {
                    formatString = format.getDisplayFormat();
                }
            }
            if (Utils.stringIsEmpty(formatString)) {
                formatString = TagResolver.getDefaultFormatForType(application, dataProviderType);
            }
            ISQLCondition or = null;
            if (raw.getClass().isArray()) {
                int length = Array.getLength(raw);
                Object[] elements = new Object[length];
                for (int e = 0; e < length; e++) {
                    Object obj = Array.get(raw, e);
                    if (obj instanceof Wrapper) {
                        obj = ((Wrapper) obj).unwrap();
                    }
                    // Have to use getAsRightType twice here, once to parse using format (getAsType(dataProviderType, formatString))
                    // and once to convert for query (getAsType(c.getDataProviderType(), null))
                    Object converted = convertFromObject(application, columnConverter, columnConverterInfo, dataProviderID, c.getDataProviderType(), Column.getAsRightType(dataProviderType, c.getFlags(), obj, formatString, c.getLength(), null, false, false), false);
                    elements[e] = Column.getAsRightType(c.getDataProviderType(), c.getFlags(), converted, null, c.getLength(), null, false, false);
                }
                // where qCol in (e1, e2, ..., en)
                or = new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, new IQuerySelectValue[] { qCol }, new Object[][] { elements }, true);
            } else {
                final IColumnConverter fColumnConverter = columnConverter;
                final ConverterInfo fColumnConverterInfo = columnConverterInfo;
                final int fDataProviderType = c.getDataProviderType();
                or = (ISQLCondition) BaseSQLGenerator.parseFindExpression(QueryFactory.INSTANCE, raw, qCol, columnTable, dataProviderType, formatString, c, rfs.getRelations().size() > 0 && relatedNullSearchAddPkCondition(), new IValueConverter() {

                    @Override
                    public Object convertFromObject(Object value) {
                        return SQLGenerator.convertFromObject(application, fColumnConverter, fColumnConverterInfo, dataProviderID, fDataProviderType, value, false);
                    }
                }, new ITypeConverter() {

                    @Override
                    public Object getAsRightType(int type, int flags, Object obj, int l, boolean throwOnFail) {
                        return Column.getAsRightType(type, flags, obj, l, throwOnFail, false);
                    }

                    @Override
                    public Object getAsRightType(int type, int flags, Object obj, String format, int l, boolean throwOnFail) {
                        return Column.getAsRightType(type, flags, obj, format, l, null, throwOnFail, false);
                    }
                }, table.getRowIdentColumns().get(0), Debug.LOGGER);
            }
            if (or != null) {
                ISQLCondition condition;
                if (c instanceof AggregateVariable) {
                    condition = createExistsCondition(application.getFlattenedSolution(), sqlSelect, or, rfs.getRelations(), columnTable, provider, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]));
                } else {
                    condition = or;
                }
                and = AndCondition.and(and, condition);
            }
        }
    }
    return and;
}

Example 14 with BaseQueryTable

use of com.servoy.base.query.BaseQueryTable in project servoy-client by Servoy.

the class SQLGenerator method createAggregateSelect.

public static QuerySelect createAggregateSelect(QuerySelect sqlSelect, Collection<QuerySelect> aggregates, List<Column> pkColumns) {
    QuerySelect selectClone = deepClone(sqlSelect);
    selectClone.clearSorts();
    selectClone.setDistinct(false);
    selectClone.setColumns(null);
    selectClone.removeUnusedJoins(true);
    QuerySelect aggregateSqlSelect;
    if (selectClone.getJoins() == null) {
        // simple case, no joins
        // Select count(pk) from main where <condition>
        aggregateSqlSelect = selectClone;
    } else {
        // we have joins, change to an exists-query to make the aggregates correct, otherwise duplicate records
        // in the main table cause incorrect aggregate values
        // Select count(pk) from main main1 where exists (select 1 from main main2 join detail on detail.FK = main2.FK where main1.PK = main2.PK and <condition>)
        QuerySelect innerSelect = selectClone;
        ArrayList<IQuerySelectValue> innerColumns = new ArrayList<IQuerySelectValue>();
        innerColumns.add(new QueryColumnValue(Integer.valueOf(1), null, true));
        innerSelect.setColumns(innerColumns);
        BaseQueryTable innerTable = innerSelect.getTable();
        QueryTable outerTable = new QueryTable(innerTable.getName(), innerTable.getDataSource(), innerTable.getCatalogName(), innerTable.getSchemaName());
        aggregateSqlSelect = new QuerySelect(outerTable);
        for (Column column : pkColumns) {
            // $NON-NLS-1$
            innerSelect.addCondition(// $NON-NLS-1$
            "EXISTS", new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, column.queryColumn(innerTable), column.queryColumn(outerTable)));
        }
        // $NON-NLS-1$
        aggregateSqlSelect.addCondition("EXISTS", new ExistsCondition(innerSelect, true));
    }
    ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
    for (QuerySelect aggregate : aggregates) {
        columns.addAll(AbstractBaseQuery.relinkTable(aggregate.getTable(), aggregateSqlSelect.getTable(), aggregate.getColumnsClone()));
    }
    aggregateSqlSelect.setColumns(columns);
    return aggregateSqlSelect;
}

Example 15 with BaseQueryTable

use of com.servoy.base.query.BaseQueryTable in project servoy-client by Servoy.

the class ViewFoundSet method doSave.

int doSave(ViewRecord record) {
    int retCode = ISaveConstants.STOPPED;
    List<ViewRecord> toSave = new ArrayList<>();
    if (record == null) {
        toSave.addAll(editedRecords);
    } else {
        if (record.isEditing())
            toSave.add(record);
    }
    if (toSave.size() > 0) {
        ArrayList<ViewRecord> processedRecords = new ArrayList<ViewRecord>();
        try {
            boolean previousRefresh = refresh;
            String serverName = DataSourceUtils.getDataSourceServerName(select.getTable().getDataSource());
            String transaction_id = manager.getTransactionID(serverName);
            HashMap<SQLStatement, ViewRecord> statementToRecord = new HashMap<>();
            List<SQLStatement> statements = new ArrayList<>();
            for (ViewRecord rec : toSave) {
                Map<String, Object> changes = rec.getChanges();
                // directly just remove it from the edited records if we try to save it.
                editedRecords.remove(rec);
                if (changes == null)
                    continue;
                Map<BaseQueryTable, Map<QueryColumn, Object>> tableToChanges = new IdentityHashMap<>();
                columnNames.forEach((selectValue, name) -> {
                    if (changes.containsKey(name)) {
                        QueryColumn realColumn = select.getRealColumn(selectValue).orElseThrow(() -> {
                            RuntimeException ex = new RuntimeException("Can't save " + rec + " for changed values " + changes + " because table for column '" + name + "' cannot be found");
                            rec.setLastException(ex);
                            if (!failedRecords.contains(rec))
                                failedRecords.add(rec);
                            return ex;
                        });
                        BaseQueryTable table = realColumn.getTable();
                        Map<QueryColumn, Object> map = tableToChanges.get(table);
                        if (map == null) {
                            map = new HashMap<>();
                            tableToChanges.put(table, map);
                        }
                        map.put(realColumn, rec.getValue(name));
                    }
                });
                tableToChanges.forEach((table, changesMap) -> {
                    List<IQuerySelectValue> pkColumns = pkColumnsForTable.get(table);
                    if (pkColumns == null) {
                        RuntimeException ex = new RuntimeException("Can't save " + rec + " for changed values " + changes + " because there are no pk's found for table with changes " + table.getAlias() != null ? table.getAlias() : table.getName());
                        rec.setLastException(ex);
                        if (!failedRecords.contains(rec))
                            failedRecords.add(rec);
                        throw ex;
                    }
                    int counter = 0;
                    Object[] pk = new Object[pkColumns.size()];
                    QueryUpdate update = new QueryUpdate(table);
                    IQuerySelectValue[] queryPks = null;
                    try {
                        RowManager rowManager = manager.getRowManager(table.getDataSource());
                        if (rowManager != null) {
                            queryPks = getOrderedPkColumns(pkColumns, rowManager.getSQLSheet().getPKColumnDataProvidersAsArray());
                        }
                    } catch (Exception ex) {
                        Debug.error(ex);
                    }
                    if (queryPks == null)
                        queryPks = pkColumns.toArray(new IQuerySelectValue[0]);
                    for (IQuerySelectValue pkColumn : queryPks) {
                        Object pkValue = rec.getValue(columnNames.get(pkColumn));
                        update.addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkColumn, pkValue));
                        pk[counter++] = pkValue;
                    }
                    IDataSet pks = new BufferedDataSet();
                    pks.addRow(pk);
                    counter = 0;
                    String[] changedColumns = new String[changes.size()];
                    for (Entry<QueryColumn, Object> entry : changesMap.entrySet()) {
                        QueryColumn column = entry.getKey();
                        update.addValue(column, entry.getValue());
                        changedColumns[counter++] = column.getName();
                    }
                    SQLStatement statement = new SQLStatement(ISQLActionTypes.UPDATE_ACTION, serverName, table.getName(), pks, transaction_id, update, manager.getTableFilterParams(serverName, update));
                    statement.setChangedColumns(changedColumns);
                    statement.setExpectedUpdateCount(1);
                    statements.add(statement);
                    statementToRecord.put(statement, rec);
                });
                JSRecordMarkers validateObject = validate(rec);
                if (validateObject != null && validateObject.isHasErrors()) {
                    Object[] genericExceptions = validateObject.getGenericExceptions();
                    if (genericExceptions.length > 0) {
                        rec.setLastException((Exception) genericExceptions[0]);
                    }
                    if (!failedRecords.contains(rec)) {
                        failedRecords.add(rec);
                        retCode = ISaveConstants.SAVE_FAILED;
                    }
                }
                if (!failedRecords.contains(rec)) {
                    processedRecords.add(rec);
                }
            }
            if (// if this is a save all call we don't save if we have failed records
            toSave.size() > 1 && failedRecords.isEmpty() || // if this is a single record save, we just check if it is failed or not
            toSave.size() == 1 && !failedRecords.contains(record)) {
                Object[] updateResult = manager.getApplication().getDataServer().performUpdates(manager.getApplication().getClientID(), statements.toArray(new SQLStatement[statements.size()]));
                for (int i = 0; i < updateResult.length; i++) {
                    // i of the updateResults should be the same for the statements;
                    ViewRecord rec = statementToRecord.remove(statements.get(i));
                    Object o = updateResult[i];
                    if (o instanceof Exception) {
                        // something went wrong
                        failedRecords.add(rec);
                        rec.setLastException((Exception) o);
                        retCode = ISaveConstants.SAVE_FAILED;
                    } else if (!statementToRecord.values().contains(rec) && !failedRecords.contains(rec)) {
                        rec.clearChanges();
                    }
                }
                for (SQLStatement statement : statements) {
                    manager.notifyDataChange(DataSourceUtils.createDBTableDataSource(statement.getServerName(), statement.getTableName()), statement.getPKsRow(0), ISQLActionTypes.UPDATE_ACTION, statement.getChangedColumns());
                }
                // do a load but only if there are listeners
                if (previousRefresh && shouldRefresh() && foundSetEventListeners.size() > 0) {
                    loadAllRecordsImpl();
                }
            }
        } catch (ServoyException | RemoteException e) {
            Debug.error(e);
        } finally {
            if (!failedRecords.isEmpty()) {
                processedRecords.stream().forEachOrdered(editedRecords::add);
            }
        }
    }
    return retCode;
}