org.pentaho.metadata.model.concept.types.AggregationType Java Examples

protected AggType convertNewThinAggregationType( AggregationType aggregationType ) {
  if ( aggregationType == null ) {
    return AggType.NONE;
  }

  switch ( aggregationType ) {
    case COUNT:
      return AggType.COUNT;
    case COUNT_DISTINCT:
      return AggType.COUNT_DISTINCT;
    case AVERAGE:
      return AggType.AVERAGE;
    case MINIMUM:
      return AggType.MIN;
    case MAXIMUM:
      return AggType.MAX;
    case SUM:
      return AggType.SUM;
    case NONE:
    default:
      return AggType.NONE;
  }
}
 

/**
 * In this test we try to see to it :<br>
 * - that the formula engine picks the 2 specified columns from 2 different business tables<br>
 * - that we calculate the sum of the multiplication <br>
 */
@Test
public void testMultiTableColumnFormulasAggregate() throws Exception {
  LogicalColumn quantityOrdered = getOrdersModel().findLogicalColumn( "BC_ORDER_DETAILS_QUANTITYORDERED" );
  Assert.assertNotNull( "Expected to find the business column 'quantity ordered'", quantityOrdered );
  LogicalColumn buyPrice = getOrdersModel().findLogicalColumn( "BC_PRODUCTS_BUYPRICE" );
  Assert.assertNotNull( "Expected to find the business column 'buy price'", buyPrice );

  // let's remove the aggregations of the quantity ordered...
  //
  AggregationType qaBackup = quantityOrdered.getAggregationType();
  AggregationType paBackup = buyPrice.getAggregationType();
  quantityOrdered.setAggregationType( AggregationType.NONE );
  buyPrice.setAggregationType( AggregationType.NONE );

  // This changes the expected result...
  //
  String formula = "SUM( [BT_ORDER_DETAILS.BC_ORDER_DETAILS_QUANTITYORDERED] * [BT_PRODUCTS.BC_PRODUCTS_BUYPRICE] )";
  String sql = "SUM( BT_ORDER_DETAILS.QUANTITYORDERED  *  BT_PRODUCTS.BUYPRICE )";

  handleFormula( getOrdersModel(), "Hypersonic", formula, sql );

  // Set it back to the way it was for further testing.
  quantityOrdered.setAggregationType( qaBackup );
  buyPrice.setAggregationType( paBackup );
}
 

private AggregationType getAggregationType( AggType type ) {
  if ( type == null ) {
    return null;
  }
  switch ( type ) {
    case COUNT:
      return AggregationType.COUNT;
    case COUNT_DISTINCT:
      return AggregationType.COUNT_DISTINCT;
    case AVERAGE:
      return AggregationType.AVERAGE;
    case MAX:
      return AggregationType.MAXIMUM;
    case MIN:
      return AggregationType.MINIMUM;
    case SUM:
      return AggregationType.SUM;
    default:
      return AggregationType.NONE;
  }
}
 

/**
 * In this test we try to test :<br>
 * - if the formula engine picks the 2 specified columns from 2 different business tables<br>
 * - if we calculate the multiplication of the sums <br>
 */
@Test
public void testMultiTableColumnFormulasAggregate2() throws Exception {
  LogicalColumn quantityOrdered = getOrdersModel().findLogicalColumn( "BC_ORDER_DETAILS_QUANTITYORDERED" );
  Assert.assertNotNull( "Expected to find the business column 'quantity ordered'", quantityOrdered );
  LogicalColumn buyPrice = getOrdersModel().findLogicalColumn( "BC_PRODUCTS_BUYPRICE" );
  Assert.assertNotNull( "Expected to find the business column 'buy price'", buyPrice );

  // let's enable the aggregations of the quantity ordered...
  //
  AggregationType qaBackup = quantityOrdered.getAggregationType();
  AggregationType paBackup = buyPrice.getAggregationType();
  quantityOrdered.setAggregationType( AggregationType.SUM );
  buyPrice.setAggregationType( AggregationType.SUM );

  // This changes the expected result...
  //
  String formula = "[BT_ORDER_DETAILS.BC_ORDER_DETAILS_QUANTITYORDERED] * [BT_PRODUCTS.BC_PRODUCTS_BUYPRICE]";
  String sql = "SUM(BT_ORDER_DETAILS.QUANTITYORDERED)  *  SUM(BT_PRODUCTS.BUYPRICE)";

  handleFormula( getOrdersModel(), "Hypersonic", formula, sql );

  // Set it back to the way it was for further testing.
  quantityOrdered.setAggregationType( qaBackup );
  buyPrice.setAggregationType( paBackup );
}
 

private AggregationType getAggregationType( AggType type ) {
  if ( type == null ) {
    return null;
  }
  switch ( type ) {
    case COUNT:
      return AggregationType.COUNT;
    case COUNT_DISTINCT:
      return AggregationType.COUNT_DISTINCT;
    case AVERAGE:
      return AggregationType.AVERAGE;
    case MAX:
      return AggregationType.MAXIMUM;
    case MIN:
      return AggregationType.MINIMUM;
    case SUM:
      return AggregationType.SUM;
    default:
      return AggregationType.NONE;
  }
}
 

protected void addOrderBy( Query query, Category category, String columnId, String aggregation, Order.Type orderType )
  throws PentahoMetadataException {

  if ( category == null ) {
    throw new PentahoMetadataException( Messages.getErrorString( "QueryXmlHelper.ERROR_0016_BUSINESS_CATEGORY_NULL" ) ); //$NON-NLS-1$ 
  }

  LogicalColumn column = category.findLogicalColumn( columnId );
  if ( column == null ) {
    throw new PentahoMetadataException( Messages.getErrorString(
        "QueryXmlHelper.ERROR_0013_BUSINESS_COL_NOT_FOUND", category.getId(), columnId ) ); //$NON-NLS-1$ 
  }

  // this code verifies the aggregation setting provided is a
  // valid option
  AggregationType aggsetting = null;
  if ( aggregation != null ) {
    AggregationType setting = AggregationType.valueOf( aggregation.toUpperCase() );
    if ( ( column.getAggregationType() == setting ) || column.getAggregationList() != null
        && column.getAggregationList().contains( setting ) ) {
      aggsetting = setting;
    }
  }

  query.getOrders().add( new Order( new Selection( category, column, aggsetting ), orderType ) );
}
 

private int convertAggType( AggregationType type ) {
  switch ( type ) {
    case NONE:
      return GroupByMeta.TYPE_GROUP_NONE;
    case AVERAGE:
      return GroupByMeta.TYPE_GROUP_AVERAGE;
    case SUM:
      return GroupByMeta.TYPE_GROUP_SUM;
    case COUNT:
      return GroupByMeta.TYPE_GROUP_COUNT_ALL;
    case COUNT_DISTINCT:
      return GroupByMeta.TYPE_GROUP_COUNT_DISTINCT;
    case MINIMUM:
      return GroupByMeta.TYPE_GROUP_MIN;
    case MAXIMUM:
      return GroupByMeta.TYPE_GROUP_MAX;
    default:
      return GroupByMeta.TYPE_GROUP_NONE;
  }
}
 

