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