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.*;
020import static org.apache.juneau.commons.utils.StringUtils.*;
021
022import java.sql.*;
023import java.time.*;
024import java.util.*;
025import java.util.function.*;
026import java.util.stream.*;
027
028import javax.sql.*;
029
030import org.apache.juneau.commons.beanquery.*;
031
032/**
033 * The shared JDBC {@link BeanQueryContext}: renders validated queries into SQL for a {@link SqlDialect} and runs
034 * them on a connection from a configured source.
035 *
036 * <p>
037 * The engine is dialect-agnostic.  Identifier quoting, the built-in operator predicates and pagination come from the
038 * {@link SqlDialect} (Juneau ships {@code juneau-beanquery-postgres}).  Columns are always explicit: the class passed
039 * to {@link #create(Class)} fixes {@code T} (and is returned by {@link #getType()}); it is introspected only to
040 * choose the default row mapper, never to derive columns.
041 *
042 * <p>
043 * If no {@link Builder#rowMapper(SqlRowMapper) rowMapper} is set, {@link Builder#build()} picks a default from the
044 * row type {@code T}: a {@code Map} type that a {@link LinkedHashMap} is assignable to gets
045 * {@link SqlRowMapper#map()}, and any other type that {@link SqlRowMapper#bean(Class)} can plan (a record, or a class
046 * with a public no-arg constructor and setters) gets {@link SqlRowMapper#bean(Class)}.  {@code Object}, other
047 * {@code Map} types (such as {@code TreeMap}) and unplannable types get no default; {@code find} and {@code stream}
048 * then throw {@link IllegalStateException}, with the reason appended when there is one.  An explicit
049 * {@code rowMapper(...)} always wins, and {@code findValues}, {@code streamValues} and {@code count} never need one.
050 *
051 * <p>
052 * {@link #renderRows(ResolvedQuery)} and {@link #renderCount(ResolvedQuery, boolean)} open no connection, so the
053 * generated SQL and binds can be inspected and unit-tested.  A {@link #guard} fragment is AND-ed, in parentheses,
054 * into every statement.
055 *
056 * <h5 class='section'>Example:</h5>
057 * <p class='bjava'>
058 *    SqlBeanQueryContext&lt;Person&gt; <jv>context</jv> = SqlBeanQueryContext
059 *       .<jsm>create</jsm>(Person.<jk>class</jk>)
060 *       .dialect(PostgresDialect.<jsf>INSTANCE</jsf>)
061 *       .table(<js>"person"</js>)
062 *       .column(<js>"name"</js>, SearchType.<jsf>TEXT</jsf>)
063 *       .column(<js>"age"</js>, SearchType.<jsf>NUMERIC</jsf>)
064 *       .columnSql(<js>"name"</js>, <js>"full_name"</js>)
065 *       .guard(SqlFragment.<jsm>of</jsm>(<js>"\"deleted\" = false"</js>))
066 *       .rowMapper((<jv>rs</jv>, <jv>cols</jv>) -&gt; <jk>new</jk> Person(<jv>rs</jv>.getString(<js>"full_name"</js>), <jv>rs</jv>.getInt(<js>"age"</js>)))
067 *       .dataSource(<jv>dataSource</jv>)
068 *       .queryTimeout(Duration.<jsm>ofSeconds</jsm>(5))
069 *       .build();
070 *
071 *    <jk>try</jk> (SqlBeanQuerySession&lt;Person&gt; <jv>session</jv> = <jv>context</jv>.getSession()) {
072 *       Page&lt;Person&gt; <jv>page</jv> = <jv>session</jv>.find(<jv>query</jv>);
073 *    }
074 *
075 *    <jc>// No rowMapper(...): Person is a record, so build() defaults to SqlRowMapper.bean(Person.class).</jc>
076 *    SqlBeanQueryContext&lt;Person&gt; <jv>defaulted</jv> = SqlBeanQueryContext.<jsm>create</jsm>(Person.<jk>class</jk>)
077 *       .dialect(PostgresDialect.<jsf>INSTANCE</jsf>)
078 *       .table(<js>"person"</js>)
079 *       .column(<js>"name"</js>, SearchType.<jsf>TEXT</jsf>)
080 *       .column(<js>"age"</js>, SearchType.<jsf>NUMERIC</jsf>)
081 *       .dataSource(<jv>dataSource</jv>)
082 *       .build();
083 *
084 *    <jc>// A Map row type defaults to SqlRowMapper.map().</jc>
085 *    SqlBeanQueryContext&lt;Map&gt; <jv>maps</jv> = SqlBeanQueryContext.<jsm>create</jsm>(Map.<jk>class</jk>)
086 *       <jc>// ...dialect, table, columns, connection source...</jc>
087 *       .build();
088 * </p>
089 *
090 * @param <T> The row (bean) type.
091 * @since 10.0.0
092 */
093@SuppressWarnings({
094   "java:S1192" // Duplicated argument-name literals read more clearly inline than as constants.
095})
096public final class SqlBeanQueryContext<T> extends BeanQueryContext<T> implements ColumnResolver {
097
098   // Applied only when a statement's search includes $regex and no explicit queryTimeout (below) was configured.
099   // Unbounded SQL regex is a DoS risk -- the in-memory engine's RegexBudget cannot reach a
100   // query that runs inside the database, so this is the SQL engine's own, separate bound.
101   static final Duration DEFAULT_REGEX_TIMEOUT = Duration.ofSeconds(5);
102
103   /**
104    * Builder for {@link SqlBeanQueryContext}.  See the class Javadoc for an example.
105    *
106    * @param <T> The row type.
107    */
108   public static final class Builder<T> extends BeanQueryContext.Builder<T,Builder<T>> {
109
110      private final Class<T> type;
111      private SqlDialect dialect;
112      private String table;
113      private SqlRowMapper<T> rowMapper;
114      private final Map<String,String> columnSql = new LinkedHashMap<>();
115      private SqlFragment guard;
116      private Supplier<Connection> connectionSupplier;
117      private Consumer<Connection> connectionReleaser = SqlBeanQueryContext::closeConnection;
118      private int fetchSize = 500;
119      private Duration queryTimeout;
120
121      Builder(Class<T> type) {
122         this.type = type;
123      }
124
125      Builder(SqlBeanQueryContext<T> copyFrom) {
126         super(copyFrom);
127         type = copyFrom.type;
128         dialect = copyFrom.dialect;
129         table = copyFrom.table;
130         rowMapper = copyFrom.rowMapper;
131         columnSql.putAll(copyFrom.columnSql);
132         guard = copyFrom.guard;
133         connectionSupplier = copyFrom.connectionSupplier;
134         connectionReleaser = copyFrom.connectionReleaser;
135         fetchSize = copyFrom.fetchSize;
136         queryTimeout = copyFrom.queryTimeout;
137      }
138
139      /**
140       * Sets the SQL dialect.  Required.
141       *
142       * @param value The dialect.  Must not be <jk>null</jk>.
143       * @return This object.
144       */
145      public Builder<T> dialect(SqlDialect value) {
146         dialect = reqnn("value", value);
147         return this;
148      }
149
150      /**
151       * Sets the table (or view) to query.  Required.
152       *
153       * @param value The raw table name; the dialect quotes it.  Must not be blank.
154       * @return This object.
155       */
156      public Builder<T> table(String value) {
157         table = reqnb("value", value);
158         return this;
159      }
160
161      /**
162       * Sets how {@link SqlBeanQuerySession#find(BeanQuery) find} and {@link SqlBeanQuerySession#stream(BeanQuery)
163       * stream} turn a row into a bean, overriding the default {@link #build()} would pick from the row type.  Not
164       * needed for {@code findValues}, {@code streamValues} or {@code count}.
165       *
166       * @param value The row mapper.  Must not be <jk>null</jk>.
167       * @return This object.
168       */
169      public Builder<T> rowMapper(SqlRowMapper<T> value) {
170         rowMapper = reqnn("value", value);
171         return this;
172      }
173
174      /**
175       * Maps a declared column to a different raw SQL column name (otherwise the column name is used).
176       *
177       * @param column The column name.  Must be declared.
178       * @param sqlName The raw SQL column name; the dialect quotes it.  Must not be blank.
179       * @return This object.
180       */
181      public Builder<T> columnSql(String column, String sqlName) {
182         columnSql.put(declared(column), reqnb("sqlName", sqlName));
183         return this;
184      }
185
186      /**
187       * Sets a server-only SQL predicate AND-ed into every statement.  A request can neither see nor remove it.
188       *
189       * @param value The guard, or <jk>null</jk> for none.
190       * @return This object.
191       */
192      public Builder<T> guard(SqlFragment value) {
193         guard = value;
194         return this;
195      }
196
197      /**
198       * Sets where sessions get a connection when none is passed to
199       * {@link SqlBeanQuerySession.Builder#connection(Connection)}.  This or {@link #dataSource(DataSource)} is
200       * required.
201       *
202       * @param value The supplier.  Must not be <jk>null</jk>.  Exceptions it throws become
203       *    {@link BeanQueryExecutionException}s.
204       * @return This object.
205       */
206      public Builder<T> connectionSupplier(Supplier<Connection> value) {
207         connectionSupplier = reqnn("value", value);
208         return this;
209      }
210
211      /**
212       * Sets how a session gives back a connection it got from the supplier.  Default: {@link Connection#close()}.
213       *
214       * @param value The releaser.  Must not be <jk>null</jk>.
215       * @return This object.
216       */
217      public Builder<T> connectionReleaser(Consumer<Connection> value) {
218         connectionReleaser = reqnn("value", value);
219         return this;
220      }
221
222      /**
223       * Shorthand for {@link #connectionSupplier(Supplier) connectionSupplier(ds::getConnection)}, with a
224       * {@link SQLException} wrapped as a {@link BeanQueryExecutionException}.
225       *
226       * @param value The data source.  Must not be <jk>null</jk>.
227       * @return This object.
228       */
229      public Builder<T> dataSource(DataSource value) {
230         reqnn("value", value);
231         return connectionSupplier(() -> {
232            try {
233               return value.getConnection();
234            } catch (SQLException e) {
235               throw new BeanQueryExecutionException(e, "Failed to open a database connection.");
236            }
237         });
238      }
239
240      /**
241       * Sets the JDBC fetch size for every statement.  Default: 500.
242       *
243       * @param value The fetch size.  Must be positive.
244       * @return This object.
245       */
246      public Builder<T> fetchSize(int value) {
247         req(value > 0, "fetchSize must be positive: %s", value);
248         fetchSize = value;
249         return this;
250      }
251
252      /**
253       * Sets the JDBC query timeout for every statement, rounded up to whole seconds.  Default: none.
254       *
255       * <p>
256       * Leave unset and this still bounds any statement whose search includes {@code $regex}, at a shorter built-in
257       * default &mdash; set this explicitly to control that bound instead of accepting the default.
258       *
259       * @param value The timeout, or <jk>null</jk> for none.  Must be positive if set.
260       * @return This object.
261       */
262      public Builder<T> queryTimeout(Duration value) {
263         req(value == null || ! (value.isNegative() || value.isZero()), "queryTimeout must be positive: %s", value);
264         queryTimeout = value;
265         return this;
266      }
267
268      /**
269       * {@inheritDoc}
270       *
271       * @throws IllegalStateException If the dialect, the table, the connection source or every column is missing.
272       */
273      @Override /* BeanQueryContext.Builder */
274      public SqlBeanQueryContext<T> build() {
275         if (dialect == null)
276            throw isex("SqlBeanQueryContext requires a dialect.");
277         if (table == null)
278            throw isex("SqlBeanQueryContext requires a table.");
279         if (connectionSupplier == null)
280            throw isex("SqlBeanQueryContext requires a connection source.");
281         if (rowMapper != null)
282            return new SqlBeanQueryContext<>(this, rowMapper, null);
283         var selection = defaultRowMapper(type);
284         return new SqlBeanQueryContext<>(this, selection.mapper(), selection.reason());
285      }
286
287      // Default row-mapper selection; runs only when no rowMapper(...) was set.
288      @SuppressWarnings({
289         "unchecked" // A LinkedHashMap row is assignable to every T the Map branch accepts.
290      })
291      private static <T> MapperSelection<T> defaultRowMapper(Class<T> type) {
292         if (type == Object.class)
293            return new MapperSelection<>(null, null);
294         if (Map.class.isAssignableFrom(type)) {
295            if (type.isAssignableFrom(LinkedHashMap.class))
296               return new MapperSelection<>((SqlRowMapper<T>)SqlRowMapper.map(), null);
297            return new MapperSelection<>(null, "rows are LinkedHashMap");
298         }
299         var plan = SqlRowMappers.tryBean(type);
300         if (plan.reason() == null)
301            return new MapperSelection<>(plan.mapper(), null);
302         return new MapperSelection<>(null, format("cannot map rows to '%s': %s", type.getSimpleName(), plan.reason()));
303      }
304
305      private record MapperSelection<T>(SqlRowMapper<T> mapper, String reason) {}
306   }
307
308   /**
309    * Returns a new builder.
310    *
311    * @param <T> The row type.
312    * @param type The row type.  Fixes {@code T} and is returned by {@link #getType()}; it is introspected only to
313    *    choose the default row mapper, never for columns.  Must not be <jk>null</jk>.
314    * @return A new builder.
315    */
316   public static <T> Builder<T> create(Class<T> type) {
317      return new Builder<>(reqnn("type", type));
318   }
319
320   private final Class<T> type;
321   private final SqlDialect dialect;
322   final String table;
323   private final SqlRowMapper<T> rowMapper;
324   private final String rowMapperReason;  // Why no default mapper could be chosen, or null.
325   private final Map<String,String> columnSql;
326   private final SqlFragment guard;
327   private final Supplier<Connection> connectionSupplier;
328   private final Consumer<Connection> connectionReleaser;
329   final int fetchSize;
330   final Duration queryTimeout;
331
332   SqlBeanQueryContext(Builder<T> builder, SqlRowMapper<T> rowMapper, String rowMapperReason) {
333      super(builder);
334      type = builder.type;
335      dialect = builder.dialect;
336      table = builder.table;
337      this.rowMapper = rowMapper;
338      this.rowMapperReason = rowMapperReason;
339      columnSql = Map.copyOf(builder.columnSql);
340      guard = builder.guard;
341      connectionSupplier = builder.connectionSupplier;
342      connectionReleaser = builder.connectionReleaser;
343      fetchSize = builder.fetchSize;
344      queryTimeout = builder.queryTimeout;
345   }
346
347   /**
348    * Returns the row type passed to {@link #create(Class)}.
349    *
350    * @return The row type.  Never <jk>null</jk>.
351    */
352   public Class<T> getType() {
353      return type;
354   }
355
356   @Override /* BeanQueryContext */
357   public Builder<T> copy() {
358      return new Builder<>(this);
359   }
360
361   @Override /* BeanQueryContext */
362   public SqlBeanQuerySession.Builder<T> createSession() {
363      return new SqlBeanQuerySession.Builder<>(this);
364   }
365
366   /**
367    * Opens a session on a connection from the configured source, with this context's settings as-is.
368    *
369    * @return A new session.  Close it to release the connection.
370    * @throws BeanQueryExecutionException If no connection can be obtained.
371    */
372   public SqlBeanQuerySession<T> getSession() {
373      return createSession().build();
374   }
375
376   @Override /* ColumnResolver */
377   public String columnSql(String column) {
378      return dialect.quote(columnSql.getOrDefault(column, column));
379   }
380
381   //-----------------------------------------------------------------------------------------------------------------
382   // Statement rendering (no connection)
383   //-----------------------------------------------------------------------------------------------------------------
384
385   /**
386    * Renders the row-selection statement for a validated query.
387    *
388    * @param query The validated query, from {@link #resolve(BeanQuery)}.  Must not be <jk>null</jk>.
389    * @return The {@code SELECT} statement.
390    */
391   public SqlStatement renderRows(ResolvedQuery query) {
392      return renderRows(query, null);
393   }
394
395   SqlStatement renderRows(ResolvedQuery query, SqlFragment sessionGuard) {
396      reqnn("query", query);
397      checkOrigin(query);
398      var cols = query.view();
399      var sb = new StringBuilder("SELECT ");
400      sb.append(cols.stream().map(this::columnSql).collect(Collectors.joining(", ")));
401      var binds = new ArrayList<>();
402      sb.append(" FROM ").append(dialect.quote(table));
403      appendWhere(sb, binds, query, true, sessionGuard);
404      appendOrderBy(sb, query);
405      var limit = query.limit() == null ? null : Long.valueOf(query.limit());
406      var offset = query.position() == 0 ? null : Long.valueOf(query.position());
407      sb.append(dialect.renderLimitOffset(limit, offset));
408      return new SqlStatement(sb.toString(), binds, cols, containsRegex(query.filter()));
409   }
410
411   /**
412    * Renders a count statement for a validated query.
413    *
414    * @param query The validated query, from {@link #resolve(BeanQuery)}.  Must not be <jk>null</jk>.
415    * @param applySearch <jk>true</jk> for the matched count (guards and search); <jk>false</jk> for the total count
416    *    (guards only).
417    * @return The {@code COUNT} statement.
418    */
419   public SqlStatement renderCount(ResolvedQuery query, boolean applySearch) {
420      return renderCount(query, applySearch, null);
421   }
422
423   SqlStatement renderCount(ResolvedQuery query, boolean applySearch, SqlFragment sessionGuard) {
424      reqnn("query", query);
425      checkOrigin(query);
426      var sb = new StringBuilder("SELECT count(*) FROM ").append(dialect.quote(table));
427      var binds = new ArrayList<>();
428      appendWhere(sb, binds, query, applySearch, sessionGuard);
429      return new SqlStatement(sb.toString(), binds, List.of(), applySearch && containsRegex(query.filter()));
430   }
431
432   private void appendWhere(StringBuilder sb, List<Object> binds, ResolvedQuery query, boolean applySearch, SqlFragment sessionGuard) {
433      var parts = new ArrayList<SqlFragment>();
434      // guard/sessionGuard are raw, caller-supplied SQL this library cannot verify is internally safe to AND
435      // unwrapped (e.g. it may itself be a top-level OR), so each is defensively parenthesized here.
436      Stream.of(guard, sessionGuard)
437         .filter(Objects::nonNull)
438         .forEach(g -> parts.add(SqlFragment.of("(" + g.sql() + ")", g.binds())));  // Parenthesized so an OR cannot escape.
439      if (applySearch) {
440         var filter = query.filter();
441         if (! (filter instanceof Filter.And and && and.items().isEmpty())) {
442            // Unlike guard/sessionGuard, search comes from SqlSearchCompiler.compile(Filter), which already
443            // guarantees its own top-level rendering is safe to AND with other WHERE parts (a multi-item
444            // top-level And/Or parenthesizes itself; a top-level Not/Leaf is already one atomic, boundaried
445            // unit) -- so wrapping it again here would just double-parenthesize it.
446            parts.add(SqlSearchCompiler.create(dialect, this).compile(filter));
447         }
448      }
449      if (parts.isEmpty())
450         return;
451      sb.append(" WHERE ");
452      sb.append(parts.stream().map(SqlFragment::sql).collect(Collectors.joining(" AND ")));
453      parts.forEach(p -> binds.addAll(p.binds()));
454   }
455
456   // True if any leaf anywhere in the filter uses the $regex operator. If filter contains a $regex leaf at all,
457   // allowRegex was necessarily true when QueryResolver resolved it (checkRegex gates this earlier in the
458   // pipeline), so there is nothing left to re-check here -- only to detect, for the statement-timeout fallback.
459   private static boolean containsRegex(Filter filter) {
460      if (filter instanceof Filter.Leaf leaf)
461         return containsRegex(leaf.expression());
462      if (filter instanceof Filter.Not not)
463         return containsRegex(not.item());
464      if (filter instanceof Filter.And and)
465         return and.items().stream().anyMatch(SqlBeanQueryContext::containsRegex);
466      if (filter instanceof Filter.Or or)
467         return or.items().stream().anyMatch(SqlBeanQueryContext::containsRegex);
468      throw new IllegalStateException("Unknown Filter type: " + filter);
469   }
470
471   private static boolean containsRegex(SearchExpression node) {
472      if (node.isLiteral())
473         return false;
474      if ("$regex".equals(node.operator().name()))
475         return true;
476      return node.args().stream().anyMatch(SqlBeanQueryContext::containsRegex);
477   }
478
479   private void appendOrderBy(StringBuilder sb, ResolvedQuery query) {
480      var keys = query.sort();
481      if (keys.isEmpty())
482         return;
483      sb.append(" ORDER BY ");
484      sb.append(keys.stream().map(k -> columnSql(k.column()) + (k.descending() ? " DESC" : " ASC")).collect(Collectors.joining(", ")));
485   }
486
487   //-----------------------------------------------------------------------------------------------------------------
488   // Session support
489   //-----------------------------------------------------------------------------------------------------------------
490
491   SqlRowMapper<T> rowMapper() {
492      if (rowMapper == null) {
493         if (rowMapperReason == null)
494            throw isex("SqlBeanQueryContext requires a rowMapper for find and stream.");
495         throw isex("SqlBeanQueryContext requires a rowMapper for find and stream: %s.", rowMapperReason);
496      }
497      return rowMapper;
498   }
499
500   Connection acquire() {
501      Connection c;
502      try {
503         c = connectionSupplier.get();
504      } catch (BeanQueryExecutionException e) {
505         throw e;
506      } catch (RuntimeException e) {
507         throw new BeanQueryExecutionException(e, "Failed to open a database connection.");
508      }
509      if (c == null)
510         throw new BeanQueryExecutionException(null, "Failed to open a database connection.");
511      return c;
512   }
513
514   void release(Connection c) {
515      try {
516         connectionReleaser.accept(c);
517      } catch (BeanQueryExecutionException e) {
518         throw e;
519      } catch (RuntimeException e) {
520         throw new BeanQueryExecutionException(e, "Failed to release a database connection.");
521      }
522   }
523
524   @SuppressWarnings({
525      "java:S1144" // Used as a method reference by the nested Builder; the analyzer misses that.
526   })
527   private static void closeConnection(Connection c) {
528      try {
529         c.close();
530      } catch (SQLException e) {
531         throw new BeanQueryExecutionException(e, "Failed to release a database connection.");
532      }
533   }
534}