public IMetadataDomainRepository getMetadataDomainRepository( final String domainId,
                                                              final ResourceManager resourceManager,
                                                              final ResourceKey contextKey,
                                                              final String xmiFile )
  throws ReportDataFactoryException {
  try {
    final InputStream stream = createStream( resourceManager, contextKey, xmiFile );
    try {
      final InMemoryMetadataDomainRepository repo = new InMemoryMetadataDomainRepository();
      final XmiParser parser = new XmiParser();
      final Domain domain = parser.parseXmi( stream );
      // add a couple of agg types to the quantity ordered physical column
      final IPhysicalTable table =
        ( (SqlPhysicalModel) domain.getPhysicalModels().get( 0 ) ).getPhysicalTables().get( 7 );
      final IPhysicalColumn col = table.getPhysicalColumns().get( 3 );
      final List<AggregationType> list = new ArrayList<AggregationType>();
      list.add( AggregationType.SUM );
      list.add( AggregationType.AVERAGE );
      col.setAggregationList( list );
      domain.setId( domainId );
      repo.storeDomain( domain, true );
      return repo;
    } finally {
      stream.close();
    }
  } catch ( final Exception e ) {
    throw new ReportDataFactoryException( "The Specified XMI File is invalid: " + xmiFile, e );
  }
}
 

public static String convertToMondrian( AggregationType aggregationType ) {
  String typeDesc = null;
  switch ( aggregationType ) {
    case NONE:
      typeDesc = "none"; //$NON-NLS-1$
      break;
    case SUM:
      typeDesc = "sum"; //$NON-NLS-1$
      break;
    case AVERAGE:
      typeDesc = "avg"; //$NON-NLS-1$
      break;
    case COUNT:
      typeDesc = "count"; //$NON-NLS-1$
      break;
    case COUNT_DISTINCT:
      typeDesc = "distinct count"; //$NON-NLS-1$
      break;
    case MINIMUM:
      typeDesc = "min"; //$NON-NLS-1$
      break;
    case MAXIMUM:
      typeDesc = "max"; //$NON-NLS-1$
      break;
  }
  return typeDesc;
}
 

public PropertyTypeRegistry() {
  // load these from a spring config file?
  addPropertyType( String.class );
  addPropertyType( LocalizedString.class );
  addPropertyType( DataType.class );
  addPropertyType( TargetTableType.class );
  addPropertyType( TargetColumnType.class );
  addPropertyType( AggregationType.class );

  // this is for agg lists,
  // we will need another way to express lists
  addPropertyType( List.class );

  addPropertyType( Alignment.class );
  addPropertyType( Color.class );
  addPropertyType( ColumnWidth.class );
  addPropertyType( FieldType.class );
  addPropertyType( Font.class );
  addPropertyType( TableType.class );
  addPropertyType( RowLevelSecurity.class );
  addPropertyType( Security.class );
  addPropertyType( Double.class );
  addPropertyType( Boolean.class );
  addPropertyType( Date.class );

  // note that URL is not GWT compatible, we'll need to figure out what to do with this
  // once we move towards a fully supported thin client metadata editor
  addPropertyType( URL.class );

}
 

@Test
public void testQueryExecutionWithAggregationsAndConstraints() throws Exception {

  List<String> users = new ArrayList<String>();
  users.add( "suzy" );
  List<String> roles = new ArrayList<String>();
  roles.add( "Authenticated" );
  int defaultAcls = 31;
  InlineEtlModelGenerator gen =
      new InlineEtlModelGenerator( "testmodel", csvFilesPath, "example.csv", true,
          ",", "\"", true, users, roles, defaultAcls, "joe" );

  Domain domain = gen.generate();

  LogicalModel model = domain.getLogicalModels().get( 0 );
  Category category = model.getCategories().get( 0 );
  category.getLogicalColumns().get( 1 ).setDataType( DataType.NUMERIC );
  category.getLogicalColumns().get( 1 ).setAggregationType( AggregationType.SUM );
  Query query = new Query( domain, model );

  query.getSelections().add( new Selection( category, category.getLogicalColumns().get( 3 ), null ) );
  query.getSelections().add( new Selection( category, category.getLogicalColumns().get( 1 ), null ) );
  query.getConstraints().add( new Constraint( CombinationType.AND, "[bc_testmodel.bc_1_Data2] > 4.0" ) );
  query.getOrders().add(
      new Order( new Selection( category, category.getLogicalColumns().get( 3 ), null ), Order.Type.DESC ) );

  InlineEtlQueryExecutor executor = new InlineEtlQueryExecutor();
  IPentahoResultSet resultset = executor.executeQuery( query, csvFilesPath, null );

  Assert.assertEquals( 2, resultset.getRowCount() );
  Assert.assertEquals( 2, resultset.getColumnCount() );
  Assert.assertEquals( "bc_3_Data4", resultset.getMetaData().getColumnHeaders()[0][0] );
  Assert.assertEquals( "bc_1_Data2", resultset.getMetaData().getColumnHeaders()[0][1] );

  Assert.assertEquals( "String Value", resultset.getValueAt( 0, 0 ) );
  Assert.assertEquals( "Bigger String Value", resultset.getValueAt( 1, 0 ) );

  Assert.assertEquals( 19.5, resultset.getValueAt( 0, 1 ) );
  Assert.assertEquals( 5.7, resultset.getValueAt( 1, 1 ) );
}
 

private LogicalTable createLogicalTable( String tblId, LogicalModel model, Category mainCat ) throws Exception {

    LogicalTable rtn = new LogicalTable();
    rtn.setId( "bt_" + tblId );
    rtn.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt_" + tblId ); //$NON-NLS-1$
    createBusinessKeyColumn( tblId, "keya", rtn, mainCat ); // 0
    createBusinessKeyColumn( tblId, "keyb", rtn, mainCat ); // 1
    createBusinessKeyColumn( tblId, "keyc", rtn, mainCat ); // 2
    createBusinessKeyColumn( tblId, "keyd", rtn, mainCat ); // 3
    createBusinessKeyColumn( tblId, "keye", rtn, mainCat ); // 4
    createBusinessKeyColumn( tblId, "keyf", rtn, mainCat ); // 5
    createBusinessKeyColumn( tblId, "keyg", rtn, mainCat ); // 6
    createBusinessKeyColumn( tblId, "keyh", rtn, mainCat ); // 7
    createBusinessKeyColumn( tblId, "keyi", rtn, mainCat ); // 8
    createBusinessKeyColumn( tblId, "keyj", rtn, mainCat ); // 9
    createBusinessKeyColumn( tblId, "keyk", rtn, mainCat ); // 10
    createBusinessKeyColumn( tblId, "keyl", rtn, mainCat ); // 11

    LogicalColumn bcs1 = new LogicalColumn();
    bcs1.setId( "bcs_" + tblId );
    bcs1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc_" + tblId ); //$NON-NLS-1$
    bcs1.setProperty( IPhysicalColumn.AGGREGATIONTYPE_PROPERTY, AggregationType.SUM );
    bcs1.setLogicalTable( rtn );
    rtn.addLogicalColumn( bcs1 );
    mainCat.addLogicalColumn( bcs1 );
    model.addLogicalTable( rtn );
    return rtn;

  }
 

