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

The following examples show how to use org.pentaho.metadata.model.LogicalTable#addLogicalColumn() . 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: 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 2
Source File: AutoModeler.java    From pentaho-metadata with GNU Lesser General Public License v2.1 5 votes vote down vote up
private LogicalTable createBusinessTable( SqlPhysicalTable physicalTable, String locale ) {

    // Create a business table with a new ID and localized name
    //
    LogicalTable businessTable = new LogicalTable( null, physicalTable );

    // Try to set the name of the business table to something nice (beautify)
    //
    String tableName = PhysicalTableImporter.beautifyName( physicalTable.getTargetTable() );
    businessTable.setName( new LocalizedString( locale, tableName ) );

    businessTable.setId( Util.proposeSqlBasedLogicalTableId( locale, businessTable, physicalTable ) );

    // Add columns to this by copying the physical columns to the business
    // columns...
    //
    for ( IPhysicalColumn physicalColumn : physicalTable.getPhysicalColumns() ) {

      LogicalColumn businessColumn = new LogicalColumn();
      businessColumn.setPhysicalColumn( physicalColumn );
      businessColumn.setLogicalTable( businessTable );

      // We're done, add the business column.
      //
      // Propose a new ID
      businessColumn.setId( Util.proposeSqlBasedLogicalColumnId( locale, businessTable,
          (SqlPhysicalColumn) physicalColumn ) );
      businessTable.addLogicalColumn( businessColumn );
    }

    return businessTable;
  }
 
Example 3
Source File: SpiderWebTestModel.java    From pentaho-metadata with GNU Lesser General Public License v2.1 5 votes vote down vote up
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;

  }
 
Example 4
Source File: SpiderWebTestModel.java    From pentaho-metadata with GNU Lesser General Public License v2.1 5 votes vote down vote up
private LogicalColumn createBusinessKeyColumn( String tblId, String columnId, LogicalTable tbl, Category cat )
  throws Exception {
  LogicalColumn rtn = new LogicalColumn();
  rtn.setId( "bc_" + columnId + "_" + tblId );
  rtn.setProperty( SqlPhysicalColumn.TARGET_COLUMN, "pc_" + columnId + "_" + tblId ); //$NON-NLS-1$
  rtn.setLogicalTable( tbl );
  tbl.addLogicalColumn( rtn );
  cat.addLogicalColumn( rtn );
  return rtn;
}
 
Example 5
Source File: ThinModelIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
@Test
public void testSerializeSqlPhysicalModel() {

  String locale = LocaleHelper.getLocale().toString();

  SqlPhysicalModel model = new SqlPhysicalModel();
  SqlDataSource dataSource = new SqlDataSource();
  dataSource.setDatabaseName( "SampleData" );
  model.setDatasource( dataSource );
  SqlPhysicalTable table = new SqlPhysicalTable( model );
  model.getPhysicalTables().add( table );
  table.setTargetTableType( TargetTableType.INLINE_SQL );
  table.setTargetTable( "select * from customers" );

  SqlPhysicalColumn column = new SqlPhysicalColumn( table );
  column.setTargetColumn( "customername" );
  column.setName( new LocalizedString( locale, "Customer Name" ) );
  column.setDescription( new LocalizedString( locale, "Customer Name Desc" ) );
  column.setDataType( DataType.STRING );

  table.getPhysicalColumns().add( column );

  LogicalModel logicalModel = new LogicalModel();
  model.setId( "MODEL" );
  model.setName( new LocalizedString( locale, "My Model" ) );
  model.setDescription( new LocalizedString( locale, "A Description of the Model" ) );

  LogicalTable logicalTable = new LogicalTable();
  logicalTable.setPhysicalTable( table );

  logicalModel.getLogicalTables().add( logicalTable );

  LogicalColumn logicalColumn = new LogicalColumn();
  logicalColumn.setId( "LC_CUSTOMERNAME" );
  logicalColumn.setPhysicalColumn( column );

  logicalTable.addLogicalColumn( logicalColumn );

  Category mainCategory = new Category();
  mainCategory.setId( "CATEGORY" );
  mainCategory.setName( new LocalizedString( locale, "Category" ) );
  mainCategory.addLogicalColumn( logicalColumn );

  logicalModel.getCategories().add( mainCategory );

  Domain domain = new Domain();
  domain.addPhysicalModel( model );
  domain.addLogicalModel( logicalModel );

  // basic tests
  SerializationService service = new SerializationService();

  String xml = service.serializeDomain( domain );

  // System.out.println(xml);

  Domain domain2 = service.deserializeDomain( xml );

  Assert.assertEquals( 1, domain2.getPhysicalModels().size() );
  SqlPhysicalModel model2 = (SqlPhysicalModel) domain2.getPhysicalModels().get( 0 );
  Assert.assertEquals( "SampleData", model2.getDatasource().getDatabaseName() );
  Assert.assertEquals( 1, model.getPhysicalTables().size() );
  Assert.assertEquals( TargetTableType.INLINE_SQL, model.getPhysicalTables().get( 0 ).getTargetTableType() );

  Assert.assertEquals( 1, domain.getLogicalModels().size() );
  Assert.assertEquals( 1, domain.getLogicalModels().get( 0 ).getCategories().size() );
  Assert.assertEquals( 1, domain.getLogicalModels().get( 0 ).getCategories().get( 0 ).getLogicalColumns().size() );
  Assert.assertEquals( domain.getLogicalModels().get( 0 ).getLogicalTables().get( 0 ).getLogicalColumns().get( 0 ),
      domain.getLogicalModels().get( 0 ).getCategories().get( 0 ).getLogicalColumns().get( 0 ) );
  Assert.assertEquals( "Customer Name", domain.getLogicalModels().get( 0 ).getCategories().get( 0 )
      .getLogicalColumns().get( 0 ).getName().getString( "en_US" ) );
  Assert.assertEquals( "Customer Name Desc", domain.getLogicalModels().get( 0 ).getCategories().get( 0 )
      .getLogicalColumns().get( 0 ).getDescription().getString( "en_US" ) );

}
 
