JBoss.orgCommunity Documentation

Chapter 10. Managing VDBs

10.1. Creating a VDB
10.2. Editing a VDB
10.3. Test a VDB
10.4. Multi-source Binding Support
10.5. UDF support
10.6. Reusing VDBs
10.7. Security and Data Access
10.8. Working with Dynamic VDBs
10.8.1. Generate VDB Archive
10.8.2. Generate Dynamic VDB

As stated in the introduction, the critical artifact for Teiid Designer the VDB, or Virtual DataBase. This section describes the details of how to create, edit, deploy and test your VDBs.

To create an empty VDB launch Eclipse's New wizard, open the Teiid Designer category folder and select Teiid VDB. You can also select one or more models in a model project, right-click and select New > Teiid VDB action.>

Launching this wizard will open the New VDB dialog. If you launched with one or more models selected the dialog will contain the pre-selected models for inclusion in the new VDB.


Note

A VDB is scoped to be aware of models and files within the same model project as the VDB. You will not be allowed to add models to a VDB that exist in a different project.

To Edit an existing VDB, select the VDB in the explorer and right-click select Open action or simply double-click the VDB. The VDB will be opened in a VDB Editor. (See the Section D.3.2, “VDB Editor” section)

For details on how to test your VDB, see Section 11.3, “Testing With Your VDB” section

Teiid Designer now supports the Teiid feature of defining relational source models and binding them to multiple data sources.

Multi-source models can be used to quickly access data in multiple sources with homogeneous metadata. When you have multiple instances using identical schema, Teiid can help you gather data across all the instances, using "multi-source" models. In this scenario, instead of creating/importing a model for every data source, one source model is defined to represents the schema and is configured with multiple data "sources" underneath it. During runtime when a query issued against this model, the query engine analyzes the information and gathers the required data from all sources configured and gathers the results and provides in a single result. Since all sources utilize the same physical metadata, this feature is most appropriate for accessing the same source type with multiple instances

The VDB editor's Models tab now contains a simplified model table on the left and a new tabbed panel on the right containing Model Details and Source Binding Definition tabs. Click the Multi-source check box if you wish to add additional source bindings. Note that each binding must be defined with a unique Source Name as well as unique JNDI Name representing a deployed data source you your server.


In Teiid Designer you can create, manage and use User Defined Functions (UDFs). These functions allow you to perform simple or complex java operations on your data during runtime. This is accomplished by deploying your custom UDF jars on your server and creating a scalar function representation of your function method to use in your view transformation. In the VDB Editor, you have the option of including your UDF jars as part of the VDB artifact. If included in the VDB, the jars will automatically be deployed to the server for you when the VDB is deployed.

The figure below illustrates a sample project setup which includes a UDF jar in a lib folder under a project. When a model defining a UDF is added to a VDB, each function is interrogated and it's referenced UDF jar (if available) is added to the VDB as well as shown in the UDF Jars tab in the editor.


Teiid 8.1 introduced the ability to treat your deployed VDB as just another database where the database category is your VDB name and each visible model in your VDB is treated as a schema. This is accomplished via a new <import-vdb> element in the vdb.xml definition. (see Teiid VDB Reuse section). By allowing VDB's to referenced other VDBs, users can create reusable database components and reduce the amount of modeling required to create complex transformations.

The sample vdb.xml file below highlights the <import-vdb> element and the corresponding import-vdb-reference within the view model's <model> element.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb version="1" name="PartssupplierViewsVDB">
    <property value="false" name="preview"/>
    <import-vdb import-data-policies="false" version="1" name="PartssupplierSourcesVDB"/>
    <model visible="true" type="VIRTUAL" name="PartsViewModel" path="/PartssupplierProject/PartsViewModel.xmi">
        <property value="1623826484" name="checksum"/>
        <property value="Relational" name="modelClass"/>
        <property value="false" name="builtIn"/>
        <property value="655076658.INDEX" name="indexName"/>
        <property value="PartssupplierSourcesVDB" name="import-vdb-reference"/>
    </model>
</vdb>

Teiid Designer exposes this capability by allowing users to import metadata from deployed VDBs via the JDBC Import option. Through this import, relational VDB source models are created which structurally represent the Catalog (VDB), Schema (Model) and Tables in Virtual DataBase.