/**
 * @param value
 * @param type
 * @return
 */
public Object getValue( final Object value, final Class type, final DataAttributeContext context ) {
  if ( value == null ) {
    return null;
  }

  if ( value instanceof AggregationType == false ) {
    return null;
  }

  if ( AggregationType.class.equals( type ) ) {
    return value;
  }

  if ( type == null || Object.class.equals( type ) ) {
    return value;
  }

  if ( AggregationType.NONE.equals( value ) ) {
    return "none";
  } else if ( AggregationType.SUM.equals( value ) ) {
    return "sum";
  } else if ( AggregationType.AVERAGE.equals( value ) ) {
    return "average";
  } else if ( AggregationType.COUNT.equals( value ) ) {
    return "count";
  } else if ( AggregationType.COUNT_DISTINCT.equals( value ) ) {
    return "count_distinct";
  } else if ( AggregationType.MINIMUM.equals( value ) ) {
    return "minimum";
  } else if ( AggregationType.MAXIMUM.equals( value ) ) {
    return "maximum";
  } else {
    return null;
  }

}
 

public AggregationType getActiveAggregationType() {
  if ( getAggregationType() == null ) {
    AggregationType aggType = logicalColumn.getAggregationType();
    if ( aggType == null ) {
      return AggregationType.NONE;
    } else {
      return aggType;
    }
  } else {
    return getAggregationType();
  }
}
 

@Test
public void testXmiParser() throws Exception {
  Domain domain = parser.parseXmi( getClass().getResourceAsStream( "/samples/steelwheels.xmi" ) );
  assertEquals( 6, domain.getConcepts().size() );
  assertEquals( 1, domain.getPhysicalModels().size() );
  assertEquals( 3, domain.getLogicalModels().size() );

  assertEquals( 2, domain.getLogicalModels().get( 0 ).getLogicalTables().size() );
  assertEquals( 8, domain.getLogicalModels().get( 0 ).getLogicalTables().get( 0 ).getLogicalColumns().size() );
  assertEquals( "BC_EMPLOYEES_EMPLOYEENUMBER", domain.getLogicalModels().get( 0 ).getLogicalTables().get( 0 )
      .getLogicalColumns().get( 0 ).getId() );
  assertEquals( 1, domain.getLogicalModels().get( 0 ).getLogicalRelationships().size() );

  assertEquals( "EMPLOYEENUMBER", domain.getLogicalModels().get( 0 ).getLogicalTables().get( 0 )
      .getLogicalColumns().get( 0 ).getPhysicalColumn().getId() );
  assertEquals( "PT_EMPLOYEES", domain.getLogicalModels().get( 0 ).getLogicalTables().get( 0 )
      .getLogicalColumns().get( 0 ).getPhysicalColumn().getPhysicalTable().getId() );
  assertNotNull( domain.getLogicalModels().get( 0 ).getLogicalTables().get( 0 ).getLogicalColumns().get( 0 )
      .getPhysicalColumn().getPhysicalTable().getPhysicalModel() );

  assertEquals( 2, domain.getLogicalModels().get( 0 ).getCategories().size() );
  assertEquals( 9, domain.getLogicalModels().get( 0 ).getCategories().get( 0 ).getLogicalColumns().size() );
  assertEquals( "BC_OFFICES_TERRITORY", domain.getLogicalModels().get( 0 ).getCategories().get( 0 )
      .getLogicalColumns().get( 0 ).getId() );
  assertEquals( "TERRITORY", domain.getLogicalModels().get( 0 ).getCategories().get( 0 ).getLogicalColumns()
      .get( 0 ).getPhysicalColumn().getId() );
  assertEquals( "PT_OFFICES", domain.getLogicalModels().get( 0 ).getCategories().get( 0 ).getLogicalColumns()
      .get( 0 ).getPhysicalColumn().getPhysicalTable().getId() );

  @SuppressWarnings( "unchecked" )
  List<AggregationType> aggTypes =
      (List<AggregationType>) domain.findLogicalModel( "BV_ORDERS" ).findCategory( "CAT_ORDERS" ).findLogicalColumn(
          "BC_ORDERS_ORDERNUMBER" ).getProperty( "aggregation_list" );
  assertNotNull( aggTypes );
  assertEquals( 2, aggTypes.size() );
  assertEquals( aggTypes.get( 0 ), AggregationType.COUNT );
  assertEquals( aggTypes.get( 1 ), AggregationType.COUNT_DISTINCT );

  // verify that inheritance is working
  assertEquals( "$#,##0.00;($#,##0.00)", domain.findLogicalModel( "BV_ORDERS" ).findCategory( "CAT_ORDERS" )
      .findLogicalColumn( "BC_ORDERDETAILS_TOTAL" ).getProperty( "mask" ) );

}
 

@Test
public void convertNewThinAggregationTypeTest() {
  MQLEditorServiceDelegate mqlESD = new MQLEditorServiceDelegate();

  Assert.assertEquals( mqlESD.convertNewThinAggregationType( null ), AggType.NONE );

  Assert.assertEquals( mqlESD.convertNewThinAggregationType( AggregationType.COUNT ), AggType.COUNT );

  Assert.assertEquals( mqlESD.convertNewThinAggregationType( AggregationType.COUNT_DISTINCT ), AggType.COUNT_DISTINCT );

  Assert.assertEquals( mqlESD.convertNewThinAggregationType( AggregationType.AVERAGE ), AggType.AVERAGE );

  Assert.assertEquals( mqlESD.convertNewThinAggregationType( AggregationType.MINIMUM ), AggType.MIN );

  Assert.assertEquals( mqlESD.convertNewThinAggregationType( AggregationType.MAXIMUM ), AggType.MAX );

  Assert.assertEquals( mqlESD.convertNewThinAggregationType( AggregationType.SUM ), AggType.SUM );

  Assert.assertEquals( mqlESD.convertNewThinAggregationType( AggregationType.NONE ), AggType.NONE );
}
 

public static AggregationSettings convertToLegacy( AggregationType aggType ) {
  if ( aggType == null ) {
    return null;
  }
  return AggregationSettings.types[aggType.ordinal()];
}
 

