Search in sources :

Example 1 with IVisitor

use of com.servoy.j2db.util.visitor.IVisitor in project servoy-client by Servoy.

the class QueryBuilderSerializer method unmarshall.

public Object unmarshall(SerializerState state, Class clazz, Object o) throws UnmarshallException {
    final QBFactory queryBuilderFactory = queryBuilderFactoryProvider.getQueryBuilderFactory();
    if (queryBuilderFactory == null) {
        throw new UnmarshallException("QueryBuilderSerializer needs query builder factory");
    }
    JSONObject jso = (JSONObject) o;
    String xml;
    String dataSource;
    String alias;
    try {
        // required
        xml = jso.getString("query");
        // required
        dataSource = jso.getString("datasource");
        // optional
        alias = jso.optString("alias", null);
    } catch (JSONException e) {
        throw new UnmarshallException("Could not get the query in QueryBuilderSerializer", e);
    }
    if (jso.has("javaClass")) {
        try {
            clazz = Class.forName(jso.getString("javaClass"));
        } catch (ClassNotFoundException e) {
            throw new UnmarshallException(e.getMessage(), e);
        } catch (JSONException e) {
            throw new UnmarshallException("Could not find javaClass", e);
        }
    }
    Object returnValue = null;
    if (QBSelect.class.equals(clazz)) {
        final String queryDataSource = dataSource;
        QuerySelect query = (QuerySelect) getXstream().fromXML(xml);
        // If the xml was from before adding dataSource to QueryTable, the query will contain QueryTable1 objects, these need to be replaced
        // with QueryTable with resolved dataSource.
        query.acceptVisitor(new VisitOnceDelegateVisitor(new IVisitor() {

            public Object visit(Object obj) {
                if (obj instanceof QueryTable1) {
                    return ((QueryTable1) obj).addDataSource(queryBuilderFactory.resolveDataSource(queryDataSource, ((QueryTable1) obj).getName()));
                }
                return obj;
            }
        }));
        returnValue = queryBuilderFactory.createSelect(dataSource, alias, query);
    }
    if (returnValue == null) {
        throw new UnmarshallException("invalid class " + clazz);
    }
    state.setSerialized(o, returnValue);
    return returnValue;
}
Also used : QBFactory(com.servoy.j2db.querybuilder.impl.QBFactory) JSONObject(org.json.JSONObject) IVisitor(com.servoy.j2db.util.visitor.IVisitor) JSONException(org.json.JSONException) VisitOnceDelegateVisitor(com.servoy.j2db.util.visitor.VisitOnceDelegateVisitor) JSONObject(org.json.JSONObject) QueryTable1(com.servoy.j2db.query.QueryTable1) UnmarshallException(org.jabsorb.serializer.UnmarshallException) QuerySelect(com.servoy.j2db.query.QuerySelect)

Example 2 with IVisitor

use of com.servoy.j2db.util.visitor.IVisitor in project servoy-client by Servoy.

the class FoundSetManager method hasTableFiltersWithJoins.

/**
 * Check if table filters for the query are defined that have joins.
 *
 * @param serverName
 * @param sql
 * @return
 */
