matrei commented on code in PR #16505:
URL: https://github.com/apache/grails-core/pull/16505#discussion_r4178241770
##########
grails-data-hibernate7/core/src/main/groovy/grails/orm/HibernateCriteriaBuilder.java:
##########
@@ -1112,6 +1113,32 @@ public Criteria sizeLt(String propertyName, int size) {
return this;
}
+ /**
+ * Restricts the results with a native SQL condition. {@code {alias}} in
the SQL stands for the table alias of
+ * the queried entity.
+ *
+ * @param sqlRestriction the SQL condition
+ * @return this criteria
+ */
+ public org.grails.datastore.mapping.query.api.Criteria
sqlRestriction(String sqlRestriction) {
+ return sqlRestriction(sqlRestriction, Collections.emptyList());
+ }
+
+ /**
+ * Restricts the results with a native SQL condition whose {@code ?}
placeholders are bound to the given values.
+ * {@code {alias}} in the SQL stands for the table alias of the queried
entity.
+ *
+ * @param sqlRestriction the SQL condition
+ * @param values the values of the {@code ?} placeholders, in order, none
of them {@code null}
+ * @return this criteria
+ * @throws IllegalArgumentException if the number of {@code ?}
placeholders differs from the number of values,
+ * or a value is {@code null}
+ */
+ public org.grails.datastore.mapping.query.api.Criteria
sqlRestriction(String sqlRestriction, List<?> values) {
+ hibernateQuery.add(new SqlRestriction(sqlRestriction, values));
Review Comment:
Fixed. The builder now builds its `and`, `or` and `not` blocks with a Groovy
category that adds `sqlRestriction` to the detached criteria those blocks
resolve their calls against. The call therefore lands on whichever criteria is
the active delegate: the junction itself, an association block inside it
(`DetachedCriteria.methodMissing` makes the association criteria the delegate),
a junction inside that association, or a nested association. The category
applies only to the current thread and only while the builder is building a
junction. Regressions through `createCriteria()` cover an association inside
`or` and `not`, an association inside `and` inside `or`, a junction inside an
association inside `or`, and nested associations with and without an enclosing
junction. The Testcontainers spec runs an association-in-`or` case on all five
databases.
##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/query/PredicateGenerator.java:
##########
@@ -448,6 +453,38 @@ private Predicate handlePropertyCriterion(
throw new UnsupportedOperationException("Unsupported criterion: " +
pc.getClass().getName());
}
+ /**
+ * Returns a column of the entity's own table, whose table alias replaces
{@code {alias}} in a SQL restriction.
+ */
+ private static Expression<?> aliasColumn(From<?, ?> root,
GrailsHibernatePersistentEntity entity) {
+ PersistentProperty identity = entity.getIdentity();
+ if (identity == null) {
+ HibernatePersistentProperty[] compositeIdentity =
entity.getCompositeIdentity();
+ if (compositeIdentity == null || compositeIdentity.length == 0) {
+ throw new ConfigurationException("Cannot use sqlRestriction on
class [" + entity.getJavaClass().getName() + "] without an identifier");
+ }
+ identity = compositeIdentity[0];
+ }
+ Path<?> path = root.get(identity.getName());
+ if (identity instanceof Association<?> association &&
association.getAssociatedEntity() != null) {
+ path =
path.get(association.getAssociatedEntity().getIdentity().getName());
Review Comment:
Fixed. The `{alias}` column is now resolved by following the identifier, or
the first property of a composite identifier, through associations until it
reaches a basic property. A composite identifier that starts with an
association to an entity with its own composite identifier resolves to the
first foreign key column on the owning table. The column is also added only
when the SQL contains `{alias}`, so a restriction without it never resolves
one. `an entity whose composite identifier starts with an association to an
entity with a composite identifier` covers both the `{alias}` and the unaliased
form.
##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/query/PredicateGenerator.java:
##########
@@ -448,6 +453,38 @@ private Predicate handlePropertyCriterion(
throw new UnsupportedOperationException("Unsupported criterion: " +
pc.getClass().getName());
}
+ /**
+ * Returns a column of the entity's own table, whose table alias replaces
{@code {alias}} in a SQL restriction.
+ */
+ private static Expression<?> aliasColumn(From<?, ?> root,
GrailsHibernatePersistentEntity entity) {
+ PersistentProperty identity = entity.getIdentity();
+ if (identity == null) {
+ HibernatePersistentProperty[] compositeIdentity =
entity.getCompositeIdentity();
+ if (compositeIdentity == null || compositeIdentity.length == 0) {
+ throw new ConfigurationException("Cannot use sqlRestriction on
class [" + entity.getJavaClass().getName() + "] without an identifier");
+ }
+ identity = compositeIdentity[0];
+ }
+ Path<?> path = root.get(identity.getName());
+ if (identity instanceof Association<?> association &&
association.getAssociatedEntity() != null) {
+ path =
path.get(association.getAssociatedEntity().getIdentity().getName());
Review Comment:
Fixed. The `{alias}` column is now resolved by following the identifier, or
the first property of a composite identifier, through associations until it
reaches a basic property. A composite identifier that starts with an
association to an entity with its own composite identifier resolves to the
first foreign key column on the owning table. The column is also added only
when the SQL contains `{alias}`, so a restriction without it never resolves
one. `an entity whose composite identifier starts with an association to an
entity with a composite identifier` covers both the `{alias}` and the unaliased
form.
##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/query/SqlRestriction.java:
##########
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.grails.orm.hibernate.query;
+
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+
+import org.grails.datastore.mapping.query.Query;
+
+/**
+ * Criterion that restricts the results with a native SQL condition, created
by {@code sqlRestriction} in a
+ * criteria query. In the SQL, {@code {alias}} stands for the table alias of
the queried entity, and each
+ * {@code ?} for one of the values, which are bound as parameters.
+ *
+ * @param sql the SQL condition
+ * @param values the values of the {@code ?} placeholders, in order
+ * @since 8.0.0
+ */
+public record SqlRestriction(String sql, List<?> values) implements
Query.Criterion {
+
+ public SqlRestriction {
+ if (sql == null) {
+ throw new IllegalArgumentException("The SQL of a sqlRestriction
must not be null");
+ }
+ if (values == null) {
+ values = List.of();
+ }
+ List<Object> copy = new ArrayList<>(values.size());
+ for (Object value : values) {
+ if (value == null) {
+ throw new IllegalArgumentException("The values of a
sqlRestriction must not be null: " + sql);
+ }
+ copy.add(value instanceof CharSequence ? value.toString() : value);
+ }
+ long placeholders = sql.chars().filter(c -> c == '?').count();
+ if (placeholders != copy.size()) {
+ throw new IllegalArgumentException("The SQL of a sqlRestriction
has " + placeholders +
+ " ? placeholders but " + copy.size() + " values: " + sql);
Review Comment:
Fixed. `SqlRestriction` validation and `GrailsSqlRestrictionFunction`
rendering now share one scan. It skips a `?` inside a string literal (`'...'`,
with `''` as an escaped quote), a quoted identifier (`"..."` or `` `...` ``), a
`--` line comment and a `/* */` block comment. Regression tests cover a literal
`?` before a real placeholder, a literal `?` with no parameters, an escaped
quote, and a `?` in a quoted identifier, a block comment and a line comment.
The Testcontainers spec also runs a literal `?` and a trailing comment on all
five databases.
##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/query/SqlRestriction.java:
##########
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.grails.orm.hibernate.query;
+
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+
+import org.grails.datastore.mapping.query.Query;
+
+/**
+ * Criterion that restricts the results with a native SQL condition, created
by {@code sqlRestriction} in a
+ * criteria query. In the SQL, {@code {alias}} stands for the table alias of
the queried entity, and each
+ * {@code ?} for one of the values, which are bound as parameters.
+ *
+ * @param sql the SQL condition
+ * @param values the values of the {@code ?} placeholders, in order
+ * @since 8.0.0
+ */
+public record SqlRestriction(String sql, List<?> values) implements
Query.Criterion {
+
+ public SqlRestriction {
+ if (sql == null) {
+ throw new IllegalArgumentException("The SQL of a sqlRestriction
must not be null");
+ }
+ if (values == null) {
+ values = List.of();
+ }
+ List<Object> copy = new ArrayList<>(values.size());
+ for (Object value : values) {
+ if (value == null) {
+ throw new IllegalArgumentException("The values of a
sqlRestriction must not be null: " + sql);
+ }
+ copy.add(value instanceof CharSequence ? value.toString() : value);
+ }
+ long placeholders = sql.chars().filter(c -> c == '?').count();
Review Comment:
Fixed. `SqlRestriction` validation and `GrailsSqlRestrictionFunction`
rendering now share one scan. It skips a `?` inside a string literal (`'...'`,
with `''` as an escaped quote), a quoted identifier (`"..."` or `` `...` ``), a
`--` line comment and a `/* */` block comment. Regression tests cover a literal
`?` before a real placeholder, a literal `?` with no parameters, an escaped
quote, and a `?` in a quoted identifier, a block comment and a line comment.
The Testcontainers spec also runs a literal `?` and a trailing comment on all
five databases.
##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/query/GrailsSqlRestrictionFunction.java:
##########
@@ -0,0 +1,99 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.grails.orm.hibernate.query;
+
+import java.util.List;
+
+import org.hibernate.metamodel.model.domain.ReturnableType;
+import
org.hibernate.query.sqm.function.AbstractSqmSelfRenderingFunctionDescriptor;
+import org.hibernate.query.sqm.produce.function.StandardArgumentsValidators;
+import
org.hibernate.query.sqm.produce.function.StandardFunctionReturnTypeResolvers;
+import org.hibernate.sql.ast.SqlAstTranslator;
+import org.hibernate.sql.ast.spi.SqlAppender;
+import org.hibernate.sql.ast.tree.SqlAstNode;
+import org.hibernate.sql.ast.tree.expression.ColumnReference;
+import org.hibernate.sql.ast.tree.expression.Expression;
+import org.hibernate.sql.ast.tree.expression.Literal;
+import org.hibernate.sql.ast.tree.expression.SqlTuple;
+import org.hibernate.type.StandardBasicTypes;
+import org.hibernate.type.spi.TypeConfiguration;
+
+/**
+ * Renders a {@link SqlRestriction} as a predicate. The arguments are the SQL
condition as a literal, a column of
+ * the queried entity whose table alias replaces {@code {alias}}, and the
value of each {@code ?} placeholder.
+ *
+ * @since 8.0.0
+ */
+public class GrailsSqlRestrictionFunction extends
AbstractSqmSelfRenderingFunctionDescriptor {
+
+ public static final String NAME = "grails_sql_restriction";
+
+ static final String ALIAS_PLACEHOLDER = "{alias}";
+
+ public GrailsSqlRestrictionFunction(TypeConfiguration typeConfiguration) {
+ super(
+ NAME,
+ StandardArgumentsValidators.min(2),
+ StandardFunctionReturnTypeResolvers.invariant(
+
typeConfiguration.getBasicTypeRegistry().resolve(StandardBasicTypes.BOOLEAN)),
+ null);
+ }
+
+ @Override
+ public boolean isPredicate() {
+ return true;
+ }
+
+ @Override
+ public void render(
+ SqlAppender sqlAppender,
+ List<? extends SqlAstNode> arguments,
+ ReturnableType<?> returnType,
+ SqlAstTranslator<?> walker) {
+ if (!(arguments.get(0) instanceof Literal literal) ||
!(literal.getLiteralValue() instanceof String sql)) {
+ throw new IllegalArgumentException("The first argument of " + NAME
+ " must be the SQL as a literal");
+ }
+ if (sql.contains(ALIAS_PLACEHOLDER)) {
+ sql = sql.replace(ALIAS_PLACEHOLDER, tableAlias(arguments.get(1)));
+ }
+ int index = 0;
+ for (int i = 2; i < arguments.size(); i++) {
+ int placeholder = sql.indexOf('?', index);
+ if (placeholder == -1) {
+ throw new IllegalArgumentException("The SQL restriction has
fewer ? placeholders than values: " + sql);
+ }
+ sqlAppender.append(sql, index, placeholder);
+ arguments.get(i).accept(walker);
+ index = placeholder + 1;
+ }
+ if (sql.indexOf('?', index) != -1) {
+ throw new IllegalArgumentException("The SQL restriction has more ?
placeholders than values: " + sql);
+ }
+ sqlAppender.append(sql, index, sql.length());
Review Comment:
Fixed. The fragment is now rendered in parentheses, so `A or B` combined
with another criterion is `(A or B) and C`. If the SQL ends in a `--` line
comment, a line break goes before the closing parenthesis so the comment cannot
swallow it. There are tests for the Dune/Emma case and its negation, and the
Testcontainers spec runs the grouping on all five databases.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
