# FQLHelper.java

    /*
    ** Module   : FQLHelper.java
    ** Abstract : Helper object for random access queries
    **
    ** Copyright (c) 2005-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --JPRM-- -----------------------------------Description-----------------------------------
    ** 001 ECF 20051007   @23251 Created initial version. Generates all HQL
    **                           submitted on behalf of random access queries.
    **                           Augments the base where clause of a query to
    **                           provide the single-record retrieval and
    **                           navigation semantic of Progress FIND and FOR
    **                           queries.
    ** 002 ECF 20051111   @23379 Cleaned up exception handling. Removed throws
    **                           clauses where possible, due to RecordBuffer
    **                           changes.
    ** 003 ECF 20060114   @23916 Removed references to NameConverter. This is
    **                           a conversion-only class. Use StringHelper
    **                           instead.
    ** 004 ECF 20060204   @24357 Added temp table support. Account for temp
    **                           table multiplexing in HQL statements.
    ** 005 ECF 20060306   @25021 Added support for foreign relation joins.
    **                           Requires additional preprocessing of the
    **                           where clause to inject a join phrase.
    ** 006 ECF 20060316   @25099 Integrated HQLPreprocessor. Rewrites certain
    **                           base where clauses before augmenting them.
    **                           Targets for preprocessing are where clauses
    **                           which reference properties of a composite
    **                           DMO inner class stored in an associated list.
    ** 007 ECF 20060529   @26592 Fixed generation of augmented where clauses
    **                           for equality matches of text. These were not
    **                           being properly uppercased.
    ** 008 ECF 20060602   @26941 Removed select clause for DMO ID. Persistence
    **                           class has been optimized to retrieve records
    **                           in a single step, rather than querying the ID
    **                           first, then loading in a second step.
    ** 009 ECF 20060712   @28048 Migrated utility code to DBUtils class.
    ** 010 ECF 20060728   @28266 Rolled back #008 (@26941). Required for more
    **                           reliable locking. The use of the second level
    **                           cache should negate any loss of performance.
    ** 011 ECF 20060802   @28351 Moved inner class SortComponent to its own
    **                           top-level class, SortCriterion. Related
    **                           functionality in the HQLHelper constructor
    **                           also migrated to the new class.
    ** 012 ECF 20060831   @29086 Simplified retrieval of schema name in
    **                           factory method obtain().
    ** 013 ECF 20070228   @32244 Enable differentiation between ID-only
    **                           projection queries and full queries. The
    **                           former returns only the primary key ID of a
    **                           record. The latter fetches the full DMO.
    ** 014 ECF 20070328   @32644 Changed to support HQLPreprocessor caching.
    **                           Use factory method to retrieve an instance of
    **                           the preprocessor rather than invoking the
    **                           constructor directly.
    ** 015 ECF 20070502   @33369 Changed to support HQLPreprocessor changes.
    **                           The signature of that class' primary factory
    **                           has changed.
    ** 016 ECF 20070827   @35000 Optimized certain temp table queries.
    **                           Eliminate multiplex ID check from where
    **                           clause where possible and hard code multiplex
    **                           ID when this test is necessary, rather than
    **                           using a query substitution parameter. Augment
    **                           order by clauses with leading multiplex ID.
    ** 017 ECF 20071206   @36268 Added sortIndex field and getSortIndex()
    **                           method. Tracks SortIndex used by associated
    **                           queries. Integrated generics.
    ** 018 ECF 20080310   @37475 Added args field. Needed by HQLPreprocesor to
    **                           derive data type information for query
    **                           substitution parameters.
    ** 019 ECF 20080403   @38149 Minor cleanup. Removed dmoIface from instance
    **                           variables and method signatures.
    ** 020 SVL 20080421   @38081 The type of the "join" field was changed from
    **                           DynamicJoin to AbstractJoin.
    ** 021 ECF 20080606   @38605 Added support for parallel dirty database HQL
    **                           in HQLBundles. This is required when there
    **                           are different dialects in use between the
    **                           primary and dirty database.
    ** 022 ECF 20081009   @40077 Integrated new support for inlining query
    **                           substitution parameters. Queries supported by
    **                           this class don't actually have their
    **                           parameters inlined, but changes were needed
    **                           to integrate HQLPreprocessor API changes and
    **                           the ParameterIndices class.
    ** 023 ECF 20081107   @40404 Moved multiplex ID position in order by
    **                           clause. It used to be the first element, but
    **                           that sometimes resulted in a less efficient
    **                           index being selected for a query plan. Now it
    **                           is either the last element, or second to last
    **                           (if the primary key is part of the clause).
    ** 024 SVL 20090118   @41168 HQL helpers which have inlined preprocessors
    **                           are not stored in cache.
    ** 025 SVL 20090122   @41197 Do not look into cache if we have a query
    **                           with unknown parameter(s).
    ** 026 ECF 20090302   @41596 Fixed order by clause for temp tables. In
    **                           cases where both multiplex ID and primary key
    **                           ID were needed, the order of these two did
    **                           not match their order in temp table indices.
    **                           Also reduced memory use of HQLBundle cache.
    ** 027 GES 20090424   @41940 Import change.
    ** 028 ECF 20090602   @42583 Expose primary HQLPreprocessor.
    ** 029 ECF 20090623   @42942 Partially backed out #016 (@35000). Replaced hard
    **                           coded multiplex IDs with query substitution
    **                           parameter placeholders. Some use cases generate
    **                           many similar queries which are differentiated
    **                           only by the hard coded multiplex IDs. This was
    **                           bloating the query cache in the Persistence
    **                           Context class.
    ** 030 ECF 20131001          Added metadata support.
    ** 031 SVL 20140210          Use HQLExpression instead of HQL string.
    ** 032 ECF 20150302          Reworked caching and replaced Apache commons logging with J2SE
    **                           standard logging.
    ** 033 ECF 20150315          Fixed regression in #032; removed indirect reference to RecordBuffer
    **                           via AbstractJoin. We no longer store any information specific to a
    **                           particular buffer instance.
    ** 034 ECF 20150801          Retrofitted HQLPreprocessor.get API changes.
    ** 035 EVL 20160223          Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
    ** 036 ECF 20160225          Changes required by PropertyHelper rewrite. Use DMO entity instead
    **                           of implementation class when composing query strings.
    ** 037 OM  20160629          HQLPreprocessor needs the list of fields of the current index.
    ** 038 OM  20160715          Addded isFindTemplate() method.
    ** 039 ECF 20160720          Fixed buffer lookup during HQL preprocessing.
    ** 040 ECF 20160901          Added safety code to return a bogus helper object from obtain() when
    **                           we have a fatal error preprocessing HQL; avoids NPE downstream.
    ** 041 OM  20171212          HQLPreprocessor.get() signature change.
    ** 042 CA  20191211          The SortCriterion must know the database, to be able to use the
    **                           correct UDF, in case of datetime-tz fields.
    ** 043 ECF 20200906          New ORM implementation.
    ** 044 OM  20201007          Optimized SortCriterion by using DmoMeta data instead of map lookups.
    **     ECF 20210506          Use RecordBuffer.getDmoInfo() getter instead of direct field access to
    **                           prevent NPE in proxy case.
    **     CA  20221006          Added JMX instrumentation for FQL prepare (in 'obtain'). Refs #6814
    **     OM  20221103          Class renamed to FQLHelper.
    ** 045 OM  20230404          The join navigation key may contain null/unknown values.
    ** 046 GBB 20230512          Logging methods replaced by CentralLogger/ConversionStatus.
    ** 047 RAA 20230525          In createBundle, the orderBy clause will be null in FIRST/LAST case if a
    **                           unique finding is encountered.
    ** 048 HC  20240222          Enabled JMX on FWD Client.
    ** 049 OM  20240909          Improved Database API.
    ** 050 TJD 20240705          Support for direct control of NULL supporting FQL queries
    ** 051 ES  20241017          Cache the FQLBundles only if all the elements of a join predicate are
    **                           fully known.
    ** 052 RNC 20241125          Used LinkedHashSet instead of HashSet in constructor to preserve
    **                           the ordering of the indexed fields.
    ** 053 LS  20250415          Added dmoMeta and included/excluded properties.
    **                           Updated the signature of obtain() and constructor accordingly.
    **                           Changed assembleSelectClause() to honor the FIELDS/EXCEPT clause.
    */

    /*
    ** 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;

    import java.lang.reflect.*;
    import java.util.*;
    import java.util.logging.*;
    import com.goldencode.util.*;
    import com.goldencode.p2j.jmx.*;
    import com.goldencode.p2j.persist.dialect.*;
    import com.goldencode.p2j.persist.meta.*;
    import com.goldencode.p2j.persist.orm.DmoMeta;
    import com.goldencode.p2j.persist.orm.Property;
    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.util.ErrorManager;
    import com.goldencode.p2j.util.logging.CentralLogger;

    /**
     * Helper class which provides services to random access style queries.
     * These include composing FQL statements for all possible query navigation
     * and creating an array of Hibernate <code>Type</code> objects to match a
     * substitution parameter array.
     * <p>
     * This class is the work horse behind {@link RandomAccessQuery}, generating
     * one or more FQL select statements for each type of navigation/retrieval
     * request issued by that query.  An instance of {@link FQLBundle} is created
     * lazily for each such command type received from the query.  These instances
     * are cached to speed subsequent lookups within the current context.
     * <p>
     * Given a {@link RecordBuffer}, an optional where clause, an optional sort
     * clause, an optional {@link DynamicJoin}, and an indicator whether this
     * query is a projection query which fetches only the primary key of a
     * record, this class will:
     * <ul>
     *   <li>{@link FQLPreprocessor preprocess} the where clause to account for
     *       any implicit ANSI joins necessary to resolve certain complex uses of
     *       subscripts;
     *   <li>augment the base where clause if necessary to account for temporary
     *       table semantics;
     *   <li>augment the base where clause if necessary to embed any natural,
     *       foreign key joins;
     *   <li>analyze the sort clause in order to:
     *   <ul>
     *     <li>create an <code>order by</code> clause for the query;
     *     <li>augment the base where clause to allow ordered, single record
     *         navigation and retrieval;
     *   </ul>
     *   <li>lazily create an {@link FQLBundle} instance for each type of
     *       navigation/retrieval request processed by the query;
     *   <li>populate each <code>FQLBundle</code> instance with the full FQL
     *       statement(s), accessor method(s), and query substitution parameter
     *       indices needed to service a particular navigation/retrieval request.
     * </ul>
     */
    final class FQLHelper
    implements QueryConstants
    {
       /** Logger */
       private static final CentralLogger LOG = CentralLogger.get(FQLHelper.class.getName());

       /** Instrumentation for {@link #obtain}. */
       private static final NanoTimer FQL_HELPER = NanoTimer.getInstance(FwdServerJMX.TimeStat.OrmHqlHelperParse);

       /** Cache of all active helper objects in the current context */
       private static final FQLHelperCache cache = new FQLHelperCache();

       /** Base where clause for associated query */
       private final String where;

       /** FQL where clause preprocessor */
       private final FQLPreprocessor fqlPreprocessor;

       /** FQL where clause preprocessor, using dirty database dialect */
       private final FQLPreprocessor altPreprocessor;

       /** DMO entity name used by query */
       private final String dmoEntity;

       /** Alias used to represent DMO instance in FQL statements */
       private final String dmoAlias;

       /** Meta information of the DMO associated with the buffer */
       private final DmoMeta dmoMeta;

       /** Whether query should return primary key ID only, or entire DMO */
       private final boolean idOnly;

       /** Are multiplex semantics required? */
       private final boolean multiplexed;

       /** Does sort clause need to be augmented with multiplex ID? */
       private final boolean multiplexSort;

       /** FQL expression for dynamic join via a foreign key relation */
       private final FQLExpression[][] joinFQL;

       /** Components of a parsed order by clause */
       private final List<SortCriterion> sortCriteria;

       /** Components of a parsed order by clause, using dirty database dialect */
       private final List<SortCriterion> altCriteria;

       /** Cache of FQL bundles, indexed by retrieval request type constant */
       private final FQLBundle[] bundleCache = new FQLBundle[5];

       /** Sort index used by query */
       private final SortIndex sortIndex;

       /** The included properties for each DMO (FIELDS option). */
       private Property[] included = null;

       /** The excluded properties for each DMO (EXCEPT option). */
       private Property[] excluded = null;

       /** Order by clause for query, adjusted for navigation direction */
       private String orderBy = null;

       /** Order by clause for dirty database use */
       private String altOrderBy = null;

       /**
        * Constructor which sets fundamental data upon which final FQL statements will be based.
        * Parses the base order by clause {@code sort} into its property and directional components,
        * which are later used to augment the base where clause for single-record retrievals.
        *
        * @param   buffer
        *          Record buffer against which the current query operates.
        * @param   referencedBuffers
        *          All buffers referenced by the where clause.
        * @param   schema
        *          Name of schema associated with query's buffer.
        * @param   dmoAlias
        *          Alias used to represent DMO instance in FQL statements.
        * @param   idOnly
        *          If {@code true}, the FQL statements generated by this object will begin with a
        *          select clause which specifies only the record's primary key identifier;  if
        *          {@code false}, no select clause will be prepended.
        * @param   joinFQL
        *          FQL expression snippet for a join via a foreign key relation.  May be {@code null}.
        * @param   where
        *          Base where clause for associated query.
        * @param   sort
        *          Base order by clause for associated query.
        * @param   included
        *          The included properties.
        * @param   excluded
        *          The excluded properties.
        * @param   args
        *          Query substitution parameters.
        *
        * @throws  PersistenceException
        *          if there is any error parsing the given {@code sort} clause.
        */
       private FQLHelper(RecordBuffer buffer,
                         RecordBuffer[] referencedBuffers,
                         String schema,
                         String dmoAlias,
                         boolean idOnly,
                         FQLExpression[][] joinFQL,
                         String where,
                         String sort,
                         Property[] included,
                         Property[] excluded,
                         Object[] args)
       throws PersistenceException
       {
          this.dmoEntity = buffer.getEntityName();
          this.dmoAlias = dmoAlias;
          this.dmoMeta = buffer.getDmoInfo();
          this.idOnly = idOnly;
          this.joinFQL = joinFQL;
          this.where = where;
          this.sortCriteria = SortCriterion.parse(sort, buffer);
          this.included = included;
          this.excluded = excluded;
          Set<String> indexedFields = new LinkedHashSet<>(sortCriteria.size());
          for (SortCriterion criterion : this.sortCriteria)
          {
             indexedFields.add(criterion.getName());
          }

          // if dirty database dialect differs from that of primary dialect, we must generate an alternative
          // SortCriteria list and FQLPreprocessor (non-temp-tables only)
          Database primaryDB = buffer.getDatabase();
          Database dirtyDB = primaryDB.toType(Database.Type.DIRTY);
          Dialect primaryDialect = DatabaseManager.getDialect(primaryDB);
          Dialect dirtyDialect = DatabaseManager.getDialect(dirtyDB);

          this.fqlPreprocessor = FQLPreprocessor.get(where,
                                                     primaryDB,
                                                     primaryDialect,
                                                     referencedBuffers,
                                                     args,
                                                     null,
                                                     false,
                                                     null,
                                                     null,
                                                     false,
                                                     indexedFields);
          this.sortIndex = SortIndex.get(sort, buffer);
          boolean hasWhere = (where != null && where.length() > 0);

          // multiplex ID semantics are required in the where clause if we have either a multiplexed temp table,
          // or we have a where clause in a temp table query
          this.multiplexed = (buffer.isMultiplexed() || hasWhere) && buffer.getMultiplexID() != null;

          // the order by clause must be augmented with a multiplex ID for any temp table query
          this.multiplexSort = (buffer.getMultiplexID() != null);

          if (buffer.isTemporary() || dirtyDialect == null || dirtyDialect.equals(primaryDialect))
          {
             this.altCriteria = this.sortCriteria;
             this.altPreprocessor = this.fqlPreprocessor;
          }
          else
          {
             this.altCriteria = SortCriterion.parse(dirtyDialect, sort, dmoAlias, dmoMeta, true);
             this.altPreprocessor = fqlPreprocessor.get(where,
                                                        dirtyDB,
                                                        dirtyDialect,
                                                        referencedBuffers,
                                                        args,
                                                        null,
                                                        false,
                                                        null,
                                                        null,
                                                        false,
                                                        indexedFields);
          }
       }

       /**
        * Obtain an instance of an {@code FQLHelper} for the given buffer and query criteria. The
        * instance is obtained from the cache if present, otherwise, it is created and cached.
        *
        * @param   buffer
        *          Record buffer against which the current query operates.
        * @param   referencedBuffers
        *          All buffers referenced by the where clause.
        * @param   args
        *          Query substitution parameters.
        * @param   idOnly
        *          If {@code true}, the FQL statements generated by this object will begin with a
        *          select clause which specifies only the record's primary key identifier;  if
        *          {@code false}, no select clause will be prepended.
        * @param   where
        *          Base where clause supplied to the current query.
        * @param   sort
        *          Base order by clause supplied to the current query.
        * @param   join
        *          Helper object for a join via a foreign key relation.  May be {@code null}.
        * @param   included
        *          The included properties.
        * @param   excluded
        *          The excluded properties.
        *
        * @return  A new or cached instance of this class, specific to the given buffer and query
        *          criteria.
        */
       static FQLHelper obtain(RecordBuffer buffer,
                               RecordBuffer[] referencedBuffers,
                               Object[] args,
                               boolean idOnly,
                               String where,
                               String sort,
                               AbstractJoin join,
                               Property[] included,
                               Property[] excluded)
       {
          Class<? extends DataModelObject> joinIface = (join == null ? null : join.getInverseInterface());
          FqlType[] types = (args != null) ? DBUtils.makeTypeArray(args) : new FqlType[] {};

          // resolve all parameters and check whether we have any unknown value(s)
          boolean unknowns = false;
          if (args != null)
          {
             for (Object parm : args)
             {
                if (parm instanceof FieldReference)
                {
                    parm = ((FieldReference) parm).resolve();
                }

                if (parm instanceof BaseDataType && ((BaseDataType) parm).isUnknown())
                {
                   unknowns = true;
                }
             }
          }

          FQLHelper helper = null;
          Class<? extends DataModelObject> dmoIface = buffer.getDMOInterface();

          if (!unknowns)
          {
             helper = cache.get(dmoIface, types, idOnly, where, sort, joinIface, included, excluded);
          }

          if (helper == null)
          {
             String schema = buffer.getSchema();
             String dmoAlias = buffer.getDMOAlias();
             String fwhere = StringHelper.safeTrim(where);
             FQLHelper[] res = new FQLHelper[1];

             FQL_HELPER.timer(() ->
             {
                try
                {
                   res[0] = new FQLHelper(buffer,
                                          referencedBuffers,
                                          schema,
                                          dmoAlias,
                                          idOnly,
                                          (join != null ? join.getFQL() : null),
                                          fwhere,
                                          sort,
                                          included,
                                          excluded,
                                          args);

                   if (!res[0].fqlPreprocessor.wasInlined())
                   {
                      cache.put(dmoIface, types, idOnly, fwhere, sort, joinIface, res[0], included, excluded);
                   }
                }
                catch (PersistenceException exc)
                {
                   ErrorManager.recordOrThrowError(exc);

                   // in the event of a fatal error preparing the FQL, we need a bogus helper which
                   // will not load a record, but which we can use to avoid NPE downstream
                   try
                   {
                      res[0] = new FQLHelper(buffer,
                                             referencedBuffers,
                                             schema,
                                             dmoAlias,
                                             idOnly,
                                             (join != null ? join.getFQL() : null),
                                             "false",
                                             sort,
                                             included,
                                             excluded,
                                             args);
                   }
                   catch (PersistenceException exc2)
                   {
                      // not much else we can do; NPE here we come...
                      if (LOG.isLoggable(Level.WARNING))
                      {
                         LOG.log(Level.WARNING, "Error preparing FQL", exc2);
                      }
                   }
                }
             });

             helper = res[0];
          }

          return helper;
       }

       /**
        * Return the sort index used by the helper's query.
        *
        * @return  Sort index.
        */
       SortIndex getSortIndex()
       {
          return sortIndex;
       }

       /**
        * Return the primary FQL preprocessor used for the underlying query's where clause.
        *
        * @return  FQL preprocessor.
        */
       FQLPreprocessor getFQLPreprocessor()
       {
          return fqlPreprocessor;
       }

       /**
        * Create a bundle containing a single FQL statement and no getter methods,
        * designed to retrieve the first record which matches the base where
        * clause criteria.  Which record is first is determined by the sort order
        * specified in the base order by clause.
        * <p>
        * No augmented FQL statements are produced for the bundle.
        * <p>
        * The resulting bundle is cached within the current context;  subsequent
        * requests return the cached bundle.
        *
        * @param   key
        *          The bitset key for join clause (set bits are not null values).
        *
        * @return  FQL bundle containing the information necessary to retrieve the first record.
        */
       synchronized FQLBundle first(BitSet key)
       {
          return createBundle(FIRST, key);
       }

       /**
        * Create a bundle containing a single FQL statement and no getter methods,
        * designed to retrieve the last record which matches the base where
        * clause criteria.  Which record is last is determined by the sort order
        * specified in the base order by clause.
        * <p>
        * No augmented FQL statements are produced for the bundle.
        * <p>
        * The resulting bundle is cached within the current context;  subsequent
        * requests return the cached bundle.
        *
        * @param   key
        *          The bitset key for join clause (set bits are not null values).
        *
        * @return  FQL bundle containing the information necessary to retrieve the last record.
        */
       synchronized FQLBundle last(BitSet key)
       {
          return createBundle(LAST, key);
       }

       /**
        * Create a bundle containing the FQL statement(s) and getter method(s)
        * necessary to retrieve the next record which matches the base where
        * clause criteria.  Which record is next is determined by the sort order
        * specified in the base order by clause, along with the sort-relevant
        * data in the current record of the associated buffer.
        * <p>
        * One or more augmented FQL statements are produced for the bundle.
        * There will be one getter method per statement.  The statements are
        * ordered from most to least specific.  Refer to the class description of
        * {@link FQLBundle} for further implementation details.
        * <p>
        * The resulting bundle is cached within the current context;  subsequent
        * requests return the cached bundle.
        *
        * @param   key
        *          The bitset key for join clause (set bits are not null values).
        *
        * @return  FQL bundle containing the information necessary to retrieve the next record.
        */
       synchronized FQLBundle next(BitSet key)
       {
          FQLBundle bundle = bundleCache[NEXT];
          if (bundle == null)
          {
             bundle = new FQLBundle();

             // Use ascending sort.
             orderBy = buildSortClause(false, sortCriteria);

             // Augment base where clause;  one variant per sort component.
             // Store DMO getter methods associated with substitution parameters.
             fillBundleLists(bundle, false, sortCriteria, true, key);

             // Final processing to complete FQL statement.
             postProcessBundle(bundle, true);

             // If necessary, augment FQL bundle with parallel statements that are
             // compatible with the dirty database's dialect.
             if (altCriteria != sortCriteria)
             {
                altOrderBy = buildSortClause(false, altCriteria);
                fillBundleLists(bundle, false, altCriteria, false, key);
                postProcessBundle(bundle, false);
             }

             bundle.freeze();
             if (key == null)
             {
                bundleCache[NEXT] = bundle;
             }

             if (LOG.isLoggable(Level.FINE))
             {
                LOG.log(Level.FINE, "NEXT:  " + bundle);
             }
          }

          return bundle;
       }

       /**
        * Create a bundle containing the FQL statement(s) and getter method(s)
        * necessary to retrieve the previous record which matches the base where
        * clause criteria.  Which record is previous is determined by the sort
        * order specified in the base order by clause, along with the
        * sort-relevant data in the current record of the associated buffer.
        * <p>
        * One or more augmented FQL statements are produced for the bundle.
        * There will be one getter method per statement.  The statements are
        * ordered from most to least specific.  Refer to the class description of
        * {@link FQLBundle} for further implementation details.
        * <p>
        * The resulting bundle is cached within the current context;  subsequent
        * requests return the cached bundle.
        *
        * @param   key
        *          The bitset key for join clause (set bits are not null values).
        *
        * @return  FQL bundle containing the information necessary to retrieve the previous record.
        */
       synchronized FQLBundle previous(BitSet key)
       {
          FQLBundle bundle = bundleCache[PREVIOUS];
          if (bundle == null)
          {
             bundle = new FQLBundle();

             // Use ascending sort.
             orderBy = buildSortClause(true, sortCriteria);

             // Augment base where clause;  one variant per sort component.
             // Store DMO getter methods associated with substitution parameters.
             fillBundleLists(bundle, true, sortCriteria, true, key);

             // Final processing to complete FQL statement.
             postProcessBundle(bundle, true);

             // If necessary, augment FQL bundle with parallel statements that are
             // compatible with the dirty database's dialect.
             if (altCriteria != sortCriteria)
             {
                altOrderBy = buildSortClause(true, altCriteria);
                fillBundleLists(bundle, true, altCriteria, false, key);
                postProcessBundle(bundle, false);
             }

             bundle.freeze();
             if (key == null)
             {
                bundleCache[PREVIOUS] = bundle;
             }

             if (LOG.isLoggable(Level.FINE))
             {
                LOG.log(Level.FINE, "PREVIOUS:  " + bundle);
             }
          }

          return bundle;
       }

       /**
        * Create a bundle containing a single FQL statement and no getter methods, designed to retrieve a single,
        * unique record which matches the base where clause criteria. Since only a single record is sought,
        * sorting is irrelevant.
        * <p>
        * If the join key is {@code null}, the FQL statements returned are not augmented. Otherwise, the returned
        * bundle contains statements augmented for matching 4GL {@code unknown} semantics.
        * <p>
        * The resulting bundle is cached within the current context if the join component is not present or if all
        * fields from the inverse are not null; subsequent requests return the cached bundle in these conditions.
        *
        * @param   key
        *          The bitset key for join clause (set bits are not null values).
        *
        * @return  FQL bundle containing the information necessary to retrieve a unique record.
        */
       synchronized FQLBundle unique(BitSet key)
       {
          return createBundle(UNIQUE, key);
       }

       /**
        * Create a bundle containing a single FQL statement and no getter methods, designed to retrieve the last,
        * the first or a single, unique record which matches the base where clause criteria.
        * See {@link #first(BitSet)}, {@link #last(BitSet)} or {@link #unique(BitSet)} for each specific case.
        *
        * @param   type
        *          Target bundle type. Can be {@link #FIRST}, {@link #LAST} or {@link #UNIQUE}.
        * @param   key
        *          The bitset key for join clause (set bits are not null values).
        *
        * @return  FQL bundle containing the information necessary to retrieve the target record.
        */
       private FQLBundle createBundle(int type, BitSet key)
       {
          FQLBundle bundle = (key == null) ? bundleCache[type] : null;
          if (bundle == null)
          {
             bundle = new FQLBundle();

             switch (type)
             {
                case FIRST:
                   // Use ascending sort.
                   orderBy = fqlPreprocessor.isUniqueFind(true) ? null : buildSortClause(false, sortCriteria);
                   break;
                case LAST:
                   // Use descending sort.
                   orderBy = fqlPreprocessor.isUniqueFind(true) ? null : buildSortClause(true, sortCriteria);
                   break;
                case UNIQUE:
                   // Null out order by clause.
                   orderBy = null;
                   break;
                default:
                   throw new IllegalArgumentException("Bundle type should be FIRST, LAST or UNIQUE!");
             }

             // use base where clause; depending on the join substitution values, augmentation is performed.
             // if [joinFQL] is not in use, just add multiplex support if temp table.
             FQLExpression whereClause = preprocessWhere(true, fqlPreprocessor, key);
             String pWhere = whereClause != null ? whereClause.toFinalExpression() : null;
             bundle.addStatement(pWhere, false);

             // Final processing to complete FQL statement.
             postProcessBundle(bundle, true);

             if (fqlPreprocessor != altPreprocessor)
             {
                switch (type)
                {
                   case FIRST:
                      altOrderBy = buildSortClause(false, altCriteria);
                      break;
                   case LAST:
                      altOrderBy = buildSortClause(true, altCriteria);
                      break;
                   case UNIQUE:
                      altOrderBy = null;
                      break;
                }

                whereClause = preprocessWhere(true, altPreprocessor, key);
                pWhere = whereClause != null ? whereClause.toFinalExpression() : null;
                bundle.addStatement(pWhere, true);
                postProcessBundle(bundle, false);
             }

             bundle.freeze();
             if (key == null)
             {
                bundleCache[type] = bundle;
             }

             if (LOG.isLoggable(Level.FINE))
             {
                StringBuilder sb = new StringBuilder();
                switch (type)
                {
                   case FIRST:
                      sb.append("FIRST");
                      break;
                   case LAST:
                      sb.append("LAST");
                      break;
                   case UNIQUE:
                      sb.append("UNIQUE");
                      break;
                }
                sb.append(":  ");
                sb.append(bundle.toString());
                LOG.log(Level.FINE, sb.toString());
             }
          }

          return bundle;
       }

       /**
        * Test whether the predicate of this query looks up for the template record (a direct access
        * using rowid/recid of the table using a negative index).
        * <p>
        * The current implementation only identifies simple form where the predicate is composed from
        * a single equality test between the rowid/recid of the record and a negative value (as P2J
        * convention). This is enough for supporting the standard idiom for loading template records
        * in P4GL.
        * <p>
        * More complex queries can be defined using AND/OR clauses in the WHERE predicate but they are
        * not supported by P2J. They do not make sense since, if if the template record exists for a
        * table, it is well defined only by it rowid/recid and immutable.
        *
        * @param   queryParams
        *          The current parameter list for the query.
        * @param   dmoClass
        *          The dmo class. We verify whether this is indeed the correct template id for
        *          respective table.
        *
        * @return  {@code true} if the query should load the template record for the buffer.
        */
       boolean isFindTemplate(Object[] queryParams, Class<? extends Record> dmoClass)
       {
          if (fqlPreprocessor == null || !fqlPreprocessor.isFindByRowid())
          {
             return false;
          }

          long queryRowid = fqlPreprocessor.getFindByRowid(queryParams);
          if (queryRowid >= 0)
          {
             return false;
          }

          Long templateRowid = MetadataManager.getTemplateRowid(dmoClass);
          return templateRowid != null && templateRowid == queryRowid;
       }

       /**
        * Preprocess the where clause to account for temp table multiplexing and
        * foreign relation joins.  This is performed on the intermediate form of
        * the base where clause as created by the {@link FQLPreprocessor}, but
        * before it is augmented to account for the sort criteria in {@link #fillBundleLists}.
        * <p>
        * If multiplexing is called for, this will prepend a test for the proper
        * multiplex ID before the where clause, if any.
        * <p>
        * If a foreign relation join is called for, the proper FQL will be
        * generated to compare a local entity with a foreign one (the foreign
        * entity is represented by a query substitution character:
        * <code>?</code>).
        *
        * @param   needWhere
        *          {@code true} if "where" prefix should be added.
        * @param   preproc
        *          FQL preprocessor to use for the base where clause.
        * @param   key
        *          The bitset key for join clause (set bits are not null values).
        *
        * @return  The preprocessed where clause or {@code null} if no where clause was generated.
        */
       private FQLExpression preprocessWhere(boolean needWhere, FQLPreprocessor preproc, BitSet key)
       {
          boolean hasWhere = (where != null && where.length() > 0);
          boolean hasJoin = (joinFQL != null);

          if (!multiplexed && !hasWhere && !hasJoin)
          {
             // No preprocessing required.
             return null;
          }

          FQLExpression whereClause = new FQLExpression();

          if (needWhere)
          {
             whereClause.append("where ");
          }

          // Handle temp table multiplexing.
          if (multiplexed)
          {
             if (hasWhere || hasJoin)
             {
                whereClause.append("(");
             }

             whereClause.append(dmoAlias);
             whereClause.append(".");
             whereClause.append(TemporaryBuffer.MULTIPLEX_FIELD_NAME);
             whereClause.append(" = ", true);

             if (hasWhere || hasJoin)
             {
                whereClause.append(") and ");
             }
          }

          // Handle a join.
          if (hasJoin)
          {
             if (multiplexed || hasWhere)
             {
                whereClause.append("(");
             }

             for (int i = 0; i < joinFQL.length; i++)
             {
                boolean isNull = (key != null) && !key.get(i);
                whereClause.append(joinFQL[i][isNull ? AbstractJoin.NULL_FIELD : AbstractJoin.NOT_NULL_FIELD]);
             }

             if (multiplexed || hasWhere)
             {
                whereClause.append(")");
             }
             if (hasWhere)
             {
                whereClause.append(" and ");
             }
          }

          // Embed the base where clause, if any.
          if (hasWhere)
          {
             if (multiplexed || hasJoin)
             {
                whereClause.append("(");
             }
             whereClause.append(preproc.getFQL());
             if (multiplexed || hasJoin)
             {
                whereClause.append(")");
             }
          }

          return whereClause;
       }

       /**
        * Post-process the augmented where clauses stored during the first pass
        * of FQL bundle creation.  Post-processing involves prepending each where
        * clause with select and from clauses, and appending with an optional
        * order by clause.  The original where clauses in the bundle are replaced
        * with the resulting, full FQL statements.
        *
        * @param   bundle
        *          FQL bundle to be post-processed.
        * @param   primary
        *          <code>true</code> to process FQL statement for primary database
        *          dialect;  <code>false</code> to process for dirty database
        *          dialect.
        */
       private void postProcessBundle(FQLBundle bundle, boolean primary)
       {
          int size = bundle.statementsSize();
          for (int i = 0; i < size; i++)
          {
             StringBuilder buf = new StringBuilder();

             assembleSelectClause(buf);
             assembleFromClause(buf);

             String where = bundle.getStatement(i, !primary);
             if (where != null)
             {
                buf.append(where);
                buf.append(" ");
             }

             if (primary)
             {
                assembleOrderByClause(buf, orderBy);
                bundle.setStatement(i, buf.toString().trim(), false);
             }
             else
             {
                assembleOrderByClause(buf, altOrderBy);
                bundle.setStatement(i, buf.toString().trim(), true);
             }
          }

          // Store query substitution parameter indices.
          // TODO:  currently, these are shared, and not dialect specific, but only because we do not inline
          //        parameters from this class.  Were this to change, we would need to maintain separate
          //        [ParameterIndices] objects for the primary and dirty database statements.
          if (primary)
          {
             bundle.setParameterIndices(fqlPreprocessor.getParameterIndices());
          }
       }

       /**
        * Compose an FQL compliant order by clause from the sort components parsed from the base order by clause
        * submitted at construction, optionally inverting the sort direction of each component.  The list of
        * original components will have been augmented with the DMO's primary key, if this is necessary to
        * disambiguate among non-unique records.
        *
        * @param   invert
        *          {@code true} to invert sort direction (i.e., {@code asc-->desc}, {@code desc-->asc});
        *          else {@code false}.
        * @param   sortCrit
        *          List of {@code SortCriterion} objects appropriate to the database dialect.
        *
        * @return  An FQL compliant order by clause.
        */
       private String buildSortClause(boolean invert, List<SortCriterion> sortCrit)
       {
          StringBuilder buf = new StringBuilder("order by ");

          // inject multiplex ID for temp table before primary key, so as to match the way these components are
          // ordered in the table's index.
          if (multiplexSort)
          {
             injectMultiplexSort(invert, buf);
             buf.append(", ");
          }

          int i = 0;
          Iterator<SortCriterion> iter = sortCrit.iterator();
          for (; iter.hasNext(); i++)
          {
             if (i > 0)
             {
                buf.append(", ");
             }
             SortCriterion next = iter.next();

             buf.append(next.toSortExpression(invert));
          }

          return buf.toString();
       }

       /**
        * Inject a sort expression into the given buffer using the well-known
        * multiplex ID field name with the appropriate DMO alias, and a sort
        * direction.
        *
        * @param   invert
        *          If <code>true</code> the sort direction is descending;  if
        *          <code>false</code>, it is ascending.
        * @param   buf
        *          String builder into which the expression is injected.
        */
       private void injectMultiplexSort(boolean invert, StringBuilder buf)
       {
          buf.append(dmoAlias);
          buf.append(".");
          buf.append(TemporaryBuffer.MULTIPLEX_FIELD_NAME);
          buf.append(" ");
          buf.append(invert ? "desc" : "asc");
       }

       /**
        * Fill an FQL bundle's internal FQL statement and getter method lists
        * with the appropriate statements and methods, respectively.  The number
        * of statements and methods correlates directly with the number of sort
        * components managed by the associated query.  The FQL statements are
        * generated and stored in order from most specific to least specific,
        * such that as they are executed, the criteria become decreasingly
        * restrictive.
        * <p>
        * This method represents the first pass in a two step process to create
        * a set of full, FQL statements ({@link #postProcessBundle} represents
        * the second pass).  In this pass, only augmented where clauses (and
        * their associated getter methods) are stored in the bundle.  The base
        * where clause is augmented with a binary match phrase for each sort
        * component, each of which is AND'd together.  The last test in the
        * augmented where clause is always a range match, whereas the previous
        * tests are always equality matches.  The range match operator is
        * determined by the sort direction of the sort component, and the value
        * of the <code>invert</code> parameter.
        * <p>
        * For example, given the following query criteria, with
        * <code>invert</code> set to <code>false</code>...
        * <pre>
        *    DMO:        Customer
        *    alias:      customer
        *    where:      customer.number = ? and customer.creditLimit = ?
        *    order by:   customer.creditLimit asc, customer.name asc
        * </pre>
        * <p>
        * ...this method would produce the following list of augmented where
        * clauses...
        * <pre>
        * 1) where (customer.number = ? and customer.creditLimit = ?)
        *      and (customer.creditLimit = ? and customer.name = ? and customer.recid &gt; ?)
        *
        * 2) where (customer.number = ? and customer.creditLimit = ?)
        *      and (customer.creditLimit = ? and customer.name &gt; ?)
        *
        * 3) where (customer.number = ? and customer.creditLimit = ?)
        *      and (customer.creditLimit &gt; ?)
        * </pre>
        * <p>
        * ...and the following list of DMO "getter" methods...
        * <pre>
        * 1) Customer.getCreditLimit()
        * 2) Customer.getName()
        * 3) Customer.primaryKey()
        * </pre>
        * <p>
        * Note that the above example assumes neither <code>creditLimit</code>
        * nor <code>name</code> (nor the combination of the two) represent a
        * unique constraint.  If this assumption was not valid, the method would
        * produce the following augmented where clauses instead...
        * <pre>
        * 1) where (customer.number = ? and customer.creditLimit = ?)
        *      and (customer.creditLimit = ? and customer.name &gt; ?)
        *
        * 2) where (customer.number = ? and customer.creditLimit = ?)
        *      and (customer.creditLimit &gt; ?)
        * </pre>
        * <p>
        * ...and the following list of DMO "getter" methods...
        * <pre>
        * 1) Customer.getCreditLimit()
        * 2) Customer.getName()
        * </pre>
        * <p>
        * If a temp table is involved in the where clause, and/or if a foreign relation join between tables is
        * required, additional preprocessing of the where clause must occur.
        *
        * @param   bundle
        *          FQL bundle to be populated.
        * @param   invert
        *          {@code true} to invert sort direction (i.e., {@code asc-->desc}, {@code desc-->asc} of each
        *          sort component when generating the range match portion of the augmented where clause;
        *          else {@code false}. Inverting the sort direction in this context means using the opposite range
        *          operator in the match test ({@code >} becomes {@code <} and vice versa).
        * @param   sortCrit
        *          List of {@code SortCriterion} objects appropriate to the database dialect.
        * @param   primary
        *          {@code true} to add generated FQL statement to the FQL bundle for use with the primary
        *          database; {@code false} to add it for use with the dirty database.
        * @param   key
        *          The bitset key for join clause (set bits are not null values).
        *
        * @see     #preprocessWhere
        */
       private void fillBundleLists(FQLBundle bundle,
                                    boolean invert,
                                    List<SortCriterion> sortCrit,
                                    boolean primary,
                                    BitSet key)
       {
          // Store methods.  These are shared between primary and dirty database
          // statements and are not dialect specific.  They must only be added once.
          if (primary)
          {
             Iterator<SortCriterion> iter = sortCrit.iterator();
             while (iter.hasNext())
             {
                SortCriterion next = iter.next();
                Method getter = next.getMethod();
                if (getter != null)
                {
                   bundle.addGetter(getter);
                }
             }
          }

          // Compose where clause variants.
          int size = sortCrit.size();
          FQLExpression prepClause = preprocessWhere(false, (primary ? fqlPreprocessor : altPreprocessor), key);

          // Create one augmented where clause variant for each sort component.
          for (int i = size - 1; i >= 0; i--)
          {
             FQLExpression whereClause = new FQLExpression("where ");

             if (prepClause != null)
             {
                whereClause.append("(");
                whereClause.append(prepClause);
                whereClause.append(") and (");
             }

             for (int j = 0; j <= i; j++)
             {
                SortCriterion next = sortCrit.get(j);
                if (next.getMethod() == null)
                {
                   j--;
                   continue;
                }

                if (j > 0)
                {
                   whereClause.append(" and ");
                }

                whereClause.append(next.toWhereExpression((j == i), invert, !next.isPrimaryKey()));
             }

             if (prepClause != null)
             {
                whereClause.append(")");
             }

             bundle.addStatement(whereClause.toFinalExpression(), !primary);
          }
       }

       /**
        * Assemble the <code>select</code> clause for an FQL query.
        * <ul>
        *    <li>
        *       For cases when {@link #idOnly} is <code>true</code> uses the form
        *       <pre>select {dmo}.recid</pre>
        *    </li>
        *    <li>
        *       Otherwise, honors the FIELDS and EXCEPT clauses.
        *    </li>
        * </ul>

        *
        * @param   buf
        *          String buffer into which clause is assembled.
        */
       private void assembleSelectClause(StringBuilder buf)
       {
          if (idOnly)
          {
             buf.append("select ");
             buf.append(dmoAlias);
             buf.append('.');
             buf.append(DatabaseManager.PRIMARY_KEY);
             buf.append(" ");
          }
          else if (included != null || excluded != null)
          {
             buf.append("select ");

             int size = dmoMeta.getFieldSize();
             Property[] props = new Property[size];
             List<String> indexedFields = new ArrayList<>();

             for (int j = 0; j < sortCriteria.size(); j++)
             {
                String name = sortCriteria.get(j).getUnqualifiedName();
                if (!name.equals(DatabaseManager.PRIMARY_KEY))
                {
                   indexedFields.add(name);
                }
             }
             // only permanent buffers can use include/exclude
             if (included == null)
             {
                // include all fields
                Iterator<Property> piter = dmoMeta.getFields(false);
                while (piter.hasNext())
                {
                   Property p = piter.next();

                   if (p.propId > 0)
                   {
                      props[p.propId - 1] = p;
                   }
                }
             }
             else
             {
                System.arraycopy(included, 0, props, 0, size);
             }

             if (excluded != null)
             {
                // exclude properties
                for (int j = 0; j < size; j++)
                {
                   if (excluded[j] != null) props[j] = null;
                }
             }

             buf.append(dmoAlias).append('.').append(DatabaseManager.PRIMARY_KEY);
             for (int j = 0; j < size; j++)
             {
                Property p = props[j];
                // extents are handled like scalars. The FQL converter will filter them out if necessary,
                // and the SQLQuery will fetch them from secondary tables at hydration time
                if (p == null)
                {
                   continue;
                }
                indexedFields.remove(p.name);
                buf.append(", ").append(dmoAlias).append(".").append(p.name);
             }

             // add the fields included in the used index
             for (int j = 0; j < indexedFields.size(); j++)
             {
                buf.append(", ").append(dmoAlias).append(".").append(indexedFields.get(j));
             }

             buf.append(" ");
          }
       }

       /**
        * Assemble the <code>from</code> clause for an FQL query.  Uses the form:
        * <pre>
        *   from {DmoImpl} as {dmo}
        *   [ join {dmo}.{property} as {dmo}_{property} [...] ]
        * </pre>
        *
        * @param   buf
        *          String buffer into which clause is assembled.
        */
       private void assembleFromClause(StringBuilder buf)
       {
          String entity = dmoEntity.substring(dmoEntity.lastIndexOf(".") + 1);

          buf.append("from ");
          buf.append(entity);
          buf.append(" as ");
          buf.append(dmoAlias);
          buf.append(" ");

          // Add any required ANSI joins created by the FQL preprocessor.  These
          // are shared for primary and dirty database use, and are not dialect
          // specific.
          Iterator<String> iter = fqlPreprocessor.ansiJoins();
          while (iter.hasNext())
          {
             buf.append(iter.next());
             buf.append(" ");
          }
       }

       /**
        * Assemble the <code>order by</code> clause for an FQL query.  Uses the
        * form:
        * <pre>
        *   order by {dmo1} {asc/desc} [, {dmo2} {asc/desc} [...]]
        * </pre>
        *
        * @param   buf
        *          String buffer into which clause is assembled.
        * @param   orderPhrase
        *          Order by phrase to appended to the buffer if not
        *          <code>null</code>.
        */
       private void assembleOrderByClause(StringBuilder buf, String orderPhrase)
       {
          if (orderPhrase != null)
          {
             buf.append(orderPhrase);
          }
       }
    }