@Test
public void testMultiValuedParamsForNonSupportingOperators() {
Map<String, Object> param = new HashMap<String, Object>();
param.put("param1", new String[] {"SHIPPED", "DELIVERED"});
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"[bc_ORDERS.BC_ORDERS_STATUS]=[param:param1]",
Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "="));
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"[bc_ORDERS.BC_ORDERS_STATUS]>[param:param1]",
Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", ">"));
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"[bc_ORDERS.BC_ORDERS_STATUS]<[param:param1]",
Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "<"));
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"[bc_ORDERS.BC_ORDERS_STATUS]>=[param:param1]",
Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", ">="));
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"[bc_ORDERS.BC_ORDERS_STATUS]<=[param:param1]",
Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "<="));
}
@Test
public void testMultiValuedParamsForNonSupportingFunctions() {
Map<String, Object> param = new HashMap<String, Object>();
param.put("param1", new String[] {"SHIPPED", "DELIVERED"});
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"ENDSWITH([bc_ORDERS.BC_ORDERS_STATUS];[param:param1])",
Messages.getErrorString(
"SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "ENDSWITH"));
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"BEGINSWITH([bc_ORDERS.BC_ORDERS_STATUS];[param:param1])",
Messages.getErrorString(
"SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "BEGINSWITH"));
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"LIKE([bc_ORDERS.BC_ORDERS_STATUS];[param:param1])",
Messages.getErrorString("SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "LIKE"));
handleFormulaFailure(
getOrdersModel(),
"Hypersonic",
param,
"COALESCE([bc_ORDERS.BC_ORDERS_STATUS];[param:param1])",
Messages.getErrorString(
"SqlOpenFormula.ERROR_0024_MULTIPLE_VALUES_NOT_SUPPORTED", "COALESCE"));
}
/**
* SqlOpenFormulaContext and SqlOpenFormula do not use libFormula in the traditional manner of
* executing a formula. Instead they generate the necessary SQL to be executed from an RDBMS.
*/
public Type resolveReferenceType(Object name) {
throw new UnsupportedOperationException(
Messages.getErrorString("SqlOpenFormulaContext.ERROR_0001_INVALID_USE")); // $NON-NLS-1$
}
/**
* 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;
}
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);
}
}
}