001/* 002// This software is subject to the terms of the Eclipse Public License v1.0 003// Agreement, available at the following URL: 004// http://www.eclipse.org/legal/epl-v10.html. 005// You must accept the terms of that agreement to use this software. 006// 007// Copyright (C) 2005-2005 Julian Hyde 008// Copyright (C) 2005-2011 Pentaho and others 009// All Rights Reserved. 010*/ 011package mondrian.rolap.agg; 012 013import mondrian.olap.Exp; 014import mondrian.olap.MondrianException; 015import mondrian.olap.Util; 016import mondrian.rolap.*; 017import mondrian.rolap.sql.SqlQuery; 018import mondrian.spi.Dialect; 019import mondrian.util.Pair; 020 021import java.util.*; 022 023/** 024 * Base class for {@link QuerySpec} implementations. 025 * 026 * @author jhyde 027 * @author Richard M. Emberson 028 */ 029public abstract class AbstractQuerySpec implements QuerySpec { 030 private final RolapStar star; 031 protected final boolean countOnly; 032 033 /** 034 * Creates an AbstractQuerySpec. 035 * 036 * @param star Star which defines columns of interest and their 037 * relationships 038 * 039 * @param countOnly If true, generate no GROUP BY clause, so the query 040 * returns a single row containing a grand total 041 */ 042 protected AbstractQuerySpec(final RolapStar star, boolean countOnly) { 043 this.star = star; 044 this.countOnly = countOnly; 045 } 046 047 /** 048 * Creates a query object. 049 * 050 * @return a new query object 051 */ 052 protected SqlQuery newSqlQuery() { 053 return getStar().getSqlQuery(); 054 } 055 056 public RolapStar getStar() { 057 return star; 058 } 059 060 /** 061 * Adds a measure to a query. 062 * 063 * @param i Ordinal of measure 064 * @param sqlQuery Query object 065 */ 066 protected void addMeasure(final int i, final SqlQuery sqlQuery) { 067 RolapStar.Measure measure = getMeasure(i); 068 if (!isPartOfSelect(measure)) { 069 return; 070 } 071 Util.assertTrue(measure.getTable() == getStar().getFactTable()); 072 measure.getTable().addToFrom(sqlQuery, false, true); 073 074 String exprInner = 075 measure.getExpression() == null 076 ? "*" 077 : measure.generateExprString(sqlQuery); 078 String exprOuter = measure.getAggregator().getExpression(exprInner); 079 sqlQuery.addSelect( 080 exprOuter, 081 measure.getInternalType(), 082 getMeasureAlias(i)); 083 } 084 085 protected abstract boolean isAggregate(); 086 087 protected Map<String, String> nonDistinctGenerateSql(SqlQuery sqlQuery) 088 { 089 // add constraining dimensions 090 RolapStar.Column[] columns = getColumns(); 091 int arity = columns.length; 092 if (countOnly) { 093 sqlQuery.addSelect("count(*)", SqlStatement.Type.INT); 094 } 095 for (int i = 0; i < arity; i++) { 096 RolapStar.Column column = columns[i]; 097 RolapStar.Table table = column.getTable(); 098 if (table.isFunky()) { 099 // this is a funky dimension -- ignore for now 100 continue; 101 } 102 table.addToFrom(sqlQuery, false, true); 103 104 String expr = column.generateExprString(sqlQuery); 105 106 StarColumnPredicate predicate = getColumnPredicate(i); 107 final String where = RolapStar.Column.createInExpr( 108 expr, 109 predicate, 110 column.getDatatype(), 111 sqlQuery); 112 if (!where.equals("true")) { 113 sqlQuery.addWhere(where); 114 } 115 116 if (countOnly) { 117 continue; 118 } 119 120 if (!isPartOfSelect(column)) { 121 continue; 122 } 123 124 // some DB2 (AS400) versions throw an error, if a column alias is 125 // there and *not* used in a subsequent order by/group by 126 final Dialect dialect = sqlQuery.getDialect(); 127 final String alias; 128 final Dialect.DatabaseProduct databaseProduct = 129 dialect.getDatabaseProduct(); 130 if (databaseProduct == Dialect.DatabaseProduct.DB2_AS400) { 131 alias = 132 sqlQuery.addSelect(expr, column.getInternalType(), null); 133 } else { 134 alias = 135 sqlQuery.addSelect( 136 expr, column.getInternalType(), getColumnAlias(i)); 137 } 138 139 if (isAggregate()) { 140 sqlQuery.addGroupBy(expr, alias); 141 } 142 143 // Add ORDER BY clause to make the results deterministic. 144 // Derby has a bug with ORDER BY, so ignore it. 145 if (isOrdered()) { 146 sqlQuery.addOrderBy(expr, true, false, false); 147 } 148 } 149 150 // Add compound member predicates 151 extraPredicates(sqlQuery); 152 153 // add measures 154 for (int i = 0, count = getMeasureCount(); i < count; i++) { 155 addMeasure(i, sqlQuery); 156 } 157 158 return Collections.emptyMap(); 159 } 160 161 /** 162 * Allows subclasses to specify if a given column must 163 * be returned as part of the result set, in the select clause. 164 */ 165 protected boolean isPartOfSelect(RolapStar.Column col) { 166 return true; 167 } 168 169 /** 170 * Allows subclasses to specify if a given column must 171 * be returned as part of the result set, in the select clause. 172 */ 173 protected boolean isPartOfSelect(RolapStar.Measure measure) { 174 return true; 175 } 176 177 /** 178 * Whether to add an ORDER BY clause to make results deterministic. 179 * Necessary if query returns more than one row and results are for 180 * human consumption. 181 * 182 * @return whether to sort query 183 */ 184 protected boolean isOrdered() { 185 return false; 186 } 187 188 public Pair<String, List<SqlStatement.Type>> generateSqlQuery() { 189 SqlQuery sqlQuery = newSqlQuery(); 190 191 int k = getDistinctMeasureCount(); 192 final Dialect dialect = sqlQuery.getDialect(); 193 final Map<String, String> groupingSetsAliases; 194 if (!dialect.allowsCountDistinct() && k > 0 195 || !dialect.allowsMultipleCountDistinct() && k > 1) 196 { 197 groupingSetsAliases = 198 distinctGenerateSql(sqlQuery, countOnly); 199 } else { 200 groupingSetsAliases = 201 nonDistinctGenerateSql(sqlQuery); 202 } 203 if (!countOnly) { 204 addGroupingFunction(sqlQuery); 205 addGroupingSets(sqlQuery, groupingSetsAliases); 206 } 207 return sqlQuery.toSqlAndTypes(); 208 } 209 210 protected void addGroupingFunction(SqlQuery sqlQuery) { 211 throw new UnsupportedOperationException(); 212 } 213 214 protected void addGroupingSets( 215 SqlQuery sqlQuery, 216 Map<String, String> groupingSetsAliases) 217 { 218 throw new UnsupportedOperationException(); 219 } 220 221 /** 222 * Returns the number of measures whose aggregation function is 223 * distinct-count. 224 * 225 * @return Number of distinct-count measures 226 */ 227 protected int getDistinctMeasureCount() { 228 int k = 0; 229 for (int i = 0, count = getMeasureCount(); i < count; i++) { 230 RolapStar.Measure measure = getMeasure(i); 231 if (measure.getAggregator().isDistinct()) { 232 ++k; 233 } 234 } 235 return k; 236 } 237 238 /** 239 * Generates a SQL query to retrieve the values in this segment using 240 * an algorithm which converts distinct-aggregates to non-distinct 241 * aggregates over subqueries. 242 * 243 * @param outerSqlQuery Query to modify 244 * @param countOnly If true, only generate a single row: no need to 245 * generate a GROUP BY clause or put any constraining columns in the 246 * SELECT clause 247 * @return A map of aliases used in the inner query if grouping sets 248 * were enabled. 249 */ 250 protected Map<String, String> distinctGenerateSql( 251 final SqlQuery outerSqlQuery, 252 boolean countOnly) 253 { 254 final Dialect dialect = outerSqlQuery.getDialect(); 255 final Dialect.DatabaseProduct databaseProduct = 256 dialect.getDatabaseProduct(); 257 final Map<String, String> groupingSetsAliases = 258 new HashMap<String, String>(); 259 // Generate something like 260 // 261 // select d0, d1, count(m0) 262 // from ( 263 // select distinct dim1.x as d0, dim2.y as d1, f.z as m0 264 // from f, dim1, dim2 265 // where dim1.k = f.k1 266 // and dim2.k = f.k2) as dummyname 267 // group by d0, d1 268 // 269 // or, if countOnly=true 270 // 271 // select count(m0) 272 // from ( 273 // select distinct f.z as m0 274 // from f, dim1, dim2 275 // where dim1.k = f.k1 276 // and dim2.k = f.k2) as dummyname 277 278 final SqlQuery innerSqlQuery = newSqlQuery(); 279 if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) { 280 innerSqlQuery.setDistinct(false); 281 } else { 282 innerSqlQuery.setDistinct(true); 283 } 284 // add constraining dimensions 285 RolapStar.Column[] columns = getColumns(); 286 int arity = columns.length; 287 for (int i = 0; i < arity; i++) { 288 RolapStar.Column column = columns[i]; 289 RolapStar.Table table = column.getTable(); 290 if (table.isFunky()) { 291 // this is a funky dimension -- ignore for now 292 continue; 293 } 294 table.addToFrom(innerSqlQuery, false, true); 295 String expr = column.generateExprString(innerSqlQuery); 296 StarColumnPredicate predicate = getColumnPredicate(i); 297 final String where = RolapStar.Column.createInExpr( 298 expr, 299 predicate, 300 column.getDatatype(), 301 innerSqlQuery); 302 if (!where.equals("true")) { 303 innerSqlQuery.addWhere(where); 304 } 305 if (countOnly) { 306 continue; 307 } 308 String alias = "d" + i; 309 alias = innerSqlQuery.addSelect(expr, null, alias); 310 if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) { 311 innerSqlQuery.addGroupBy(expr, alias); 312 } 313 final String quotedAlias = dialect.quoteIdentifier(alias); 314 outerSqlQuery.addSelectGroupBy(quotedAlias, null); 315 // Add this alias to the map of grouping sets aliases 316 groupingSetsAliases.put( 317 expr, 318 dialect.quoteIdentifier( 319 "dummyname." + alias)); 320 } 321 322 // add predicates not associated with columns 323 extraPredicates(innerSqlQuery); 324 325 // add measures 326 for (int i = 0, count = getMeasureCount(); i < count; i++) { 327 RolapStar.Measure measure = getMeasure(i); 328 329 Util.assertTrue(measure.getTable() == getStar().getFactTable()); 330 measure.getTable().addToFrom(innerSqlQuery, false, true); 331 332 String alias = getMeasureAlias(i); 333 String expr = measure.generateExprString(outerSqlQuery); 334 innerSqlQuery.addSelect( 335 expr, 336 measure.getInternalType(), 337 alias); 338 if (databaseProduct == Dialect.DatabaseProduct.GREENPLUM) { 339 innerSqlQuery.addGroupBy(expr, alias); 340 } 341 outerSqlQuery.addSelect( 342 measure.getAggregator().getNonDistinctAggregator() 343 .getExpression(dialect.quoteIdentifier(alias)), 344 measure.getInternalType()); 345 } 346 outerSqlQuery.addFrom(innerSqlQuery, "dummyname", true); 347 return groupingSetsAliases; 348 } 349 350 /** 351 * Adds predicates not associated with columns. 352 * 353 * @param sqlQuery Query 354 */ 355 protected void extraPredicates(SqlQuery sqlQuery) { 356 List<StarPredicate> predicateList = getPredicateList(); 357 for (StarPredicate predicate : predicateList) { 358 for (RolapStar.Column column 359 : predicate.getConstrainedColumnList()) 360 { 361 final RolapStar.Table table = column.getTable(); 362 table.addToFrom(sqlQuery, false, true); 363 } 364 StringBuilder buf = new StringBuilder(); 365 predicate.toSql(sqlQuery, buf); 366 final String where = buf.toString(); 367 if (!where.equals("true")) { 368 sqlQuery.addWhere(where); 369 } 370 } 371 } 372 373 /** 374 * Returns a list of predicates not associated with a particular column. 375 * 376 * @return list of non-column predicates 377 */ 378 protected List<StarPredicate> getPredicateList() { 379 return Collections.emptyList(); 380 } 381} 382 383 384// End AbstractQuerySpec.java