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