/*
// $Id: //open/mondrian/src/main/mondrian/rolap/sql/SqlQuery.java#110 $
// This software is subject to the terms of the Eclipse Public License v1.0
// Agreement, available at the following URL:
// http://www.eclipse.org/legal/epl-v10.html.
// Copyright (C) 2002-2002 Kana Software, Inc.
// Copyright (C) 2002-2009 Julian Hyde and others
// All Rights Reserved.
// You must accept the terms of that agreement to use this software.
//
// jhyde, Mar 21, 2002
*/
package mondrian.rolap.sql;
import mondrian.olap.MondrianDef;
import mondrian.olap.MondrianProperties;
import mondrian.olap.Util;
import mondrian.rolap.RolapUtil;
import mondrian.rolap.RolapStar;
import mondrian.spi.Dialect;
import mondrian.spi.DialectManager;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.*;
/**
* SqlQuery
allows us to build a select
* statement and generate it in database-specific SQL syntax.
*
*
Notable differences in database syntax are:
select * from "emp"
. Access prefers brackets,
* for example select * from [emp]
. mySQL allows single- and
* double-quotes for string literals, and therefore does not allow
* identifiers to be quoted, for example select 'foo', "bar" from
* emp
. select from emp as e
vs. select * from emp
* e
. select empno + 1 from emp
. select * from (emp)
select * from
* (select * from emp) as e
.
* NOTE: Instances of this class are NOT thread safe so the user must make
* sure this is accessed by only one thread at a time.
*
* @author jhyde
* @version $Id: //open/mondrian/src/main/mondrian/rolap/sql/SqlQuery.java#110 $
*/
public class SqlQuery {
/** Controls the formatting of the sql string. */
private final boolean generateFormattedSql;
private boolean distinct;
private final ClauseList select;
private final FromClauseList from;
private final OnClauseList on;
private final ClauseList where;
private final ClauseList groupBy;
private final ClauseList having;
private final ClauseList orderBy;
private final List Returns whether the relation was added to the query.
*
* @param relation Relation to add
* @param alias Alias of relation. If null, uses relation's alias.
* @param failIfExists Whether to fail if relation is already present
* @return true, if relation *was* added to query
*/
public boolean addFrom(
final MondrianDef.RelationOrJoin relation,
final String alias,
final boolean failIfExists)
{
if (relation instanceof MondrianDef.View) {
final MondrianDef.View view = (MondrianDef.View) relation;
final String viewAlias =
(alias == null)
? view.getAlias()
: alias;
final String sqlString = view.getCodeSet().chooseQuery(dialect);
return addFromQuery(sqlString, viewAlias, false);
} else if (relation instanceof MondrianDef.InlineTable) {
final MondrianDef.Relation relation1 =
RolapUtil.convertInlineTableToRelation(
(MondrianDef.InlineTable) relation, dialect);
return addFrom(relation1, alias, failIfExists);
} else if (relation instanceof MondrianDef.Table) {
final MondrianDef.Table table = (MondrianDef.Table) relation;
final String tableAlias =
(alias == null)
? table.getAlias()
: alias;
return addFromTable(
table.schema,
table.name,
tableAlias,
table.getFilter(),
table.getHintMap(),
failIfExists);
} else if (relation instanceof MondrianDef.Join) {
final MondrianDef.Join join = (MondrianDef.Join) relation;
final String leftAlias = join.getLeftAlias();
final String rightAlias = join.getRightAlias();
boolean addLeft = addFrom(join.left, leftAlias, failIfExists);
boolean addRight = addFrom(join.right, rightAlias, failIfExists);
boolean added = addLeft || addRight;
if (added) {
buf.setLength(0);
dialect.quoteIdentifier(buf, leftAlias, join.leftKey);
buf.append(" = ");
dialect.quoteIdentifier(buf, rightAlias, join.rightKey);
switch(dialect.getDatabaseProduct()) {
case HIVE:
addOn(buf.toString());
break;
default:
addWhere(buf.toString());
}
}
return added;
} else {
throw Util.newInternal("bad relation type " + relation);
}
}
/**
* Adds an expression to the select clause, automatically creating a
* column alias.
*/
public String addSelect(final String expression) {
// Some DB2 versions (AS/400) throw an error if a column alias is
// *not* used in a subsequent order by (Group by).
// Derby fails on 'SELECT... HAVING' if column has alias.
switch (dialect.getDatabaseProduct()) {
case DB2_AS400:
case DERBY:
return addSelect(expression, null);
default:
return addSelect(expression, nextColumnAlias());
}
}
/**
* Adds an expression to the SELECT and GROUP BY clauses. Uses the alias in
* the GROUP BY clause, if the dialect requires it.
*
* @param expression Expression
* @return Alias of expression
*/
public String addSelectGroupBy(final String expression) {
final String alias = addSelect(expression);
addGroupBy(expression, alias);
return alias;
}
public int getCurrentSelectListSize()
{
return select.size();
}
public String nextColumnAlias() {
return "c" + select.size();
}
/**
* Adds an expression to the select clause, with a specified column
* alias.
*
* @param expression Expression
* @param alias Column alias (or null for no alias)
* @return Column alias
*/
public String addSelect(final String expression, final String alias) {
buf.setLength(0);
buf.append(expression);
if (alias != null) {
buf.append(" as ");
dialect.quoteIdentifier(alias, buf);
}
select.add(buf.toString());
return alias;
}
/**
* Add expression to on() join condition in from clause
*/
public void addOn(final String expression) {
if(acceptsOn(expression)) {
on.add(expression);
} else {
addWhere(expression);
}
}
public void addWhere(
final String exprLeft,
final String exprMid,
final String exprRight)
{
int len = exprLeft.length() + exprMid.length() + exprRight.length();
StringBuilder buf = new StringBuilder(len);
buf.append(exprLeft);
buf.append(exprMid);
buf.append(exprRight);
addWhere(buf.toString());
}
public void addWhere(RolapStar.Condition joinCondition) {
String left = joinCondition.getLeft().getTableAlias();
String right = joinCondition.getRight().getTableAlias();
if (fromAliases.contains(left) && fromAliases.contains(right)) {
addWhere(
joinCondition.getLeft(this),
" = ",
joinCondition.getRight(this));
}
}
public void addWhere(final String expression)
{
where.add(expression);
}
public void addGroupBy(final String expression)
{
groupBy.add(expression);
}
public void addGroupBy(final String expression, final String alias) {
if (dialect.requiresGroupByAlias()) {
addGroupBy(dialect.quoteIdentifier(alias));
} else {
addGroupBy(expression);
}
}
public void addHaving(final String expression)
{
having.add(expression);
}
/**
* Adds an item to the ORDER BY clause.
*
* @param expr the expr to order by
* @param ascending sort direction
* @param prepend whether to prepend to the current list of items
* @param nullable whether the expression might be null
*/
public void addOrderBy(
String expr,
boolean ascending,
boolean prepend,
boolean nullable)
{
String orderExpr = dialect.generateOrderItem(expr, nullable, ascending);
if (prepend) {
orderBy.add(0, orderExpr);
} else {
orderBy.add(orderExpr);
}
}
public String toString()
{
if (generateFormattedSql) {
StringWriter sw = new StringWriter(256);
PrintWriter pw = new PrintWriter(sw);
print(pw, "");
pw.flush();
return sw.toString();
} else {
buf.setLength(0);
select.toBuffer(
buf,
distinct ? "select distinct " : "select ", ", ");
buf.append(getGroupingFunction(""));
switch(dialect.getDatabaseProduct()) {
case HIVE:
if(on.size() == 0) {
from.toBuffer(buf, " from ", " join ");
} else {
from.toBufferWithOn(buf, " from ", " join ", on);
}
break;
default:
from.toBuffer(buf, " from ", ", ");
}
where.toBuffer(buf, " where ", " and ");
if (hasGroupingSet()) {
StringWriter stringWriter = new StringWriter();
printGroupingSets(new PrintWriter(stringWriter), "");
buf.append(stringWriter.toString());
} else {
groupBy.toBuffer(buf, " group by ", ", ");
}
having.toBuffer(buf, " having ", " and ");
orderBy.toBuffer(buf, " order by ", ", ");
return buf.toString();
}
}
/**
* Prints this SqlQuery to a PrintWriter with each clause on a separate
* line, and with the specified indentation prefix.
*
* @param pw Print writer
* @param prefix Prefix for each line
*/
public void print(PrintWriter pw, String prefix) {
select.print(
pw, generateFormattedSql, prefix,
distinct ? "select distinct " : "select ",
", ");
pw.print(getGroupingFunction(prefix));
from.print(pw, generateFormattedSql, prefix, "from ", ", ");
where.print(
pw, generateFormattedSql, prefix, "where ", " and ");
if (hasGroupingSet()) {
printGroupingSets(pw, prefix);
} else {
groupBy.print(pw, generateFormattedSql, prefix, "group by ", ", ");
}
having.print(pw, generateFormattedSql, prefix, "having ", " and ");
orderBy.print(pw, generateFormattedSql, prefix, "order by ", ", ");
}
private String getGroupingFunction(String prefix) {
if (!hasGroupingSet()) {
return "";
}
StringBuilder buf = new StringBuilder();
for (int i = 0; i < groupingFunction.size(); i++) {
if (generateFormattedSql) {
buf.append(" ").append(prefix);
}
buf.append(", ");
buf.append("grouping(");
buf.append(groupingFunction.get(i));
buf.append(") as ");
dialect.quoteIdentifier("g" + i, buf);
if (generateFormattedSql) {
buf.append(Util.nl);
}
}
return buf.toString();
}
private void printGroupingSets(PrintWriter pw, String prefix) {
pw.print(" group by grouping sets (");
for (int i = 0; i < groupingSet.size(); i++) {
if (i > 0) {
pw.print(",");
}
pw.print("(");
groupingSet.get(i).print(
pw, generateFormattedSql, prefix, "", ",", "", "");
pw.print(")");
}
pw.print(")");
}
private boolean hasGroupingSet() {
return !groupingSet.isEmpty();
}
public Dialect getDialect() {
return dialect;
}
public static SqlQuery newQuery(DataSource dataSource, String err) {
final Dialect dialect =
DialectManager.createDialect(dataSource, null);
return new SqlQuery(dialect);
}
public void addGroupingSet(ListSqlQuery
with the same environment as this
* one. (As per the Gang of Four 'prototype' pattern.)
*/
public SqlQuery cloneEmpty()
{
return new SqlQuery(dialect);
}
public void setDistinct(final boolean distinct) {
this.distinct = distinct;
}
/**
* Chooses whether table optimization hints may be used
* (assuming the dialect supports it).
*
* @param t True to allow hints to be used, false otherwise
*/
public void setAllowHints(boolean t) {
this.allowHints = t;
}
/**
* Adds a subquery to the FROM clause of this Query with a given alias.
* If the query already exists it either, depending on
* failIfExists
, throws an exception or does not add the query
* and returns false.
*
* @param query Subquery
* @param alias (if not null, must not be zero length).
* @param failIfExists if true, throws exception if alias already exists
* @return true if query *was* added
*
* @pre alias != null
*/
public boolean addFromQuery(
final String query,
final String alias,
final boolean failIfExists)
{
assert alias != null;
if (fromAliases.contains(alias)) {
if (failIfExists) {
throw Util.newInternal(
"query already contains alias '" + alias + "'");
} else {
return false;
}
}
buf.setLength(0);
buf.append('(');
buf.append(query);
buf.append(')');
if (alias != null) {
Util.assertTrue(alias.length() > 0);
if (dialect.allowsAs()) {
buf.append(" as ");
} else {
buf.append(' ');
}
dialect.quoteIdentifier(alias, buf);
fromAliases.add(alias);
}
from.add(buf.toString());
return true;
}
/**
* Adds [schema.]table AS alias
to the FROM clause.
*
* @param schema schema name; may be null
* @param name table name
* @param alias table alias, may not be null
* (if not null, must not be zero length).
* @param filter Extra filter condition, or null
* @param hints table optimization hints, if any
* @param failIfExists Whether to throw a RuntimeException if from clause
* already contains this alias
*
* @pre alias != null
* @return true if table was added
*/
boolean addFromTable(
final String schema,
final String name,
final String alias,
final String filter,
final Map hints,
final boolean failIfExists)
{
if (fromAliases.contains(alias)) {
if (failIfExists) {
throw Util.newInternal(
"query already contains alias '" + alias + "'");
} else {
return false;
}
}
buf.setLength(0);
dialect.quoteIdentifier(buf, schema, name);
if (alias != null) {
Util.assertTrue(alias.length() > 0);
if (dialect.allowsAs()) {
buf.append(" as ");
} else {
buf.append(' ');
}
dialect.quoteIdentifier(alias, buf);
fromAliases.add(alias);
}
if (this.allowHints) {
dialect.appendHintsAfterFromClause(buf, hints);
}
from.add(buf.toString());
if (filter != null) {
// append filter condition to where clause
addWhere("(", filter, ")");
}
return true;
}
//only join conditions like A.a = B.b or Upper(A.a) = Upper(B.b)
// will be added to on, or will be added to where.
private boolean acceptsOn(String exp) {
StringTokenizer st = new StringTokenizer(exp, "=");
if(st.countTokens() != 2) return false;
StringTokenizer st1 = new StringTokenizer(st.nextToken(), ".");
if(st1.countTokens() != 2) return false;
st1 = new StringTokenizer(st.nextToken(), ".");
if(st1.countTokens() != 2) return false;
return true;
}
public void addFrom(
final SqlQuery sqlQuery,
final String alias,
final boolean failIfExists)
{
addFromQuery(sqlQuery.toString(), alias, failIfExists);
}
/**
* Adds a relation to a query, adding appropriate join conditions, unless
* it is already present.
*
*