org.pentaho.metadata.model.SqlPhysicalModel Java Examples

@Test
public void testSqlPhysicalModel() {

  // this is the minimum physical sql model, it could
  // theoretically be used to execute sql directly.

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

  // basic tests

  Assert.assertEquals( "SampleData", model.getDatasource().getDatabaseName() );
  Assert.assertEquals( 1, model.getPhysicalTables().size() );
  Assert.assertEquals( TargetTableType.INLINE_SQL, model.getPhysicalTables().get( 0 ).getTargetTableType() );
  Assert.assertEquals( "select * from customers", model.getPhysicalTables().get( 0 ).getTargetTable() );
  Assert.assertEquals( 1, model.getPhysicalTables().size() );
}
 

private DatabaseMeta getDatabaseMeta( final Query queryObject ) throws ReportDataFactoryException {
  // need to get the correct DatabaseMeta
  final List<LogicalTable> tables = queryObject.getLogicalModel().getLogicalTables();
  if ( tables.isEmpty() ) {
    throw new ReportDataFactoryException( "No Tables in this query" );
  }
  final SqlPhysicalModel sqlModel = (SqlPhysicalModel) tables.get( 0 ).getPhysicalTable().getPhysicalModel();
  return ThinModelConverter.convertToLegacy( sqlModel.getId(), sqlModel.getDatasource() );
}
 

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

public Domain generateDomain( final ImportStrategy importStrategy ) throws PentahoMetadataException {
  Domain domain = new Domain();
  domain.setId( modelName );

  List<LocaleType> locales = new ArrayList<LocaleType>();
  locales.add( new LocaleType( "en_US", "English (US)" ) ); //$NON-NLS-1$ //$NON-NLS-2$
  domain.setLocales( locales );

  SqlPhysicalModel physicalModel = new SqlPhysicalModel();
  physicalModel.setId( databaseMeta.getName() );
  physicalModel.setDatasource( ThinModelConverter.convertFromLegacy( databaseMeta ) );

  Database database = database();

  try {
    // Add the database connection to the empty schema...
    //
    domain.addPhysicalModel( physicalModel );

    // Also add a model with the same name as the model name...
    //
    String bmID = Util.getLogicalModelIdPrefix() + "_" + modelName.replaceAll( " ", "_" ).toUpperCase(); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    LogicalModel logicalModel = new LogicalModel();
    logicalModel.setId( bmID );
    domain.addLogicalModel( logicalModel );

    // Connect to the database...
    //
    database.connect();

    // clear the cache
    DBCache.getInstance().clear( databaseMeta.getName() );

    for ( int i = 0; i < tableNames.length; i++ ) {
      SchemaTable schemaTable = tableNames[i];

      // Import the specified tables and turn them into PhysicalTable
      // objects...
      //
      SqlPhysicalTable physicalTable =
          PhysicalTableImporter.importTableDefinition( database, schemaTable.getSchemaName(), schemaTable
          .getTableName(), locale, importStrategy );
      physicalModel.addPhysicalTable( physicalTable );

      // At the same time, we will create a business table and add that to the
      // business model...
      //
      LogicalTable businessTable = createBusinessTable( physicalTable, locale );
      logicalModel.addLogicalTable( businessTable );
    }
  } catch ( Exception e ) {
    // For the unexpected stuff, just throw the exception upstairs.
    //
    throw new PentahoMetadataException( e );
  } finally {
    // Make sure to close the connection
    //
    database.disconnect();
  }

  return domain;
}
 

