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();
}
}
}