001 /*
002 // $Id: //open/mondrian/src/main/mondrian/rolap/aggmatcher/AggGen.java#19 $
003 // This software is subject to the terms of the Common Public License
004 // Agreement, available at the following URL:
005 // http://www.opensource.org/licenses/cpl.html.
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
011 package mondrian.rolap.aggmatcher;
012
013 import mondrian.olap.MondrianDef;
014 import mondrian.olap.Util;
015 import mondrian.rolap.RolapStar;
016 import mondrian.rolap.sql.SqlQuery;
017 import mondrian.rolap.RolapAggregator;
018 import org.apache.log4j.Logger;
019 import java.io.PrintWriter;
020 import java.io.StringWriter;
021 import java.util.List;
022 import java.util.ArrayList;
023 import java.util.Set;
024 import java.util.HashSet;
025 import java.util.Map;
026 import java.util.HashMap;
027 import java.util.Iterator;
028 import java.sql.SQLException;
029 import java.sql.Types;
030
031 /**
032 * This class is used to create "lost" and "collapsed" aggregate table
033 * creation sql (creates the rdbms table and inserts into it from the base
034 * fact table).
035 *
036 * @author Richard M. Emberson
037 * @version $Id: //open/mondrian/src/main/mondrian/rolap/aggmatcher/AggGen.java#19 $
038 */
039 public class AggGen {
040 private static final Logger LOGGER = Logger.getLogger(AggGen.class);
041
042 private final String cubeName;
043 private final RolapStar star;
044 private final RolapStar.Column[] columns;
045
046 /** map RolapStar.Table to list of JdbcSchema Column Usages */
047 private final Map<RolapStar.Table, List<JdbcSchema.Table.Column.Usage>>
048 collapsedColumnUsages =
049 new HashMap<RolapStar.Table, List<JdbcSchema.Table.Column.Usage>>();
050
051 /** set of JdbcSchema Column Usages */
052 private final Set<JdbcSchema.Table.Column.Usage> notLostColumnUsages =
053 new HashSet<JdbcSchema.Table.Column.Usage>();
054
055 /** list of JdbcSchema Column Usages */
056 private final List<JdbcSchema.Table.Column.Usage> measures =
057 new ArrayList<JdbcSchema.Table.Column.Usage>();
058
059 private boolean isReady;
060
061 public AggGen(
062 String cubeName,
063 RolapStar star,
064 RolapStar.Column[] columns)
065 {
066 this.cubeName = cubeName;
067 this.star = star;
068 this.columns = columns;
069 init();
070 }
071
072 private Logger getLogger() {
073 return LOGGER;
074 }
075
076 /**
077 * Return true if this instance is ready to generate the sql. If false,
078 * then something went wrong as it was trying to understand the columns.
079 */
080 public boolean isReady() {
081 return isReady;
082 }
083
084 protected RolapStar.Table getFactTable() {
085 return star.getFactTable();
086 }
087
088 protected String getFactTableName() {
089 return getFactTable().getAlias();
090 }
091
092 protected SqlQuery getSqlQuery() {
093 return star.getSqlQuery();
094 }
095
096 protected String getFactCount() {
097 return "fact_count";
098 }
099
100 protected JdbcSchema.Table getTable(JdbcSchema db, RolapStar.Table rt) {
101 JdbcSchema.Table jt = getTable(db, rt.getAlias());
102 return (jt == null)
103 ? getTable(db, rt.getTableName())
104 : jt;
105 }
106
107 protected JdbcSchema.Table getTable(JdbcSchema db, String name) {
108 return db.getTable(name);
109 }
110
111 protected JdbcSchema.Table.Column getColumn(
112 JdbcSchema.Table table,
113 String name) {
114 return table.getColumn(name);
115 }
116
117 protected String getRolapStarColumnName(RolapStar.Column rColumn) {
118 MondrianDef.Expression expr = rColumn.getExpression();
119 if (expr instanceof MondrianDef.Column) {
120 MondrianDef.Column cx = (MondrianDef.Column) expr;
121 return cx.getColumnName();
122 }
123 return null;
124 }
125 protected void addForeignKeyToNotLostColumnUsages(
126 JdbcSchema.Table.Column column) {
127
128 // first make sure its not already in
129 String cname = column.getName();
130 for (JdbcSchema.Table.Column.Usage usage : notLostColumnUsages) {
131 JdbcSchema.Table.Column c = usage.getColumn();
132 if (cname.equals(c.getName())) {
133 return;
134 }
135 }
136 JdbcSchema.Table.Column.Usage usage;
137 if (column.hasUsage(JdbcSchema.UsageType.FOREIGN_KEY)) {
138 Iterator<JdbcSchema.Table.Column.Usage> it =
139 column.getUsages(JdbcSchema.UsageType.FOREIGN_KEY);
140 it.hasNext();
141 usage = it.next();
142 } else {
143 usage = column.newUsage(JdbcSchema.UsageType.FOREIGN_KEY);
144 usage.setSymbolicName(JdbcSchema.UsageType.FOREIGN_KEY.name());
145 }
146 notLostColumnUsages.add(usage);
147 }
148
149 /**
150 * The columns are the RolapStar columns taking part in an aggregation
151 * request. This is what happens.
152 * First, for each column, walk up the column's table until one level below
153 * the base fact table. The left join condition contains the base fact table
154 * and the foreign key column name. This column should not be lost.
155 * Get the base fact table's measure columns.
156 * With a list of columns that should not be lost and measure, one can
157 * create lost create and insert commands.
158 */
159 private void init() {
160 JdbcSchema db = JdbcSchema.makeDB(star.getDataSource());
161 try {
162 db.load();
163 } catch (SQLException ex) {
164 getLogger().error(ex);
165 return;
166 }
167
168 JdbcSchema.Table factTable = getTable(db, getFactTableName());
169 if (factTable == null) {
170 StringBuilder buf = new StringBuilder(64);
171 buf.append("Init: ");
172 buf.append("No fact table with name \"");
173 buf.append(getFactTableName());
174 buf.append("\"");
175 getLogger().warn(buf.toString());
176 return;
177 }
178 try {
179 factTable.load();
180 } catch (SQLException ex) {
181 getLogger().error(ex);
182 return;
183 }
184
185 if (getLogger().isDebugEnabled()) {
186 StringBuilder buf = new StringBuilder(512);
187 buf.append("Init: ");
188 buf.append("RolapStar:");
189 buf.append(Util.nl);
190 buf.append(getFactTable());
191 buf.append(Util.nl);
192 buf.append("FactTable:");
193 buf.append(Util.nl);
194 buf.append(factTable);
195 getLogger().debug(buf.toString());
196 }
197
198 // do foreign keys
199 for (RolapStar.Column column : columns) {
200 if (getLogger().isDebugEnabled()) {
201 StringBuilder buf = new StringBuilder(64);
202 buf.append("Init: ");
203 buf.append("Column: ");
204 buf.append(column);
205 getLogger().debug(buf.toString());
206 }
207 RolapStar.Table table = column.getTable();
208
209 if (table.getParentTable() == null) {
210 // this is for those crazy dimensions which are in the
211 // fact table, you know, non-shared with no table element
212
213 // How the firetruck to enter information for the
214 // collapsed case. This column is in the base fact table
215 // and can be part of a dimension hierarchy but no where
216 // in the RolapStar is this hiearchy captured - ugg.
217 if (!addSpecialCollapsedColumn(db, column)) {
218 return;
219 }
220
221
222 MondrianDef.Expression expr = column.getExpression();
223 if (expr instanceof MondrianDef.Column) {
224 MondrianDef.Column exprColumn = (MondrianDef.Column) expr;
225 String name = exprColumn.getColumnName();
226 JdbcSchema.Table.Column c = getColumn(factTable, name);
227 if (c == null) {
228 StringBuilder buf = new StringBuilder(64);
229 buf.append("Init: ");
230 buf.append("FactTable:");
231 buf.append(getFactTableName());
232 buf.append(Util.nl);
233 buf.append("No Column with name \"");
234 buf.append(name);
235 buf.append("\"");
236 getLogger().warn(buf.toString());
237 return;
238 }
239 if (getLogger().isDebugEnabled()) {
240 getLogger().debug(" Jdbc Column: c=" + c);
241 }
242 addForeignKeyToNotLostColumnUsages(c);
243 }
244
245 } else {
246
247 if (!addCollapsedColumn(db, column)) {
248 return;
249 }
250
251 while (table.getParentTable().getParentTable() != null) {
252 table = table.getParentTable();
253 }
254 RolapStar.Condition cond = table.getJoinCondition();
255 if (getLogger().isDebugEnabled()) {
256 getLogger().debug(" RolapStar.Condition: cond=" + cond);
257 }
258 MondrianDef.Expression left = cond.getLeft();
259 if (left instanceof MondrianDef.Column) {
260 MondrianDef.Column leftColumn = (MondrianDef.Column) left;
261 String name = leftColumn.getColumnName();
262 JdbcSchema.Table.Column c = getColumn(factTable, name);
263 if (c == null) {
264 StringBuilder buf = new StringBuilder(64);
265 buf.append("Init: ");
266 buf.append("FactTable:");
267 buf.append(getFactTableName());
268 buf.append(Util.nl);
269 buf.append("No Column with name \"");
270 buf.append(name);
271 buf.append("\"");
272 getLogger().warn(buf.toString());
273 return;
274 }
275 if (getLogger().isDebugEnabled()) {
276 getLogger().debug(" Jdbc Column: c=" + c);
277 }
278 addForeignKeyToNotLostColumnUsages(c);
279 }
280 }
281 }
282
283 // do measures
284 for (RolapStar.Column rColumn : getFactTable().getColumns()) {
285 String name = getRolapStarColumnName(rColumn);
286 if (name == null) {
287 StringBuilder buf = new StringBuilder(64);
288 buf.append("Init: ");
289 buf.append("For fact table \"");
290 buf.append(getFactTableName());
291 buf.append(
292 "\", could not get column name for RolapStar.Column: ");
293 buf.append(rColumn);
294 getLogger().warn(buf.toString());
295 return;
296 }
297 if (!(rColumn instanceof RolapStar.Measure)) {
298 // TODO: whats the solution to this?
299 // its a funky dimension column in the fact table!!!
300 getLogger().warn("not a measure: " + name);
301 continue;
302 }
303 RolapStar.Measure rMeasure = (RolapStar.Measure) rColumn;
304 if (!rMeasure.getCubeName().equals(cubeName)) {
305 continue;
306 }
307 final RolapAggregator aggregator = rMeasure.getAggregator();
308 JdbcSchema.Table.Column c = getColumn(factTable, name);
309 if (c == null) {
310 StringBuilder buf = new StringBuilder(64);
311 buf.append("For RolapStar: \"");
312 buf.append(getFactTable().getAlias());
313 buf.append("\" measure with name, ");
314 buf.append(name);
315 buf.append(", is not a column name. ");
316 buf.append("The measure's column name may be an expression");
317 buf.append(" and currently AggGen does not handle expressions.");
318 buf.append(" You will have to add this measure to the");
319 buf.append(" aggregate table definition by hand.");
320 getLogger().warn(buf.toString());
321 continue;
322 }
323 if (getLogger().isDebugEnabled()) {
324 getLogger().debug(" Jdbc Column m=" + c);
325 }
326 /*
327 JdbcSchema.Table.Column.Usage usage =
328 c.newUsage(JdbcSchema.MEASURE_COLUMN_USAGE);
329 usage.setAggregator(aggregator);
330 usage.setSymbolicName(rColumn.getName());
331 measures.add(usage);
332 */
333
334 JdbcSchema.Table.Column.Usage usage = null;
335 if (c.hasUsage(JdbcSchema.UsageType.MEASURE)) {
336 for (Iterator<JdbcSchema.Table.Column.Usage> uit =
337 c.getUsages(JdbcSchema.UsageType.MEASURE);
338 uit.hasNext();) {
339 JdbcSchema.Table.Column.Usage tmpUsage = uit.next();
340 if ((tmpUsage.getAggregator() == aggregator) &&
341 tmpUsage.getSymbolicName().equals(rColumn.getName())) {
342 usage = tmpUsage;
343 break;
344 }
345 }
346 }
347 if (usage == null) {
348 usage = c.newUsage(JdbcSchema.UsageType.MEASURE);
349 usage.setAggregator(aggregator);
350 usage.setSymbolicName(rColumn.getName());
351 }
352 measures.add(usage);
353 }
354
355 // If we got to here, then everything is ok.
356 isReady = true;
357 }
358 private boolean addSpecialCollapsedColumn(final JdbcSchema db,
359 final RolapStar.Column rColumn) {
360 String rname = getRolapStarColumnName(rColumn);
361 if (rname == null) {
362 StringBuilder buf = new StringBuilder(64);
363 buf.append("Adding Special Collapsed Column: ");
364 buf.append("For fact table \"");
365 buf.append(getFactTableName());
366 buf.append("\", could not get column name for RolapStar.Column: ");
367 buf.append(rColumn);
368 getLogger().warn(buf.toString());
369 return false;
370 }
371 // this is in fact the fact table.
372 RolapStar.Table rt = rColumn.getTable();
373
374 JdbcSchema.Table jt = getTable(db, rt);
375 if (jt == null) {
376 StringBuilder buf = new StringBuilder(64);
377 buf.append("Adding Special Collapsed Column: ");
378 buf.append("For fact table \"");
379 buf.append(getFactTableName());
380 buf.append("\", could not get jdbc schema table ");
381 buf.append("for RolapStar.Table with alias \"");
382 buf.append(rt.getAlias());
383 buf.append("\"");
384 getLogger().warn(buf.toString());
385 return false;
386 }
387 try {
388 jt.load();
389 } catch (SQLException ex) {
390 getLogger().error(ex);
391 return false;
392 }
393
394 List<JdbcSchema.Table.Column.Usage> list = collapsedColumnUsages.get(rt);
395 if (list == null) {
396 list = new ArrayList<JdbcSchema.Table.Column.Usage>();
397 collapsedColumnUsages.put(rt, list);
398 }
399
400 JdbcSchema.Table.Column c = getColumn(jt, rname);
401 if (c == null) {
402 StringBuilder buf = new StringBuilder(64);
403 buf.append("Adding Special Collapsed Column: ");
404 buf.append("For fact table \"");
405 buf.append(getFactTableName());
406 buf.append("\", could not get jdbc schema column ");
407 buf.append("for RolapStar.Table with alias \"");
408 buf.append(rt.getAlias());
409 buf.append("\" and column name \"");
410 buf.append(rname);
411 buf.append("\"");
412 getLogger().warn(buf.toString());
413 return false;
414 }
415 // NOTE: this creates a new usage for the fact table
416 // I do not know if this is a problem is AggGen is run before
417 // Mondrian uses aggregate tables.
418 list.add(c.newUsage(JdbcSchema.UsageType.FOREIGN_KEY));
419
420 RolapStar.Column prColumn = rColumn;
421 while (prColumn.getParentColumn() != null) {
422 prColumn = prColumn.getParentColumn();
423 rname = getRolapStarColumnName(prColumn);
424 if (rname == null) {
425 StringBuilder buf = new StringBuilder(64);
426 buf.append("Adding Special Collapsed Column: ");
427 buf.append("For fact table \"");
428 buf.append(getFactTableName());
429 buf.append("\", could not get parent column name");
430 buf.append("for RolapStar.Column \"");
431 buf.append(prColumn);
432 buf.append("\" for RolapStar.Table with alias \"");
433 buf.append(rt.getAlias());
434 buf.append("\"");
435 getLogger().warn(buf.toString());
436 return false;
437 }
438 c = getColumn(jt, rname);
439 if (c == null) {
440 getLogger().warn("Can not find column: " + rname);
441 break;
442 }
443 // NOTE: this creates a new usage for the fact table
444 // I do not know if this is a problem is AggGen is run before
445 // Mondrian uses aggregate tables.
446 list.add(c.newUsage(JdbcSchema.UsageType.FOREIGN_KEY));
447 }
448
449 return true;
450 }
451
452 private boolean addCollapsedColumn(final JdbcSchema db,
453 final RolapStar.Column rColumn) {
454 // TODO: if column is "id" column, then there is no collapse
455 String rname = getRolapStarColumnName(rColumn);
456 if (rname == null) {
457 StringBuilder buf = new StringBuilder(64);
458 buf.append("Adding Collapsed Column: ");
459 buf.append("For fact table \"");
460 buf.append(getFactTableName());
461 buf.append("\", could not get column name for RolapStar.Column: ");
462 buf.append(rColumn);
463 getLogger().warn(buf.toString());
464 return false;
465 }
466
467 RolapStar.Table rt = rColumn.getTable();
468
469 JdbcSchema.Table jt = getTable(db, rt);
470 if (jt == null) {
471 StringBuilder buf = new StringBuilder(64);
472 buf.append("Adding Collapsed Column: ");
473 buf.append("For fact table \"");
474 buf.append(getFactTableName());
475 buf.append("\", could not get jdbc schema table ");
476 buf.append("for RolapStar.Table with alias \"");
477 buf.append(rt.getAlias());
478 buf.append("\"");
479 getLogger().warn(buf.toString());
480 return false;
481 }
482 try {
483 jt.load();
484 } catch (SQLException ex) {
485 getLogger().error(ex);
486 return false;
487 }
488
489 //CG guarantee the columns has been loaded before looking up them
490 try {
491 jt.load();
492 } catch (SQLException sqle) {
493 getLogger().error(sqle);
494 return false;
495 }
496
497 // if this is a dimension table, then walk down the levels until
498 // we hit the current column
499 List<JdbcSchema.Table.Column.Usage> list =
500 new ArrayList<JdbcSchema.Table.Column.Usage>();
501 for (RolapStar.Column rc : rt.getColumns()) {
502 // do not include name columns
503 if (rc.isNameColumn()) {
504 continue;
505 }
506 String name = getRolapStarColumnName(rc);
507 if (name == null) {
508 StringBuilder buf = new StringBuilder(64);
509 buf.append("Adding Collapsed Column: ");
510 buf.append("For fact table \"");
511 buf.append(getFactTableName());
512 buf.append("\", could not get column name");
513 buf.append(" for RolapStar.Column \"");
514 buf.append(rc);
515 buf.append("\" for RolapStar.Table with alias \"");
516 buf.append(rt.getAlias());
517 buf.append("\"");
518 getLogger().warn(buf.toString());
519 return false;
520 }
521 JdbcSchema.Table.Column c = getColumn(jt, name);
522 if (c == null) {
523 getLogger().warn("Can not find column: " + name);
524 break;
525 }
526
527 JdbcSchema.Table.Column.Usage usage =
528 c.newUsage(JdbcSchema.UsageType.FOREIGN_KEY);
529 usage.usagePrefix = rc.getUsagePrefix();
530
531 list.add(usage);
532
533 if (rname.equals(name)) {
534 break;
535 }
536 }
537 // may already be there so only enter if new list is bigger
538 List<JdbcSchema.Table.Column.Usage> l = collapsedColumnUsages.get(rt);
539 if ((l == null) || (l.size() < list.size())) {
540 collapsedColumnUsages.put(rt, list);
541 }
542
543 return true;
544 }
545
546 private static final String AGG_LOST_PREFIX = "agg_l_XXX_";
547
548 String makeLostAggregateTableName(String factTableName) {
549 StringBuilder buf = new StringBuilder(64);
550 buf.append(AGG_LOST_PREFIX);
551 buf.append(factTableName);
552 return buf.toString();
553 }
554
555 private static final String AGG_COLLAPSED_PREFIX = "agg_c_XXX_";
556
557 String makeCollapsedAggregateTableName(String factTableName) {
558 StringBuilder buf = new StringBuilder(64);
559 buf.append(AGG_COLLAPSED_PREFIX);
560 buf.append(factTableName);
561 return buf.toString();
562 }
563
564
565
566 /**
567 * Return a String containing the sql code to create a lost dimension
568 * table.
569 *
570 * @return lost dimension sql code
571 */
572 public String createLost() {
573 StringWriter sw = new StringWriter(512);
574 PrintWriter pw = new PrintWriter(sw);
575 String prefix = " ";
576
577 pw.print("CREATE TABLE ");
578 pw.print(makeLostAggregateTableName(getFactTableName()));
579 pw.println(" (");
580
581 // do foreign keys
582 for (JdbcSchema.Table.Column.Usage usage : notLostColumnUsages) {
583 addColumnCreate(pw, prefix, usage);
584 }
585
586 // do measures
587 for (JdbcSchema.Table.Column.Usage usage : measures) {
588 addColumnCreate(pw, prefix, usage);
589 }
590 // do fact_count
591 pw.print(prefix);
592 pw.print(getFactCount());
593 pw.println(" INTEGER NOT NULL");
594
595 pw.println(");");
596 return sw.toString();
597 }
598
599 /**
600 * Return the sql code to populate a lost dimension table from the fact
601 * table.
602 */
603 public String insertIntoLost() {
604 StringWriter sw = new StringWriter(512);
605 PrintWriter pw = new PrintWriter(sw);
606 String prefix = " ";
607 String factTableName = getFactTableName();
608 SqlQuery sqlQuery = getSqlQuery();
609
610 pw.print("INSERT INTO ");
611 pw.print(makeLostAggregateTableName(getFactTableName()));
612 pw.println(" (");
613
614 for (JdbcSchema.Table.Column.Usage usage : notLostColumnUsages) {
615 JdbcSchema.Table.Column c = usage.getColumn();
616
617 pw.print(prefix);
618 pw.print(c.getName());
619 pw.println(',');
620 }
621
622 for (JdbcSchema.Table.Column.Usage usage : measures) {
623 JdbcSchema.Table.Column c = usage.getColumn();
624
625 pw.print(prefix);
626 String name = getUsageName(usage);
627 pw.print(name);
628 pw.println(',');
629 }
630 // do fact_count
631 pw.print(prefix);
632 pw.print(getFactCount());
633 pw.println(")");
634
635 pw.println("SELECT");
636 for (JdbcSchema.Table.Column.Usage usage : notLostColumnUsages) {
637 JdbcSchema.Table.Column c = usage.getColumn();
638
639 pw.print(prefix);
640 pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName,
641 c.getName()));
642 pw.print(" AS ");
643 pw.print(sqlQuery.getDialect().quoteIdentifier(c.getName()));
644 pw.println(',');
645 }
646 for (JdbcSchema.Table.Column.Usage usage : measures) {
647 JdbcSchema.Table.Column c = usage.getColumn();
648 RolapAggregator agg = usage.getAggregator();
649
650 pw.print(prefix);
651 pw.print(
652 agg.getExpression(sqlQuery.getDialect().quoteIdentifier(
653 factTableName, c.getName())));
654 pw.print(" AS ");
655 pw.print(sqlQuery.getDialect().quoteIdentifier(c.getName()));
656 pw.println(',');
657 }
658
659 // do fact_count
660 pw.print(prefix);
661 pw.print("COUNT(*) AS ");
662 pw.println(sqlQuery.getDialect().quoteIdentifier(getFactCount()));
663
664 pw.println("FROM ");
665 pw.print(prefix);
666 pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName));
667 pw.print(" ");
668 pw.println(sqlQuery.getDialect().quoteIdentifier(factTableName));
669
670 pw.println("GROUP BY ");
671 int k = 0;
672 for (JdbcSchema.Table.Column.Usage notLostColumnUsage : notLostColumnUsages) {
673 if (k++ > 0) {
674 pw.println(",");
675 }
676 JdbcSchema.Table.Column.Usage usage = notLostColumnUsage;
677 JdbcSchema.Table.Column c = usage.getColumn();
678
679 pw.print(prefix);
680 pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName,
681 c.getName()));
682 }
683
684 pw.println(';');
685 return sw.toString();
686 }
687 /**
688 * Return a String containing the sql code to create a collapsed dimension
689 * table.
690 *
691 * @return collapsed dimension sql code
692 */
693 public String createCollapsed() {
694 StringWriter sw = new StringWriter(512);
695 PrintWriter pw = new PrintWriter(sw);
696 String prefix = " ";
697
698 pw.print("CREATE TABLE ");
699 pw.print(makeCollapsedAggregateTableName(getFactTableName()));
700 pw.println(" (");
701
702 // do foreign keys
703 for (List<JdbcSchema.Table.Column.Usage> list : collapsedColumnUsages.values()) {
704 for (JdbcSchema.Table.Column.Usage usage : list) {
705 addColumnCreate(pw, prefix, usage);
706 }
707 }
708
709 // do measures
710 for (JdbcSchema.Table.Column.Usage usage : measures) {
711 addColumnCreate(pw, prefix, usage);
712 }
713 // do fact_count
714 pw.print(prefix);
715 pw.print(getFactCount());
716 pw.println(" INTEGER NOT NULL");
717
718 pw.println(");");
719 return sw.toString();
720 }
721
722 /**
723 * Return the sql code to populate a collapsed dimension table from
724 * the fact table.
725 */
726 public String insertIntoCollapsed() {
727 StringWriter sw = new StringWriter(512);
728 PrintWriter pw = new PrintWriter(sw);
729 String prefix = " ";
730 String factTableName = getFactTableName();
731 SqlQuery sqlQuery = getSqlQuery();
732
733 pw.print("INSERT INTO ");
734 pw.print(makeCollapsedAggregateTableName(getFactTableName()));
735 pw.println(" (");
736
737
738 for (List<JdbcSchema.Table.Column.Usage> list : collapsedColumnUsages.values()) {
739 for (JdbcSchema.Table.Column.Usage usage : list) {
740 JdbcSchema.Table.Column c = usage.getColumn();
741 pw.print(prefix);
742 if (usage.usagePrefix != null) {
743 pw.print(usage.usagePrefix);
744 }
745 pw.print(c.getName());
746 pw.println(',');
747 }
748 }
749
750 for (JdbcSchema.Table.Column.Usage usage : measures) {
751 JdbcSchema.Table.Column c = usage.getColumn();
752
753 pw.print(prefix);
754 String name = getUsageName(usage);
755 pw.print(name);
756 //pw.print(c.getName());
757 pw.println(',');
758 }
759 // do fact_count
760 pw.print(prefix);
761 pw.print(getFactCount());
762 pw.println(")");
763
764 pw.println("SELECT");
765 for (List<JdbcSchema.Table.Column.Usage> list : collapsedColumnUsages.values()) {
766 for (JdbcSchema.Table.Column.Usage usage : list) {
767 JdbcSchema.Table.Column c = usage.getColumn();
768 JdbcSchema.Table t = c.getTable();
769
770 pw.print(prefix);
771 pw.print(sqlQuery.getDialect().quoteIdentifier(t.getName(),
772 c.getName()));
773 pw.print(" AS ");
774 String n = (usage.usagePrefix == null)
775 ? c.getName() : usage.usagePrefix + c.getName();
776 pw.print(sqlQuery.getDialect().quoteIdentifier(n));
777 pw.println(',');
778 }
779 }
780 for (JdbcSchema.Table.Column.Usage usage : measures) {
781 JdbcSchema.Table.Column c = usage.getColumn();
782 JdbcSchema.Table t = c.getTable();
783 RolapAggregator agg = usage.getAggregator();
784
785 pw.print(prefix);
786 pw.print(
787 agg.getExpression(sqlQuery.getDialect().quoteIdentifier(
788 t.getName(), c.getName())));
789 pw.print(" AS ");
790 pw.print(sqlQuery.getDialect().quoteIdentifier(c.getName()));
791 pw.println(',');
792 }
793
794 // do fact_count
795 pw.print(prefix);
796 pw.print("COUNT(*) AS ");
797 pw.println(sqlQuery.getDialect().quoteIdentifier(getFactCount()));
798
799 pw.println("FROM ");
800 pw.print(prefix);
801 pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName));
802 pw.print(" ");
803 pw.print(sqlQuery.getDialect().quoteIdentifier(factTableName));
804 pw.println(',');
805
806 // add dimension tables
807 int k = 0;
808 for (RolapStar.Table rt : collapsedColumnUsages.keySet()) {
809 if (k++ > 0) {
810 pw.println(',');
811 }
812 pw.print(prefix);
813 pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
814 pw.print(" AS ");
815 pw.print(sqlQuery.getDialect().quoteIdentifier(rt.getAlias()));
816
817 // walk up tables
818 if (rt.getParentTable() != null) {
819 while (rt.getParentTable().getParentTable() != null) {
820 rt = rt.getParentTable();
821
822 pw.println(',');
823
824 pw.print(prefix);
825 pw.print(sqlQuery
826 .getDialect().quoteIdentifier(rt.getAlias()));
827 pw.print(" AS ");
828 pw.print(sqlQuery
829 .getDialect().quoteIdentifier(rt.getAlias()));
830 }
831 }
832 }
833
834 pw.println();
835 pw.println("WHERE ");
836 k = 0;
837 for (RolapStar.Table rt : collapsedColumnUsages.keySet()) {
838 if (k++ > 0) {
839 pw.println(" and");
840 }
841
842 RolapStar.Condition cond = rt.getJoinCondition();
843 if (cond == null) {
844 continue;
845 }
846 pw.print(prefix);
847 pw.print(cond.toString(sqlQuery));
848
849 if (rt.getParentTable() != null) {
850 while (rt.getParentTable().getParentTable() != null) {
851 rt = rt.getParentTable();
852 cond = rt.getJoinCondition();
853
854 pw.println(" and");
855
856 pw.print(prefix);
857 pw.print(cond.toString(sqlQuery));
858 }
859 }
860 }
861
862 pw.println();
863 pw.println("GROUP BY ");
864 k = 0;
865 for (List<JdbcSchema.Table.Column.Usage> list : collapsedColumnUsages.values()) {
866 for (JdbcSchema.Table.Column.Usage usage : list) {
867 if (k++ > 0) {
868 pw.println(",");
869 }
870 JdbcSchema.Table.Column c = usage.getColumn();
871 JdbcSchema.Table t = c.getTable();
872
873 String n = (usage.usagePrefix == null)
874 ? c.getName() : usage.usagePrefix + c.getName();
875 pw.print(prefix);
876 pw.print(sqlQuery.getDialect().quoteIdentifier(t.getName(), n));
877 }
878 }
879 pw.println(';');
880
881 return sw.toString();
882 }
883
884
885
886 private String getUsageName(final JdbcSchema.Table.Column.Usage usage) {
887 JdbcSchema.Table.Column c = usage.getColumn();
888 String name = c.getName();
889 // if its a measure which is based upon a foreign key, then
890 // the foreign key column name is already used (for the foreign key
891 // column) so we must choose a different name.
892 if (usage.getUsageType() == JdbcSchema.UsageType.MEASURE) {
893 if (c.hasUsage(JdbcSchema.UsageType.FOREIGN_KEY)) {
894 name = usage.getSymbolicName().replace(' ', '_').toUpperCase();
895 }
896 }
897 return name;
898 }
899
900 private void addColumnCreate(final PrintWriter pw,
901 final String prefix,
902 final JdbcSchema.Table.Column.Usage usage) {
903 JdbcSchema.Table.Column c = usage.getColumn();
904 String name = getUsageName(usage);
905
906 pw.print(prefix);
907 if (usage.usagePrefix != null) {
908 pw.print(usage.usagePrefix);
909 }
910 pw.print(name);
911 pw.print(' ');
912 pw.print(c.getTypeName().toUpperCase());
913 switch (c.getType()) {
914 case Types.NUMERIC:
915 case Types.DECIMAL:
916 pw.print('(');
917 pw.print(c.getNumPrecRadix());
918 pw.print(",");
919 pw.print(c.getDecimalDigits());
920 pw.print(')');
921 break;
922 case Types.CHAR:
923 case Types.VARCHAR:
924 pw.print('(');
925 pw.print(c.getCharOctetLength());
926 pw.print(')');
927 break;
928 default:
929 }
930 if (! c.isNullable()) {
931 pw.print(" NOT NULL");
932 }
933 pw.println(',');
934
935 }
936 }
937
938 // End AggGen.java