Search in sources :

Example 1 with AndCondition

use of com.servoy.j2db.query.AndCondition 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;
    }
}
Also used : Placeholder(com.servoy.j2db.query.Placeholder) Table(com.servoy.j2db.persistence.Table) QueryTable(com.servoy.j2db.query.QueryTable) ITable(com.servoy.j2db.persistence.ITable) ArrayList(java.util.ArrayList) QueryJoin(com.servoy.j2db.query.QueryJoin) QueryTable(com.servoy.j2db.query.QueryTable) AndCondition(com.servoy.j2db.query.AndCondition) Relation(com.servoy.j2db.persistence.Relation) IGlobalValueEntry(com.servoy.j2db.dataprocessing.IGlobalValueEntry) QueryCompositeJoin(com.servoy.j2db.query.QueryCompositeJoin) QueryColumn(com.servoy.j2db.query.QueryColumn) Column(com.servoy.j2db.persistence.Column) IColumn(com.servoy.j2db.persistence.IColumn) ISQLTableJoin(com.servoy.j2db.query.ISQLTableJoin) QueryColumn(com.servoy.j2db.query.QueryColumn) CompareCondition(com.servoy.j2db.query.CompareCondition) ITable(com.servoy.j2db.persistence.ITable) JSONObject(org.json.JSONObject) IRootObject(com.servoy.j2db.persistence.IRootObject) ScriptNameValidator(com.servoy.j2db.persistence.ScriptNameValidator)

Example 2 with AndCondition

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

the class QBWhereCondition method getCondition.

/**
 * Get a named condition in the query where-clause.
 *
 * @param name The condition name.
 *
 * @sampleas conditionnames()
 */
@JSFunction
public QBCondition getCondition(String name) {
    QuerySelect query = getRoot().getQuery(false);
    AndCondition condition = query == null ? null : query.getCondition(name);
    return condition == null ? null : new QBCondition(getRoot(), getParent(), AbstractBaseQuery.deepClone(condition));
}
Also used : QuerySelect(com.servoy.j2db.query.QuerySelect) AndCondition(com.servoy.j2db.query.AndCondition) JSFunction(org.mozilla.javascript.annotations.JSFunction)

Example 3 with AndCondition

use of com.servoy.j2db.query.AndCondition 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;
}
Also used : DerivedTable(com.servoy.j2db.query.DerivedTable) QueryTable(com.servoy.j2db.query.QueryTable) Table(com.servoy.j2db.persistence.Table) QueryJoin(com.servoy.j2db.query.QueryJoin) TableExpression(com.servoy.j2db.query.TableExpression) QueryTable(com.servoy.j2db.query.QueryTable) AndCondition(com.servoy.j2db.query.AndCondition) JSFunction(org.mozilla.javascript.annotations.JSFunction)

Example 4 with AndCondition

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

the class FoundSet method loadByQuery.

private boolean loadByQuery(QuerySelect sqlSelect) throws ServoyException {
    if (initialized && (getFoundSetManager().getEditRecordList().stopIfEditing(this) != ISaveConstants.STOPPED)) {
        fsm.getApplication().reportJSError(// $NON-NLS-1$
        "couldn't load dataset because foundset had edited records but couldn't save it: " + this + ", edited record(s): " + Utils.stringJoin(getFoundSetManager().getEditRecordList().getEditedRecords(this), '.') + ", failed record(s): " + Utils.stringJoin(getFoundSetManager().getEditRecordList().getFailedRecords(this), '.'), null);
        return false;
    }
    IDataSet pks = pksAndRecords.getPks();
    Object[] selectedPK = (pks != null && getSelectedIndex() >= 0 && getSelectedIndex() < pks.getRowCount()) ? pks.getRow(getSelectedIndex()) : null;
    int sizeBefore = getSize();
    if (sizeBefore > 1) {
        fireSelectionAdjusting();
    }
    clearOmit(sqlSelect);
    if (relationName != null) {
        AndCondition fsRelatedCondition = pksAndRecords.getQuerySelectForReading().getCondition(SQLGenerator.CONDITION_RELATION);
        AndCondition selectRelatedCondition = sqlSelect.getCondition(SQLGenerator.CONDITION_RELATION);
        if (selectRelatedCondition != null && !selectRelatedCondition.equals(fsRelatedCondition)) {
            // add the different relation condition as search
            sqlSelect.addCondition(SQLGenerator.CONDITION_SEARCH, selectRelatedCondition);
        }
        // Make sure the relation condition of this related foundset is left untouched
        sqlSelect.setCondition(SQLGenerator.CONDITION_RELATION, AbstractBaseQuery.relinkTable(pksAndRecords.getQuerySelectForReading().getTable(), sqlSelect.getTable(), fsRelatedCondition));
    }
    initialized = true;
    // do query with sqlSelect
    String transaction_id = fsm.getTransactionID(sheet);
    IDataSet pk_data;
    try {
        pk_data = performQuery(transaction_id, sqlSelect, getRowIdentColumnTypes(), 0, fsm.config.pkChunkSize(), IDataServer.CUSTOM_QUERY);
    } catch (RemoteException e) {
        clear();
        throw new RepositoryException(e);
    }
    if (pk_data.getRowCount() > 0 && pk_data.getColumnCount() != sheet.getPKIndexes().length)
        // $NON-NLS-1$
        throw new IllegalArgumentException(fsm.getApplication().getI18NMessage("servoy.foundSet.query.error.incorrectNumberOfPKS"));
    IFoundSetChanges changes = pksAndRecords.setPksAndQuery(pk_data, pk_data.getRowCount(), sqlSelect);
    clearInternalState(true);
    fireDifference(sizeBefore, getSize(), changes);
    // try to preserve selection after load by query; if not possible select first record
    if (selectedPK != null) {
        if (!selectRecord(selectedPK)) {
            setSelectedIndex(getSize() > 0 ? 0 : -1);
        }
    }
    return true;
}
Also used : RepositoryException(com.servoy.j2db.persistence.RepositoryException) RemoteException(java.rmi.RemoteException) AndCondition(com.servoy.j2db.query.AndCondition)

Example 5 with AndCondition

use of com.servoy.j2db.query.AndCondition 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

AndCondition (com.servoy.j2db.query.AndCondition)8 QueryJoin (com.servoy.j2db.query.QueryJoin)4 RepositoryException (com.servoy.j2db.persistence.RepositoryException)3 QuerySelect (com.servoy.j2db.query.QuerySelect)3 Column (com.servoy.j2db.persistence.Column)2 IColumn (com.servoy.j2db.persistence.IColumn)2 Table (com.servoy.j2db.persistence.Table)2 CompareCondition (com.servoy.j2db.query.CompareCondition)2 DerivedTable (com.servoy.j2db.query.DerivedTable)2 ISQLTableJoin (com.servoy.j2db.query.ISQLTableJoin)2 Placeholder (com.servoy.j2db.query.Placeholder)2 QueryColumn (com.servoy.j2db.query.QueryColumn)2 QueryTable (com.servoy.j2db.query.QueryTable)2 RemoteException (java.rmi.RemoteException)2 ArrayList (java.util.ArrayList)2 JSFunction (org.mozilla.javascript.annotations.JSFunction)2 BaseQueryColumn (com.servoy.base.query.BaseQueryColumn)1 IGlobalValueEntry (com.servoy.j2db.dataprocessing.IGlobalValueEntry)1 AbstractBase (com.servoy.j2db.persistence.AbstractBase)1 IDataProvider (com.servoy.j2db.persistence.IDataProvider)1