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

Example 1 with QueryJoin

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

the class FlattenedSolution method getValuelistSortRelation.

/**
 * Get the internal relation that can be used to sort on this value list using the display values.
 * @param valueList
 * @param callingTable
 * @param dataProviderID
 * @param foundSetManager
 * @return
 * @throws RepositoryException
 */
public Relation getValuelistSortRelation(ValueList valueList, Table callingTable, String dataProviderID, IFoundSetManagerInternal foundSetManager) throws RepositoryException {
    if (callingTable == null || valueList == null) {
        return null;
    }
    String destDataSource;
    Relation[] relationSequence;
    String relationPrefix;
    switch(valueList.getDatabaseValuesType()) {
        case IValueListConstants.TABLE_VALUES:
            // create an internal relation
            relationSequence = null;
            // $NON-NLS-1$
            relationPrefix = "";
            destDataSource = valueList.getDataSource();
            break;
        case IValueListConstants.RELATED_VALUES:
            // replace the last relation in the sequence with an internal relation
            relationSequence = getRelationSequence(valueList.getRelationName());
            if (relationSequence == null) {
                return null;
            }
            if (relationSequence.length > 1) {
                for (Relation r : relationSequence) {
                    if (r.getJoinType() != INNER_JOIN) {
                        // outer join on the intermediate tables causes extra results that influence the sorting result
                        return null;
                    }
                }
            }
            StringBuilder sb = new StringBuilder();
            for (Relation r : relationSequence) {
                sb.append('-').append(r.getName());
            }
            relationPrefix = sb.toString();
            destDataSource = relationSequence[relationSequence.length - 1].getForeignDataSource();
            break;
        default:
            return null;
    }
    if (destDataSource == null || !DataSourceUtils.isSameServer(callingTable.getDataSource(), destDataSource)) {
        // do not create a cross-server relation
        return null;
    }
    Table destTable = (Table) foundSetManager.getTable(destDataSource);
    if (destTable == null) {
        return null;
    }
    String relationName = // $NON-NLS-1$
    Relation.INTERNAL_PREFIX + "VL-" + callingTable.getDataSource() + '-' + dataProviderID + relationPrefix + '-' + valueList.getName() + '-';
    synchronized (this) {
        Column callingColumn = callingTable.getColumn(dataProviderID);
        if (callingColumn == null) {
            return null;
        }
        Relation relation = getRelation(relationName);
        if (relation == null) {
            // create in internal relation
            String dp;
            int returnValues = valueList.getReturnDataProviders();
            if ((returnValues & 1) != 0) {
                dp = valueList.getDataProviderID1();
            } else if ((returnValues & 2) != 0) {
                dp = valueList.getDataProviderID2();
            } else if ((returnValues & 4) != 0) {
                dp = valueList.getDataProviderID3();
            } else {
                return null;
            }
            Column destColumn = destTable.getColumn(dp);
            if (destColumn == null) {
                return null;
            }
            // create internal value list relation
            QueryTable callingQTable = new QueryTable(callingTable.getSQLName(), callingTable.getDataSource(), callingTable.getCatalog(), callingTable.getSchema());
            QueryTable destQTable = new QueryTable(destTable.getSQLName(), destTable.getDataSource(), destTable.getCatalog(), destTable.getSchema());
            List<ISQLTableJoin> joins = new ArrayList<ISQLTableJoin>();
            ISQLTableJoin lastJoin = null;
            if (relationSequence == null) {
                // table values
                joins.add(lastJoin = new QueryJoin(relationName, callingQTable, destQTable, new AndCondition(), LEFT_OUTER_JOIN, false));
                if (// apply name as filter on column valuelist_name
                valueList.getUseTableFilter()) {
                    lastJoin.getCondition().addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, new QueryColumn(destQTable, DBValueList.NAME_COLUMN), valueList.getName()));
                }
            } else {
                // related values
                QueryTable primaryQTable = callingQTable;
                for (int i = 0; i < relationSequence.length; i++) {
                    Relation r = relationSequence[i];
                    QueryTable foreignQTable;
                    if (i == relationSequence.length - 1) {
                        // last one
                        foreignQTable = destQTable;
                    } else {
                        ITable relForeignTable = getTable(r.getForeignDataSource());
                        if (relForeignTable == null) {
                            return null;
                        }
                        foreignQTable = new QueryTable(relForeignTable.getSQLName(), relForeignTable.getDataSource(), relForeignTable.getCatalog(), relForeignTable.getSchema());
                    }
                    lastJoin = SQLGenerator.createJoin(this, r, primaryQTable, foreignQTable, false, new IGlobalValueEntry() {

                        public Object setDataProviderValue(String dpid, Object value) {
                            return null;
                        }

                        public Object getDataProviderValue(String dpid) {
                            // A value will be added when the relation is used, see SQLGenerator.createJoin
                            return new Placeholder(new ObjectPlaceholderKey<int[]>(null, dpid));
                        }

                        public boolean containsDataProvider(String dpid) {
                            return false;
                        }
                    });
                    joins.add(lastJoin);
                    primaryQTable = foreignQTable;
                }
            }
            // add condition for return dp id
            lastJoin.getCondition().addCondition(new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, destColumn.queryColumn(destQTable), callingColumn.queryColumn(callingQTable)));
            relation = getSolutionCopy().createNewRelation(new ScriptNameValidator(this), relationName, callingTable.getDataSource(), destDataSource, LEFT_OUTER_JOIN);
            ISQLTableJoin join;
            if (joins.size() == 1) {
                join = lastJoin;
            } else {
                // combine joins
                join = new QueryCompositeJoin(relationName, joins);
            }
            relation.setRuntimeProperty(Relation.RELATION_JOIN, join);
        }
        return relation;
    }
}

