001/*
002 * Licensed to the Apache Software Foundation (ASF) under one or more
003 * contributor license agreements.  See the NOTICE file distributed with
004 * this work for additional information regarding copyright ownership.
005 * The ASF licenses this file to You under the Apache License, Version 2.0
006 * (the "License"); you may not use this file except in compliance with
007 * the License.  You may obtain a copy of the License at
008 *
009 *      http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017package org.apache.juneau.beanquery.sql;
018
019import static org.apache.juneau.commons.utils.Shorts.*;
020
021import java.util.*;
022
023import org.apache.juneau.commons.beanquery.*;
024
025/**
026 * Compiles a resolved {@link Filter} tree into a SQL {@code WHERE} predicate for a {@link SqlDialect}.
027 *
028 * <p>
029 * This is the dialect-agnostic core of the shared JDBC engine.  The terms are already parsed, allow-listed and
030 * type-checked.  The compiler walks each tree and:
031 * <ul>
032 *    <li>combines the terms (one per column) with {@code AND};
033 *    <li>combines a column's {@code $and}/{@code $or}/{@code $not} sub-expressions itself;
034 *    <li>delegates every leaf to the {@link SqlDialect} (a built-in via {@link SqlDialect#builtinRenderer(String)}, a
035 *       bare pattern via {@link SqlDialect#renderBare}) or, for a custom operator, to that operator's
036 *       {@link SearchOperator#extension(Class) SearchSqlRenderer extension}.
037 *    <li>a {@link #compile(Filter)} caller gets the same per-leaf rendering across a resolved, cross-column tree.
038 * </ul>
039 *
040 * <p>
041 * The SQL result must match the in-memory result, so a built-in the dialect cannot faithfully express is rejected with a
042 * {@link BeanQuerySyntaxException} whose code is {@link org.apache.juneau.commons.beanquery.BeanQuerySyntaxException.Code#BAD_VALUE BAD_VALUE} rather than
043 * rendered differently: an operator for which {@link SqlDialect#builtinRenderer(String)} returns <jk>null</jk> (for
044 * example {@code $regex} on a database without regex support), and a range/ordering operator ({@code $gt}, {@code $gte},
045 * {@code $lt}, {@code $lte}, {@code $between}) on a {@code VERSION} column when
046 * {@link SqlDialect#supportsVersionOrdering()} is <jk>false</jk>.  Equality on versions is unaffected.  A custom
047 * operator with no usable renderer still raises {@link IllegalStateException}.
048 *
049 * <h2 class='section'>Example:</h2>
050 * <p class='bjava'>
051 *    ResolvedQuery <jv>q</jv> = <jv>context</jv>.resolve(BeanQuery.<jsm>create</jsm>().gt(<js>"age"</js>, 21).eq(<js>"name"</js>, <js>"Bob"</js>).build());
052 *    SqlFragment <jv>where</jv> = SqlSearchCompiler.<jsm>create</jsm>(<jv>dialect</jv>, <jv>context</jv>).compile(<jv>q</jv>.filter());
053 *    <jc>// where.sql():   "age" &gt; ? AND "name" = ?</jc>
054 *    <jc>// where.binds(): [21, Bob]</jc>
055 * </p>
056 *
057 * @since 10.0.0
058 */
059public final class SqlSearchCompiler {
060
061   /** Range/ordering built-ins; on a VERSION column these need the dialect to order dotted tuples semantically. */
062   private static final Set<String> VERSION_ORDERING = Set.of("$gt", "$gte", "$lt", "$lte", "$between");
063
064   private final SqlDialect dialect;
065   private final ColumnResolver columns;
066
067   private SqlSearchCompiler(SqlDialect dialect, ColumnResolver columns) {
068      reqnn("dialect", dialect, "columns", columns);
069      this.dialect = dialect;
070      this.columns = columns;
071   }
072
073   /**
074    * Creates a compiler for a dialect and column resolver.
075    *
076    * @param dialect The target dialect.  Must not be <jk>null</jk>.
077    * @param columns Maps validated column names to quoted SQL references.
078    * @return A new compiler.
079    */
080   public static SqlSearchCompiler create(SqlDialect dialect, ColumnResolver columns) {
081      return new SqlSearchCompiler(dialect, columns);
082   }
083
084   /**
085    * Compiles an already-resolved {@link Filter} tree into a boolean {@code WHERE} predicate.
086    *
087    * <p>
088    * Renders the full cross-column {@code Filter.And}/{@code Filter.Or}/{@code Filter.Not} tree, so a top-level
089    * {@code $or} across <i>different</i> columns (for example DataTables' global search) compiles correctly.
090    * {@code Filter.And}/{@code Filter.Or} with no items render as always-true/always-false ({@code 1=1}/{@code 1=0})
091    * per their own contract &mdash; see {@code SqlBeanQueryContext.appendWhere}, which special-cases an empty
092    * top-level filter so an unfiltered query's {@code WHERE} clause does not pick up a spurious {@code (1=1)}.
093    *
094    * <h4 class='section'>Example:</h4>
095    * <p class='bjava'>
096    *    SqlFragment <jv>where</jv> = SqlSearchCompiler.<jsm>create</jsm>(<jv>dialect</jv>, <jv>columns</jv>)
097    *       .compile(<jv>filter</jv>);
098    * </p>
099    *
100    * @param filter The resolved filter, or <jk>null</jk> for no predicate.
101    * @return The predicate fragment, or <jk>null</jk> if {@code filter} is <jk>null</jk>.
102    * @throws BeanQuerySyntaxException (BAD_VALUE) If a built-in leaf is not supported by the dialect.
103    * @throws IllegalStateException If a custom-operator leaf cannot be rendered for the dialect.
104    */
105   public SqlFragment compile(Filter filter) {
106      return filter == null ? null : renderFilter(filter);
107   }
108
109   private SqlFragment renderFilter(Filter node) {
110      if (node instanceof Filter.Leaf leaf)
111         return render(leaf.expression(), columns.columnSql(leaf.column()), leaf.type());
112      if (node instanceof Filter.Not not) {
113         var child = renderFilter(not.item());
114         return not(child);
115      }
116      if (node instanceof Filter.And and) {
117         if (and.items().isEmpty())
118            return SqlFragment.of("1=1");
119         return join(and.items().stream().map(this::renderFilter).toList(), " AND ");
120      }
121      if (node instanceof Filter.Or or) {
122         if (or.items().isEmpty())
123            return SqlFragment.of("1=0");
124         return join(or.items().stream().map(this::renderFilter).toList(), " OR ");
125      }
126      throw new IllegalStateException("Unknown Filter type: " + node);
127   }
128
129   private SqlFragment render(SearchExpression node, String columnSql, SearchType type) {
130      if (node.isLiteral())
131         return required(dialect.renderBare(columnSql, type, node.typedValue(), node.isQuoted()), "bare pattern", node);
132      var op = node.operator();
133      if (op.isCombinator())
134         return renderCombinator(node, columnSql, type);
135      if (op.isCustom()) {
136         var r = op.extension(SearchSqlRenderer.class).orElse(null);
137         if (r == null)
138            throw new IllegalStateException("Custom operator '" + op.name() + "' has no SQL renderer; it cannot run against dialect '" + dialect.id() + "'.");
139         return required(r.render(dialect, columnSql, type, op.typedArgs() ? node.typedArgs() : List.copyOf(node.literalArgs())), "custom operator '" + op.name() + "'", node);
140      }
141      if (type == SearchType.VERSION && VERSION_ORDERING.contains(op.name()) && ! dialect.supportsVersionOrdering())
142         throw new BeanQuerySyntaxException(BeanQuerySyntaxException.Code.BAD_VALUE,
143            "Operator '%s' is not supported on version columns by the '%s' SQL dialect (version ordering is semantic in memory, so it is rejected rather than compared as text).", op.name(), dialect.id());
144      var r = dialect.builtinRenderer(op.name());
145      if (r == null)
146         throw new BeanQuerySyntaxException(BeanQuerySyntaxException.Code.BAD_VALUE,
147            "Operator '%s' is not supported by the '%s' SQL dialect.", op.name(), dialect.id());
148      return required(r.render(dialect, columnSql, type, node.typedArgs()), "operator '" + op.name() + "'", node);
149   }
150
151   private SqlFragment renderCombinator(SearchExpression node, String columnSql, SearchType type) {
152      if ("$not".equals(node.name())) {
153         return not(render(node.args().get(0), columnSql, type));
154      }
155      var children = node.args().stream().map(a -> render(a, columnSql, type)).toList();
156      return join(children, "$or".equals(node.name()) ? " OR " : " AND ");
157   }
158
159   /**
160    * Negates a fragment with two-valued (strictly true/false) semantics.
161    *
162    * <p>
163    * The in-memory engine evaluates every leaf to true or false, so a negated leaf matches a row whose cell is
164    * {@code null}.  In SQL a predicate over a {@code NULL} cell is {@code NULL}, and {@code NOT NULL} is still
165    * {@code NULL}, which would drop the row.  Coalescing the inner predicate to {@code FALSE} first restores the
166    * in-memory result.
167    */
168   private static SqlFragment not(SqlFragment child) {
169      return SqlFragment.of("(NOT COALESCE(" + child.sql() + ", FALSE))", child.binds());
170   }
171
172   /** Joins fragments with a boolean operator, concatenating binds; the whole group is always parenthesized. */
173   private static SqlFragment join(List<SqlFragment> frags, String sep) {
174      var sb = new StringBuilder("(");
175      var binds = new ArrayList<>();
176      for (var i = 0; i < frags.size(); i++) {
177         if (i > 0)
178            sb.append(sep);
179         sb.append(frags.get(i).sql());
180         binds.addAll(frags.get(i).binds());
181      }
182      sb.append(')');
183      return SqlFragment.of(sb.toString(), binds);
184   }
185
186   private SqlFragment required(SqlFragment f, String what, SearchExpression node) {
187      if (f == null)
188         throw new IllegalStateException("Dialect '" + dialect.id() + "' returned no SQL for " + what + " in '" + node + "'.");
189      return f;
190   }
191}