public boolean hasTableFiltersWithJoins(String serverName, IQueryElement sql) {
    final List<TableFilter> serverFilters = tableFilterParams.get(serverName);
    if (serverFilters == null) {
        return false;
    }
    // get the sql table names in the query
    // find the filters for the tables found in the query
    final AtomicBoolean hasTableFiltersWithJoins = new AtomicBoolean(false);
    sql.acceptVisitor(new IVisitor() {

        private final Set<String> tableSqlNames = new HashSet<String>();

        public Object visit(Object o) {
            if (o instanceof QueryTable && ((QueryTable) o).getName() != null && tableSqlNames.add(((QueryTable) o).getName())) {
                QueryTable qTable = (QueryTable) o;
                for (TableFilter filter : serverFilters) {
                    if (Utils.stringSafeEquals(filter.getTableSQLName(), qTable.getName())) {
                        if (filter.getTableFilterdefinition() instanceof QueryTableFilterdefinition) {
                            List<ISQLJoin> joins = ((QueryTableFilterdefinition) filter.getTableFilterdefinition()).getQuerySelect().getJoins();
                            if (joins != null && !joins.isEmpty()) {
                                hasTableFiltersWithJoins.set(true);
                                return new VisitorResult(o, false);
                            }
                        }
                    }
                }
            }
            return o;
        }
    });
    return hasTableFiltersWithJoins.get();
}
Also used : AtomicBoolean(java.util.concurrent.atomic.AtomicBoolean) IVisitor(com.servoy.j2db.util.visitor.IVisitor) ServoyJSONObject(com.servoy.j2db.util.ServoyJSONObject) Arrays.asList(java.util.Arrays.asList) CopyOnWriteArrayList(java.util.concurrent.CopyOnWriteArrayList) ArrayList(java.util.ArrayList) List(java.util.List) SortedList(com.servoy.j2db.util.SortedList) Collectors.toList(java.util.stream.Collectors.toList) BaseQueryTable(com.servoy.base.query.BaseQueryTable) QueryTable(com.servoy.j2db.query.QueryTable) HashSet(java.util.HashSet)

Example 3 with IVisitor

use of com.servoy.j2db.util.visitor.IVisitor in project servoy-client by Servoy.

the class SQLGenerator method getPKSelectSqlSelect.

/*
 * _____________________________________________________________ The methods below belong to this class
 */
