com.sap.cds.impl.sql.SelectStatementBuilder Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of cds4j-runtime Show documentation
CDS QL Execution Engine
There is a newer version: 3.4.0
/**************************************************************************
 * (C) 2019-2024 SAP SE or an SAP affiliate company. All rights reserved. *
 **************************************************************************/
package com.sap.cds.impl.sql;

import static com.sap.cds.impl.sql.SQLStatementBuilder.commaSeparated;
import static com.sap.cds.impl.sql.SpaceSeparatedCollector.joining;
import static com.sap.cds.ql.impl.SelectBuilder.COLLATING;
import static com.sap.cds.ql.impl.SelectBuilder.COLLATING_OFF;
import static com.sap.cds.util.CdsModelUtils.entity;

import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Deque;
import java.util.List;
import java.util.Locale;
import java.util.Optional;
import java.util.stream.Stream;

import com.google.common.collect.Streams;
import com.sap.cds.SessionContext;
import com.sap.cds.impl.Context;
import com.sap.cds.impl.PreparedCqnStmt.Parameter;
import com.sap.cds.impl.builder.model.Conjunction;
import com.sap.cds.impl.localized.LocaleUtils;
import com.sap.cds.impl.qat.FromClauseBuilder;
import com.sap.cds.impl.qat.QatBuilder;
import com.sap.cds.impl.qat.QatSelectRootNode;
import com.sap.cds.impl.qat.QatSelectableNode;
import com.sap.cds.impl.qat.Ref2QualifiedColumn;
import com.sap.cds.jdbc.spi.SqlMapping;
import com.sap.cds.jdbc.spi.StatementResolver;
import com.sap.cds.ql.CQL;
import com.sap.cds.ql.cqn.CqnLock;
import com.sap.cds.ql.cqn.CqnPredicate;
import com.sap.cds.ql.cqn.CqnSelect;
import com.sap.cds.ql.cqn.CqnSelectListItem;
import com.sap.cds.ql.cqn.CqnSortSpecification;
import com.sap.cds.ql.cqn.CqnSource;
import com.sap.cds.ql.cqn.CqnStructuredTypeRef;
import com.sap.cds.ql.cqn.CqnValue;
import com.sap.cds.reflect.CdsBaseType;
import com.sap.cds.reflect.CdsEntity;
import com.sap.cds.util.CqnStatementUtils;

public class SelectStatementBuilder implements SQLStatementBuilder {

	private final Context context;
	private final CqnSelect select;
	private final Deque outer;
	private final StatementResolver statementResolver;
	private final SessionContext sessionContext;
	private final List params;

	private Ref2QualifiedColumn aliasResolver;
	private TokenToSQLTransformer toSQL;
	private final SqlMapping sqlMapping;
	private final FromClauseBuilder fromClauseBuilder;
	private final LocaleUtils localeUtils;
	private final CdsEntity targetEntity;
	private final Collating collating;
	private final boolean isSubquery;

	public SelectStatementBuilder(Context context, List params, CqnSelect select,
			Deque outer) {
		this(context, params, select, outer, false, false);
	}

	public SelectStatementBuilder(Context context, List params, CqnSelect select,
			Deque outer, boolean noCollating, boolean isSubquery) {
		this.context = context;
		this.params = params;
		this.select = select;
		this.outer = append(outer, new QatBuilder(context, select, outer.size()).create());
		this.statementResolver = context.getDbContext().getStatementResolver();
		this.sessionContext = context.getSessionContext();
		this.fromClauseBuilder = new FromClauseBuilder(context, params, select.hints());
		this.localeUtils = new LocaleUtils(context.getCdsModel(), context.getDataStoreConfiguration());
		this.targetEntity = select.from().isRef() ? entity(context.getCdsModel(), select.from().asRef()) : null;
		this.collating = determineCollating(select, localeUtils, statementResolver,
				context.getSessionContext().getLocale(), noCollating);
		this.sqlMapping = context.getDbContext().getSqlMapping(targetEntity);
		this.isSubquery = isSubquery || outer.peekFirst() instanceof QatSelectRootNode;
	}

	@Override
	public SQLStatement build() {
		aliasResolver = new Ref2QualifiedColumn(context.getDbContext()::getSqlMapping, outer, localeUtils);
		// TODO propagate statement-level collation from inner to outer query
		toSQL = new TokenToSQLTransformer(context, params, aliasResolver, outer, true);
		List snippets = new ArrayList<>();
		with().forEach(snippets::add);
		snippets.add("SELECT");
		distinct().forEach(snippets::add);
		columns().forEach(snippets::add);
		from().forEach(snippets::add);
		where().forEach(snippets::add);
		groupBy().forEach(snippets::add);
		having().forEach(snippets::add);
		orderBy().forEach(snippets::add);
		limit().forEach(snippets::add);
		lock().forEach(snippets::add);
		withCollation().ifPresent(snippets::add);
		hints().forEach(snippets::add);
		String sql = snippets.stream().collect(joining());

		return new SQLStatement(sql, params);
	}

	private static boolean hasCollatingOffHint(CqnSelect select) {
		return COLLATING_OFF.equals(select.hints().get(COLLATING));
	}

	private Optional withCollation() {
		if (collating == Collating.STATEMENT) {
			Locale locale = context.getSessionContext().getLocale();
			return statementResolver.statementWideCollation(select, locale);
		}
		return Optional.empty();
	}

	private Stream distinct() {
		if (select.isDistinct()) {
			return Stream.of("DISTINCT");
		}

		return Stream.empty();
	}

