FqlToSqlConverter.java
/*
** Module : FqlToSqlConverter.java
** Abstract : Handles conversion of FQL statements to SQL.
**
** Copyright (c) 2019-2025, Golden Code Development Corporation.
**
** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
** 001 OM 20190915 First revision. Converts simple FQL queries into SQL.
** 002 OM 20201001 Improved DMO manipulation performance by caching slow Property annotation access.
** CA 20201003 Replaced Guava identity HashSet with Collections.newSetFromMap(IdentityHashMap).
** CA 20201005 Do not emit NULLS option for _multiplex and PK columns, in ORDER BY.
** OM 20201120 Avoid NPE in getScopedDmoInfo(). Changes error message to a more explicit one.
** IAS 20201126 Re-write SQL statement with CONTAINS to use words' table
** EVL 20210202 Workaround to eliminate NPE and resume client start.
** IAS 20210203 A better solution for the NPE.
** IAS 20210219 Word tables support for custom extents
** IAS 20210303 Added SQL re-writing using common table expressions (WITH clause)
** IAS 20210310 Added implicit ordering
** IAS 20210315 Use Database instead of schema in constructor, re-work implicit ordering
** IAS 20210321 Re-worked implicit ordering for word tables. Added implicit sorting for
** _temp and 'dirty' databases.
** IAS 29210401 Flash _UserTableStat before access
** OM 20210412 Javadoc fixes. Dropped unneeded casts.
** ECF 20210420 Fixed schema lookup.
** OM 20210423 Fixed construction of word-index driven queries for specific cases.
** OM 20210503 The components of ORDER BY clause used by TemporaryBuffer.readAllRows() must be property
** names not SQL column names. They will be converted to SQL later.
** RFB 20210504 Temporarily undo OM changes until the regression can be determined.
** 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.
** OM 20210908 Used SchemaDictionary.TEMP_TABLE_DB instead of hardcoded constant.
** OM 20220212 Use ReservedProperty static method for testing reserved properties names.
** IAS 20220324 Re-worked LockTableUpdater.
** OM 20220409 Added support for List/array query parameters.
** OM 20220411 Added missing annotations for nodes created after pre-processing.
** OM 20220428 The extent cardinality (paramcard) is always 1.
** OM 20220627 rewriteSelectDynamicSubscripts() extracts the terms for list__index at top level.
** OM 20220628 When the parameters are reordered, a parameter permutation is created.
** OM 20220722 Added support for constants (strings & numbers) in list of selected elements.
** OM 20220727 FieldId and PropertyId are different for denormalized extent fields.
** SBI 20220811 Prepared the code removal.
** 20221031 Collected fields of "select" query.
** 20221101 Fixed possible NPE.
** OM 20220913 Enhanced dialect API for sorting nulls.
** OM 20221012 Fixed regression (invalid sort criteria) in previous commit.
** OM 20221103 New class names for FQLPreprocessor, FQLExpression, FQLBundle, and FQLCache.
** SVL 20230108 Improved performance by replacing some "for-each" loops with indexed "for" loops.
** 20221031 Collected fields of "select" query.
** 20221101 Fixed possible NPE.
** 003 RAA 20230213 Added the keyword "lazy". This can be appended to the SQL that gets generated.
** 004 AL2 20230316 Introduced wildcard selection for some temp-tables.
** 005 IAS 20230405 Second pass query generation for recursive DATA-RELATION FILL support
** Added fix for H2 bug with bind parameters in recursive CTEs.
** Fixed second pass query generation for queries with CONTAINS operator
** 006 DDF 20230306 Skip analyzing propertyStr too see whether it is a ComputedColumn when the
** dialect doesn't require computed columns (refs: #7108).
** Fixed second pass query generation for queries with CONTAINS operator.
** 007 OM 20230428 Made sure [aliases] is balanced. Avoid double-emit or skipping of FQLAst tokens.
* Fixed handling of nested SUBSELECTs. Local optimizations. Bit of code cleanup.
** 008 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
** 009 AL2 20230516 Include the number of SQL fields in the field counter.
** 010 IAS 20230706 Runtime changes for CONTAINS with non-constant search expression.
** 011 AB 20230803 Added support for the "ON" clause.
** AB 20230822 Fixed the spacing in generateJoin().
** 012 AL2 20231211 Use the new way of appending properties to the row structure.
** 013 AB 20231213 Updated generateFrom() to correctly create the inner-join.
** 014 ASL 20240105 Reverted changes in 013. Improved method generateWhere()
** to include extent indexes-only case without an existing where clause.
** 015 IAS 20230208 Dialect-specific error handler support.
** 016 CA 20240320 Avoid BDTs within internal FWD runtime. Replaced iterators with Java 'for', where it
** applies.
** 017 OM 20240430 Allow normalized extent fields in query list, but once they are added to RowStructure
** drop them from final SQL statement. The query will fetch them individually, later at
** hydration time.
** 018 SP 20240513 Updated processSelect() for sql statements using contains, to avoid generating
** same rewritten contains call when processing a subselect.
** 019 SP 20240517 Setting crwd.alwaysFalse earlier and updated containsWithWordTableAndCTE() to remove the
** parameter, before appending (1 = 0) instead of the rewritten contains call.
** 020 TJD 20240123 Java 17 compatibility update
** 021 SP 20240603 Fixed cases when usages of contains on extent fields was resulting in a crash.
** Use property.column directly when setting crwd.fldName instead of using NameConverter.
** 022 OM 20240801 Code maintenance: optimized imports, reduced accessibility of some methods.
** 023 SP 20240904 Small performance improvement to converted contains call. Code cleanup.
** Use property.column directly when setting crwd.fldName instead of using NameConverter
** 024 DDF 20240709 Add alias for DATETIME-TZ offset column so that the recursive CTE name
** uses the correct number of columns.
** DDF 20240710 Replaced appendDTZColAlias lambda with a method that has the same name.
** DDF 20240712 Removed appendDTZColAlias method, simplified implementation.
** 025 SP 20240924 Fixed a performance regression and improved the changes in H023.
** 026 SP 20240927 Fixed and extended optimizeContainsCTE flag for some cases.
** 027 SP 20241002 Don't create a CTE for CONTAINS if the result is expected to be alwaysFalse.
** SP 20240103 Fixed orderFields generation to support aliases used in a FUNCTION.
** 028 SP 20241111 Enable optimizeContainsCTE for all rewritten contains that use CTEs.
** Replace P2JQuery.ParamResolver parameters with the actual resolved value.
** 029 AL2 20241129 Don't use DISTINCT clause for WCTE recid as it disables nested partial join optimization
** in the DB engine.
** 030 SP 20250311 Added cache for parsed SELECT_STMT fql ASTs.
** 031 OM 20250326 Replaced in isVST() with getMetaTable() method in DmoMeta.
** 032 LS 20250502 Changed the signature of 'generateProperty'. Retrieve also the _offset column for
** datetime-tz fields.
*/
/*
** 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 static com.goldencode.p2j.persist.orm.FQLParserTokenTypes.*;
import java.io.*;
import java.util.*;
import java.util.concurrent.atomic.*;
import java.util.function.*;
import java.util.logging.*;
import java.util.stream.*;
import com.goldencode.ast.*;
import com.goldencode.cache.*;
import com.goldencode.p2j.cfg.*;
import com.goldencode.p2j.persist.*;
import com.goldencode.p2j.persist.FQLPreprocessor.*;
import com.goldencode.p2j.persist.Record;
import com.goldencode.p2j.persist.dialect.*;
import com.goldencode.p2j.persist.orm.Query.*;
import com.goldencode.p2j.persist.meta.*;
import com.goldencode.p2j.persist.meta.MetadataManager.*;
import com.goldencode.p2j.persist.orm.DmoMeta.*;
import com.goldencode.p2j.schema.*;
import com.goldencode.p2j.util.*;
import com.goldencode.p2j.util.LogicalExpressionConverter.*;
import com.goldencode.p2j.util.logging.*;
import org.apache.commons.lang3.tuple.*;
import antlr.*;
/**
* This class handles conversion of FQL statements into SQL using a specified
* {@code Dialect}. The implementation targets the correctness and performance, so a minimum
* number of walks on the parsed tree must be executed. TRPL is obviously, excluded.
*/
public class FqlToSqlConverter
{
/** Logger */
private static final CentralLogger LOG = CentralLogger.get(FqlToSqlConverter.class.getName());
/** Cache of parsed fql ASTs */
private static final LRUCache<String, FQLAst> fqlAstCache =
CacheManager.createLRUCache(FqlToSqlConverter.class, "ast", 8192);
/** Name of the primary key column. */
private static final String PK = Configuration.getSchemaConfig().getPrimaryKeyName();
/** Constant for a virtual alias when none is specified (used in case of single table statements). */
private static final String NO_ALIAS = "";
/** The ASTERISK literal used in aggregate SQL functions. */
private static final char ASTERISK = '*';
/** The dialect used to construct the SQL statement. */
private final Dialect dialect;
/** The database */
private final Database db;
/** The schema of the database. This is used to unambiguate short-names entities. */
private final String schema;
/** The {@code StringBuilder} used to construct the SQL statement. */
private StringBuilder sb = null;
/**
* The {@code StringBuilder} used to construct an optional single property name, if not {@code null}.
* Ignored otherwise.
*/
private StringBuilder sbProp = null;
/** Counter for making column aliases unique. */
private int columnUid = 0;
/**
* The aliases (table names) currently used in this statement. Each level represents additional
* aliases added at each nested select. We need to exclude them when their scope ends.
*/
private final ArrayDeque<HashMap<String, DmoMeta>> aliases = new ArrayDeque<>();
/** The aliases used in SQL queries. */
private final Map<String, String> sqlTableAliasMap = new HashMap<>();
/**
* If this flag is {@code true} the SQL names will be used for table aliases, otherwise the
* FQL named received in FQL string will be used.
*/
private final boolean useSqlTableAlias;
/**
* If this flag is {@code true}, the selected columns are named. An unique name is computed for
* each column.
*/
private final boolean generateUniqueSqlColumnNames;
/** The maximum numbers of results in a query. Used for paging results. */
private int maxResults = -1;
/** Flag for selecting results in a lazy manner. Used in {@code Select} statements. */
private boolean lazyMode = false;
/** The offset of the record to start a result with. Used for paging results. */
private int startOffset = -1;
/** The number of SQL parameters encountered during parsing. */
private int paramCount = 0;
/**
* The structure of the result to be returned. For each record returned, the number of fields
* is stored in order to facilitate the re-hydration of the result back to {@code Record}s.
* This is a by-product of the query statement conversion.
*/
private ArrayList<RowStructure> rowStructure = null;
/** The number of subscript with substitution parameter, like <code>[?]</code>. */
private int complexSubscript = 0;
/** Flag identifying to alias will be emitted for properties. Required by the UPDATE statement. */
private boolean forceNoAlias = false;
/** Query parameters. */
private final QueryParams queryParams;
/** Parameters permutation. */
private int[] paraPerm = null;
/** Flags the a query was re-written during conversion to SQL */
private boolean queryWasRewritten = 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;
/** Re-write data for CONTAINS operators */
private final ArrayList<ContainsRewriteData> containsRewriteData = new ArrayList<>();
/** Level of nested function calls for the CONTAINS first argument */
// TODO: do not add RTRIM for the CONTAINS field in the HQLPreprocessor
private int nestedInContains = 0;
/** Index of currently processed CONTAINS */
private int currentContains = -1;
/** positional parameters found so far */
private int nargs = 0;
/** number of CTE parameters */
private int nCteArgs = 0;
/** generator for the CTE suffix */
private int cteNo = 0;
/** CTE used for implicit ordering imposed by CONTAINS */
private ContainsRewriteData cte4order = null;
/** Holder for 'weights' used for implicit ordering imposed by CONTAINS */
private final StringBuilder weigths = new StringBuilder();
/** 'use word tables' flag */
private final boolean useWordTables;
/**
* Flags that we can optimize CONTAINS operator. Only for contains rewritten using word tables and CTEs.
* When the where clause is simple with no ORs
*/
private boolean optimizeContainsCTE;
/** Number of AND terms */
private int andTerms = 0;
/** Number of OR terms */
private int orTerms = 0;
/** List of the row fields/ aliases */
private final List<String> rowAliases = new ArrayList<>();
/** The recursive DATA-RELATION the query is to FILL for */
private DataRelation relation = null;
/** SELECT fields' aliases by legacy name */
private Map<String, String> selectAliases = null;
/** SELECT fields' names by legacy name */
private Map<String, String> selectFields = null;
/** Collector for SELECT fields' aliases */
private BiConsumer<String, String> collectAliases = (l, a) -> {};
/** Collector for SELECT fields' names */
private BiConsumer<String, String> collectFields = (l, f) -> {};
/** Flags that query bind parameters should be mapped to session variables */
private boolean bindParam2SessionVar = false;
/** Number of bind parameters. */
private final AtomicInteger nbind = new AtomicInteger(0);
/** Supplier of bind parameter placeholder */
private Supplier<String> argRef = () -> "?" ;
/** Mutable session attributes' values used in the query */
private final List<SessionAttr> sessionAttrs = new ArrayList<>();
/** The name of the checkError UDF with prepended dot */
private final String _checkErrorFn;
/** Flag indicating that the query contains error handling */
private boolean containsErrorHandling = false;
/**
* The constructor is private. To create an instance of this class, use the {@code getInstance}
* builder method.
*
* @param dialect
* The SQL dialect to be used when generating SQL statements.
* @param db
* The database.
* @param queryParams
* queryParameters;
* @param useSqlTableAlias
* When {@code true}, the SQL names will be used for table aliases, otherwise the
* FQL named received in FQL string will be kept.
*/
private FqlToSqlConverter(Dialect dialect, Database db, QueryParams queryParams, boolean useSqlTableAlias)
{
this.dialect = dialect;
this.db = db;
this.schema = db.isMeta() ? MetadataManager.META_SCHEMA : DatabaseManager.getSchema(db);
this.queryParams = queryParams;
this.useSqlTableAlias = useSqlTableAlias;
this.generateUniqueSqlColumnNames = true;
this.useWordTables = !SchemaDictionary.TEMP_TABLE_DB.equals(schema) &&
!db.isDirty() && !db.isMeta() && dialect.useWordTables();
this._checkErrorFn = "." + dialect.checkErrorFn();
}
/**
* Builder method for create an instance of this class.
*
* @param dialect
* The SQL dialect to be used when generating SQL statements.
* @param db
* The database.
*
* @return an instance of this class
*/
public static FqlToSqlConverter getInstance(Dialect dialect, Database db)
{
return getInstance(dialect, db, new QueryParams());
}
/**
* Builder method for create an instance of this class.
*
* @param dialect
* The SQL dialect to be used when generating SQL statements.
* @param db
* The database.
* @param queryParams
* The query parameters.
*
* @return an instance of this class
*/
public static FqlToSqlConverter getInstance(Dialect dialect, Database db, QueryParams queryParams)
{
return new FqlToSqlConverter(dialect, db, queryParams, true);
}
/**
* Check if the query was re-written
*
* @return <code>true</code> if the query was re-written
*/
public boolean isQueryWasRewritten()
{
return queryWasRewritten;
}
/**
* Check if the query bind parameters should be mapped to session variables.
*
* @return <code>true</code> if the query parameters should be mapped to session variables.
*/
public boolean bindParam2SessionVar()
{
return bindParam2SessionVar;
}
/**
* Get list of mutable session attributes' values used in the query.
*
* @return List of mutable session attributes' values used in the query.
*/
public List<SessionAttr> sessionAttrs()
{
return sessionAttrs;
}
/**
* Checks if the query contains error handling.
*
* @return {@code true} if the query contains error handling.
*/
public boolean containsErrorHandling()
{
return containsErrorHandling;
}
/**
* Check if the query reads _UserTableStat VST.
*
* @return <code>true</code> if the query reads _UserTableStat VST.
*/
public boolean isUserTableStatRead()
{
return userTableStatRead;
}
/**
* Check if the query reads _Lock VST.
*
* @return <code>true</code> if the query reads _Lock VST.
*/
public boolean isLockTableRead()
{
return lockTableRead;
}
/**
* Re-write CONTAINS using word tables and CTE.
*
* @param crwd
* Structure holding the re-write data to be processed.
*/
private void containsWithWordTableAndCTE(ContainsRewriteData crwd)
{
if (crwd.narg >= 0)
{
queryParams.removeParam(nargs - 1);
paramCount = paramCount - 1;
nargs = nargs - 1;
}
if (crwd.alwaysFalse)
{
sb.append("(1 = 0)");
return;
}
queryParams.insertParam(nCteArgs, crwd.args.toArray());
paramCount += crwd.args.size();
nargs += crwd.args.size();
nCteArgs += crwd.args.size();
// "(ALIAS.recid in (select recid from wcte1))" is being skipped. We will
// later replace the left join with a simple join which will work the same.
if (optimizeContainsCTE)
{
return;
}
sb.append("(").
append(crwd.tblAlias).append(".").append(PK).
append(" in (select ").append(PK).append(" from ").append(crwd.cteName).
append(")");
sb.append(")");
}
/**
* Re-write CONTAINS using word tables.
*
* @param crwd
* Structure holding the re-write data to be processed.
*/
private void containsWithWordTable(ContainsRewriteData crwd)
{
sb.append("(");
crwd.containsQuery(schema, sb);
sb.append(")");
if (crwd.narg >= 0)
{
queryParams.replaceParam(nargs - 1, crwd.args.toArray());
paramCount += crwd.args.size() - 1;
nargs += crwd.args.size() - 1;
}
else
{
queryParams.insertParam(nargs, crwd.args.toArray());
paramCount += crwd.args.size();
nargs += crwd.args.size();
}
}
/**
* Set field property for CONTAINS.
*
* @param crwd
* Structure holding the re-write data to be processed.
*/
private void setFieldProperty(ContainsRewriteData crwd)
{
DmoMeta meta = getScopedDmoInfo(crwd.tblMetaKey);
if (meta == null)
{
throw new IllegalStateException("No DmoMeta for [" + crwd.tblName + "]/[" + crwd.tblAlias + "]");
}
Property fp = meta.propsByName.get(crwd.fldName);
if (fp == null)
{
fp = meta.propsByName.values().stream().
filter(p -> crwd.fldName.equals(p.column)).
findFirst().orElse(null);
}
if (fp == null)
{
throw new IllegalStateException("Unknown field: [" + crwd.fldName + "]" );
}
crwd.fp = fp;
if (fp.extent > 0 && !fp.denormalized)
{
crwd.extentTable = meta.sqlTable + "__" + fp.extent;
}
}
/**
* Re-write CONTAINS using UDF.
*
* @param crwd
* Structure holding the re-write data to be processed.
*/
private void containsWithUdf(ContainsRewriteData crwd)
{
setFieldProperty(crwd);
int extent = crwd.fp.extent;
boolean caseSensitive = crwd.fp.caseSensitive;
StringBuilder contains = new StringBuilder(dialect.containsUdfName()).append("(");
toUpper(!caseSensitive, contains, () -> contains.append(crwd.fldName));
contains.append(", ");
toUpper(!caseSensitive, contains, () ->
{
if (crwd.narg >= 0)
{
contains.append("?");
}
else
{
contains.append("'").append(crwd.expr).append("'");
}
});
contains.append(")");
if (extent == 0)
{
sb.append(contains);
}
else
{
sb.append("(").append(PK).
append(" in (select distinct parent__id from ").
append(crwd.extentTable).
append(" where ").append(contains).append("))");
}
}
/**
* Re-write CONTAINS using UDF if corresponding word index is used for sorting
*
* @param crwd
* Structure holding the re-write data to be processed.
*/
private void containsWithUdfAndCTE(ContainsRewriteData crwd)
{
if (crwd.narg >= 0)
{
queryParams.removeParam(nargs - 1);
paramCount = paramCount - 1;
nargs = nargs - 1;
}
queryParams.insertParam(nCteArgs, crwd.args.toArray());
paramCount += crwd.args.size();
nargs += crwd.args.size();
nCteArgs += crwd.args.size();
// "(ALIAS.recid in (select recid from wcte1))" is being skipped. We will
// later replace the left join with a simple join which will work the same.
if (optimizeContainsCTE)
{
return;
}
sb.append("(").
append(crwd.tblAlias).append(".").append(PK).
append(" in (select ").append(PK).append(" from ").append(crwd.cteName).
append(")");
sb.append(")");
}
/**
* Converts a {@code FQL} statement into its SQL representation.
*
* @param fql
* A valid {@code FQL} statement.
* @param maxResults
* The maximum numbers of results to be returned.
* @param startOffset
* The start offset. Together with {@code maxResults} this parameter helps to implement paging of
* a bigger the result set.
* @param rowStructure
* (Acts as an output parameter). If not {@code null}, at return, the map will contain
* the list of entities returned and the number of fields coded in the generated
* {@code SELECT} statement for each of them.
* @param relation
* The recursive DATA-RELATION the query is to FILL for.
* @param lazyMode
* Should the returned SQL execute the statement in a lazy manner?
* {@code true} if the answer is yes, {@code false} otherwise.
*
* @return The SQL representation of the {@code fql}.
*/
public String toSQL(String fql,
int maxResults,
int startOffset,
ArrayList<RowStructure> rowStructure,
DataRelation relation,
boolean lazyMode)
{
if (LOG.isLoggable(Level.FINE))
{
LOG.log(Level.FINE,
"FQL: " + fql + ", LIMIT: " + maxResults + ", START AT: " + startOffset);
}
this.rowStructure = rowStructure;
if ((this.relation = relation) != null)
{
this.selectAliases = new LinkedHashMap<>();
this.selectFields = new LinkedHashMap<>();
this.collectAliases = this.selectAliases::put;
this.collectFields = this.selectFields::put;
this.bindParam2SessionVar = !dialect.allowBindParamsInRecursiveCTE();
if (bindParam2SessionVar)
{
this.argRef = () -> "@_v" + nbind.incrementAndGet();
}
}
FQLAst root;
synchronized (fqlAstCache)
{
root = fqlAstCache.get(fql);
}
if (root == null)
{
FQLParser parser = new FQLParser(new FQLLexer(new StringReader(fql)));
parser.setListener(this);
try
{
parser.statement();
}
catch (RecognitionException | TokenStreamException e)
{
if (LOG.isLoggable(Level.SEVERE))
{
LOG.log(Level.SEVERE, "Failed to parse FQL statement [" + fql + "]", e);
}
return null;
}
root = (FQLAst) parser.getAST();
if (LOG.isLoggable(Level.FINER))
{
LOG.log(Level.FINER, "Parsed Tree: " + root.dumpTree(true));
}
if (root.getType() == SELECT_STMT)
{
synchronized (fqlAstCache)
{
fqlAstCache.put(fql, (FQLAst) root.duplicate());
}
}
}
else
{
root = (FQLAst) root.duplicate();
}
paramCount = 0;
preprocess(root);
columnUid = 0;
sb = new StringBuilder(fql.length());
this.maxResults = maxResults;
this.startOffset = startOffset;
this.lazyMode = lazyMode;
processStatement(root, new FQLAst[1]);
String sql = sb.toString();
if (LOG.isLoggable(Level.FINE))
{
LOG.log(Level.FINE, "SQL: " + sql);
}
return sql;
}
/**
* Obtain the parameter permutation for the converted SQL.
*
* @return The parameter permutation function, if any detected.
*/
public int[] getParameterPermutation()
{
return paraPerm;
}
/**
* Returns the number of parameters in the last converted statement.
*
* @return The number of parameters (that is SUBST and POSITIONAL nodes) encountered while
* processing the last successfully converted FQL statement.
*/
public int getLastConversionParamCount()
{
return paramCount;
}
/**
* TODO: replace
* upper(rtrim(alias.ch-property)) with alias.__ich-property and
* rtrim(alias.ch-property) with alias.__sch-property
* if the dialect requires so (H2) and only if the property is part of an indexed.
* WARNING: this should not be done here, see the DatabaseManager.getIgnoreCase usage - the computed
* column should be emitted explicitly.
*
* Does a quick preprocess of the AST tree after the initial parsing. The following
* transformations are executed:
* <ul>
* <li>"index(PROPERTY)" is converted to "[ALIAS].list__index [PROPERTY]";</li>
* <li>"alias.property [PROPERTY]" is converted to "alias [ALIAS].property [PROPERTY]";</li>
* <li>simplify some (sub-)expressions by dropping the extra LPARENS;</li>
* <li>simplify [(a = b) = true] to [a = b]</li>
* <li>counts the parameters in {@code paramCount} field;</li>
* <li>for full-path DMOs, make sure the interface is registered with {@code DmoMetadataManager}</li>
* <li>TO be added as encountered ...</li>
* </ul>
*
* @param root
* The root of the AST tree of a statement.
*/
private void preprocess(FQLAst root)
{
AstWalkListener awl = new AstWalkListener()
{
/**
* Called whenever a transition from a parent node to its first child takes place during
* an AST walk. This will be called <em>before</em> the next AST node in the tree
* traversal is visited.
*
* @param ast
* Source AST node which represents the parent in the parent to child transition
* of the current AST walk.
*/
@Override
public void descent(Aast ast)
{
}
/**
* Called when the AST walk ascends between levels of the tree. Used to close off certain
* constructs when emitting HQL.
*
* @param node
* AST node to which the walk is ascending.
*/
public void ascent(Aast node)
{
if (node == null)
{
return;
}
if (node.getType() == FUNCTION &&
node.getNumImmediateChildren() == 1 &&
node.getFirstChild().getType() == PROPERTY &&
"index".equals(node.getText())) // TODO: extract [index] as constant
{
// convert from "index(PROPERTY)" to "ALIAS.list__index"
int nodeIdx = node.getIndexPos();
Aast parent = node.getParent();
Aast alias = (Aast) node.getFirstChild();
alias.remove();
node.remove();
// do the changes:
alias.setType(ALIAS);
node.setType(PROPERTY);
node.setText("list__index"); // TODO: extract [list__index] as constant
// reassemble back:
alias.graft(node);
parent.graftAt(alias, nodeIdx);
return; // done processing this node
}
if (node.getType() == EQUALS)
{
// simplify [(a = b) = true] to [a = b] and [(a = b) = false] to [a != b]
Aast firstChild = (Aast) node.getFirstChild();
Aast secondChild = (Aast) firstChild.getNextSibling();
if (firstChild.getType() == BOOL_TRUE || firstChild.getType() == BOOL_FALSE)
{
if (secondChild.getType() == EQUALS || secondChild.getType() == NOT_EQ)
{
Aast parent = node.getParent();
int idx = node.getIndexPos();
node.remove();
secondChild.remove();
parent.graftAt(secondChild, idx);
if (firstChild.getType() == BOOL_FALSE)
{
secondChild.setType(secondChild.getType() == EQUALS ? NOT_EQ : EQUALS);
}
return;
}
}
else if (secondChild.getType() == BOOL_TRUE || secondChild.getType() == BOOL_FALSE)
{
if (firstChild.getType() == EQUALS || firstChild.getType() == NOT_EQ)
{
Aast parent = node.getParent();
int idx = node.getIndexPos();
node.remove();
firstChild.remove();
parent.graftAt(firstChild, idx);
if (secondChild.getType() == BOOL_FALSE)
{
firstChild.setType(firstChild.getType() == EQUALS ? NOT_EQ : EQUALS);
}
return;
}
}
}
switch (node.getType())
{
case AND:
andTerms++;
case GT:
case GTE:
case LT:
case LTE:
case EQUALS:
case FUNCTION:
if (node.getParent() != null &&
node.getParent().getType() == LPARENS &&
node.getParent().getParent().getType() == AND)
{
// simplify expression by dropping the extra LPARENS
Aast lparens = node.getParent();
Aast and = lparens.getParent();
int nodeIdx = lparens.getIndexPos();
lparens.remove();
and.graftAt(node, nodeIdx);
}
break;
case OR:
orTerms++;
break;
case IS_NULL:
case NOT_NULL:
if (node.getParent() != null && node.getParent().getType() != LPARENS)
{
// Hibernates injects a LPARENS as parent here to be sure the IS/NOT NULL is
// always parenthesised but we do not (at this moment) to keep the expression
// simplified
}
break;
case LPARENS:
if (node.getParent() != null && node.getParent().getType() == WHERE)
{
// simplify expression by dropping this extra LPARENS
Aast child = (Aast) node.getFirstChild();
Aast where = node.getParent();
int nodeIdx = node.getIndexPos();
node.remove();
where.graftAt(child, nodeIdx);
}
default:
break;
}
}
/**
* Called whenever a transition from a child node to its next right sibling takes place
* during an AST walk. This will be called <em>before</em> the next AST node in the tree
* traversal is visited. The parent node of the children is in scope at the time of
* notification. This effectively mimics recursion by notifying the listener when one
* child node has been processed, before the next is visited. This allows the listener to
* perform parent-level processing between visits to child nodes.
* <p>
* This method is <em>not</em> invoked after the last child is visited, as this is the
* purpose of the {@link #ascent} method.
*
* @param ast
* Source AST node which represents the parent of the children between which the
* lateral transition is taking place in the current AST walk.
* @param index
* The index of the child.
*/
@Override
public void nextChild(Aast ast, int index)
{
}
};
ArrayList<Pair<FQLAst, Integer>> propsToBeExpanded = new ArrayList<>();
Iterator<Aast> iter = root.iterator(0, awl);
while (iter.hasNext())
{
Aast node = iter.next();
int ntype = node.getType();
if (ntype == SUBST || ntype == POSITIONAL)
{
node.putAnnotation("paramNo", (long) paramCount);
node.putAnnotation("paramCard", (long) queryParams.getCardinality(paramCount));
++paramCount;
continue;
}
FQLAst firstChild = (FQLAst) node.getFirstChild();
if ((firstChild == null || firstChild.getType() == LBRACKET) && ntype == PROPERTY)
{
String nodeText = node.getText();
int k = nodeText.indexOf('.');
if (k != -1)
{
propsToBeExpanded.add(Pair.of((FQLAst) node, k));
}
continue;
}
if (firstChild == null && ntype == DMO)
{
// make sure the DMO interface is loaded and have access to implementation
String nodeText = node.getText();
if (nodeText.startsWith(DmoMetadataManager.getDmoBasePackage()))
{
try
{
DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(nodeText, schema);
Class<? extends Record> impl = dmoInfo.implClass;
node.setText(impl.getSimpleName());
}
catch (Exception e)
{
LOG.log(Level.SEVERE, "Failed to load DMO: " + nodeText, e);
}
}
}
}
// [alias.property:PROPERTY] nodes are converted to [alias:ALIAS].[property:PROPERTY]
// this is done after the full tree was processed because the changes might interfere with normal
// iteration of the AST
for (int i = 0; i < propsToBeExpanded.size(); i++)
{
Pair<FQLAst, Integer> pair = propsToBeExpanded.get(i);
FQLAst node = pair.getLeft();
int k = pair.getRight();
String nodeText = node.getText();
FQLAst firstChild = (FQLAst) node.getFirstChild(); // optional LBRACKET subtree
if (firstChild != null)
{
firstChild.remove();
}
FQLAst newNode = new FQLAst();
newNode.setType(PROPERTY);
newNode.setText(nodeText.substring(k + 1));
node.setType(ALIAS);
node.setText(nodeText.substring(0, k));
node.graft(newNode);
if (firstChild != null)
{
newNode.graft(firstChild);
}
}
}
/**
* Converts any FQL statement into the SQL language. The output is directly appended to {@code sb}.
*
* @param root
* The root of the {@code FQLAst} statement.
* @param lastProcessedAst
* Output parameter: the last processed Ast. Allow a caller to know where the SUBSELECT ends.
*/
private void processStatement(FQLAst root, FQLAst[] lastProcessedAst)
{
switch (root.getType())
{
case SUBSELECT:
// register the entire tree as processed
Iterator<Aast> iter = root.iterator();
while (iter.hasNext())
{
lastProcessedAst[0] = (FQLAst) iter.next();
}
case SELECT_STMT:
processSelect(root);
break;
case DELETE_STMT:
processDelete(root);
break;
case UPDATE_STMT:
processUpdate(root);
break;
case INSERT_STMT:
// TODO: add INSERT for completeness
throw new UnsupportedOperationException("FQL INSERT statement is not supported!");
}
}
/**
* Converts a {@code UPDATE} FQL statement into the SQL language. The output is directly
* appended to {@link #sb}.
*
* @param stmt
* The {@code UPDATE} FQL statement to be converted.
*/
private void processUpdate(FQLAst stmt)
{
this.forceNoAlias = true;
// collect the initial aliases and their DMO mappings from DMO node
HashMap<String, DmoMeta> topLevelAliasMap = new LinkedHashMap<>();
FQLAst dmoNode = (FQLAst) stmt.getImmediateChild(DMO, null);
String dmoName = dmoNode.getText();
DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(dmoName, schema);
topLevelAliasMap.put(NO_ALIAS, dmoInfo);
String sqlAlias = dmoName.substring(0, Math.min(dmoName.length(), 10));
sqlAlias = sqlAlias.toLowerCase() + this.sqlTableAliasMap.size() + "_";
sqlTableAliasMap.put(NO_ALIAS, sqlAlias);
FQLAst whereNode = (FQLAst) stmt.getImmediateChild(WHERE, null);
FQLAst setNode = (FQLAst) stmt.getImmediateChild(SET, null);
// rewrite all standalone PROPERTY nodes to add the ALIAS from the SET and WHERE nodes
rewriteNoAliasPropertyNodes(NO_ALIAS, setNode);
if (whereNode != null)
{
rewriteNoAliasPropertyNodes(NO_ALIAS, whereNode);
}
// do this before rewriting the extent properties, so we can access the aliases
aliases.push(topLevelAliasMap);
collectNormalizedExtentsAliases(whereNode,
topLevelAliasMap,
null,
this::rewriteSubscriptsAsSubselect,
this::rewriteSubscriptsAsSubselect);
sb.append("\nupdate ").append(dmoInfo.getSqlTableName()).append(" set");
generateSet(setNode);
generateWhere(whereNode, null);
if (aliases.pop() != topLevelAliasMap)
{
warn("Imbalance in FQL scopes.", stmt);
}
}
/**
* Generate the <code>SET</code> phrase for the <code>UPDATE</code> statement.
*
* @param set
* The AST for the SET phrase.
*/
private void generateSet(FQLAst set)
{
// TODO: this does not allow extent fields. The FWD usage at this time is only by calls from
// TemporaryBuffer.removeRecords.
FQLAst child = (FQLAst) set.getFirstChild();
boolean first = true;
while (child != null)
{
if (first)
{
first = false;
}
else
{
sb.append(",");
}
StringBuilder sbBackup = sb;
try
{
sb = new StringBuilder();
generateExpression(child);
sbBackup.append("\n\t").append(sb);
}
finally
{
sb = sbBackup;
}
child = (FQLAst) child.getNextSibling();
}
}
/**
* Converts a {@code DELETE} FQL statement into the SQL language. The output is directly
* appended to {@link #sb}.
*
* @param stmt
* The {@code UPDATE} FQL statement to be converted.
*/
private void processDelete(FQLAst stmt)
{
// collect the initial aliases and their DMO mappings from FROM
HashMap<String, DmoMeta> topLevelAliasMap = new LinkedHashMap<>();
FQLAst fromNode = (FQLAst) stmt.getImmediateChild(FROM, null);
FQLAst whereNode = (FQLAst) stmt.getImmediateChild(WHERE, null);
collectTableAliases(fromNode, topLevelAliasMap);
String alias = topLevelAliasMap.keySet().iterator().next();
// rewrite all standalone PROPERTY nodes to add the ALIAS from the FROM
rewriteNoAliasPropertyNodes(alias, whereNode);
// do this before rewriting the extent properties, so we can access the aliases
aliases.push(topLevelAliasMap);
collectNormalizedExtentsAliases(whereNode,
topLevelAliasMap,
null,
this::rewriteSubscriptsAsSubselect,
this::rewriteSubscriptsAsSubselect);
sb.append("\ndelete \n\t");
generateFrom(fromNode, null);
generateWhere(whereNode, null);
if (aliases.pop() != topLevelAliasMap)
{
warn("Imbalance in FQL scopes.", stmt);
}
}
/**
* Rewrite {@link FQLParserTokenTypes#PROPERTY} ASTs so that they are parented by an <code>ALIAS</code>
* AST.
*
* @param alias
* The alias to add to all unqualified properties.
* @param where
* The <code>WHERE</code> AST.
*/
private void rewriteNoAliasPropertyNodes(String alias, FQLAst where)
{
ArrayList<FQLAst> noAliasProps = new ArrayList<>();
Iterator<Aast> iter = where.iterator();
while (iter.hasNext())
{
FQLAst node = (FQLAst) iter.next();
if (node.getType() == PROPERTY && node.getParent().getType() != ALIAS)
{
noAliasProps.add(node);
}
}
for (int i = 0; i < noAliasProps.size(); i++)
{
FQLAst node = noAliasProps.get(i);
FQLAst aliasAst = new FQLAst(new Token(ALIAS));
aliasAst.setText(alias);
int idx = node.getIndexPos();
FQLAst parent = (FQLAst) node.getParent();
parent.graftAt(aliasAst, idx);
node.move(aliasAst, 0);
}
}
/**
* For DELETE and UPDATE statements which reference extent fields in the WHERE clause, these need to be
* rewritten via subselects. The subselect is inlined as a STRING AST, which will be emitted directly by
* {@link #generateExpression}.
*
* @param node
* The root WHERE node.
* @param subscripts
* The list of subscript properties which need to be rewritten.
*/
private void rewriteSubscriptsAsSubselect(FQLAst node, List<FQLAst> subscripts)
{
for (FQLAst aliasAst : subscripts)
{
// find the first logical operator (AND/OR).
FQLAst parent = aliasAst;
while (!(parent == node || parent.getType() == AND || parent.getType() == OR))
{
parent = (FQLAst) parent.getParent();
}
// find the expression where the dynamic subscript is part of
FQLAst child = (FQLAst) parent.getFirstChild();
while (!child.isAncestorOf(0, aliasAst))
{
child = (FQLAst) child.getNextSibling();
}
int childIndex = child.getIndexPos();
child.remove();
// create a LPARENS root
FQLAst lparens = new FQLAst(new Token(LPARENS));
lparens.setText("(");
parent.graftAt(lparens, childIndex);
generateExpression(child);
String childTest = sb.toString();
sb = new StringBuilder();
String childAlias = aliasAst.getText();
String childSqlTable = aliases.peek().get(childAlias).getSqlTableName();
String subscript = (String) aliasAst.getAnnotation("subscript");
String parentAlias = (String) aliasAst.getAnnotation("parentAlias");
if (forceNoAlias)
{
parentAlias = "";
}
else
{
parentAlias = parentAlias + ".";
}
// add the '(select count(*) from ALIAS where ALIAS.parent__id = parent.PK and ALIAS.list__index = ?) > 0' condition
String subselect = "(select count(*) from " + childSqlTable + " as " + childAlias;
subselect += " where " + childAlias + ".parent__id = " + parentAlias + Session.PK;
subselect += " and " + childAlias + ".list__index = " + subscript + " and " + childTest + ") > 0";
FQLAst subselectAst = new FQLAst(new Token(STRING));
subselectAst.setText(subselect);
lparens.addChild(subselectAst);
}
}
/**
* Converts a {@code SELECT} FQL statement into the SQL language. The output is directly
* appended to {@code sb}.
*
* @param stmt
* The {@code SELECT} FQL statement to be converted.
*/
private void processSelect(FQLAst stmt)
{
// collect the initial aliases and their DMO mappings from FROM and JOIN nodes
HashMap<String, DmoMeta> topLevelAliasMap = new LinkedHashMap<>();
FQLAst fromNode = (FQLAst) stmt.getImmediateChild(FROM, null);
FQLAst whereNode = (FQLAst) stmt.getImmediateChild(WHERE, null);
FQLAst orderNode = (FQLAst) stmt.getImmediateChild(ORDER, null);
collectTableAliases(fromNode, topLevelAliasMap);
Map<String, String> innerJoins = new HashMap<>();
Map<String, String> extIdxConstraints = new HashMap<>();
BiConsumer<FQLAst, List<FQLAst>> rewriteSelectStaticSubscripts =
(FQLAst node, List<FQLAst> dynamicSubscripts) ->
{
for (FQLAst alias : dynamicSubscripts)
{
extIdxConstraints.putIfAbsent(alias.getText(), (String) alias.getAnnotation("subscript"));
}
};
int oldCrdSize = containsRewriteData.size();
collectNormalizedExtentsAliases(whereNode,
topLevelAliasMap,
innerJoins,
rewriteSelectStaticSubscripts,
this::rewriteSelectDynamicSubscripts);
if (orderNode != null)
{
collectNormalizedExtentsAliases(orderNode,
topLevelAliasMap,
innerJoins,
rewriteSelectStaticSubscripts,
this::rewriteSelectDynamicSubscripts);
}
int newCrdSize = containsRewriteData.size();
optimizeContainsCTE = newCrdSize == 1 && orTerms == 0;
boolean currentHasContains = oldCrdSize < newCrdSize;
aliases.push(topLevelAliasMap);
Set<String> tbls = new HashSet<>(topLevelAliasMap.keySet());
topLevelAliasMap.keySet().stream().map(this::getSqlTableAliasName).forEach(tbls::add);
if (currentHasContains)
{
containsRewriteData.stream().skip(oldCrdSize).
forEach(d -> {
generateProperty(d.fieldAst, false, true, d);
d.cteName = generateCteName("wcte", tbls);
});
}
List<FieldInfo> orderFields = new ArrayList<>();
List<Aast> orderAsts = orderNode == null ? Collections.emptyList() :
getOrderByAsts(orderNode);
boolean onlySpecial = true;
for (Aast ast: orderAsts)
{
FieldInfo data = new FieldInfo();
onlySpecial &= generateProperty((FQLAst)ast, false, true, data);
orderFields.add(data);
}
if (onlySpecial && orderFields.size() > 0 && relation == null)
{
for (int i = 0; i < newCrdSize; i++)
{
ContainsRewriteData crwd = containsRewriteData.get(i);
if (!crwd.alwaysFalse &&
crwd.tblAlias.equals(orderFields.get(0).tblAlias) &&
dialect.weightAggregator().isPresent())
{
crwd.useCTE = true;
crwd.forSorting = true;
cte4order = crwd;
break;
}
}
}
if (cte4order == null || this.relation != null || !currentHasContains)
{
optimizeContainsCTE = false;
}
String sep = "";
if (useWordTables && !dialect.useUdf4Contains() && dialect.useCTE4Contains())
{
String prefix = relation == null ? "with\n" : "with recursive\n";
for (int i = oldCrdSize; i < newCrdSize; i++)
{
ContainsRewriteData crwd = containsRewriteData.get(i);
if (crwd.alwaysFalse || !crwd.useCTE)
{
optimizeContainsCTE = false;
continue;
}
sb.append(prefix);
prefix = "";
sb.append(sep);
sep = crwd.containsWith(schema, dialect.weightAggregator(), weigths, sb) ? ",\n" : "";
}
}
else if (cte4order != null && currentHasContains)
{
if (!cte4order.forSorting)
{
optimizeContainsCTE = false;
}
setFieldProperty(cte4order);
sb.append("with\n");
cte4order.containsWith4UDF(schema, dialect, weigths, sb);
sep = ",\n";
}
String mcte = generateCteName("mcte", tbls);
if (cte4order != null && currentHasContains)
{
sb.append(sep).append(mcte).append(" as (");
}
int selectStart = sb.length();
sb.append("\nselect \n\t");
FQLAst select = (FQLAst) stmt.getImmediateChild(SELECT, null);
// the SELECT node may be missing in FQL in case of 'Polymorphic queries'
if (select != null)
{
FQLAst child = (FQLAst) select.getFirstChild();
int expressionCounter = 0;
while (child != null)
{
FQLAst nextChild = null;
int funcLvl = -1;
StringBuilder pushSb = sb;
sb = new StringBuilder();
try
{
// the first condition is caused by the lack of knowledge in the parser: it was not able to
// properly detect the ALIAS-es at that moment (see FQLParser.select_expr)
String childText = child.getText();
if ((child.getType() == PROPERTY && topLevelAliasMap.containsKey(childText)) ||
(child.getType() == ALIAS && child.getNumImmediateChildren() == 0))
{
DmoMeta scopedDmoInfo = getScopedDmoInfo(childText);
if (scopedDmoInfo != null)
{
if (!(scopedDmoInfo instanceof CompositeDmoInfo))
{
expandAlias(scopedDmoInfo, childText, expressionCounter == 0);
expressionCounter++;
}
}
else
{
warn("Invalid alias: ", child);
}
}
else
{
if (expressionCounter != 0)
{
sb.append(", ");
}
expressionCounter++;
if (child.getType() == FUNCTION)
{
funcLvl = 0;
nextChild = (FQLAst) child.getNextSibling();
while (child.getType() == FUNCTION)
{
sb.append(childText).append("(");
child = (FQLAst) child.getFirstChild();
childText = child.getText();
funcLvl = funcLvl + 1;
}
}
Map.Entry<String, Property>[] propertyBuffer = new Map.Entry[1];
generateProperty(child, false, true, null, propertyBuffer, funcLvl, currentHasContains);
//TODO test this code removal that doubles fields in sql query.
// for case-insensitive character fields we need to request the insensitive value, too
/* start removal
if (isCaseInsensitiveField(child))
{
sb.append(", ");
generateProperty(child, true, true);
if (generateUniqueSqlColumnNames)
{
sb.append(" as ");
appendColAlias.accept("col");
}
++newFields;
}
end removal */
switch (child.getType())
{
case STRING:
case DEC_LITERAL:
case NUM_LITERAL:
case LONG_LITERAL:
case BOOL_TRUE:
case BOOL_FALSE:
case NULL:
// do nothing
break;
default:
// update the [rowStructure] with the number of columns added. If no record of this
// interface, create a new entry:
DmoMeta dmoMeta = getScopedDmoInfo(childText);
if (dmoMeta == null && childText.length() == 1 && childText.charAt(0) == ASTERISK)
{
// the aggregate functions use the default alias
dmoMeta = getScopedDmoInfo(NO_ALIAS);
}
if (dmoMeta != null)
{
// the assumption here is the DMO fields (for a certain alias) are contiguous.
if (rowStructure.isEmpty() ||
rowStructure.get(rowStructure.size() - 1).getDmoClass() != dmoMeta.dmoImplInterface)
{
rowStructure.add(new AdaptiveRowStructure(dmoMeta));
}
RowStructure oldStruct = rowStructure.get(rowStructure.size() - 1);
if (propertyBuffer[0] != null)
{
Property prop = propertyBuffer[0].getValue();
if (propertyBuffer[0].getKey().equals(Session.PK))
{
oldStruct.addRecid(prop);
}
else if (propertyBuffer[0].getKey().equals(ReservedProperty._MULTIPLEX.name))
{
oldStruct.addMultiplex(prop);
}
else
{
oldStruct.addProperty(prop);
if (prop.extent != 0 && !prop.denormalized)
{
// the RowStructure was notified that this is a normalized field, and it
// will take care of retrieving the extent values at the right time.
// Removing now the field from SELECT list since its column is not in the
// primary SQL table
nextChild = (FQLAst) child.getNextSibling();
child.remove();
//
expressionCounter--;
sb = new StringBuilder(); // fast clear
}
}
}
else
{
warn("Failed to append DMO property for " + childText + " ALIAS", child);
}
}
else
{
warn("Failed to obtain DMO data for " + childText + " ALIAS", child);
}
}
}
}
finally
{
pushSb.append(sb.toString());
sb = pushSb;
if (nextChild != null)
{
child = nextChild;
}
else
{
child = (FQLAst) child.getNextSibling();
}
}
}
}
else
{
// we generate a default SQL query
Set<Map.Entry<String, DmoMeta>> topLevelAliases = topLevelAliasMap.entrySet();
boolean first = true;
for (Map.Entry<String, DmoMeta> entry : topLevelAliases)
{
DmoMeta dmoMeta = entry.getValue();
if (dmoMeta instanceof CompositeDmoInfo)
{
continue;
}
String alias = entry.getKey();
expandAlias(dmoMeta, alias, first);
first = false;
}
}
String mcteRecid = "col_recid_";
if (cte4order != null && currentHasContains)
{
sb.append(", ").append(cte4order.tblAlias).append(".").append(PK).append(" as ").append(mcteRecid);
}
sb.append(" ");
int fromStart = this.sb.length();
String masterTable = generateFrom(fromNode, innerJoins);
int fromEnd = this.sb.length();
generateWhere(whereNode, extIdxConstraints);
if (this.relation != null) {
rewrite4SecondPass(this.getSqlTableAliasName(masterTable),
generateCteName("r", tbls), generateCteName("base", tbls),
selectStart, fromStart, fromEnd);
}
if (cte4order != null && this.relation == null && currentHasContains)
{
sb.append("\n)");
sb.append("\nselect ").append(
rowAliases.stream().map(alias -> mcte + "." + alias).collect(Collectors.joining(", "))
).
append("\nfrom ").append(mcte).
append(optimizeContainsCTE ? "\n\tjoin " : "\n\tleft join ").
append(cte4order.cteName).append(" on (").
append(mcte).append(".").append(mcteRecid).
append(" = ").
append(cte4order.cteName).append(".").append(PK).
append(")").
append("\norder by ").append(weigths).append(mcte).append(".").append(mcteRecid);
}
else if (this.relation == null)
{
generateOrderBy((FQLAst) stmt.getImmediateChild(ORDER, null));
}
generateSingle((FQLAst) stmt.getImmediateChild(SINGLE, null));
if (stmt.getType() == SELECT_STMT)
{
if (maxResults > 0 || startOffset > 0)
{
generatePaging();
}
if (lazyMode)
{
generateLazyMode();
}
}
if (aliases.pop() != topLevelAliasMap)
{
warn("Imbalance in FQL scopes.", stmt);
}
}
/**
* Generate second pass query for recursive DATA-RELATION FILL support.
*
* @param masterAlias
* Alias for the master source table
* @param rCteName
* Recursive CTE name
* @param baseCteName
* Base CTE name
* @param selectStart
* Base SELECT start position
* @param fromStart
* Base FROM start position
* @param fromEnd
* Base FROM end position
*/
private void rewrite4SecondPass(String masterAlias,
String rCteName,
String baseCteName,
int selectStart,
int fromStart,
int fromEnd)
{
String[] relFields = this.relation.getRelationFields(false);
String[] parentRelFields = this.relation.getRelationFields(true);
List<String> join = new ArrayList<>(relFields.length);
for (int nf = 0; nf < relFields.length; ++nf)
{
String c = masterAlias + "." + selectFields.get(relFields[nf]) + " = " +
rCteName + "." + selectAliases.get(parentRelFields[nf]);
join.add(c);
}
String pka = this.selectAliases.get(FqlToSqlConverter.PK);
StringBuilder sql = new StringBuilder(selectStart == 0 ? "with recursive " : ",\n").
append(baseCteName).append(" as (\n").
append(sb, selectStart, sb.length()).
append("),\n").
append(rCteName).append("(").
append(String.join(", ", this.selectAliases.values())).
append(") as (\n").
append("select * from ").
append(baseCteName).
append("\n").
append("union all\n").
append(sb.substring(selectStart, fromEnd)).
append("\n").
append("join ").
append(rCteName).
append(" on (").append(String.join(" and ", join)).append(")\n").
append(")\n").
append("select * from ").
append(rCteName).append("\n").
append("where ").append(pka).append(" not in (").
append("select ").append(pka).append(" from ").append(baseCteName).append(")\n")
;
sb.setLength(selectStart);
sb.append(sql);
}
/**
* Generate the alias for the common table expression for CONTAINS re-writing.
*
* @param base
* The base of the name
* @param tbls
* Set of already used table names/aliases.
*
* @return The generated alias.
*/
private String generateCteName(String base, Set<String> tbls)
{
String cte;
do
{
cte = base + (++cteNo);
}
while(!tbls.add(cte));
return cte;
}
/**
* Check if we can simply use an {@code alias.*} syntax instead of listing all SQL fields.
*
* @param dmoMeta
* The meta of the table to be considered for wildcard field selection.
*
* @return {@code true} if we can emit an wildcard syntax for field selection.
*/
private boolean canUseWildcard(DmoMeta dmoMeta)
{
return cte4order == null && !dmoMeta.hasComputedColumns(false);
}
/**
* Expands the alias into all its properties in a SELECT clause.
*
* @param dmoMeta
* The node meta-data for the node to be expanded.
* @param alias
* The node alias (text of the node) to be expanded.
* @param first
* {@code true} if this is the first expanded node.
*/
private void expandAlias(DmoMeta dmoMeta, String alias, boolean first)
{
String sqlTableAliasName = getSqlTableAliasName(alias);
if (dialect.allowSelectWildcard() &&
canUseWildcard(dmoMeta) &&
!NO_ALIAS.equals(sqlTableAliasName))
{
if (!first)
{
sb.append(", ");
}
sb.append(sqlTableAliasName).append(".*");
if (rowStructure != null)
{
rowStructure.add(new FullRowStructure(dmoMeta));
}
return;
}
Set<Map.Entry<String, Property>> fieldEntries = dmoMeta.propsByName.entrySet();
for (Map.Entry<String, Property> field : fieldEntries)
{
Property property = field.getValue();
if (property.extent > 0 && property.index == 0)
{
// skip the extent normalized fields: they are found in the
// [dmoMeta].[table].[name] + "__" + [property.extent()]
// these properties are located in secondary tables and will be requested with other queries
continue;
}
if (first)
{
first = false;
}
else
{
sb.append(", ");
}
if (!NO_ALIAS.equals(sqlTableAliasName))
{
sb.append(sqlTableAliasName).append(".");
}
sb.append(property.column);
if (generateUniqueSqlColumnNames)
{
String sqlColumnAlias = getSqlColumnAlias(property);
sb.append(" as ").append(sqlColumnAlias);
if (cte4order != null)
{
rowAliases.add(sqlColumnAlias);
}
String legacy = (property.id < 0) ? property.column : property.legacy;
collectAliases.accept(legacy, sqlColumnAlias);
collectFields.accept(legacy, property.column);
}
// TODO: DO we?, if so, in which case? Need an additional parameter?
if (false && dialect.needsComputedColumns())
{
// for case-insensitive character fields we need to request the insensitive value, too
boolean caseSensitive = property.caseSensitive;
if (!caseSensitive && property._isCharacter)
{
sb.append(", ");
sb.append(sqlTableAliasName).append(".")
.append(dialect.getComputedColumnPrefix(caseSensitive))
.append(property.column);
if (generateUniqueSqlColumnNames)
{
sb.append(" as ");
appendColAlias("column");
}
}
}
if (property._isDatetimeTz)
{
sb.append(", ");
sb.append(sqlTableAliasName).append(".")
.append(property.column).append(DDLGeneratorWorker.DTZ_OFFSET);
if (generateUniqueSqlColumnNames)
{
// Covers a case during a FILL operation when there is a DataRelation.
// The problem is that the number of columns will not match when building
// the second pass query for recursive DATA-RELATION FILL because the
// DATETIME-TZ is split into 2 columns in the database and the Recursive
// CTE name is only specifying the selectAliases collection which will not
// contain this additional TZ alias as it was not collected.
sb.append(" as ");
String dtzColAlias = appendColAlias("column", true);
String legacy = property.legacy + DDLGeneratorWorker.DTZ_OFFSET;
collectAliases.accept(legacy, dtzColAlias);
collectFields.accept(legacy, property.column);
}
}
}
if (rowStructure != null)
{
rowStructure.add(new FullRowStructure(dmoMeta));
}
}
/**
* Analyzes the {@link #maxResults} and {@link #startOffset} values and constructs the paging clauses.
* <p>
* Note: MSSQL dialect ({@code SELECT TOP n}) not supported at this time.
*/
private void generatePaging()
{
sb.append("\n");
if (maxResults > 0)
{
sb.append(" limit ").append(argRef.get());
++paramCount; // add [maxResults] at the end of parameter list
}
if (startOffset > 0)
{
sb.append(" offset ").append(argRef.get());
++paramCount; // add [startOffset] at the end of parameter list
}
}
/**
* Add the keyword "lazy" to the end of the SQL. This works only when there is a
* {@code Select} statement that is intended to be executed in a lazy manner.
*/
private void generateLazyMode()
{
sb.append("\nlazy");
}
/**
* Generates the {@code ORDER BY} part of the query. The output is directly appended to {@link #sb}.
*
* @param order
* The FQL {@code ORDER BY} node.
*/
private void generateOrderBy(FQLAst order)
{
if (order == null)
{
return;
}
sb.append("\norder by\n\t");
boolean first = true;
int cnt = order.getNumImmediateChildren();
for (int k = 0; k < cnt; k++)
{
if (first)
{
first = false;
}
else
{
sb.append(", ");
}
// temporarily save the [sb] to [ssb] so that the next calls will create in the sort criterion in [sb]
StringBuilder ssb = sb;
sb = new StringBuilder();
sbProp = dialect.injectComputedColumns() ? new StringBuilder() : null;
// if not null, [sbProp] will get the property name, without being wrapped in functions
boolean reserved = generateOrderByComponent((FQLAst) order.getChildAt(k));
boolean asc = true;
if (k < cnt - 1)
{
Aast dir = order.getChildAt(k + 1);
if (dir.getType() == DESC)
{
asc = false;
k++;
}
else if (dir.getType() == ASC)
{
k++; // just consume the token
}
// otherwise assume ASC by default
}
// now we have the sort criterion in [sb], extract the expression and restore the original [sb]
String sortCrit = sb.toString();
sb = ssb;
if (reserved)
{
sb.append(sortCrit).append(asc ? " asc" : " desc");
}
else
{
dialect.orderByNulls(sb, sortCrit, sbProp == null ? null : sbProp.toString(), asc, true);
}
}
}
/**
* Generates am order-by component. Usually this is equivalent of converting a normal property,
* but for some dialects (PostgreSQL), some SQL functions are allowed to be used. This method
* will be called recursively in order to support nested function calls.
* <p>
* Note: only a one-argument functions are supported (like {@code upper} and {@code rtrim} used
* for character fields). If more complex expressions are needed the more generic
* {@link #generateExpression} should be used instead.
*
* @param node
* The root node for this expression.
*
* @return <code>true</code> if this is the '_multiplex' or PK column.
*/
private boolean generateOrderByComponent(FQLAst node)
{
switch (node.getType())
{
case FUNCTION:
sb.append(node.getText());
sb.append("(");
generateOrderByComponent((FQLAst) node.getFirstChild());
sb.append(")");
return false;
case ALIAS:
return generateProperty(node, false, true);
case PROPERTY:
return generateProperty(node, false, false);
default:
warn("Unknown token " + node.getSymbolicTokenType() + " in ORDER BY clause", node);
return false;
}
}
/**
* Generate a list of order by fields ASTs. The resulting list of nodes are the 4GL fields
* used in original query, not the expressions to be used in the SQL to be constructed.
* These two lists are different.
*
* @param orderNode
* The root node for the order by expression.
*
* @return a list of order by fields ASTs.
*
*/
private List<Aast> getOrderByAsts(FQLAst orderNode)
{
List<Aast> orderAsts = new ArrayList<>();
int cnt = orderNode.getNumImmediateChildren();
for (int k = 0; k < cnt; k++)
{
generateOrderByField((FQLAst) orderNode.getChildAt(k), orderAsts);
}
return orderAsts;
}
/**
* Append an order by field AST to {@code orderAsts}.
*
* @param node
* The root node for this expression.
* @param orderAsts
* list of order by fields ASTs.
*/
private void generateOrderByField(FQLAst node, List<Aast> orderAsts)
{
switch (node.getType())
{
case FUNCTION:
generateOrderByField((FQLAst) node.getFirstChild(), orderAsts);
break;
case ALIAS:
orderAsts.add(node);
break;
}
}
/**
* Convert the SINGLE FQL token to a dialect-based <code>LIMIT 1</code> expression.
*
* @param single
* The SINGLE AST. May be <code>null</code>.
*/
private void generateSingle(FQLAst single)
{
if (single == null)
{
return;
}
sb.append(dialect.getLimitString("", 0, 1));
}
/**
* Checks whether this is case-insensitive character field.
*
* @param alias
* The {@code alias} node. This represents the table this property belongs to.
* Normally it should have a single child, with the name of the {@code PROPERTY}.
*
* @return {@code true} if this property is detected as a case-insensitive character field.
*/
private boolean isCaseInsensitiveField(FQLAst alias)
{
String aliasStr = alias.getText();
if (aliasStr.length() == 1 && aliasStr.charAt(0) == ASTERISK)
{
return false;
}
switch (alias.getType())
{
case STRING:
case DEC_LITERAL:
case NUM_LITERAL:
case LONG_LITERAL:
case BOOL_TRUE:
case BOOL_FALSE:
case NULL:
return false;
}
DmoMeta dmoInfo = getScopedDmoInfo(aliasStr);
if (dmoInfo == null)
{
warn("Invalid alias: ", alias);
return false;
}
FQLAst property = (FQLAst) alias.getFirstChild();
if (property == null)
{
warn("Unexpected null property: ", property);
return false;
}
String propertyStr = property.getText();
Property propertyAnn = dmoInfo.propsByName.get(propertyStr);
if (propertyAnn == null)
{
warn("Invalid property: ", property);
return false;
}
if (propertyAnn.caseSensitive)
{
return false;
}
return propertyAnn._isCharacter;
}
/**
* Converts a FQL property to a SQL field name. The output is directly appended to {@link #sb}.
*
* @param node
* The {@code alias} or {@code property} node. If the former, this represents the table this
* property belongs to. Normally it should have a single child, with the name of the {@code
* PROPERTY}. If the latter, this represents an unqualified property name (and a single-table
* statement is assumed).
* @param forceCsPrefix
* If {@code true}, the dialect specific case-sensitive prefix will be injected.
* @param qualified
* {@code true} if property is qualified by an alias; {@code false} if {@code node}
* represents an unqualified property name.
*
* @return <code>true</code> if this is the '_multiplex' or PK column.
*/
private boolean generateProperty(FQLAst node, boolean forceCsPrefix, boolean qualified)
{
return generateProperty(node, forceCsPrefix, qualified, null);
}
/**
* Converts a FQL property to a SQL field name. The output is directly appended to {@link #sb}.
*
* @param node
* The {@code alias} or {@code property} node. If the former, this represents the table this
* property belongs to. Normally it should have a single child, with the name of the {@code
* PROPERTY}. If the latter, this represents an unqualified property name (and a single-table
* statement is assumed).
* @param forceCsPrefix
* If {@code true}, the dialect specific case-sensitive prefix will be injected.
* @param qualified
* {@code true} if property is qualified by an alias; {@code false} if {@code node}
* represents an unqualified property name.
* @param fieldInfo
* Auxiliary structure holding table names and alias
*
* @return <code>true</code> if this is the '_multiplex' or PK column.
*/
private boolean generateProperty(FQLAst node,
boolean forceCsPrefix,
boolean qualified,
FieldInfo fieldInfo)
{
return generateProperty(node, forceCsPrefix, qualified, fieldInfo, null, null, false);
}
/**
* Converts a FQL property to a SQL field name. The output is directly appended to {@link #sb}.
*
* @param node
* The {@code alias} or {@code property} node. If the former, this represents the table this
* property belongs to. Normally it should have a single child, with the name of the {@code
* PROPERTY}. If the latter, this represents an unqualified property name (and a single-table
* statement is assumed).
* @param forceCsPrefix
* If {@code true}, the dialect specific case-sensitive prefix will be injected.
* @param qualified
* {@code true} if property is qualified by an alias; {@code false} if {@code node}
* represents an unqualified property name.
* @param fieldInfo
* Auxiliary structure holding table names and alias
* @param propertyBuffer
* Collects an unqualified property name
* @param funcLvl
* Has two roles:
* <ol>
* <li>
* Keeps track of the function level for cases when {@code type == FUNCTION}
* </li>
* <li>
* Works as a flag that indicates if the column unique name(s) should be generated
* as part of the generated properties.
* </li>
* </ol>
* @param currentHasContains
* Flag indicating a statement with CONTAINS.
*
* @return <code>true</code> if this is the '_multiplex' or PK column.
*/
private boolean generateProperty(FQLAst node,
boolean forceCsPrefix,
boolean qualified,
FieldInfo fieldInfo,
Map.Entry<String, Property>[] propertyBuffer,
Integer funcLvl,
boolean currentHasContains)
{
switch (node.getType())
{
case MULTIPLY: // ASTERISK ?
sb.append(ASTERISK);
return false;
case STRING:
case DEC_LITERAL:
case NUM_LITERAL:
case LONG_LITERAL:
case NULL:
sb.append(node.getText());
return false;
case BOOL_TRUE:
case BOOL_FALSE:
sb.append(dialect.supportsBooleanDatatype() ? node.getText() :
node.getType() == BOOL_TRUE ? "1=1" : "0=1");
return false;
}
String aliasStr = qualified ? node.getText() : NO_ALIAS;
boolean withAlias = !this.forceNoAlias && !aliasStr.isEmpty();
String aliasName = withAlias ? getSqlTableAliasName(aliasStr) : null;
if (withAlias)
{
if (fieldInfo != null)
{
DmoMeta dmoMeta = aliases.peek().get(aliasName);
if (dmoMeta != null)
{
fieldInfo.tblMetaKey = aliasName;
}
else // if (dmoMeta == null) -- evidently
{
dmoMeta = aliases.peek().get(aliasStr);
if (dmoMeta != null)
{
fieldInfo.tblMetaKey = aliasStr;
}
}
fieldInfo.tblName = dmoMeta != null ? dmoMeta.getSqlTableName() : aliasStr;
fieldInfo.tblAlias = aliasName;
}
else
{
sb.append(aliasName);
if (sbProp != null)
{
sbProp.append(aliasName);
}
}
}
boolean reserved = false;
FQLAst property = qualified ? (FQLAst) node.getFirstChild() : node;
if (property != null)
{
boolean prefixed = false;
String propertyStr = property.getText();
DmoMeta dmoInfo = getScopedDmoInfo(aliasStr);
if (dmoInfo == null)
{
warn("Invalid alias: ", node);
return false;
}
Property propertyAnn = dmoInfo.propsByName.get(propertyStr);
if (propertyAnn == null && fieldInfo != null)
{
List<Property> customExtent = dmoInfo.customExtents.get(propertyStr);
if (customExtent != null)
{
fieldInfo.fldName = customExtent.get(0).column;
fieldInfo.extent = customExtent.size();
return false;
}
}
if (propertyAnn == null && dialect.needsComputedColumns())
{
// analyze the propertyStr to see whether it is a ComputedColumn (used by case insensitive
// character fields)
String csPrefix = dialect.getComputedColumnPrefix(true);
if (csPrefix != null && propertyStr.startsWith(csPrefix))
{
propertyAnn = dmoInfo.propsByName.get(propertyStr.substring(csPrefix.length()));
prefixed = true;
}
else
{
String ciPrefix = dialect.getComputedColumnPrefix(false);
if (ciPrefix != null && propertyStr.startsWith(ciPrefix))
{
propertyAnn = dmoInfo.propsByName.get(propertyStr.substring(ciPrefix.length()));
prefixed = true;
}
}
}
if (propertyAnn == null)
{
// propertyAnn is [null] for [id], [_multiplex], [_rowState], etc
if (withAlias)
{
sb.append(".");
if (sbProp != null)
{
sbProp.append(".");
}
}
sb.append(propertyStr);
if (sbProp != null)
{
sbProp.append(propertyStr);
}
// we do not have information on this property. Is this a surrogate?
if (propertyBuffer != null)
{
propertyBuffer[0] = new AbstractMap.SimpleEntry<>(propertyStr, null);
}
warn("Property '" + propertyStr +
"' not found to extract column name. Assuming it is a column name already.", property);
}
else
{
reserved = (propertyAnn instanceof ReservedProperty) &&
(Session.PK.equals(propertyStr) ||
ReservedProperty._MULTIPLEX.name.equals(propertyStr));
String prefix = (prefixed || forceCsPrefix) ?
dialect.getComputedColumnPrefix(propertyAnn.caseSensitive) : "";
if (fieldInfo != null)
{
fieldInfo.extent = propertyAnn.extent;
fieldInfo.fldName = (propertyAnn.extent > 0)?
propertyAnn.column : prefix + propertyAnn.column;
}
else
{
if (withAlias)
{
sb.append(".");
}
if (sbProp != null)
{
sbProp.append(".");
}
if (prefix != null && !prefix.isEmpty())
{
sb.append(prefix); // do not add the prefix if the dialect does not need it
if (sbProp != null)
{
sbProp.append(prefix);
}
}
sb.append(propertyAnn.column);
if (sbProp != null)
{
sbProp.append(propertyAnn.column);
}
if (propertyBuffer != null)
{
propertyBuffer[0] = new AbstractMap.SimpleEntry<>(propertyAnn.name, propertyAnn);
}
}
}
// indicates if the unique names should be generated
// funcLvl != null only if this method is called from processSelect()
if (funcLvl != null)
{
while (funcLvl > 0)
{
sb.append(")");
funcLvl = funcLvl - 1;
}
if (generateUniqueSqlColumnNames)
{
sb.append(" as ");
appendColAlias("col", currentHasContains);
}
if (propertyAnn != null && propertyAnn._isDatetimeTz)
{
// for datetime-tz fields, the offset needs to be retrieved as well
sb.append(", ");
if (withAlias)
{
sb.append(aliasName);
}
sb.append(".").append(propertyAnn.column).append(DDLGeneratorWorker.DTZ_OFFSET);
if (generateUniqueSqlColumnNames)
{
// Covers a case during a FILL operation when there is a DataRelation.
// The problem is that the number of columns will not match when building
// the second pass query for recursive DATA-RELATION FILL because the
// DATETIME-TZ is split into 2 columns in the database and the Recursive
// CTE name is only specifying the selectAliases collection which will not
// contain this additional TZ alias as it was not collected.
sb.append(" as ");
String dtzColAlias = appendColAlias("col", currentHasContains);
String legacy = propertyAnn.legacy + DDLGeneratorWorker.DTZ_OFFSET;
collectAliases.accept(legacy, dtzColAlias);
collectFields.accept(legacy, propertyAnn.column);
}
}
}
}
else
{
warn("Invalid property: ", node);
}
return reserved;
}
/**
* Search for a specific alias in the stack of scoped aliases declared by current statement.
*
* @param aliasStr
* The name of the alias to be found.
*
* @return The {@code DmoMeta} structure for the specified alias.
*/
private DmoMeta getScopedDmoInfo(String aliasStr)
{
if (NO_ALIAS.equals(aliasStr))
{
HashMap<String, DmoMeta> innerScope = aliases.peek();
if (innerScope != null && innerScope.size() == 1)
{
return innerScope.entrySet().iterator().next().getValue();
}
warn("Failed to identify the default DMO (NO-ALIAS) in local scope (" + aliases.size() +
"). The generated SQL might not be valid.", null);
return null;
}
for (HashMap<String, DmoMeta> aliasMap : aliases)
{
DmoMeta dmoInfo = aliasMap.get(aliasStr);
if (dmoInfo != null)
{
return dmoInfo;
}
}
return null;
}
/**
* Generated the {@code WHERE} predicate of the SQL query. Walks each FQL node and converts
* it into a SQL representation which is appended to the {@link #sb}.
*
* @param where
* The {@code WHERE} node of the FQL query.
* @param extIdxConstraints
* The set of extra constraints required by extent indexes. For the specified aliases,
* a new constraint for the {@code list__index} is injected.
*/
private void generateWhere(FQLAst where, Map<String, String> extIdxConstraints)
{
boolean extraConstraints = extIdxConstraints != null && !extIdxConstraints.isEmpty();
boolean hasWhere = where != null && where.getFirstChild() != null;
if (!hasWhere && !extraConstraints)
{
return;
}
if (!optimizeContainsCTE || andTerms > 0 || extraConstraints)
{
sb.append("\nwhere\n\t");
}
if (extraConstraints)
{
sb.append("(");
boolean first = true;
for (Map.Entry<String, String> entry : extIdxConstraints.entrySet())
{
if (first)
{
first = false;
}
else
{
sb.append(" and ");
}
sb.append(entry.getKey()).append(".list__index=").append(entry.getValue());
}
if (hasWhere)
{
sb.append(") and \n\t(");
}
}
if (hasWhere)
{
generateExpression((FQLAst) where.getFirstChild());
}
if (extraConstraints)
{
sb.append(")");
}
}
/**
* Recursively walks a FQL predicate and generates the SQL one. The output is directly
* appended to {@link #sb}.
*
* @param node
* The root node for a FQL predicate to be processed.
*/
private void generateExpression(FQLAst node)
{
nestedInContains = 0;
final boolean[] ffd = { false }; // FAST FORWARD mode flag
AstWalkListener awl = new AstWalkListener()
{
/**
* Called when the AST walk ascends between levels of the tree. Used to close off certain
* constructs when emitting HQL.
*
* @param ast
* AST node to which the walk is ascending.
*/
public void ascent(Aast ast)
{
if (ffd[0] || ast == null)
{
return;
}
if ((((FQLAst) ast).state & FQLAst.ASCENDED) != 0)
{
return; //already processed
}
switch (ast.getType())
{
case IN:
case LPARENS:
case CAST:
sb.append(")");
break;
case FUNCTION:
if (nestedInContains > 0 )
{
nestedInContains--;
if (nestedInContains == 0)
{
ContainsRewriteData crwd = containsRewriteData.get(currentContains);
queryWasRewritten = true;
if (useWordTables && !dialect.useUdf4Contains())
{
if (dialect.useCTE4Contains())
{
if (crwd.useCTE)
{
containsWithWordTableAndCTE(crwd);
}
else
{
containsWithWordTable(crwd);
}
}
else
{
containsWithWordTable(crwd);
}
}
else
{
if (crwd.forSorting )
{
containsWithUdfAndCTE(crwd);
}
else
{
containsWithUdf(crwd);
}
}
}
}
else
{
sb.append(")");
}
break;
case TERNARY:
sb.append(") end");
break;
case LBRACKET:
sb.append("]");
break;
case IS_NULL:
sb.append(" is null");
break;
case NOT_NULL:
sb.append(" is not null");
break;
case SELECT:
case FROM:
case JOIN:
sb.append(" ");
break;
default:
break;
}
((FQLAst) ast).state |= FQLAst.ASCENDED;
}
/**
* Called when the AST walk descends between levels of the tree. Initially used to emit a
* dot operator after an alias, in order to dereference a property, it also adds a space
* to separate the text from certain nodes from the text of the first child.
*
* @param ast
* AST node from which the walk is descending.
*/
public void descent(Aast ast)
{
if (ffd[0] || ast == null)
{
return;
}
if ((((FQLAst) ast).state & FQLAst.DESCENDED) != 0)
{
return; //already processed
}
switch (ast.getType())
{
case SELECT:
case FROM:
case JOIN:
case WHERE:
case ESCAPE:
case NOT:
sb.append(" ");
break;
default:
break;
}
((FQLAst) ast).state |= FQLAst.DESCENDED;
}
/**
* Called when the AST walk moves laterally between sibling nodes. Used to emit the
* symbol of a binary operator between operands of a binary operation, or to emit a comma
* or other separators between function parameters or complete the operator's structure.
*
* @param ast
* AST parent node of the siblings between which the walk
* is moving laterally.
* @param index
* Zero-based index of the sibling node about to be visited.
*/
public void nextChild(Aast ast, int index)
{
if (ffd[0] || ast == null)
{
return;
}
if (isBinaryOperator(ast))
{
sb.append(" ");
if (index == 1 || ast.getType() != LIKE)
{
Aast func = (optimizeContainsCTE && ast.getType() == AND) ?
ast.getImmediateChild(FUNCTION, null) : null;
if (func != null && dialect.isUdfContains(func.getText()))
{
// "and" (ALIAS.recid in (select recid from wcte1)) is being skipped. We will
// later replace the left join with a simple join which will work the same.
return;
}
// LIKE is not a truly binary operator like +, -, || to use chaining
// in fact the other comparing operators are not, as well
sb.append(ast.getText());
sb.append(" ");
}
if (index == 1 && ast.getType() == IN)
{
sb.append("("); // start the list
}
}
else if (ast.getType() == IN)
{
if (index == 1)
{
sb.append(" in ("); // start the list
}
else
{
sb.append(", "); // separate the list elements
}
}
else
{
switch (ast.getType())
{
case FUNCTION:
if (nestedInContains == 0)
{
sb.append(", ");
}
break;
case CAST:
sb.append(" as ");
break;
case TERNARY:
switch (index)
{
case 1:
sb.append(" then (");
break;
case 2:
sb.append(") else (");
break;
default:
break;
}
break;
default:
break;
}
}
}
};
// walk the full tree and emit the expression into the string buffer
Iterator<Aast> iter = node.iterator(0, awl);
FQLAst next = null;
// iter.hasNext() is notifying listeners if there are no more elements, and this must not be done when
// we re-use the next AST, computed after processing a SUBSELECT (as the listeners were already notified)
while (iter.hasNext())
{
next = (FQLAst) iter.next();
int ttype = next.getType();
if (!isBinaryOperator(next))
{
String text = next.getText();
long card;
switch (ttype)
{
case POSITIONAL:
nargs++;
if (nestedInContains == 1)
{
break;
}
// fall through if not in CONTAINS
case SUBST:
sb.append(argRef.get());
card = (long) next.getAnnotation("paramCard");
if (card != 1)
{
for (int k = 1; k < card; k++)
{
sb.append(",").append(argRef.get());
}
}
break;
case CAST:
sb.append(text);
sb.append("(");
if (next.getNumImmediateChildren() == 0)
{
sb.append(")");
}
break;
case FUNCTION:
if (dialect.isUdfContains(text) || (nestedInContains > 0))
{
if (nestedInContains== 0)
{
currentContains++;
}
// TODO: do not add RTRIM for the CONTAINS field in the HQLPreprocessor
nestedInContains++;
}
else
{
if (text.equals(dialect.checkErrorFn()) || text.endsWith(_checkErrorFn))
{
containsErrorHandling = true;
}
sb.append(text);
sb.append("(");
if (next.getNumImmediateChildren() == 0)
{
sb.append(")");
}
}
break;
case TERNARY:
sb.append("case when ");
break;
case SUBSELECT:
ArrayList<RowStructure> rowStructureBackup = rowStructure;
rowStructure = new ArrayList<>(1);
FQLAst[] lastProcessedAst = new FQLAst[1];
processStatement(next, lastProcessedAst);
try
{
ffd[0] = true; // FF mode: consume this sub-select and don't emit anything
while (iter.hasNext() && lastProcessedAst[0] != iter.next())
;
}
finally
{
ffd[0] = false;
rowStructure = rowStructureBackup;
}
break;
case IS_NULL:
case NOT_NULL:
break;
case IN:
break; // [in] is emitted in infixed form, similar to binary operators
case ALIAS:
if (next.getParent().getType() != FROM)
{
if (nestedInContains == 0)
{
generateProperty(next, false, true);
}
}
// otherwise already processed with the FROM parent
break;
case PROPERTY:
if (next.getParent().getType() != ALIAS)
{
generateProperty(next, false, false);
}
// otherwise the property was already processed with the ALIAS parent
break;
case DMO:
// do not output this, it was / will be used with the alias in a FROM node
break;
case STRING:
SessionAttr.sessionAttr(text).ifPresent(sessionAttrs::add);
if (nestedInContains != 1)
{
sb.append(text);
}
break;
default:
sb.append(text);
break;
}
}
}
}
/**
* Convenience method to detect whether an AST represents a binary operator, based on its
* token type.
*
* @param ast
* AST node to test.
*
* @return {@code true} if the AST's token type indicates a binary operator, otherwise
* {@code false} is returned.
*/
private boolean isBinaryOperator(Aast ast)
{
switch (ast.getType())
{
case OR:
case AND:
case EQUALS:
case NOT_EQ:
case LIKE:
case GT:
case LT:
case GTE:
case LTE:
case PLUS:
case MINUS:
case MULTIPLY:
case DIVIDE:
case CONCAT:
return true;
}
return false;
}
/**
* Generates the list of source tables where the records are to be fetched (ie {@code FROM} and
* any type of {@code JOIN} part. For the first occurrence {@code FROM} keyword is used, for
* the subsequent, the appropriate {@code JOIN} is generated.
*
* @param node
* A {@code FQLAst} node which contains the node/s.
* @param innerJoins
* A map with inner aliases to be joined.
* @return the alias of the master table of the query
*/
private String generateFrom(FQLAst node, Map<String, String> innerJoins)
{
sb.append("\nfrom\n\t");
boolean first = true;
String rightAlias = null;
String masterAlias = null;
DmoMeta rightDmoInfo = null;
int cnt = node.getNumImmediateChildren();
for (int k = 0; k < cnt; k++)
{
FQLAst child = (FQLAst) node.getChildAt(k);
if (child.getType() == ALIAS || (cnt == 1 && child.getType() == DMO))
{
rightAlias = child.getType() == ALIAS ? child.getText() : NO_ALIAS;
rightDmoInfo = getScopedDmoInfo(rightAlias);
if (rightDmoInfo != null)
{
if (first)
{
first = false;
masterAlias = rightAlias;
}
else
{
// sb.append(", ");
// sb.append(" cross join ");
sb.append("\ncross join\n\t");
}
sb.append(rightDmoInfo.getSqlTableName()).append(" ").
append(getSqlTableAliasName(rightAlias));
}
else
{
warn("Alias '" + rightAlias + "' not known ", node);
}
}
else if (child.getType() == JOIN)
{
generateJoin(child, rightAlias, rightDmoInfo);
}
}
if (innerJoins != null && !innerJoins.isEmpty())
{
for (Map.Entry<String, String> entry : innerJoins.entrySet())
{
String child = getSqlTableAliasName(entry.getKey());
String father = getSqlTableAliasName(entry.getValue());
String sqlTable = aliases.peek().get(child).getSqlTableName();
sb.append("\ninner join\n\t").append(sqlTable).append(" ").append(child).
append(" on ").append(father).append(".").append(Session.PK).
append("=").append(child).append(".parent__id ");
}
}
sb.append(" ");
return masterAlias;
}
/**
* Generates the JOIN part of the query statement.
*
* @param joinNode
* The {@code FQLAst} node which has the FQL JOIN data.
* @param rightAlias
* The right alias.
* @param rightDmo
* The DMO info for the right table.
*/
private void generateJoin(FQLAst joinNode, String rightAlias, DmoMeta rightDmo)
{
if (joinNode.isAnnotation("composite-join"))
{
FQLAst compositeDmo = (FQLAst) joinNode.getImmediateChild(ALIAS, null);
FQLAst compositeAlias = (FQLAst) joinNode.getImmediateChild(ALIAS, compositeDmo);
String alias = compositeAlias.getText();
DmoMeta dmoAnn = getScopedDmoInfo(alias);
if (dmoAnn == null)
{
warn("Alias '" + rightAlias + "' not known ", joinNode);
return;
}
// sb.append(" inner join ");
sb.append("\ninner join\n\t");
String sqlCompositeTableAliasName = getSqlTableAliasName(alias);
sb.append(dmoAnn.getSqlTableName())
.append(" ")
.append(sqlCompositeTableAliasName);
// sb.append(" on ");
sb.append("\non\n\t");
sb.append(getSqlTableAliasName(rightAlias))
.append(".").append(Session.PK).append("=") // TODO: extract constant for [id]
.append(sqlCompositeTableAliasName)
.append(".parent__id"); // TODO: extract constant for [parent__id]
return;
}
boolean cross = true;
if (joinNode.getImmediateChild(OUTER, null) != null)
{
if (joinNode.getImmediateChild(LEFT, null) != null)
{
sb.append("\nleft ");
}
else if (joinNode.getImmediateChild(RIGHT, null) != null)
{
sb.append(" right ");
}
sb.append(" outer ");
cross = false;
}
else if (joinNode.getImmediateChild(FULL, null) != null)
{
sb.append(" full ");
cross = false;
}
else if (joinNode.getImmediateChild(INNER, null) != null)
{
sb.append(" inner ");
cross = false;
}
if (cross)
{
sb.append(" cross ");
}
FQLAst dmo = (FQLAst) joinNode.getImmediateChild(DMO, null);
String alias = dmo.getRightAdjacent().getText();
DmoMeta dmoInfo = getScopedDmoInfo(alias);
// sb.append(" join ");
sb.append("join\n\t")
.append(dmoInfo.getSqlTableName())
.append(" ")
.append(getSqlTableAliasName(alias));
this.generateOnClause((FQLAst) joinNode.getImmediateChild(ON, null));
}
/**
* Generates the on clause for a join.
*
* @param onClause
* The node from where we start to generate the "on" clause.
*/
private void generateOnClause(FQLAst onClause)
{
if (onClause == null || onClause.getFirstChild() == null)
{
return;
}
sb.append("\non\n\t");
generateExpression((FQLAst) onClause.getFirstChild());
}
/**
* Collects into {@code aliasesMap} all occurrences of aliases with normalized accesses to extents fields
* (that is, using bracket {@code [} notation).
* <p>
* Important: only the normalized tables that are already in the map are processed. If other normalized
* extent fields are encountered, they are skipped as they will be processed at the moment their main
* alias is collected (as part of a subselect, most likely).
*
* @param node
* The root node to start from (the {@code WHERE} or {@code ORDER} node).
* @param aliasesMap
* The {@code Map} where the new aliases will be stored.
* @param innerJoins
* The map where to save the inner join information.
* @param staticSubscriptConstraints
* The consumer to be applied for static subscript fields.
* @param dynamicSubscriptConstraints
* The consumer to be applied for dynamic subscript fields.
*/
private void collectNormalizedExtentsAliases(FQLAst node,
Map<String, DmoMeta> aliasesMap,
Map<String, String> innerJoins,
BiConsumer<FQLAst, List<FQLAst>> staticSubscriptConstraints,
BiConsumer<FQLAst, List<FQLAst>> dynamicSubscriptConstraints)
{
if (node == null || node.getFirstChild() == null)
{
return; // if the where predicate is absent, nothing to collect
}
List<FQLAst> dynamicSubscripts = new ArrayList<>(paramCount);
List<FQLAst> staticSubscripts = new ArrayList<>(paramCount);
final Aast[] crtSubselect = { null }; // nasty Java final requirement
AstWalkListener awl = new AstWalkListener()
{
@Override
public void descent(Aast ast)
{
if (ast.getType() == SUBSELECT && crtSubselect[0] == null)
{
crtSubselect[0] = ast;
}
}
@Override
public void ascent(Aast ast)
{
if (crtSubselect[0] != null)
{
if (ast == crtSubselect[0])
{
crtSubselect[0] = null;
}
return; // nothing more to process here
}
if (ast.getType() == LBRACKET && ast.getParent().getType() == PROPERTY)
{
Aast grandFather = ast.getParent().getParent();
if (grandFather.getType() == ALIAS)
{
Aast contains = insideContains(grandFather);
if (contains != null)
{
ast.remove();
}
else
{
DmoMeta dmoMeta = aliasesMap.get(grandFather.getText());
String extentName = ast.getParent().getText();
Property extProperty = dmoMeta.propsByName.get(extentName);
if (extProperty == null && ReservedProperty.isReservedProperty(extentName))
{
warn("Failed to process extent ALIAS. " +
"Probable caused by a WORD index which is not supported by FWD yet.",
(FQLAst) ast);
extProperty = dmoMeta.propsByName.get(extentName.substring(3));
if (extProperty == null)
{
return;
}
}
// if the extent property was denormalized, treat it as a simple field
int extent = extProperty.index > 0 ? 0 : extProperty.extent;
if (extent == 0)
{
warn("[] syntax for a non extent property.", (FQLAst) grandFather);
return;
}
FQLAst subscriptAst = (FQLAst) ast.getFirstChild();
String extIdx = subscriptAst.getText();
String extIdxLit = extIdx;
if (subscriptAst.getType() != NUM_LITERAL)
{
// make the extent index unique - to allow multiple indexed properties with complex exprs
extIdx = "d" + (complexSubscript++);
// extIdxLit = "?";
}
String grandfatherSqlAlias = getSqlTableAliasName(grandFather.getText());
String newSqlAlias = grandfatherSqlAlias + "_" + extent + "x" + extIdx + "_"; // aliases(grandfather)
aliasesMap.putIfAbsent(newSqlAlias, new CompositeDmoInfo(dmoMeta, extent));
if (innerJoins != null)
{
innerJoins.putIfAbsent(newSqlAlias, grandfatherSqlAlias);
}
if (subscriptAst.getType() == NUM_LITERAL)
{
staticSubscripts.add((FQLAst) grandFather);
}
else
{
dynamicSubscripts.add((FQLAst) grandFather);
}
grandFather.putAnnotation("parentAlias", grandfatherSqlAlias);
grandFather.putAnnotation("subscript", extIdxLit);
grandFather.setText(newSqlAlias);
ast.remove();
}
}
else
{
warn("Generate SQL from unknown node.", (FQLAst) grandFather);
}
}
if (node.getType() == WHERE && ast.getType() == FUNCTION
&& dialect.isUdfContains(ast.getText()))
{
ContainsRewriteData rewriteData = new ContainsRewriteData();
Aast arg = ast.getImmediateChild(null, FUNCTION, ALIAS);
// TODO: do not add RTRIM for the CONTAINS field in the HQLPreprocessor
while (arg != null && arg.getType() == FUNCTION)
{
arg = arg.getImmediateChild(null, FUNCTION, ALIAS);
}
if (arg == null)
{
warn("Generate SQL from unknown node.", (FQLAst) ast);
throw new IllegalStateException();
}
rewriteData.fieldAst = (FQLAst) arg;
Aast expr = ast.getImmediateChild(null, POSITIONAL, STRING);
if (expr == null)
{
warn("Generate SQL from unknown node.", (FQLAst) ast);
throw new IllegalStateException();
}
if (expr.getType() == POSITIONAL)
{
rewriteData.narg = ((Long) expr.getAnnotation("index")).intValue();
Object oParam = queryParams.parameters[rewriteData.narg];
character param;
if (oParam instanceof P2JQuery.ParamResolver)
{
param = (character)((P2JQuery.ParamResolver) oParam).resolve();
queryParams.parameters[rewriteData.narg] = param;
}
else
{
param = (character) oParam;
}
rewriteData.expr = param == null || param.isUnknown() ? "" :
param.toStringMessage();
}
else
{
String text = expr.getText();
rewriteData.expr = text.substring(1, text.length() - 1);
}
if (!useWordTables || dialect.useUdf4Contains())
{
rewriteData.toRPN();
}
else
{
rewriteData.toCNF(dialect.useMixedNode4Contains());
}
if (rewriteData.terms.isEmpty())
{
rewriteData.alwaysFalse = true;
}
containsRewriteData.add(rewriteData);
}
}
/**
* Check if the given Ast is inside a CONTAINS UDF call.
*
* @param node
* AST to be checked.
*
* @return CONTAINS UDF call AST or {@code null}.
*/
private Aast insideContains(Aast node)
{
Aast parent = node.getParent();
while (parent != null && parent.getType() == FUNCTION)
{
if (dialect.isUdfContains(parent.getText()))
{
return parent;
}
parent = parent.getParent();
}
return null;
}
@Override
public void nextChild(Aast ast, int index)
{
}
};
Iterator<Aast> iter = node.iterator(0, awl);
while (iter.hasNext())
{
iter.next();
}
staticSubscriptConstraints.accept(node, staticSubscripts);
dynamicSubscriptConstraints.accept(node, dynamicSubscripts);
}
/**
* Rewrite the dynamic subscripts in a SELECT's WHERE clause, so that the field's index is matched before
* testing the field's value, to preserve substitution argument order.
*
* @param whereNode
* The parent WHERE node.
* @param dynamicSubscripts
* The list of ALIAS ASTs which use dynamic subscripts.
*/
private void rewriteSelectDynamicSubscripts(FQLAst whereNode, List<FQLAst> dynamicSubscripts)
{
for (FQLAst aliasAst : dynamicSubscripts)
{
String subStr = aliasAst.getAnnotation("subscript").toString(); // it's a String, anyway
int subNo = Integer.parseInt(subStr.substring(1)); // skip the first character ('?')
FQLAst exprNode = (FQLAst) whereNode.getFirstChild();
exprNode.remove(); // it's the only child of the WHERE node, so we don't need to save its position
// create AND operator
FQLAst and = new FQLAst(new Token(AND));
and.setText("and");
// add the 'alias.list__index = ?' condition
FQLAst eq = new FQLAst(new Token(EQUALS));
eq.setText("=");
FQLAst alias = new FQLAst(new Token(ALIAS));
alias.setText(aliasAst.getText());
FQLAst property = new FQLAst(new Token(PROPERTY));
property.setText("list__index");
alias.addChild(property);
eq.addChild(alias);
FQLAst pos = new FQLAst(new Token(POSITIONAL));
pos.setText("?");
pos.putAnnotation("paramNo", (long) subNo);
pos.putAnnotation("paramCard", 1L); // the 'extent' is always *an* integer
eq.addChild(pos);
and.addChild(eq);
if (exprNode.getType() == OR)
{
// create a LPARENS root
FQLAst lparens = new FQLAst(new Token(LPARENS));
lparens.setText("(");
lparens.addChild(exprNode);
and.addChild(lparens);
}
else
{
and.addChild(exprNode);
}
whereNode.addChild(and);
// shift the parameters, too:
if (paraPerm == null)
{
paraPerm = new int[queryParams.maxParamIndex + 1];
for (int k = 0; k < paraPerm.length; k++)
{
paraPerm[k] = k;
}
}
System.arraycopy(paraPerm, 0, paraPerm, 1, subNo);
paraPerm[0] = subNo;
}
}
/**
* Collects the list of aliases declared in a FQL {@code FROM} or {@code JOIN}. For each encountered alias,
* a {@code DmoMeta} structure is added. It will be used later to convert the table name and properties to
* SQL values.
*
* @param node
* The node which contains the declarations of aliases for this query.
*/
private void collectTableAliases(FQLAst node, Map<String, DmoMeta> aliases)
{
if (node == null)
{
return;
}
int cnt = node.getNumImmediateChildren();
for (int k = 0; k < cnt; k++)
{
FQLAst child = (FQLAst) node.getChildAt(k);
if (child.getType() == DMO)
{
FQLAst alias = (FQLAst) node.getChildAt(k + 1); // childAt(n) returns null, no IOOBE is thrown
String dmoName = child.getText();
DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(dmoName, schema);
if (dmoInfo != null)
{
if (dmoInfo.getMetaTable() == SystemTable._UserTableStat)
{
userTableStatRead = true;
}
if (dmoInfo.getMetaTable() == SystemTable._Lock)
{
lockTableRead = true;
}
if (alias == null || alias.getType() == ALIAS)
{
String aliasName;
if (alias != null)
{
k++;
aliasName = alias.getText();
}
else
{
// special case: single table statement with no alias provided in FROM clause, only
// the DMO implementation name; all properties are unqualified
aliasName = NO_ALIAS;
}
aliases.put(aliasName, dmoInfo);
String sqlAlias = dmoName.substring(0, Math.min(dmoName.length(), 10));
sqlAlias = sqlAlias.toLowerCase() + this.sqlTableAliasMap.size() + "_";
sqlTableAliasMap.put(aliasName, sqlAlias);
}
}
else
{
warn("Failed to resolve metadata for '" + dmoName + "' DMO in '" + schema + "' schema. " +
"The generated SQL might not be valid.", node);
}
}
else if (child.getType() == ALIAS &&
child.getFirstChild() != null &&
node.isAnnotation("composite-join"))
{
FQLAst dmo = (FQLAst) node.getPrevSibling().getPrevSibling();
FQLAst composite = (FQLAst) child.getFirstChild();
FQLAst extentAlias = (FQLAst) child.getNextSibling(); // @ k + 1
DmoMeta dmoInfo = DmoMetadataManager.getDmoInfo(dmo.getText(), schema);
// RecordMeta meta = new RecordMeta(dmoInfo.iface); -- useless at this moment
// detecting the composite table by parsing the PROPERTY below current ALIAS
int extent = Integer.parseInt(composite.getText().substring("composite".length()));
aliases.put(extentAlias.getText(), new CompositeDmoInfo(dmoInfo, extent));
String compositeTableName =
"composite" + sqlTableAliasMap.size() + "x" + sqlTableAliasMap.size() + "_";
sqlTableAliasMap.put(extentAlias.getText(), compositeTableName);
k++;
}
else if (child.getType() == JOIN)
{
collectTableAliases(child, aliases);
}
}
}
/**
* Obtain the SQL table alias for a FQL alias.
*
* @param alias
* The FQL alias for a table.
*
* @return The SQL table alias. If not found or SQL aliases are not enabled, the FQL alias
* is returned.
*/
private String getSqlTableAliasName(String alias)
{
if (!useSqlTableAlias)
{
return alias;
}
String sqlAlias = sqlTableAliasMap.get(alias);
return sqlAlias == null ? alias : sqlAlias;
}
/**
* Computes an unique SQL name for a field/column.
*
* @param property
* The property that represent the field to be returned by a SELECT statement.
*
* @return An unique identifier for this column.
*/
private String getSqlColumnAlias(Property property)
{
if (property.propId == ReservedProperty.ID_PRIMARY_KEY)
{
return "id" + (columnUid++) + "_";
}
else if (property.propId < 0)
{
return "column" + (columnUid++) + "_" + (aliases.size() - 1) + "_";
}
else
{
String fqlName = property.column;
int k = 0;
while (k < fqlName.length() && Character.isLetter(fqlName.charAt(k)))
{
k++;
}
String sqlName = fqlName.substring(0, k);
if (sqlName.isEmpty())
{
sqlName = "column";
}
return sqlName + (columnUid++) + "_" + (aliases.size() - 1) + "_";
}
}
/**
* Appends a unique column alias to the sql statement.
*
* @param base
* The base prefix used to build the alias.
*/
private void appendColAlias(String base)
{
appendColAlias(base, true);
}
/**
* Appends a unique column alias to the sql statement.
*
* @param base
* The base prefix used to build the alias.
* @param addToRowAliases
* If we should add the alias to the rowAliases list when converting a statement with contains.
*
* @return The unique column alias.
*/
private String appendColAlias(String base, boolean addToRowAliases)
{
String columnAlias = base + (columnUid++) + "_" + (aliases.size() - 1) + "_";
sb.append(columnAlias);
if (cte4order != null && addToRowAliases)
{
rowAliases.add(columnAlias);
}
return columnAlias;
}
/**
* Logging utility method. Writes a warning message and the node/location of the issue encountered.
*
* @param msg
* The message to be printed.
* @param node
* The node that caused the issue (may be null).
*/
private static void warn(String msg, FQLAst node)
{
if (LOG.isLoggable(Level.WARNING))
{
LOG.log(Level.WARNING, (node != null) ? msg + " " + node.toStringVerbose() : msg);
}
}
/**
* Generate a SQL condition ('=' or 'LIKE') for an operation on the CNF.
*
* @param token
* CNF token
* @param args
* holder for the generated query parameters
*
* @return a SQL condition ('=' or 'LIKE') for an operation on the CNF
*/
private static String op(Term token, List<String> args)
{
if (token instanceof Equals)
{
args.add(token.getValue());
return "=";
}
if (token instanceof StartsWith)
{
args.add(token.getValue() + "%");
return "like";
}
// CNF for a valid CONTAINS argument does not contain negations
throw new IllegalStateException("Unexpected CNF token:" + token);
}
/**
* Conditionally wrap operation on a StringBuilder with upper() call.
*
* @param wrap
* Flag indicating that wrapping is required.
* @param sb
* The output {@code StringBuilder} instance.
* @param op
* Operation to be wrapped.
*/
private static void toUpper(boolean wrap, StringBuilder sb, Runnable op)
{
if (!wrap)
{
op.run();
}
else
{
sb.append("upper(");
op.run();
sb.append(")");
}
}
/**
* The immutable structure for a composite table. It adds only the extent as internal data and
* foreign key to its {@code parent} DMO table.
*/
private static class CompositeDmoInfo
extends DmoMeta
{
/** The parent DMO structure. */
private final DmoMeta parentDmoInfo;
/** The extent for the fields in this table. */
private final int extent;
/** The SQL name of the table. It is composed of the parent's table name and the extent. */
private final String sqlTableName;
/**
* The constructor initializes the data structure.
*/
CompositeDmoInfo(DmoMeta parent, int extent)
{
super(parent.dmoImplInterface, parent.iface, parent.table);
this.parentDmoInfo = parent;
this.extent = extent;
this.propsByName = new LinkedHashMap<>(parent.propsByName);
this.propsByName.put("list__index", ReservedProperty.LIST__INDEX);
this.propsByName.put("parent__id", ReservedProperty.PARENT__ID);
this.sqlTableName = parent.getSqlTableName() + "__" + extent; // this is weak
}
/**
* Obtain the name of the SQL table for this DMO.
*
* @return the name of the SQL table for this DMO.
*/
public String getSqlTableName()
{
return sqlTableName;
}
/**
* Get parent DmoMeta.
*
* @return The parent DmoMeta.
*/
public DmoMeta getParentDmoInfo()
{
return parentDmoInfo;
}
}
/**
* Field info holder
*/
private static class FieldInfo
{
/** The table name. */
public String tblName = null;
/** The table alias. */
public String tblAlias = null;
/** The table key in the aliases map. */
public String tblMetaKey = null;
/** The field name. */
public String fldName = null;
/** The extent size ({@code > 0} only for custom extents). */
public int extent = 0;
}
/**
* Auxiliary date used for CONTAINS UDF call re-writing
*/
private static class ContainsRewriteData
extends FieldInfo
{
/** Field AST */
public FQLAst fieldAst;
/** CTE name */
public String cteName = null;
/** CONTAINS UDF second argument value */
public String expr = null;
/** CNF representation of expr */
public List<List<Term>> cnf;
/** Distinct terms found in the expression */
private Map<String, Term> terms = new LinkedHashMap<>();
/** Flags that expression is good for using CTE */
public boolean useCTE = true;
/** The argument index (if placeholder found). */
public int narg = -1;
/** the condition is always false (is empty or invalid) */
public boolean alwaysFalse = false;
/** Flag that word index is used for implicit sorting of the result set */
public boolean forSorting = false;
/** the holder for the generated query parameters */
public List<String> args = new ArrayList<>();
/** The field Property. */
public Property fp;
/** The extent table (for extent field). */
public String extentTable;
/**
* Generate a replacement for the CONTAINS UDF call.
*
* @param schema
* schema name
* @param sb
* output buffer
*/
public void containsQuery(String schema, StringBuilder sb)
{
if (this.alwaysFalse)
{
sb.append("1 = 0");
return;
}
sb.append(tblAlias).append(".").append(PK).append(" in (\n");
containsCTE(schema, null, null, sb);
sb.append(")");
}
/**
* Convert 'expr' to CNF
*
* @param useMixedMode
* Set 'useCTE' flag based on the expression complexity.
*/
public void toCNF(boolean useMixedMode)
{
LogicalExpressionConverter converter = new LogicalExpressionConverter(expr, true);
cnf = converter.toCNF(false);
terms = converter.getTerms();
if (useMixedMode)
{
useCTE = cnf.size() < 2;
}
}
/** Validate 'expr' by converting to RPN. */
public void toRPN()
{
LogicalExpressionConverter converter = new LogicalExpressionConverter(expr, true);
converter.toRPN(false);
terms = converter.getTerms();
useCTE = false;
}
/**
* Generate a WITH clause for the {@code CONTAINS} UDF call.
*
* @param schema
* The schema name.
* @param aggregator
* The aggregate helper.
* @param weights
* The holder for ORDER BY components (used for implicit sorting).
* @param sb
* The output buffer.
*
* @return {@code true} if a new term in the WITH clause.
*/
public boolean containsWith(String schema,
Optional<Dialect.WeightAggregator> aggregator,
StringBuilder weights,
StringBuilder sb)
{
if (this.alwaysFalse)
{
return false;
}
sb.append(cteName).append(" as (\n");
containsCTE(schema, aggregator, weights, sb);
sb.append(")\n");
return true;
}
/**
* Generate a WITH clause for the {@code CONTAINS} UDF call (no word tables).
*
* @param schema
* The schema name.
* @param dialect
* The database dialect.
* @param weights
* The holder for ORDER BY components (used for implicit sorting).
* @param sb
* The output buffer.
*/
public void containsWith4UDF(String schema,
Dialect dialect,
StringBuilder weights,
StringBuilder sb)
{
sb.append(cteName).append(" as (\n");
containsCTE4UDF(schema, dialect, weights, sb);
sb.append(")\n");
}
/**
* Generate common table expression for {@code CONTAINS} (no word tables).
*
* @param schema
* The schema name.
* @param dialect
* The database dialect.
* @param weights
* The holder for ORDER BY components (used for implicit sorting).
* @param sb
* The output buffer.
*/
private void containsCTE4UDF(String schema,
Dialect dialect,
StringBuilder weights,
StringBuilder sb)
{
Optional<Dialect.WeightAggregator> aggregator = dialect.weightAggregator();
sb.append("\tselect "); //distinct ").append(PK).
String indent = "";
String placeHolder = fp.caseSensitive? " ?" : " UPPER(?)";
BiConsumer<StringBuilder, Runnable> aggregate = aggregator.get().aggregate();
String minAggrValue = aggregator.get().minAggregatedValue();
String tbl = extentTable != null ? "e" : "t";
String contains = dialect.containsUdfName();
for (Term t: terms.values())
{
args.add(t.containsExpr());
String calias = "ww" + t.getOrder();
aggregate.accept(sb, () -> {
sb.append(contains).append("(").append(tbl).append(".").append(fldName).
append(", ").append(placeHolder).append(")");
});
sb.append(" as ").append(calias).append(", ");
weights.append("coalesce(").
append(cteName).append(".").append(calias).
append(", ").append(minAggrValue).append(") desc, ");
}
sb.append(indent).append("t.").append(PK).
append("\n\t").append("from ").append(tblName).append(" t\n");
if (extentTable != null)
{
sb.append("\tjoin ").
append(extentTable).
append(" e on (e.parent__id = t.").append(PK).append(")\n");
}
sb.append("\twhere ").append(contains).append("(").
append(tbl).append(".").append(fldName).
append(", ").append(placeHolder).append(") ");
sb.append("group by t.").append(PK);
args.add(expr);
}
/**
* Generate common table expression for CONTAINS.
*
* @param schema
* The schema name.
* @param aggregator
* The aggregate helper.
* @param weights
* The holder for ORDER BY components (used for implicit sorting).
* @param sb
* The output buffer.
*/
private void containsCTE(String schema,
Optional<Dialect.WeightAggregator> aggregator,
StringBuilder weights,
StringBuilder sb)
{
WordIndexData indexData = MetadataManager.getWordTableName(schema, tblName, fldName);
if (indexData == null)
{
return; // TODO: not sure if this is the right action
}
sb.append("\tselect "); //distinct ").append(PK).
String indent = "";
List<Weight> w = new ArrayList<>(terms.size());
if (forSorting)
{
sb.append(indent).append("t.").append(PK);
int[] n = {1};
Map<String, List<Term>> cnfClone = cnf.stream()
.map(ArrayList::new)
.collect(Collectors.toMap(l -> "w" + n[0]++, Function.identity()));
terms.values().stream().forEach(term -> {
searchmatch:
for (Map.Entry<String, List<Term>> andTerm: cnfClone.entrySet())
{
Iterator<Term> ti = andTerm.getValue().iterator();
while (ti.hasNext())
{
Term t = ti.next();
if (term.expr().equals(t.expr()))
{
String walias = andTerm.getKey();
String calias = walias + t.getOrder();
w.add(new Weight(walias, calias, t));
ti.remove();
break searchmatch;
}
}
}
});
String placeHolder = indexData.caseSensitive ? " ?" : " UPPER(?)";
BiConsumer<StringBuilder, Runnable> aggregate = aggregator.get().aggregate();
String minAggrValue = aggregator.get().minAggregatedValue();
for (Weight ww : w)
{
sb.append(", ");
aggregate.accept(sb, () -> {
sb.append(ww.walias).append(".word ").
append(op(ww.term, args)).append(placeHolder);
});
sb.append(" as ").append(ww.calias);
weights.append("coalesce(").
append(cteName).append(".").append(ww.calias).
append(", ").append(minAggrValue).append(") desc, ");
}
}
else
{
// don't use distinct as it rules out some DB optimizations
// sb.append("distinct ");
sb.append(indent).append("t.").append(PK);
}
sb.append("\n\t").append("from ").append(tblName).append(" t\n");
String extentTableName = indexData.extentTableName;
if (extentTableName == null && extent > 0) // custom extent
{
extentTableName = "(select distinct parent__id, list__index from " + indexData.wordTableName + ")";
}
if (extentTableName != null)
{
sb.append("\tjoin ").
append(extentTableName).
append(" e on (e.parent__id = t.").append(PK).append(")\n");
}
int joinN = 1;
for (List<Term> andTerm: cnf)
{
String walias = "w" + joinN++;
sb.append("\t\tjoin ").
append(indexData.wordTableName).append(" as ").append(walias).
append(" on (").append(walias);
if (indexData.extentTableName != null)
{
sb.append(".parent__id = e.parent__id and ").
append(walias).append(".list__index = e.list__index");
}
else
{
sb.append(".parent__id = t.").append(PK);
}
sb.append(" and (");
onClause(sb, walias, andTerm, indexData.caseSensitive);
sb.append("))\n");
}
if (forSorting)
{
sb.append("\tgroup by ").append(PK).append("\n");
}
}
/**
* Generate an ON clause for a CONTAINS UDF call replacement query.
*
* @param sb
* The output buffer.
* @param walias
* The word table alias.
* @param andTerm
* The list of tokens in the AND term of the CNF.
* @param caseSensitive
* Flag indicating that field is case-sensitive.
*/
private void onClause(StringBuilder sb, String walias, List<Term> andTerm, boolean caseSensitive)
{
// NB: uppercase at the database side
String placeHolder = caseSensitive ? " ?" : " UPPER(?)";
Iterator<Term> tokens = andTerm.iterator();
Term token = tokens.next(); // always exists
sb.append(walias).append(".word ").append(op(token, args)).append(placeHolder);
while (tokens.hasNext())
{
token = tokens.next(); // always exists
sb.append(" or ").append(walias).append(".word ").append(op(token, args)).append(placeHolder);
}
}
/**
* Generate a 'weight' column for a CONTAINS UDF call replacement query.
*
* @param weights
* The holder for ORDER BY components (used for implicit sorting).
* @param sb
* The output buffer.
* @param aggregate
* The aggregate wrapper.
* @param walias
* The word table alias.
* @param andTerm
* The list of tokens in the AND term of the CNF.
* @param caseSensitive
* Flag indicating that field is case-sensitive.
*/
private void weights(StringBuilder weights,
StringBuilder sb,
BiConsumer<StringBuilder, Runnable> aggregate,
String walias, List<Term> andTerm,
boolean caseSensitive)
{
// NB: uppercase at the database side
String placeHolder = caseSensitive ? " ?" : " UPPER(?)";
for (Term t : andTerm)
{
String colAlias = walias + t.getOrder();
aggregate.accept(sb,
() -> sb.append(walias).append(".word ").append(op(t, args)).append(placeHolder));
sb.append(" as ").append(colAlias).append(", ");
weights.append(cteName).append(".").append(colAlias).append(" desc,");
}
}
}
/**
* An immutable structure which contains weight column data.
*/
private static class Weight
{
/** The word table alias. */
public final String walias;
/** The weight column alias. */
public final String calias;
/** The weight term. */
public final Term term;
/**
* Constructor.
*
* @param walias
* The word table alias.
* @param calias
* The weight column alias.
* @param term
* The weight term.
*/
public Weight(String walias, String calias, Term term)
{
this.walias = walias;
this.calias = calias;
this.term = term;
}
}
/**
* Test program.
*
* @param args
* The command line arguments.
*/
public static void main(String[] args)
{
FqlToSqlConverter fql2sql = getInstance(new P2JH2Dialect(), new Database("fwd"));
String sql = fql2sql.toSQL(
"UPDATE com.goldencode.testcases.dmo.fwd.Book SET bookId=1, bookTitle='X'",
0, 0, new ArrayList<>(), null, false);
System.out.println(sql);
}
}