Teiid - Build Vdb from API Java Code

[parrotola]

Hi,

if I create a Emdded Application like this:

EmbeddedServer es = new EmbeddedServer();
EmbeddedConfiguration ec = new EmbeddedConfiguration();
//set any configuration properties
ec.setUseDisk(false);
es.start(ec);

//example of adding a translator by pre-initialized ExecutionFactory and given translator name
H2ExecutionFactory ef = new H2ExecutionFactory()
ef.setSupportsDirectQueryProcedure(e);
ef.start();
es.addTranslator("translator-h2", ef);

//add a Connection Factory with a third-party connection pool
DataSource ds = EmbeddedHelper.newDataSource("org.h2.Driver", "jdbc:h2:mem://localhost/~/account", "sa", "sa");
es.addConnectionFactory("java:/accounts-ds", ds);

//add a vdb

//physical model
ModelMetaData mmd = new ModelMetaData();
mmd.setName("my-schema");
mmd.addSourceMapping("my-schema", "translator-h2", "java:/accounts-ds");

//virtual model
ModelMetaData mmd1 = new ModelMetaData();
mmd1.setName("virt");
mmd1.setModelType(Type.VIRTUAL);
mmd1.setSchemaSourceType("ddl");
mmd1.setSchemaText("create view \"my-view\" OPTIONS (UPDATABLE 'true') as select * from \"my-table\"");

es.deployVDB("test", mmd, mmd1);

is there a way to deploy Vdb model on Remote JBoss Teiid Server? Or is it possible to create vdb file (or Dynamic vdb xml) from MOdelmetadata?

If it is not possible, is it possbile to create a vdb xml file where I define to import all catalog from RDBMS Database without define all table in the source.xmi file?

Thank you for all.

Best regards.

FRancesco

[rareddy]

Hello Francesco,

It is little confusing your intentions in using the Teiid embedded engine. Teiid Embedded is the runtime engine, that is similar to server version but lot of server specific feature removed. Typically one either chooses to go with Server version or Embedded version.

>is there a way to deploy Vdb model on Remote JBoss Teiid Server?

Yes, You do not need to use embedded for this. There are several different ways

1) You can use web-console, which is web application that is used manage the Teiid/WildFly server

2) You can use CLI. This is scripting language that can be executed. Teiid Management CLI · Teiid Documentation

3) use AdminShell AdminShell · Teiid Documentation

3) You can use Admin API. This is programmatic way to manage the server. AdminAPI · Teiid Documentation

>Or is it possible to create vdb file (or Dynamic vdb xml) from MOdelmetadata?

Yes. See this example teiid/TestVDBMetaData.java at master · teiid/teiid · GitHub  You do not need embedded Teiid for this. This shows how to create dynamic vdb files. Also note that in Teiid 9.2 (CR1 released, Final next week) has a way to completely define your VDB using DDL all the way. That means you can use whatever the tools you want to generate the DDL. More on this here DDL VDB · Teiid Documentation

>is it possbile to create a vdb xml file where I define to import all catalog from RDBMS Database without define all table in the source.xmi file?

Yes, it is possible. XMI files come into picture only when you are using the Designer based VDBs. In the Dynamic VDB or DDL VDB you do not. A simple VDB like below

<vdb name="inventory" version="1">
    <model visible="true" name="test">
        <property name="importer.schemaPattern" value="inventory"/>       
        <property name="importer.useFullSchemaName" value="false"/>       
        <source name="Inventory" translator-name="mysql5" connection-jndi-name="java:/inventoryDS"/>
        <metadata type = "NATIVE"/> 
    </model>
</vdb>

can fetch metadata from a MySQL database at the time of VDB deployment, and place it in the "test" model.  Similar can be done also DDL based VDB. Look at the above DDL link I provided.

Hopefully above is enough information, if not please let us know.

Thank you for using Teiid.

Ramesh..

[parrotola]

Hi Ramesh,

thank you for all. I would like to create a vdb xml in a Java Standalone Application to deploy on JBoss Teiid Server. I tried this code:

    <model name="oracleins" type="PHYSICAL">
        <property name="importer.schemaPattern" value="public"/>
        <property name="importer.useFullSchemaName" value="false"/>
        <property name="importer.tableTypes" value="TABLE,VIEW"/>
        <source name="oracleins" translator-name="oracle" connection-jndi-name="java:/oracle"/>
    </model>

But It doesnt't work with oracle Connection and File translator. I had an errror about metadata. Are there a "how to" to configure every DB without set every table?

Best regards.

Francesco

[rareddy]

See this test how configure data sources and deploy vdb using the admin api teiid/IntegrationTestDeployment.java at master · teiid/teiid · GitHub

[parrotola]

Hi Ramesh,

thank you for you help.

Is there a way to create ORACLE Db configuration (as Dynamic VDB xml) without set every table? at the same time with file?

Francesco

[rareddy]

I am not sure what you are asking I gave you complete example as to how to create data source and VDB programmatically already.