# Query.java

    /*
    ** Module   : Query.java
    ** Abstract : Wrapper for FQL statements.
    **
    ** Copyright (c) 2019-2024, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
    ** 001 ECF 20191001 Created initial version with stubbed methods.
    **     OM  20191108 Added method implementations.
    **     ECF 20200419 Added getSQLQuery for UNDO processing.
    ** 002 IAS 20201125 Wrap parameters in the auxiliary class
    **     IAS 20210303 Added removeParam(()
    **     IAS 20210315 Used Database instead of schema in the createQuery.
    **     OM  20210412 Dropped unused imports. Javadoc fixes.
    **     IAS 20210506 Rework _UserTableStat flash logic as per Eric advise.
    **     IAS 20210905 Re-working re-writing queries with the values of the mutable SESSION attributes.
    **     ECF 20210910 UserTableStatUpdater API change.
    **     IAS 20211223 Make getDialect(Session session) package private.
    **     CA  20220304 Throw a IllegalStateException if the firstResult or maxResults parameter can't be set for
    **                  the query.
    **     IAS 20220324 Re-worked LockTableUpdater.
    **     OM  20220409 Added support for List/array query parameters.
    **     OM  20220628 removeParam() returns the removed object.
    **     OM  20220629 Signature change for createSQLQuery() method. Implementation of QueryParams.toString().
    **     CA  20221006 Added JMX instrumentation for FQL query parse. Refs #6814
    **     SBI 20221023 Added a usage of SQLQuery.uniqueResult(Session session, RowStructure rowStructure).
    **     CA  20221031 Javadoc fixes.
    **     IAS 20230209 Fixed ${TZ} placeholder value
    ** 003 RAA 20230213 Added lazyMode flag. This is useful if the intention is to execute the query in a lazy
    **                  manner.
    ** 004 IAS 20230505 Fixed FILL support for recursive DATA-RELATION
    **                  Added fix for H2 bug with bind parameters in recursive CTEs.
    **                  Fixed second pass query generation for queries with CONTAINS operator
    ** 005 SR  20230505 Removed readOnly flag.
    ** 006 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
    ** 007 OM  20231214 Added a parameter to list() which allows the caller to select whether the result is a set
    **                  of Records of just an array of Java plain objects.
    ** 008 IAS 20230208 Dialect-specific error handler support.
    ** 009 HC  20240222 Enabled JMX on FWD Client.
    ** 010 CA  20240320 Avoid BDTs within internal FWD runtime.  Replaced iterators with Java 'for', where it
    **                  applies.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero 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 Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.persist.orm;

    import java.lang.reflect.*;
    import java.util.*;
    import java.util.function.*;

    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.jmx.*;
    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.persist.meta.*;

    /**
     *
     */
    public class Query
    {
       /** Instrumentation for {@link #createSqlQuery}. */
       private static final NanoTimer QUERY_PARSE = NanoTimer.getInstance(FwdServerJMX.TimeStat.OrmFqlParse);

       /** Caching lambda */
       private final Consumer<Query> cache;

       /** The query as a FQL (FWD Query Language) statement. */
       private final String fql;

       /** Flags a query which was re-written */
       private boolean wasRewritten = false;

       /** Flag indicating that the query reads from _UserTableStat VST */
       private boolean userTableStatRead = false;

       /** Flag indicating that the query reads from _Lock VST */
       private boolean lockTableRead = false;

       /** Flag indicating that the query contains error handling */
       private boolean containsErrorHandling = false;
       /**
        * The maximum number of results accepted by the (SELECT) query. Used with {@code firstResult}
        * to configure result pagination.
        */
       private int maxResults = -1;

       /**
        * Flag accepted by the {@code Select} query. If it is set to {@code true}, then the query will be
        * executed in a lazy manner.
        * By default, queries are executed in a preselect manner.
        */
       private boolean lazyMode = false;

       /**
        * The index of the first matching results accepted by the (SELECT) query. Used in combination
        * with {@code firstResult} to configure result pagination.
        */
       private int firstResult = -1;

       /** The number of parameter as detected after parsing the FQL statement. */
       private int paramCount = 0;

       /** Query parameters' data */
       private final QueryParams params = new QueryParams();

       /** The cached SQL query. */
       private SQLQuery sqlQuery = null;

       /** The recursive DATA-RELATION the query is to FILL for */
       private DataRelation relation = null;
       /**
        * The expected structure of a query, as generated by the {@link FqlToSqlConverter}. The content
        * is not very explicit for the moment, only the DMO and the number of fields. If more info
        * is needed ity can be added in subsequent developonet rounds.
        */
       private ArrayList<RowStructure> rowStructure = null;

       /**
        * Creates a new {@code Query} object.
        * @param   cache
        *          cache query lambda
        * @param   fql
        *          The query as a FQL (FWD Query Language) statement.
        */
       public Query(Consumer<Query> cache, String fql)
       {
          this.cache = cache;
          this.fql = fql;
       }

       /**
        * Configures the maximum number of rows returned by this query. Depending on the SQL dialect
        * from the {@code session}, the value is injected as the parameter of SQL {@code limit}
        * clause.
        *
        * @param   maxResults
        *          The maximum number of results accepted by the (SELECT) query.
        */
       public void setMaxResults(int maxResults)
       {
          // TODO: if (sqlQuery != null) invalidate? or throw error?
          this.maxResults = maxResults;
       }

       /**
        * Setter for the {@code lazyMode} flag.
        * Note: this method should only be called if the {@code Query} executed is a {@code Select}.
        *
        * @param   lazyMode
        *          If {@code true}, the {@code Query} will be executed in a lazy manner.
        */
       public void setLazyMode(boolean lazyMode)
       {
          this.lazyMode = lazyMode;
       }

       /**
        * Configures the index of the first row to be returned by this query. Depending on the SQL
        * dialect from the {@code session}, the value is injected as the parameter of SQL
        * {@code limit} clause.
        *
        * @param   firstResult
        *          The index of teh first matching results accepted by the (SELECT) query.
        */
       public void setFirstResult(int firstResult)
       {
          // TODO: if (sqlQuery != null) invalidate? or throw error?
          this.firstResult = firstResult;
       }

       /**
        * Sets the value of a parameter for this query. This is done before parsing the HQL so the
        * index of the parameter is NOT checked.
        *
        * @param   index
        *          The index of the parameter to be set (0-base).
        * @param   arg
        *          The parameter value.
        */
       public void setParameter(int index, Object arg)
       {
          params.setParameter(index, arg);
       }

       /**
        * Returns a scrollable list of rows of type {@code <T>} by converting the {@code fql} to SQL
        * and executing the query against the SQL server.
        *
        * @param   <T>
        *          The type of the rows returned in the list.
        *
        * @param   session
        *          The {@code Session} to be used.
        * @param   scrollMode
        *          The scroll mode.
        *
        * @return  A scrollable list of rows of type {@code <T>} from SQL server, as specified by the
        *          {@code fql} query.
        *
        * @throws  PersistenceException
        *          when an error occurred while performing the requested operations.
        */
       public <T> ScrollableResults<T> scroll(Session session, int scrollMode)
       throws PersistenceException
       {
          Dialect dialect = getDialect(session);
          return createSqlQuery(dialect, session.getDatabase()).
                scroll(session, scrollMode, rowStructure);
       }

       /**
        * Returns an unique row of type {@code <T>} by converting the {@code fql} to SQL and
        * executing the query against the SQL server.
        *
        * @param   <T>
        *          The type of the row to be returned.
        *
        * @param   session
        *          The {@code Session} to be used.
        *
        * @return  A row of type {@code <T>} from SQL server, as specified by the {@code fql} query.
        *
        * @throws  UniqueResultException
        *          if the result set contains not an exactly one row.
        * @throws  PersistenceException
        *          when an error occurred while performing the requested operations.
        */
       public <T> T uniqueResult(Session session)
       throws UniqueResultException,
              PersistenceException
       {
          SQLQuery sqlQuery = createSqlQuery(getDialect(session), session.getDatabase());

          if (rowStructure.size() != 1)
          {
             throw new UniqueResultException("Not a unique result query");
          }
          RowStructure struct = rowStructure.get(0);

          return sqlQuery.uniqueResult(session, struct);
       }

       /**
        * Returns a list of rows of type {@code <T>} by converting the {@code fql} to SQL and executing the query
        * against the SQL server. The returned list will contain a set of hydrated {@link Record} objects.
        *
        * @param   <T>
        *          The type of the rows returned in the list.
        *
        * @param   session
        *          The {@code Session} to be used.
        *
        * @return  A list of rows of type {@code <T>} from SQL server, as specified by the {@code fql} query.
        *
        * @throws  PersistenceException
        *          when an error occurred while performing the requested operations.
        */
       public <T> List<T> list(Session session)
       throws PersistenceException
       {
          Dialect dialect = getDialect(session);
          return createSqlQuery(dialect, session.getDatabase()).list(session, rowStructure, true);
       }

       /**
        * Returns a list of rows of type {@code <T>} by converting the {@code fql} to SQL and executing the query
        * against the SQL server. According to last parameter, the returning list can contain hydrated
        * {@link Record} objects (if {@code hydrateRecords} is {@code true}) or plain Java wrappers
        * (driver specific, which includes: {@code Integer}, {@code String}, {@code BigDecimal}, etc.)
        *
        * @param   <T>
        *          The type of the rows returned in the list.
        *
        * @param   session
        *          The {@code Session} to be used.
        * @param   hydrateRecords
        *          If {@code true} the result is interpreted as legacy records and will be hydrated.
        *          Otherwise, the returned {@code List} contains an array for each row.
        *
        * @return  A list of rows of type {@code <T>} from SQL server, as specified by the {@code fql} query.
        *
        * @throws  PersistenceException
        *          when an error occurred while performing the requested operations.
        */
       public <T> List<T> list(Session session, boolean hydrateRecords)
       throws PersistenceException
       {
          Dialect dialect = getDialect(session);
          return createSqlQuery(dialect, session.getDatabase()).list(session, rowStructure, hydrateRecords);
       }

       /**
        * Converts the {@code fql} statement to SQL and applies the eventual parameters then execute
        * the update SQL query. When finished with success returns the number of affected columns.
        *
        * @param   session
        *          The {@code Session} to be used.
        *
        * @return  The number of affected rows by the statement.
        *
        * @throws  PersistenceException
        *          when an error occurred while performing the requested operations.
        */
       public int executeUpdate(Session session)
       throws PersistenceException
       {
          Dialect dialect = getDialect(session);
          return createSqlQuery(dialect, session.getDatabase()).executeUpdate(session);
       }

       /**
        * Get the {@code SQLQuery} object created by this object. If invoked before the query is
        * executed, {@code null} is returned.
        *
        * @return  SQL query object or {@code null} if invoked too early.
        */
       SQLQuery getSqlQuery()
       {
          return sqlQuery;
       }

        /**
        * Creates the delegating SQL query. The {@code fql} statement is converted to SQL and a
        * {@code SQLQuery} is created from current session. The pagination are configured and the
        * eventual parameters are set. When finished, the SQL query is returned.
        *
        * @param   dialect
        *          The {@code Dialect} to be used in conversion.
        * @param   db
        *          The database. This is used to unambiguate short-names entities.
        *
        * @return  The SQL query as described above.
        *
        * @throws  PersistenceException
        *          on error occurred while creating the query or setting the parameters.
        */
       private SQLQuery createSqlQuery(Dialect dialect, Database db)
       throws PersistenceException
       {
          if (sqlQuery == null)
          {
             Operation op = () ->
             {
                FqlToSqlConverter fql2sql = FqlToSqlConverter.getInstance(dialect, db, params);
                rowStructure = new ArrayList<>(2); // this should be enough for most cases
                String sql = fql2sql.toSQL(fql, maxResults, firstResult, rowStructure,
                                           relation, lazyMode);
                paramCount = fql2sql.getLastConversionParamCount();
                wasRewritten = fql2sql.isQueryWasRewritten();
                userTableStatRead = fql2sql.isUserTableStatRead();
                this.containsErrorHandling = fql2sql.containsErrorHandling();
                lockTableRead = fql2sql.isLockTableRead();
                if (!wasRewritten)
                {
                   cache.accept(this);
                }
                sqlQuery = Session.createSQLQuery(sql, fql2sql.sessionAttrs(),
                         fql2sql.getParameterPermutation()).
                         bindParam2SessionVar(fql2sql.bindParam2SessionVar()).
                         containsErrorHandling(containsErrorHandling);
             };

             QUERY_PARSE.timer(op);
          }

          // [paramCount] includes optional [maxResults], [firstResult] positions
          params.ensureParameterSize(paramCount);
          int paging = paramCount;

          if (firstResult > 0)
          {
             // add [firstResult] at the end of parameter list
             int idx = --paging;
             if (idx < 0)
             {
                throw new IllegalStateException("Could not set firstResult parameter for query: " + fql);
             }
             params.parameters[idx] = firstResult;
          }

          if (maxResults > 0)
          {
             // add [maxResults] at the end of parameter list
             int idx = --paging;
             if (idx < 0)
             {
                throw new IllegalStateException("Could not set firstResult parameter for query: " + fql);
             }
             params.parameters[idx] = maxResults;
          }

          for (int i = 0; i < paramCount; i++)
          {
             sqlQuery.setParameter(i, params.parameters[i]);
          }

          if (userTableStatRead)
          {
             UserTableStatUpdater.persist(db);
          }
          if (lockTableRead)
          {
             LockTableUpdater ltu = DatabaseManager.getLockTableUpdater(db);
             if (ltu != null)
             {
                ltu.persist();
             }
          }
          return sqlQuery.expandPlaceholders(dialect);
       }

       /**
        * Get the dialect of the database the session is connected to.
        *
        * @param   session
        *          The session to be investigated.
        *
        * @return  the dialect of the database the session is connected to.
        */
       static Dialect getDialect(Session session)
       {
          return DatabaseManager.getDialect(session.getDatabase());
       }

       /**
        * Get the schema of the database the session is connected to.
        *
        * @param   session
        *          The session to be investigated.
        *
        * @return  the schema of the database the session is connected to.
        */
       private static String getSchema(Session session)
       {
          Database db = session.getDatabase();
          return db.isMeta() ? MetadataManager.META_SCHEMA : db.getName();
       }

       /**
        * Get the recursive DATA-RELATION the query is to FILL for.
        * @return the recursive DATA-RELATION the query is to FILL for
        */
       public DataRelation getDataRelation()
       {
          return this.relation;
       }

       /**
        * Set the recursive DATA-RELATION the query is to FILL for.
        * @param relation
        *       the recursive DATA-RELATION the query is to FILL for
        */
       public void setDataRelation(final DataRelation relation)
       {
          if (relation != this.relation)
          {
              this.sqlQuery = null;
          }
          this.relation = relation;
       }

       /**
        * Parameters' data holder
        */
       public static class QueryParams
       {
          /** The set of positional parameters */
          public Object[] parameters = null;

          /**
           * The index of the maximum parameter set. It is assumed this is the last positional parameter
           * for the FQL query.
           */
          public int maxParamIndex = -1;

          /**
           * Makes sure the array that holds the parameters for this query is large enough to accommodate
           * the {@code fitIndex}-th parameter. If the index fits the current size nothing is performed.
           * Otherwise the array is reallocated and the existing parameters are copied to their fixed
           * positions. To improve performance, more space is allocated so that this operation will not
           * happen at each method call.
           *
           * @param   fitIndex
           *          The index of the latest added parameter.
           */
          public void ensureParameterSize(int fitIndex)
          {
             if (maxParamIndex < fitIndex)
             {
                maxParamIndex = fitIndex;
             }

             if (parameters == null)
             {
                parameters = new Object[Math.max(5, fitIndex + 1)]; // TODO: optimize this ?
                return;
             }

             if (parameters.length > fitIndex)
             {
                // nothing to do
                return;
             }

             // create a new array and copy from previously set [parameters] to new location
             Object[] newParam = new Object[1 + fitIndex * 3 / 2]; // TODO: optimize this ?
             System.arraycopy(parameters, 0, newParam, 0, parameters.length);

             this.parameters = newParam;
          }

          /**
           * Sets the value of a parameter for this query. This is done before parsing the HQL so the
           * index of the parameter is NOT checked.
           *
           * @param   index
           *          The index of the parameter to be set (0-base).
           * @param   arg
           *          The parameter value.
           */
          public void setParameter(int index, Object arg)
          {
             ensureParameterSize(index);
             parameters[index] = arg;
          }

          /**
           * Removes one of the query's parameter and shifting all the parameters on higher offset one position to
           * the left.
           *
           * @param   pos
           *          The offset of the parameter to be removed.
           *
           * @return  the value which have been removed.
           */
          public Object removeParam(int pos)
          {
             int oldnPar = parameters.length;
             Object[] newParams = new Object[oldnPar - 1];
             Object ret = parameters[pos];

             int np = 0;
             for (int i = 0; i < pos; i++)
             {
                newParams[np++] = parameters[i];
             }

             for (int i = pos + 1; i < oldnPar; i++)
             {
                newParams[np++] = parameters[i];
             }

             parameters = newParams;
             --maxParamIndex;

             return ret;
          }

          /**
           * Replaces a query parameter with a list of values. The total number of parameters increases by
           * {@code n - 1}, where {@code n} is the number of values in the {@code args} list. All existing
           * parameters at higher offsets than {@code pos} are shifted to the right by {@code n - 1} positions.
           *
           * @param   pos
           *          The offset of the parameter to be replaced.
           * @param   args
           *          A list of replacement parameters.
           */
          public void replaceParam(int pos, List<Object> args)
          {
             replaceParam(pos, args.toArray());
          }

          /**
           * Replaces a query parameter with a list of values. The total number of parameters increases by
           * {@code n - 1}, where {@code n} is the number of values in the {@code args} list. All existing
           * parameters at higher offsets than {@code pos} are shifted to the right by {@code n - 1} positions.
           *
           * @param   pos
           *          The offset of the parameter to be replaced.
           * @param   args
           *          A list of replacement parameters.
           */
          public void replaceParam(int pos, Object[] args)
          {
             int oldnPar = parameters.length;
             Object[] newParams = new Object[oldnPar + args.length - 1];

             int np = 0;
             for (int i = 0; i < pos; i++)
             {
                newParams[np++] = parameters[i];
             }

             for (Object s: args)
             {
                newParams[np++] = s;
             }

             for (int i = pos + 1; i < oldnPar; i++)
             {
                newParams[np++] = parameters[i];
             }

             parameters = newParams;
             maxParamIndex += args.length - 1;
          }

          /**
           * Inserts a list of parameters at a specified position. The total number of parameters increases by
           * {@code n}, where {@code n} is the number of values in the {@code args} list. All existing parameters
           * at higher offsets than {@code pos} are shifted to the right by {@code n} positions.
           *
           * @param   pos
           *          The offset after which to insert the new list.
           * @param   args
           *          The list of parameters to be added.
           */
          public void insertParam(int pos, List<Object> args)
          {
             insertParam(pos, args.toArray());
          }

          /**
           * Inserts a list of parameters at a specified position. The total number of parameters increases by
           * {@code n}, where {@code n} is the number of values in the {@code args} list. All existing parameters
           * at higher offsets than {@code pos} are shifted to the right by {@code n} positions.
           *
           * @param   pos
           *          The offset after which to insert the new list.
           * @param   args
           *          The list of parameters to be added.
           */
          public void insertParam(int pos, Object[] args)
          {
             if (parameters == null)
             {
                maxParamIndex = args.length;
                parameters = new Object[maxParamIndex];
                System.arraycopy(args, 0, parameters, 0, maxParamIndex);
                return;
             }

             int oldnPar = parameters.length;
             Object[] newParams = new Object[oldnPar + args.length];

             int np = 0;
             for (int i = 0; i < pos; i++)
             {
                newParams[np++] = parameters[i];
             }

             for (Object s: args)
             {
                newParams[np++] = s; // new character(s);
             }

             for (int i = pos; i < oldnPar; i++)
             {
                newParams[np++] = parameters[i];
             }

             parameters = newParams;
             maxParamIndex += args.length;
          }

          /**
           * Returns the cardinality of the parameter on a specific position.
           *
           * @param   pos
           *          The position of the parameter to be analysed.
           *
           * @return  The cardinality of the parameter. That is 1 for all scalars, the size of the list or length
           *          of an array.
           */
          public int getCardinality(int pos)
          {
             if (parameters == null)
             {
                return 1;
             }

             Object ufo = parameters[pos];

             if (ufo == null)
             {
                return 1; // faster than wait for last if branch
             }
             else if (ufo.getClass().isArray())
             {
                return Array.getLength(ufo);
             }
             else if (ufo instanceof List)
             {
                return ((List<?>) ufo).size();
             }

             return 1;
          }

          /**
           * Get a short representation of the object in String format, used for debugging purposes.
           *
           * @return  short representation of the object.
           */
          @Override
          public String toString()
          {
             StringBuilder sb = new StringBuilder("QueryParams{");
             sb.append(maxParamIndex + 1).append(": ");

             for (int i = 0; i <= maxParamIndex; i++)
             {
                Object p = parameters[i];
                if (p == null)
                {
                   sb.append("null, ");
                   continue;
                }

                boolean isStr = p instanceof Text || p instanceof String;
                if (isStr)
                {
                   sb.append("'");
                }
                if (p instanceof BaseDataType)
                {
                   sb.append(((BaseDataType) p).toStringMessage());
                }
                else
                {
                   sb.append(p);
                }
                if (isStr)
                {
                   sb.append("'");
                }

                if (i != maxParamIndex)
                {
                   sb.append(", ");
                }
             }
             sb.append("}");

             return sb.toString();
          }
       }
    }