protected List<LogicalTable> getTablesInvolved(
LogicalModel model,
List<Selection> selections,
List<Constraint> conditions,
List<Order> orderBy,
Map<Constraint, SqlOpenFormula> constraintFormulaMap,
Map<String, Object> parameters,
boolean genAsPreparedStatement,
DatabaseMeta databaseMeta,
String locale,
Constraint securityConstraint) {
Set<LogicalTable> treeSet = new TreeSet<LogicalTable>();
// Figure out which tables are involved in the SELECT
//
for (Selection selection : selections) {
// We need to figure out which tables are involved in the formula.
// This could simply be the parent table, but it could also be another one too.
//
// If we want to know all the tables involved in the query, we need to parse all the formula
// first
// TODO: We re-use the static method below, maybe there is a better way to clean this up a
// bit.
//
SqlAndTables sqlAndTables =
getBusinessColumnSQL(
model, selection, null, parameters, genAsPreparedStatement, databaseMeta, locale);
// Add the involved tables to the list...
//
for (LogicalTable businessTable : sqlAndTables.getUsedTables()) {
treeSet.add(businessTable);
}
}
// Figure out which tables are involved in the WHERE
//
for (Constraint condition : conditions) {
SqlOpenFormula formula = constraintFormulaMap.get(condition);
List<Selection> cols = formula.getSelections();
for (Selection selection : cols) {
LogicalTable businessTable = selection.getLogicalColumn().getLogicalTable();
treeSet.add(businessTable);
}
}
// Figure out which tables are involved in the ORDER BY
//
for (Order order : orderBy) {
SqlAndTables sqlAndTables =
getBusinessColumnSQL(
model,
order.getSelection(),
null,
parameters,
genAsPreparedStatement,
databaseMeta,
locale);
// Add the involved tables to the list...
//
for (LogicalTable businessTable : sqlAndTables.getUsedTables()) {
treeSet.add(businessTable);
}
}
// find any tables listed in the security constraint
if (securityConstraint != null) {
SqlOpenFormula formula = constraintFormulaMap.get(securityConstraint);
List<Selection> cols = formula.getSelections();
for (Selection selection : cols) {
treeSet.add(selection.getLogicalColumn().getLogicalTable());
}
}
return new ArrayList<LogicalTable>(treeSet);
}
/**
* returns the generated SQL and additional metadata
*
* @param selections The selected business columns
* @param conditions the conditions to apply (null = no conditions)
* @param orderBy the ordering (null = no order by clause)
* @param databaseMeta the meta info which determines the SQL generated.
* @param locale the locale
* @param disableDistinct if true, disables default behavior of using DISTINCT when there are no
* groupings.
* @param securityConstraint if provided, applies a global security constraint to the query
* @return a SQL query based on a column selection, conditions and a locale
*/
protected MappedQuery getSQL(
LogicalModel model,
List<Selection> selections,
List<Constraint> conditions,
List<Order> orderBy,
DatabaseMeta databaseMeta,
String locale,
Map<String, Object> parameters,
boolean genAsPreparedStatement,
boolean disableDistinct,
int limit,
Constraint securityConstraint)
throws PentahoMetadataException {
SQLQueryModel query = new SQLQueryModel();
// Get settings for the query model
Object val = null;
val = model.getProperty("delay_outer_join_conditions"); // $NON-NLS-1$
if ((val != null) && (val instanceof Boolean)) {
query.setDelayOuterJoinConditions(((Boolean) val).booleanValue());
}
Map<String, String> columnsMap = new HashMap<String, String>();
// generate the formula objects for constraints
Map<Constraint, SqlOpenFormula> constraintFormulaMap =
new HashMap<Constraint, SqlOpenFormula>();
for (Constraint constraint : conditions) {
SqlOpenFormula formula =
new SqlOpenFormula(
model,
databaseMeta,
constraint.getFormula(),
null,
parameters,
genAsPreparedStatement);
formula.parseAndValidate();
constraintFormulaMap.put(constraint, formula);
}
if (securityConstraint != null) {
SqlOpenFormula formula =
new SqlOpenFormula(
model,
databaseMeta,
securityConstraint.getFormula(),
null,
parameters,
genAsPreparedStatement);
formula.parseAndValidate();
constraintFormulaMap.put(securityConstraint, formula);
}
// These are the tables involved in the field selection
//
List<LogicalTable> tabs =
getTablesInvolved(
model,
selections,
conditions,
orderBy,
constraintFormulaMap,
parameters,
genAsPreparedStatement,
databaseMeta,
locale,
securityConstraint);
// Now get the shortest path between these tables.
Path path = getShortestPathBetween(model, tabs);
if (path == null) {
throw new PentahoMetadataException(
Messages.getErrorString("SqlGenerator.ERROR_0002_FAILED_TO_FIND_PATH")); // $NON-NLS-1$
}
List<LogicalTable> usedBusinessTables = path.getUsedTables();
if (path.size() == 0) {
// just a selection from 1 table: pick any column...
if (selections.size() > 0) // Otherwise, why bother, right?
{
usedBusinessTables.add(selections.get(0).getLogicalColumn().getLogicalTable());
}
}
Map<LogicalTable, String> tableAliases = null;
if (usedBusinessTables.size() > 0) {
// generate tableAliases mapping
int maxAliasNameWidth = SQLDialectFactory.getSQLDialect(databaseMeta).getMaxTableNameLength();
tableAliases = new HashMap<LogicalTable, String>();
for (LogicalTable table : usedBusinessTables) {
String uniqueAlias =
generateUniqueAlias(table.getId(), maxAliasNameWidth, tableAliases.values());
tableAliases.put(table, uniqueAlias);
}
boolean group =
hasFactsInIt(
model,
selections,
conditions,
constraintFormulaMap,
parameters,
genAsPreparedStatement,
databaseMeta,
locale);
generateSelect(
query,
model,
databaseMeta,
selections,
disableDistinct,
limit,
group,
locale,
tableAliases,
columnsMap,
parameters,
genAsPreparedStatement);
generateFromAndWhere(
query,
usedBusinessTables,
model,
path,
conditions,
tableAliases,
constraintFormulaMap,
parameters,
genAsPreparedStatement,
databaseMeta,
locale);
if (group) {
generateGroupBy(
query,
model,
selections,
tableAliases,
parameters,
genAsPreparedStatement,
databaseMeta,
locale);
}
generateOrderBy(
query,
model,
orderBy,
databaseMeta,
locale,
tableAliases,
columnsMap,
parameters,
genAsPreparedStatement);
if (securityConstraint != null) {
// apply current table aliases
SqlOpenFormula securityFormula = constraintFormulaMap.get(securityConstraint);
securityFormula.setTableAliases(tableAliases);
// generate sql
String sqlFormula = securityFormula.generateSQL(locale);
query.setSecurityConstraint(sqlFormula, securityFormula.hasAggregate());
}
}
// this is available to classes that override sql generation behavior
preprocessQueryModel(query, selections, tableAliases, databaseMeta);
// Convert temporary param placements with Sql Prepared Statement ? values
SQLDialectInterface dialect = SQLDialectFactory.getSQLDialect(databaseMeta);
List<String> paramNames = null;
String sql = dialect.generateSelectStatement(query);
Pattern p = Pattern.compile("___PARAM\\[(.*?)\\]___"); // $NON-NLS-1$
Matcher m = p.matcher(sql);
StringBuffer sb = new StringBuffer();
while (m.find()) {
String paramName = m.group(1);
String repl = "?";
if (parameters.get(paramName) instanceof Object[]) {
Object[] paramz = (Object[]) parameters.get(paramName);
for (int i = 1; i < paramz.length; i++) {
repl += ", ?";
}
}
m.appendReplacement(sb, repl); // $NON-NLS-1$
if (paramNames == null) {
paramNames = new ArrayList<String>();
}
paramNames.add(paramName);
}
m.appendTail(sb);
String sqlStr = sb.toString();
if (logger.isTraceEnabled()) {
logger.trace(sqlStr);
}
// this is available to classes that override sql generation behavior
String sqlOutput = processGeneratedSql(sb.toString());
return new MappedQuery(sqlOutput, columnsMap, selections, paramNames);
}
protected String getJoin(
LogicalModel businessModel,
LogicalRelationship relation,
Map<LogicalTable, String> tableAliases,
Map<String, Object> parameters,
boolean genAsPreparedStatement,
DatabaseMeta databaseMeta,
String locale)
throws PentahoMetadataException {
String join = ""; // $NON-NLS-1$
if (relation.isComplex()) {
try {
// parse join as MQL
SqlOpenFormula formula =
new SqlOpenFormula(
businessModel,
databaseMeta,
relation.getComplexJoin(),
tableAliases,
parameters,
genAsPreparedStatement);
formula.parseAndValidate();
join = formula.generateSQL(locale);
} catch (PentahoMetadataException e) {
// backward compatibility, deprecate
logger.warn(
Messages.getErrorString(
"SqlGenerator.ERROR_0017_FAILED_TO_PARSE_COMPLEX_JOIN", relation.getComplexJoin()),
e); //$NON-NLS-1$
join = relation.getComplexJoin();
}
} else if (relation.getFromTable() != null
&& relation.getToTable() != null
&& relation.getFromColumn() != null
&& relation.getToColumn() != null) {
// Left side
String leftTableAlias = null;
if (tableAliases != null) {
leftTableAlias = tableAliases.get(relation.getFromColumn().getLogicalTable());
} else {
leftTableAlias = relation.getFromColumn().getLogicalTable().getId();
}
join = databaseMeta.quoteField(leftTableAlias);
join += "."; // $NON-NLS-1$
join +=
databaseMeta.quoteField(
(String) relation.getFromColumn().getProperty(SqlPhysicalColumn.TARGET_COLUMN));
// Equals
join += " = "; // $NON-NLS-1$
// Right side
String rightTableAlias = null;
if (tableAliases != null) {
rightTableAlias = tableAliases.get(relation.getToColumn().getLogicalTable());
} else {
rightTableAlias = relation.getToColumn().getLogicalTable().getId();
}
join += databaseMeta.quoteField(rightTableAlias);
join += "."; // $NON-NLS-1$
join +=
databaseMeta.quoteField(
(String) relation.getToColumn().getProperty(SqlPhysicalColumn.TARGET_COLUMN));
} else {
throw new PentahoMetadataException(
Messages.getErrorString(
"SqlGenerator.ERROR_0003_INVALID_RELATION", relation.toString())); // $NON-NLS-1$
}
return join;
}
/**
* This method first traverses the set of included business tables and renders those tables to the
* SQL string buffer. Second, it traverses the list of joins and renders those in the WHERE
* clause. Finally, it traverses the constraints and adds them to the where or having clauses.
*
* @param query sql query model
* @param usedBusinessTables used business tables in query
* @param model the current business model
* @param path the join path
* @param conditions the where conditions
* @param databaseMeta database metadata
* @param locale locale string
*/
protected void generateFromAndWhere(
SQLQueryModel query,
List<LogicalTable> usedBusinessTables,
LogicalModel model,
Path path,
List<Constraint> conditions,
Map<LogicalTable, String> tableAliases,
Map<Constraint, SqlOpenFormula> constraintFormulaMap,
Map<String, Object> parameters,
boolean genAsPreparedStatement,
DatabaseMeta databaseMeta,
String locale)
throws PentahoMetadataException {
// Boolean delayConditionOnOuterJoin = null;
// Object val = null;
// FROM TABLES
for (int i = 0; i < usedBusinessTables.size(); i++) {
LogicalTable businessTable = usedBusinessTables.get(i);
String schemaName = null;
if (businessTable.getProperty(SqlPhysicalTable.TARGET_SCHEMA) != null) {
schemaName =
databaseMeta.quoteField(
(String) businessTable.getProperty(SqlPhysicalTable.TARGET_SCHEMA));
}
// ToDo: Allow table-level override of delaying conditions.
// val = businessTable.getProperty("delay_table_outer_join_conditions");
// if ( (val != null) && (val instanceof Boolean) ) {
// delayConditionOnOuterJoin = (Boolean)val;
// } else {
// delayConditionOnOuterJoin = null;
// }
// this code allows subselects to drive the physical model.
// TODO: make this key off a metadata flag vs. the
// beginning of the table name.
String tableName = (String) businessTable.getProperty(SqlPhysicalTable.TARGET_TABLE);
TargetTableType type =
(TargetTableType) businessTable.getProperty(SqlPhysicalTable.TARGET_TABLE_TYPE);
if (type == TargetTableType.INLINE_SQL) {
tableName = "(" + tableName + ")"; // $NON-NLS-1$ //$NON-NLS-2$
} else {
tableName = databaseMeta.getQuotedSchemaTableCombination(schemaName, tableName);
}
query.addTable(tableName, databaseMeta.quoteField(tableAliases.get(businessTable)));
}
// JOIN CONDITIONS
if (path != null) {
for (int i = 0; i < path.size(); i++) {
LogicalRelationship relation = path.getRelationship(i);
String joinFormula =
getJoin(
model,
relation,
tableAliases,
parameters,
genAsPreparedStatement,
databaseMeta,
locale);
String joinOrderKey = relation.getJoinOrderKey();
JoinType joinType;
switch (RelationshipType.getJoinType(relation.getRelationshipType())) {
case LEFT_OUTER:
joinType = JoinType.LEFT_OUTER_JOIN;
break;
case RIGHT_OUTER:
joinType = JoinType.RIGHT_OUTER_JOIN;
break;
case FULL_OUTER:
joinType = JoinType.FULL_OUTER_JOIN;
break;
default:
joinType = JoinType.INNER_JOIN;
break;
}
String leftTableName =
databaseMeta.getQuotedSchemaTableCombination(
(String) relation.getFromTable().getProperty(SqlPhysicalTable.TARGET_SCHEMA),
(String) relation.getFromTable().getProperty(SqlPhysicalTable.TARGET_TABLE));
String leftTableAlias = tableAliases.get(relation.getFromTable());
String rightTableName =
databaseMeta.getQuotedSchemaTableCombination(
(String) relation.getToTable().getProperty(SqlPhysicalTable.TARGET_SCHEMA),
(String) relation.getToTable().getProperty(SqlPhysicalTable.TARGET_TABLE));
String rightTableAlias = tableAliases.get(relation.getToTable());
query.addJoin(
leftTableName,
leftTableAlias,
rightTableName,
rightTableAlias,
joinType,
joinFormula,
joinOrderKey);
// query.addWhereFormula(joinFormula, "AND"); //$NON-NLS-1$
}
}
// WHERE CONDITIONS
if (conditions != null) {
boolean first = true;
for (Constraint condition : conditions) {
SqlOpenFormula formula = constraintFormulaMap.get(condition);
// configure formula to use table aliases
formula.setTableAliases(tableAliases);
// The ones with aggregates in it are for the HAVING clause
if (!formula.hasAggregate()) {
String sqlFormula = formula.generateSQL(locale);
String[] usedTables = formula.getLogicalTableIDs();
query.addWhereFormula(sqlFormula, condition.getCombinationType().toString(), usedTables);
first = false;
} else {
query.addHavingFormula(
formula.generateSQL(locale), condition.getCombinationType().toString());
}
}
}
}
public static SqlAndTables getBusinessColumnSQL(
LogicalModel businessModel,
Selection column,
Map<LogicalTable, String> tableAliases,
Map<String, Object> parameters,
boolean genAsPreparedStatement,
DatabaseMeta databaseMeta,
String locale) {
String targetColumn =
(String) column.getLogicalColumn().getProperty(SqlPhysicalColumn.TARGET_COLUMN);
LogicalTable logicalTable = column.getLogicalColumn().getLogicalTable();
if (column.getLogicalColumn().getProperty(SqlPhysicalColumn.TARGET_COLUMN_TYPE)
== TargetColumnType.OPEN_FORMULA) {
// convert to sql using libformula subsystem
try {
// we'll need to pass in some context to PMSFormula so it can resolve aliases if necessary
SqlOpenFormula formula =
new SqlOpenFormula(
businessModel,
logicalTable,
databaseMeta,
targetColumn,
tableAliases,
parameters,
genAsPreparedStatement);
formula.parseAndValidate();
String formulaSql = formula.generateSQL(locale);
// check for old style, where function is hardcoded in the model.
if (column.hasAggregate() && !hasAggregateDefinedAlready(formulaSql, databaseMeta)) {
formulaSql = getFunctionExpression(column, formulaSql, databaseMeta);
}
return new SqlAndTables(formulaSql, formula.getLogicalTables(), formula.getSelections());
} catch (PentahoMetadataException e) {
// this is for backwards compatibility.
// eventually throw any errors
logger.warn(
Messages.getErrorString(
"SqlGenerator.ERROR_0001_FAILED_TO_PARSE_FORMULA", targetColumn),
e); //$NON-NLS-1$
// Report just this table and column as being used along with the formula.
//
return new SqlAndTables(targetColumn, logicalTable, column);
}
} else {
String tableColumn = ""; // $NON-NLS-1$
// this step is required because this method is called in two contexts. The first
// call determines all the tables involved, making it impossible to guarantee
// unique aliases.
String tableAlias = null;
if (tableAliases != null) {
tableAlias = tableAliases.get(logicalTable);
} else {
tableAlias = logicalTable.getId();
}
tableColumn += databaseMeta.quoteField(tableAlias);
tableColumn += "."; // $NON-NLS-1$
// TODO: WPG: instead of using formula, shouldn't we use the physical column's name?
tableColumn += databaseMeta.quoteField(targetColumn);
// For the having clause, for example: HAVING sum(turnover) > 100
if (column.hasAggregate()) {
return new SqlAndTables(
getFunctionExpression(column, tableColumn, databaseMeta), logicalTable, column);
} else {
return new SqlAndTables(tableColumn, logicalTable, column);
}
}
}
