@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); } } }