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;
}
}
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));
}
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;
}
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;
}
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);
}
Aggregations