@Test
public void testXmiLegacyConceptProperties() throws Exception {
  Domain domain = parser.parseXmi( getClass().getResourceAsStream( "/all_concept_properties.xmi" ) );
  assertEquals( 2, domain.getConcepts().size() );
  assertEquals( 1, domain.getPhysicalModels().size() );
  assertEquals( 1, domain.getLogicalModels().size() );

  assertEquals( "http://localhost:8080/pentaho/ServiceAction", domain
      .getChildProperty( "LEGACY_EVENT_SECURITY_SERVICE_URL" ) );

  assertEquals( 1, domain.getLogicalModels().get( 0 ).getLogicalTables().size() );
  assertEquals( 29, domain.getLogicalModels().get( 0 ).getLogicalTables().get( 0 ).getLogicalColumns().size() );
  assertEquals( "BC_CUSTOMER_CUSTOMER_ID", domain.getLogicalModels().get( 0 ).getLogicalTables().get( 0 )
      .getLogicalColumns().get( 0 ).getId() );
  assertEquals( 0, domain.getLogicalModels().get( 0 ).getLogicalRelationships().size() );

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

  assertEquals( 1, domain.getLogicalModels().get( 0 ).getCategories().size() );
  assertEquals( 29, domain.getLogicalModels().get( 0 ).getCategories().get( 0 ).getLogicalColumns().size() );
  assertEquals( "BC_CUSTOMER_FULLNAME", domain.getLogicalModels().get( 0 ).getCategories().get( 0 )
      .getLogicalColumns().get( 0 ).getId() );
  assertEquals( "fullname", domain.getLogicalModels().get( 0 ).getCategories().get( 0 ).getLogicalColumns()
      .get( 0 ).getPhysicalColumn().getId() );
  assertEquals( "PT_CUSTOMER", domain.getLogicalModels().get( 0 ).getCategories().get( 0 ).getLogicalColumns()
      .get( 0 ).getPhysicalColumn().getPhysicalTable().getId() );

  String xmi = parser.generateXmi( domain );

  Domain domain2 = parser.parseXmi( new ReaderInputStream( new StringReader( xmi ) ) );
  SqlDataSource ds = ( (SqlPhysicalModel) domain.getPhysicalModels().get( 0 ) ).getDatasource();
  SqlDataSource ds2 = ( (SqlPhysicalModel) domain2.getPhysicalModels().get( 0 ) ).getDatasource();

  assertEquals( "http://localhost:8080/pentaho/ServiceAction", domain2
      .getChildProperty( "LEGACY_EVENT_SECURITY_SERVICE_URL" ) );

  assertEquals( "foodmart", ds.getDatabaseName() );
  assertEquals( ds.getDatabaseName(), ds2.getDatabaseName() );

  assertEquals( "MYSQL", ds.getDialectType() );
  assertEquals( ds.getDialectType(), ds2.getDialectType() );

  assertEquals( "NATIVE", ds.getType().toString() );
  assertEquals( ds.getType(), ds2.getType() );

  assertEquals( "localhost", ds.getHostname() );
  assertEquals( ds.getHostname(), ds2.getHostname() );

  assertEquals( "3306", ds.getPort() );
  assertEquals( ds.getPort(), ds2.getPort() );

  assertEquals( "foodmart", ds.getUsername() );
  assertEquals( ds.getUsername(), ds2.getUsername() );

  assertEquals( "foodmart", ds.getPassword() );
  assertEquals( ds.getPassword(), ds2.getPassword() );

  assertEquals( 9, ds.getAttributes().size() );
  assertEquals( ds.getAttributes().size(), ds2.getAttributes().size() );

  assertEquals( "Y", ds2.getAttributes().get( "QUOTE_ALL_FIELDS" ) );

  // test DatabaseMeta conversion
  DatabaseMeta meta = ThinModelConverter.convertToLegacy( "test", ds );

  assertEquals( "test", meta.getName() );
  assertEquals( "MYSQL", meta.getDatabaseTypeDesc() );
  assertEquals( "Native", meta.getAccessTypeDesc() );
  assertEquals( "localhost", meta.getHostname() );
  assertEquals( "3306", meta.getDatabasePortNumberString() );
  assertEquals( "foodmart", meta.getDatabaseName() );
  assertEquals( "foodmart", meta.getUsername() );
  assertEquals( "foodmart", meta.getPassword() );
  assertTrue( meta.isQuoteAllFields() );

  // Verify that RowLevelSecurity is in the xmi
  assertTrue( xmi.contains(
      "<row-level-security type="global"><formula><![CDATA[TRUE()]]></formula>"
          + "<entries></entries></row-level-security>" ) );

  // Verify that the SqlDatasource is to and from successfully
}
 