/**
 * Scenario: we have 2 sums and we want to calculate a ratio.<br>
 * The aggregation on the ratio is obviously "none".<br>
 * However, the generator has to keep in mind that it still needs to generate a group by.<br>
 * <br>
 * This is a simple one-table example.<br>
 * 
 */
@Test
public void testRatioOfSumsGroupBy() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$NON-NLS-1$
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$

  // dimension column d1
  //
  final LogicalColumn d1 = new LogicalColumn();
  d1.setId( "d1" ); //$NON-NLS-1$
  d1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "d" ); //$NON-NLS-1$
  d1.setLogicalTable( bt1 );
  d1.setAggregationType( AggregationType.NONE );
  d1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.DIMENSION );

  bt1.addLogicalColumn( d1 );
  mainCat.addLogicalColumn( d1 );

  // Sum column bc1
  //
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "a" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bc1.setAggregationType( AggregationType.SUM );
  bc1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( bc1 );
  mainCat.addLogicalColumn( bc1 );

  // Sum column bc2
  //
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "b" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt1 );
  bc2.setAggregationType( AggregationType.SUM );
  bc2.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  // A calculated column: ratio
  //
  final LogicalColumn ratio = new LogicalColumn();
  ratio.setId( "ratio" ); //$NON-NLS-1$
  ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "[bt1.bc1] / [bt1.bc2]" ); //$NON-NLS-1$
  ratio.setLogicalTable( bt1 );
  ratio.setAggregationType( AggregationType.NONE );
  ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.OPEN_FORMULA );
  ratio.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( ratio );
  mainCat.addLogicalColumn( ratio );

  DatabaseMeta databaseMeta = TestHelper.createOracleDatabaseMeta();
  Query myTest = new Query( null, model ); //$NON-NLS-1$
  myTest.getSelections().add( new Selection( null, d1, null ) );
  myTest.getSelections().add( new Selection( null, ratio, null ) );

  MappedQuery query = new SqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper.assertEqualsIgnoreWhitespaces(
      "SELECT bt1.d AS COL0 , SUM(bt1.a) / SUM(bt1.b) AS COL1 FROM t1 bt1 GROUP BY bt1.d", //$NON-NLS-1$
      query.getQuery() );
}
 

/**
 * Scenario: we want to make a sum of a ratio<br>
 * The aggregation on the ratio is obviously "SUM".<br>
 * However, the aggregation on the used columns is none.<br>
 * <br>
 * This is a simple one-table example.<br>
 * 
 */
@Test
public void testSumOfRatioGroupBy() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$NON-NLS-1$
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "t1" ); //$NON-NLS-1$

  // dimension column d1
  //
  final LogicalColumn d1 = new LogicalColumn();
  d1.setId( "d1" ); //$NON-NLS-1$
  d1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "d" ); //$NON-NLS-1$
  d1.setLogicalTable( bt1 );
  d1.setAggregationType( AggregationType.NONE );
  d1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.DIMENSION );

  bt1.addLogicalColumn( d1 );
  mainCat.addLogicalColumn( d1 );

  // Sum column bc1
  //
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "a" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bc1.setAggregationType( AggregationType.NONE );
  bc1.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( bc1 );
  mainCat.addLogicalColumn( bc1 );

  // Sum column bc2
  //
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "b" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt1 );
  bc2.setAggregationType( AggregationType.NONE );
  bc2.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  // A calculated column: ratio
  //
  final LogicalColumn ratio = new LogicalColumn();
  ratio.setId( "ratio" ); //$NON-NLS-1$
  ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "SUM( [bt1.bc1] / [bt1.bc2] )" ); //$NON-NLS-1$
  ratio.setLogicalTable( bt1 );
  ratio.setAggregationType( AggregationType.SUM );
  ratio.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.OPEN_FORMULA );
  ratio.setProperty( IPhysicalColumn.FIELDTYPE_PROPERTY, FieldType.FACT );

  bt1.addLogicalColumn( ratio );
  mainCat.addLogicalColumn( ratio );

  DatabaseMeta databaseMeta = TestHelper.createOracleDatabaseMeta();
  Query myTest = new Query( null, model ); //$NON-NLS-1$
  myTest.getSelections().add( new Selection( null, d1, null ) );
  myTest.getSelections().add( new Selection( null, ratio, null ) );

  MappedQuery query = new SqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper.assertEqualsIgnoreWhitespaces(
      "SELECT bt1.d AS COL0 , SUM( bt1.a / bt1.b ) AS COL1 FROM t1 bt1 GROUP BY bt1.d", //$NON-NLS-1$
      query.getQuery() );
}
 

public LogicalModel getDefaultModel() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" );
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" );

  LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" );
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" );
  bc1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bc1 );
  mainCat.addLogicalColumn( bc1 );

  LogicalColumn bcs1 = new LogicalColumn();
  bcs1.setId( "bcs1" );
  bcs1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" );
  bcs1.setAggregationType( AggregationType.SUM );
  bcs1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bcs1 );
  mainCat.addLogicalColumn( bcs1 );

  final LogicalTable bt2 = new LogicalTable();
  bt2.setId( "bt2" );
  bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" );

  LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" );
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" );
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalColumn bce2 = new LogicalColumn();
  bce2.setId( "bce2" ); //$NON-NLS-1$
  bce2.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.OPEN_FORMULA );
  bce2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "[bt2.bc2] * 2" ); //$NON-NLS-1$
  bce2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bce2 );
  mainCat.addLogicalColumn( bce2 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" );
  bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt3" );

  LogicalColumn bc3 = new LogicalColumn();
  bc3.setId( "bc3" );
  bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc3" );
  bc3.setLogicalTable( bt3 );
  bt3.addLogicalColumn( bc3 );
  mainCat.addLogicalColumn( bc3 );

  final LogicalRelationship rl1 = new LogicalRelationship();

  rl1.setFromTable( bt1 );
  rl1.setToTable( bt2 );
  rl1.setFromColumn( bc1 );
  rl1.setToColumn( bc2 );

  final LogicalRelationship rl2 = new LogicalRelationship();

  rl2.setToTable( bt2 );
  rl2.setFromTable( bt3 );
  rl2.setFromColumn( bc3 );
  rl2.setToColumn( bc2 );

  model.getLogicalTables().add( bt1 );
  model.getLogicalTables().add( bt2 );
  model.getLogicalTables().add( bt3 );
  model.getLogicalRelationships().add( rl1 );
  model.getLogicalRelationships().add( rl2 );

  return model;
}
 

/**
 * Scenario 1b: Two Tables are outer joined with an aggregate
 */
