Java Code Examples for org.pentaho.metadata.model.LogicalTable#setProperty()

The following examples show how to use org.pentaho.metadata.model.LogicalTable#setProperty() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: StarModelDialog.java    From pentaho-kettle with Apache License 2.0 6 votes vote down vote up
protected boolean copyTable(Shell shell, LogicalModel logicalModel, String tableName) {

    LogicalTable originalTable = findLogicalTable(tableName);
    if (originalTable!=null) {
      // Copy
      //
      LogicalTable logicalTable = new LogicalTable();
      logicalTable.setId(UUID.randomUUID().toString());
      logicalTable.setName(new LocalizedString(locale, ConceptUtil.getName(originalTable, locale)+" (Copy)"));
      logicalTable.setDescription(new LocalizedString(locale, ConceptUtil.getDescription(originalTable, locale)+" (Copy)"));
      logicalTable.setProperty(DefaultIDs.LOGICAL_TABLE_PHYSICAL_TABLE_NAME, originalTable.getProperty(DefaultIDs.LOGICAL_TABLE_PHYSICAL_TABLE_NAME));
      logicalTable.setProperty(DefaultPropertyID.TABLE_TYPE.getId(), originalTable.getProperty(DefaultPropertyID.TABLE_TYPE.getId()));
      for (LogicalColumn column : originalTable.getLogicalColumns()) {
        logicalTable.getLogicalColumns().add((LogicalColumn) column.clone());
      }

      DimensionTableDialog dialog = new DimensionTableDialog(shell, logicalTable, locale);
      if (dialog.open()!=null) {
        logicalModel.addLogicalTable(logicalTable);
        return true;
      }
    }
    return false;
  }
 
Example 2
Source File: SQLJoinIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 5 votes vote down vote up
private LogicalTable getDummySingleColumnTable( String identifier ) {
  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt" + identifier );
  bt1.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt" + identifier );
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc" + identifier );
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc" + identifier );
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN_TYPE, TargetColumnType.COLUMN_NAME );
  bc1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bc1 );
  bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
  return bt1;
}
 
Example 3
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 2a: Two Joined Tables are outer joined to two other tables
 */
@Test
public void testOuterJoinScenario2a() 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$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$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, "t2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" ); //$NON-NLS-1$
  bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
  final LogicalColumn bc3 = new LogicalColumn();
  bc3.setId( "bc3" ); //$NON-NLS-1$
  bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc3.setLogicalTable( bt3 );
  bt3.addLogicalColumn( bc3 );
  mainCat.addLogicalColumn( bc3 );

  final LogicalTable bt4 = new LogicalTable();
  bt4.setId( "bt4" ); //$NON-NLS-1$
  bt4.setProperty( SqlPhysicalTable.TARGET_TABLE, "t4" ); //$NON-NLS-1$
  final LogicalColumn bc4 = new LogicalColumn();
  bc4.setId( "bc4" ); //$NON-NLS-1$
  bc4.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc4.setLogicalTable( bt4 );
  bt4.addLogicalColumn( bc4 );
  mainCat.addLogicalColumn( bc4 );

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

  model.getLogicalRelationships().add( rl1 );

  final LogicalRelationship rl2 = new LogicalRelationship();
  rl2.setRelationshipType( RelationshipType._0_N );
  rl2.setFromTable( bt2 );
  rl2.setFromColumn( bc2 );
  rl2.setToTable( bt3 );
  rl2.setToColumn( bc3 );

  model.getLogicalRelationships().add( rl2 );

  final LogicalRelationship rl3 = new LogicalRelationship();
  rl3.setRelationshipType( RelationshipType._1_N );
  rl3.setFromTable( bt3 );
  rl3.setFromColumn( bc3 );
  rl3.setToTable( bt4 );
  rl3.setToColumn( bc4 );

  model.getLogicalRelationships().add( rl3 );

  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.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
  myTest.getSelections().add( new AliasedSelection( null, bc4, null, null ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT DISTINCT bt1.k AS COL0,bt2.k AS COL1,bt3.k AS COL2,bt4.k AS COL3 FROM t1 bt1 JOIN(t4 bt4 JOIN(t2 bt2 LEFT OUTER JOIN t3 bt3 ON(bt2.k = bt3.k))ON(bt3.k = bt4.k))ON(bt1.k = bt2.k)",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 4
Source File: MondrianModelExporterTest.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
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;
}
 
Example 5
Source File: MondrianModelExporterTest.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
@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 );
}
 
