-
Language:
English
-
Language:
English
Red Hat Training
A Red Hat training course is available for Red Hat JBoss Data Virtualization
User Guide Volume 1: Teiid Designer
This guide is for developers.
Red Hat Customer Content Services
Abstract
Chapter 1. Read Me
1.1. Back Up Your Data
Warning
1.2. Variable Name: EAP_HOME
EAP_HOME
refers to the root directory of the Red Hat JBoss Enterprise Application Platform installation on which JBoss Data Virtualization has been deployed.
1.3. Variable Name: MODE
MODE
will either be standalone
or domain
depending on whether JBoss Data Virtualization is running in standalone or domain mode. Substitute one of these whenever you see MODE
in a file path in this documentation. (You need to set this variable yourself, based on where the product has been installed in your directory structure.)
1.4. Red Hat Documentation Site
Chapter 2. Some Key Terms Used Throughout this Document
2.1. What is Teiid Designer?
2.2. What is Metadata?
- Named ZIPCode
- Numeric
- A string
- Nine characters long
- Located in the StreetAddress table
- Comprised of two parts: The first five digits represent the five ZIP code numbers, the final four represent the ZIP Plus Four digits if available, or 0000 if not
- Formatted only in integer numeric characters. Errors will result if formatted as 631410.00 or 6314q0000
- What information does the metadata contain? (See Section 2.4, “Business and Technical Metadata”)
- What data does the metadata represent? (See Section 2.11, “Source and View Metadata”)
- How will my organization use and manage this metadata? (See Section 2.7, “Design-Time and Runtime Metadata”)
2.3. Metadata Models
2.4. Business and Technical Metadata
- Technical metadata describes the information required to access the data, such as where the data resides or the structure of the data in its native environment.
- Business metadata details other information about the data, such as keywords related to the meta object or notes about the meta object.
Note
2.5. Technical Metadata
- Named ZIPCode
- Nine characters long
- A string
- Located in the StreetAddress table
- Uses SQL Query Language
2.6. Business Metadata
- The first five characters represent the five ZIP code numbers, the final four represent the ZIP Plus Four digits if available, or 0000 if not
- The application used to populate this field in the database strictly enforces the integrity of the data format
2.7. Design-Time and Runtime Metadata
2.8. Design-Time Metadata
2.9. Runtime Metadata
- derives the runtime metadata from a consistent set of metadata models.
- creates a subset of design-time metadata, focusing on the technical metadata that describes the access to underlying enterprise information systems.
- optimizes runtime metadata for data access performance.
2.10. What are Models?
.xmi
(for example, NorthwindOracle.xmi
) which adheres to the XMI syntax defined by the OMG.
Figure 2.1. Sample Model File
Note
Figure 2.2. Model Internals
- Relational Tables and Views
- XML
- Web services
- Functions
2.11. Source and View Metadata
2.12. Compact Node Type Definition
2.13. Java Content Repository
- read/write access to information,
- the ability to structure files in hierarchical manner,
- the ability to work with structured/unstructured content,
- the ability to search and query,
- versioning of information, and
- the ability to control access to the content.
2.14. ModeShape Tools
2.15. The Virtual Database
Part I. Teiid Designer
Chapter 3. Introduction to Teiid Designer
3.1. Why Use Teiid Designer?
- resolve semantic differences
- create virtual data structures at a physical or logical level
- use declarative interfaces to integrate, aggregate, and transform the data on its way from source to target formats which is compatible and optimized for consumption by your applications
- Web Services/SOAP/XML
- JDBC/SQL
- ODBC/SQL
3.2. Modeling Your Source Metadata
- Identification of datatype
- Storage formats
- Constraints
- Source-specific locations and names
3.3. Modeling Your View Metadata
- You can expose only the information relevant to an application. The application uses this View Metadata to resolve its queries to the ultimate physical data storage.
- You can add content to existing applications that require different views of the data by adding the View Metadata to the existing View Metadata that application uses. You save time and effort since you do not have to create new models nor modify your existing applications.
- Your applications do not need to refer to specific physical enterprise information systems, offering flexibility and interchangeability. As you change sources for information, you do not have to change your end applications.
- The View Metadata models document the various ways your enterprise uses the information and the different terminology that refers to that information. They do so in a central location.
Chapter 4. : Models
4.1. Guiding through the process
- Consuming a SOAP Web Service
- Modelling from a Flat File Source (a text file)
- Modelling from a JDBC Data Source
- Modelling from a Local XML File Source
- Modelling from a Remote XML File Source
- Modelling from a Teiid Data Source (deployed on server)
- Connecting to a Teiid Server
4.2. Model Classes and Types
- Relational - Model data that can be represented in table columns and records form. Relational models can represent structures found in relational databases, spreadsheets, text files, or simple Web services.
- XML - Model that represents the basic structures of XML documents. These can be backed by XML Schemas. XML models represent nested structures, including recursive hierarchies.
- XML Schema - W3C standard for formally defining the structure and constraints of XML documents, as well as the datatypes defining permissible values in XML documents.
- Web Services - which define Web service interfaces, operations, and operation input and output parameters (in the form of XML Schemas).
- Function - The Function metamodel supports the capability to provide user defined functions, including binary source jars, to use in custom transformation SQL statements.
4.3. VDB Content and Structure
.vdb
file extension. VDBs are structurally just ZIP archive files containing 3 folders:
- META-INF - contains
vdb.xml
definition file. - runtime-inf - contains a binary INDEX file for each model included in your VDB.
- project folder - contains of the models you will be adding in the VDB Editor (that is,
*.xmi
and*.xsd
files)
- VDB name, version, properties
- contained model information (name, translator name, connection info)
- translator info
- data role definitions for the referenced models
- import VDB references
vdb.xml
file for you and assists in synchronizing your workspace models with any related models in your VDB. (See Section E.8.1, “VDB Editor”)
4.4. Model Validation
Note
4.5. Testing Your Models
- Relational table or view, including tables involving access patterns
- Relational procedure
- Web Service operation
- XML Document staging table
Note
Chapter 5. Model Object Extensions
5.1. Model Object Extensions
- Eliminate need for separate EMF metamodel.
- Simpler approach including reduction of extendable metamodels and metamodel objects (Relational, Web Services, XML Document, User Defined Functions) and replacing EMF terminology with basic object types.
- Allows metamodels to be extended by multiple MEDs.
- MEDs are stored in models so no added dependency needed in VDB.
5.2. Model Extension Definition (MED)
- Namespace Prefix - a unique identifier. Typically only a small number of letters and can be used as an abbreviation for the namespace URI.
- Namespace URI - a unique URI.
- Extended Metamodel URI (Model Class) - the metamodel URI that is being extended. Each metamodel URI also has model class and that is typically what is shown in the Designer. The model classes supported for extension are: Relational, Web Service, XML Document, and Function.
- Version - (currently not being used)
- Description - an optional description or purpose.
- Extended Model Object Types (Metaclasses) - a set of model object types, or metaclasses, that have extension properties defined.
- Properties - the extension property definitions grouped by model object type.
mxd
. A MED schema file (see attached modelExtension.xsd
file) is used to validate a MED file. Here is a sample MED file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <modelExtension xmlns:p="http://org.teiid.modelExtension/2011" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" metamodelUri="http://www.metamatrix.com/metamodels/Relational" namespacePrefix="mymodelextension" namespaceUri="org.my.extension.mymodelextension" version="1" xsi:schemaLocation="http://org.teiid.modelExtension/2011 modelExtension.xsd" xmlns="http://org.teiid.modelExtension/2011"> <p:description>This is my model extension</p:description> <p:extendedMetaclass name="com.metamatrix.metamodels.relational.impl.BaseTableImpl"> <p:property advanced="false" index="true" masked="false" name="copyable" required="false" type="boolean"> <p:description locale="en_US">Indicates if table can be copied</p:description> <p:display locale="en_US">Copyable</p:display> </p:property> </p:extendedMetaclass> </modelExtension>
.mxd
file in the Extension Editor.
5.3. Model Extension Definition Registry (MED Registry)
- Built-In MED - these are registered during Teiid Designer installation. These MEDs cannot be updated or unregistered by the user.
- User Defined MED - these are created by the user. These MEDs can be updated, registered, and unregistered by the user.
Chapter 6. Server Management
6.1. Targeting the JBoss Data Virtualization Server
6.1.1. Targeting the JBoss Data Virtualization Server
6.1.2. Server Version Preference
Figure 6.1. Default Server Version Preference
6.1.3. Defining JBoss Data Virtualization Server
- Configure New JBoss Server
- Edit JBoss/Teiid Instance Properties
- Set the Default JBoss/Teiid Instance
- Refresh Teiid Instance
Figure 6.2. Teiid Server Category in the Guides View
Note
6.1.4. Server Version Status Panel
Figure 6.3. Default Server Status Panel
6.2. Setting up a Server
Figure 6.4. Server View with no created servers
Figure 6.5. New Server Configuration
Figure 6.6. Server View with a single server
Figure 6.7. Editor for Configuring the Teiid Server
6.3. Connect JBDS to a Remote Red Hat JBoss Data Virtualization Server
standalone.xml
file does not work.
Note
Chapter 7. Teiid Designer Examples
7.1. Teiid Designer Examples
- GuidesThe Guides View is a good starting point for many common modeling tasks. The view includes categorized Modeling Actions and also links to Cheat Sheets for common tasks. The categorized Modeling Actions simply group together all of the actions that you'll need to accomplish a task. You can launch the actions directly from the Guides view, rather than hunting through the various Teiid Designer menus.
- Cheat SheetsThe Cheat Sheets go beyond even the categorized Action Sets, and walk you step-by-step through some common tasks. At each step, the data entered in the previous step is carried through the process when possible.
7.2. Guide View
- Define Teiid Model Project
- Create JDBC connection
- Create source model for JDBC data source
- Preview Data
- Define VDB
- Execute VDB
7.3. Model a JDBC Source
Open Guides View
To open the Teiid Designer's Guides view, on the main menu, click Window >Show View > Other... and then click Teiid Designer > Guidesview in the dialog.The Guides view is shown below, with the Model JDBC Source Action Set selected:Figure 7.1. Guides View
Define Teiid Model Project
- The Define Teiid Model Project action launches the New Model Project wizard. In the Action Set list, double-click the action (or select the action, then click Execute selected action). The wizard is launched as shown below:Enter a project name, for example, MyProject in the Name field. Then click Next.
Figure 7.2. New Project Wizard
- The next page of the wizard is shown below:
Figure 7.3. New Project Folders
- Under Create Folders, clear schemas and web_services. We will not need them for this example. Now, click Finish to exit the wizard. The project has now been created.Your Model Explorer view will look like this:
Figure 7.4. Model Explorer
Create JDBC connection
The Create JDBC connection action will create the Connection profile for your database. The connection profile defines the properties and driver to be used when connecting to the database. In the Action Set list, double-click the action (or select it, then click Execute selected action). The wizard is launched as shown below:Figure 7.5. Connection Profile Name and Type
Select the type of database that you are connecting to (for example, MySQL), and enter a name for the connection profile, for example, TestMySQL. Click Next.Figure 7.6. Connection Profile properties
Now, select the driver and enter the login properties for your database. Click Finish to complete the profile creation.Create source model for JDBC data source
- The Create source model for JDBC data source action will now utilize the Connection profile that you have created, to import the metadata from the database to create your Teiid Source Model. In the Action Set list, double-click the action (or select it, then click Execute selected action). The wizard is launched as shown below:
Figure 7.7. Select Connection Profile
- On this page, select the TestMySQL Connection profile that you created in the previous step. Click Next.
Figure 7.8. Select Database Metadata
- On this page, select the database metadata that you want to import. When finished, click Next.
Figure 7.9. Select Database Objects
- On this page, select the specific objects from the database that you want to import. When finished, click Next.
Figure 7.10. Import Options
- Finally, choose the name for the model to be created (defaults to
profileName.xmi
). The Into Folder field defines the target location for your new model. Select theMyProject/sources
folder. Now, click Finish. The source model has now been created. Your Model Explorer view will look like this:Figure 7.11. Model Explorer
Preview Data
All execution capabilities in Teiid Designer (Preview Data, VDB execution) require you to connect to a running JBoss Data Virtualization Server. See teiid-view for instructions on establishing a JBoss Data Virtualization Server connection. Once you are connected to a JBoss Data Virtualization Server, you can proceed with the following steps.- The Preview Data action allows you to preview a sample of data rows from your source. In the Action Set list, double-click the action (or select it, then click Execute selected action).
- In the dialog, select the source table you want to preview, as shown below:
Figure 7.12. Select Preview Table
- After selecting the table, click OK. Now, the preview results will be displayed:
Figure 7.13. Preview Results
Define VDB
- The Define VDB action allows you to create a VDB (Virtual Database) artifact for deployment to a JBoss Data Virtualization Server. In the Action Set list, double-click the action (or select it, then click Execute selected action). The following dialog is displayed:
Figure 7.14. New VDB
- In the dialog, select the target In Folder location where the VDB will be placed. Enter a Name for the VDB, for example myVDB. Finally, select the models that will be included in the VDB. When finished, click Finish. The VDB will be created in your Teiid Model Project as shown in the following figure.
Figure 7.15. Model Explorer
Execute VDB
The Execute VDB action allows you to execute your VDB and run sample queries against it. In the Action Set list, double-click the action (or select it, then click Execute selected action). In the dialog, select the VDB you want to execute, then click OK. The VDB will be deployed and executed, and the perpective will switch to the Database Development perspective. You can now run queries against the VDB, as show in the following example:Figure 7.16. Execute VDB Example
7.4. Cheat Sheets
7.5. Consume a SOAP Web Service
Open the Cheat Sheet
You can access the Cheat Sheet from the Designer Menu. From the Designer main menu, select Window > Show View > Other..., then select Help > Cheat Sheets in the dialog.Alternately, you can access the Cheat Sheet from the Guide View. A sample Guide view is shown below, with the Consume a SOAP Web Service Action Set selected:Figure 7.17. Guides View
To open the Cheat Sheet from the Guide View, expand the Cheat Sheet section in the lower portion of the Guide View, then select the Consume a SOAP Web Service link.Begin the Cheat Sheet
The Consume a SOAP Web Service Cheat Sheet is shown below:Figure 7.18. Consume SOAP Web Service Cheat Sheet
To start the Cheat Sheet process, expand the Introduction section, then select Click to Begin. The Create New Teiid Model Project section opens, as shown.Figure 7.19. Create Model Project
Note
Each section of the sheet has basic instructions outlining what to do at each step.Click next to Launch New Teiid Model Project Wizard to launch the New Project wizard. Follow the wizard to create a new Model Project. For this example, we will useSOAPProj
for our project name. On the second page of the wizard, select the sources and views folders. Click Finish. The new project is created.In the Cheat Sheet, you can advance to the next step - once the wizard has completed. Click to advance to the next step.Create SOAP Web Service Connection
This section of the Cheat Sheet provides instructions for creating a connection profile for the SOAP Web Service, as shown below:Figure 7.20. Create SOAP Connection Profile
Click next to Launch Create SOAP Connection Profile Wizard to launch the wizard. The first page of the wizard is shown below:Figure 7.21. Create SOAP Connection Profile
The Web Services Data Source (SOAP) profile type will be selected. Enter CountryInfoConn for the profile name, then click Next. The next page of the wizard is shown below:Figure 7.22. SOAP Connection Properties
The connection profile properties are entered on this page. Click the URL... button, then enter the following URL: http://www.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL. Select None for SecurityType, then click OK to complete the wizard. In the Cheat Sheet, you can now continue - once the wizard has completed. Click to advance to the next step.Create Models from SOAP Connection
This section of the Cheat Sheet provides instructions for creating relational models using the previously created connection profile for the SOAP Web Service, as shown below:Figure 7.23. Create Models from SOAP Connection
Click next to Launch the Consume SOAP Web Service Wizard to launch the wizard. The first page of the wizard is shown below:Figure 7.24. Consume SOAP Wizard
For Connection Profile, select the previously created CountryInfoConn profile. The available WSDL Operations will then be displayed under Select the desired WSDL Operations. Select only the first CapitalCity Operation for this example. Click Next to proceed to the next page, as shown below:Figure 7.25. Consume SOAP Wizard
On the Model Definition page, the source and view model info section will be pre-filled. We will keep the names and location defaults for the source and view models. Click Next to proceed to the next page, as shown below:Figure 7.26. Consume SOAP Wizard
On the Procedure Definition page, the CapitalCity Operation will be selected since it is the only one used for this example. On the Request tab, select the sCountryISOCode element - then click the Add button. This will add the selected element to the request. Now select the Response tab, as shown below:Figure 7.27. Consume SOAP Wizard
On the Response tab, select the Body sub-tab. In the Schema Contents, select the CapitalCityResult, and then click the Add button. This will add the selected element to the response. Select the Wrapper Procedure tab to see the full Generated Procedure SQL, as shown below.Figure 7.28. Consume SOAP Wizard
Click Finish to exit the wizard. In the Cheat Sheet, you can now continue. Click to advance to the next step.Create VDB
This section of the Cheat Sheet provides instructions for creating a VDB using the models that you created in the previous step. The Cheat Sheet section is shown below:Figure 7.29. Create VDB
Click next to Launch New VDB Wizard to launch the wizard. Follow the steps to create a VDB in your workspace. When complete, exit the wizard. In the Cheat Sheet, you can now continue. Click to advance to the next step.Test VDB
This final section of the Cheat Sheet provides instructions for executing the VDB created in the previous step. Click next to Launch Execute VDB Dialog to launch the wizard. Select the previously created VDB to execute it.
Chapter 8. New Model Wizards
8.1. Launch New Model Wizards
- Click File > New... > Metadata Model action .
- Select a project or folder in the Model Explorer View and choose the same action in the right-click menu.
- Select the New button on the main toolbar and select the Metadata Model action .
Note
8.2. Create New Relational Source Model
8.2.1. Create a New Relational Source Model
- Launch the New Model Wizard.
- Specify a unique model name.
- Select Relational option from Model Class drop-down menu.
- Select Source Model from Model Type drop-down menu.
- Click Finish.
Note
8.2.2. Generate File Translator Procedures
- Select the model builder labeled Generate File Translator Procedures and click Next >. The Generate File Translator Procedures dialog will be displayed.
- Select one or more of the Available File Translator Procedures, then click Finish.
8.2.3. Generate Web Service Translator Procedures
- Select the model builder labeled Generate Web Service Translator Procedures and click Next >. The Generate Web Service Translator Procedures dialog will be displayed.
- Select one or more of the Available Web Services Translator Procedures, then click Finish.
8.2.4. Copy from an Existing Relational Source Model
- Select the model builder labeled Copy from existing model of the same model class and click Next >. The Copy Existing Model dialog will be displayed.
- Select an existing relational model from the workspace using the browse button.
Note
An existing model will be preselected if a relational model in the workspace is selected in the model-explorer-view prior to starting the new model wizard. - Select the Copy all descriptions option if desired. Click Finish.
8.3. Create a New Relational View Model
8.3.1. Create a New Relational View Model
- Launch the New Model Wizard.
- Specify a unique model name.
- Select Relational option from Model Class drop-down menu.
- Select View Model from Model Type drop-down menu.
- Click Finish.
Note
- Copy from existing model of the same model class.
- Transform from existing model.
8.3.2. Copy an Existing Relational View Model
- Select the model builder labeled Copy from existing model of the same model class and click Next >. The Copy Existing Model dialog will be displayed.
- Select an existing relational model from the workspace using the browse button.
- Select the Copy all descriptions option if desired. Click Finish.
8.3.3. Transform from an Existing Relational View Model
SELECT * FROM SourceModel.Table_X
) for each source table. The steps are the same as for the Copy from Relational View Model section described above.
8.4. Create a New XML Document View Model
8.4.1. Create a New XML Document View Model
- Launch the New Model Wizard.
- Specify a unique model name.
- Select XML option from Model Class drop-down menu.
- Select View Model from Model Type drop-down menu.
- Click Finish.
Note
- Copy from existing model of the same model class.
- Build XML documents from XML schema.
8.4.2. Copy an Existing XML Document View Model
- Select the model builder labeled Copy from existing model of the same model class and click Next >. The Copy Existing Model dialog will be displayed.
- Select an existing relational model from the workspace using the browse button.
- Select the Copy all descriptions option if desired. Click Finish.
8.4.3. Build an XML Document View Model
- Select the model builder labeled Build XML documents from XML schema and click Next >. The Select XML Schema dialog will be displayed.
- Select an existing schema model from the workspace using the browse button.
Note
An existing model will be preselected if an XSD model in the workspace is selected in the VDB explorer prior to starting the new model wizard. The schema must be found in the workspace so if you need to get one or more into the workspace use the XSD Schemas on file system importer. - Move the available schema root elements you want to become virtual documents in the new model over to the Virtual Documents list by using the arrow button to move all elements.
- Select the appropriate document options and mapping options. Click Finish.
- Click Finish to create a model of all selected document entities or (optional) click Next > to view Selected Documents Statistics page which shows document entity statistics and gives you an idea the size of the model being created.
- (Optional) Click Finish to create a model of all selected document entities or click Next > to view Preview Generated Documents page that allows you to exclude document specific entities then click Finish.
Note
For deeply nested schema, your total entity count may be large. If so, displaying the preview may take some time.
8.5. Create a New XML Schema Model
8.5.1. Create a New XML Schema Model
.xsd
) model:
- Launch the New Model Wizard.
- Specify a unique model name.
- Select XML Schema (XSD) option from Model Class drop-down menu.
- Select Datatype Model from Model Type drop-down menu.
- Click Finish.
Note
- Copy from existing model of the same model class.
8.5.2. Copy an Existing XML Schema Model
- Select the model builder labeled Copy from existing model of the same model class and click Next >. The Copy Existing Model dialog will be displayed.
- Select an existing relational model from the workspace using the browse button.
- Select the Copy all descriptions option if desired. Click Finish.
8.6. Create a New Web Service View Model
8.6.1. Create a New Web Service View Model
- Launch the New Model Wizard.
- Specify a unique model name.
- Select Web Service option from Model Class drop-down menu.
- Select View Model from Model Type drop-down menu.
- Click Finish.
Note
- Copy from existing model of the same model class.
- Build from existing WSDL file(s) or URL.
8.6.2. Copy an Existing Web Service View Model
- Select the model builder labeled Copy from existing model of the same model class and click Next >. The Copy Existing Model dialog will be displayed.
- Select an existing relational model from the workspace using the browse button.
- Select the Copy all descriptions option if desired. Click Finish.
8.6.3. Create a Web Service View Model from a WSDL File or URL
- Select the model builder labeled Build from existing WSDL file(s) or URL and click Next >.
- The remaining wizard steps are identical to those found using the wsdl-to-web-service-import section action option.
8.6.4. Create a Web Service View Model from Relational Models
8.6.5. Create a Web Service View Model from XML Document View Models
- Select either a single XML Document or single XML Document root in the Model Explorer View.
- Right-click select Modeling > Create Web Service action .
- Fill in missing properties in Web Service Generation Wizard shown below.
- Click Finish to generate model. When model generation is complete, a confirmation dialog is displayed. Click OK.
Note
Chapter 9. Importers
9.1. Importers
Figure 9.1. Import Wizard
9.2. Import DDL
- In Model Explorer, click File > Import action in the toolbar or select a project, folder or model in the tree and click Import...
- Select the import option Teiid Designer > DDL File >> Source or View Model and click Next>.
- Select existing DDL from either Choose from file system... or Choose from workspace.... Set the Model folder location, enter or select valid model name, set Model type (Source Model or View Model), set desired options and click NEXT> (or Finish if enabled)
Figure 9.2. DDL Import Options
- If you click NEXT>, a difference report is presented for viewing or deselecting individual relational entities. Click Finish to complete.
Figure 9.3. Import DDL Dialog
9.3. Import From JDBC Database
Note
- In Model Explorer, Click File > Import action in the toolbar or select a project, folder or model in the tree and click Import...
- Select the import option Metadata Modeling > JDBC Database >> Source Model and click Next>.
- Select existing or previous connection profile from the drop-down selector or click New... button to launch the New Connection Profile dialog or Edit... to modify/change an existing connection profile prior to selection.
Note
The Connection Profile selection list will be populated with only JDBC Database connections.Figure 9.4. Select JDBC Source Configuration Dialog
Because JDBC databases are different, special processing of your metadata to be required in order to convert datatypes or to interpret your metadata. The JDBC Metadata Processor drop-down selector will be selected automatically based on your selected connection profile. Special processors are available for DB2, ModeShape, ODBC, Oracle, PostgeSQL, SQL Server and Sybase. For all other DBs a default JDBC processor is available. - After selecting a Connection Profile, enter the password (if not provided). Click Next> (or Finish if enabled).
Figure 9.5. Select JDBC Source Configuration Dialog
- On the Select Database Metadata page, select the types of objects in the database to import. Click Next> (or Finish if enabled).
Figure 9.6. Select Database Metadata Dialog
- On the Select Database Objects page, view the contents of the schema, or change selections. Select which database schema objects will be used to construct relational objects. Click Next> (or Finish if enabled).
Figure 9.7. Select Database Options Dialog
- On the Specify Import Options page, specify desired Model Name as well as any other options used to customize the naming of your relational objects. Click Finish to complete.
Figure 9.8. Specify Import Options Dialog
- During the Finish processing, a monitor will be displayed providing feedback on the import progress.
Figure 9.9. JDBC Import Progress Dialog
9.4. Import From Teiid Data Source Connection
Procedure 9.1. Import From Teiid Data Source Connection
- Go to the Model Explorer.
- Choose the File - Import action in the toolbar.Select a project, folder or model in the tree and then select Import...
- Select Teiid Designer - Teiid Connection - Source Model and then click Next.
- Select the datasource to use for the import.You can create a new source if the one you want does not already exist. You can also undertake other source management functions here.
- Click Next.
- Select the appropriate translator for your data source type as well as defined the target relational model that you wish to create or update and then click Next.
- When you move to next page of the wizard, a temporary dynamic vdb is actually deployed to your server and the schema for your data source is retrieved in DDL form. This DDL is displayed (and you can also be exported if you so desire). Click Next.
- On the final page of the wizard, a difference report is presented for viewing or de-selecting individual relational entities. Press Finish to complete.
9.5. Import From Flat File Source
getTextFiles()
procedures supported by JBoss Data Virtualization.
getTextFiles()
procedure from your source model as well as the column definitions and parameters required for the Teiid TEXTTABLE()
function used to query the data file. You can also choose to update an existing view model instead of creating a new view model.
TEXTTABLE
function processes character input to produce tabular output. It supports both fixed and delimited file format parsing. The function itself defines what columns it projects. The TEXTTABLE
function is implicitly a nested table and may be correlated to preceding FROM clause entries.
TEXTTABLE(expression COLUMNS <COLUMN>, ... [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS name
SELECT A.Name, A.Sport, A.Position, A.Team, A.City, A.StateCode, A.AnnualSalary FROM (EXEC PlayerDataSource.getTextFiles('PlayerData.txt')) AS f, TEXTTABLE(f.file COLUMNS Name string, Sport string, Position string, Team string, City string, StateCode string, AnnualSalary string HEADER 2 SKIP 3) AS A
- In Model Explorer, click File > Import action in the toolbar or select a project, folder or model in the tree and click Import...
- Select the import option Teiid Designer > File Source (Flat) >> Source and View Model and click Next>.
Figure 9.10. Import from Flat File Source
- Select existing or previous connection profile from the drop-down selector or click New... button to launch the New Connection Profile dialog or Edit... to modify or change an existing connection profile prior to selection. Note that the Flat File Source selection list will be populated with only Flat File connection profiles.After selecting a Connection Profile, the file contents of the folder defined in the connection profile will be displayed in the Available Data Files panel. Select the data file you wish to process. The data from this file, along with your custom import options, will be used to construct a view table containing the required SQL transformation for retrieving your data and returning a result set.Lastly enter or unique source model name in the Source Model Definition section at the bottom of the page or select an existing source model using the Browse button. Note the Model Status section which will indicate the validity of the model name, whether the model exists or not and whether the model already contains the
getTextFiles()
procedure. In this case, the source model nor the procedure will be generated.When finished with this page, click Next>.Figure 9.11. Data File Source Selection Page
- The next page, titled Flat File Column Format Definition, requires defining the format of your column data in the file. The options are Character delimited and Fixed width. This page contains a preview of the contents of your file to aid in determining the format. The wizard defaults to displaying the first 20 lines, but you can change that value if you wish.When finished with this page, click Next>.
Figure 9.12. Data File Source Selection Page
- Character Delimited Option - The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file. This page presents a number of options you can use to customize the Generated SQL Statement, shown in the bottom panel, for the character delimited option. Specify header options (Column names in header, header line number and first data line number), Parse selected row, changed character delimiter and edit the
TEXTTABLE()
function options.If columns names are not defined in a file header or if you wish to modify or create custom columns, you can use the ADD, DELETE, UP, DOWN to manage the column info in your SQL.When finished with this page, click Next>.Figure 9.13. Flat File Delimited Columns Options Page
To aid in determining if your parser settings are correct you can select a data row in your File Contents Preview section and click the Parse Selected Row button. A dialog will be displayed showing the list of columns and the resulting column data. If your column data is not what you expected, you'll need to adjust your settings accordingly.Figure 9.14. Parse Column Data Dialog
- Fixed Column Width Option - The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file. This page presents a number of options you can use to customize the Generated SQL Statement , shown in the bottom panel, for the fixed column width option. Specify header options (Column names in header, header line number and first data line number), Parse selected row, changed character delimiter and edit the
TEXTTABLE()
function options. See the Teiid User's Guide for details on theTEXTTABLE()
function.If columns names are not defined in a file header or if you wish to modify or create custom columns, you can use the ADD, DELETE, UP, DOWN to manage the column info in your SQL.You can also utilize the cursor position and text length values in the upper left panel to determine what your column widths are in your data file.When finished with this page, click Next>.Figure 9.15. Flat File Fixed Columns Width Options Page
- On the View Model Definition page, select the target folder location where your new view model will be created. You can also select an existing model for your new view tables. Note the Model Status section which will indicate the validity of the model name, whether the model exists or not. Lastly, enter a unique, valid view table name. Click Finish to generate your models and finish the wizard.
Figure 9.16. View Model Definition Page
getTextFiles()
procedure.
Figure 9.17. Generated Flat File Procedures
getTextFiles
procedure above and the Teiid TEXTTABLE()
function. The following figure is an example of a generated view table.
Figure 9.18. Generated Flat File View Table
9.6. Import From XML Data File Source
getTextFiles()
procedure or invokeHTTP()
procedure which are both supported by JBoss Data Virtualization.
getTextFiles()
procedure from your source model as well as the column definitions and parameters required for the Teiid XMLTABLE()
function used to query the data file. You can also choose to update an existing view model instead of creating a new view model.
XMLTABLE
function uses XQuery to produce tabular output. The XMLTABLE
function is implicitly a nested table and may be correlated to preceding FROM clause entries. XMLTABLE
is part of the SQL/XML 2006 specification.
XMLTABLE([<NSP>,] xquery-expression [<PASSING>] [COLUMNS <COLUMN>, ... )] AS name
COLUMN := name (FOR ORDINALITY | (datatype [DEFAULT expression] [PATH string]))
SELECT A.entryDate AS entryDate, A.internalAudit AS internalAudit FROM (EXEC CCC.getTextFiles('sample.xml')) AS f, XMLTABLE(XMLNAMESPACES('http://www.kaptest.com/schema/1.0/party' AS pty), '/pty:students/student' PASSING XMLPARSE(DOCUMENT f.file) COLUMNS entryDate FOR ORDINALITY, internalAudit string PATH '/internalAudit') AS A
- In Model Explorer, click File > Import action in the toolbar or select a project, folder or model in the tree and choose Import...
- Select the import option Teiid Designer > File Source (XML) >> Source and View Model and click Next>
Figure 9.19. Import from XML File Source
- The next page of the wizard allows selection of the XML Import mode that specifies whether the XML file is local or remote. The description at the top describes what operations this wizard will perform. Select either the XML file on local file system or XML file via remote URL and click Next>
Figure 9.20. XML Import File Options Page
- Select existing or previous connection profile from the drop-down selector or press New... button to launch the New Connection Profile dialog or Edit... to modify/change an existing connection profile prior to selection.When creating a New XML Connection Profile, you can either create from XML File URL or REST Web Services URL . XML File URL option enables conncetions to file or URL based XML documents. REST Web Services URL option enables connections to any REST-based web service.After selecting a Connection Profile, the XML data file from the connection profile will be displayed in the Available Data Files panel. Select the data file you wish to process. The data from this file, along with your custom import options, will be used to construct a view table containing the required SQL transformation for retrieving your data and returning a result set.Lastly enter the unique source model name in the Source Model Definition section at the bottom of the page or select an existing source model using the Browse button. Note the Model Status section which will indicate the validity of the model name, whether the model exists or not and whether the model already contains the
getTextFiles()
procedure. In this case, the source model nor the procedure will be generated.When finished with this page, click Next>.Figure 9.21. XML Data File Source Selection Page
- The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file. This page presents a number of options you can use to customize the Generated SQL Statement, shown in the bottom panel. The to panel contains an XML tree view of your file contents and actions/buttons you can use to create column entries displayed in the middle, Column Information panel.To create columns, select a root XML element and right-click select Set as root path action. This populates the root path value. Next, select columns in the tree that you wish to include on your query. You can modify or create custom columns, by using the ADD, DELETE, UP, DOWN to manage the column info in your SQL.Note that the Path property value for a column is the selected element's path relative to the defined root path. If no root path is defined all paths are absolute. Each column entry requires a datatype and an optional default value. See the Teiid User's Guide for details on the
XMLTABLE()
function.When finished with this page, click Next>.Figure 9.22. XML File Delimited Columns Options Page
- On the View Model Definition page, select the target folder location where your new view model will be created. You can also select an existing model for your new view tables. Note the Model Status section which will indicate the validity of the model name, whether the model exists or not. Lastly, enter a unique, valid view table name. Click Finish to generate your models and finish the wizard.
Figure 9.23. View Model Definition Page
9.7. Import From Salesforce
Note
- In Model Explorer click File > Import action in the toolbar or select a project, folder or model in the tree and click Import...
- Select the import option Teiid Designer > Salesforce >> Source Model and click Next>
- Select existing or previous connection profile from the drop-down selector or click New... button to launch the New Connection Profile dialog or Edit... to modify or change an existing connection profile prior to selection. Note that the Connection Profile selection list will be populated with only Salesforce connection profiles.
Figure 9.24. Select Salesforce Credentials Dialog
- After selecting a Connection Profile, enter the password (if not provided). Click Next> to display the Salesforce Objects selection page.
Figure 9.25. Select Salesforce Objects Dialog
- On the Target Model Selection page, specify the target folder location for your generated model, a unique model name and select desired import options. Click Next> (or Finish if enabled).
Figure 9.26. Target Model Selection Dialog
- If you are updating an existing relational model, the next page will be Review Model Updates page. Any differences. Click Finish to create your models and tables.
Figure 9.27. Review Model Updates Dialog
- When finished, the new or changed relational model's package diagram will be displayed showing your new tables.
Figure 9.28. New Saleforce Tables Diagram
9.8. Import Metadata From Text File
9.8.1. Import Metadata From Text File
- In Teiid Designer, Click File > Import action and then click Import...
- Select the import option Teiid Designer > Designer Text File >> Source or View Models and click Next>.
- Select an import type from the drop-down menu shown below.
Figure 9.29. Import Wizard
- Relation Model Text Import
- Relational Table Text Import
- Virtual Table Text Import
- Datatype Table Text Import
9.8.2. Import Relational Model (XML Format)
- Select the Relational Model (XML Format) import type, then click Next >.
Figure 9.30. Select Import Type - Relational Model (XML Format)
- On the next page, select the XML file on your local file system via the Browse... button. Select a target model to which the imported relational objects will be added via the second Browse... button. The dialog allows selecting an existing relational model or creating a new model. Note the contents of your selected XML file will be display in the File Contents viewer. Click Finish.
Figure 9.31. Select Source Text File and Target Relational Model Page
- If the target model contains named children (tables, views, procedures) that conflict with the objects being imported, a dialog will be displayed giving you options on how to proceed including: replacing specific existing objects, creating new same named objects or cancel import entirely.
Figure 9.32. Duplicate Objects Dialog
9.8.3. Import Relational Tables (CSV Format)
- Select the Relational Tables (CSV Format) import type, then click Next >.
Figure 9.33. Select Import Type - Relational Tables (CSV Format)
- In the next page, you'll need to provide a source text file containing the metadata formatted to the specifications on the previous page.
Figure 9.34. Select Source Text File and Target Relational Model
- Select an existing relational model as the target location for your new relational components using the Browse... button to open the Relational Model Selector dialog. Select a relational model from your workspace or specify a unique name to create a new model.
- Select any additional options and click Finish.
9.8.4. Import Relational View Tables (CSV Format)
- Select the Relational Virtual Tables (CSV Format) import type, then click Next >.
Figure 9.35. Select Import Type - Relational Virtual Tables (CSV Format)
- In the next page, you'll need to provide a source text file containing the metadata formatted to the specifications on the previous page.
Figure 9.36. Select Source Text File and Target Virtual Relational Model
- Select an existing relational virtual model as the target location for your new model components using the Browse... button to open the Virtual Model Selector dialog. Select a virtual relational model from your workspace or specify a unique name to create a new model.
- Click Finish.
9.8.5. Import Datatypes (CSV Format)
- Select the Datatypes (CSV Format) import type, then click Next >.
Figure 9.37. Select Import Type - Datatypes (CSV Format)
- In the next page, you'll need to provide a source text file containing the metadata formatted to the specifications on the previous page.
Figure 9.38. Select Source Text File and Datatypes Model
- Select an existing datatype model as the target location for your new model components using the Browse... button to open the Datatypes Model Selector dialog. Select a datatypes model from your workspace or specify a unique name to create a new model.
- Select any additional options and click Finish.
9.9. Import WSDL into Relational Models
9.9.1. Import WSDL into Relational Models
- In Model Explorer click the File > Import action in the toolbar or select a project, folder or model in the tree and click Import...
- Select the import option Teiid Designer > WSDL File or URL >> Source and View Model (SOAP) and click Next>.
- On the next page select an existing Web Service Connection Profile from the list, or click the New button to create a new profile.
Figure 9.39. WSDL Source Selection
- Select individual Web Service Operations to model. The default behavior of this page selects all available operations in the tree. Operations can be deselected if they are not being modeled. The Selection Details panel displays static information about the operation such as the names of the input and output messages, and faults thrown by the operation.Click Next >.
- The next page entitled Model Definition requires both a model location (i.e. folder or project) and a valid model name for both source and view models. Use the Browse... button to select existing folders or models. Click Next> when all the information is defined.
Figure 9.40. WSDL Source Selection
- This wizard generates both request and response procedures that are used in the queryable wrapped procedure. The next page, Procedure Definition, provides the means to define the details of your request and response structures.In the Request tab, select and double-click the schema elements you wish to be input parameters for your request. These will be added to the Element Info panel and the resulting generated SQL statement will be updated to reflect the new element. Note the BODY and HEADER tabs which exist on both the Request and Response tabs. If the selected service mode for this procedure is set to MESSAGE, the HEADER tab will be enabled and allow you to define the SOAP header variables utilizing the same schema tree.Select the Response tab and create the response procedures result set columns in the same way.Repeat this process for all operations by changing the selection target operation via the Operations selector at the top.
Figure 9.41. Procedure Definition Page
- Click Finish. After generation the new models can be found in the specified location in your workspace.
9.9.2. Circular References in WSDL Schemas
StackOverFlow
exception and exiting of the application.
WsdlSchemaHandlerRecursiveDepth
to a larger value, for example, -D WsdlSchemaHandlerRecursiveDepth=800
. This should only be used with caution as on some systems it is possible the JVM throws a StackOverFlow
exception before the new depth limit is reached.
Figure 9.42. Warning message displayed if depth limit has been reached
9.10. Import WSDL Into Web Service
9.10.1. Import WSDL Into Web Service
- Workspace Location
- File System Location
- URL
9.10.2. Import WSDL from Workspace Location
- Click the File > Import action in the toolbar or select a project, folder or model in the tree and click Import...
- Select the import option Teiid Designer > WSDL File or URL >> Web Service Model option shown below and click Next>.
- Enter a valid name for your Web Service model and click the Workspace... button. Locate your workspace WSDL file in the selection dialog and click OK>. Click Next> to continue.
Figure 9.43. WSDL File Selection Dialog
Figure 9.44. WSDL File Workspace Selection Dialog
Note
- If no WSDL is selected or specified then the importer will only create an empty Web Service model. No XML Schema or XML View models will be generated.
- Any referenced files (WSDLs or schemas) must either be embedded in the WSDL file or exist on your file system.
- The next page is titled Namespace Resolution. This page identifies successful and errant WSDL namespace resolution. The main WSDL document will essentially always be resolved, since the workspace file chooser is used to obtain the path. Problems will occur when the main WSDL file imports other WSDL files that cannot be resolved. If no errors, click Next to proceed, or Finish (if enabled) to complete with default options.
Figure 9.45. Namespace Resolution Dialog
- The next page WSDL Operations Selection allows customizing the resulting content of your Web Service model by selecting/deselecting various operations and interfaces in the following dialog.
Figure 9.46. Namespace Resolution Dialog
- The next page is titled Schema Workspace Location Selection. This page lists all schemas imported by the WSDL (along with any dependent schemas referenced within schemas) as well as schemas embedded in the WSDL and indicates whether or not they are resolvable. All resolved schemas will be created in a separate file and added to the workspace. The editor panel allows you to change the default file name of the new schema file(s).If no errors, click Next to proceed, or Finish to complete with default option.
Figure 9.47. Namespace Resolution Dialog
- The last page titled XML Model Generation allows you to change the name of the XML View model if the Generate virtual XML document model is checked. Enter desired name or use the default name provide. Click Finish to complete.
Figure 9.48. Namespace Resolution Dialog
Figure 9.49. WSDL Validation Problems Dialog
9.10.3. Import WSDL from File System Location
- Click the File > Import action in the toolbar or select a project, folder or model in the tree and click Import...
- Select the import option Teiid Designer > WSDL File or URL >> Web Service Model and click Next>.
- Input a valid name for your Web Service model and click the File System... button. Locate your file system WSDL file in the selection dialog and click OK>.
Figure 9.50. WSDL File Selection Dialog
Note
- If no WSDL is selected or specified then the importer will only create an empty Web Service model. No XML Schema or XML View models will be generated.
- Any referenced files (WSDLs or schemas) must either be embedded in the WSDL file or exist on your file system.
- The next page is titled Namespace Resolution. This page identifies successful and errant WSDL namespace resolution. The main WSDL document will essentially always be resolved, since the workspace file chooser is used to obtain the path. Problems will occur when the main WSDL file imports other WSDL files that cannot be resolved. If no errors, click Next to proceed, or Finish (if enabled) to complete with default options.
Figure 9.51. Namespace Resolution Dialog
- The next page WSDL Operations Selection allows customizing the resulting content of your Web Service model by selecting/deselecting various operations and interfaces in the following dialog.
Figure 9.52. Namespace Resolution Dialog
- The next page is titled Schema Workspace Location Selection. This page lists all schemas imported by the WSDL (along with any dependent schemas referenced within schemas) as well as schemas embedded in the WSDL and indicates whether or not they are resolvable. All resolved schemas will be created in a separate file and added to the workspace. The editor panel allows you to change the default file name of the new schema file(s).If no errors, click Next to proceed, or Finish to complete with default option.
Figure 9.53. Namespace Resolution Dialog
- The last page titled XML Model Generation allows you to change the name of the XML View model if the Generate virtual XML document model is selected. Enter the desired name or use the default name provided. Click Finish to complete.
Figure 9.54. Namespace Resolution Dialog
Figure 9.55. WSDL Validation Problems Dialog
9.10.4. Import WSDL from URL
- Click the File > Import action in the toolbar or select a project, folder or model in the tree and click Import...
- Click the import option Teiid Designer > WSDL File or URL >> Web Service Model and click Next>.
- Input a valid name for your Web Service model and click the URL... button.
- Enter a valid WSDL URL. If the URL cannot be validated then an error will be displayed and the OK> button will be disabled.
- If the WSDL is protected by basic HTTP authentication then this option should be selected and the appropriate username and password entered.
- Click OK> to continue.
Click Next> to continue.Figure 9.56. WSDL URL Dialog
Note
- If no WSDL is selected or specified then the importer will only create an empty Web Service model. No XML Schema or XML View models will be generated.
- Any referenced files (WSDLs or schemas) must either be embedded in the WSDL file or exist on your file system.
- The next page is titled Namespace Resolution. This page identifies successful and errant WSDL namespace resolution. The main WSDL document will essentially always be resolved, since the workspace file chooser is used to obtain the path. Problems will occur when the main WSDL file imports other WSDL files that cannot be resolved. If no errors, click Next to proceed, or Finish (if enabled) to complete with default options.
Figure 9.57. Namespace Resolution Dialog
- The next page WSDL Operations Selection allows customizing the resulting content of your Web Service model by selecting/deselecting various operations and interfaces in the following dialog.
Figure 9.58. Namespace Resolution Dialog
- The next page is titled Schema Workspace Location Selection. This page lists all schemas imported by the WSDL (along with any dependent schemas referenced within schemas) as well as schemas embedded in the WSDL and indicates whether or not they are resolvable. All resolved schemas will be created in a separate file and added to the workspace. The editor panel allows you to change the default file name of the new schema file(s).If no errors, click Next to proceed, or Finish to complete with default option.
Figure 9.59. Namespace Resolution Dialog
- The last page titled XML Model Generation allows you to change the name of the XML View model if the Generate virtual XML document model is checked. Enter desired name or use the default name provide. Click Finish to complete.
Figure 9.60. Namespace Resolution Dialog
Figure 9.61. WSDL Validation Problems Dialog
9.11. Import Data from REST Services
Procedure 9.2. Import Data from REST Services
- Go into the Model Explorer.
- Choose the File - Import action from the toolbar.
Note
Alternatively, you can select a project, folder or model from the tree and choose Import... - Select the import option Teiid Designer - File Source (XML) - Source and View Model and click Next.
- Select XML file via remote URL and click Next.
- Select an existing or previous connection profile from the drop-down selector.Alternatively, press the New... button to launch the New Connection Profile dialog or Edit... to modify or change an existing connection profile prior to selection.
- If you are creating a new connection profile, choose the REST Web Services URL option when prompted and then click the OK button.
- Name your new REST Connection Profile and click Next.
- Set your REST URL in the Connection URL text field and your Security Type and credentials, if applicable. Click Next to see a summary of your properties or click Finish to end.There is also an option to add Request Header parameters. The importer assumes an Accept header value of application/xml and a Content-Type header value of application/xml. These defaults can be overriden in the Optional Request Header Properties section. You can also add any other header properties required for the service. Click the Test Connection button to validate your connection properties.
- After selecting or creating a new Connection Profile, the REST XML result from the connection profile will be displayed in the Available Data Files panel. Check the data file you wish to process.The data from this web service, along with your custom import options, will be used to construct a view table with the required SQL transformation for querying your data and returning a result set.
- Enter a unique source model name in the Source Model Definition section at the bottom of the page.Alternatively, you can select an existing source model using the Browse button.
Note
The Model Status section which will indicate the validity of the model name, whether the model exists or not and whether the model already contains the getTextFiles() procedure. In this case, the source model nor the table will be generated. - Click Next.
- The primary purpose of this importer is to help you create a view procedure containing the transformations required to query the user-defined data file. The panel contains an XML tree view of your result contents and actions/buttons you can use to create column entries displayed in the middle, Column Information panel.The root path is used for xpath parsing of the result document. The importer sets a root path for you. You can change the root path, if needed, by selecting an XML element and right-click select Set as root path action.Next, select columns in the tree that you wish to include on your query and select Add button. You can also modify or create custom columns, by using the ADD, DELETE, UP, DOWN to manage the column info in your SQL.
- Click Next.
- On the View Model Definition page, select the target directory location. This is where your new view model will be created. (You can also select an existing model for your new view tables.)
Note
The Model Status section which will indicate the validity of the model name, whether the model exists or not. - Enter a unique, valid view table name.
- Click Finish to generate your models and finish the wizard.
9.12. Lightweight Directory Access Protocol (LDAP)
9.13. LDAP in Teiid Designer
- LDAP subtrees are represented as if they were tables in a relational database.
- Each node in the subtree is represented as a row in the table.
- Each attribute of the given node can be represented as a column in the table.
- The RDN (or DN) can be used to represent a primary key.
9.14. Create a Relational Source Model from LDAP Data
Procedure 9.3. Create a Relational Source Model from LDAP Data
- Go into the Model Explorer.
- Choose the File - Import action in the toolbar.Alternatively, you can select a project, folder or model in the Tree and choose Import...
- Select the import option by clicking Teiid Designer - LDAP Service - Source Model and then click Next.
- Select an existing or previous LDAP connection profile from the drop-down selector.Alternatively, click the New... button to launch the New Connection Profile dialog or Edit... to modify or change an existing connection profile prior to selection.
Note
You must supply the following information when you create a new connection:- A connection Username and password - this is an administrator account to browse the ldap tree, eg. cn=Manager,dc=birds-of-prey,dc=org
- A connection URL, for example ldap://falcon:389
- Principal Distinguished Name (DN) Suffix - this is the root DN of the ldap tree
- An LDAP Connection Factory implementation class, for example com.sun.jndi.ldap.LdapCtxFactory
When you select the connection profile, it automatically populates the LDAP Service URL and DN Suffix fields. The remaining requirements for the wizard page is the choosing of a suitable model file as the destination of the imported tables. If the selection is an existing model then the wizard will merge the new tables with the model's current content. - After selecting a Connection Profile, click Next.
- On the Select LDAP Entries to be modeled as tables page, select the LDAP entries from the tree to be created as tables in the source model. (Select entries by ticking their respective checkboxes in the tree.)If you highlight an entry, the following information about it is displayed:
- Table Name - this is the table's label and can be modified to a more readable value
- Table Source Name - the fully qualified entry name. This is not editable in the wizard and should remain unchanged in the subsequently created source model
- Table Source Name Suffix - an additional suffix can be added that further limits the scope of the table's search criteria. The suffix is in the format of ?search_scope?objectClass_name where search_scope is one of OBJECT_SCOPE (first and only one entry returned), ONELEVEL_SCOPE (only entries directly below the selected entry are returned) or SUBTREE_SCOPE (recursively return all entries below the selected entry) and objectClass_name is the name of a specific type of objectClass in the LDAP tree, eg. return only the 'inetOrgPerson' entries. Both criteria are optional (but the '?'s are not) so it is possible to have a suffix such as ? ? inetOrgPerson.
- Click Next.
- The Select the LDAP Attributes to be modeled as columns page displays the previously selected LDAP entries and the attributes of their child entries. The purpose of a selected attribute is to be created as a column in the relevant source model table. Select attributes by ticking their respective checkboxes in the tree.If you highlight an entry, the following information about it is displayed:
- Column Name - this is the column's label and can be modified to a more readable value
- Column Source Name - the real LDAP attribute name. This is not editable in the wizard and should remain unchanged in the subsequently created source model
- Column Distinct Value Count - The number of distinct values assigned to the specific attribute in the LDAP service. This value is useful in optimising queries using the source model. This is not editable in the wizard and should remain unchanged in the subsequently created source model.
- Column Null Value Count - The number of entries where the specific attribute has no value assigned in the LDAP service. This value is useful in optimising queries using the source model. This is not editable in the wizard and should remain unchanged in the subsequently created source model.
- Column Length - The maximum length of existing values assigned to the attribute in the LDAP service. This value is assigned as the maximum length of the column. This is not editable in the wizard but can be edited in the source model later should this be required.
- Click Finish.
9.15. LDAP Connector Update Capabilities
- Supports Update table property - to enable updates, each source model table must have this property set to 'true';
- Updateable column property - to enable updates, each column in the source model table must have this property set to 'true';
- You also need these additional columns:
- DN - for all update types (INSERT, UPDATE, and DELETE), the distinguished name must be modeled as a column, setting the name in source to dn. For UPDATE and DELETE capability, the DN must be specified in the criteria clause while for INSERT, the DN must be one of the column values to be set.
- objectClass - for INSERT, the objectclass must be modelled as a column, setting the name in source to objectClass. It must also be one of the column values to be set.
- additional - each entry defined in the LDAP directory’s schema may also have one or more additional required columns. This is dependent on the LDAP server implementatation so consult the LDAP documentation accordingly.
SELECT * FROM LdapModel.People
INSERT INTO LdapModel.People (dn, sn, objectclass, Name) VALUES ('cn=JoeYoung,ou=people,dc=example,dc=org','Young','person', 'Joe Young')
UPDATE LdapModel.People SET PhoneNumber='(314) 299-2999' WHERE DN='cn=JoeYoung,ou=people,dc=example,dc=org'
DELETE FROM LdapModel.People WHERE DN='cn=JoeYoung,ou=people,dc=example,dc=org'
9.16. XSD Schema File
- In Model Explorer, click the File > Import action in the toolbar or select a project, folder or model in the tree and choose Import...
- Select the import option Metadata Modeling > XSD Schemas and click Next>.
- Select either Import XSD Schemas from file system or Import XSD Schemas via URL and click Next >.
- If importing from file system, the Import XSD Files dialog is displayed. Click the Browse button to find the directory that contains the XSD file(s) you wish to import.
- To select all of the XSD files in the directory, click the checkbox next to the folder in the left panel.
- To select individual XSD files, click the check boxes next to the files you want in the right panel.
Figure 9.62. Select XSD From File System
- If importing from URL, select the Import XML Schemas via URL option and click OK to display the final Add XML Schema URLs wizard page.
Figure 9.63. Add XML Schema URLs Dialog
- Click the Add XML Schema URL button . Enter a valid schema URL. Click OK. Schema will be validated and resulting entry added to the list of XML Schema URLs.
Figure 9.64. Add XSD Schema URLs
The schema URL is now displayed in the XML Schema URLs list.Figure 9.65. Add XSD Schema URLs
- Click Finish.
Note
XSD files may have dependent files. This importer will determine these dependencies and import these as well if Add Dependent Schema Files is selected.
Chapter 10. Creating and Editing Model Objects
10.1. Creating New Model Objects
10.1.1. Creating New Model Objects
- New Child Action
- New Sibling Action
- New Association Action
10.1.2. New Child Action
- Select the parent object to which you want to add a child. For example, you can add a package to a package or an attribute to a class.
- Right-click on a container object. From the pop-up menu, click New Child. You can now select the child object you would like to add.
Figure 10.1. New Child Action In Model Explorer
- The new model object displays on the Model Explorer view and is highlighted for renaming.
Figure 10.2. New Model Object In Explorer
- Select the parent object to which you want to add a child. For example, you can add a package to a package or an attribute to a class.
- Right-click on a container object. From the pop-up menu, click New Child. You can now select the child object you would like to add.
Figure 10.3. New Child Action In Diagram
- The new model object displays on the diagram and is highlighted for renaming.
Figure 10.4. New Model Object In Diagram
- Select the row for the parent object to which you want to add a child. For example to add a column, click the Base Table tab and select base table row.
- Right-click on a table row. From the pop-up menu, click New Child. You can now select the child object you would like to add.
Figure 10.5. New Child Action In Table Editor
- The selected tab in the Table Editor changes to the tab for the child object type, the new model object row is displayed and the row's name table cell is highlighted for renaming.
10.1.3. New Sibling Action
- Select the object to which you want to add a sibling. For example, you can add a column sibling to a column.
- Right-click on that object. From the pop-up menu, click New Sibling. You can now select the sibling object you would like to add.
Figure 10.6. New Sibling Action In Model Explorer
- The new model object displays in the Model Explorer view and is highlighted for renaming.
- Select the object to which you want to add a sibling. For example, you can add a column sibling to a column.
- Right-click on that object. From the pop-up menu, click New Sibling. You can now select the sibling object you would like to add.
Figure 10.7. New Sibling Action In Diagram
- The new model object displays on the diagram and is highlighted for renaming.
- Select the row for the object to which you want to add a sibling. For example, you can add a column sibling to a column.
- Right-click on a row. From the pop-up menu, click New Sibling. You can now select the sibling object you would like to add.
Figure 10.8. New Sibling Action In Table Editor
- The selected tab in the Table Editor changes to the tab for the child object type, the new model object row is displayed and the row's name table cell is highlighted for renaming.
10.1.4. New Association Action
- Select two objects you wish to associate. For example, select columns in different base tables.
- Right-click. From the pop-up menu, click New Association > Foreign Key Relationship.
Figure 10.9. New Association Action In Model Explorer
- The new relationship link is displayed in the diagram.
Figure 10.10. New Association In Diagram
- Select two objects you wish to associate. For example, select columns in different base tables.
- Right-click. From the pop-up menu, click New Association > Foreign Key Relationship.
- The new relationship link is displayed in the diagram. The Column, Foreign Key, Primary Key reference properties are properly set on the selected columns, new primary key and new foreign key.
- Select a column in table.
- Drag the column to another table and drag over a column and drop onto this column. The target column should highlight in Yellow.
- The new relationship link is displayed in the diagram. The Column, Foreign Key, Primary Key reference properties are properly set on the selected columns, new primary key and new foreign key.
- Select two objects you wish to associate. For example, select columns in different base tables.
- Right-click. From the pop-up menu, select New Association > Foreign Key Relationship.
- New Foreign Key and Primary Key objects will be added to the contents of their respective tabs in the Table Editor. The Column, Foreign Key, Primary Key reference properties are properly set on the selected columns, new primary key and new foreign key.
10.1.5. New Model Objects Wizard
- The Create Relational Table Wizard allows you to create a complete relational table including columns, unique keys, foreign keys definition and indexes.
Note
Note that the relational view wizard is identical to the relational table wizard. - The Create Relational Procedure Wizard allows you to create a complete relational procedure including columns, unique keys, foreign keys definition and indexes. The relational procedure object can represent different types of procedures, including a standard procedure and source function (pushdown function). When the New Child - Procedure... action is launched, the first dialog gives you the option of selecting the procedure type.
Note
You can model the user-defined functions in a view model.The second dialog in this wizard customizes the Create Relational Procedure dialog based on your selected type.Source functions are procedures that are already deployed and accessable on your database. By defining source functions in your relational model, you can call these functions from within your transformation SQL and the functions will be pushed down to your database for execution. - Create Relational Index Wizard: Indexes can be created at the same time as your relational table object or via New Child - Index... action and dialog.
- Create View Model Objects Wizards: For view models, only tables, procedures (standard procedures and user defined functions) and indexes can be created. For view tables and procedures, the primary difference in the wizards is that they include a SQL Transformation tab.
- Create User Defined Functions Wizard: You can define custom functions as view model procedures of the type "function". When you launch the New Child - Procedure... for a view model, the first dialog gives you the option of selecting the procedure type which includes either a standard view procedure or a user-defined function.User Defined Functions require additional properties such as Java Class and Method as well as a path to the jar file containing the code.
10.2. Model Object Editors
- Cut - Deletes the selected object(s) and copies it to the clipboard.
- Copy - Copies the selected object(s) to the clipboard.
- Paste - Pastes the contents of the clipboard to the selected context.
- Clone - Duplicates the selected object in the same location with the same name; user is able to rename the new object right in the tree.
- Delete - Deletes the selected object(s).
- Rename - Allows a user to rename an object.
In addition to the New Child/Sibling/Association menus available for object creation Teiid Designer provides a Modeling sub-menu which presents various object-specific actions which can be performed.
Figure 10.11. Modeling Sub-Menu for Source Table
Figure 10.12. Modeling Sub-Menu for Source Table
10.3. Transformation Editor
10.3.1. Transformation Editor
- Double-click Edit.
- A relational view table or procedure in the Model Explorer or Diagram Editor
- A transformation node in a transformation diagram or mapping transformation diagram
- A transformation node in a transformation diagram or mapping transformation diagram
- Right-click Edit action on selected object in the Model Explorer, Diagram Editor or Table Editor.
- A relational view table or procedure
- A transformation node in a transformation diagram or mapping transformation diagram
- A mapping class in a mapping diagram or mapping transformation diagram
Figure 10.13. Editing String Property
- Disable specific update transformation types on this virtual class.
- Start your transformation with a provided SQL Template.
- Build or edit a criteria clause to use in your transformation.
- Build or edit an expression to use in your transformation.
- Find and replace a string within your transformation.
- Validate the transformation to ensure its content contains no errors.
- Reconcile target attributes to ensure the symbols in your transformation match the attributes in your virtual metadata class.
- The Transformation Editor toolbar actions are summarized below.
- Preview Virtual Data - executes a simple preview query for the target table or procedure of the transformation being edited.
- Search Transformations - provides a simple way select and edit another transformation based SQL text search criteria.
- Edit Transformation - provides a simple way to change which transformation to edit without searching in a diagram or the Model Explorer. Click the action and select from a list of views, tables, procedures or operations from the currently edited model.
- Cursor Position (line, column) - shows the current line and column position of the insertion cursor. For example, Cursor Position(1,4) indicates that the cursor is presently located at column 4 of line 1.
- Supports Update - checkbox allows you to enable or disable updates for the current transformation target. If Supports Update is selected, the editor shows four tabs at the bottom for the Select, Update, Insert and Delete transformations. If Supports Update is cleared, all updates are disabled and only the Select transformation is displayed.
- Reconcile - allows you to resolve any discrepancies between the transformation symbols and the target attributes. Clicking this button will display the Reconcile Virtual Target Attributes dialog box in which you can resolve discrepancies.
- Save/Validate - saves edits to the current transformation and validates the transformation SQL. Any Warning or Error messages will be displayed at the bottom of the editor in the messages area. If the SQL validates without error, the message area is not displayed.
- Criteria Builder - allows you to build a criteria clause in your transformation. The button will enable if the cursor position is within a query that allows a criteria. Pressing the button will launch the Criteria Builder dialog. If the Criteria Builder is launched inside an existing criteria, that criteria will be displayed for edit, otherwise the Criteria Builder will be initially empty.
- Expression Builder - allows you to build an expression within your transformation. The button will enable if the cursor position is at a location that allows an expression. Pressing the button will launch the Expression Builder dialog. If the Expression Builder is launched inside an existing expression, that expression will be displayed for edit, otherwise the Expression Builder will be initially empty.
- Expand Select * - allows you to expand a SELECT * clause into a SELECT clause which contains all of the SELECT symbols. The button will enable only if the cursor is within a query that contains a SELECT * clause that can be expanded.
- Increase Font Size - increases the font size of all editor text by 1.
- Decrease Font Size - decreases the font size of all editor text by 1.
- Show/Hide Messages - toggles the display of the message area at the bottom of the transformation editor.
- Optimize SQL - when toggled ON, will use the short names of all SQL symbols that can be optimized. Some symbol names may remain fully qualified in the event of a duplicate name or if the optimizer is unable to optimize it. When the action is toggled OFF, all symbol names will be fully qualified.
- Import SQL Text - allows you to import an SQL statement from a text file on your file system. Pressing this button will display an import dialog in which you can navigate to the file.
- Export SQL Text - allows you to export the currently displayed SQL statement into a text file on your file system. Pressing this button will display an export dialog in which you can choose the location for export.
- Close X - closes the transformation editor.
- The Transformation Editor context menu can be displayed by clicking the right mouse button within the editor's text area. The context menu is show below:Following is a summary of the context menu actions:
Figure 10.14. Transformation Editor context menu
- Cut - Copy - Paste - Typical text editor actions to cut, copy or paste text within the editor.
- Undo - Redo - Allows you to Undo or Redo the previous action.
- Find - Displays a Find and Replace Dialog which allows you to search and replace text within the transformation.
- Apply Template... - Displays the Choose an SQL Template Dialog, which allows you to choose a starting SQL Template from a list of common SQL patterns. See View Table wizard section for a description of this dialog.
10.3.2. Using the Reconciler
Figure 10.15. Reconciler Dialog
- Target Attributes - SQL Symbol Table: This table shows the target attributes in the left column and the SQL Symbols in the right column. The SQL Symbols are the symbols that are projected from the SQL transformation. A symbol is referred to as being bound to a target attribute when it is displayed next to the attribute.If a target attribute is unbound, its row is highlighted in red. The transformation is not valid until all attributes have a corresponding SQL symbol binding.Here are a few things you can do in the table section:
- Lock Target Attributes: To lock the target attribute ordering, select the Lock Target Attributes checkbox. This will lock the attributes in place.
- Re-Order Attributes: To change the ordering of the target attributes, use the Top, Up, Swap, Down, and Bottom controls beneath the table. Select single or multiple table rows, then click the desired action button.
- Delete Attributes: To delete one or more of the target attributes, select the table row(s) that you want to delete and then click the Delete button.
- Resolve Types: If an Attribute-SQL Symbol binding has a datatype conflict, a message will be displayed. To assist in resolving the datatype conflict, a Datatype Resolver Dialog is provided. Click on the table row, then click the Type Resolver... button to display the dialog. See Using Datatype Resolver section for further information.
- Unmatched SQL Symbols list: This list is to the right of the attribute-symbol binding table, and shows the SQL symbols from the transformation SQL that are not bound to a target table attribute.Here are a few things you can do in the list section:
- Add SQL Symbols: To add SQL Symbols to the list, click the Add button. You will be presented with a dialog showing all available symbols from your transformation source tables. Click on the symbols that you want to add, then click OK.
- Remove or Clear Symbols: To remove one or more of the SQL symbols, select the list items then click the Remove button. To clear the entire SQL symbols list, click the Clear button.
- Sort Symbols: By default, the symbols are shown in the order that they appear in the SQL query. To show them alphabetically in the list, click the Sort button.
- Binding Controls: The Binding Controls are located between the Attribute-Symbol table and the Unmatched SQL Symbols list. Use these buttons to define the Attribute-Symbol bindings.Here are a few things you can do with the binding controls:
- Bind: This button will bind an SQL Symbol to a target attribute. Select an Unmatched SQL symbol and select a target attribute, then click Bind to establish the binding.
- Unbind: This button will unbind an Attribute-Symbol binding. Select an already bound attribute in the table, then click Unbind. The SQL Symbol will be released to the Unmatched Symbols list.
- New: This button will create a new target attribute, using an Unmatched SQL Symbol. Select an Unmatched Symbol from the list, then click New. A new target attribute will be added to the bottom of the Attribute-Symbol table, bound to the selected SQL symbol.
- Null: This button allows you to bind null to a target attribute instead of binding an SQL Symbol to it. Select a row in the Attribute-Symbol table, then click Null. The target attribute will be bound to null. If it was originally bound to an SQL Symbol, the symbol will be released to the Unmatched Symbol list.
- Function: This button allows you to define an expression instead of an SQL Symbol for the binding. To define the expression, select a row in the Attribute-Symbol table, then click the Function button. The Expression Builder Dialog will display, allowing you to define any type of expression. See Using Expression Builder section for further information about the Expression Builder.
- SQL Display: The current transformation SQL is shown at the bottom of the Reconciler dialog. As you add/remove SQL symbols and make other changes, you can see the SQL display change to reflect those changes. When you click OK, this SQL will be your new transformation SQL. If desired, the SQL Display can be hidden by clearing the Show SQL Display checkbox.
10.3.3. Using the Datatype Resolver
Figure 10.16. Datatype Resolver Dialog
- Convert all source SQL symbol datatypes: Click this button to apply a CONVERT function to all of SQL symbols in the table so that their datatype is compatible with the corresponding attribute datatype.
- Change all target column datatypes: If the suggested datatype is not acceptable, click this button to choose your own datatype from the datatype dialog.
- Source SQL Symbol - Matched Datatype Table: This table shows all SQL Symbol datatype information for the selected binding. Select on a table row to populate the lower panel.
- Selected Binding Info: The lower panel shows the binding information for the selected SQL symbol. hows all SQL Symbol datatype information for the selected binding. Select on a table row to populate the lower panel.
10.3.4. Using the Criteria Builder
- In the Transformation Editor, click the Launch Criteria Builder button.
- The Criteria Builder displays.
Figure 10.17. Editing String Property
The two tabs at the top, Tree View and SQL View, show the current contents of the criteria you have built.The Criteria Editor at the bottom allows you to build a criteria clause. To build a criteria clause, you must add information to the left side of the predicate, select a comparison operator, and add a value to the right side. - The radio buttons on either side of the Predicate Editor let you choose what type of content to place in that side of your predicate. Click the radio button of the type of content you want to place in your criteria. You can click:
- Attribute to add an attribute to the predicate. If you click the Attribute radio button, the Predicate Editor looks like this:
Figure 10.18. Attribute Panel
From the tree, select the attribute you want to add to the expression. You can select an attribute from any of the source classes in the transformation. - Constant to add a hardwired constant value to the predicate. If you click this radio button, the Predicate Editor looks like this:
Figure 10.19. Constants Panel
Select the datatype for this constant from the Type drop-down list and enter the value in the Value edit box. - Function to add a function.
Figure 10.20. Functions
Click the Edit button to use the Expression Builder to construct a function to use in the predicate of your SQL Criterion.
- Set a value left side of the predicate and, when necessary, the right side of the predicate. If the right side of the predicate does not require a value of some sort, the Criteria Builder will not let you enter one.
- Click Apply.
- When you have created both a Left Expression and a Right Expression in the Predicate Editor, click Apply to add the criterion to the tree view at the top of the dialog box.
- Delete to remove the selected criterion.
- AND to create a new criterion that must also be true.
- OR to create a new criterion that can be true instead of the selected criterion.
- NOT to establish negative criterion.
10.3.5. Using the Expression Builder
- Attributes by selecting an attribute.
- Constants by selecting the datatype and value.
- Functions from both the standard Teiid Designer SQL functions and your enterprise's custom user defined functions. If you select a function before you launch the Expression Builder, you can use the Expression Builder to edit the selected function; otherwise, you can create a new function from scratch.
Note
The functions made available through the expression builder are described in the Teiid Reference Guide.
- In the Transformation Editor, click the location where you want to insert the function.
- Click the Expression Builder button. The SQL Expression Builder displays.
Figure 10.21. Expression Builder
The two tabs at the top, Tree View and SQL View, show the current contents of the expression you have built. To build an expression, you must specify the type of expression you want to build and populate it. In most cases, you will use the Expression Builder to construct a complex expression. - Click the Function radio button to add a function.
Note
You can simply add constants and attributes as expressions by themselves using the Attribute or Constant radio buttons; however, the Expression Editor is most useful for functions. - The Expression Editor displays the Function editor.
Figure 10.22. Function Panel Selected
From the Category drop-down list, choose the type of function you want to add. By default, the Teiid Designer offers the following categories:- Conversion for functions that convert one datatype into another.
- Datetime for functions that handle date or time information.
- Miscellaneous for other functions.
- Numeric for mathematical and other numeric functions.
- String for string manipulation functions.
Note
Any additional categories represent those containing user defined functions your site has created. - From the Function drop-down list, select the function you want. The table beneath the drop-down lists displays the number of arguments required for this function.
- Click Apply.
- Your function displays in the tree at the top. Sub nodes display for each argument you need to set for this function.
Figure 10.23. New Blank Function Created
You need to set an attribute or constant value for each sub node in the tree to specify the arguments this function needs. You can also nest another function in the tree using the Function editor.Figure 10.24. Nested Function Example
- Click each sub node in the tree and use the editors at the bottom of the dialog box to apply an attribute, constant, or function value to it.
- When you have added values to all nodes, as shown below, click OK to add this expression to your query or Cancel to close the dialog box without inserting the expression.If the OK button does not enable, you have not added a value to all nodes in the tree.
10.4. Input Set Editor (XML)
Figure 10.25. Edit Input Set Button
Figure 10.26. Input Set Editor Panel
- Add a mapping attribute from a parent mapping class to the Input Set. In the tree on the right, select the symbol for which you want to create an attribute and click New. The item displays in the Input Parameters and Mapping Class Bindings table.
- Delete a mapping attribute from the Input Set. Click the row in the Input Parameters and Mapping Class Bindings table that you want to delete and click Delete. The Teiid Designer removes this row from the table and this mapping attribute from your Input Set.
- Bind and Unbind Input Parameters.
10.5. Choice Editor (XML)
10.5.1. Choice Editor (XML)
Figure 10.27. Opening The Choice Editor
Figure 10.28. The Choice Editor
Figure 10.29. Choice Criteria Summary
You can address each choice option by performing one of the following:
- Specify a criteria statement for the Teiid Designer Server to apply in order to determine which elements or elements to insert into the result document.
- Exclude or include the option's fragment from the document.
- Set the criteria test order for elements.
- Set a default action that occurs if none of the criteria you set is met.
10.5.2. Excluding Fragments
10.5.3. Editing Choice Criteria
- In the table on the Choice Editor panel, select the element you want to edit..
- Click Edit Criteria button to launch the Criteria Builder dialog.
- Use the Criteria Builder to create the conditions for which the Teiid Designer Server will test to determine whether to choose this option in the XML instance document.
- Click OK. The criteria you set displays both in the table and in the summary tab.
10.5.4. Setting Choice Element Order
10.5.5. Setting a Default Choice Action
- Any of the options within the table except those you have excluded from the document.
- THROW to throw a Teiid Designer Server exception.
- RECORD to record the Teiid Designer Server exception.
- DISCARD to place no element within the XML instance document.
Note
10.6. Recursion Editor (XML)
10.6.1. What is Recursion?
- The first cycle can be thought of as an entry condition for the recursion. The mapping class located at this node defines a normal mapping transformation like that of any other in the document model.
- The second cycle defines a mapping transformation that will be performed repeatedly until conditions are met that will halt the document instance being generated by the Teiid Designer Server. This fragment of the document model is called the recursive fragment. The mapping transformation for this fragment is no different from the first, except that you can access the first cycle's mapping class attributes, plus you have the opportunity to specify the conditions that will halt the recursion.
10.6.2. Recursion Editor (XML)
- A fixed number of results to the query.
- An SQL based criteria limit condition.
- A combination of both.
Figure 10.30. Open Recursion Editor Button
Figure 10.31. Recursion Editor
10.6.3. Edit Recursion Properties
- Select the Enable Recursion checkbox if you want the Teiid Designer Server to perform the query you specify to generate the nested tags within the XML document.
- Click the arrows beside the Count Limit box to limit the number of times to recursively perform the query. If you do not set a Limit Condition in the text area, the recursion finishes when the query reaches this limit. You can only set this limit to a maximum supported by your Teiid Designer Server. For more information about this limit, contact your system administrator.
- Click the Action When Count Limit Exceeded drop-down menu to instruct the Teiid Designer Server what to do if it encounters more results for the query than the count limit before it reaches the limit condition.
- Click the Edit button to launch the SQL to build a limiting condition for this recursion.
Note
10.7. Operation Editor
- Operations section showing a tree view of Interfaces and Operations contained within the Web Service model.
- Input Variables section providing editing of desired Input Variable declarations.
- Procedure section providing SQL editing of the procedure.
Figure 10.32. Operation Editor
Figure 10.33. Edit Input Variables Dialog
10.8. Managing Model Object Extensions
10.8.1. Managing Model Object Extensions
- Model Extension Definitions (MEDs)
- MED Registry - keeps track of all the MEDs that are registered in a workspace. Only registered MEDs can be used to extend a model.
- MED Editor
10.8.2. Create New MED
Note
Figure 10.34. MED Editor Overview Tab
10.8.3. Edit MED
.mxd
file in your workspace and right-click select the Open action. The MED Editor will open to allow editing .
Figure 10.35. MED Editor Properties Tab
Figure 10.36. Select Model Object Name Dialog
Figure 10.37. Edit Property Definition Dialog
10.8.4. Extending Models With MEDs
.mxd
file and lastly, update the version of MED in a model if it differs from a version in your MED registry.
Figure 10.38. Manage Model Extension Definitions Dialog
Figure 10.39. Add Model Extension Definitions Dialog
Note
10.8.5. Setting Extended Property Values
Figure 10.40. Properties View For Extended Model Object
Chapter 11. User Defined Functions
11.1. User Defined Functions
- Function Name - When you create the function name, remember that:
- You cannot overload existing Teiid System functions.
- The function name must be unique among user defined functions in its model for the number of arguments. You can use the same function name for different numbers of types of arguments. Hence, you can overload your user defined functions.
- The function name cannot contain the '.' character.
- The function name cannot exceed 255 characters.
- Input Parameters- Input Parameter defines a type specific signature list. All arguments are considered required.
- Return Type- the expected type of the returned scalar value.
- Pushdown - Indicates the expected pushdown behavior. It can be one of REQUIRED, NEVER, ALLOWED. If NEVER or ALLOWED are specified then a Java implementation of the function must be supplied. If REQUIRED is used, then user must extend the Translator for the source and add this function to its pushdown function library.
- invocationClass/invocationMethod- optional properties indicating the method to invoke when the UDF is not pushed down.
- Deterministic - if the method always returns the same result for the same input parameters. Defaults to false. It is important to mark the function as deterministic if it returns the same value for the same inputs as this leads to better performance.
11.2. Modeling your functions
Figure 11.1. VDB UDF Jar Files
- Function Category - unique name for a grouping of user defined functions. This category is displayed in the Expression Builder wizard so you can locate and select your function.
- Java Class - fully qualified name of the java class containing your function method.
- Java Method - the name of the function method in your java class.
- UDF Jar Path - the relative path in your project for the jar containing your UDF which will be in a lib folder in your project. If you are defining your jar for the first time and there is no jars in your lib folder, the property editor will allow you to select a jar from your file system. If one or more jars already exist in a lib folder, the editor will allow you to choose between selecting from your workspace or your file system.
11.3. Utilizing your Functions in Transformations
11.4. Including Functions in your VDB
Figure 11.2. VDB UDF Jar Files
Chapter 12. Metadata-specific Modeling
12.1. Relational Source Modeling
12.1.1. Source Function
Figure 12.1. New Source Function Action
Figure 12.2. New Source Function Action
Figure 12.3. Create New Source Function Dialog
Figure 12.4. New Source Function In Package Diagram
12.1.2. Create Relational Table Wizard
Figure 12.5. New Relational Table Wizard Action
Figure 12.6. Properties Tab
Figure 12.7. Columns Tab
Figure 12.8. Primary Key Tab
Figure 12.9. Foreign Keys Tab
Figure 12.10. Create Foreign Key Dialog
12.2. Relational View Modeling
12.2.1. Create Materialized Views
Figure 12.11. Materialized Table Properties
- Right-click on one or more view tables in the Model Explorer View and select the Modeling > Create Materialized Views action.
Figure 12.12.
- In the Create Materialized View Model dialog specify or select a target relational model for your generated relational tables.
Figure 12.13.
- Selecting the Browse ... button displays the Relational Model Selector dialog where you select an existing relational model or specify a unique name for a new model.
Figure 12.14.
- Click OK to create relational tables corresponding to your selected view tables and automatically set the Materialized property to TRUE and the Materialized Table reference value to your newly generated table.
Figure 12.15. Materialized Table Properties
12.2.2. Create Relational View Table Wizard
Figure 12.16. New View Table Wizard Action
Figure 12.17. Properties Tab
Figure 12.18. Columns Tab
Figure 12.19. Transformation SQL Tab
Figure 12.20. SQL Templates Dialog
12.3. XML Document Modeling
12.3.1. Create XML View Documents from Schema
Figure 12.21. Create XML View Documents Action
Figure 12.22. Select or Create XML View Model Dialog
Figure 12.23. Build XML Documents From XML Schema Dialog
12.4. Web Services Modeling
12.4.1. Create Web Service Action
- Select any combination of relational models, tables and/or procedures in the Model Explorer view tree.
Note
It is recommended that the user selects single source models, which enables auto-naming of input/output schema and Web service models in Step 3. - Right-click select Modeling > Create Web Service action. .
Figure 12.24. Create Web Service Action
- In the Create Web Service dialog, specify file names for the generated Input Schema file, Output Schema file and Web service model. Change options as desired. Click Finish when done.
Figure 12.25. Create Web Service Dialog
- When model generation is complete, a confirmation dialog appears. Click OK.
Figure 12.26. Generation Completed Dialog
12.4.2. Web Services War Generation
12.4.2.1. Web Services War Generation
12.4.2.2. Generating a JBossWS-CXF War
- Right-click on the VDB containing your web service model(s) and select the Modeling > Generate JBossWS-CXF War action.
Figure 12.27.
- Fill in missing properties in Web Service War Generation Wizard shown below.
Figure 12.28. Generate a JBossWS-CXF War Web Service Dialog
Table 12.1. Field Descriptions
Field Name Description Name The name of the generated war file. Host The server host name (or IP). Port The server port. VDB JNDI Name The JNDI connection name to the deployed Teiid source VDB. Security options- None - no username/password required to connect to the VDB through the generated web service.
- HTTP Basic - the specified security realm and role will be used. The default realm value is the realm that comes out of the box with JBoss Data Virtualization (teiid-security). The role needs to be defined in the appropriate security mechanism. In the case of Teiid, use the
teiid-security-roles.properties
file. When using HTTPBasic, a local Teiid connection using the PassthroughAuthentication property is required. - WS-Security - a password callback class will be generated for you which will validate that the username/password values you specified in the war generator dialog are passed in. This is meant to be a testing mechanism for your WS-Security enabled web service and your own security mechanism should be implemented in this class. All source code is included in the generated war along with the compiled class files.
Target namespace This is the target namespace that will be used in the generated WSDL and subsequent generated web service classes. MTOM (Message Transmission Optimization Mechanism) If selected, MTOM will be enabled for the web service endpoint(s). You will also need to update your output schema accordingly by adding the xmlns:xmime="http://www.w3.org/2005/05/xmlmim" schema and adding type="xs:base64Binary"xmime:expectedContentTypes="application/octet-stream" to the output element you wish to optimize. War File Save Location The folder where the generated WAR file should be saved. - Click OK to generate the web service war. When war generation is complete, a confirmation dialog should appear. Click OK.
Figure 12.29. Generation Completed Dialog
- Click OK to create relational tables corresponding to your selected view tables and automatically set the Materialized property to TRUE and the Materialized Table reference value to your newly generated table.
Figure 12.30. Materialized Table Properties
12.4.2.3. Generating a RESTEasy War
http://{host}:{port}/{war_context}/{model_name}/resource
will accept URI path parameters and/or XML while http://{host}:{port}/{war_context}/{model_name}/json/resource
will accept URI path parameters and/or JSON. You can specify query parameters in the target REST procedure's URI property using & as a delimiter. For example, REST:URI = authors&parm1&parm2
.
- In a virtual model, add a procedure(s) that returns an XMLLiteral object. The target of your procedure can be any models in your VDB. Here is an example procedure that selects from a virtual table (VirtualBooks) and returns the results as an XMLLiteral:
Figure 12.31.
Notice the syntax used to convert the relation table result of the select from VirtualBooks, to an XMLLiteral.Here is an example of an update procedure that will insert a row and return an XMLLiteral object:Figure 12.32.
The input format for the REST procedure could be URI parameters, an XML/JSON document, or some combination of both. When using an XML document your root node must be <input> and the XML nodes must correspond to order of the procedure's input parameters. For example, here is the input for the above insert procedure:Figure 12.33. Sample XML Input
When using a JSON document, ensure your values match the order of your procedure input parameters as well. Here is the input for the above insert procedure:Figure 12.34. Sample JSON Input
- Now we need to identify our procedure as REST eligible. To do this we add enable REST properties for the procedure(s) via the Modeling > Enable context menu option.
Figure 12.35.
This will enable two new properties in the property tab for all procedures defined in the model. The two required properties are defined in the table below:Table 12.2. Required Extended Properties for RESTful Procedures
Property Name Description Rest Method The HTTP method that will determine the REST mapping of this procedure. Supported methods are: GET, PUT, POST and DELETE URI The resource path to the procedure. For example, if you use books/{isbn} as your URI value for a procedure, http://{host}:{port}/{war_context}/{model_name}/books/123 would execute this procedure and pass 123 in as a parameter. Here is what the above example would look like in the Property tab:Figure 12.36.
Note that the generated URI will have the model name included as part of the path, so full URL would look like this: http://{host}:{port}/{war_context}/{model_name}/books/123. If you wanted a REST service to return all books, you would write your procedure just as it is above, but remove the input parameter. The URI property would then just be 'books' (or whatever you want) and the URL would be http://{host}:{port}/{war_context}/{model_name}/books.Once you have added all of your procedures along with the required extended properties, be sure and add the model to your VDB or synchronize if it's already included in the VDB. You will then need to redeploy the VDB.Important
If you redeploy your VDB during development, you may receive an "Invalid Session Exception"due to a stale connection obtained for the pool. This can be corrected by flushing the data source or, alternatively, you could add a test query to your VDB connection's -ds.xml file. This will insure you get a valid connection after redeploying your VDB. The syntax for the test query is as follows: <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>" - If you have not already done so, you will need to create a data source for your VDB. This can be done in the Server view of Designer. Right-click on your deployed VDB and select Create Data Source. The Generate REST WAR dialog will ask you for the JNDI name for your created source so that it can connect to your VDB.
- Right-click on the VDB containing your virtual model(s) with REST eligible procedures and select the Modeling > Generate RESTEasy War action. If there are no procedures that are REST eligible, the "Generate RESTEasy War" option will not be enabled.
Figure 12.37.
- Fill in missing properties in the REST War Generation Wizard shown below.
Figure 12.38. Generate a REST WAR War File Dialog
Table 12.3. Field Descriptions
Field Name Description Name The name of the generated war file. Connection JNDI Name The JNDI connection name to the deployed Teiid source VDB. War File Save Location The folder where the generated WAR file should be saved. Include RESTEasy Jars in lib Folder of WAR If selected, the RESTEasy jars and there dependent jars will be included in the lib folder of the generated WAR. If not selected, the jars will not be included. This should be cleared in environments where RESTEasy is installed in the classpath of the server installation to avoid conflicts. - Click OK to generate the REST war. When war generation is complete, a confirmation dialog appears. Click OK.
Figure 12.39. Generation Completed Dialog
12.4.2.4. Deploying Your Generated WAR File
From JBDS or JBoss Tools
- Insure target JBossAS is configured and running.
- Select your WAR file in the Model Explorer view. If you did not generate your war to that location, you can copy and paste it there.
- Right-click on the WAR file and select Mark as Deployable. This will cause you WAR file to be automatically deployed the JBoss AS instance you have defined.
Figure 12.40.
Using the JBoss AS Administration Console
- Using the administration console that comes with JBoss AS, you can deploy WAR files. The administration console is available at http://{host:port}/admin-console. Once logged on, use the Add a New Resource button of the Web Application (WAR) resource folder.
Manual Deployment to JBossAS
- It is possible to deploy the generated WAR by manually copying the file to the deploy folder of the target JBoss AS. If the server is running, the WAR will deploy automatically via hot deploy. Otherwise, the WAR will deploy at the next start of the server.
12.4.2.5. Testing Your Generated WAR Files
SOAP WAR Testing
- Determining Your WSDL URL
- You can get your WSDL URL at http://{server:port}/jbossws/services. This is where all the deployed web services for the target JBossAS server will be listed. Find your service and click the Endpoint Address link. This will retrieve your web service's WSDL and the WSDL URL address will appear in the browser's address bar.
Figure 12.41.
Now that you have your WSDL URL, you can use any SOAP testing tool such as the Web Service Tester that comes with JBDS and JBoss Tools or an external tool like soapUI. - Using the JBoss AS Administration Console
- Using the administration console that comes with JBoss AS, you can deploy WAR files. The administration console is available at http://{host:port}/admin-console. Once logged on, simply use the "Add a New Resource" button of the "Web Application (WAR)" resource folder.
REST WAR Testing
- What is my URI?
- When you modeled your REST procedures, you assigned a URI for each HTTP Operation you defined along with the corresponding operation (GET, PUT, POST or DELETE). The full path of each URI is defined as /{war_context}/{model_name}/{resource} for XML input/output and /{war_context}/{model_name}/json/{resource} for JSON input/output.Using your REST URL, you can use any testing tool with REST support such as the Web Service Tester included with JBDS and JBoss Tools or an external tool like soapUI or cURL.
Chapter 13. Editing Models and Projects
13.1. Editing Models and Projects
13.2. Rename A Model
- Select a model in the Model Explorer view.
- Right-click select the Refactor > Rename action.
Figure 13.1. Refactor Rename Action In Model Explorer
- Specify unique model name in the Rename Model File dialog. Click OK.
Figure 13.2. Rename Model File Dialog
Note
13.3. Move Model
- Select a model in the Model Explorer view.
- Right-click select the Refactor > Move action.
Figure 13.3. Refactor Move Action In Model Explorer
- Select a new location (that is, Project or Folder) and click OK.
Figure 13.4. Move Model Dialog
13.4. Save Copy of Model
Note
- Open the model you wish to copy in a Model Editor by double-clicking the model in Model Explorer view or right-click and click Open action.
- Select the editor tab for the model you opened.
Figure 13.5. Select Editor Tab
- Click File > Save As... action to open the Save Model As dialog.
Figure 13.6. Save Model As Dialog
- Enter a unique model name in the new model name text field and click OK.
- If dependent models are detected, the Save Model As - Import References dialog is presented to give you the opportunity to change any of the dependent models imports to reference the new model or not.
Figure 13.7. Save Model As Dialog
13.5. Clone Project
- This action clones a complete model project containing any number of model (XMI or XSD) files organized in a user defined directory structure.
- All object references (UUIDs) within the original project will be replaced with new unique references.
- Any model dependencies or internal object references are refactored to reflect the dependencies within the cloned project.
- Any model references to models in projects external to the original project will NOT be replaced.
- Only XMI and XSD files are cloned. All other file types in your project will NOT be processed nor copied into your newly cloned project including VDBs.
- If one or more editors that require save are open, the user will be asked to save them before continuing with the cloning process.
- Select an existing model project in the Model Explorer view.
- Right-click, then select Model Project > Clone in the context menu. Otherwise you can select the Project > Clone Project action located in the Teiid Designer's main menu bar.
Figure 13.8. Clone Project In Project Menu
- On the Clone Project wizard page, provide a name for your new project.
Figure 13.9. Clone Project In Project Menu
- (Optional) If you wish to create your cloned project in a location other than your default workspace location, clear the Use default location checkbox and specify (type in or browse to) a new directory location on your local file system.
- Click Finish to generate your new project.
Chapter 14. Managing Your Virtual Databases
14.1. Create a Virtual Database
Procedure 14.1. Create a Virtual Database
- Launch Red Hat JBoss Developer Studio's New wizard.
- Open the Teiid Designer category directory and select Teiid VDB.
Note
You can also select one or more models in a model project. To do so 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.
14.2. Edit a Virtual Database
Procedure 14.2. Edit an Existing Virtual Database
- Select the VDB you want to edit in the Explorer.
- Right-click and select the Open action. (You could also double-click it.)The VDB will then open in the Editor.
14.3. Multi-Source Binding Support
Note
14.4. User-Defined Functions Support
14.5. Reusing Virtual Databases
vdb.xml
definition. 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.
vdb.xml
file 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>
- 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
14.6. Create a VDB Source Model
Procedure 14.3. Create a VDB Source Model
- Deploy your VDB.
- Launch the JDBC Import Wizard by clicking Import - Teiid Designer - JDBC Database Source Model.
- On the first page of the wizard 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.
Note
If Import as VDB source model is NOT checked, then the wizard will continue importing as a normal JDBC import - On the Select Database Objects page, select a single schema to use in order to create as VDB source model.
- The final page shows the name of the resulting VDB source model. The name is NOT editable.
Note
All other options are disabledNote
The target Into Folder must NOT contain a model with the same name or the Finish button will be disabled.Note
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 reference is added in its 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.
14.7. Security and Data Access
- Security (Row-based condition and column masking)
- Create
- Read
- Update
- Delete
- Execute
- Alter
Chapter 15. Testing Your Models
15.1. Manage Connection Profiles
15.1.1. Manage Connection Profiles
15.1.2. Set Connection Profile for Source Model
15.1.3. View Connection Profile for Source Model
Figure 15.1. Connection Profile Information Dialog
Note
Figure 15.2. No Connection Info Dialog
15.1.4. Remove Connection Profile from Source Model
15.2. Previewing Data For a Model
15.2.1. Previewing Data For a Model
- The selected object must be one of several previewable model object types.
- All source models within the model dependency tree must reference a connection profile.
Note
Figure 15.3. Preview Not Available
Figure 15.4. Missing Password
When editing transformation SQL in the Transformation Editor, a special SQL Results data action is provided in the editor toolbar . You can change your transformation SQL, re-validate and preview your the data for your modified SQL. The following sections provide steps for previewing your data. Note that all steps assume that all source models referenced by your models, either directly or through dependencies, are bound to connector bindings.
15.2.2. Preview Relational Table or View
- Select a relational table or view in the Model Explorer view or diagram. The table or view can be in a view model as well as a source model. Staging tables are not visible in the Model Explorer view, so you need to open the mapping diagram and select it there.
- Right-click select the Preview Data action . You can also select the same action in the toolbar of either the Model Explorer view or diagram.
- Your query results will be displayed in the Preview Results view. The view will automatically open or get focus if not visible in your perspective.
15.2.3. Preview Relational Table With Access Pattern
- Select a relational table or view in the Model Explorer view or diagram that contains an access pattern. The table or view can be in a view model as well as a source model.
- Right-click select the Preview Data action . You can also select the same action in the toolbar of either the Model Explorer view or diagram.
- A column input dialog is presented. Select each access pattern and enter a value for each required column.
Note
If data entered does not match the column datatype (String, integer, etc...), an error message will be displayed in the dialog header.When all required values are entered, click the OK button to execute the query.Figure 15.5. Access Pattern Column Input Dialog
- Your query results will be displayed in the Preview Results view. The view will automatically open or get focus if not visible in your perspective.
15.2.4. Preview Relational Procedure
- Select a relational procedure in the Model Explorer view or diagram. The procedure can be in a view model as well as a source model.
- Right-click select the Preview Data action . You can also select the same action in the toolbar of either the Model Explorer view or diagram.
- An input parameter input dialog is presented. Enter a valid value for each parameter.
Note
If data entered does not match the parameter datatype (String, integer, etc...), an error message will be displayed in the dialog header.When all required values are entered, click the OK button to execute the query.Figure 15.6. Procedure Parameter Input Dialog
- Your query results will be displayed in the Preview Results view. The view will automatically open or get focus if not visible in your perspective.
15.2.5. Preview Web Service Operation
- Select a Web service operation in the Model Explorer view or diagram. The operation can be in a view model as well as a source model.
- Right-click select the Preview Data action . You can also select the same action in the toolbar of either the Model Explorer view or diagram.
- An input parameter input dialog is presented. Enter a valid value for each parameter.
Note
If data entered does not match the parameter datatype (String, integer, etc...), an error message will be displayed in the dialog header.When all required values are entered, click the OK button to execute the query.Figure 15.7. Procedure Parameter Input Dialog
- Your query results will be displayed in the Preview Results view. The view will automatically open or get focus if not visible in your perspective.
15.2.6. Sample SQL Results for Preview Data
Note
Figure 15.8. SQL Results View
15.2.7. Execution Plans
Figure 15.9. Teiid Execution Plan View
15.3. Testing With Your VDB
15.3.1. Testing With Your VDB
- A deployed VDB backed by valid deployed Data Sources
- An instance of a Teiid Connection Profile configured for the deployed VDB
15.3.2. Creating Data Sources
Figure 15.10. Create Data Source Dialog
15.3.3. Execute VDB from Model Explorer
- Right-click a VDB in your Model Explorer select Modeling > Execute VDB action. This action will insure your selected VDB is deployed to JBoss Data Virtualization, create a Teiid Connection Profile specific for that VDB, open the Database Development perspective and create a connection to your VDB.
Figure 15.11. Execute VDB Action
- Select your new Teiid connection profile and right-click select Open SQL Scrapbook, enter your designer SQL (i.e.
SELECT * FROM TableXXXX
), select all text and right-click select Execute Selected Text.Figure 15.12. SQL Scrapbook Editor
- Results of query should be displayed in the SQL Results view on the Result1 tab.
Figure 15.13. SQL Results View
15.3.4. Deploy VDB from Model Explorer
Note
Figure 15.14. No Teiid Instance Defined
15.3.5. Executing a Deployed VDB
- Open the Database Development perspective.
- Select the Database Connections folder and choose the New action to display the New Connection Profile dialog.
Figure 15.15. New Connection Profile Dialog
- Enter unique name for your profile, select an existing connection profile type and hit Next.
- In the Teiid Profile Wizard page, select the New Driver Definition button to locate and select the Teiid client jar on your file system. Configure your URL using your VDB Name, Host, Port, Username (default = admin) and Password (default = teiid).
Figure 15.16. Teiid Connection Profile Dialog
- Select Next to view a summary of your new Teiid Connection Profile.
Figure 15.17. Teiid Connection Profile Summary
- Select Finish.
- Select your new Teiid connection profile and right-click select Open SQL Scrapbook, enter your designer SQL (i.e. SELECT * FROM TableXXXX), select all text and right-click select Execute Selected Text.
Figure 15.18. SQL Scrapbook Editor
Chapter 16. Searching
16.1. Searching
- Transformations... - Launches the Transformation Search dialog. User can search models in the workspace for matching SQL text. Search results appear in the dialog and user can select and view SQL as well as open desired transformations for editing.
- Metadata... - Launches the Search dialog. User can search for models in the workspace by specifying an Object Type, and/or a Data Type, and/or a property value. Search results appear in the Search Results view, and double clicking a result will open that model in the appropriate editor.
- Find Model Object - Launches the Find Model Object dialog, which can be used to find an object in the workspace by specifying all or part of its name. Selecting the object will open it in the appropriate editor.
16.2. Finding Model Objects
- Open the Find Model Object dialog by either selecting the action on the main Teiid Designer toolbar or select the same action via the main menu's Search > Find Model Object action.
- Begin typing a word or partial word in the Type Object Name field. Wild card (*) characters will be honored. As you type, the objects which match the desired name will be displayed in the Matching Model Objects list. If there are more than one objects with the same name, the locations or paths of the objects are displayed in the Locations list.
- If more than one object exists with the desired name, select the one of the locations.
- Click OK. If editor is not open for the object's model, an editor will open. The desired object results in a diagram (if applicable) and selected.
16.3. Search Transformation SQL
- Click Search > Transformations... action on the Teiid Designer main menu which opens the Search Transformations dialog.
- Specify a string segment in the Find: field and specify/change your case sensitive preference.
- Select Perform Search button. Any transformation object containing SQL text which contains occurrences of your string will be displayed in the results section.
16.4. Search Models Via Metadata Properties
- Click Search > Metadata... action on the main Teiid Designer toolbar which opens the Search dialog.
- Specify desired search options for Object Type, Data Type and Properties.
- Click Search. The search will be performed and the results will be displayed in the Search Results View. If the view is not yet open, it will be opened automatically.
Appendix A. Supported Configurations
A.1. Supported Data Sources and Translators
Table A.1. Supported Data Sources and Translators
Data Source | Translator | Supported DV Version | Driver |
---|---|---|---|
Apache Hive 12
|
-
|
6.0+
|
-
|
Apache Solr
|
solr
|
6.1+
|
-
|
Cloudera Hadoop
|
-
|
6.1+
|
-
|
EDS 5.x
|
teiid
|
6.0+
|
-
|
Files – delimited, fixed length
|
file
|
6.0+
|
-
|
Generic Datasource-JDBC ansi
|
jdbc-ansi
|
6.0+
|
-
|
Generic Datasource-JDBC simple
|
jdbc-simple
|
6.0+
|
-
|
Google Spreadsheet
|
-
|
6.0+
|
-
|
Greenplum 4.x
|
postgresql
|
6.0+
|
-
|
Hortonworks Hadoop
|
-
|
6.1.+
|
-
|
HSQL (for test/examples only)
|
-
|
-
|
-
|
IBM DB2 10
|
db2
|
6.1+
|
Universal Driver v4.x
|
IBM DB2 9.7
|
db2
|
6.0+
|
Universal Driver v4.x
|
Ingres 10
|
ingres
|
6.0+
|
-
|
Intel Hadoop
|
-
|
6.1+
|
-
|
JBoss Data Grid 6.4 (remote client - hotrod)
|
infinispan-cache-dsl
|
6.2+
|
-
|
JBoss Data Grid 6.4 (library mode)
|
infinispan-cache
|
6.0 - post GA, 6.1+
|
-
|
LDAP/ActiveDirectory v3
|
ldap
|
6.0+
|
-
|
Mainframe (CICS,IMS,VSAM)
|
-
|
6.0+
|
-
|
MariaDB
|
mysql5
|
6.1+
|
-
|
ModeShape/JCR 3.1
|
-
|
6.0+
|
-
|
MongoDB 2.2
|
mongodb
|
6.0
|
post GA, 6.1+ -
|
MS Access 2010
|
-
|
6.0+
|
-
|
MS Access 2013
|
-
|
6.0+
|
-
|
MS Excel 2010
|
excel
|
6.0+
|
-
|
MS Excel 2013
|
excel
|
6.0+
|
-
|
MS SQL Server 2008
|
sqlserver
|
6.0+
|
Microsoft SQL Server JDBC Driver 4
|
MS SQL Server 2012
|
sqlserver
|
6.0+
|
Microsoft SQL Server JDBC Driver 4
|
MySQL 5.1
|
mysql5
|
6.0+
|
V5.1
|
MySQL 5.5
|
mysql5
|
6.0+
|
V5.5
|
Netezza 6.0.2
|
netezza
|
6.0+
|
-
|
Oracle 10g R2
|
oracle
|
6.0+
|
Oracle JDBC Driver v10
|
Oracle 11g RAC
|
oracle
|
6.0+
|
Oracle JDBC Driver v11
|
Oracle 12c
|
oracle
|
6.0 - post GA, 6.1+
|
Oracle JDBC Driver v12
|
PostgreSQL 8.4
|
postgresql
|
6.0+
|
-
|
PostgreSQL 9.2
|
postgresql
|
6.0+
|
-
|
REST/JSON over HTTP
|
ws
|
-
|
-
|
RHEL 5.5/6 PostgreSQL config
|
-
|
6.0+
|
-
|
Salesforce.com API 22
|
salesforce
|
6.0+
|
-
|
SAP Netweaver Gateway (OData)
|
sap-nw-gateway
|
6.1+
|
-
|
Support SAP Service Registry as a Data Source
|
-
|
6.2+
|
-
|
Sybase ASE 15
|
sybase
|
6.0+
|
jConnect JDBC3.0 v7
|
Teradata Express 12
|
teradata
|
6.0+
|
-
|
Webservices
|
ws
|
6.0+
|
-
|
XML Files
|
FILE
|
6.0+
|
-
|
Note
A.2. Designer Metadata Usage Requirements In JBoss Data Virtualization Runtime
Table A.2. Data Usage for Tables
TABLE | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | Yes | Yes | Yes | Name of the Table |
NameInSource | String | Yes | Yes | Yes | Name of Table in the source system, for view this can be empty, also used on variety of use cases |
Cardinality | Integer | Yes | Yes | Yes | Cardinality is used to calculate the cost of source node access |
TableType | Integer | Yes | Yes | Yes | Table,View,Document,XmlMappingClass,XmlStagingTable,MaterializedTable |
IsVirtual | Boolean | Yes | Yes | Yes | Used to find if this is source table Vs view |
IsSystem | Boolean | Yes | Yes | No | Only used for System metadata |
IsMaterialized | Boolean | Yes | Yes | Yes | To identify that the table is materialized |
SupportsUpdate | Boolean | Yes | Yes | Yes | To allow updates on the table |
PrimaryKeyID | String | Yes | KeyRecord | Yes | Used for creating indexes on temp tables and to create default update/delete procedures |
ForeignKeyIDs | Collection | Yes | List<ForeignKey> | Yes | Used in Planning of query (rule raise access) |
IndexIDs | Collection | Yes | List<KeyRecord> | Yes | Used for creating indexes on temp tables and in planning (estimate predicate cost) |
UniqueKeyIDs | Collection | Yes | List<KeyRecord> | Yes | Used for query planning |
AccessPatternIDs | Collection | Yes | List<KeyRecord> | Yes | Used for enforcing the criteria on query |
MaterializedTableID | String | Yes | Table | Yes | Reference to Materialization table |
insertEnabled | Boolean | ** | Yes | Yes | Flag for checking insert procedure is enabled for view |
deleteEnabled | Boolean | ** | Yes | Yes | Flag for checking delete procedure is enabled for view |
updateEnabled | Boolean | ** | Yes | Yes | Flag for checking update procedure is enabled for view |
Select Transformation | String | ** | Yes | Yes | Transformation for Select in case of View |
Insert Plan | String | ** | Yes | Yes | Transformation for Insert in case of View |
Update Plan | String | ** | Yes | Yes | Transformation for Update in case of View |
Delete Plan | String | ** | Yes | Yes | Transformation for Delete in case of View |
Bindings | Collection | ** | Yes | Yes | XML Document |
SchemaPaths | Collection | ** | Yes | Yes | XML Document |
Table A.3. Data Usage for Columns
COLUMN | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | Yes | Yes | Yes | Name of the column |
NameInSource | String | Yes | Yes | Yes | Name of the column in source system |
IsSelectable | Boolean | Yes | Yes | Yes | Column is allowed in select |
IsUpdatable | Boolean | Yes | Yes | Yes | Column is allowed in Update/Insert/Delete |
NullType | Integer | Yes | Yes | Yes | Used for validation if null value allowed |
IsAutoIncrementable | Boolean | Yes | Yes | Yes | During insert used to validate if a value is required or not |
IsCaseSensitive | Boolean | Yes | Yes | ?? | ?? |
IsSigned | Boolean | Yes | Yes | ?? | Used in System Metadata |
IsCurrency | Boolean | Yes | Yes | No | Only used for System metadata |
IsFixedLength | Boolean | Yes | Yes | No | Only used for System metadata |
IsTranformationInputParameter | Boolean | Yes | ?? | ?? | ?? |
SearchType | Integer | Yes | Yes | Yes | Used for defining the capability of the source |
Length | Integer | Yes | Yes | ?? | Used in System Metadata |
Scale | Integer | Yes | Yes | ?? | Used in System Metadata |
Precision | Integer | Yes | Yes | ?? | Used in System Metadata |
CharOctetLength | Integer | Yes | Yes | No | only used for System metadata |
Radix | Integer | Yes | Yes | ?? | Used in System Metadata |
DistinctValues | Integer | Yes | Yes | Yes | Used for cost calculations, System metadata |
NullValues | Integer | Yes | Yes | Yes | Used for cost calculations, System metadata |
MinValue | String | Yes | Yes | Yes | Used for cost calculations, System metadata |
MaxValue | String | Yes | Yes | Yes | Used for cost calculations, System metadata |
Format | String | Yes | Yes | No | Only used for System metadata |
RuntimeType | String | Yes | DataType | Yes | Data Type |
NativeType | String | Yes | Yes | Yes | Translators can use this field to further plan |
DatatypeObjectID | String | Yes | ?? | ?? | |
DefaultValue | String | Yes | Yes | Yes | Used for Insert and procedure execute operations when the values are not supplied |
Position | Integer | Yes | Yes | Yes | Used in the index calculations |
Table A.4. Data Usage for Primary Keys
PRIMARY KEY | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | See the KeyRecord, See Table | |||
NameInSource | String | ||||
ColumnIDs | Collection | ||||
ForeignKeyIDs | Collection | Extends KeyRecord |
Table A.5. Data Usage for Unique Keys
UNIQUE KEY | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | See the KeyRecord, See Table | |||
NameInSource | String | ||||
ColumnIDs | Collection | ||||
ForeignKeyIDs | Collection |
Table A.6. Data Usage for Indexes
INDEX | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | See the KeyRecord, See Table | |||
NameInSource | String | ||||
ColumnIDs | Collection |
Table A.7. Data Usage for Access Patterns
ACCESS PATTERNS | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | See the KeyRecord, See Table | |||
NameInSource | String | ||||
ColumnIDs | Collection |
Table A.8. Data Usage for Result Sets
RESULT SET | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | See DataType | |||
NameInSource | String | ||||
ColumnIDs | Collection |
Table A.9. Data Usage for Foreign Keys
FOREIGN KEY | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | See the KeyRecord, See Table | |||
NameInSource | String | ||||
ColumnIDs | Collection | ||||
UniqueKeyID | String |
Table A.10. Data Usage for Data Types
DATA TYPE | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | No | Only used for System metadata | ||
NameInSource | String | No | Only used for System metadata | ||
Length | Integer | No | Only used for System metadata | ||
PrecisionLength | Integer | No | Only used for System metadata | ||
Scale | Integer | No | Only used for System metadata | ||
Radix | Integer | No | Only used for System metadata | ||
IsSigned | Boolean | No | Only used for System metadata | ||
IsAutoIncrement | Boolean | No | Only used for System metadata | ||
IsCaseSensitive | Boolean | No | Only used for System metadata | ||
Type | Integer | No | Only used for System metadata | ||
SearchType | Integer | No | Only used for System metadata | ||
NullType | Integer | No | Only used for System metadata | ||
JavaClassName | String | Yes | Maps to runtime type based on java class name | ||
RuntimeTypeName | String | No | Only used for System metadata | ||
DatatypeID | String | No | Only used for System metadata | ||
BaseTypeID | String | No | Only used for System metadata | ||
PrimitiveTypeID | String | No | Only used for System metadata | ||
VarietyType | Integer | No | Only used for System metadata | ||
VarietyProps | Collection | No | Only used for System metadata |
Table A.11. Data Usage for Procedures
PROCEDURE | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | Yes | Yes | Yes | Name of the column |
NameInSource | String | Yes | Yes | Yes | Name of the column in source system |
IsFunction | Boolean | Yes | Yes | Determines if this function | |
IsVirtual | Boolean | Yes | Yes | If Function then UDF else stored procedure | |
ParametersIDs | Collection | Yes | Yes | Parameter List | |
ResultSetID | String | Yes | Yes | Result set columns | |
UpdateCount | Integer | Yes | Yes | Update count defines the number of sources being updated, only applicable for virtual procedures |
Table A.12. Data Usage for Procedure Parameters
PROCEDURE PARAMETER | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
ObjectID | String | Same as Column | |||
FullName | String | Same as Column | |||
nameInSource | String | Same as Column | |||
defaultValue | String | Same as Column | |||
RuntimeType | String | Same as Column | |||
DatatypeObjectID | String | Same as Column | |||
Length | Integer | Same as Column | |||
Radix | Integer | Same as Column | |||
Scale | Integer | Same as Column | |||
NullType | Integer | Same as Column | |||
Precision | Integer | Same as Column | |||
Position | Integer | Same as Column | |||
Type | String | Yes | Defines parameter is IN/OUT/RETURN | ||
Optional | Boolean | No | Defines if the parameter is optional or not, only used system metadata |
Table A.13. Data Usage for SQL Transformations
SQL TRANSFORMATION(**) | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
VirtualGroupName | String | Yes | No | Yes | See Table, the properties defined on Table |
TransformedObjectID | String | Yes | No | Yes | See Table, the properties defined on Table |
TransformationObjectID | String | Yes | No | Yes | See Table, the properties defined on Table |
TransformationSql | String | Yes | No | Yes | See Table, the properties defined on Table |
Bindings | Collection | Yes | No | Yes | See Table, the properties defined on Table |
SchemaPaths | Collection | Yes | No | Yes | See Table, the properties defined on Table |
Table A.14. Data Usage for VDBs
VDB | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | Yes | vdb.xml | Yes | Name of the VDB |
NameInSource | String | ?? | No | No | Not required |
Version | String | Yes | vdb.xml | Yes | VDB version |
Identifier | String | Yes | No | No | Not required |
Description | String | Yes | vdb.xml | No | Used by System metadata |
ProducerName | String | Yes | No | No | Not required |
ProducerVersion | String | Yes | No | No | Not required |
Provider | String | Yes | No | No | Not required |
TimeLastChanged | String | Yes | No | No | Not required |
TimeLastProduced | String | Yes | No | No | Not required |
ModelIDs | Collection | Yes | vdb.xml | Yes | Defines the model list in a VDB |
Table A.15. Data Usage for Annotations
ANNOTATION | Type | In Designer | In Metadata API | Required | Description |
---|---|---|---|---|---|
FullName | String | Yes | Yes | No | System metadata, as description on procedure parameter |
NameInSource | String | Yes | No | No | Not required |
Description | String | Yes | No | No | Not required |
Appendix B. User Preferences
B.1. User Preferences
Figure B.1. Preferences Dialog
B.2. Teiid Designer Preferences
- Always open editor without prompting - To change/edit a model, it must be opened for editing. Selecting this box will automatically open the model in an editor if the user attempts to perform a change in a model. If unchecked, the user will be informed that an editor will be opened before the operation is completed.
- Open Designer perspective when model is opened - If a model is opened via importing projects, the New > Teiid Metadata Model menu and the Teiid Designer perspective is not open, you may want to automatically open the perspective and begin working on your model. This preference has 3 settings. Always open, which means always open the perspective without prompting; never open, which means do not open the Teiid Designer perspective, or prompt, which will always ask you if you wish to open the Teiid Designer perspective.
- Check and update imports during save - Occasionally editing a model may add or remove objects in one model that reference objects in another model. Model Imports keep track of these dependencies within each model. A validation error or warning may appear during a build. Checking this box will automatically check and update imports during the save process. This will result in any unneeded imports being removed from the model or any required imports added to the model. If unchecked, no updating of imports will be performed.
- Enable Preview - If the Designer Runtime feature is installed and a Teiid Instance is defined, Teiid Designer will automatically keep the preview artifacts (VDBs) in sync with the workspace models. Clearing this preference will disable preview feature and not create preview artifacts.
- Enable Preview Teiid Cleanup - If operating Designer with Enable Preview = TRUE, then this preference will result in automatic clean-up of your preview artifacts from your JBoss Data Virtualization servers. Any preview VDBs or preview data sources will be undeployed from your servers as part of Eclipse's shutdown process.
Figure B.2. General Teiid Designer Preferences Panel
B.3. Diagram Preferences
- Notations - Standard diagram notation for Teiid Designer is based on UML notation. Future releases may include alternate notations.
- Routers - The relationship link type for Package and Custom diagrams (Foreign Key - Primary Key relationships) can be customized. Available options include Orthogonal (default), Direct or Manual (user defined breakpoints).
- Font Settings - Select font type, style and size.
- Background Color Settings - Select a unique background color for each diagram type to help differentiate between types.
- Model Size - Displaying very large diagram may take a considerably long time. This preference allows users to set an upper limit on the number of objects to display in a diagram. If this limit is exceeded, a warning is displayed to the user and the diagram is not constructed.
- Relationship Options - UML-type relationships can be customized in a couple of ways. Role Names and Multiplicity labels can be shown or hidden using the checkboxes labeled Show Role Names and Show Multiplicity.
Figure B.3. Diagram Preferences Panel
B.4. Diagram Printing Preferences
Figure B.4. Diagram Preferences Panel
B.5. Editor Preferences
B.5.1. XML Document Preferences
Figure B.5. XML Document Preferences Panel
B.5.2. Table Editor Preferences
Figure B.6. Table Editor Preferences Panel
B.5.3. Transformation Editor Preferences
Figure B.7. Transformation Editor Preferences Panel
B.5.4. VDB Editor Preferences
Figure B.8. VDB Editor Preferences Panel
B.6. Validation Preferences
Figure B.9. Core Model Validation Preferences Panel
Figure B.10. Relational Model Validation Preferences Panel
Figure B.11. XML Document Model Validation Preferences Panel
Figure B.12. XSD Schema Model Validation Preferences Panel
Note
Appendix C. Teiid Designer UI Reference
C.1. Teiid Designer Perspectives
C.2. Teiid Designer Perspective
- Model Explorer View - Teiid tree view of Model Objects.
- Server View - JBoss Data Virtualization Server instance view. Provides view of contents for connected instances of installed Teiid runtime.
- Model Editors - Custom editors targeted for
.xmi
metadata model files. - Properties View - Standard property values for selected workbench objects.
- Guides View - Guides and Status Views which enhance usability.
- Miscellaneous Views - Includes the Problems view, Message Log view and the SQL Results view (opened if Preview Data action is performed).
Figure C.1. Teiid Designer Perspective Layout
C.3. Opening a Perspective
- Using the Open Perspective button on the shortcut bar.
- Selecting a perspective from the Window > Open Perspective menu.
- Click the Open Perspective button .
- A menu appears showing the same choices as shown on the Window > Open Perspective menu. Click Other from the menu.
Figure C.2. Perspectives Menu
- In the Select Perspective dialog, select Teiid Designer and click OK.
Figure C.3. Select Perspective Dialog
- The title of the window will indicate which perspective is in use.
Figure C.4. Workbench Window Title Bar
- The shortcut bar may contain multiple perspectives. The perspective button which is pressed in, indicates that it is the current perspective.
- To display the full name of the perspectives, right-click the perspective bar and click Show Text and conversely click Hide Text to only show icons.
- To quickly switch between open perspectives, select the desired perspective button. Notice that the set of views is different for each of the perspectives.
Figure C.5. Workbench Window Title Bar
C.4. Further Information
Appendix D. Teiid Designer Views
D.1. Teiid Designer Views
Figure D.1. Show View Dialog
D.2. Model Explorer View
Figure D.2. Model Explorer View
- Preview Data - Executes a simple preview query
(SELECT * FROM )
. - Sort Model Contents - Sorts the contents of the models based on object type and alphabetizing.
- Refresh Markers - Refreshes error and warning markers for objects in tree.
- Back - Displays the last Go Into location. (See Eclipse Help)
- Forward - Displays the next Go Into location. (See Eclipse Help)
- Up - Navigates up one folder/container location. (See Eclipse Help)
- Collapse All - Collapses all projects.
- Link with Editor - When object is selected in an open editor, this option auto-selects and reveals object in Model Explorer.
- Additional Actions
Figure D.3. Additional Actions
Figure D.4. Show Model Imports Action
D.3. Selection-Based Action Menus
Figure D.5. Sample Context Menu
D.4. Outline View
D.5. Outline Tree View
Figure D.6. Outline View
D.6. Outline Thumbnail View
Figure D.7. Outline View
Figure D.8.
D.7. Server View
Figure D.9. Server View
- Select the New... action in the Server view and this will launch the JBoss Data Virtualization Server wizard. Configuring the JBoss Data Virtualization Server instance (with Teiid installed) will enable connection to the Teiid Server.
- In the New Server wizard, select the JBoss Enterprise Application Platform server type under the JBoss Enterprise Middleware category and click Next >.
Figure D.10. New Server Dialog
- On the JBoss Runtime page, click the top Browse... button to select the installation folder of your JBoss Enterprise Application Platform server.
Figure D.11. JBoss Runtime Definition
- Then click the bottom Browse... button to select the
standalone.xml
configuration file located under the EAP_HOME/standalone/configuration/ folder on your file system. Then click Finish.Figure D.12. Teiid Configuration File Selection
- Your new JBoss Data Virtualization server configuration will be opened in the JBoss / Teiid Editor for viewing. In this editor you can test both Teiid admin and JDBC connections.
Figure D.13. Teiid Editor
- Teiid Server Properties - View and edit properties of an existing Teiid instance.
- Reconnect - Reconnect and refresh contents of the selected Teiid instance.
- Execute VDB - Creates a JDBC Teiid connection profile and opens the Data Tools Database Development perspective.
- Undeploy VDB - Removes the selected VDB from the Teiid instance.
- Create Data Source - Launches the New Data Source wizard.
- Delete Data Source - Removes the selected Data Source from the Teiid instance.
Note
Figure D.14. Teiid Contents in Server View
D.8. Properties View
Figure D.15. Properties View
Figure D.16. Open Model Editor Dialog
Figure D.17. Open Model Editor Dialog
- Show Categories - toggles between categorized properties and flat alphabetical properties list.
- Show Advanced Properties - shows/hide advanced properties (if available).
- Restore Default Value - for a selected property, this action will reset the current to a default value (if available).
D.9. Description View
Figure D.18. Description View
Figure D.19. Description View Context Menu
Figure D.20. Edit Description Dialog
D.10. Problems View
Figure D.21. Problems View
- Description - A description of the problem preceded by a severity icon (i.e., error , warning , or info ).
- Resource - The name of the resource.
- Path - The project name.
- Location - The object within the resource that has a validation error.
- type - Type of validation item.
D.11. Toolbar Menu
D.12. Context Menu
Figure D.22. Problems View Context Menu
- Go To - will open the appropriate editor and select the affected/referenced object.
- Show In Navigator - Opens the Basic > Navigator view (if not open) and expands file system tree and reveals applicable resource.
- Copy - Copies the problem information to the system clipboard.
- Paste - Pastes the problem information located in the system clipboard (if applicable ) into the cursor location for a specified text editor.
- Delete - Deletes the selected problem rows ( if applicable ).
- Select All - selects all problems in the table.
- Quick Fix - (Not yet implemented in Teiid Designer).
- Properties - displays a dialog containing additional information.
D.13. Search Results View
Figure D.23. Search Results View
- Show Next Match - Navigates down one row in the view.
- Show Previous Match - Navigates up one row in the view.
- Remove Selected Matches - Removes selected results from the view.
- Remove All Matches - Clears the view.
- Search - Launches the Teiid Designer Dialog.
- Previous Search Results - Select previous search results from history.
Figure D.24. Search Results Context Menu
D.14. Datatype Hierarchy View
Figure D.25. Datatype Hierarchy View
Table D.1. Corresponding Runtime Types
Teiid Designer Type | Java Runtime Type |
---|---|
anyURI | java.lang.String |
base64Binary | java.lang.String |
bigdecimal | java.math.BigDecimal |
biginteger | java.math.BigInteger |
blob | java.sql.Blob [a] |
boolean | java.lang.Boolean |
byte | java.lang.Byte |
char | java.lang.Character |
clob | java.sql.Clob [b] |
date | java.sql.Date |
dateTime | java.sql.Timestamp |
decimal | java.math.BigDecimal |
double | java.lang.Double |
duration | java.lang.String |
ENTITIES | java.lang.String |
ENTITY | java.lang.String |
float | java.lang.Float |
gDay | java.math.BigInteger |
gMonth | java.math.BigInteger |
gMonthDay | java.sql.Timestamp |
gYear | java.math.BigInteger |
gYearMonth | java.sql.Timestamp |
hexBinary | java.lang.String |
ID | java.lang.String |
IDREF | java.lang.String |
IDREFS | java.lang.String |
int | java.lang.Integer |
integer | java.math.BigInteger |
language | java.lang.String |
long | java.lang.Long |
Name | java.lang.String |
NCName | java.lang.String |
negativeInteger | java.math.BigInteger |
NMTOKENS | java.lang.String |
NMTOKENS | java.lang.String |
nonNegativeInteger | java.math.BigInteger |
nonPositiveInteger | java.math.BigInteger |
normalizedString | java.lang.String |
NOTATION | java.lang.String |
object | java.lang.Object |
positiveInteger | java.math.BigInteger |
QName | java.lang.String |
short | java.lang.Short |
string | java.lang.String |
time | java.sql.Time |
timestamp | java.sql.Timestamp |
token | java.lang.String |
unsignedByte | java.lang.Short |
unsignedInt | java.lang.Long |
unsignedLong | java.lang.BigInteger |
unsignedShort | java.lang.Integer |
XMLLiteral | java.sql.SQLXML [c] |
[a]
The concrete type is expected to be org.teiid.core.types.BlobType.
[b]
The concrete type is expected to be org.teiid.core.types.ClobType.
[c]
The concrete type is expected to be org.teiid.core.types.XMLType.
|
D.15. Teiid Model Classes View
Figure D.26. Datatype Hierarchy View
D.16. System Catalog View
Figure D.27. System Catalog View
D.17. SQL Reserved Words View
Figure D.28. System Catalog View
Figure D.29. SQL Reserved Words Action
D.18. Model Extension Definition Registry View (MED Registry View)
Figure D.30. System Catalog View
- Built-In MED - these are registered during Teiid Designer installation. These MEDs cannot be updated or unregistered by the user.
- User-Defined MED - these are created by the user. These MEDs can be updated, registered, and unregistered by the user.
Note
D.19. Guides View
Figure D.31. Guides View
D.20. Status View
Figure D.32. Status View
- Source Connections - all Source Connections are fully defined.
- Sources - Source Models exist.
- XML Schema - XML Schemas exist.
- Views - View Models exist.
- VDBs - VDBs exist and are deployable.
- Model Validation (Status) - all Models pass validation.
- Test - all defined VDBs pass validation.
D.21. Cheat Sheets View
Figure D.33. Cheat Sheet Sample
Appendix E. Editors
E.1. Editors
Figure E.1. Editor Tabs
Figure E.2. Viewing Multiple Editors
- Diagram Editor - All models except XML Schema models.
- Table Editor - All models.
- Simple Datatypes Editor - XML Schema models only.
- Semantics Editor - XML Schema models only.
- Source Editor - XML Schema models only.
- Transformation Editor - Manages Transformation SQL for Relational View Base Tables, Procedures and XML Web Service Operations.
- Choice Editor - Manages properties and criteria for XML choice elements in XML Document View models.
- Input Editor - Manages Input Set parameters used between Mapping Classes in XML Document View models.
- Recursion Editor - Manages recursion properties for recursive XML Elements in XML Document View models.
- Operation Editor - Manages SQL and Input Variables for Web Service Operations.
E.2. Model Editor
E.2.1. Model Editor
E.2.2. Diagram Editor
- Package Diagram
- Custom Diagram
- Transformation Diagram
- Mapping Diagram
- Mapping Transformation Diagram
Figure E.3. Main Toolbar Diagram Actions
- Zoom In
- Zoom to Level
- Zoom Out
- Increase Font Size
- Decrease Font Size
- Perform Diagram Layout
E.2.3. Package Diagram
Figure E.4. Package Diagram Example
- Refresh Diagram - Redraws diagram.
- Show Parent Diagram - Navigates to diagram for parent object (if available).
- Preview Data - Executes a simple preview query
(SELECT * FROM )
. - Save Diagram as Image - Save the diagram image to file in JPG or BMP format.
- Modify Diagram Printing Preferences - Modify page layout information for printing diagrams. Includes margins, orientation, etc...
- Show/Hide Page Grid - Show current page boundaries as grid in diagram.
Figure E.5. Package Diagram Context Menu
E.2.4. Custom Diagram
Figure E.6. Custom Diagram Example
- Refresh Diagram - Redraws diagram.
- Show Parent Diagram - Navigates to diagram for parent object (if available).
- Preview Data - Executes a simple preview query
(SELECT * FROM )
. - Add To Diagram - Add objects selected in Model Explorer to diagram.
- Remove From Diagram - Removed objects selected in diagram from diagram.
- Clear Diagram - Remove all objects from diagram.
- Save Diagram as Image - Save the diagram image to file in JPG or BMP format.
- Modify Diagram Printing Preferences - Modify page layout information for printing diagrams. Includes margins, orientation, etc...
- Show/Hide Page Grid - Show current page boundaries as grid in diagram.
E.2.5. Transformation Diagram
Figure E.7. Transformation Diagram Example
- Refresh Diagram - Redraws diagram.
- Show Parent Diagram - Navigates to diagram for parent object (if available).
- Preview Data - Executes a simple preview query
(SELECT * FROM )
. - Add Transformation Sources - Add selected sources to transformation.
- Add Union Transformation Sources - Add selected sources as union sources.
- Remove Transformation Sources - Removed sources selected in diagram from transformation.
- Clear Transformation - Remove all sources from transformation.
- Open Transformation Reconciler dialog
- Save Diagram as Image - Save the diagram image to file in JPG or BMP format.
- Modify Diagram Printing Preferences - Modify page layout information for printing diagrams. Includes margins, orientation, etc...
- Show/Hide Page Grid - Show current page boundaries as grid in diagram.
E.2.6. Mapping Diagram
Figure E.8. Mapping Diagram Example
- Refresh Diagram - Redraws diagram.
- Show Parent Diagram - Navigates to diagram for parent object (if available).
- Show Mapping Transformation Diagram - Show detailed mapping transformation diagram for selected mapping class.
- Preview Data - Executes a simple preview query (SELECT * FROM ).
- Generate Mapping Classes - Generate mapping classes for the selected XML document root element.
- New Mapping Class - Insert new mapping class referenced to the selected XML document element or attribute..
- New Staging Table - Insert new staging table referenced to the selected XML document element or attribute.
- Merge Mapping Classes - Merge selected mapping classes.
- Split Mapping Class - Split selected mapping class.
- Display All Mapping Classes
- Show Mapping Class Columns
- Filter Displayed Mapping Classes with Selection
E.2.7. Mapping Transformation Diagram
Figure E.9. Mapping Transformation Diagram Example
- Refresh Diagram - Redraws diagram.
- Show Parent Diagram - Navigates to diagram for parent object (if available).
- Preview Data - Executes a simple preview query
(SELECT * FROM )
. - New Mapping Link - Create a mapping link between selected mapping extent (i.e. XML element or attribute) and mapping class column.
- Remove Mapping Link - Delete mapping link between selected mapping extent (i.e. XML element or attribute) and mapping class column.
- Add Transformation Sources - Add selected sources to transformation.
- Add Union Transformation Sources - Add selected sources as union sources.
- Remove Transformation Sources - Removed sources selected in diagram from transformation.
- Clear Transformation - Remove all sources from transformation.
- Open Transformation Reconciler dialog
- Save Diagram as Image - Save the diagram image to file in JPG or BMP format.
- Modify Diagram Printing Preferences - Modify page layout information for printing diagrams. Includes margins, orientation, etc...
E.3. Table Editor
E.3.1. Table Editor
Figure E.10. Table Editor Example
- Edit existing properties.
- Add, remove or edit objects, via the main Edit menu and context menu ( Cut, Copy, Paste, Clone, Delete, Rename, Insert Rows ).
- Paste information from your clipboard into the table.
- Print your tables.
- Table Paste - Paste common spreadsheet data (like Microsoft Excel) to set object properties.
- Table Editor Preferences - Change table editor preferences, including customizing visible properties.
- Insert Rows - Create multiple new sibling objects.
- Refresh Table - Refreshes the contents of the current Table Editor to insure it is in sync with the model.
Figure E.11. Table Editor Example
E.3.2. Editing Properties
Figure E.12. Editing String Property
Figure E.13. Editing Boolean Value
Figure E.14. Editing Multi-Value Property
Figure E.15. Editing Multi-Value With Picker
Figure E.16. Editing Datatype Values
E.3.3. Inserting Table Rows
- Select a table row to insert rows after.
- Right-click select Insert Rows action or click the Insert Rows action on the main toolbar. The following dialog will be displayed.
Figure E.17. Editing String Property
- Edit the Number of Rows value in the dialog, or use the up/down buttons to change the value.
- Click OK in dialog.
E.4. Simple Datatypes Editor
Figure E.18. Editing String Property
E.5. Semantic Editor
E.6. Source Editor
E.7. Model Object Editors
E.8. VDB Editor
E.8.1. VDB Editor
Figure E.19. VDB Editor
Figure E.20. Change Translator or Data Source Actions
E.8.2. Editing Data Roles
Figure E.21. VDB Data Roles Panel
Figure E.22. VDB Data Roles Tab
E.8.3. Editing Translator Overrides
Figure E.23. VDB Translator Overrides Tab
Figure E.24. Add Translator Override Dialog
Figure E.25. Add New Translator Override Dialog
E.9. Model Extension Definition Editor
*.mxd
files) in the workspace. The MED Editor has 3 sub-editors (Overview, Properties, and Source) which share a common header section. Here are the MED sub-editor tabs:
- Overview Sub-Editor - this editor is where the general MED information is managed. This information includes the namespace prefix, namespace URI, extended model class, and the description. The Overview sub-editor looks like this:
Figure E.26. Overview Tab
- Properties Sub-Editor - this editor is where the MED extension properties are managed. Each extension property must be associated with a model object type. The Properties sub-editor is divided into 2 sections (Extended Model Objects and Extension Properties) and looks like this:
Figure E.27. Properties Tab
- Source - this tab is a read-only XML source viewer to view the details of your MED. This source viewer is NOT editable.
Figure E.28. Text Field With Error
- Status Image - an image indicating the most severe validation message (error, warning, or info). If there are no validation messages the model extension image is shown.
- Title - the title of the sub-editor being shown.
- Menu - a drop-down menu containing actions for (1) adding to and updating the MED in the registry, and (2) for showing the Model Extension Registry View.
- Validation Message - this area will display an OK message or an error summary message. When a summary message is shown, the tooltip for that message will enumerate all the messages.
- Toolbar - contains the same actions as the drop-down menu.
Figure E.29. Shared Header Example
Appendix F. Teiid Designer Main Menu
F.1. Teiid Designer Main Menu
- Resource management actions
- Standard edit actions including undo/redo
- Resource actions (i.e. Rename, Move, etc...)
- Find data within your workspace.
- Model level actions
- Custom metadata-related actions
- Change perspectives or add/remove views to your perspective.
- Access available Teiid Designer help documents, Teiid Designer SQL Support Guide and Eclipse Overview information.
Figure F.1. Application Main Menu
F.2. File Menu
Figure F.2. File Menu
Figure F.3. File Menu
- New > Model Project - Create user a new model project.
- New > Folder - Create new folder within an existing project or folder.
- New > Model - Create a new model of a specified model type and class using the New Model Wizards.
- New > Virtual Database Definition - Create a new VDB, or Virtual Database Definition.
- Open File - Enables you to open a file for editing - including files that do not reside in the Workspace.
- Close (Ctrl+W) - Closes the active editor. You are prompted to save changes before the file closes.
- Close All (Shift+Ctrl+W) - Closes all open editors. You are prompted to save changes before the files close.
- Save (Ctrl+S) - Saves the contents of the active editor.
- Save As - Enables you to save the contents of the active editor under another file name or location.
- Save All (Shift+Ctrl+S) - Saves the contents of all open editors.
- Move - Launches a Refactor > Move resource dialog..
- Rename (F2) - Launches a Refactor > Rename resource dialog if resource selected, else inline rename is preformed.
- Refresh - Refreshes the resource with the contents in the file system.
- Convert Line Delimiters To - Alters the line delimiters for the selected files. Changes are immediate and persist until you change the delimiter again - you do not need to save the file.
- Print (Ctrl+P) - Prints the contents of the active editor. In the Teiid Designer, this action prints the diagram in the selected editor. Allows control over orientation (portrait or landscape), scaling, margins and page order. User can also specify a subset of the pages to print (i.e., 2 through 8).
- Switch Workspace - Opens the Workspace Launcher, from which you can switch to a different workspace. This restarts the Workbench.
- Restart - Exits and restarts the Workbench.
- --> Import - Launches the Import Wizard which provides several ways to construct or import models..
- Export - Launches the Export Wizard which provides options for exporting model data.
- Properties (Alt+Enter) - Opens the Properties dialog for the currently selected resource. These will include path to the resource on the file system, date of last modification and its writable or executable state.
- Most Recent Files List - Contains a list of the most recently accessed files in the Workbench. You can open any of these files from the File menu by selecting the file name.
- Exit - Closes and exits the Workbench.
F.3. Edit Menu
Figure F.4. Edit Menu
- New > Child - This menu is created dynamically to support the creation of whatever types of child objects can be created under the selected object.
- New > Sibling - This menu is created dynamically to support the creation of whatever types of sibling objects can be created under the same parent as the selected object
- New > Association - This menu is created dynamically to support the creation of whatever types of associations can be created with the selected object.
- Modeling > - This menu is created dynamically. Various modeling operations are presented based on selected model object type.
- Undo - Reverses the effect of the most recent command.
- Redo - Reapplies the most recently undone command.
- Cut - Deletes the selected object(s) and copies it to the clipboard.
- Copy - Copies the selected object(s) to the clipboard.
- Paste - Pastes the contents of the clipboard to the selected context.
- Paste Special... - Provides additional paste capabilities for complex clipboard objects.
- Clone - Duplicates the selected object in the same location with the same name. User is able to rename the new object right in the tree.
- Delete - Deletes the selected object(s).
- Select All - Select All objects in current view.
- Rename - Allows a user to rename an object in the tree.
- Find/Replace - Launches dialog that can be used to search in the current text view, such as a Transformation Editor.
- Open - Opens the selected object in the appropriate editor.
- Edit - Opens the selected object in the appropriate specialized editor, such as the Choice Editor or Recursion Editor..
- Add Bookmark... - This command adds a bookmark in the active file on the line where the cursor is currently displayed.
- Add Task... - This command adds a task in the active file on the line where the cursor is currently displayed.
F.4. Refactor Menu
Figure F.5. Refactor Menu
- Undo - Undo the last refactor command.
- Redo - Redo the last undone refactor command.
- Move - Move a model from one container (folder or project) to another.
- Rename - Rename a model.
F.5. Navigate Menu
Figure F.6. Navigate Menu
F.6. Search Menu
Figure F.7. Search Menu
Figure F.8. Search Menu
- Transformations... - Launches the Transformation Search dialog. User can search models in the workspace for matching SQL text. Search results appear in the dialog and user can select and view SQL as well as open desired transformations for editing.
- Metadata... - Launches the Search dialog. User can search for models in the workspace by specifying an Object Type, and/or a Data Type, and/or a property value. Search results appear in the Search Results view, and double-clicking a result will open that model in the appropriate editor.
- Find Model Object - Launches the Find Model Object dialog, which can be used to find an object in the workspace by specifying all or part of its name. Selecting the object will open it in the appropriate editor.
F.7. Project Menu
Figure F.9. Project Menu
- Open Project - Launches the Open Project dialog.
- Close Project - Closes the currently selected project(s).
- Build All - Validates the contents of the entire workspace. Any errors or warnings will appear in the Problems View.
- Build Project - Validates the contents of the selected project(s). Any errors or warnings will appear in the Problems View.
- Build Working Set - Validates the contents of the selected working set. Any errors or warnings will appear in the Problems View.
- Clean.. - Launches the Clean dialog.
- Build Automatically - Sets the Build Automatically flag on or off. When on, a checkmark appears to the left of this menu item. When this is turned on, validation of changes is done automatically each time a Save is done.
- Clone Project - Launches the Clone Project dialog.
- Build Project Imports - Reconciles all model import dependencies for models contained within the selected project.
- Build All Imports - Reconciles all model import dependencies for models contained within the workspace.
- Build Packages - TBD
- Validate Model Transformations - Revalidates all transformations for the selected view model.
- Properties - Displays the operating system's file properties dialog for the selected file.
F.8. Metadata Menu
Figure F.10. Metadata Menu
- Update Model from Source - If the selected model is a relational source model that was originally created via JDBC Import, then the model will be updated based on changes in the database schema.
- Show System Catalog - Opens the System Catalog view.
- Show Datatype Hierarchy - Opens the System Catalog view.
- Re-resolve References - Analyzes references within models to other model components.
- Convert to Enterprise Datatypes - Adds an additional property to simple datatypes within your selected schema model to label them as enterprise datatypes.
- Show Model Statistics - Opens the Model Statistics dialog for the selected model.
- Build Model Imports - Reconciles all model import dependencies for the selected model.
F.9. Run Menu
Figure F.11. Window Menu
F.10. Window Menu
Figure F.12. Window Menu
Note
Figure F.13. Show View Dialog
F.11. Help Menu
Figure F.14. Help Menu
- Welcome - Shifts to the Welcome perspective, which contains links to documentation, examples and 'how-to' starting points.
- Help Contents - Launches the Help Window. All of Designer's online documentation is accessible from there as well.
- Search - Launches the Help Search view, which can be used to search for phrases in the documentation.
- Dynamic Help - Opens the docked dynamic help view.
- Key Assist (Ctrl-Shift-L) ... - Launches a dialog describing existing key assist bindings.
- Tips and Tricks... - Launches a dialog to select one of any contributed Tips and Tricks help pages.
- Cheat Sheets... - Launches a dialog to select one of any contributed Eclipse cheat sheets.
- Project Examples... - A JBoss contributed action which provides quick access to import various project examples into your workspace.
- Report Problem - A JBoss contributed action which provides simple problem reporting.
- Check for Updates... - provides access to retrieve updates to installed Eclipse software.
- Install New Software... - provides access to install new software into your workbench.
- About JBoss Developer Studio - Launches the About dialog.
Appendix G. Revision History
Revision History | |||
---|---|---|---|
Revision 6.2.0-03 | Fri Nov 20 2015 | David Le Sage | |
|