use of com.servoy.j2db.query.IQuerySelectValue 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;
}
use of com.servoy.j2db.query.IQuerySelectValue in project servoy-client by Servoy.
the class SQLGenerator method createTableFiltercondition.
public static QueryFilter createTableFiltercondition(BaseQueryTable qTable, Table table, DataproviderTableFilterdefinition filterdefinition) {
Column c = table.getColumn(filterdefinition.getDataprovider());
if (c == null) {
// $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
Debug.error("Could not apply filter " + filterdefinition + " on table " + table + " : column not found:" + filterdefinition.getDataprovider());
return null;
}
int op = filterdefinition.getOperator();
int maskedOp = op & IBaseSQLCondition.OPERATOR_MASK;
Object value = filterdefinition.getValue();
QueryColumn qColumn = c.queryColumn(qTable);
ISQLCondition filterWhere;
if (maskedOp == IBaseSQLCondition.EQUALS_OPERATOR || maskedOp == IBaseSQLCondition.NOT_OPERATOR || maskedOp == IBaseSQLCondition.IN_OPERATOR || maskedOp == IBaseSQLCondition.NOT_IN_OPERATOR || maskedOp == IBaseSQLCondition.GT_OPERATOR || maskedOp == IBaseSQLCondition.LT_OPERATOR || maskedOp == IBaseSQLCondition.GTE_OPERATOR || maskedOp == IBaseSQLCondition.LTE_OPERATOR) {
Object inValues;
boolean andCondition = true;
if (value instanceof List<?>) {
inValues = new Object[][] { ((List<?>) value).toArray() };
andCondition = maskedOp != IBaseSQLCondition.NOT_OPERATOR && maskedOp != IBaseSQLCondition.NOT_IN_OPERATOR;
} else if (value != null && value.getClass().isArray()) {
inValues = new Object[][] { (Object[]) value };
andCondition = maskedOp != IBaseSQLCondition.NOT_OPERATOR && maskedOp != IBaseSQLCondition.NOT_IN_OPERATOR;
} else {
if (value != null && isSelectQuery(value.toString())) {
if ((op & IBaseSQLCondition.IS_SQL_MODIFIER) == 0) {
Debug.warn("Filter is created using a custom query without using the sql-modifier, this will be removed in a future version of servoy, please use operator '" + RelationItem.getOperatorAsString(op | IBaseSQLCondition.IS_SQL_MODIFIER) + "'");
}
// add as subquery
inValues = new QueryCustomSelect(value.toString(), null);
} else {
if (value != null && (op & IBaseSQLCondition.IS_SQL_MODIFIER) != 0) {
Debug.warn("Filter has the sql-modifier, but the value is not valid sql for filters: " + SQL_QUERY_VALIDATION_MESSAGE + ":" + value);
}
inValues = new Object[][] { new Object[] { value } };
}
}
// replace values with column type value
if (inValues instanceof Object[][]) {
Object[][] array = (Object[][]) inValues;
for (int i = 0; i < array.length; i++) {
for (int j = 0; array[i] != null && j < array[i].length; j++) {
Object v = c.getAsRightType(array[i][j]);
if (v == null)
v = ValueFactory.createNullValue(c.getType());
array[i][j] = v;
}
}
}
filterWhere = new SetCondition(op, new IQuerySelectValue[] { qColumn }, inValues, andCondition);
} else if (maskedOp == IBaseSQLCondition.BETWEEN_OPERATOR || maskedOp == IBaseSQLCondition.NOT_BETWEEN_OPERATOR) {
Object op1 = null;
Object op2 = null;
if (value instanceof List<?> && ((List<?>) value).size() > 1) {
op1 = ((List<?>) value).get(0);
op2 = ((List<?>) value).get(1);
} else if (value != null && value.getClass().isArray() && ((Object[]) value).length > 1) {
op1 = ((Object[]) value)[0];
op2 = ((Object[]) value)[1];
}
op1 = c.getAsRightType(op1);
if (op1 == null)
op1 = ValueFactory.createNullValue(c.getType());
op2 = c.getAsRightType(op2);
if (op2 == null)
op2 = ValueFactory.createNullValue(c.getType());
filterWhere = new CompareCondition(op, qColumn, new Object[] { op1, op2 });
} else {
Object operand;
if (maskedOp == IBaseSQLCondition.LIKE_OPERATOR || maskedOp == IBaseSQLCondition.NOT_LIKE_OPERATOR) {
operand = value;
} else {
operand = c.getAsRightType(value);
if (operand == null)
operand = ValueFactory.createNullValue(c.getType());
}
filterWhere = new CompareCondition(op, qColumn, operand);
}
return new QueryFilter(filterWhere);
}
use of com.servoy.j2db.query.IQuerySelectValue 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;
}
use of com.servoy.j2db.query.IQuerySelectValue 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;
}
use of com.servoy.j2db.query.IQuerySelectValue in project servoy-client by Servoy.
the class ViewFoundSet method getTable.
@Override
public ITable getTable() {
if (table == null) {
try {
table = manager.getTable(getDataSource());
if (table == null) {
table = new Table(IServer.VIEW_SERVER, DataSourceUtils.getViewDataSourceName(getDataSource()), true, ITable.VIEW, null, null);
((Table) table).setDataSource(getDataSource());
for (IQuerySelectValue col : select.getColumns()) {
Column newCol = null;
QueryColumn qCol = col.getColumn();
if (qCol != null && qCol.getTable() != null) {
ITable colTable = manager.getTable(qCol.getTable().getDataSource());
if (colTable != null) {
Column column = colTable.getColumn(qCol.getName());
if (column != null) {
String colname = getColunmName(col, qCol);
newCol = table.createNewColumn(DummyValidator.INSTANCE, colname, column.getType(), column.getLength(), column.getScale(), column.getAllowNull());
if (column.getColumnInfo() != null) {
DatabaseUtils.createNewColumnInfo(manager.getApplication().getFlattenedSolution().getPersistFactory().getNewElementID(null), newCol, false);
newCol.getColumnInfo().copyFrom(column.getColumnInfo());
}
}
}
}
if (newCol == null) {
// existing database column not found, create column on the fly
BaseColumnType columnType = col.getColumnType();
if (columnType == null) {
columnType = ColumnType.getColumnType(IColumnTypes.TEXT);
}
String colname = getColunmName(col, qCol);
table.createNewColumn(DummyValidator.INSTANCE, colname, columnType.getSqlType(), columnType.getLength(), columnType.getScale(), true);
}
}
}
} catch (RepositoryException e) {
Debug.error(e);
}
}
return table;
}
Aggregations