{
// Make sure we have any necessary ordering columns in the SELECT (where required)
addOrderingColumnsToSelect();
final RDBMSAdapter rdbmsAdapter = (RDBMSAdapter)storeMgr.getDatastoreAdapter();
StatementText stmtText = null;
stmtText = new StatementText("SELECT ");
if (rangeOffset > -1 || rangeCount > -1)
{
if (rdbmsAdapter.getRangeByRowNumberColumn().length()>0)
{
// When doing range queries using ROWNUM, we must wrap the original query
// in an outer query that does selection based on an aliased ROWNUM column
// of the inner (original) query (CORE-2721)
//
// see also http://72.14.207.104/search?q=cache:IZZXCUsqdIIJ:www.arrowsent.com/oratip/tip41.htm+oracle+rownum+range&hl=en&ct=clnk&cd=1
// SELECT the ROWNUM column and alias it in what will be the inner query
stmtText.append(rdbmsAdapter.getRangeByRowNumberColumn() + " rn, ");
}
else
{
// Add a LIMIT clause to SELECT if it is supported
stmtText.append(rdbmsAdapter.getRangeByLimitSelectClause(rangeOffset, rangeCount));
}
}
boolean usingDistinct = false;
if (!isExistsSubQuery)
{
if (distinctResults)
{
stmtText.append("DISTINCT ");
usingDistinct = true;
}
Iterator iterator = selected.iterator();
while (iterator.hasNext())
{
Object selectExpr = iterator.next();
stmtText.append(selectExpr.toString());
if (iterator.hasNext())
{
stmtText.append(',');
}
}
if ((rangeOffset > -1 || rangeCount > -1) && rdbmsAdapter.getRangeByRowNumberColumn().length() > 0)
{
// Add a ROW NUMBER column if supported as the means of handling ranges by the RDBMS
stmtText.append(',').append(rdbmsAdapter.getRangeByRowNumberColumn());
}
}
else
{
JavaTypeMapping m = rdbmsAdapter.getMapping(Integer.class, storeMgr);
stmtText.append(m.newLiteral(this, new Integer("1")).toStatementText(ScalarExpression.PROJECTION).toStatementString(ScalarExpression.PROJECTION));
}
/*
* 1. Push joins down
* 2. try to make sure joins refer to previous table in stack
*
* A
* JOIN B = A
* JOIN C = C
* JOIN D = D
*/
//SORT JOINS
List sorted = sortJoins(joins);
final Join[] sortedJoins = (Join[]) sorted.toArray(new Join[sorted.size()]);
// FROM condition(s)
stmtText.append(" FROM ");
List crossJoinss = new ArrayList();
crossJoinss.add(mainTableExpr);
crossJoinss.addAll(crossJoins);
if (crossJoinss.size()==1)
{
stmtText.append(crossJoinss.get(0).toString());
if (lock && rdbmsAdapter.getSelectWithLockOption() != null && rdbmsAdapter.getPlaceWithOptionAfterFromClause())
{
// Add any locking of the rows for datastores that require WITH option to be placed after the FROM clause
stmtText.append(" WITH " + rdbmsAdapter.getSelectWithLockOption());
}
crossJoinss.remove(0);
}
for (int i=0; i<sortedJoins.length; i++)
{
final Join join = sortedJoins[i];
for (int j=crossJoinss.size()-1; j>=0; j--)
{
if ((sortedJoins[i].expr1.getLogicSetExpression().equals(crossJoinss.get(j)) ||
sortedJoins[i].expr2.getLogicSetExpression().equals(crossJoinss.get(j))))
{
if (i > 0)
{
stmtText.append(rdbmsAdapter.cartersianProduct((LogicSetExpression)crossJoinss.get(j)));
}
else
{
stmtText.append(crossJoinss.get(j).toString());
}
if (lock && rdbmsAdapter.getSelectWithLockOption() != null && rdbmsAdapter.getPlaceWithOptionAfterFromClause())
{
// Add any locking of the rows for datastores that require WITH option to be placed after the FROM clause
stmtText.append(" WITH " + rdbmsAdapter.getSelectWithLockOption());
}
crossJoinss.remove(j);
break;
}
}
stmtText.append(stmtJoinsSeparator).append(join.toString(rdbmsAdapter, lock));
}
crossJoinss.remove(null);
for (int i=0; i<crossJoinss.size(); i++)
{
if (sortedJoins.length > 0 || i > 0)
{
stmtText.append(rdbmsAdapter.cartersianProduct((LogicSetExpression)crossJoinss.get(i)));
}
else
{
stmtText.append(crossJoinss.get(i).toString());
}
if (lock && rdbmsAdapter.getSelectWithLockOption() != null && rdbmsAdapter.getPlaceWithOptionAfterFromClause())
{
// Add any locking of the rows for datastores that require WITH option to be placed after the FROM clause
stmtText.append(" WITH " + rdbmsAdapter.getSelectWithLockOption());
}
}
// WHERE condition(s)
if (whereExpr != null)
{
stmtText.append(" WHERE ").append(whereExpr.toStatementText(ScalarExpression.FILTER),ScalarExpression.FILTER);
}
// GROUP BY clause(s)
if (groupingExpressions != null)
{
List groupBy = new ArrayList();
for (int i=0;i<groupingExpressions.size();i++)
{
// Add on the user-specified groupings
String exprText = ((ScalarExpression)groupingExpressions.get(i)).toStatementText(ScalarExpression.PROJECTION).toString();
if (!groupBy.contains(exprText))
{
groupBy.add(exprText);
}
}
if (groupBy.size() > 0 && hasAggregateExpression)
{
stmtText.append(" GROUP BY ");
boolean first = true;
for (int i=0; i<groupBy.size(); i++)
{
if (!first)
{
stmtText.append(',');
}
stmtText.append((String)groupBy.get(i));
first = false;
}
}
}
// HAVING clause
if (havingExpr != null)
{
stmtText.append(" HAVING ").append(havingExpr.toStatementText(ScalarExpression.FILTER),ScalarExpression.FILTER);
}
// UNION to other query statements
Iterator iterator = union.iterator();
while (iterator.hasNext())
{
if (!rdbmsAdapter.supportsUnionSyntax())
{
throw new JPOXException(LOCALISER.msg("052504", "UNION")).setFatal();
}
if (rdbmsAdapter.useUnionAll())
{
stmtText.append(" UNION ALL ");
}
else
{
stmtText.append(" UNION ");
}
QueryStatement qs = ((QueryStatement)iterator.next());
//do not use for update in other union queries. is this correct for all databases?
stmtText.append(qs.toStatementText(false),ScalarExpression.FILTER);
}
// ORDER clause
if (!isExistsSubQuery)
{
if (orderingExpressions != null && orderingExpressions.length > 0)
{
StatementText orderByStmt = generateOrderingStatement();
stmtText.append(" ORDER BY ").append(orderByStmt,ScalarExpression.PROJECTION);
}
}
if (rangeOffset > -1 || rangeCount > -1)
{
// Add a LIMIT clause to WHERE if it is supported
stmtText.append(rdbmsAdapter.getRangeByLimitWhereClause(rangeOffset, rangeCount));
}
// Add any required locking based on the RDBMS capability
if (lock && rdbmsAdapter.supportsLockWithSelectForUpdate())
{
if (usingDistinct && !rdbmsAdapter.supportsDistinctWithSelectForUpdate())
{
JPOXLogger.QUERY.warn(LOCALISER.msg("052502"));
}
else
{
stmtText.append(" FOR UPDATE");
}
}
if ((rangeOffset > -1 || rangeCount > -1) && rdbmsAdapter.getRangeByRowNumberColumn().length() > 0)
{
// range query using ROWNUM (CORE-2721): wrap in outer query,
// which must select all columns of the inner query,
// except for the ROWNUM column (which is not contained in
// selected list)
StatementText innerQuery = stmtText;
stmtText = new StatementText("SELECT ");
iterator = selected.iterator();
while (iterator.hasNext())
{
Object selectExpr = iterator.next();
stmtText.append("subq.");