use of com.servoy.j2db.query.ISQLTableJoin in project servoy-client by Servoy.
the class PartNode method process.
public List<DataRendererDefinition> process(FormPreviewPanel fpp, FoundSet fs, Table table, QuerySelect sqlString) throws Exception {
// Selection model must be in print mode to be able to set the selection to -1 . Otherwise is not allowed by the selectionModel
((ISwingFoundSet) fs).getSelectionModel().hideSelectionForPrinting();
// this is needed because we must keep sql the same in foundset during printing
FoundSet rootSet = (FoundSet) fs.copy(false);
foundSets.add(rootSet);
IApplication app = fpp.getApplication();
// retval
List<DataRendererDefinition> list = new ArrayList<DataRendererDefinition>();
if (part != null && (part.getPartType() == Part.LEADING_SUBSUMMARY || part.getPartType() == Part.TRAILING_SUBSUMMARY || isLeadingAndTrailingSubsummary)) {
QuerySelect newSQLString = AbstractBaseQuery.deepClone(sqlString);
IDataServer server = app.getDataServer();
// build the sql parts based on sort columns
ArrayList<IQuerySelectValue> selectCols = new ArrayList<IQuerySelectValue>();
ArrayList<QueryColumn> groupbyCols = new ArrayList<QueryColumn>();
ArrayList<QuerySort> sortbyCols = new ArrayList<QuerySort>();
for (SortColumn element : sortColumns) {
BaseQueryTable queryTable = sqlString.getTable();
Relation[] relations = element.getRelations();
if (relations != null) {
for (Relation relation : relations) {
ISQLTableJoin join = (ISQLTableJoin) sqlString.getJoin(queryTable, relation.getName());
if (join == null) {
// $NON-NLS-1$ //$NON-NLS-2$
Debug.log("Missing relation " + relation.getName() + " in join condition for form on table " + table.getName());
} else {
queryTable = join.getForeignTable();
}
}
}
Column column = (Column) element.getColumn();
QueryColumn queryColumn = column.queryColumn(queryTable);
selectCols.add(queryColumn);
groupbyCols.add(queryColumn);
sortbyCols.add(new QuerySort(queryColumn, element.getSortOrder() == SortColumn.ASCENDING, fs.getFoundSetManager().getSortOptions(column)));
}
// make sql
for (AggregateVariable ag : allAggregates) {
selectCols.add(new QueryAggregate(ag.getType(), new QueryColumn(newSQLString.getTable(), -1, ag.getColumnNameToAggregate(), ag.getDataProviderType(), ag.getLength(), 0, null, ag.getFlags()), ag.getName()));
}
newSQLString.setColumns(selectCols);
newSQLString.setGroupBy(groupbyCols);
ArrayList<IQuerySort> oldSort = newSQLString.getSorts();
// fix the sort (if columns not are selected of used in groupby they cannot be used in sort)
newSQLString.setSorts(sortbyCols);
FoundSetManager foundSetManager = ((FoundSetManager) app.getFoundSetManager());
String transaction_id = foundSetManager.getTransactionID(table.getServerName());
IDataSet data = server.performQuery(app.getClientID(), table.getServerName(), transaction_id, newSQLString, null, foundSetManager.getTableFilterParams(table.getServerName(), newSQLString), false, 0, foundSetManager.config.pkChunkSize() * 4, IDataServer.PRINT_QUERY);
// create a new FoundSet with 'data' and with right 'table', 'where','whereArgs'
SubSummaryFoundSet newSet = new SubSummaryFoundSet(app.getFoundSetManager(), rootSet, sortColumns, allAggregates, data, table);
// restore the sort for child body parts
newSQLString.setSorts(oldSort);
// make new where for use in sub queries
for (QuerySort sortbyCol : sortbyCols) {
QueryColumn sc = (QueryColumn) (sortbyCol).getColumn();
newSQLString.addCondition(SQLGenerator.CONDITION_SEARCH, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, sc, new Placeholder(new TablePlaceholderKey(sc.getTable(), '#' + sc.getName()))));
}
int count = newSet.getSize();
for (int ii = 0; ii < count; ii++) {
// make copy for setting sort column
QuerySelect newSQLStringCopy = AbstractBaseQuery.deepClone(newSQLString);
// handle the child first, this puts the rootset in the right state! for use of related(!) fields in the subsums
// THIS is EXTREMELY important for correct printing, see also SubSummaryFoundSet.queryForRelatedFoundSet
List<DataRendererDefinition> childRetval = null;
IFoundSetInternal curLeafFoundSet = null;
if (child != null) {
for (int i = 0; i < sortbyCols.size(); i++) {
QueryColumn sc = (QueryColumn) (sortbyCols.get(i)).getColumn();
TablePlaceholderKey placeholderKey = new TablePlaceholderKey(sc.getTable(), '#' + sc.getName());
if (!newSQLStringCopy.setPlaceholderValue(placeholderKey, data.getRow(ii)[i])) {
Debug.error(// $NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$
new RuntimeException("Could not set placeholder " + placeholderKey + " in query " + newSQLStringCopy + "-- continuing"));
}
}
childRetval = child.process(fpp, rootSet, table, newSQLStringCopy);
curLeafFoundSet = child.getCurrentLeafFoundSet();
}
SubSummaryFoundSet.PrintState state = (SubSummaryFoundSet.PrintState) newSet.getRecord(ii);
state.setDelegate(curLeafFoundSet);
if (part.getPartType() == Part.LEADING_SUBSUMMARY) {
state.doAggregatesLookup();
list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, state));
}
if (childRetval != null) {
list.addAll(childRetval);
}
if (isLeadingAndTrailingSubsummary) {
state.doAggregatesLookup();
list.add(new DataRendererDefinition(fpp, renderParent, second_part, second_renderer, state));
} else if (part.getPartType() == Part.TRAILING_SUBSUMMARY) {
state.doAggregatesLookup();
list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, state));
}
}
} else // for handeling (virtual) body part
{
rootSet.browseAll(sqlString);
int count = app.getFoundSetManager().getFoundSetCount(rootSet);
for (int ii = 0; ii < count; ii++) {
currentLeafFoundSet = rootSet;
list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, rootSet, ii));
}
}
return list;
}
use of com.servoy.j2db.query.ISQLTableJoin in project servoy-client by Servoy.
the class RelatedValueList method createRelatedValuelistQuery.
public static Pair<QuerySelect, BaseQueryTable> createRelatedValuelistQuery(IServiceProvider application, ValueList valueList, Relation[] relations, IRecordInternal parentState) throws ServoyException {
if (parentState == null) {
return null;
}
FoundSetManager foundSetManager = (FoundSetManager) application.getFoundSetManager();
SQLGenerator sqlGenerator = foundSetManager.getSQLGenerator();
IGlobalValueEntry scopesScopeProvider = foundSetManager.getScopesScopeProvider();
SQLSheet childSheet = sqlGenerator.getCachedTableSQLSheet(relations[0].getPrimaryDataSource());
// this returns quickly if it already has a sheet for that relation, but optimize further?
sqlGenerator.makeRelatedSQL(childSheet, relations[0]);
QuerySelect select = AbstractBaseQuery.deepClone((QuerySelect) childSheet.getRelatedSQLDescription(relations[0].getName()).getSQLQuery());
Object[] relationWhereArgs = foundSetManager.getRelationWhereArgs(parentState, relations[0], false);
if (relationWhereArgs == null) {
return null;
}
TablePlaceholderKey placeHolderKey = SQLGenerator.createRelationKeyPlaceholderKey(select.getTable(), relations[0].getName());
if (!select.setPlaceholderValue(placeHolderKey, relationWhereArgs)) {
// $NON-NLS-1$//$NON-NLS-2$
Debug.error(new RuntimeException("Could not set relation placeholder " + placeHolderKey + " in query " + select));
return null;
}
FlattenedSolution fs = application.getFlattenedSolution();
BaseQueryTable lastTable = select.getTable();
ITable foreignTable = fs.getTable(relations[0].getForeignDataSource());
for (int i = 1; i < relations.length; i++) {
foreignTable = fs.getTable(relations[i].getForeignDataSource());
ISQLTableJoin join = SQLGenerator.createJoin(application.getFlattenedSolution(), relations[i], lastTable, new QueryTable(foreignTable.getSQLName(), foreignTable.getDataSource(), foreignTable.getCatalog(), foreignTable.getSchema()), true, scopesScopeProvider);
select.addJoin(join);
lastTable = join.getForeignTable();
}
List<SortColumn> defaultSort = foundSetManager.getSortColumns(relations[relations.length - 1].getForeignDataSource(), valueList.getSortOptions());
foundSetManager.getSQLGenerator().addSorts(select, lastTable, scopesScopeProvider, foreignTable, defaultSort, true, true);
int showValues = valueList.getShowDataProviders();
int returnValues = valueList.getReturnDataProviders();
int total = (showValues | returnValues);
ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
if ((total & 1) != 0) {
columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID1()));
}
if ((total & 2) != 0) {
columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID2()));
}
if ((total & 4) != 0) {
columns.add(getQuerySelectValue(foreignTable, lastTable, valueList.getDataProviderID3()));
}
select.setColumns(columns);
// not allowed in all situations
select.setDistinct(false);
return new Pair<QuerySelect, BaseQueryTable>(select, lastTable);
}
use of com.servoy.j2db.query.ISQLTableJoin in project servoy-client by Servoy.
the class JSDatabaseManager method convertFoundSet.
public FoundSet convertFoundSet(Object foundset, Object related) throws ServoyException {
checkAuthorized();
if (foundset instanceof FoundSet && ((FoundSet) foundset).getTable() != null) {
FoundSet fs_old = (FoundSet) foundset;
try {
String relationName;
if (related instanceof RelatedFoundSet) {
relationName = ((RelatedFoundSet) related).getRelationName();
} else if (related instanceof String) {
relationName = (String) related;
} else {
// $NON-NLS-1$
Debug.warn("convertFoundSet: invalid argument " + related);
return null;
}
Relation relation = application.getFlattenedSolution().getRelation(relationName);
if (relation == null || relation.isMultiServer() || fs_old.getTable() == null || !fs_old.getTable().equals(application.getFlattenedSolution().getTable(relation.getPrimaryDataSource()))) {
// $NON-NLS-1$
Debug.warn("convertFoundSet: cannot use relation " + relationName);
return null;
}
ITable ft = application.getFlattenedSolution().getTable(relation.getForeignDataSource());
FoundSet fs_new = (FoundSet) application.getFoundSetManager().getNewFoundSet(ft, null, application.getFoundSetManager().getDefaultPKSortColumns(ft.getDataSource()));
QuerySelect sql = fs_old.getPksAndRecords().getQuerySelectForModification();
SQLSheet sheet_new = fs_old.getSQLSheet().getRelatedSheet(relation, ((FoundSetManager) application.getFoundSetManager()).getSQLGenerator());
if (sheet_new != null) {
BaseQueryTable oldTable = sql.getTable();
ISQLTableJoin join = (ISQLTableJoin) sql.getJoin(oldTable, relation.getName());
if (join == null) {
join = SQLGenerator.createJoin(application.getFlattenedSolution(), relation, oldTable, new QueryTable(ft.getSQLName(), ft.getDataSource(), ft.getCatalog(), ft.getSchema()), true, fs_old);
sql.addJoin(join);
}
BaseQueryTable mainTable = join.getForeignTable();
// invert the join
sql.setTable(mainTable);
// $NON-NLS-1$
join.invert("INVERTED." + join.getName());
// set the columns to be the PKs from the related table
ArrayList<IQuerySelectValue> pkColumns = new ArrayList<IQuerySelectValue>();
Iterator<Column> pks = sheet_new.getTable().getRowIdentColumns().iterator();
while (pks.hasNext()) {
Column column = pks.next();
pkColumns.add(column.queryColumn(mainTable));
}
sql.setColumns(pkColumns);
// sorting will be on the original columns, when distinct is set, this will conflict with the related pk columns
sql.setDistinct(false);
fs_new.setSQLSelect(sql);
return fs_new;
}
} catch (Exception e) {
Debug.error(e);
}
}
return null;
}
Aggregations