When dealing with the these VDB source models there are some limitations or rules, namely:

  • VDB source models are read-only

  • VDB source model name is determined by the deployed model name (schema) from the VDB it was imported from

  • Model names have to be unique within a model project

    • VDB source models have to be imported/created in a project different than the project used to create and deploy the Reuse VDB

  • The JDBC Import Wizard will restrict your options to comply with these rules

To create a VDB source model:

  • Step 1 - Deploy your VDB

  • Step 2 - Launch the JDBC Import Wizard via the "Import > Teiid Designer > JDBC Database >> Source Model" action

  • Step 3 - On the first page of the wizard create/select a valid connection profile for your deployed VDB.

    • The wizard will detect that the connection profile is a Teiid VDB connection and a section will be displayed on the wizard page titled Teiid VDB Source Options

    • If Import as VDB source model is NOT checked, then the wizard will continue importing as a normal JDBC import

  • Step 4 - On the 3rd page, titled Select Database Objects, select a single schema to use to create as VDB source model.

    Note

    The schema names are the names of the visible models in your deployed VDB.

  • Step 5 - The final page shows the name of the resulting VDB source model and the name is NOT editable.

    • All other options are disabled

    • The target Into Folder must NOT contain a model with the same name or the Finish button will be disabled

You can use your VDB source model like any other source model in your project. VDB source model tables can be used in your transformation queries and the view models will contain model imports to your VDB source models. However, when your view model is added to a VDB, any referenced VDB source models do NOT get added to your VDB. Instead, an <import-vdb> element (described above) reference is added in it's place.

If VDB imports exist for a VDB, the Show Import VDBs button will be enabled and allow viewing the names of the imported VDBs as shown below. Note that the dialog now supports editing of the version number for each referenced VDB.

You have some options on defining your data access security for your VDB via the VDB Editor (See the Section D.3.2, “VDB Editor” section).

The first level is provided by the model visibility check-box in the Models section (Spyglass column). If unchecked, that model and it's contents will not be returned by the Teiid runtime with the standard JDBC metadata.

The next level of security is provided defining permissions for your data roles which can be managed via the lower panel in the VDB Editor. For a unique data role, each model and most objects within that model can have specific values of data access including:

  • Security (Row-based condition and column masking)

  • Create

  • Read

  • Update

  • Delete

  • Execute

  • Alter

The image below is an example of the Permissions defined for a data role.


Teiid provides 2 security concepts for object level permissions: (see Permissions)

  • Row-Based Security is a permission against a fully qualified table/view/procedure may also specify a condition.

    • Unlike the allow CRUD actions defined above, a condition is always applied - not just at the user query level. The condition can be any valid SQL referencing the columns of the table/view/procedure. Procedure result set columns may be referenced as proc.col. The condition will act as a row-based filter and as a checked constraint for insert/update operations.

    • An example of a condition might be: column1=user()

  • Column Masking is "a permission against a fully qualified table/view/procedure column may also specify a mask and optionally a condition."

    • When the query is submitted the roles are consulted and the relevant mask/condition information are combined to form a searched case expression to mask the values that would have been returned by the access. Unlike the CRUD allow actions defined above, the resulting masking effect is always applied - not just at the user query level. The condition and expression can be any valid SQL referencing the columns of the table/view/procedure. Procedure result set columns may be referenced as proc.col.

    • An example of a mask might be: CASE WHEN column1=user() THEN column1 END

You can define Row and Column Based security in Designer's Data Row Wizard. By double-clicking a target table, view, procedure or column in the Models table, the appropriate editor dialog will be displayed. Note those objects that already have existing security defined are highlighted in blue.

If a table, view or procedure is double-clicked, the Row Filter Definition dialog is displayed. Enter a valid SQL condition and specify whether or not this filter should be treated as a constraint or not.


If a column is double-clicked, the Row Filter Definition dialog is displayed. Enter valid column masking SQL expression, an optional order (see Teiid documentation) and an option condition expression.


You can also edit these these values via the Add, Edit and Remove buttons on the respective Row Filter and Column Masking tabs



This section desribes the Designer features which allow generating a VDB archive with XMI models from existing dynamic VDBs (*-vdb.xml) files and generating dynamic VDBs from existing VDB archives.