@Test
public void testOuterJoinScenario1b() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$NON-NLS-1$
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" ); //$NON-NLS-1$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bc1 );
  bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
  mainCat.addLogicalColumn( bc1 );

  final LogicalTable bt2 = new LogicalTable();
  bt2.setId( "bt2" ); //$NON-NLS-1$
  bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" ); //$NON-NLS-1$
  bc2.setAggregationType( AggregationType.SUM );
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalRelationship rl1 = new LogicalRelationship();
  rl1.setRelationshipType( RelationshipType._0_N );
  rl1.setFromTable( bt1 );
  rl1.setFromColumn( bc1 );
  rl1.setToTable( bt2 );
  rl1.setToColumn( bc2 );

  model.getLogicalRelationships().add( rl1 );

  DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
  Query myTest = new Query( null, model ); //$NON-NLS-1$
  myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
  myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT bt1.pc1 AS COL0 ,SUM(bt2.pc2) AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 ) GROUP BY bt1.pc1",
          query.getQuery() ); //$NON-NLS-1$
}
 

/**
 * Scenario 1c: Two Tables are outer joined with an aggregate constraint
 */
@Test
public void testOuterJoinScenario1c() throws Exception {
  final LogicalModel model = new LogicalModel();
  model.setId( "model_01" );
  Category mainCat = new Category();
  mainCat.setId( "cat_01" );
  model.getCategories().add( mainCat );

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$NON-NLS-1$
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt1" ); //$NON-NLS-1$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc1" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bc1 );
  bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
  mainCat.addLogicalColumn( bc1 );

  final LogicalTable bt2 = new LogicalTable();
  bt2.setId( "bt2" ); //$NON-NLS-1$
  bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc2" ); //$NON-NLS-1$
  bc2.setAggregationType( AggregationType.SUM );
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalRelationship rl1 = new LogicalRelationship();
  rl1.setRelationshipType( RelationshipType._0_N );
  rl1.setFromTable( bt1 );
  rl1.setFromColumn( bc1 );
  rl1.setToTable( bt2 );
  rl1.setToColumn( bc2 );

  model.getLogicalRelationships().add( rl1 );

  DatabaseMeta databaseMeta = new DatabaseMeta( "", "ORACLE", "Native", "", "", "", "", "" ); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
  Query myTest = new Query( null, model ); //$NON-NLS-1$
  myTest.getSelections().add( new AliasedSelection( null, bc1, null, null ) );
  myTest.getSelections().add( new AliasedSelection( null, bc2, null, null ) );
  myTest.getConstraints().add( new Constraint( CombinationType.AND, "[cat_01.bc2] > 1" ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT bt1.pc1 AS COL0 ,SUM(bt2.pc2) AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 ) GROUP BY bt1.pc1 HAVING ( SUM(bt2.pc2) > 1 )",
          query.getQuery() ); //$NON-NLS-1$
}
 

private Column createColumn( LogicalModel m, LogicalColumn c ) {
  Column col = new Column();
  col.setName( c.getName( locale ) );
  col.setId( c.getId() );

  ColumnType ourType = null;
  if ( c.getDataType() != null ) {
    int type = c.getDataType().getType();
    switch ( type ) {
      case DataTypeSettings.DATA_TYPE_BOOLEAN:
        ourType = ColumnType.BOOLEAN;
        break;
      case DataTypeSettings.DATA_TYPE_STRING:
        ourType = ColumnType.TEXT;
        break;
      case DataTypeSettings.DATA_TYPE_NUMERIC:
        ourType = ColumnType.FLOAT;
        break;
      case DataTypeSettings.DATA_TYPE_DATE:
        ourType = ColumnType.DATE;
        break;
    }
  }
  col.setType( ourType );
  List<AggregationType> possibleAggs = c.getAggregationList();
  if ( possibleAggs != null ) {
    for ( AggregationType agg : possibleAggs ) {
      col.getAggTypes().add( getAggType( agg.ordinal() ) );
    }
  }

  // There might be a default agg, but no agg list. If so, add it to the list.

  AggType defaultAggType = null;
  if ( c.getAggregationType() != null ) {
    defaultAggType = getAggType( c.getAggregationType().ordinal() );
  } else {
    defaultAggType = AggType.NONE;
  }
  if ( col.getAggTypes().contains( defaultAggType ) == false ) {
    col.getAggTypes().add( defaultAggType );
  }
  col.setDefaultAggType( defaultAggType );
  col.setSelectedAggType( defaultAggType );

  return col;
}
 

public MqlQuery convertModelToThin( org.pentaho.metadata.query.model.Query query ) {
  Query q = new Query();

  String domainId = query.getDomain().getId();
  String modelId = query.getLogicalModel().getId();

  MqlDomain selectedDomain = null;
  MqlModel selectedModel = null;
  MqlDomain storedDomain = domains.get( domainId );
  if ( storedDomain != null ) {
    selectedDomain = storedDomain;
  }
  if ( selectedDomain == null ) {
    throw new IllegalStateException( "Could not find domain: " + domainId );
  }
  q.setDomain( (Domain) selectedDomain );
  for ( MqlModel m : selectedDomain.getModels() ) {
    if ( m.getId().equals( modelId ) ) {
      selectedModel = m;
      break;
    }
  }
  if ( selectedModel == null ) {
    throw new IllegalStateException( "Could not find model: " + modelId );
  }
  q.setModel( (Model) selectedModel );
  List<Column> cols = q.getColumns();
  for ( org.pentaho.metadata.query.model.Selection sel : query.getSelections() ) {
    Column c = (Column) convertNewThinColumn( selectedModel, sel.getLogicalColumn().getId() );
    c.setSelectedAggType( convertNewThinAggregationType( sel.getAggregationType() ) );
    cols.add( c );
  }

  for ( org.pentaho.metadata.query.model.Constraint constraint : query.getConstraints() ) {
    FormulaParser fp = new FormulaParser( constraint.getFormula() );

    Condition cond = fp.getCondition();
    Outter: for ( MqlCategory cat : selectedModel.getCategories() ) {
      for ( MqlColumn col : cat.getBusinessColumns() ) {
        if ( col.getId().equals( fp.getColID() ) ) {
          cond.setColumn( (Column) col );
          break Outter;
        }
      }
    }

    // PRD-3710
    if ( fp.getAggType() != null ) {
      cond.setSelectedAggType( convertNewThinAggregationType( AggregationType.valueOf( fp.getAggType() ) ) );
    }

    cond.setCombinationType( CombinationType.valueOf( constraint.getCombinationType().name().toUpperCase() ) );
    String val = cond.getValue();

    // check to see if it was parameterized, if so resolve default and setup the condition properly
    if ( val.indexOf( "[param:" ) == 0 ) {
      String paramKey = val.substring( 7, val.length() - 1 );
      cond.setValue( "{" + paramKey + "}" );
      cond.setParameterized( true );
      for ( Parameter p : query.getParameters() ) {
        if ( p.getName().equals( paramKey ) ) {
          // convert arrays back to vertical bar (pipe) delimited string to support multi-valued defaults
          cond.setDefaultValue( getDisplayableDefaultValue( p ) );
          break;
        }
      }
    }
    q.getConditions().add( cond );
  }

  for ( org.pentaho.metadata.query.model.Order ord : query.getOrders() ) {
    Order o = new Order();
    o.setColumn( (Column) convertNewThinColumn( selectedModel, ord.getSelection().getLogicalColumn().getId() ) );
    o.setOrderType( MqlOrder.Type.valueOf( ord.getType().toString().toUpperCase() ) );
    q.getOrders().add( o );
  }

  q.setLimit( query.getLimit() );
  return q;
}
 

