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