com.reportmill.datasource
Class RMSQLExpressionBuilder

java.lang.Object
  com.reportmill.datasource.RMSQLExpressionBuilder

public class RMSQLExpressionBuilder
extends java.lang.Object

RMSQLExpressionBuilder This class is used to translate filter expressions in ReportMill syntax into sql fetches.

Constructor Summary

RMSQLExpressionBuilder()

Method Summary

java.lang.String	aliasForTable(RMEntity e) Convenience method to get the alias for a particular entity
java.lang.String	aliasForTable(java.lang.String tableName) Find or create the alias for this table.
java.lang.String	buildBooleanWhereClause(java.lang.Object node, RMEntity rootEntity)
java.lang.String	buildSelect(RMEntity e) Builds the SELECT clause to select just those columns of the entity's table.
java.lang.String	buildWhereClause(java.lang.Object node, RMEntity rootEntity) Build the sql equivalent of the ReportMill filter expression.
java.lang.String	getSQLExpression(RMKeyChain chain, RMEntity rootEntity) Top level method for turning a keychain into a SQL expression.
java.lang.String	getSQLExpression(java.lang.String expr, RMEntity rootEntity) Parses the string in expr and returns the sql expression.
java.util.Map	popSelect() restore previous select statement
void	pushSelect() push a new select statement
java.lang.String	sqlForAggregate(RMJoin toManyJoin, RMKeyChain expr) Handles RM aggregators.
java.lang.String	sqlForFunctionCall(java.lang.String fname, RMEntity rootEntity, java.lang.String args) Aggregator type functions are handled by sqlForAggregate.
java.lang.String	sqlForProperty(RMKeyChain key, RMEntity rootEntity)
java.lang.String	sqlForRMFunctionName(java.lang.String func) Map reportmill builtins to their SQL equivalents
java.lang.String	sqlForString(java.lang.String s) Turns a string (as returned from the keychain parser) into an sql string.
java.lang.String	sqlForTopLevelAggregate(RMEntity entity, RMKeyChain expr) Used for aggregates of an entire entity, for example "Up.total.revenue" or "Studios.average.budget"

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

RMSQLExpressionBuilder

public RMSQLExpressionBuilder()

Method Detail

getSQLExpression

public java.lang.String getSQLExpression(RMKeyChain chain,
                                         RMEntity rootEntity)
                                  throws java.sql.SQLException

Top level method for turning a keychain into a SQL expression. The expression must be a boolean expression (eg. revenue>1000000). All keys in the expression are evaluated relative to rootEntity.

Throws:

java.sql.SQLException

getSQLExpression

public java.lang.String getSQLExpression(java.lang.String expr,
                                         RMEntity rootEntity)
                                  throws java.sql.SQLException

Parses the string in expr and returns the sql expression.

Throws:

java.sql.SQLException

buildSelect

public java.lang.String buildSelect(RMEntity e)

Builds the SELECT clause to select just those columns of the entity's table.

buildWhereClause

public java.lang.String buildWhereClause(java.lang.Object node,
                                         RMEntity rootEntity)
                                  throws java.sql.SQLException

Build the sql equivalent of the ReportMill filter expression. Returns only the string associated with the first part of the WHERE clause. Joins are recorded for adding on later.

Throws:

java.sql.SQLException

buildBooleanWhereClause

public java.lang.String buildBooleanWhereClause(java.lang.Object node,
                                                RMEntity rootEntity)
                                         throws java.sql.SQLException

Throws:

java.sql.SQLException

sqlForString

public java.lang.String sqlForString(java.lang.String s)

Turns a string (as returned from the keychain parser) into an sql string.

sqlForProperty

public java.lang.String sqlForProperty(RMKeyChain key,
                                       RMEntity rootEntity)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

aliasForTable

public java.lang.String aliasForTable(java.lang.String tableName)

Find or create the alias for this table. NB: Won't be able to do self-joins with this method. SELECT t2.NAME FROM MOVIES t, MOVIES t2 WHERE t.NAME LIKE 'Star Wars' AND t.RATING=t2.RATING (find all movies with the same rating as Star Wars) Not sure how you'd specify that in an RM expression, anyway.

aliasForTable

public java.lang.String aliasForTable(RMEntity e)

Convenience method to get the alias for a particular entity

pushSelect

public void pushSelect()

push a new select statement

popSelect

public java.util.Map popSelect()

restore previous select statement

sqlForAggregate

public java.lang.String sqlForAggregate(RMJoin toManyJoin,
                                        RMKeyChain expr)
                                 throws java.sql.SQLException

Handles RM aggregators. The presence of an aggregate function in a filter expression is a little tricky. getMovies.average.revenue > 3000000 An aggregator takes an array of records, so there are only two places where it's appropriate to have an aggregate in the where clause: for a to-many relationship : getMovies.total.revenue or at the top level : Up.total.revenue or Movies.total.revenue TODO: think about filter(), too getMovies.total.revenue > Up.average.revenue (Note that SQL doesn't allow you to have any aggregates in the Where clause -- that's what the HAVING clause is for) To make an aggregate, we do the following. Given a to-many relationship and an aggregator, we create a sub-SELECT statement that performs the aggregation on the destination of the to-many and returns a temporary table with two columns - the foreign key for the original relationship and the result of the aggregation. So, getMovies.total.revenue (where getMovies is defined by a join of STUDIOS.ID->>MOVIES.STUDIOID) becomes (SELECT t.STUDIOID as foreignKey, SUM(t.REVENUE) as returnValue FROM MOVIES t GROUP BY t.STUDIOID)) subq By then performing the join on STUDIOS.ID->subq.foreignKey, we have transformed the to-many into a to-one. The resulting reference is then subq.returnValue Simple cases might be able to use the HAVING clause, but this is more generic and will work for any number of strange expressions.

Throws:

java.sql.SQLException

sqlForTopLevelAggregate

public java.lang.String sqlForTopLevelAggregate(RMEntity entity,
                                                RMKeyChain expr)
                                         throws java.sql.SQLException

Used for aggregates of an entire entity, for example "Up.total.revenue" or "Studios.average.budget"

Throws:

java.sql.SQLException

sqlForRMFunctionName

public java.lang.String sqlForRMFunctionName(java.lang.String func)

Map reportmill builtins to their SQL equivalents

sqlForFunctionCall

public java.lang.String sqlForFunctionCall(java.lang.String fname,
                                           RMEntity rootEntity,
                                           java.lang.String args)
                                    throws java.sql.SQLException

Aggregator type functions are handled by sqlForAggregate. This method should only be called for database built-ins like RANDOM(). getStudio.doSomething() should never happen

Throws:

java.sql.SQLException