/**********************************************************************
Copyright (c) 2008 Andy Jefferson and others. All rights reserved.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
Contributors:
...
**********************************************************************/
package org.datanucleus.store.rdbms.sql;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.datanucleus.ClassLoaderResolver;
import org.datanucleus.exceptions.NucleusException;
import org.datanucleus.store.mapped.DatastoreContainerObject;
import org.datanucleus.store.mapped.DatastoreField;
import org.datanucleus.store.mapped.DatastoreIdentifier;
import org.datanucleus.store.mapped.mapping.DatastoreMapping;
import org.datanucleus.store.mapped.mapping.JavaTypeMapping;
import org.datanucleus.store.rdbms.RDBMSStoreManager;
import org.datanucleus.store.rdbms.adapter.DatabaseAdapter;
import org.datanucleus.store.rdbms.adapter.RDBMSAdapter;
import org.datanucleus.store.rdbms.query.QueryGenerator;
import org.datanucleus.store.rdbms.sql.SQLJoin.JoinType;
import org.datanucleus.store.rdbms.sql.expression.AggregateNumericExpression;
import org.datanucleus.store.rdbms.sql.expression.AggregateTemporalExpression;
import org.datanucleus.store.rdbms.sql.expression.BooleanExpression;
import org.datanucleus.store.rdbms.sql.expression.BooleanLiteral;
import org.datanucleus.store.rdbms.sql.expression.SQLExpression;
import org.datanucleus.store.rdbms.sql.expression.SQLExpressionFactory;
import org.datanucleus.util.Localiser;
import org.datanucleus.util.NucleusLogger;
import org.datanucleus.util.StringUtils;
/**
* Class providing an API for generating SQL statements.
* Caller should create the SQLStatement object and (optionally) call setClassLoaderResolver() to set any
* class loading restriction. Then the caller builds up the statement using the various methods, and
* accesses the SQL statement using getStatement(). Generated statement is of the form :-
* <pre>
* SELECT {expr}, {expr}, ...
* FROM {tblExpr} [joinInfo {tblExpr} ON ...] ...
* WHERE {boolExpr} [AND|OR] {boolExpr} ...
* GROUP BY {expr}, {expr}
* HAVING {boolExpr}
* ORDER BY {expr} [ASC|DESC], {expr} [ASC|DESC], ...
* </pre>
* and also supports UNIONs between SQLStatements, and having sub-queries of other SQLStatements.
* Alternatively, for an UPDATE,
* <pre>
* UPDATE {tbl}
* SET {expr}={val}, {expr}={val}, ...
* WHERE {boolExpr} [AND|OR] {boolExpr} ...
* </pre>
* <p>
* The generated SQL is cached. Any use of a mutating method, changing the composition of the statement
* will clear the cached SQL, and it will be regenerated when <pre>getStatement</pre> is called next.
* <h3>Table Groups</h3>
* When tables are registered in the statement they are split into "table groups". A table group is,
* in simple terms, an object in the query. If a table has a super-table and a field of the object
* is selected that is in the super-table then the super-table is added to the table group. If there
* is a join to a related object then the table of this object will be put in a new table group.
* So the same datastore table can appear multiple times in the statement, each time for a different
* object.
* <h3>Table Aliases</h3>
* All methods that cause a new SQLTable to be created also allow specification of the table alias
* in the statement. Where the alias is not provided then we use a table "namer" (definable on the
* plugin-point "org.datanucleus.store.rdbms.sql_tablenamer"). The table namer can define names
* simply based on the table number, or based on table group and the number of tables in the group
* etc etc. To select a particular table "namer", set the extension "datanucleus.sqlTableNamingStrategy"
* to the key of the namer plugin. The default is "alpha-scheme" which bases table names on the
* group and number in that group.
*/
public class SQLStatement
{
/** Localisation for messages. */
protected static final Localiser LOCALISER = Localiser.getInstance(
"org.datanucleus.store.rdbms.Localisation", RDBMSStoreManager.class.getClassLoader());
/** Map of SQLTable naming instance keyed by the name of the naming scheme. */
protected static final Map<String, SQLTableNamer> tableNamerByName = new HashMap();
/** Cached SQL statement, generated by getStatement(). */
protected SQLText sql = null;
/** Manager for the RDBMS datastore. */
protected RDBMSStoreManager rdbmsMgr;
/** ClassLoader resolver to use. Used by sub-expressions. Defaults to the loader resolver for the store manager. */
protected ClassLoaderResolver clr;
/** Context of any query generation. */
protected QueryGenerator queryGenerator = null;
/** Name of class that this statement selects (optional, only typically for unioned statements). */
protected String candidateClassName = null;
/** Whether the statement is distinct. */
protected boolean distinct = false;
/** Map of extensions for use in generating the SQL, keyed by the extension name. */
protected HashMap<String, Object> extensions;
/** Parent statement, if this is a subquery. Must be set at construction. */
protected SQLStatement parent = null;
/** List of unioned SQLStatements (if any). */
protected List<SQLStatement> unions = null;
/** List of select objects. */
protected List<String> selects = new ArrayList();
/** Array of update expressions when the statement is an UPDATE. */
protected SQLExpression[] updates = null;
/** whether there is an aggregate expression present in the select **/
protected boolean aggregated = false;
/** Primary table for this statement. */
protected SQLTable primaryTable;
/** List of joins for this statement. */
protected List<SQLJoin> joins;
protected boolean requiresJoinReorder = false;
/** Map of tables referenced in this statement, keyed by their alias. */
protected Map<String, SQLTable> tables;
/** Map of table groups keyed by the group name. */
protected Map<String, SQLTableGroup> tableGroups = new HashMap<String, SQLTableGroup>();
/** Where clause. */
protected BooleanExpression where;
/** Expression(s) for the GROUP BY clause. */
protected List<SQLExpression> groupingExpressions = null;
/** Having clause. */
protected BooleanExpression having;
/** Expressions for any ORDER BY clause. */
protected SQLExpression[] orderingExpressions = null;
/** Directions for any ORDER BY expressions (1 for each orderingExpressions entry). */
protected boolean[] orderingDirections = null;
/** The offset for any range restriction. */
protected long rangeOffset = -1;
/** The number of records to be retrieved in any range restriction. */
protected long rangeCount = -1;
/**
* Constructor for an SQL statement.
* @param rdbmsMgr The datastore manager
* @param table The primary table
* @param alias Alias for this table
* @param tableGroupName Name of candidate table-group (if any). Uses "Group0" if not provided
*/
public SQLStatement(RDBMSStoreManager rdbmsMgr, DatastoreContainerObject table, DatastoreIdentifier alias,
String tableGroupName)
{
this(null, rdbmsMgr, table, alias, tableGroupName);
}
/**
* Constructor for an SQL statement that is a subquery of another statement.
* @param parentStmt Parent statement
* @param rdbmsMgr The datastore manager
* @param table The primary table
* @param alias Alias for this table
* @param tableGroupName Name of candidate table-group (if any). Uses "Group0" if not provided
*/
public SQLStatement(SQLStatement parentStmt, RDBMSStoreManager rdbmsMgr, DatastoreContainerObject table,
DatastoreIdentifier alias, String tableGroupName)
{
this.parent = parentStmt;
this.rdbmsMgr = rdbmsMgr;
String namerStrategy = rdbmsMgr.getStringProperty("datanucleus.rdbms.sqlTableNamingStrategy");
addExtension("datanucleus.sqlTableNamingStrategy", namerStrategy);
String tableGrpName = (tableGroupName != null ? tableGroupName : "Group0");
if (alias == null)
{
// No alias provided so generate one
alias = rdbmsMgr.getIdentifierFactory().newDatastoreContainerIdentifier(
generateTableAlias(table, tableGrpName));
}
this.primaryTable = new SQLTable(this, table, alias, tableGrpName);
putSQLTableInGroup(primaryTable, tableGrpName, null);
if (parentStmt != null)
{
// Use same query generator
queryGenerator = parentStmt.getQueryGenerator();
}
}
public RDBMSStoreManager getRDBMSManager()
{
return rdbmsMgr;
}
public void setClassLoaderResolver(ClassLoaderResolver clr)
{
this.clr = clr;
}
public ClassLoaderResolver getClassLoaderResolver()
{
if (clr == null)
{
clr = rdbmsMgr.getNucleusContext().getClassLoaderResolver(null);
}
return clr;
}
public void setCandidateClassName(String name)
{
this.candidateClassName = name;
}
public String getCandidateClassName()
{
return candidateClassName;
}
public QueryGenerator getQueryGenerator()
{
return queryGenerator;
}
public void setQueryGenerator(QueryGenerator gen)
{
this.queryGenerator = gen;
}
public SQLExpressionFactory getSQLExpressionFactory()
{
return rdbmsMgr.getSQLExpressionFactory();
}
public DatabaseAdapter getDatabaseAdapter()
{
return (DatabaseAdapter)rdbmsMgr.getDatastoreAdapter();
}
public SQLStatement getParentStatement()
{
return parent;
}
/**
* Convenience method to return if this statement is a child (inner) statement of the supplied
* statement.
* @param stmt The statement that may be parent, grandparent etc of this statement
* @return Whether this is a child of the supplied statement
*/
public boolean isChildStatementOf(SQLStatement stmt)
{
if (stmt == null || parent == null)
{
return false;
}
if (stmt == parent)
{
return true;
}
else
{
return isChildStatementOf(parent);
}
}
/**
* Method to define an extension for this query statement allowing control over its behaviour
* in generating a query.
* @param key Extension key
* @param value Value for the key
*/
public void addExtension(String key, Object value)
{
invalidateStatement();
if (extensions == null)
{
extensions = new HashMap();
}
extensions.put(key, value);
}
/**
* Accessor for the value for an extension.
* @param key Key for the extension
* @return Value for the extension (if any)
*/
public Object getValueForExtension(String key)
{
if (extensions == null)
{
return extensions;
}
return extensions.get(key);
}
/**
* Method to union this SQL statement with another SQL statement.
* @param stmt The other SQL statement to union
*/
public void union(SQLStatement stmt)
{
invalidateStatement();
if (unions == null)
{
unions = new ArrayList<SQLStatement>();
}
unions.add(stmt);
}
public int getNumberOfUnions()
{
if (unions == null)
{
return 0;
}
int number = unions.size();
Iterator<SQLStatement> unionIterator = unions.iterator();
while (unionIterator.hasNext())
{
SQLStatement unioned = unionIterator.next();
number += unioned.getNumberOfUnions();
}
return number;
}
/**
* Accessor for the unioned statements.
* @return The unioned SQLStatements
*/
public List<SQLStatement> getUnions()
{
return unions;
}
/**
* Convenience accessor for whether all unions of this statement are for the same primary table.
* @return Whether all unions have the same primary table
*/
public boolean allUnionsForSamePrimaryTable()
{
if (unions != null)
{
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement unionStmt = unionIter.next();
if (!unionStmt.getPrimaryTable().equals(primaryTable))
{
return false;
}
}
}
return true;
}
// --------------------------------- SELECT --------------------------------------
/**
* Accessor for whether the statement restricts the results to distinct.
* @return Whether results are distinct
*/
public boolean isDistinct()
{
return this.distinct;
}
/**
* Mutator for whether the query returns distinct results.
* @param distinct Whether to return distinct
*/
public void setDistinct(boolean distinct)
{
invalidateStatement();
this.distinct = distinct;
}
/**
* Accessor for the number of selected items in the SELECT clause.
* @return Number of selected items
*/
public int getNumberOfSelects()
{
return selects.size();
}
/**
* Select an expression.
* This will be used when adding aggregates to the select clause (e.g "COUNT(*)").
* @param expr The expression to add to the select statement
* @param alias Optional alias for this selected expression
* @return The index(es) of the expression in the select
*/
public int[] select(SQLExpression expr, String alias)
{
if (expr == null)
{
throw new NucleusException("Expression to select is null");
}
invalidateStatement();
if (expr instanceof AggregateNumericExpression || expr instanceof AggregateTemporalExpression)
{
aggregated = true;
}
int[] selected = new int[expr.getNumberOfSubExpressions()];
if (expr.getNumberOfSubExpressions() > 1)
{
for (int i=0;i<expr.getNumberOfSubExpressions();i++)
{
String exprStr = expr.getSubExpression(i).toSQLText().toSQL();
if (alias != null)
{
exprStr += " AS " + alias + i;
}
selected[i] = selectItem(exprStr);
}
}
else
{
String exprStr = expr.toSQLText().toSQL();
if (alias != null)
{
exprStr += " AS " + alias;
}
selected[0] = selectItem(exprStr);
}
if (unions != null)
{
// Apply the select to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.select(expr, alias);
}
}
return selected;
}
/**
* Add a select clause for the specified field (via its mapping).
* If an alias is supplied and there are more than 1 column for this mapping then they will have
* names like "{alias}_n" where n is the column number (starting at 0).
* @param table The SQLTable to select from (null implies the primary table)
* @param mapping The mapping for the field
* @param alias optional alias
* @param applyToUnions Whether to apply to unions
* @return The column index(es) in the statement for the specified field (1 is first).
*/
public int[] select(SQLTable table, JavaTypeMapping mapping, String alias, boolean applyToUnions)
{
if (mapping == null)
{
throw new NucleusException("Mapping to select is null");
}
else if (table == null)
{
// Default to the primary table if not specified
table = primaryTable;
}
if (mapping.getDatastoreContainer() != table.getTable())
{
throw new NucleusException("Table being selected from (\"" + table.getTable() +
"\") is inconsistent with the column selected (\"" + mapping.getDatastoreContainer() + "\")");
}
invalidateStatement();
DatastoreMapping[] mappings = mapping.getDatastoreMappings();
int[] selected = new int[mappings.length];
for (int i=0;i<selected.length;i++)
{
DatastoreIdentifier colAlias = null;
if (alias != null)
{
String name = alias;
if (selected.length > 1)
{
name = alias + "_" + i;
}
colAlias = rdbmsMgr.getIdentifierFactory().newDatastoreFieldIdentifier(name);
}
SQLColumn col = new SQLColumn(table, mappings[i].getDatastoreField(), colAlias);
int position = selectItem(col.toString());
selected[i] = position;
}
if (applyToUnions && unions != null)
{
// Apply the select to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.select(table, mapping, alias);
}
}
return selected;
}
/**
* Add a select clause for the specified field (via its mapping) and apply to unions.
* If an alias is supplied and there are more than 1 column for this mapping then they will have
* names like "{alias}_n" where n is the column number (starting at 0).
* @param table The SQLTable to select from (null implies the primary table)
* @param mapping The mapping for the field
* @param alias optional alias
* @return The column index(es) in the statement for the specified field (1 is first).
*/
public int[] select(SQLTable table, JavaTypeMapping mapping, String alias)
{
return select(table, mapping, alias, true);
}
/**
* Add a select clause for the specified column.
* @param table The SQLTable to select from (null implies the primary table)
* @param column The column
* @param alias Optional alias
* @return The column index in the statement for the specified column (1 is first).
*/
public int select(SQLTable table, DatastoreField column, String alias)
{
if (column == null)
{
throw new NucleusException("Column to select is null");
}
else if (table == null)
{
// Default to the primary table if not specified
table = primaryTable;
}
if (column.getDatastoreContainerObject() != table.getTable())
{
throw new NucleusException("Table being selected from (\"" + table.getTable() +
"\") is inconsistent with the column selected (\"" + column.getDatastoreContainerObject() + "\")");
}
invalidateStatement();
DatastoreIdentifier colAlias = null;
if (alias != null)
{
colAlias = rdbmsMgr.getIdentifierFactory().newDatastoreFieldIdentifier(alias);
}
SQLColumn col = new SQLColumn(table, column, colAlias);
int position = selectItem(col.toString());
if (unions != null)
{
// Apply the select to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.select(table, column, alias);
}
}
return position;
}
/**
* Internal method to find the position of an item in the select list and return the position
* if found (first position is 1). If the item is not found then it is added and the new position
* returned.
* @param item The item
* @return Position in the select list (first position is 1)
*/
private int selectItem(String item)
{
if (selects.contains(item))
{
// Already have a select item with this exact name so just return with that
return selects.indexOf(item) + 1;
}
int numberSelected = selects.size();
for (int i=0;i<numberSelected;i++)
{
String selectedItem = selects.get(i);
if (selectedItem.startsWith(item + " "))
{
// We already have the same column but with an alias
return (i+1);
}
else if (item.startsWith(selectedItem + " "))
{
// We are trying to add an aliased form of something that already exists
// so swap what is there already for our aliased variant
selects.set(i, item);
return (i+1);
}
}
// The item doesn't exist so add it and return its new position
selects.add(item);
return selects.indexOf(item) + 1;
}
// --------------------------------- UPDATE --------------------------------------
/**
* Method to set the UPDATE clause of the statement.
* @param exprs The update clause expression
*/
public void setUpdates(SQLExpression[] exprs)
{
invalidateStatement();
updates = exprs;
}
// --------------------------------- FROM --------------------------------------
/**
* Accessor for the primary table of the statement.
* @return The primary table
*/
public SQLTable getPrimaryTable()
{
return primaryTable;
}
/**
* Accessor for the SQLTable object with the specified alias (if defined for this statement).
* @param alias Alias
* @return The SQLTable
*/
public SQLTable getTable(String alias)
{
if (alias.equals(primaryTable.alias.getIdentifierName()))
{
return primaryTable;
}
else if (tables != null)
{
return tables.get(alias);
}
return null;
}
/**
* Accessor for the SQLTable object for the specified table (if defined for this statement)
* in the specified table group.
* @param table The table
* @param groupName Name of the table group where we should look for this table
* @return The SQLTable (if found)
*/
public SQLTable getTable(DatastoreContainerObject table, String groupName)
{
if (groupName == null)
{
return null;
}
SQLTableGroup tableGrp = tableGroups.get(groupName);
if (tableGrp == null)
{
return null;
}
SQLTable[] tables = tableGrp.getTables();
for (int i=0;i<tables.length;i++)
{
if (tables[i].getTable() == table)
{
return tables[i];
}
}
return null;
}
/**
* Accessor for the table group with this name.
* @param groupName Name of the group
* @return The table group
*/
public SQLTableGroup getTableGroup(String groupName)
{
return tableGroups.get(groupName);
}
/**
* Accessor for the number of table groups.
* @return Number of table groups (including that of the candidate)
*/
public int getNumberOfTableGroups()
{
return tableGroups.size();
}
/**
* Accessor for the number of tables defined for this statement.
* @return Number of tables (in addition to the primary table)
*/
public int getNumberOfTables()
{
return tables != null ? tables.size() : -1;
}
/**
* Method to form an inner join to the specified table using the provided mappings.
* Will be applied to all unioned statements.
* @param sourceTable SQLTable for the source (null implies primaryTable)
* @param sourceMapping Mapping in this table to join from
* @param target Table to join to
* @param targetAlias Alias for the target table (if known)
* @param targetMapping Mapping in the other table to join to (also defines the table to join to)
* @param discrimValues Any discriminator values to apply for the joined table (null if not)
* @param tableGrpName Name of the table group for the target (null implies a new group)
* @return SQLTable for the target
*/
public SQLTable innerJoin(SQLTable sourceTable, JavaTypeMapping sourceMapping,
DatastoreContainerObject target, String targetAlias, JavaTypeMapping targetMapping,
Object[] discrimValues, String tableGrpName)
{
return innerJoin(sourceTable, sourceMapping, null, target, targetAlias, targetMapping, null,
discrimValues, tableGrpName);
}
/**
* Method to form an inner join to the specified table using the provided mappings.
* Will be applied to all unioned statements.
* @param sourceTable SQLTable for the source (null implies primaryTable)
* @param sourceMapping Mapping in this table to join from
* @param sourceParentMapping Optional, if this source mapping is a sub mapping (e.g interface impl).
* @param target Table to join to
* @param targetAlias Alias for the target table (if known)
* @param targetMapping Mapping in the other table to join to (also defines the table to join to)
* @param targetParentMapping Optional, if this source mapping is a sub mapping (e.g interface impl).
* @param discrimValues Any discriminator values to apply for the joined table (null if not)
* @param tableGrpName Name of the table group for the target (null implies a new group)
* @return SQLTable for the target
*/
public SQLTable innerJoin(SQLTable sourceTable, JavaTypeMapping sourceMapping, JavaTypeMapping sourceParentMapping,
DatastoreContainerObject target, String targetAlias, JavaTypeMapping targetMapping, JavaTypeMapping targetParentMapping,
Object[] discrimValues, String tableGrpName)
{
invalidateStatement();
// Create the SQLTable to join to.
if (tables == null)
{
tables = new HashMap();
}
if (tableGrpName == null)
{
tableGrpName = "Group" + tableGroups.size();
}
if (targetAlias == null)
{
targetAlias = generateTableAlias(target, tableGrpName);
}
if (sourceTable == null)
{
sourceTable = primaryTable;
}
DatastoreIdentifier targetId = rdbmsMgr.getIdentifierFactory().newDatastoreContainerIdentifier(targetAlias);
SQLTable targetTbl = new SQLTable(this, target, targetId, tableGrpName);
putSQLTableInGroup(targetTbl, tableGrpName, JoinType.INNER_JOIN);
join(JoinType.INNER_JOIN, sourceTable, sourceMapping, sourceParentMapping,
targetTbl, targetMapping, targetParentMapping, discrimValues);
if (unions != null)
{
// Apply the join to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.innerJoin(sourceTable, sourceMapping, sourceParentMapping,
target, targetAlias, targetMapping, targetParentMapping,
discrimValues, tableGrpName);
}
}
return targetTbl;
}
/**
* Method to form a left outer join to the specified table using the provided mappings.
* Will be applied to all unioned statements.
* @param sourceTable SQLTable for the source (null implies primaryTable)
* @param sourceMapping Mapping in this table to join from
* @param target Table to join to
* @param targetAlias Alias for the target table (if known)
* @param targetMapping Mapping in the other table to join to (also defines the table to join to)
* @param discrimValues Any discriminator values to apply for the joined table (null if not)
* @param tableGrpName Name of the table group for the target (null implies a new group)
* @return SQLTable for the target
*/
public SQLTable leftOuterJoin(SQLTable sourceTable, JavaTypeMapping sourceMapping,
DatastoreContainerObject target, String targetAlias, JavaTypeMapping targetMapping,
Object[] discrimValues, String tableGrpName)
{
return leftOuterJoin(sourceTable, sourceMapping, null, target, targetAlias, targetMapping, null,
discrimValues, tableGrpName);
}
/**
* Method to form a left outer join to the specified table using the provided mappings.
* Will be applied to all unioned statements.
* @param sourceTable SQLTable for the source (null implies primaryTable)
* @param sourceMapping Mapping in this table to join from
* @param sourceParentMapping Optional, if this source mapping is a sub mapping (e.g interface impl).
* @param target Table to join to
* @param targetAlias Alias for the target table (if known)
* @param targetMapping Mapping in the other table to join to (also defines the table to join to)
* @param targetParentMapping Optional, if this source mapping is a sub mapping (e.g interface impl).
* @param discrimValues Any discriminator values to apply for the joined table (null if not)
* @param tableGrpName Name of the table group for the target (null implies a new group)
* @return SQLTable for the target
*/
public SQLTable leftOuterJoin(SQLTable sourceTable, JavaTypeMapping sourceMapping, JavaTypeMapping sourceParentMapping,
DatastoreContainerObject target, String targetAlias, JavaTypeMapping targetMapping, JavaTypeMapping targetParentMapping,
Object[] discrimValues, String tableGrpName)
{
invalidateStatement();
// Create the SQLTable to join to.
if (tables == null)
{
tables = new HashMap();
}
if (tableGrpName == null)
{
tableGrpName = "Group" + tableGroups.size();
}
if (targetAlias == null)
{
targetAlias = generateTableAlias(target, tableGrpName);
}
if (sourceTable == null)
{
sourceTable = primaryTable;
}
DatastoreIdentifier targetId = rdbmsMgr.getIdentifierFactory().newDatastoreContainerIdentifier(targetAlias);
SQLTable targetTbl = new SQLTable(this, target, targetId, tableGrpName);
putSQLTableInGroup(targetTbl, tableGrpName, JoinType.LEFT_OUTER_JOIN);
join(SQLJoin.JoinType.LEFT_OUTER_JOIN, sourceTable, sourceMapping, sourceParentMapping,
targetTbl, targetMapping, targetParentMapping, discrimValues);
if (unions != null)
{
// Apply the join to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.leftOuterJoin(sourceTable, sourceMapping, sourceParentMapping,
target, targetAlias, targetMapping, targetParentMapping,
discrimValues, tableGrpName);
}
}
return targetTbl;
}
/**
* Method to form a right outer join to the specified table using the provided mappings.
* Will be applied to all unioned statements.
* @param sourceTable SQLTable for the source (null implies primaryTable)
* @param sourceMapping Mapping in this table to join from
* @param target Table to join to
* @param targetAlias Alias for the target table (if known)
* @param targetMapping Mapping in the other table to join to (also defines the table to join to)
* @param discrimValues Any discriminator values to apply for the joined table (null if not)
* @param tableGrpName Name of the table group for the target (null implies a new group)
* @return SQLTable for the target
*/
public SQLTable rightOuterJoin(SQLTable sourceTable, JavaTypeMapping sourceMapping,
DatastoreContainerObject target, String targetAlias, JavaTypeMapping targetMapping,
Object[] discrimValues, String tableGrpName)
{
return rightOuterJoin(sourceTable, sourceMapping, null, target, targetAlias, targetMapping, null,
discrimValues, tableGrpName);
}
/**
* Method to form a right outer join to the specified table using the provided mappings.
* Will be applied to all unioned statements.
* @param sourceTable SQLTable for the source (null implies primaryTable)
* @param sourceMapping Mapping in this table to join from
* @param target Table to join to
* @param targetAlias Alias for the target table (if known)
* @param targetMapping Mapping in the other table to join to (also defines the table to join to)
* @param discrimValues Any discriminator values to apply for the joined table (null if not)
* @param tableGrpName Name of the table group for the target (null implies a new group)
* @return SQLTable for the target
*/
public SQLTable rightOuterJoin(SQLTable sourceTable, JavaTypeMapping sourceMapping, JavaTypeMapping sourceParentMapping,
DatastoreContainerObject target, String targetAlias, JavaTypeMapping targetMapping, JavaTypeMapping targetParentMapping,
Object[] discrimValues, String tableGrpName)
{
invalidateStatement();
// Create the SQLTable to join to.
if (tables == null)
{
tables = new HashMap();
}
if (tableGrpName == null)
{
tableGrpName = "Group" + tableGroups.size();
}
if (targetAlias == null)
{
targetAlias = generateTableAlias(target, tableGrpName);
}
if (sourceTable == null)
{
sourceTable = primaryTable;
}
DatastoreIdentifier targetId = rdbmsMgr.getIdentifierFactory().newDatastoreContainerIdentifier(targetAlias);
SQLTable targetTbl = new SQLTable(this, target, targetId, tableGrpName);
putSQLTableInGroup(targetTbl, tableGrpName, JoinType.RIGHT_OUTER_JOIN);
join(JoinType.RIGHT_OUTER_JOIN, sourceTable, sourceMapping, sourceParentMapping,
targetTbl, targetMapping, targetParentMapping, discrimValues);
if (unions != null)
{
// Apply the join to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.rightOuterJoin(sourceTable, sourceMapping, sourceParentMapping,
target, targetAlias, targetMapping, targetParentMapping,
discrimValues, tableGrpName);
}
}
return targetTbl;
}
/**
* Method to form a right outer join to the specified table using the provided mappings.
* Will be applied to all unioned statements.
* @param target Table to join to
* @param targetAlias Alias for the target table (if known)
* @param tableGrpName Name of the table group for the target (null implies a new group)
* @return SQLTable for the target
*/
public SQLTable crossJoin(DatastoreContainerObject target, String targetAlias, String tableGrpName)
{
invalidateStatement();
// Create the SQLTable to join to.
if (tables == null)
{
tables = new HashMap();
}
if (tableGrpName == null)
{
tableGrpName = "Group" + tableGroups.size();
}
if (targetAlias == null)
{
targetAlias = generateTableAlias(target, tableGrpName);
}
DatastoreIdentifier targetId = rdbmsMgr.getIdentifierFactory().newDatastoreContainerIdentifier(targetAlias);
SQLTable targetTbl = new SQLTable(this, target, targetId, tableGrpName);
putSQLTableInGroup(targetTbl, tableGrpName, JoinType.CROSS_JOIN);
join(JoinType.CROSS_JOIN, primaryTable, null, null, targetTbl, null, null, null);
if (unions != null)
{
// Apply the join to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.crossJoin(target, targetAlias, tableGrpName);
}
}
return targetTbl;
}
/**
* Accessor for the type of join used for the specified table.
* @param sqlTbl The table to check
* @return The join type, or null if not joined in this statement
*/
public JoinType getJoinTypeForTable(SQLTable sqlTbl)
{
if (joins == null)
{
return null;
}
Iterator<SQLJoin> joinIter = joins.iterator();
while (joinIter.hasNext())
{
SQLJoin join = joinIter.next();
if (join.getTable().equals(sqlTbl))
{
return join.getType();
}
}
return null;
}
/**
* Method to remove a cross join for the specified table (if joined via cross join).
* Also removes the table from the list of tables.
* This is called where we have bound a variable via a CROSS JOIN (in the absence of better information)
* and found out later it could become an INNER JOIN.
* If the supplied table is not joined via a cross join then does nothing.
* @param targetSqlTbl The table to drop the cross join for
*/
public String removeCrossJoin(SQLTable targetSqlTbl)
{
if (joins == null)
{
return null;
}
Iterator<SQLJoin> joinIter = joins.iterator();
while (joinIter.hasNext())
{
SQLJoin join = joinIter.next();
if (join.getTable().equals(targetSqlTbl) && join.getType() == JoinType.CROSS_JOIN)
{
joinIter.remove();
requiresJoinReorder = true;
tables.remove(join.getTable().alias.getIdentifierName());
String removedAliasName = join.getTable().alias.getIdentifierName();
if (unions != null)
{
// Apply the join removal to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.removeCrossJoin(targetSqlTbl);
}
}
return removedAliasName;
}
}
return null;
}
/**
* Convenience method to add the SQLTable to the specified group.
* If the group doesn't yet exist then it adds it.
* @param sqlTbl SQLTable to add
* @param groupName The group
* @param joinType type of join to start this table group
*/
private void putSQLTableInGroup(SQLTable sqlTbl, String groupName, JoinType joinType)
{
SQLTableGroup tableGrp = tableGroups.get(groupName);
if (tableGrp == null)
{
tableGrp = new SQLTableGroup(groupName, joinType);
}
tableGrp.addTable(sqlTbl);
tableGroups.put(groupName, tableGrp);
}
/**
* Internal method to form a join to the specified table using the provided mappings.
* @param joinType Type of join (INNER, LEFT OUTER, RIGHT OUTER, CROSS, NON-ANSI)
* @param sourceTable SQLTable to join from
* @param sourceMapping Mapping in this table to join from
* @param sourceParentMapping Optional parent of this source mapping (when joining an impl of an interface)
* @param targetTable SQLTable to join to
* @param targetMapping Mapping in the other table to join to (also defines the table to join to)
* @param targetParentMapping Optional parent of this target mapping (when joining an impl of an interface)
* @param discrimValues Any discriminator values to apply for the joined table (null if not)
*/
protected void join(SQLJoin.JoinType joinType,
SQLTable sourceTable, JavaTypeMapping sourceMapping, JavaTypeMapping sourceParentMapping,
SQLTable targetTable, JavaTypeMapping targetMapping, JavaTypeMapping targetParentMapping,
Object[] discrimValues)
{
if (tables == null)
{
throw new NucleusException("tables not set in statement!");
}
if (tables.containsValue(targetTable))
{
// Already have a join to this table
// What if we have a cross join, and want to change to inner join?
NucleusLogger.DATASTORE.debug("Attempt to join to " + targetTable + " but join already exists");
return;
}
// Add the table to the referenced tables for this statement
tables.put(targetTable.alias.getIdentifierName(), targetTable);
// Generate the join condition to use
BooleanExpression joinCondition = getJoinConditionForJoin(sourceTable, sourceMapping, sourceParentMapping,
targetTable, targetMapping, targetParentMapping, discrimValues);
if (rdbmsMgr.getDatastoreAdapter().supportsOption(RDBMSAdapter.ANSI_JOIN_SYNTAX))
{
// "ANSI-92" style join
SQLJoin join = new SQLJoin(joinType, targetTable, sourceTable, joinCondition);
if (joins == null)
{
joins = new ArrayList<SQLJoin>();
}
joins.add(join);
}
else
{
// "ANSI-86" style join
SQLJoin join = new SQLJoin(JoinType.NON_ANSI_JOIN, targetTable, sourceTable, null);
if (joins == null)
{
joins = new ArrayList<SQLJoin>();
}
joins.add(join);
// Specify joinCondition in the WHERE clause since not allowed in FROM clause with ANSI-86
// TODO Cater for Oracle LEFT OUTER syntax "(+)"
whereAnd(joinCondition, false);
}
}
/**
* Convenience method to generate the join condition between source and target tables for the supplied
* mappings.
* @param sourceTable Source table
* @param sourceMapping Mapping in source table
* @param sourceParentMapping Optional parent of this source mapping (if joining an impl of an interface)
* @param targetTable Target table
* @param targetMapping Mapping in target table
* @param targetParentMapping Optional parent of this target mapping (if joining an impl of an interface)
* @param discrimValues Optional discriminator values to further restrict
* @return The join condition
*/
protected BooleanExpression getJoinConditionForJoin(
SQLTable sourceTable, JavaTypeMapping sourceMapping, JavaTypeMapping sourceParentMapping,
SQLTable targetTable, JavaTypeMapping targetMapping, JavaTypeMapping targetParentMapping,
Object[] discrimValues)
{
BooleanExpression joinCondition = null;
if (sourceMapping != null && targetMapping != null)
{
// Join condition(s) - INNER, LEFT OUTER, RIGHT OUTER joins
if (sourceMapping.getNumberOfDatastoreMappings() != targetMapping.getNumberOfDatastoreMappings())
{
throw new NucleusException("Cannot join from " + sourceMapping + " to " + targetMapping +
" since they have different numbers of datastore columns!");
}
SQLExpressionFactory factory = rdbmsMgr.getSQLExpressionFactory();
// Set joinCondition to be "source = target"
SQLExpression sourceExpr = null;
if (sourceParentMapping == null)
{
sourceExpr = factory.newExpression(this,
sourceTable != null ? sourceTable : primaryTable, sourceMapping);
}
else
{
sourceExpr = factory.newExpression(this,
sourceTable != null ? sourceTable : primaryTable, sourceMapping, sourceParentMapping);
}
SQLExpression targetExpr = null;
if (targetParentMapping == null)
{
targetExpr = factory.newExpression(this, targetTable, targetMapping);
}
else
{
targetExpr = factory.newExpression(this, targetTable, targetMapping, targetParentMapping);
}
joinCondition = sourceExpr.eq(targetExpr);
// Process discriminator for any additional conditions
JavaTypeMapping discrimMapping = targetTable.getTable().getDiscriminatorMapping(false);
if (discrimMapping != null && discrimValues != null)
{
SQLExpression discrimExpr = factory.newExpression(this, targetTable, discrimMapping);
BooleanExpression discrimCondition = null;
for (int i=0;i<discrimValues.length;i++)
{
SQLExpression discrimVal = factory.newLiteral(this, discrimMapping, discrimValues[i]);
BooleanExpression condition = discrimExpr.eq(discrimVal);
if (discrimCondition == null)
{
discrimCondition = condition;
}
else
{
discrimCondition = discrimCondition.ior(condition);
}
}
discrimCondition.encloseInParentheses();
joinCondition = joinCondition.and(discrimCondition);
}
}
return joinCondition;
}
/**
* Method to generate the alias to be used for a joined table.
* Names tables according to the extension "datanucleus.sqlTableNamingStrategy".
* @param tbl Table object
* @param groupName Name of the table group
* @return The alias to use
*/
protected synchronized String generateTableAlias(DatastoreContainerObject tbl, String groupName)
{
String namingSchema = null;
if (extensions != null)
{
namingSchema = (String)extensions.get("datanucleus.sqlTableNamingStrategy");
}
if (namingSchema == null)
{
// Fall-back to "alpha-scheme" : Tables called A0, A1, A2, B0, ... etc
namingSchema = "alpha-scheme";
}
SQLTableNamer namer = tableNamerByName.get(namingSchema);
if (namer == null)
{
// Instantiate the namer of this schema name (if available)
try
{
namer = (SQLTableNamer)rdbmsMgr.getNucleusContext().getPluginManager().createExecutableExtension(
"org.datanucleus.store.rdbms.sql_tablenamer", "name", namingSchema, "class",
null, null);
}
catch (Exception e)
{
throw new NucleusException("Attempt to find/instantiate SQL table namer " + namingSchema +
" threw an exception", e);
}
tableNamerByName.put(namingSchema, namer);
}
return namer.getAliasForTable(this, tbl, groupName);
}
// --------------------------------- WHERE --------------------------------------
/**
* Method to add an AND condition to the WHERE clause.
* @param expr The condition
*/
public void whereAnd(BooleanExpression expr, boolean applyToUnions)
{
invalidateStatement();
if (expr instanceof BooleanLiteral && !expr.isParameter() && (Boolean)((BooleanLiteral)expr).getValue())
{
// Where condition is "TRUE" so omit
return;
}
if (where == null)
{
where = expr;
}
else
{
where = where.and(expr);
}
if (unions != null && applyToUnions)
{
// Apply the where to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.whereAnd(expr, true);
}
}
}
/**
* Method to add an OR condition to the WHERE clause.
* @param expr The condition
* @param applyToUnions Whether to apply to unions
*/
public void whereOr(BooleanExpression expr, boolean applyToUnions)
{
invalidateStatement();
if (where == null)
{
where = expr;
}
else
{
where = where.ior(expr);
}
if (unions != null && applyToUnions)
{
// Apply the where to all unions
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
SQLStatement stmt = unionIter.next();
stmt.whereOr(expr, true);
}
}
}
// --------------------------------- GROUPING --------------------------------------
/**
* Method to add a grouping expression to the query.
* Adds the grouping to any unioned queries
* @param expr The expression
*/
public void addGroupingExpression(SQLExpression expr)
{
invalidateStatement();
if (groupingExpressions == null)
{
groupingExpressions = new ArrayList();
}
groupingExpressions.add(expr);
aggregated = true;
if (unions != null)
{
// Apply the grouping to all unions
Iterator<SQLStatement> i = unions.iterator();
while (i.hasNext())
{
i.next().addGroupingExpression(expr);
}
}
}
// --------------------------------- HAVING --------------------------------------
/**
* Mutator for the "having" expression.
* @param expr Boolean expression for the having clause
*/
public void setHaving(BooleanExpression expr)
{
invalidateStatement();
having = expr;
aggregated = true;
}
// --------------------------------- ORDERING --------------------------------------
/**
* Mutator for the ordering criteria.
* @param exprs The expressions to order by
* @param descending Whether each expression is ascending/descending
*/
public void setOrdering(SQLExpression[] exprs, boolean[] descending)
{
if (exprs.length != descending.length)
{
throw new NucleusException(LOCALISER.msg("052503", "" + exprs.length, "" + descending.length)).setFatal();
}
invalidateStatement();
orderingExpressions = exprs;
orderingDirections = descending;
}
// --------------------------------- RANGE --------------------------------------
/**
* Method to add a range constraint on any SELECT.
* This typically will use LIMIT/OFFSET where they are supported by the underlying RDBMS.
* @param offset The offset to start from
* @param count The number of records to return
*/
public void setRange(long offset, long count)
{
invalidateStatement();
this.rangeOffset = offset;
this.rangeCount = count;
}
// --------------------------------- STATEMENT ----------------------------------
/**
* Accessor for the SQL SELECT statement.
* If any mutator method has been called since this was last called the SQL will be regenerated
* otherwise the SQL is cached.
* @return The SQL statement
*/
public synchronized SQLText getSelectStatement()
{
if (sql != null)
{
return sql;
}
DatabaseAdapter dba = getDatabaseAdapter();
boolean lock = false;
Boolean val = (Boolean)getValueForExtension("lock-for-update");
if (val != null)
{
lock = val.booleanValue();
}
// SELECT ..., ..., ...
sql = new SQLText("SELECT ");
if (distinct)
{
sql.append("DISTINCT ");
}
addOrderingColumnsToSelect();
Iterator<String> selectIter = selects.iterator();
while (selectIter.hasNext())
{
String selected = selectIter.next();
sql.append(selected);
if (selectIter.hasNext())
{
sql.append(',');
}
}
if ((rangeOffset > -1 || rangeCount > -1) && dba.getRangeByRowNumberColumn().length() > 0)
{
// Add a ROW NUMBER column if supported as the means of handling ranges by the RDBMS
sql.append(',').append(dba.getRangeByRowNumberColumn()).append(" rn");
}
// FROM ...
sql.append(" FROM ");
sql.append(primaryTable.toString());
if (joins != null)
{
if (requiresJoinReorder)
{
List<SQLJoin> theJoins = new ArrayList<SQLJoin>(joins.size());
reorderJoins(theJoins, joins);
joins = theJoins;
}
Iterator<SQLJoin> iter = joins.iterator();
while (iter.hasNext())
{
SQLJoin join = iter.next();
if (join.getType() == JoinType.CROSS_JOIN)
{
if (dba.supportsOption(RDBMSAdapter.ANSI_CROSSJOIN_SYNTAX))
{
// ANSI-92 style joins, separate joins by space
sql.append(" ");
sql.append(join.toFromClause(dba, lock));
}
else if (dba.supportsOption(RDBMSAdapter.CROSSJOIN_ASINNER11_SYNTAX))
{
sql.append(" INNER JOIN " + join.getTable() + " ON 1=1");
}
else
{
// "ANSI-86" style cross join, separate join by comma
sql.append(",");
sql.append(join.getTable().toString());
}
}
else
{
if (dba.supportsOption(RDBMSAdapter.ANSI_JOIN_SYNTAX))
{
// ANSI-92 style joins, separate joins by space
sql.append(" ");
sql.append(join.toFromClause(dba, lock));
}
else
{
// "ANSI-86" style joins, separate joins by comma
sql.append(",");
sql.append(join.toFromClause(dba, lock));
}
}
}
}
// WHERE ...
if (where != null)
{
sql.append(" WHERE ").append(where.toSQLText());
}
// GROUP BY ...
if (groupingExpressions != null)
{
List groupBy = new ArrayList();
Iterator<SQLExpression> groupIter = groupingExpressions.iterator();
while (groupIter.hasNext())
{
SQLExpression expr = groupIter.next();
String exprText = expr.toSQLText().toSQL();
if (!groupBy.contains(exprText))
{
groupBy.add(exprText);
}
}
if (groupBy.size() > 0 && aggregated)
{
sql.append(" GROUP BY ");
for (int i=0; i<groupBy.size(); i++)
{
if (i > 0)
{
sql.append(',');
}
sql.append((String)groupBy.get(i));
}
}
}
// HAVING ...
if (having != null)
{
sql.append(" HAVING ").append(having.toSQLText());
}
if (unions != null)
{
// Add on any UNIONed statements
if (!dba.supportsOption(RDBMSAdapter.UNION_SYNTAX))
{
throw new NucleusException(LOCALISER.msg("052504", "UNION")).setFatal();
}
Iterator<SQLStatement> unionIter = unions.iterator();
while (unionIter.hasNext())
{
if (dba.supportsOption(RDBMSAdapter.USE_UNION_ALL))
{
sql.append(" UNION ALL ");
}
else
{
sql.append(" UNION ");
}
SQLStatement stmt = unionIter.next();
SQLText unionSql = stmt.getSelectStatement();
sql.append(unionSql);
}
}
// ORDER BY ...
SQLText orderStmt = generateOrderingStatement();
if (orderStmt != null)
{
sql.append(" ORDER BY ").append(orderStmt);
}
// RANGE
if (rangeOffset > -1 || rangeCount > -1)
{
// Add a LIMIT clause to end of statement if supported by the adapter
String limitClause = dba.getRangeByLimitEndOfStatementClause(rangeOffset, rangeCount);
if (limitClause.length() > 0)
{
sql.append(" ").append(limitClause);
}
}
if (lock && dba.supportsOption(RDBMSAdapter.LOCK_WITH_SELECT_FOR_UPDATE))
{
// Add any required locking based on the RDBMS capability
if (distinct && !dba.supportsOption(RDBMSAdapter.DISTINCT_WITH_SELECT_FOR_UPDATE))
{
NucleusLogger.QUERY.warn(LOCALISER.msg("052502"));
}
else
{
sql.append(" " + dba.getSelectForUpdateText());
}
}
if (lock && !dba.supportsOption(RDBMSAdapter.LOCK_WITH_SELECT_FOR_UPDATE) &&
!dba.supportsOption(RDBMSAdapter.LOCK_OPTION_PLACED_AFTER_FROM) &&
!dba.supportsOption(RDBMSAdapter.LOCK_OPTION_PLACED_WITHIN_JOIN))
{
NucleusLogger.QUERY.warn("Requested locking of query statement, but this RDBMS doesn't suppot a convenient mechanism");
}
if (rangeOffset > -1 || rangeCount > -1)
{
if (dba.getRangeByRowNumberColumn().length() > 0)
{
// Wrap the required query inside an outer query for the rownum - see issue CORE-2721
// Must select all columns of the inner query, except for the ROWNUM column
// Add conditions on the row number to meet the required range
SQLText innerQuery = sql;
sql = new SQLText("SELECT ");
selectIter = selects.iterator();
while (selectIter.hasNext())
{
String selectExpr = selectIter.next();
sql.append("subq.");
String selectedCol = selectExpr;
final int dotIndex = selectedCol.indexOf(".");
if (dotIndex != -1)
{
// must remove prepending "THIS.bla" from referenced column bla
// TODO This fails when you have the same column name in two columns in the result
selectedCol = selectedCol.substring(dotIndex + 1);
}
sql.append(selectedCol);
if (selectIter.hasNext())
{
sql.append(',');
}
}
sql.append(" FROM (");
sql.append(innerQuery);
sql.append(") subq WHERE ");
if (rangeOffset > -1)
{
sql.append("subq.rn").append(">=").append("" + rangeOffset);
}
if (rangeCount > -1)
{
if (rangeOffset > -1)
{
sql.append(" AND ");
}
sql.append("subq.rn").append("<").append("" + (rangeCount + rangeOffset));
}
}
}
return sql;
}
/**
* Convenience method to reorder the joins to be in logical order.
* If a join needed to be changed during the generation process, it will have been removed and then
* the replacement added later. This method reorders the joins so that the joins are only relative to
* "known" tables.
*/
private void reorderJoins(List knownJoins, List joinsToAdd)
{
if (joinsToAdd == null)
{
requiresJoinReorder = false;
return;
}
while (joinsToAdd.size() > 0)
{
Iterator<SQLJoin> joinIter = joinsToAdd.iterator();
int origSize = joinsToAdd.size();
while (joinIter.hasNext())
{
SQLJoin join = joinIter.next();
if (join.getType() == JoinType.CROSS_JOIN)
{
// Cross joins don't relate to any other table so are fine
knownJoins.add(join);
joinIter.remove();
}
else if (join.getType() == JoinType.NON_ANSI_JOIN)
{
// Non-ANSI joins use the WHERE clause so are fine
knownJoins.add(join);
joinIter.remove();
}
else if (join.getJoinedTable().equals(primaryTable))
{
// Joins to the primary table are fine
knownJoins.add(join);
joinIter.remove();
}
else
{
Iterator<SQLJoin> knownJoinIter = knownJoins.iterator();
boolean valid = false;
while (knownJoinIter.hasNext())
{
SQLJoin currentJoin = knownJoinIter.next();
if (join.getJoinedTable().equals(currentJoin.getTable()))
{
valid = true;
break;
}
}
if (valid)
{
// Only used known joins so fine
knownJoins.add(join);
joinIter.remove();
}
}
}
if (joinsToAdd.size() == origSize)
{
// Somehow the user has ended up with a circular pattern of joins
throw new NucleusException("Unable to reorder joins for SQL statement since circular!" +
" Consider reordering the components in the WHERE clause :" +
" affected joins - " + StringUtils.collectionToString(joinsToAdd));
}
}
requiresJoinReorder = false;
}
/**
* Accessor for the SQL UPDATE statement.
* If any mutator method has been called since this was last called the SQL will be regenerated
* otherwise the SQL is cached.
* @return The SQL statement for UPDATE
*/
public synchronized SQLText getUpdateStatement()
{
if (sql != null)
{
return sql;
}
DatabaseAdapter dba = getDatabaseAdapter();
sql = new SQLText("UPDATE ");
sql.append(primaryTable.toString());
if (joins != null && dba.supportsOption(RDBMSAdapter.UPDATE_MULTITABLE))
{
if (requiresJoinReorder)
{
List<SQLJoin> theJoins = new ArrayList<SQLJoin>(joins.size());
reorderJoins(theJoins, joins);
joins = theJoins;
}
Iterator<SQLJoin> iter = joins.iterator();
while (iter.hasNext())
{
SQLJoin join = iter.next();
if (join.getType() == JoinType.CROSS_JOIN)
{
if (dba.supportsOption(RDBMSAdapter.ANSI_CROSSJOIN_SYNTAX))
{
// ANSI-92 style joins, separate joins by space
sql.append(" ");
sql.append(join.toFromClause(dba, false));
}
else
{
// "ANSI-86" style cross join, separate join by comma
sql.append(",");
sql.append(join.getTable().toString());
}
}
else
{
if (dba.supportsOption(RDBMSAdapter.ANSI_JOIN_SYNTAX))
{
// ANSI-92 style joins, separate joins by space
sql.append(" ");
sql.append(join.toFromClause(dba, false));
}
else
{
// "ANSI-86" style joins, separate joins by comma
sql.append(",");
sql.append(join.toFromClause(dba, false));
}
}
}
}
sql.append(" SET ");
if (updates != null && updates.length > 0)
{
for (int i=0;i<updates.length;i++)
{
if (i != 0)
{
sql.append(",");
}
sql.append(updates[i].toSQLText());
}
}
if (where != null)
{
sql.append(" WHERE ").append(where.toSQLText());
}
return sql;
}
/**
* Accessor for the SQL DELETE statement. Generates a statement like
* <code>DELETE FROM tbl1 A0 WHERE A0.xxx = yyy</code>
* If any mutator method has been called since this was last called the SQL will be regenerated
* otherwise the SQL is cached.
* @return The SQL statement for DELETE
*/
public synchronized SQLText getDeleteStatement()
{
if (sql != null)
{
return sql;
}
sql = new SQLText(((RDBMSAdapter)rdbmsMgr.getDatastoreAdapter()).getDeleteTableStatement(primaryTable));
if (where != null)
{
sql.append(" WHERE ").append(where.toSQLText());
}
return sql;
}
/** Positions of order columns in the SELECT (for datastores that require ordering using those). */
private int[] orderingColumnIndexes;
/**
* Convenience method to generate the ordering statement to add to the overall query statement.
* @return The ordering statement
*/
protected SQLText generateOrderingStatement()
{
SQLText orderStmt = null;
if (orderingExpressions != null && orderingExpressions.length > 0)
{
RDBMSAdapter dba = getDatabaseAdapter();
if (dba.supportsOption(RDBMSAdapter.ORDERBY_USING_SELECT_COLUMN_INDEX))
{
// Order using the indexes of the ordering columns in the SELECT
orderStmt = new SQLText();
for (int i=0; i<orderingExpressions.length; ++i)
{
if (i > 0)
{
orderStmt.append(',');
}
orderStmt.append(Integer.toString(orderingColumnIndexes[i]));
if (orderingDirections[i])
{
orderStmt.append(" DESC");
}
}
}
else
{
// Order using column aliases "NUCORDER{i}"
orderStmt = new SQLText();
boolean needsSelect = dba.supportsOption(RDBMSAdapter.INCLUDE_ORDERBY_COLS_IN_SELECT);
for (int i=0; i<orderingExpressions.length; ++i)
{
if (i > 0)
{
orderStmt.append(',');
}
if (needsSelect && !aggregated)
{
// Order by the "NUCORDER?" if we need them to be selected and it isn't an aggregate
String orderString = "NUCORDER" + i;
if (orderingExpressions[i].getNumberOfSubExpressions() == 1)
{
orderStmt.append(dba.getOrderString(rdbmsMgr, orderString, orderingExpressions[i]));
}
else
{
JavaTypeMapping m = orderingExpressions[i].getJavaTypeMapping();
DatastoreMapping[] mappings = m.getDatastoreMappings();
for (int j=0;j<mappings.length;j++)
{
String alias = orderString + "_" + j;
orderStmt.append(dba.getOrderString(rdbmsMgr, alias, orderingExpressions[i]));
if (j < mappings.length-1)
{
orderStmt.append(',');
}
}
}
}
else
{
// Order by the "THIS.COLUMN" otherwise
String orderString = orderingExpressions[i].toSQLText().toSQL();
orderStmt.append(dba.getOrderString(rdbmsMgr, orderString, orderingExpressions[i]));
}
if (orderingDirections[i])
{
orderStmt.append(" DESC");
}
}
}
}
return orderStmt;
}
/**
* Convenience method to add any necessary columns to the SELECT that are needed
* by the ordering constraint.
*/
protected void addOrderingColumnsToSelect()
{
// TODO Cater for these columns already being selected but with no alias, so add the alias
// to the already selected column
if (orderingExpressions != null)
{
// Add any ordering columns to the SELECT
RDBMSAdapter dba = getDatabaseAdapter();
if (dba.supportsOption(RDBMSAdapter.ORDERBY_USING_SELECT_COLUMN_INDEX))
{
// Order using the indexes of the ordering columns in the SELECT
orderingColumnIndexes = new int[orderingExpressions.length];
// Add the ordering columns to the selected list, saving the positions
for (int i=0; i<orderingExpressions.length; ++i)
{
selects.add(orderingExpressions[i].toSQLText().toString());
orderingColumnIndexes[i] = selects.size();
if (unions != null)
{
Iterator<SQLStatement> iterator = unions.iterator();
while (iterator.hasNext())
{
iterator.next().selectSQLExpressionInternal(orderingExpressions[i], null);
}
}
}
}
else if (dba.supportsOption(RDBMSAdapter.INCLUDE_ORDERBY_COLS_IN_SELECT))
{
// Order using column aliases "NUCORDER{i}"
for (int i=0; i<orderingExpressions.length; ++i)
{
String orderExpr = "NUCORDER" + i;
if (orderingExpressions[i].getNumberOfSubExpressions() == 1 || aggregated)
{
if (unions != null)
{
Iterator<SQLStatement> iterator = unions.iterator();
while (iterator.hasNext())
{
SQLStatement stmt = iterator.next();
if (aggregated)
{
stmt.selectSQLExpressionInternal(orderingExpressions[i], null);
}
else
{
stmt.selectSQLExpressionInternal(orderingExpressions[i], orderExpr);
}
}
}
if (aggregated)
{
selectSQLExpressionInternal(orderingExpressions[i], null);
}
else
{
selectSQLExpressionInternal(orderingExpressions[i], orderExpr);
}
}
else
{
JavaTypeMapping m = orderingExpressions[i].getJavaTypeMapping();
DatastoreMapping[] mappings = m.getDatastoreMappings();
for (int j=0;j<mappings.length;j++)
{
String alias = orderExpr + "_" + j;
DatastoreIdentifier aliasId =
rdbmsMgr.getIdentifierFactory().newDatastoreFieldIdentifier(alias);
SQLColumn col = new SQLColumn(orderingExpressions[i].getSQLTable(),
mappings[j].getDatastoreField(), aliasId);
String selectedName = col.toString();
selectItem(selectedName);
if (unions != null)
{
Iterator<SQLStatement> iterator = unions.iterator();
while (iterator.hasNext())
{
SQLStatement stmt = iterator.next();
stmt.selectItem(selectedName);
}
}
}
}
}
}
}
}
/**
* Convenience method for selecting columns when generating the SQL text.
* Does the same as selectSQLExpression except doesn't invalidate the SQL.
* @param expr The expression
* @param alias The alias to use
* @return The position of this column
*/
protected int selectSQLExpressionInternal(SQLExpression expr, String alias)
{
String exprStr = expr.toSQLText().toSQL();
if (alias != null)
{
exprStr += " AS " + alias;
}
return selectItem(exprStr);
}
/**
* Method to uncache the generated SQL (because some condition has changed).
*/
protected void invalidateStatement()
{
sql = null;
}
/**
* Method to dump the statement to the supplied log (debug level).
* @param logger The logger
*/
public void log(NucleusLogger logger)
{
// Log the statement (assumed to be SELECT)
logger.debug("SQLStatement : " + getSelectStatement().toSQL());
// Log the table groups
Iterator grpIter = tableGroups.keySet().iterator();
while (grpIter.hasNext())
{
String grpName = (String)grpIter.next();
logger.debug("SQLStatement : TableGroup=" + tableGroups.get(grpName));
}
}
}