@Test
public void testSqlLogicalModel() {

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

  // this sql model is the minimum required for
  // MQL execution

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

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

  // logical model

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

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

  // test name inheritance
  Assert.assertEquals( column.getName().getString( Locale.getDefault().toString() ), logicalColumn.getName()
      .getString( Locale.getDefault().toString() ) );

  // test datatype inheritance
  Assert.assertEquals( column.getDataType(), logicalColumn.getDataType() );

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

  // replacement for formula / is exact could be
  // target column + target column type (calculated, exact, etc)
}
 

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

}
 

public static Domain getBasicDomain() {

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

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

    SqlPhysicalColumn column = new SqlPhysicalColumn( table );
    column.setId( "PC1" );
    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();
    logicalModel.setId( "MODEL" );
    logicalModel.setName( new LocalizedString( locale, "My Model" ) );
    logicalModel.setDescription( new LocalizedString( locale, "A Description of the Model" ) );

    LogicalTable logicalTable = new LogicalTable();
    logicalTable.setId( "LT" );
    logicalTable.setPhysicalTable( table );
    logicalTable.setLogicalModel( logicalModel );

    logicalModel.getLogicalTables().add( logicalTable );

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

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

    logicalModel.getCategories().add( mainCategory );

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

    return domain;
  }
 

public Domain generatePhysicalMetadataModel() throws KettleException {

    // First do some checking and lookups...
    //
    String targetDatabaseName = ConceptUtil.getString(logicalDomain, DefaultIDs.DOMAIN_TARGET_DATABASE);
    if (Utils.isEmpty(targetDatabaseName)) {
      throw new KettleException("Please specify a target database!");
    }
    DatabaseMeta targetDatabaseMeta = DatabaseMeta.findDatabase(databases, targetDatabaseName);
    if (targetDatabaseMeta==null) {
      throw new KettleException("Target database with name '"+targetDatabaseName+"' can't be found!");
    }

    // Now start creation of a new domain with physical underpinning.
    //
    Domain domain = new Domain();

    // Copy the domain information...
    //
    domain.setId( createId("DOMAIN", null, domain) );
    domain.setName(logicalDomain.getName());
    domain.setDescription(logicalDomain.getDescription());

    // Now copy all the models...
    //
    for (LogicalModel logicalModel : logicalDomain.getLogicalModels()) {
      // Copy model information...
      //
      LogicalModel model = new LogicalModel();
      model.setId( createId("MODEL", domain, model));
      model.setName(logicalModel.getName());
      model.setDescription(logicalModel.getDescription());

      // Create a physical model...
      //
      SqlPhysicalModel sqlModel = new SqlPhysicalModel();
      sqlModel.setDatasource(createSqlDataSource(targetDatabaseMeta));
      model.setPhysicalModel(sqlModel);

      for (LogicalTable logicalTable : logicalModel.getLogicalTables()) {
        LogicalTable table = new LogicalTable();
        table.setId( createId("LOGICAL_TABLE", logicalModel, logicalTable) );
        table.setName(logicalTable.getName());
        table.setDescription(logicalTable.getDescription());

        String targetTable = ConceptUtil.getString(logicalTable, DefaultIDs.LOGICAL_TABLE_PHYSICAL_TABLE_NAME);

        SqlPhysicalTable sqlTable = new SqlPhysicalTable(sqlModel);
        table.setPhysicalTable(sqlTable);

        // Copy name & description from physical level...
        //
        sqlTable.setId( createId("PHYSICAL_TABLE", logicalModel, logicalTable));
        sqlTable.setName(logicalTable.getName());
        sqlTable.setDescription(logicalTable.getDescription());
        sqlTable.setTableType(ConceptUtil.getTableType(logicalTable));
        sqlTable.setTargetSchema(targetDatabaseMeta.getPreferredSchemaName());
        sqlTable.setTargetTable(targetTable);


      }
    }

    return domain;
  }