001 /* 002 // This software is subject to the terms of the Common Public License 003 // Agreement, available at the following URL: 004 // http://www.opensource.org/licenses/cpl.html. 005 // Copyright (C) 2004-2005 TONBELLER AG 006 // Copyright (C) 2005-2008 Julian Hyde and others 007 // All Rights Reserved. 008 // You must accept the terms of that agreement to use this software. 009 */ 010 package mondrian.rolap; 011 012 import mondrian.olap.*; 013 import mondrian.olap.fun.FunUtil; 014 import mondrian.rolap.sql.SqlQuery; 015 import mondrian.rolap.sql.TupleConstraint; 016 import mondrian.util.*; 017 018 import java.sql.ResultSet; 019 import java.sql.SQLException; 020 import java.util.*; 021 import javax.sql.DataSource; 022 023 /** 024 * Reads the members of a single level (level.members) or of multiple levels 025 * (crossjoin). 026 * 027 * @author luis f. canals 028 * @since Dec, 2007 029 * @version $Id: //open/mondrian/src/main/mondrian/rolap/HighCardSqlTupleReader.java#5 $ 030 */ 031 public class HighCardSqlTupleReader extends SqlTupleReader { 032 private ResultLoader resultLoader; 033 private boolean moreRows; 034 035 private final List<Target> targets = new ArrayList<Target>(); 036 int maxRows = 0; 037 038 public HighCardSqlTupleReader(final TupleConstraint constraint) { 039 super(constraint); 040 } 041 042 public void addLevelMembers( 043 final RolapLevel level, 044 final MemberBuilder memberBuilder, 045 final List<RolapMember> srcMembers) 046 { 047 targets.add( 048 new Target(level, memberBuilder, srcMembers, constraint, this)); 049 } 050 051 public Object getCacheKey() { 052 List<Object> key = new ArrayList<Object>(); 053 key.add(constraint.getCacheKey()); 054 key.add(SqlTupleReader.class); 055 for (Target target : targets) { 056 // don't include the level in the key if the target isn't 057 // processed through native sql 058 if (target.getSrcMembers() != null) { 059 key.add(target.getLevel()); 060 } 061 } 062 return key; 063 } 064 065 /** 066 * @return number of targets that contain enumerated sets with calculated 067 * members 068 */ 069 public int getEnumTargetCount() { 070 int enumTargetCount = 0; 071 for (Target target : targets) { 072 if (target.getSrcMembers() != null) { 073 enumTargetCount++; 074 } 075 } 076 return enumTargetCount; 077 } 078 079 protected void prepareTuples( 080 final DataSource dataSource, 081 final List<List<RolapMember>> partialResult, 082 final List<List<RolapMember>> newPartialResult) 083 { 084 String message = "Populating member cache with members for " + targets; 085 SqlStatement stmt = null; 086 final ResultSet resultSet; 087 boolean execQuery = (partialResult == null); 088 try { 089 if (execQuery) { 090 // we're only reading tuples from the targets that are 091 // non-enum targets 092 List<Target> partialTargets = new ArrayList<Target>(); 093 for (Target target : targets) { 094 if (target.getSrcMembers() == null) { 095 partialTargets.add(target); 096 } 097 } 098 String sql = makeLevelMembersSql(dataSource); 099 stmt = RolapUtil.executeQuery(dataSource, sql, maxRows, 100 "HighCardSqlTupleReader.readTuples " + partialTargets, 101 message, -1, -1); 102 resultSet = stmt.getResultSet(); 103 } else { 104 resultSet = null; 105 } 106 107 for (Target target : targets) { 108 target.open(); 109 } 110 111 // determine how many enum targets we have 112 int enumTargetCount = getEnumTargetCount(); 113 int[] srcMemberIdxes = null; 114 if (enumTargetCount > 0) { 115 srcMemberIdxes = new int[enumTargetCount]; 116 } 117 118 int currPartialResultIdx = 0; 119 if (execQuery) { 120 this.moreRows = resultSet.next(); 121 if (this.moreRows) { 122 ++stmt.rowCount; 123 } 124 } else { 125 this.moreRows = currPartialResultIdx < partialResult.size(); 126 } 127 128 this.resultLoader = new ResultLoader(enumTargetCount, 129 targets, stmt, resultSet, execQuery, partialResult, 130 newPartialResult); 131 132 // Read first and second elements if exists (or marks 133 // source as having "no more rows") 134 readNextTuple(); 135 readNextTuple(); 136 } catch (SQLException sqle) { 137 if (stmt != null) { 138 stmt.handle(sqle); 139 } else { 140 throw Util.newError(sqle, message); 141 } 142 } 143 } 144 145 public List<RolapMember> readMembers( 146 final DataSource dataSource, 147 final List<List<RolapMember>> partialResult, 148 final List<List<RolapMember>> newPartialResult) 149 { 150 prepareTuples(dataSource, partialResult, newPartialResult); 151 assert targets.size() == 1; 152 return targets.get(0).close(); 153 } 154 155 public List<RolapMember[]> readTuples( 156 final DataSource jdbcConnection, 157 final List<List<RolapMember>> partialResult, 158 final List<List<RolapMember>> newPartialResult) 159 { 160 prepareTuples(jdbcConnection, partialResult, newPartialResult); 161 162 // List of tuples 163 final int n = targets.size(); 164 final List<RolapMember>[] lists = new List[n]; 165 for (int i = 0; i < n; i++) { 166 lists[i] = targets.get(i).close(); 167 } 168 169 final List<RolapMember[]> tupleList = 170 new TraversalList<RolapMember>(lists, RolapMember.class); 171 172 // need to hierarchize the columns from the enumerated targets 173 // since we didn't necessarily add them in the order in which 174 // they originally appeared in the cross product 175 int enumTargetCount = getEnumTargetCount(); 176 if (enumTargetCount > 0) { 177 FunUtil.hierarchize(tupleList, false); 178 } 179 return tupleList; 180 } 181 182 /** 183 * Avoids loading of more results. 184 */ 185 public void noMoreRows() { 186 this.moreRows = false; 187 } 188 189 /** 190 * Reads next tuple notifing all internal targets. 191 * 192 * @return whether there are any more rows 193 */ 194 public boolean readNextTuple() { 195 if (!this.moreRows) { 196 return false; 197 } 198 try { 199 this.moreRows = this.resultLoader.loadResult(); 200 } catch (SQLException sqle) { 201 this.resultLoader.handle(sqle); 202 this.moreRows = false; 203 } 204 if (!this.moreRows) { 205 this.resultLoader.close(); 206 } 207 return this.moreRows; 208 } 209 210 public void setMaxRows(int maxRows) { 211 this.maxRows = maxRows; 212 } 213 214 public int getMaxRows() { 215 return maxRows; 216 } 217 218 // 219 // Private stuff ------------------------------------------ 220 // 221 222 private String makeLevelMembersSql(DataSource dataSource) { 223 // In the case of a virtual cube, if we need to join to the fact 224 // table, we do not necessarily have a single underlying fact table, 225 // as the underlying base cubes in the virtual cube may all reference 226 // different fact tables. 227 // 228 // Therefore, we need to gather the underlying fact tables by going 229 // through the list of measures referenced in the query. And then 230 // we generate one sub-select per fact table, joining against each 231 // underlying fact table, unioning the sub-selects. 232 RolapCube cube = null; 233 boolean virtualCube = false; 234 if (constraint instanceof SqlContextConstraint) { 235 SqlContextConstraint sqlConstraint = 236 (SqlContextConstraint) constraint; 237 if (sqlConstraint.isJoinRequired()) { 238 Query query = constraint.getEvaluator().getQuery(); 239 cube = (RolapCube) query.getCube(); 240 virtualCube = cube.isVirtual(); 241 } 242 } 243 244 if (virtualCube) { 245 final StringBuffer selectString = new StringBuffer(); 246 final Query query = constraint.getEvaluator().getQuery(); 247 final List<RolapCube> baseCubes = query.getBaseCubes(); 248 249 int k = -1; 250 for (RolapCube baseCube : baseCubes) { 251 boolean finalSelect = (++k == baseCubes.size() - 1); 252 WhichSelect whichSelect = 253 finalSelect ? WhichSelect.LAST : WhichSelect.NOT_LAST; 254 selectString.append( 255 generateSelectForLevels(dataSource, baseCube, whichSelect)); 256 if (!finalSelect) { 257 selectString.append(" union "); 258 } 259 } 260 return selectString.toString(); 261 } else { 262 return generateSelectForLevels(dataSource, cube, WhichSelect.ONLY); 263 } 264 } 265 266 /** 267 * Generates the SQL string corresponding to the levels referenced. 268 * 269 * @param dataSource jdbc connection that they query will execute 270 * against 271 * @param baseCube this is the cube object for regular cubes, and the 272 * underlying base cube for virtual cubes 273 * @param whichSelect Position of this select statement in a union 274 * @return SQL statement string 275 */ 276 private String generateSelectForLevels( 277 DataSource dataSource, 278 RolapCube baseCube, 279 WhichSelect whichSelect) { 280 281 String s = "while generating query to retrieve members of level(s) " 282 + targets; 283 SqlQuery sqlQuery = SqlQuery.newQuery(dataSource, s); 284 285 for (Target target : targets) { 286 if (target.getSrcMembers() == null) { 287 addLevelMemberSql( 288 sqlQuery, 289 target.getLevel(), 290 baseCube, 291 whichSelect); 292 } 293 } 294 295 // additional constraints 296 constraint.addConstraint(sqlQuery, baseCube); 297 298 return sqlQuery.toString(); 299 } 300 301 /** 302 * Generates the SQL statement to access members of <code>level</code>. For 303 * example, <blockquote> 304 * <pre>SELECT "country", "state_province", "city" 305 * FROM "customer" 306 * GROUP BY "country", "state_province", "city", "init", "bar" 307 * ORDER BY "country", "state_province", "city"</pre> 308 * </blockquote> accesses the "City" level of the "Customers" 309 * hierarchy. Note that:<ul> 310 * 311 * <li><code>"country", "state_province"</code> are the parent keys;</li> 312 * 313 * <li><code>"city"</code> is the level key;</li> 314 * 315 * <li><code>"init", "bar"</code> are member properties.</li> 316 * </ul> 317 * 318 * @param sqlQuery the query object being constructed 319 * @param level level to be added to the sql query 320 * @param baseCube this is the cube object for regular cubes, and the 321 * underlying base cube for virtual cubes 322 * @param whichSelect describes whether this select belongs to a larger 323 * select containing unions or this is a non-union select 324 */ 325 private void addLevelMemberSql( 326 SqlQuery sqlQuery, 327 RolapLevel level, 328 RolapCube baseCube, 329 WhichSelect whichSelect) 330 { 331 RolapHierarchy hierarchy = level.getHierarchy(); 332 333 // lookup RolapHierarchy of base cube that matches this hierarchy 334 if (hierarchy instanceof RolapCubeHierarchy) { 335 RolapCubeHierarchy cubeHierarchy = (RolapCubeHierarchy)hierarchy; 336 if (baseCube != null 337 && !cubeHierarchy.getDimension().getCube().equals(baseCube)) { 338 hierarchy = baseCube.findBaseCubeHierarchy(hierarchy); 339 } 340 } 341 342 RolapLevel[] levels = (RolapLevel[]) hierarchy.getLevels(); 343 int levelDepth = level.getDepth(); 344 for (int i = 0; i <= levelDepth; i++) { 345 RolapLevel currLevel = levels[i]; 346 if (currLevel.isAll()) { 347 continue; 348 } 349 350 MondrianDef.Expression keyExp = currLevel.getKeyExp(); 351 MondrianDef.Expression ordinalExp = currLevel.getOrdinalExp(); 352 MondrianDef.Expression captionExp = currLevel.getCaptionExp(); 353 354 String keySql = keyExp.getExpression(sqlQuery); 355 String ordinalSql = ordinalExp.getExpression(sqlQuery); 356 357 hierarchy.addToFrom(sqlQuery, keyExp); 358 hierarchy.addToFrom(sqlQuery, ordinalExp); 359 360 String captionSql = null; 361 if (captionExp != null) { 362 captionSql = captionExp.getExpression(sqlQuery); 363 hierarchy.addToFrom(sqlQuery, captionExp); 364 } 365 366 sqlQuery.addSelect(keySql); 367 sqlQuery.addGroupBy(keySql); 368 369 if (!ordinalSql.equals(keySql)) { 370 sqlQuery.addSelect(ordinalSql); 371 sqlQuery.addGroupBy(ordinalSql); 372 } 373 374 if (captionSql != null) { 375 sqlQuery.addSelect(captionSql); 376 sqlQuery.addGroupBy(captionSql); 377 } 378 379 constraint.addLevelConstraint(sqlQuery, baseCube, null, currLevel); 380 381 // If this is a select on a virtual cube, the query will be 382 // a union, so the order by columns need to be numbers, 383 // not column name strings or expressions. 384 switch (whichSelect) { 385 case LAST: 386 sqlQuery.addOrderBy( 387 Integer.toString( 388 sqlQuery.getCurrentSelectListSize()), 389 true, false, true); 390 break; 391 case ONLY: 392 sqlQuery.addOrderBy(ordinalSql, true, false, true); 393 break; 394 } 395 396 RolapProperty[] properties = currLevel.getProperties(); 397 for (RolapProperty property : properties) { 398 String propSql = property.getExp().getExpression(sqlQuery); 399 sqlQuery.addSelect(propSql); 400 sqlQuery.addGroupBy(propSql); 401 } 402 } 403 } 404 405 /** 406 * Description of the position of a SELECT statement in a UNION. Queries 407 * on virtual cubes tend to generate unions. 408 */ 409 enum WhichSelect { 410 /** 411 * Select statement does not belong to a union. 412 */ 413 ONLY, 414 /** 415 * Select statement belongs to a UNION, but is not the last. Typically 416 * this occurs when querying a virtual cube. 417 */ 418 NOT_LAST, 419 /** 420 * Select statement is the last in a UNION. Typically 421 * this occurs when querying a virtual cube. 422 */ 423 LAST 424 } 425 } 426 427 // End HighCardSqlTupleReader.java