private Column createColumn( LogicalModel m, LogicalColumn c ) {
  Column col = new Column();
  col.setName( c.getName( getLocale() ) );
  col.setId( c.getId() );

  ColumnType ourType = null;
  if ( c.getDataType() != null ) {
    int type = c.getDataType().getType();
    switch ( type ) {
      case DataTypeSettings.DATA_TYPE_BOOLEAN:
        ourType = ColumnType.BOOLEAN;
        break;
      case DataTypeSettings.DATA_TYPE_STRING:
        ourType = ColumnType.TEXT;
        break;
      case DataTypeSettings.DATA_TYPE_NUMERIC:
        ourType = ColumnType.FLOAT;
        break;
      case DataTypeSettings.DATA_TYPE_DATE:
        ourType = ColumnType.DATE;
        break;
    }
  }
  col.setType( ourType );
  List<AggregationType> possibleAggs = c.getAggregationList();
  if ( possibleAggs != null ) {
    for ( AggregationType agg : possibleAggs ) {
      col.getAggTypes().add( getAggType( agg.ordinal() ) );
    }
  }

  // There might be a default agg, but no agg list. If so, add it to the list.

  AggType defaultAggType = null;
  if ( c.getAggregationType() != null ) {
    defaultAggType = getAggType( c.getAggregationType().ordinal() );
  } else {
    defaultAggType = AggType.NONE;
  }
  if ( col.getAggTypes().contains( defaultAggType ) == false ) {
    col.getAggTypes().add( defaultAggType );
  }
  col.setDefaultAggType( defaultAggType );
  col.setSelectedAggType( defaultAggType );

  return col;
}
 

@Test
public void testQueryExecutionWithAggregations() throws Exception {

  List<String> users = new ArrayList<String>();
  users.add( "suzy" );
  List<String> roles = new ArrayList<String>();
  roles.add( "Authenticated" );
  int defaultAcls = 31;
  InlineEtlModelGenerator gen =
      new InlineEtlModelGenerator( "testmodel", csvFilesPath, "example.csv", true,
          ",", "\"", true, users, roles, defaultAcls, "joe" );

  Domain domain = gen.generate();

  LogicalModel model = domain.getLogicalModels().get( 0 );
  Category category = model.getCategories().get( 0 );
  category.getLogicalColumns().get( 1 ).setDataType( DataType.NUMERIC );
  category.getLogicalColumns().get( 1 ).setAggregationType( AggregationType.SUM );
  Query query = new Query( domain, model );

  query.getSelections().add( new Selection( category, category.getLogicalColumns().get( 3 ), null ) );
  query.getSelections().add( new Selection( category, category.getLogicalColumns().get( 1 ), null ) );

  InlineEtlQueryExecutor executor = new InlineEtlQueryExecutor();
  IPentahoResultSet resultset = executor.executeQuery( query, csvFilesPath, null );

  Assert.assertEquals( 4, resultset.getRowCount() );
  Assert.assertEquals( 2, resultset.getColumnCount() );
  Assert.assertEquals( "bc_3_Data4", resultset.getMetaData().getColumnHeaders()[0][0] );
  Assert.assertEquals( "bc_1_Data2", resultset.getMetaData().getColumnHeaders()[0][1] );

  Assert.assertEquals( "A String", resultset.getValueAt( 0, 0 ) );
  Assert.assertEquals( "Bigger String Value", resultset.getValueAt( 1, 0 ) );
  Assert.assertEquals( "String Value", resultset.getValueAt( 2, 0 ) );
  Assert.assertEquals( "Very Long String Value for testing columns", resultset.getValueAt( 3, 0 ) );

  Assert.assertEquals( 1.1, resultset.getValueAt( 0, 1 ) );
  Assert.assertEquals( 5.7, resultset.getValueAt( 1, 1 ) );
  Assert.assertEquals( 19.5, resultset.getValueAt( 2, 1 ) );
  Assert.assertEquals( 3.4, resultset.getValueAt( 3, 1 ) );
}
 

/**
 * This test is ignored until PMD-532 is resolved
 * 
 */
@Test
public void testQueryExecutionWithDifferentAggregations() throws Exception {

  List<String> users = new ArrayList<String>();
  users.add( "suzy" );
  List<String> roles = new ArrayList<String>();
  roles.add( "Authenticated" );
  int defaultAcls = 31;
  InlineEtlModelGenerator gen =
      new InlineEtlModelGenerator( "testmodel", csvFilesPath, "example.csv", true,
          ",", "\"", true, users, roles, defaultAcls, "joe" );

  Domain domain = gen.generate();

  LogicalModel model = domain.getLogicalModels().get( 0 );
  Category category = model.getCategories().get( 0 );
  category.getLogicalColumns().get( 1 ).setDataType( DataType.NUMERIC );
  category.getLogicalColumns().get( 1 ).setAggregationType( AggregationType.SUM );
  Query query = new Query( domain, model );

  query.getSelections().add( new Selection( category, category.getLogicalColumns().get( 3 ), null ) );
  query.getSelections().add( new Selection( category, category.getLogicalColumns().get( 1 ), null ) );
  query.getSelections()
      .add( new Selection( category, category.getLogicalColumns().get( 1 ), AggregationType.AVERAGE ) );

  InlineEtlQueryExecutor executor = new InlineEtlQueryExecutor();
  IPentahoResultSet resultset = executor.executeQuery( query,
    csvFilesPath, null );

  Assert.assertEquals( 4, resultset.getRowCount() );
  Assert.assertEquals( 3, resultset.getColumnCount() );
  Assert.assertEquals( "bc_3_Data4", resultset.getMetaData().getColumnHeaders()[0][0] );
  Assert.assertEquals( "bc_1_Data2", resultset.getMetaData().getColumnHeaders()[0][1] );
  Assert.assertEquals( "bc_1_Data2_1", resultset.getMetaData().getColumnHeaders()[0][2] );

  Assert.assertEquals( "A String", resultset.getValueAt( 0, 0 ) );
  Assert.assertEquals( "Bigger String Value", resultset.getValueAt( 1, 0 ) );
  Assert.assertEquals( "String Value", resultset.getValueAt( 2, 0 ) );
  Assert.assertEquals( "Very Long String Value for testing columns", resultset.getValueAt( 3, 0 ) );

  Assert.assertEquals( 1.1, resultset.getValueAt( 0, 1 ) );
  Assert.assertEquals( 5.7, resultset.getValueAt( 1, 1 ) );
  Assert.assertEquals( 19.5, resultset.getValueAt( 2, 1 ) );
  Assert.assertEquals( 3.4, resultset.getValueAt( 3, 1 ) );

  Assert.assertEquals( 1.1, resultset.getValueAt( 0, 2 ) );
  Assert.assertEquals( 5.7, resultset.getValueAt( 1, 2 ) );
  Assert.assertEquals( 9.75, resultset.getValueAt( 2, 2 ) );
  Assert.assertEquals( 3.4, resultset.getValueAt( 3, 2 ) );

}
 

