Teiid Designer 7.5 What's New

Release 7.5 was targeted for bug fixes and any changes or feature additions required for 7.5 Teiid release.

 

Relational Model Source Functions

 

TEIIDDES-992

 

To improve ability to utilize database functions within View transformations, a new "New Child > Source Function" action and wizard was added to assist in building a source procedure that conforms to a function structure, including input and output parameters. Prior to Teiid Designer 7.5, these functions were required to be modeled as User Defined functions.

 

To create a new Source Function, select a Relational Source Model in your workspace and select "New Child > Source Function". Enter your database function name, define input parameters including datatype and length, specify output parameter info, set options and click OK.  The resulting source function will be added to your model and will be represented by the source-function.png icon.

 

create-source-function-dialog.png

 


 

New Flat File Import Wizard

 

TEIIDDES-1017

 

Teiid supports Flat Files as data sources.  In release 7.5, Teiid Designer introduces a new Import wizard designed to assist in creating the metadata models required to access the data in your flat files. As with Designer's JDBC, Salesforce and WSDL importers, the Flat File importer is based on utilizing a specific Data Tools Connection Profile.

 

The results of the importer will include a source model containing the three Flat File procedures supported by Teiid. Namely:

  • getFiles()
  • getTextFiles()
  • saveFile()

 

The importer will also create a new view model containing a view table for each of your selected flat file source files. Within these view tables will be generated SQL transformations containing the "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.

 

The TEXTTABLE function, as defined in the Teiid documentation, processes character input to produce tabular ouptut. 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 preceeding FROM clause entries.

 

TEXTTABLE(expression COLUMNS <COLUMN>, ... [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS name

 

Teiid Designer will construct the full SQL statement for each view table in the form:

 

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

 

The import option is labeled "Flat File Source >> Source and View Model" and is show below in the Import wizard's first page under the Teiid Designer category.  Note also that Designer's import labels have changed to reflect both a source (i.e. JDBC Database) and one or more target metamodels that will be created or updated during the import process.

 

teiid-designer-import-options.png

 

The first page of the wizard requires selection of an existing Flat File Source connection profile in order to locate the designer source file folder. Once selected (or created view "New..." button) the available data files will be displayed in the table.

 

Check each data file you wish to process. Each file will be used to construct a view table containing the required SQL transformation for retrieving your data and returning a result set.

 

teiid-metadata-file-import-page-1.pngUpdate Existing Model

 

Each data file will analized and validated based on a set of parser settings. You can view the details via the "Edit Parser Settings..." button.  If the validation fails, an Error Status will be displayed instead of OK and you will be required to fix the errors in order to proceed with importing that data file. You are encouraged to view the parser settings for ALL your data files to insure that header information, column names etc. are what you expect.

 

The Teiid Data File Parser Settings dialog, shown below, contains the various parser settings and TEXTTABLE() method and parameter options.  The File Contents Preview section displays a portion of the raw file contents. If your data is character delimited (default), choose the delimiter character in Delimiter Options section.  If "User header for column names" check-box is selected, the dialog will attempt to parse the header line and extract your column names. If "Fixed Width" option is selected, no header is expected and you are requried to specifiy the number of data columns, their names, data types and width of each column.

 

teiid-metadata-file-import-configure.png

 

At any time 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.

 

teiid-metadata-file-import-parse-column.png

The last page of the wizard, the Target Models Definition page, contains two model definition sections. Both sections allow you to select a workspace location to place the generated model. In the case of the View Model Definition, you can select an existing view model via the "..." browse button and the "Update Existing Model" checkbox will be selected.

 

teiid-metadata-file-import-page-2.png

This import operation will result in the generated source model shown below...

generated-flat-file-diagram.png

... and generated view tables containing the completed SQL required to access the data in your flat file using the "getFiles" procedure above and the Teiid TEXTTABLE() function.

generated-flat-file-view-table.png

 


 

New View Procedure Template

 

TEIIDDES-332

 

When you create a new view procedure the following SQL template will be defined instead of an empty SQL string. Just replace <insert SQL here> with your desired SQL statement (example:  SELECT * FROM TableA, TableB where TableA.col_A = TableB.col_B)

 

CREATE VIRTUAL PROCEDURE

      BEGIN

          <insert SQL here>;

      END

 


 

Previewable Table and Procedure Decorations

 

TEIIDDES-369

 

A green arrow decorator icon was added in 7.5 to indicate which model objects (tables, views or procedures) will be executable as shown in the figure below:

 

preview-data-decorators.png

 


 

New Teiid SQL Grammar Reserved Words View

 

TEIIDDES-958

 

Teiid supports a subset of various SQL reserved words. A full list of the supported reserved words is now available in a new SQL  Reserverd Words view.  You can open this view via "Preferences > Other ... > Teiid Designer > SQ Reserved Words" or select the "Metadata > Show SQL Reserved Words" action in the main toolbar shown below.

 

show-sql-reserved-words-action.png

sql-reserved-words-view.png

 


 

Additional Generate WAR Options

 

MTOM support  (TEIIDDES-965)

 

Support for Message Transmission Optimization (MTOM) was added to the Generate JBossws-CXF war wizard. This option will enable MTOM capabilities in your generated war file. You just need to update your response schema to identify the element(s) to optimize.

 

genwarwithmtom.png

 

JSON support   (TEIIDDES-990)

 

In addition to XML, the generated RESTEasy war now includes support for JavaScript Object Notation (JSON) data structures as input/output to and from the REST web service.  JSON is a simple data format that is commonly used to transfer data to JavaScript client code. More information about JSON can be found at the JSON Web site json.org.

 


 

New VDB Model Permissions

 

TEIIDDES-946

 

For 7.5, you can now specify, edit and manage ALTER and EXECUTE permissions for tables and columns in your VDB's in addition to the standard CRUD options. Below is an example of the VDB Data Roles dialog containing

vdb-data-roles-dialog.png