mondrian.rolap.sql
Class SqlQuery

java.lang.Object
  mondrian.rolap.sql.SqlQuery

public class SqlQuery
extends Object

SqlQuery allows us to build a select statement and generate it in database-specific SQL syntax.

Notable differences in database syntax are:

Identifier quoting

Oracle (and all JDBC-compliant drivers) uses double-quotes, for example 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.

AS in from clause

Oracle doesn't like AS in the from * clause, for example select from emp as e vs. select * from emp e.

Column aliases

Some databases require that every column in the select list has a valid alias. If the expression is an expression containing non-alphanumeric characters, an explicit alias is needed. For example, Oracle will barfs at select empno + 1 from emp.

Parentheses around table names

Oracle doesn't like select * from (emp)

Queries in FROM clause

PostgreSQL and hsqldb don't allow, for example, select * from (select * from emp) as e.

Uniqueness of index names

In PostgreSQL and Oracle, index names must be unique within the database; in Access and hsqldb, they must merely be unique within their table

Datatypes

In Oracle, BIT is CHAR(1), TIMESTAMP is DATE. In PostgreSQL, DOUBLE is DOUBLE PRECISION, BIT is BOOL.

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

Nested Class Summary

(package private) static class	SqlQuery.ClauseList
static class	SqlQuery.CodeSet Collection of alternative code for alternative dialects.
(package private) static class	SqlQuery.FromClauseList

Constructor Summary

SqlQuery(Dialect dialect)
Creates a SqlQuery using a given dialect and inheriting the formatting preferences from MondrianProperties.GenerateFormattedSql property.

SqlQuery(Dialect dialect, boolean formatted)
Base constructor used by all other constructors to create an empty instance.

Method Summary

boolean	addFrom(MondrianDef.RelationOrJoin relation, String alias, boolean failIfExists) Adds a relation to a query, adding appropriate join conditions, unless it is already present.
void	addFrom(SqlQuery sqlQuery, String alias, boolean failIfExists)
boolean	addFromQuery(String query, String alias, boolean failIfExists) Adds a subquery to the FROM clause of this Query with a given alias.
(package private) boolean	addFromTable(String schema, String name, String alias, String filter, Map hints, boolean failIfExists) Adds [schema.]table AS alias to the FROM clause.
void	addGroupBy(String expression)
void	addGroupBy(String expression, String alias)
void	addGroupingFunction(String columnExpr)
void	addGroupingSet(List<String> groupingColumnsExpr)
void	addHaving(String expression)
void	addOrderBy(String expr, boolean ascending, boolean prepend, boolean nullable) Adds an item to the ORDER BY clause.
void	addOrderBy(String expr, boolean ascending, boolean prepend, boolean nullable, boolean collateNullsLast) Adds an item to the ORDER BY clause.
String	addSelect(String expression, SqlStatement.Type type) Adds an expression to the select clause, automatically creating a column alias.
String	addSelect(String expression, SqlStatement.Type type, String alias) Adds an expression to the select clause, with a specified type and column alias.
String	addSelectGroupBy(String expression, SqlStatement.Type type) Adds an expression to the SELECT and GROUP BY clauses.
void	addWhere(RolapStar.Condition joinCondition)
void	addWhere(String expression)
void	addWhere(String exprLeft, String exprMid, String exprRight)
SqlQuery	cloneEmpty() Creates an empty SqlQuery with the same environment as this one.
String	getAlias(String expression)
int	getCurrentSelectListSize()
Dialect	getDialect()
static SqlQuery	newQuery(DataSource dataSource, String err)
String	nextColumnAlias()
void	registerRootRelation(MondrianDef.RelationOrJoin root)
void	setAllowHints(boolean t) Chooses whether table optimization hints may be used (assuming the dialect supports it).
void	setDistinct(boolean distinct)
void	toBuffer(StringBuilder buf, String prefix) Writes this SqlQuery to a StringBuilder with each clause on a separate line, and with the specified indentation prefix.
Pair<String,List<SqlStatement.Type>>	toSqlAndTypes()
String	toString()

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Constructor Detail

SqlQuery

public SqlQuery(Dialect dialect,
                boolean formatted)

Base constructor used by all other constructors to create an empty instance.

Parameters:

dialect - Dialect

formatted - Whether to generate SQL formatted on multiple lines

SqlQuery

public SqlQuery(Dialect dialect)

Creates a SqlQuery using a given dialect and inheriting the formatting preferences from MondrianProperties.GenerateFormattedSql property.

Parameters:

dialect - Dialect

