Java Code Examples for org.pentaho.metadata.model.SqlPhysicalModel

The following examples show how to use org.pentaho.metadata.model.SqlPhysicalModel. These examples are extracted from open source projects. 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
@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() );
}
 
Example 2
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() );
}
 
Example 3
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 );
  }
}
 
Example 4
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;
}
 
Example 5
@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(
      "&lt;row-level-security type=&quot;global&quot;&gt;&lt;formula&gt;&lt;![CDATA[TRUE()]]&gt;&lt;/formula&gt;"
          + "&lt;entries&gt;&lt;/entries&gt;&lt;/row-level-security&gt;" ) );

  // Verify that the SqlDatasource is to and from successfully
}
 
Example 6
@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)
}
 
Example 7
@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 8
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;
  }
 
Example 9
Source Project: pentaho-kettle   Source File: MetadataGenerator.java    License: Apache License 2.0 4 votes vote down vote up
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;
  }