Example 6
Source File: AdvancedQueryIT.java    From pentaho-metadata with GNU Lesser General Public License v2.1 4 votes vote down vote up
@Test
public void testComplexJoinMQL() throws Exception {

  String locale = "en_US"; //$NON-NLS-1$

  final LogicalModel model = new LogicalModel();

  final LogicalTable bt1 = new LogicalTable();
  bt1.setId( "bt1" ); //$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 );

  final LogicalTable bt2 = new LogicalTable();
  bt2.setId( "bt2" ); //$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 );

  final LogicalTable bt3 = new LogicalTable();
  bt3.setId( "bt3" ); //$NON-NLS-1$

  final LogicalRelationship rl1 = new LogicalRelationship();

  rl1.setFromTable( bt1 );
  rl1.setToTable( bt2 );
  rl1.setComplexJoin( "[bt1.bc1] = [bt2.bc2]" ); //$NON-NLS-1$
  rl1.setComplex( true );

  final LogicalRelationship rl2 = new LogicalRelationship();

  rl2.setToTable( bt2 );
  rl2.setFromTable( bt3 );

  final LogicalRelationship rl3 = new LogicalRelationship();

  rl3.setToTable( bt1 );
  rl3.setFromTable( bt3 );

  model.getLogicalTables().add( bt1 );
  model.getLogicalTables().add( bt2 );
  model.getLogicalTables().add( bt3 );

  model.getLogicalRelationships().add( rl1 );
  model.getLogicalRelationships().add( rl2 );
  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 ) );

  MappedQuery query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper.assertEqualsIgnoreWhitespaces(
      "SELECT DISTINCT bt1.pc1 AS COL0 ,bt2.pc2 AS COL1 FROM null bt1 ,null bt2 WHERE ( bt1.pc1 = bt2.pc2 )", //$NON-NLS-1$
      query.getQuery() );

  myTest.getSelections().add( new AliasedSelection( null, bc2, null, "alias" ) );
  query = new AdvancedSqlGenerator().generateSql( myTest, "en_US", null, databaseMeta );
  TestHelper
      .assertEqualsIgnoreWhitespaces(
          "SELECT DISTINCT bt1.pc1 AS COL0 ,bt2.pc2 AS COL1 ,bt2_alias.pc2 AS COL2 FROM null bt1 ,null bt2 ,null bt2_alias WHERE ( bt1.pc1 = bt2.pc2 ) AND ( bt1.pc1 = bt2_alias.pc2 )", //$NON-NLS-1$
          query.getQuery() );
}
 
Example 7
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 8
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 testOuterJoinScenario5b() 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( "B" );
  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( "A" );
  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 LEFT OUTER JOIN ( t1 bt1 RIGHT OUTER JOIN t3 bt3 ON ( bt1.k = bt3.k ) ) ON ( bt1.k = bt2.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 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 10
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 11
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 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() );
}