Method Detail

cloneEmpty

public SqlQuery cloneEmpty()

Creates an empty SqlQuery with the same environment as this one. (As per the Gang of Four 'prototype' pattern.)

setDistinct

public void setDistinct(boolean distinct)

setAllowHints

public void setAllowHints(boolean t)

Chooses whether table optimization hints may be used (assuming the dialect supports it).

Parameters:

t - True to allow hints to be used, false otherwise

addFromQuery

public boolean addFromQuery(String query,
                            String alias,
                            boolean failIfExists)

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.

Parameters:

query - Subquery

alias - (if not null, must not be zero length).

failIfExists - if true, throws exception if alias already exists

Returns:

true if query *was* added

Pre-condition:

alias != null

addFromTable

boolean addFromTable(String schema,
                     String name,
                     String alias,
                     String filter,
                     Map hints,
                     boolean failIfExists)

Adds [schema.]table AS alias to the FROM clause.

Parameters:

schema - schema name; may be null

name - table name

alias - table alias, may not be null (if not null, must not be zero length).

filter - Extra filter condition, or null

hints - table optimization hints, if any

failIfExists - Whether to throw a RuntimeException if from clause already contains this alias

Returns:

true if table was added

Pre-condition:

alias != null

addFrom

public void addFrom(SqlQuery sqlQuery,
                    String alias,
                    boolean failIfExists)

addFrom

public boolean addFrom(MondrianDef.RelationOrJoin relation,
                       String alias,
                       boolean failIfExists)

Adds a relation to a query, adding appropriate join conditions, unless it is already present.

Returns whether the relation was added to the query.

Parameters:

relation - Relation to add

alias - Alias of relation. If null, uses relation's alias.

failIfExists - Whether to fail if relation is already present

Returns:

true, if relation *was* added to query

addSelect

public String addSelect(String expression,
                        SqlStatement.Type type)

Adds an expression to the select clause, automatically creating a column alias.

addSelectGroupBy

public String addSelectGroupBy(String expression,
                               SqlStatement.Type type)

Adds an expression to the SELECT and GROUP BY clauses. Uses the alias in the GROUP BY clause, if the dialect requires it.

Parameters:

expression - Expression

Returns:

Alias of expression

getCurrentSelectListSize

public int getCurrentSelectListSize()

nextColumnAlias

public String nextColumnAlias()

addSelect

public String addSelect(String expression,
                        SqlStatement.Type type,
                        String alias)

Adds an expression to the select clause, with a specified type and column alias.

Parameters:

expression - Expression

type - Java type to be used to hold cursor column

alias - Column alias (or null for no alias)

Returns:

Column alias

getAlias

public String getAlias(String expression)

addWhere

public void addWhere(String exprLeft,
                     String exprMid,
                     String exprRight)

addWhere

public void addWhere(RolapStar.Condition joinCondition)

addWhere

public void addWhere(String expression)

addGroupBy

public void addGroupBy(String expression)

addGroupBy

public void addGroupBy(String expression,
                       String alias)

addHaving

public void addHaving(String expression)

addOrderBy

public void addOrderBy(String expr,
                       boolean ascending,
                       boolean prepend,
                       boolean nullable)

Adds an item to the ORDER BY clause.

Parameters:

expr - the expr to order by

ascending - sort direction

prepend - whether to prepend to the current list of items

nullable - whether the expression might be null

addOrderBy

public void addOrderBy(String expr,
                       boolean ascending,
                       boolean prepend,
                       boolean nullable,
                       boolean collateNullsLast)

Adds an item to the ORDER BY clause.

Parameters:

expr - the expr to order by

ascending - sort direction

prepend - whether to prepend to the current list of items

nullable - whether the expression might be null

collateNullsLast - whether null values should appear first or last.

toString

public String toString()

Overrides:

toString in class Object

toBuffer

public void toBuffer(StringBuilder buf,
                     String prefix)

Writes this SqlQuery to a StringBuilder with each clause on a separate line, and with the specified indentation prefix.

Parameters:

buf - String builder

prefix - Prefix for each line

getDialect

public Dialect getDialect()

newQuery

public static SqlQuery newQuery(DataSource dataSource,
                                String err)

addGroupingSet

public void addGroupingSet(List<String> groupingColumnsExpr)

addGroupingFunction

public void addGroupingFunction(String columnExpr)

toSqlAndTypes

public Pair<String,List<SqlStatement.Type>> toSqlAndTypes()

registerRootRelation

public void registerRootRelation(MondrianDef.RelationOrJoin root)