// SQL pk(s) select for foundset,concatenating those strings will always deliver a executable SQL
// Note: removeUnusedJoins must be false when the resulting query is changed afterwards (like adding columns)
QuerySelect getPKSelectSqlSelect(IGlobalValueEntry provider, Table table, QuerySelect oldSQLQuery, List<IRecordInternal> findStates, boolean reduce, IDataSet omitPKs, List<SortColumn> orderByFields, boolean removeUnusedJoins) throws ServoyException {
    if (table == null) {
        throw new RepositoryException(ServoyException.InternalCodes.TABLE_NOT_FOUND);
    }
    QuerySelect retval;
    if (oldSQLQuery != null) {
        retval = deepClone(oldSQLQuery);
        retval.setGroupBy(null);
        // will be generated based on foundset sorting
        if (orderByFields != null)
            retval.clearSorts();
        // remove all servoy conditions, except filter, search and relation
        for (String conditionName : retval.getConditionNames()) {
            if (conditionName.startsWith(SERVOY_CONDITION_PREFIX) && !(CONDITION_FILTER.equals(conditionName) || CONDITION_SEARCH.equals(conditionName) || CONDITION_RELATION.equals(conditionName))) {
                retval.setCondition(conditionName, null);
            }
        }
    } else {
        retval = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    }
    // Example:-select pk1,pk2 from tablename1 where ((fieldname1 like '%abcd%') or ((fieldname2 like '%xyz%')) (retrieve max 200 rows)
    ArrayList<IQuerySelectValue> pkQueryColumns = new ArrayList<IQuerySelectValue>(3);
    ArrayList<Column> pkColumns = new ArrayList<Column>(3);
    // getPrimaryKeys from table
    Iterator<Column> pks = table.getRowIdentColumns().iterator();
    // make select
    if (!pks.hasNext()) {
        throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { table.getName() });
    }
    while (pks.hasNext()) {
        Column column = pks.next();
        pkColumns.add(column);
        pkQueryColumns.add(column.queryColumn(retval.getTable()));
    }
    retval.setColumns(pkQueryColumns);
    if (omitPKs != null && omitPKs.getRowCount() != 0) {
        // omit is rebuild each time
        retval.setCondition(CONDITION_OMIT, createSetConditionFromPKs(IBaseSQLCondition.NOT_OPERATOR, pkQueryColumns.toArray(new QueryColumn[pkQueryColumns.size()]), pkColumns, omitPKs));
    } else if (oldSQLQuery != null) {
        retval.setCondition(CONDITION_OMIT, oldSQLQuery.getConditionClone(CONDITION_OMIT));
    }
    if (// new
    findStates != null && findStates.size() != 0) {
        ISQLCondition moreWhere = null;
        for (IRecordInternal obj : findStates) {
            if (obj instanceof FindState) {
                moreWhere = OrCondition.or(moreWhere, createConditionFromFindState((FindState) obj, retval, provider, pkQueryColumns));
            }
        }
        if (moreWhere != null) {
            if (reduce) {
                retval.addCondition(CONDITION_SEARCH, moreWhere);
            } else {
                retval.addConditionOr(CONDITION_SEARCH, moreWhere);
            }
            if (retval.getJoins() != null) {
                // check if the search condition has an or-condition
                final boolean[] hasOr = { false };
                retval.getCondition(CONDITION_SEARCH).acceptVisitor(new IVisitor() {

                    public Object visit(Object o) {
                        if (o instanceof OrCondition && ((OrCondition) o).getConditions().size() > 1) {
                            hasOr[0] = true;
                            return new VisitorResult(o, false);
                        }
                        return o;
                    }
                });
                if (hasOr[0]) {
                    // override join type to left outer join, a related OR-search should not make the result set smaller
                    for (ISQLJoin join : retval.getJoins()) {
                        if (join instanceof QueryJoin && ((QueryJoin) join).getJoinType() == IQueryConstants.INNER_JOIN) {
                            ((QueryJoin) join).setJoinType(IQueryConstants.LEFT_OUTER_JOIN);
                        }
                    }
                }
            }
        }
    }
    // make orderby
    if (orderByFields != null || retval.getSorts() == null) {
        List<SortColumn> orderBy = orderByFields == null ? new ArrayList<SortColumn>(3) : orderByFields;
        if (orderBy.size() == 0) {
            for (Column pkColumn : pkColumns) {
                orderBy.add(new SortColumn(pkColumn));
            }
        }
        addSorts(retval, retval.getTable(), provider, table, orderBy, true, false);
    }
    if (removeUnusedJoins) {
        // remove unneeded joins, some may have been added because of a previous sort and are no longer needed.
        retval.removeUnusedJoins(false);
    }
    // 1 do not remove sort or groupby test, will cause invalid queries
    // 1 this one causes error and can not be fixed,
    // 1 if (joinswherepart.length() != 0 && !sortIsRelated && groupbyKeyword == STRING_EMPTY && table.getPrimaryKeyCount() == 1)
    // 1 sql select distinct(s_contacts.contactsid) from s_contacts,s_companies where s_contacts.company_id = s_companies.company_id order by s_contacts.surname  ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list
    // retval may have set distinct and plainPKSelect flag based on previous sort columns, make sure to reset first
    retval.setDistinct(false);
    retval.setPlainPKSelect(false);
    if (// if joined pks comes back multiple times
    retval.getJoins() != null && retval.getColumns().size() == 1 && isDistinctAllowed(retval.getColumns(), retval.getSorts())) {
        retval.setDistinct(true);
    } else if (// plain pk select
    retval.getJoins() == null && retval.getColumns().size() == pkColumns.size()) {
        retval.setPlainPKSelect(true);
    }
    return retval;
}
Also used : IVisitor(com.servoy.j2db.util.visitor.IVisitor) ArrayList(java.util.ArrayList) SafeArrayList(com.servoy.j2db.util.SafeArrayList) QueryJoin(com.servoy.j2db.query.QueryJoin) RepositoryException(com.servoy.j2db.persistence.RepositoryException) ISQLJoin(com.servoy.j2db.query.ISQLJoin) QuerySelect(com.servoy.j2db.query.QuerySelect) ISQLCondition(com.servoy.j2db.query.ISQLCondition) BaseQueryTable(com.servoy.base.query.BaseQueryTable) QueryTable(com.servoy.j2db.query.QueryTable) RelatedFindState(com.servoy.j2db.dataprocessing.FindState.RelatedFindState) QueryColumn(com.servoy.j2db.query.QueryColumn) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) IColumn(com.servoy.j2db.persistence.IColumn) Column(com.servoy.j2db.persistence.Column) OrCondition(com.servoy.j2db.query.OrCondition) IQuerySelectValue(com.servoy.j2db.query.IQuerySelectValue)