Example 6
Source File: MondrianModelExporterTest.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
@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 );
}
 
Example 7
Source File: ComplexExpressionsIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
private LogicalModel createModel() {

    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 );
    model.getLogicalTables().add( bt1 );

    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.setLogicalTable( bt2 );
    bt2.addLogicalColumn( bc2 );
    mainCat.addLogicalColumn( bc2 );
    model.getLogicalTables().add( bt2 );

    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] * [bt1.bc1]" ); //$NON-NLS-1$
    bce2.setLogicalTable( bt2 );
    bt2.addLogicalColumn( bce2 );
    mainCat.addLogicalColumn( bce2 );

    final LogicalRelationship rl1 = new LogicalRelationship();

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

    model.getLogicalRelationships().add( rl1 );

    return model;
  }
 
Example 8
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 6: 4 tables outer joined
 * 
 * NOTE: This does not work on MYSQL, because FULL OUTER JOIN is not supported.
 */
@Test
public void testOuterJoinScenario6() 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$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$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, "t2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" ); //$NON-NLS-1$
  bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
  final LogicalColumn bc3 = new LogicalColumn();
  bc3.setId( "bc3" ); //$NON-NLS-1$
  bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc3.setLogicalTable( bt3 );
  bt3.addLogicalColumn( bc3 );
  mainCat.addLogicalColumn( bc3 );

  final LogicalTable bt4 = new LogicalTable();
  bt4.setId( "bt4" ); //$NON-NLS-1$
  bt4.setProperty( SqlPhysicalTable.TARGET_TABLE, "t4" ); //$NON-NLS-1$
  final LogicalColumn bc4 = new LogicalColumn();
  bc4.setId( "bc4" ); //$NON-NLS-1$
  bc4.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc4.setLogicalTable( bt4 );
  bt4.addLogicalColumn( bc4 );
  mainCat.addLogicalColumn( bc4 );

  final LogicalRelationship rl1 = new LogicalRelationship();
  rl1.setRelationshipType( RelationshipType._0_0 );
  rl1.setJoinOrderKey( "A" );
  rl1.setFromTable( bt1 );
  rl1.setFromColumn( bc1 );
  rl1.setToTable( bt2 );
  rl1.setToColumn( bc2 );

  model.getLogicalRelationships().add( rl1 );

  final LogicalRelationship rl2 = new LogicalRelationship();
  rl2.setRelationshipType( RelationshipType._0_0 );
  rl2.setJoinOrderKey( "B" );
  rl2.setFromTable( bt2 );
  rl2.setFromColumn( bc2 );
  rl2.setToTable( bt3 );
  rl2.setToColumn( bc3 );

  model.getLogicalRelationships().add( rl2 );

  final LogicalRelationship rl3 = new LogicalRelationship();
  rl3.setRelationshipType( RelationshipType._0_N );
  rl3.setJoinOrderKey( "A" );
  rl3.setFromTable( bt2 );
  rl3.setFromColumn( bc2 );
  rl3.setToTable( bt4 );
  rl3.setToColumn( bc4 );

  model.getLogicalRelationships().add( rl3 );

  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.getSelections().add( new AliasedSelection( null, bc3, null, null ) );
  myTest.getSelections().add( new AliasedSelection( null, bc4, null, null ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 ,bt4.k AS COL3 FROM t3 bt3 FULL OUTER JOIN ( t1 bt1 FULL OUTER JOIN ( t2 bt2 LEFT OUTER JOIN t4 bt4 ON ( bt2.k = bt4.k ) ) ON ( bt1.k = bt2.k ) ) ON ( bt2.k = bt3.k )",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 9
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 5: Two outer joins in the opposite direction
 */
@Test
public void testOuterJoinScenario5a() 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$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$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, "t2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" ); //$NON-NLS-1$
  bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
  final LogicalColumn bc3 = new LogicalColumn();
  bc3.setId( "bc3" ); //$NON-NLS-1$
  bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc3.setLogicalTable( bt3 );
  bt3.addLogicalColumn( bc3 );
  mainCat.addLogicalColumn( bc3 );

  final LogicalRelationship rl1 = new LogicalRelationship();
  rl1.setRelationshipType( RelationshipType._N_0 );
  rl1.setJoinOrderKey( "A" );
  rl1.setFromTable( bt1 );
  rl1.setFromColumn( bc1 );
  rl1.setToTable( bt2 );
  rl1.setToColumn( bc2 );

  model.getLogicalRelationships().add( rl1 );

  final LogicalRelationship rl2 = new LogicalRelationship();
  rl2.setRelationshipType( RelationshipType._N_0 );
  rl2.setJoinOrderKey( "B" );
  rl2.setFromTable( bt1 );
  rl2.setFromColumn( bc1 );
  rl2.setToTable( bt3 );
  rl2.setToColumn( bc3 );

  model.getLogicalRelationships().add( rl2 );

  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.getSelections().add( new AliasedSelection( null, bc3, null, null ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 FROM t3 bt3 LEFT OUTER JOIN ( t1 bt1 RIGHT OUTER JOIN t2 bt2 ON ( bt1.k = bt2.k ) ) ON ( bt1.k = bt3.k )",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 10
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 4: Two outer joins on a single table
 */
@Test
public void testOuterJoinScenario4() 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$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$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, "t2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" ); //$NON-NLS-1$
  bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
  final LogicalColumn bc3 = new LogicalColumn();
  bc3.setId( "bc3" ); //$NON-NLS-1$
  bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc3.setLogicalTable( bt3 );
  bt3.addLogicalColumn( bc3 );
  mainCat.addLogicalColumn( bc3 );

  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 );

  final LogicalRelationship rl2 = new LogicalRelationship();
  rl2.setRelationshipType( RelationshipType._0_N );
  rl2.setFromTable( bt1 );
  rl2.setFromColumn( bc1 );
  rl2.setToTable( bt3 );
  rl2.setToColumn( bc3 );

  model.getLogicalRelationships().add( rl2 );

  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.getSelections().add( new AliasedSelection( null, bc3, null, null ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 FROM t2 bt2 RIGHT OUTER JOIN ( t1 bt1 LEFT OUTER JOIN t3 bt3 ON ( bt1.k = bt3.k ) ) ON ( bt1.k = bt2.k )",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 11
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 3: Three Tables are outer joined
 */
@Test
public void testOuterJoinScenario3() 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$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$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, "t2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" ); //$NON-NLS-1$
  bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "t3" ); //$NON-NLS-1$
  final LogicalColumn bc3 = new LogicalColumn();
  bc3.setId( "bc3" ); //$NON-NLS-1$
  bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc3.setLogicalTable( bt3 );
  bt3.addLogicalColumn( bc3 );
  mainCat.addLogicalColumn( bc3 );

  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 );

  final LogicalRelationship rl2 = new LogicalRelationship();
  rl2.setRelationshipType( RelationshipType._0_N );
  rl2.setFromTable( bt2 );
  rl2.setFromColumn( bc2 );
  rl2.setToTable( bt3 );
  rl2.setToColumn( bc3 );

  model.getLogicalRelationships().add( rl2 );

  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.getSelections().add( new AliasedSelection( null, bc3, null, null ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt3.k AS COL2 FROM t1 bt1 LEFT OUTER JOIN ( t2 bt2 LEFT OUTER JOIN t3 bt3 ON ( bt2.k = bt3.k ) ) ON ( bt1.k = bt2.k )",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 12
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 2: Two Joined Tables are outer joined to a single table
 */
@Test
public void testOuterJoinScenario2() 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.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  mainCat.addLogicalColumn( bc2 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" ); //$NON-NLS-1$
  bt3.setProperty( SqlPhysicalTable.TARGET_TABLE, "pt3" ); //$NON-NLS-1$
  final LogicalColumn bc3 = new LogicalColumn();
  bc3.setId( "bc3" ); //$NON-NLS-1$
  bc3.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc3" ); //$NON-NLS-1$
  bc3.setLogicalTable( bt3 );
  bt3.addLogicalColumn( bc3 );
  mainCat.addLogicalColumn( bc3 );

  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 );

  final LogicalRelationship rl2 = new LogicalRelationship();
  rl2.setRelationshipType( RelationshipType._1_N );
  rl2.setFromTable( bt2 );
  rl2.setFromColumn( bc2 );
  rl2.setToTable( bt3 );
  rl2.setToColumn( bc3 );

  model.getLogicalRelationships().add( rl2 );

  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.getSelections().add( new AliasedSelection( null, bc3, null, null ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT DISTINCT bt1.pc1 AS COL0,bt2.pc2 AS COL1,bt3.pc3 AS COL2 FROM pt3 bt3 JOIN(pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON(bt1.pc1 = bt2.pc2))ON(bt2.pc2 = bt3.pc3)",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 13
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * 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$
}
 
Example 14
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * 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$
}
 
Example 15
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 1a: Two Tables are outer joined with a constraint
 */
@Test
public void testOuterJoinScenario1a() 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.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 DISTINCT bt1.pc1 AS COL0 ,bt2.pc2 AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 AND ( bt2.pc2 > 1 ) )",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 16
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 1: Two Tables are outer joined
 */
@Test
public void testOuterJoinScenario1() 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.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 DISTINCT bt1.pc1 AS COL0 ,bt2.pc2 AS COL1 FROM pt1 bt1 LEFT OUTER JOIN pt2 bt2 ON ( bt1.pc1 = bt2.pc2 )",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 17
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * Scenario 1: Two Tables are outer joined
 */
@Test
public void testOuterJoinScenario1WithAddlAlias() 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$
  final LogicalColumn bc1 = new LogicalColumn();
  bc1.setId( "bc1" ); //$NON-NLS-1$
  bc1.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc1.setLogicalTable( bt1 );
  bt1.addLogicalColumn( bc1 );
  bt1.setProperty( SqlPhysicalTable.RELATIVE_SIZE, 1 );
  model.getLogicalTables().add( bt1 );
  mainCat.addLogicalColumn( bc1 );

  final LogicalTable bt2 = new LogicalTable();
  bt2.setId( "bt2" ); //$NON-NLS-1$
  bt2.setProperty( SqlPhysicalTable.TARGET_TABLE, "t2" ); //$NON-NLS-1$
  final LogicalColumn bc2 = new LogicalColumn();
  bc2.setId( "bc2" ); //$NON-NLS-1$
  bc2.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "k" ); //$NON-NLS-1$
  bc2.setLogicalTable( bt2 );
  bt2.addLogicalColumn( bc2 );
  model.getLogicalTables().add( bt2 );
  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.getSelections().add( new AliasedSelection( null, bc2, null, "alias" ) );

  myTest.getConstraints().add( new Constraint( CombinationType.AND, "[cat_01.bc2] = 1" ) );
  myTest.getConstraints().add( new Constraint( CombinationType.AND, "[alias.bc2] = 2" ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT DISTINCT bt1.k AS COL0 ,bt2.k AS COL1 ,bt2_alias.k AS COL2 FROM t2 bt2 RIGHT OUTER JOIN ( t1 bt1 LEFT OUTER JOIN t2 bt2_alias ON ( bt1.k = bt2_alias.k AND ( bt2_alias.k = 2 ) ) ) ON ( bt1.k = bt2.k AND ( bt2.k = 1 ) )",
          query.getQuery() ); //$NON-NLS-1$
}
 
Example 18
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
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;
}
 
Example 19
Source File: AggregationScenariosIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * 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() );
}
 
Example 20
Source File: AggregationScenariosIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 * 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() );
}