	private Stream columns() {
		List items = select.items();
		if (items.isEmpty()) {
			throw new IllegalStateException("select * not expected");
		}

		Stream.Builder sql = Stream.builder();
		items.stream().flatMap(CqnSelectListItem::ofValue).forEach(slv -> {
			sql.add(",");
			String column = toSQL.apply(slv.value());
			sql.add(column);
			slv.alias().ifPresent(a -> {
				if (isSubquery) {
					String alias = sqlMapping.delimitedCasing(a.replace('.', '_'));
					if (!column.endsWith(alias)) {
						sql.add("as");
						sql.add(alias);
					}
				} else {
					sql.add("as");
					sql.add(SQLHelper.delimited(a));
				}
			});
		});

		return sql.build().skip(1);
	}

	private Stream with() {
		return fromClauseBuilder.with(outer);
	}

	private Stream from() {
		return Streams.concat(Stream.of("FROM"), fromClauseBuilder.sql(outer));
	}

	private Stream where() {
		Optional where = Conjunction.and(select.where(), select.search());
		CqnSource source = select.from();
		if (source.isRef()) {
			CqnStructuredTypeRef ref = source.asRef();
			where = Conjunction.and(where, ref.targetSegment().filter());
		}

		if (collating == Collating.COLLATE) {
			Locale locale = sessionContext.getLocale();
			statementResolver.collate(locale).ifPresent(aliasResolver::startCollate);
		}
		Stream.Builder sql = Stream.builder();
		where.map(w -> toSQL.toSQL(targetEntity, w)).ifPresent(s -> {
			sql.add("WHERE");
			sql.add(s);
		});
		aliasResolver.stopCollate();

		return sql.build();
	}

	private Stream groupBy() {
		List groupByTokens = select.groupBy();
		if (groupByTokens.isEmpty()) {
			return Stream.empty();
		}
		return Streams.concat(Stream.of("GROUP BY"), commaSeparated(groupByTokens.stream(), toSQL::apply));
	}

	private Stream having() {
		Optional having = select.having();

		if (collating == Collating.COLLATE) {
			Locale locale = sessionContext.getLocale();
			statementResolver.collate(locale).ifPresent(aliasResolver::startCollate);
		}
		Stream.Builder sql = Stream.builder();
		having.map(h -> toSQL.toSQL(targetEntity, h)).ifPresent(s -> {
			sql.add("HAVING");
			sql.add(s);
		});
		aliasResolver.stopCollate();

		return sql.build();
	}

	private Stream orderBy() {
		List orderBy = select.orderBy();
		if (orderBy.isEmpty()) {
			return Stream.empty();
		}
		return Streams.concat(Stream.of("ORDER BY"), commaSeparated(orderBy.stream(), this::sort));
	}

	private String sort(CqnSortSpecification o) {
		StringBuilder sort = new StringBuilder(toSQL.apply(o.value()));
		Optional collateClause = statementResolver.collate(o, sessionContext.getLocale());
		if (collating == Collating.COLLATE && collateClause.isPresent() && requiresCollate(o.value())) {
			sort.append(" " + collateClause.get());
		}
		sort.append(" " + sortOrderToSql(o));
		return sort.toString();
	}
	
	public static String sortOrderToSql(CqnSortSpecification o) {
		return switch (o.order()) {
			case DESC -> "DESC NULLS LAST";
			case DESC_NULLS_FIRST -> "DESC NULLS FIRST";
			case ASC_NULLS_LAST -> "NULLS LAST";
			default -> "NULLS FIRST";
		};
	}

	private boolean requiresCollate(CqnValue value) {
		if (targetEntity == null) {
			return false;
		}
		if (value.isRef()) {
			return localeUtils.requiresCollate(targetEntity, value.asRef());
		}
		return CqnStatementUtils.getCdsType(targetEntity, value).filter(t -> CdsBaseType.STRING == t).isPresent();
	}

	private Stream limit() {
		long top = select.top();
		long skip = select.skip();

		Stream.Builder sql = Stream.builder();
		if (top < 0 && skip > 0) {
			top = Integer.MAX_VALUE;
		}

		if (top >= 0) {
			sql.add("LIMIT");
			sql.add(toSQL.apply(CQL.constant(top)));
		}

		if (skip > 0) {
			sql.add("OFFSET");
			sql.add(toSQL.apply(CQL.val(skip)));
		}

		return sql.build();
	}

	private Stream lock() {
		Stream stream = select.getLock().stream();

		return stream.flatMap(statementResolver::lockClause);
	}

	private Stream hints() {
		Stream.Builder sql = Stream.builder();
		statementResolver.hints(select.hints()).ifPresent(sql::add);

		return sql.build();
	}

	private static Deque append(Deque prefix, QatSelectableNode newRoot) {
		ArrayDeque roots = new ArrayDeque<>(prefix);
		roots.add(newRoot);

		return roots;
	}

	private static Collating determineCollating(CqnSelect select, LocaleUtils localeUtils,
			StatementResolver statementResolver, Locale locale, boolean noCollating) {
		if (locale == null || noCollating || hasCollatingOffHint(select)) {
			return Collating.OFF;
		}

		return statementResolver.supportsStatementWideCollation() && localeUtils.requiresCollationClause(select, locale)
				? Collating.STATEMENT
				: Collating.COLLATE;
	}

	private enum Collating {
		STATEMENT, COLLATE, OFF
	}

}