Example 4 with IVisitor

use of com.servoy.j2db.util.visitor.IVisitor 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);
                }
            }
        }
    }
}
Also used : BaseQueryTable(com.servoy.base.query.BaseQueryTable) IVisitor(com.servoy.j2db.util.visitor.IVisitor) HashSet(java.util.HashSet)

Example 5 with IVisitor

use of com.servoy.j2db.util.visitor.IVisitor in project servoy-client by Servoy.

the class SQLGenerator method createJoin.

/**
 * Join clause for this relation.
 */
public static ISQLTableJoin createJoin(IDataProviderHandler flattenedSolution, IRelation relation, BaseQueryTable primaryTable, BaseQueryTable foreignTable, boolean permanentJoin, final IGlobalValueEntry provider) throws RepositoryException {
    if (relation instanceof AbstractBase) {
        ISQLTableJoin queryJoin = ((AbstractBase) relation).getRuntimeProperty(Relation.RELATION_JOIN);
        if (queryJoin != null) {
            // a query join was defined for this relation, just relink the tables for the first and last in the joins
            queryJoin = deepClone(queryJoin);
            queryJoin = AbstractBaseQuery.relinkTable(queryJoin.getPrimaryTable(), primaryTable, queryJoin);
            queryJoin = AbstractBaseQuery.relinkTable(queryJoin.getForeignTable(), foreignTable, queryJoin);
            // update the placeholders for globals
            queryJoin.acceptVisitor(new IVisitor() {

                public Object visit(Object o) {
                    if (o instanceof Placeholder && ((Placeholder) o).getKey() instanceof ObjectPlaceholderKey) {
                        Object value = provider.getDataProviderValue(((ObjectPlaceholderKey<int[]>) ((Placeholder) o).getKey()).getName());
                        int[] args = ((ObjectPlaceholderKey<int[]>) ((Placeholder) o).getKey()).getObject();
                        int dataProviderType = args[0];
                        int flags = args[1];
                        if (value == null) {
                            return ValueFactory.createNullValue(dataProviderType);
                        }
                        return Column.getAsRightType(dataProviderType, flags, value, Integer.MAX_VALUE, false, false);
                    }
                    return o;
                }
            });
            return queryJoin;
        }
    }
    // build a join from the relation items
    IDataProvider[] primary = relation.getPrimaryDataProviders(flattenedSolution);
    Column[] foreign = relation.getForeignColumns(flattenedSolution);
    int[] operators = relation.getOperators();
    AndCondition joinCondition = new AndCondition();
    for (int x = 0; x < primary.length; x++) {
        Column primaryColumn = null;
        // check if stored script calc or table column
        if (primary[x] instanceof ScriptCalculation) {
            ScriptCalculation sc = ((ScriptCalculation) primary[x]);
            // null when not stored
            primaryColumn = sc.getTable().getColumn(sc.getName());
        } else if (primary[x] instanceof Column) {
            primaryColumn = (Column) primary[x];
        }
        QueryColumn foreignColumn = foreign[x].queryColumn(foreignTable);
        Object value;
        if (primaryColumn == null) {
            if (primary[x] instanceof LiteralDataprovider) {
                value = ((LiteralDataprovider) primary[x]).getValue();
                value = foreign[x].getAsRightType(value);
            } else {
                value = provider.getDataProviderValue(primary[x].getDataProviderID());
                if (value == null) {
                    value = ValueFactory.createNullValue(primary[x].getDataProviderType());
                } else if (value instanceof Placeholder) {
                    if (((Placeholder) value).getKey() instanceof ObjectPlaceholderKey<?>) {
                        ((ObjectPlaceholderKey) ((Placeholder) value).getKey()).setObject(new int[] { primary[x].getDataProviderType(), primary[x].getFlags() });
                    }
                } else {
                    value = Column.getAsRightType(primary[x].getDataProviderType(), primary[x].getFlags(), value, Integer.MAX_VALUE, false, false);
                }
            }
        } else // table type, can be stored calc
        {
            value = primaryColumn.queryColumn(primaryTable);
        }
        // all operators are swappable because only relation operators in RelationItem.RELATION_OPERATORS can be defined.
        // NOTE: elements in joinCondition MUST be CompareConditions (expected in QueryGenerator and SQLGenerator.createConditionFromFindState)
        joinCondition.addCondition(new CompareCondition(RelationItem.swapOperator(operators[x]), foreignColumn, value));
    }
    if (joinCondition.getConditions().size() == 0) {
        // $NON-NLS-1$
        throw new RepositoryException("Missing join condition in relation " + relation.getName());
    }
    return new QueryJoin(relation.getName(), primaryTable, foreignTable, joinCondition, relation.getJoinType(), permanentJoin);
}
Also used : Placeholder(com.servoy.j2db.query.Placeholder) IVisitor(com.servoy.j2db.util.visitor.IVisitor) AbstractBase(com.servoy.j2db.persistence.AbstractBase) QueryJoin(com.servoy.j2db.query.QueryJoin) RepositoryException(com.servoy.j2db.persistence.RepositoryException) IDataProvider(com.servoy.j2db.persistence.IDataProvider) LiteralDataprovider(com.servoy.j2db.persistence.LiteralDataprovider) AndCondition(com.servoy.j2db.query.AndCondition) ScriptCalculation(com.servoy.j2db.persistence.ScriptCalculation) ISQLTableJoin(com.servoy.j2db.query.ISQLTableJoin) QueryColumn(com.servoy.j2db.query.QueryColumn) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) IColumn(com.servoy.j2db.persistence.IColumn) Column(com.servoy.j2db.persistence.Column) QueryColumn(com.servoy.j2db.query.QueryColumn) BaseQueryColumn(com.servoy.base.query.BaseQueryColumn) CompareCondition(com.servoy.j2db.query.CompareCondition) ObjectPlaceholderKey(com.servoy.j2db.query.ObjectPlaceholderKey)

