de.fraunhofer.iosb.ilt.frostserver.persistence.pgjooq.QueryBuilder Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of FROST-Server.SQLjooq Show documentation
Show all versions of FROST-Server.SQLjooq Show documentation
SQL bindings for the FROST-Server.
/*
* Copyright (C) 2024 Fraunhofer Institut IOSB, Fraunhoferstr. 1, D 76131
* Karlsruhe, Germany.
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Lesser General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with this program. If not, see selectedProperties;
private TableRef lastPath;
private NavigationPropertyMain lastNavProp;
private boolean forPath = false;
private ResourcePath requestedPath;
private boolean forTypeAndId = false;
private EntityType requestedEntityType;
private PkValue requestedId;
private boolean forUpdate = false;
private boolean parsed = false;
private QueryState queryState;
public QueryBuilder(JooqPersistenceManager pm, CoreSettings coreSettings, TableCollection tableCollection) {
this.pm = pm;
this.coreSettings = coreSettings;
this.tableCollection = tableCollection;
}
public JooqPersistenceManager getPersistenceManager() {
return pm;
}
public QueryState getQueryState() {
return queryState;
}
public ResultQuery buildSelect() {
gatherData();
final DSLContext dslContext = pm.getDslContext();
final SelectIntoStep selectStep;
if (staQuery != null && staQuery.isSelectDistinct()) {
selectStep = dslContext.selectDistinct(queryState.getSqlSelectFields());
} else if (queryState.isDistinctRequired()) {
if (queryState.isSqlSortFieldsSet()) {
if (staQuery == null || !staQuery.isPkOrder()) {
queryState.getSqlSortFields().addAll(queryState.getSqlMainIdFields());
}
selectStep = dslContext.select(queryState.getSqlSelectFields())
.distinctOn(queryState.getSqlSortFields().getSqlSortSelectFields());
} else {
selectStep = dslContext.select(queryState.getSqlSelectFields())
.distinctOn(queryState.getSqlMainIdFields());
}
} else {
selectStep = dslContext.select(queryState.getSqlSelectFields());
}
var whereStep = selectStep.from(queryState.getSqlFrom())
.where(queryState.getFullSqlWhere());
final var sortFields = queryState.getSqlSortFields().getSqlSortFields();
final var orderByStep = whereStep.orderBy(sortFields.toArray(OrderField[]::new));
int skip = 0;
int count;
if (staQuery != null) {
count = staQuery.getTopOrDefault() + 1;
if (staQuery.getSkipFilter() == null) {
skip = staQuery.getSkip(0);
}
} else {
count = 1;
}
var limit = orderByStep.limit(skip, count);
if (LOGGER.isTraceEnabled()) {
LOGGER.trace(GENERATED_SQL, limit.getSQL(ParamType.INDEXED));
}
if (forUpdate) {
return limit.forUpdate();
}
return limit;
}
/**
* Build a count query.
*
* @return the count query.
*/
public ResultQuery> buildCount() {
gatherData();
DSLContext dslContext = pm.getDslContext();
AggregateFunction count;
if (staQuery != null && staQuery.isSelectDistinct()) {
final Set sqlSelectFields = queryState.getSqlSelectFields();
count = DSL.countDistinct(sqlSelectFields.toArray(Field[]::new));
} else if (queryState.isDistinctRequired()) {
final List sqlMainIdFields = queryState.getSqlMainIdFields();
count = DSL.countDistinct(sqlMainIdFields.toArray(Field[]::new));
} else {
final List sqlMainIdFields = queryState.getSqlMainIdFields();
if (sqlMainIdFields.size() > 1) {
count = DSL.count();
} else {
count = DSL.count(sqlMainIdFields.get(0));
}
}
SelectConditionStep> query = dslContext.select(count)
.from(queryState.getSqlFrom())
.where(queryState.getSqlWhere());
if (LOGGER.isTraceEnabled()) {
LOGGER.trace(GENERATED_SQL, query.getSQL(ParamType.INDEXED));
}
return query;
}
public ResultQuery> buildCount(int limit) {
gatherData();
DSLContext dslContext = pm.getDslContext();
SelectSelectStep subSelect;
if (staQuery != null && staQuery.isSelectDistinct()) {
final Set sqlSelectFields = queryState.getSqlSelectFields();
subSelect = DSL.selectDistinct(sqlSelectFields.toArray(Field[]::new));
} else if (queryState.isDistinctRequired()) {
subSelect = DSL.selectDistinct(queryState.getSqlMainIdFields());
} else {
subSelect = DSL.select(queryState.getSqlMainIdFields());
}
var selectFromWhere = subSelect.from(queryState.getSqlFrom())
.where(queryState.getSqlWhere())
.limit(limit);
var query = dslContext.selectCount().from(selectFromWhere);
if (LOGGER.isTraceEnabled()) {
LOGGER.trace(GENERATED_SQL, query.getSQL(ParamType.INDEXED));
}
return query;
}
public static final class CountSampleResult {
public final ResultQuery countQuery;
public final int sampledTables;
public CountSampleResult(ResultQuery countQuery, int sampledTables) {
this.countQuery = countQuery;
this.sampledTables = sampledTables;
}
}
public CountSampleResult buildEstimateCountSample() {
ResultQuery> baseQuery = buildCount();
String queryString = baseQuery.getSQL(ParamType.INLINED);
String extendedQuery = RegExUtils.replaceFirst(queryString, TABLE_SEARCH_PATTERN, TABLESAMPLE_REPLACE_REGEX);
int replaces = (extendedQuery.length() - queryString.length()) / " tablesample system (1)".length();
DSLContext dslContext = pm.getDslContext();
final var query = dslContext.resultQuery(extendedQuery);
if (LOGGER.isTraceEnabled()) {
LOGGER.trace(GENERATED_SQL, extendedQuery);
}
return new CountSampleResult(query, replaces);
}
public ResultQuery> buildEstimateCountExplain() {
gatherData();
DSLContext dslContext = pm.getDslContext();
SelectSelectStep select;
if (staQuery != null && staQuery.isSelectDistinct()) {
final Set sqlSelectFields = queryState.getSqlSelectFields();
select = dslContext.selectDistinct(sqlSelectFields.toArray(Field[]::new));
} else if (queryState.isDistinctRequired()) {
select = dslContext.selectDistinct(queryState.getSqlMainIdFields());
} else {
select = dslContext.select(queryState.getSqlMainIdFields());
}
String selectQuery = select
.from(queryState.getSqlFrom())
.where(queryState.getSqlWhere())
.getSQL(ParamType.INLINED);
Field countField = DSL.field("count_estimate({0})", SQLDataType.INTEGER, selectQuery);
SelectSelectStep> countQuery = dslContext.select(countField);
if (LOGGER.isTraceEnabled()) {
LOGGER.trace(GENERATED_SQL, countQuery.getSQL(ParamType.INDEXED));
}
return countQuery;
}
public Delete buildDelete(PathElementEntitySet set) {
gatherData();
DSLContext dslContext = pm.getDslContext();
final StaMainTable table = tableCollection.getTablesByType().get(set.getEntityType());
if (table == null) {
throw new AssertionError("Don't know how to delete" + set.getEntityType().entityName, new IllegalArgumentException("Unknown type for delete"));
}
final List sqlMainIdFields = queryState.getSqlMainIdFields();
SelectConditionStep idSelect = DSL.select(sqlMainIdFields)
.from(queryState.getSqlFrom())
.where(queryState.getSqlWhere());
DeleteConditionStep delete = dslContext
.deleteFrom(table)
.where(
DSL.row(table.getPkFields()).in(idSelect));
if (LOGGER.isTraceEnabled()) {
LOGGER.trace(GENERATED_SQL, delete.getSQL(ParamType.INDEXED));
}
return delete;
}
public QueryBuilder forTypeAndId(EntityType entityType, PkValue id) {
if (forPath || forTypeAndId) {
throw new IllegalStateException("QueryBuilder already used.");
}
forTypeAndId = true;
requestedEntityType = entityType;
requestedId = id;
return this;
}
public QueryBuilder forPath(ResourcePath path) {
if (forPath || forTypeAndId) {
throw new IllegalStateException("QueryBuilder already used.");
}
forPath = true;
requestedPath = path;
requestedEntityType = path.getMainElementType();
return this;
}
public QueryBuilder forUpdate(boolean forUpdate) {
this.forUpdate = forUpdate;
return this;
}
public QueryBuilder usingQuery(Query query) {
this.staQuery = query;
return this;
}
private void gatherData() {
if (!parsed) {
parsed = true;
selectedProperties = findSelectedProperties(staQuery);
if (forPath) {
parsePath();
}
if (forTypeAndId) {
parseTypeAndId();
}
// Joins created when generating the path should not be merged with
// joins generated for the filter or orderby.
queryState.getTableRef().clearJoins();
// Add joins for NavPropEntity expands
handleEntityExpands(staQuery, queryState);
parseFilter(staQuery);
parseOrder(staQuery);
}
}
private void parsePath() {
int count = requestedPath.size();
for (int i = count - 1; i >= 0; i--) {
PathElement element = requestedPath.get(i);
element.visit(this);
}
}
private void parseTypeAndId() {
lastPath = queryEntityType(new PathElementEntity(requestedEntityType, null), requestedId, lastPath);
}
private Set findSelectedProperties(Query query) {
Set foundProperties = new HashSet<>();
if (query == null) {
return foundProperties;
}
for (Property property : query.getSelect()) {
if (property instanceof NavigationPropertyMain) {
foundProperties.addAll(requestedEntityType.getPrimaryKey().getKeyProperties());
}
foundProperties.add(property);
}
if (query.isPkOrder() && !query.isSelectDistinct() && !foundProperties.isEmpty()) {
// We're ordering by PK, make sure we select it too, so we can build better nextLinks
foundProperties.addAll(requestedEntityType.getPrimaryKey().getKeyProperties());
}
if (!query.getExpand().isEmpty() && !foundProperties.isEmpty()) {
// If we expand, and there is a $select, make sure we load the EP_ID and the navigation properties.
// If no $select, then we already load everything.
foundProperties.addAll(requestedEntityType.getPrimaryKey().getKeyProperties());
for (Expand expand : query.getExpand()) {
NavigationProperty expandPath = expand.getPath();
if (expandPath != null) {
foundProperties.add(expandPath);
}
}
}
return foundProperties;
}
private void handleEntityExpands(Query staQuery, QueryState sqlState) {
if (staQuery == null) {
return;
}
for (Expand expand : staQuery.getExpand()) {
NavigationProperty expandPath = expand.getPath();
if (expandPath instanceof NavigationPropertyEntity navPropEntity) {
handleEntityExpand(sqlState, expand, navPropEntity);
}
}
}
private void handleEntityExpand(QueryState sqlState, Expand expand, NavigationPropertyEntity navPropEntity) {
Query subQuery = expand.getSubQuery();
TableRef tableRef = sqlState.getTableRef();
TableRef joinRef = tableRef.createJoin(navPropEntity.getName(), queryState);
tableRef.addJoin(navPropEntity, joinRef);
StaMainTable joinedTable = joinRef.getTable();
QueryState subState = new QueryState(joinedTable, sqlState, sqlState.getNextAlias());
Set subProperties = findSelectedProperties(subQuery);
subState.setSelectedProperties(joinedTable.getPropertyFieldRegistry().getFieldsForProperties(subProperties));
sqlState.addChildState(navPropEntity, subState);
handleEntityExpands(subQuery, subState);
}
private void parseOrder(Query query) {
if (query != null) {
PgExpressionHandler handler = new PgExpressionHandler(coreSettings, this);
for (OrderBy ob : query.getOrderBy()) {
handler.addOrderbyToQuery(ob, queryState.getSqlSortFields());
}
}
}
public void parseFilter(Query query) {
if (query != null) {
queryState.setFilter(true);
final Expression filter = query.getFilter();
final Expression skipFilter = query.getSkipFilter();
PgExpressionHandler handler = new PgExpressionHandler(coreSettings, this);
if (filter != null) {
queryState.setSqlWhere(handler.addFilterToWhere(filter, queryState.getSqlWhere()));
}
if (skipFilter != null) {
queryState.setSqlSkipWhere(handler.addFilterToWhere(skipFilter, queryState.getSqlSkipWhere()));
}
}
}
@Override
public void visit(PathElementEntity element) {
lastPath = queryEntityType(element, element.getPkValues(), lastPath);
}
@Override
public void visit(PathElementEntitySet element) {
lastPath = queryEntityType(element, null, lastPath);
}
@Override
public void visit(PathElementProperty element) {
selectedProperties.add(element.getProperty());
}
@Override
public void visit(PathElementCustomProperty element) {
// noting to do for custom properties.
}
@Override
public void visit(PathElementArrayIndex element) {
// noting to do for custom properties.
}
/**
* Queries the given entity type, as relation to the given table reference
* and returns a new table reference. Effectively, this generates a join.
*
* @param pe The path element to query.
* @param targetId The id of the requested entity.
* @param last The table the requested entity is related to.
* @return The table reference of the requested entity.
*/
private TableRef queryEntityType(PathElementEntityType pe, PkValue targetId, TableRef last) {
final EntityType entityType = pe.getEntityType();
TableRef result;
if (last == null) {
StaMainTable tableForType = tableCollection.getTableForType(entityType).asSecure(DEFAULT_PREFIX, pm);
queryState = new QueryState(pm, tableForType, tableForType.getPropertyFieldRegistry().getFieldsForProperties(selectedProperties));
result = queryState.getTableRef();
} else {
TableRef existingJoin = last.getJoin(pe.getNavigationProperty());
if (existingJoin != null) {
return existingJoin;
}
if (entityType.equals(last.getType()) && lastNavProp == null) {
result = last;
} else {
result = last.createJoin(lastNavProp.getInverse().getName(), queryState);
}
}
if (targetId != null) {
final List lastId = result.getTable().getPkFields();
result.getJoinEqual(lastId);
Condition where = lastId.get(0).eq(targetId.get(0));
for (int idx = 1; idx < lastId.size(); idx++) {
where = where.and(lastId.get(idx).eq(targetId.get(idx)));
}
queryState.setSqlWhere(queryState.getSqlWhere().and(where));
}
lastNavProp = pe.getNavigationProperty();
return result;
}
public TableCollection getTableCollection() {
return tableCollection;
}
public static TableRef createJoinedRef(TableRef base, NavigationProperty np, StaMainTable table) {
if (np.getEntityType() != table.getEntityType()) {
throw new IllegalArgumentException("NavProp does not point to given table: " + np.getEntityType() + " != " + table.getEntityType());
}
TableRef newRef = new TableRef(table);
if (base != null) {
base.addJoin(np, newRef);
}
return newRef;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy