/**
* 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);
}
/**
* 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());
}
}
}
}