Aggregations

IVisitor (com.servoy.j2db.util.visitor.IVisitor)6 BaseQueryTable (com.servoy.base.query.BaseQueryTable)3 QuerySelect (com.servoy.j2db.query.QuerySelect)3 ArrayList (java.util.ArrayList)3 BaseQueryColumn (com.servoy.base.query.BaseQueryColumn)2 Column (com.servoy.j2db.persistence.Column)2 IColumn (com.servoy.j2db.persistence.IColumn)2 RepositoryException (com.servoy.j2db.persistence.RepositoryException)2 Placeholder (com.servoy.j2db.query.Placeholder)2 QueryColumn (com.servoy.j2db.query.QueryColumn)2 QueryJoin (com.servoy.j2db.query.QueryJoin)2 QueryTable (com.servoy.j2db.query.QueryTable)2 HashSet (java.util.HashSet)2 RelatedFindState (com.servoy.j2db.dataprocessing.FindState.RelatedFindState)1 DbIdentValue (com.servoy.j2db.dataprocessing.ValueFactory.DbIdentValue)1 AbstractBase (com.servoy.j2db.persistence.AbstractBase)1 IDataProvider (com.servoy.j2db.persistence.IDataProvider)1 LiteralDataprovider (com.servoy.j2db.persistence.LiteralDataprovider)1 ScriptCalculation (com.servoy.j2db.persistence.ScriptCalculation)1 AndCondition (com.servoy.j2db.query.AndCondition)1