Example 2 with QueryJoin

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

the class QBJoins method add.

/**
 * @clonedesc com.servoy.j2db.querybuilder.IQueryBuilderJoins#add(String, int, String)
 * @sampleas add(String, int)
 *
 * @param dataSource data source
 * @param joinType join type, one of {@link IQueryBuilderJoin#LEFT_OUTER_JOIN}, {@link IQueryBuilderJoin#INNER_JOIN}, {@link IQueryBuilderJoin#RIGHT_OUTER_JOIN}, {@link IQueryBuilderJoin#FULL_JOIN}
 * @param alias the alias for joining table
 */
@JSFunction
public QBJoin add(String dataSource, int joinType, String alias) {
    String name;
    QBJoin join;
    if (alias == null) {
        name = UUID.randomUUID().toString();
        join = null;
    } else {
        name = alias;
        join = getJoin(name);
    }
    if (join == null) {
        Table foreignTable = root.getTable(dataSource);
        join = addJoin(new QueryJoin(name, parent.getQueryTable(), new TableExpression(new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema(), alias)), new AndCondition(), joinType, true), dataSource, name);
    }
    return join;
}

Example 3 with QueryJoin

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

the class FoundSet method findQueryRelationSequence.

private List<Relation> findQueryRelationSequence(QuerySelect sqlSelect, BaseQueryTable qTable) {
    if (sqlSelect.getTable() == qTable) {
        // column on base table, not related
        return Collections.emptyList();
    }
    // find the join to this table
    for (ISQLJoin join : iterate(sqlSelect.getJoins())) {
        if (join.getName() != null && join instanceof QueryJoin && ((QueryJoin) join).getForeignTable() == qTable) {
            Relation relation = fsm.getRelation(join.getName());
            if (relation != null && relation.getForeignDataSource().equals(qTable.getDataSource())) {
                List<Relation> subRelated = findQueryRelationSequence(sqlSelect, ((QueryJoin) join).getPrimaryTable());
                if (subRelated != null) {
                    // found matching relation sequence
                    List<Relation> relationSequence = new ArrayList<>(subRelated);
                    relationSequence.add(relation);
                    return relationSequence;
                }
            }
        }
    }
    // not found
    return null;
}

Example 4 with QueryJoin

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

Example 5 with QueryJoin

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