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.
static class	SqlQuery.Datatype Datatype of a column.
static class	SqlQuery.Dialect Description of a SQL dialect.

Constructor Summary

SqlQuery(DatabaseMetaData databaseMetaData)
Creates a SqlQuery

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

SqlQuery(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 table, String alias, String filter, boolean failIfExists) Adds [schema.]table AS alias to the FROM clause.
void	addGroupBy(String expression)
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	addSelect(String expression) Adds an expression to the select clause, automatically creating a column alias.
void	addSelect(String expression, String alias) Adds an expression to the select clause, with a specified column alias.
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.
int	getCurrentSelectListSize()
SqlQuery.Dialect	getDialect()
static SqlQuery	newQuery(Connection jdbcConnection, String err)
static SqlQuery	newQuery(DataSource dataSource, String err)
String	nextColumnAlias()
void	print(PrintWriter pw, String prefix) Prints this SqlQuery to a PrintWriter with each clause on a separate line, and with the specified indentation prefix.
void	setDistinct(boolean distinct)
String	toString()

Methods inherited from class java.lang.Object

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

Constructor Detail

SqlQuery

public SqlQuery(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(SqlQuery.Dialect dialect)

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

Parameters:

dialect - Dialect

SqlQuery

public SqlQuery(DatabaseMetaData databaseMetaData)

Creates a SqlQuery

Parameters:

databaseMetaData - used to determine which dialect of SQL to generate. Must not be held beyond the constructor.

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)

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.

Pre-condition:

alias != null

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

addFromTable

boolean addFromTable(String schema,
                     String table,
                     String alias,
                     String filter,
                     boolean failIfExists)

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

Pre-condition:

alias != null

Parameters:

schema - schema name; may be null

table - table name

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

filter - Extra filter condition, or null

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

Returns:

true if table was added

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 void addSelect(String expression)

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

getCurrentSelectListSize

public int getCurrentSelectListSize()

nextColumnAlias

public String nextColumnAlias()

addSelect

public void addSelect(String expression,
                      String alias)

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

addWhere

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

addWhere

public void addWhere(String expression)

addGroupBy

public void addGroupBy(String expression)

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

toString

public String toString()

Overrides:

toString in class Object

print

public void print(PrintWriter pw,
                  String prefix)

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

Parameters:

pw - Print writer

prefix - Prefix for each line

getDialect

public SqlQuery.Dialect getDialect()

newQuery

public static SqlQuery newQuery(Connection jdbcConnection,
                                String err)

newQuery

public static SqlQuery newQuery(DataSource dataSource,
                                String err)

addGroupingSet

public void addGroupingSet(List<String> groupingColumnsExpr)

addGroupingFunction

public void addGroupingFunction(String columnExpr)