@Test
public void testLevelAnnotations() throws Exception {

  List<OlapDimension> dimensions = new ArrayList<OlapDimension>();
  OlapDimension dimension = new OlapDimension();
  dimension.setName( "Dim1" );

  List<OlapHierarchy> hierarchies = new ArrayList<OlapHierarchy>();
  OlapHierarchy hierarchy = new OlapHierarchy();
  hierarchy.setName( "Hier1" );
  List<OlapHierarchyLevel> hierarchyLevels = new ArrayList<OlapHierarchyLevel>();
  OlapHierarchyLevel level = new OlapHierarchyLevel();
  level.setName( "Lvl1" );
  level.setFormatter( "com.pentaho.formatter" );

  level.getAnnotations().add( new OlapAnnotation( "GeoRole", "city" ) );
  level.getAnnotations().add( new OlapAnnotation( "RequiredParents", "country,state" ) );

  hierarchyLevels.add( level );

  hierarchy.setHierarchyLevels( hierarchyLevels );
  hierarchies.add( hierarchy );
  dimension.setHierarchies( hierarchies );

  dimensions.add( dimension );

  List<OlapCube> cubes = new ArrayList<OlapCube>();
  OlapCube cube = new OlapCube();
  cube.setName( "Cube1" );
  cubes.add( cube );

  List<OlapMeasure> measures = new ArrayList<OlapMeasure>();
  OlapMeasure measure = new OlapMeasure();
  measure.setName( "Meas1" );
  measures.add( measure );
  cube.setOlapMeasures( measures );

  List<OlapDimensionUsage> dimensionUsages = new ArrayList<OlapDimensionUsage>();

  OlapDimensionUsage dimUsage = new OlapDimensionUsage();
  dimensionUsages.add( dimUsage );
  cube.setOlapDimensionUsages( dimensionUsages );

  dimUsage.setName( "Dim1" );
  dimUsage.setOlapDimension( dimension );

  LogicalModel businessModel = TestHelper.buildDefaultModel();
  LogicalTable logicalTable = businessModel.getLogicalTables().get( 0 );
  hierarchy.setLogicalTable( logicalTable );
  List<LogicalColumn> logicalColumns = new ArrayList<LogicalColumn>();
  level.setReferenceColumn( logicalTable.getLogicalColumns().get( 0 ) );
  level.setLogicalColumns( new ArrayList<LogicalColumn>() );
  logicalTable.setProperty( SqlPhysicalTable.TARGET_TABLE, "select * from customer" );
  LogicalColumn m = logicalTable.getLogicalColumns().get( 0 );
  m.setAggregationType( AggregationType.SUM );
  measure.setLogicalColumn( m );
  businessModel.getLogicalTables().get( 0 ).setProperty( SqlPhysicalTable.TARGET_TABLE_TYPE,
      TargetTableType.INLINE_SQL );
  cube.setLogicalTable( businessModel.getLogicalTables().get( 0 ) );

  businessModel.setProperty( "olap_dimensions", dimensions );
  businessModel.setProperty( "olap_cubes", cubes );

  businessModel.setName( new LocalizedString( "en_US", "model" ) );
  MondrianModelExporter exporter = new MondrianModelExporter( businessModel, "en_US" );
  String data = exporter.createMondrianModelXML();

  TestHelper.assertEqualsIgnoreWhitespaces(
    "<Schema name=\"model\">\n"
      + "  <Dimension name=\"Dim1\">\n"
      + "    <Hierarchy name=\"Hier1\" hasAll=\"false\">\n"
      + "    <View alias=\"FACT\">\n"
      + "        <SQL dialect=\"generic\">\n"
      + "         <![CDATA[select * from customer]]>\n"
      + "        </SQL>\n"
      + "    </View>\n"
      + "      <Level name=\"Lvl1\" uniqueMembers=\"false\" column=\"pc1\" type=\"Numeric\" formatter=\"com.pentaho.formatter\">\n"
      + "        <Annotations>\n"
      + "          <Annotation name=\"GeoRole\">city</Annotation>\n"
      + "          <Annotation name=\"RequiredParents\">country,state</Annotation>\n"
      + "        </Annotations>\n"
      + "      </Level>\n"
      + "    </Hierarchy>\n"
      + "  </Dimension>\n"
      + "  <Cube name=\"Cube1\">\n"
      + "    <View alias=\"FACT\">\n"
      + "        <SQL dialect=\"generic\">\n"
      + "         <![CDATA[select * from customer]]>\n"
      + "        </SQL>\n" + "    </View>\n"
      + "    <DimensionUsage name=\"Dim1\" source=\"Dim1\" foreignKey=\"pc2\"/>\n"
      + "    <Measure name=\"bc1\" column=\"pc1\" aggregator=\"sum\"/>\n"
      + "  </Cube>\n"
      + "</Schema>", data );
}
 

