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