@Test
public void testLevelProperties() throws Exception {

  List<OlapDimension> dimensions = new ArrayList<OlapDimension>();
  OlapDimension dimension = new OlapDimension();
  dimension.setName( "Dim1" );

  List<OlapHierarchy> hierarchies = new ArrayList<OlapHierarchy>();
  OlapHierarchy hierarchy = new OlapHierarchy();
  hierarchy.setName( "Hier1" );
  List<OlapHierarchyLevel> hierarchyLevels = new ArrayList<OlapHierarchyLevel>();
  OlapHierarchyLevel level = new OlapHierarchyLevel();
  level.setName( "Lvl1" );

  level.getAnnotations().add( new OlapAnnotation( "GeoRole", "city" ) );

  List<LogicalColumn> cols = new ArrayList<LogicalColumn>();
  LogicalColumn lcLat = new LogicalColumn();
  lcLat.setName( new LocalizedString( "en_US", "latitude" ) );
  lcLat.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "Latitude" );
  lcLat.setDataType( DataType.NUMERIC );
  LogicalColumn lcLon = new LogicalColumn();
  lcLon.setName( new LocalizedString( "en_US", "longitude" ) );
  lcLon.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "Longitude" );
  lcLon.setDataType( DataType.NUMERIC );

  cols.add( lcLat );
  cols.add( lcLon );

  level.setLogicalColumns( cols );

  hierarchyLevels.add( level );

  hierarchy.setHierarchyLevels( hierarchyLevels );
  hierarchies.add( hierarchy );
  dimension.setHierarchies( hierarchies );

  dimensions.add( dimension );

  List<OlapCube> cubes = new ArrayList<OlapCube>();
  OlapCube cube = new OlapCube();
  cube.setName( "Cube1" );
  cubes.add( cube );

  List<OlapMeasure> measures = new ArrayList<OlapMeasure>();
  OlapMeasure measure = new OlapMeasure();
  measure.setName( "Meas1" );
  measures.add( measure );
  cube.setOlapMeasures( measures );

  List<OlapDimensionUsage> dimensionUsages = new ArrayList<OlapDimensionUsage>();

  OlapDimensionUsage dimUsage = new OlapDimensionUsage();
  dimensionUsages.add( dimUsage );
  cube.setOlapDimensionUsages( dimensionUsages );

  dimUsage.setName( "Dim1" );
  dimUsage.setOlapDimension( dimension );

  LogicalModel businessModel = TestHelper.buildDefaultModel();
  LogicalTable logicalTable = businessModel.getLogicalTables().get( 0 );
  hierarchy.setLogicalTable( logicalTable );
  List<LogicalColumn> logicalColumns = new ArrayList<LogicalColumn>();
  level.setReferenceColumn( logicalTable.getLogicalColumns().get( 0 ) );
  // level.setLogicalColumns(new ArrayList<LogicalColumn>());
  logicalTable.setProperty( SqlPhysicalTable.TARGET_TABLE, "select * from customer" );
  LogicalColumn m = logicalTable.getLogicalColumns().get( 0 );
  m.setAggregationType( AggregationType.SUM );
  measure.setLogicalColumn( m );
  businessModel.getLogicalTables().get( 0 ).setProperty( SqlPhysicalTable.TARGET_TABLE_TYPE,
      TargetTableType.INLINE_SQL );
  cube.setLogicalTable( businessModel.getLogicalTables().get( 0 ) );

  businessModel.setProperty( "olap_dimensions", dimensions );
  businessModel.setProperty( "olap_cubes", cubes );

  businessModel.setName( new LocalizedString( "en_US", "model" ) );
  MondrianModelExporter exporter = new MondrianModelExporter( businessModel, "en_US" );
  String data = exporter.createMondrianModelXML();

  TestHelper.assertEqualsIgnoreWhitespaces( "<Schema name=\"model\">\n" + "  <Dimension name=\"Dim1\">\n"
      + "    <Hierarchy name=\"Hier1\" hasAll=\"false\">\n" + "    <View alias=\"FACT\">\n"
      + "        <SQL dialect=\"generic\">\n" + "         <![CDATA[select * from customer]]>\n" + "        </SQL>\n"
      + "    </View>\n" + "      <Level name=\"Lvl1\" uniqueMembers=\"false\" column=\"pc1\" type=\"Numeric\">\n"
      + "        <Annotations>\n" + "          <Annotation name=\"GeoRole\">city</Annotation>\n"
      + "        </Annotations>\n" + "        <Property name=\"latitude\" column=\"Latitude\" type=\"Numeric\"/>\n"
      + "        <Property name=\"longitude\" column=\"Longitude\" type=\"Numeric\"/>\n" + "      </Level>\n"
      + "    </Hierarchy>\n" + "  </Dimension>\n" + "  <Cube name=\"Cube1\">\n" + "    <View alias=\"FACT\">\n"
      + "        <SQL dialect=\"generic\">\n" + "         <![CDATA[select * from customer]]>\n" + "        </SQL>\n"
      + "    </View>\n" + "    <DimensionUsage name=\"Dim1\" source=\"Dim1\" foreignKey=\"pc2\"/>\n"
      + "    <Measure name=\"bc1\" column=\"pc1\" aggregator=\"sum\"/>\n" + "  </Cube>\n"
      + "</Schema>", data );
}
 

private LogicalModel getTestModel( TargetTableType tableType, String targetTable, String targetSchema,
    boolean hiddenMembers ) {
  List<OlapDimension> dimensions = new ArrayList<OlapDimension>();
  OlapDimension dimension = new OlapDimension();
  dimension.setName( "Dim1" );

  List<OlapHierarchy> hierarchies = new ArrayList<OlapHierarchy>();
  OlapHierarchy hierarchy = new OlapHierarchy();
  hierarchy.setName( "Hier1" );
  List<OlapHierarchyLevel> hierarchyLevels = new ArrayList<OlapHierarchyLevel>();
  OlapHierarchyLevel level = new OlapHierarchyLevel();
  level.setName( "Lvl1" );
  level.setHidden( hiddenMembers );
  hierarchyLevels.add( level );

  hierarchy.setHierarchyLevels( hierarchyLevels );
  hierarchies.add( hierarchy );
  dimension.setHierarchies( hierarchies );

  dimensions.add( dimension );

  List<OlapCube> cubes = new ArrayList<OlapCube>();
  OlapCube cube = new OlapCube();
  cube.setName( "Cube1" );
  cubes.add( cube );

  List<OlapMeasure> measures = new ArrayList<OlapMeasure>();
  OlapMeasure measure = new OlapMeasure();
  measure.setName( "Meas1" );
  measures.add( measure );
  measure.setHidden( hiddenMembers );
  cube.setOlapMeasures( measures );

  List<OlapDimensionUsage> dimensionUsages = new ArrayList<OlapDimensionUsage>();

  OlapDimensionUsage dimUsage = new OlapDimensionUsage();
  dimensionUsages.add( dimUsage );
  cube.setOlapDimensionUsages( dimensionUsages );

  dimUsage.setName( "Dim1" );
  dimUsage.setOlapDimension( dimension );

  LogicalModel businessModel = TestHelper.buildDefaultModel();
  LogicalTable logicalTable = businessModel.getLogicalTables().get( 0 );
  hierarchy.setLogicalTable( logicalTable );
  List<LogicalColumn> logicalColumns = new ArrayList<LogicalColumn>();
  level.setReferenceColumn( logicalTable.getLogicalColumns().get( 0 ) );
  level.setLogicalColumns( new ArrayList<LogicalColumn>() );
  logicalTable.setProperty( SqlPhysicalTable.TARGET_TABLE, targetTable );
  logicalTable.setProperty( SqlPhysicalTable.TARGET_SCHEMA, targetSchema );

  LogicalColumn m = logicalTable.getLogicalColumns().get( 0 );
  m.setAggregationType( AggregationType.SUM );
  measure.setLogicalColumn( m );
  businessModel.getLogicalTables().get( 0 ).setProperty( SqlPhysicalTable.TARGET_TABLE_TYPE, tableType );
  cube.setLogicalTable( businessModel.getLogicalTables().get( 0 ) );

  businessModel.setProperty( "olap_dimensions", dimensions );
  businessModel.setProperty( "olap_cubes", cubes );

  businessModel.setName( new LocalizedString( "en_US", "model" ) );
  return businessModel;
}