At the heart of the Teiid Runtime is the ability to connect to numerous different types of data sources via translators, whether built-in or custom. These translators define how a source's data is converted into the known Teiid metadata structure. For most data source types, this structure can be represented via Teiid relational DDL.
- Komodo will leverage this DDL as the common business object structure defined at design-time and included in the VDB artifact.
- For deployed data sources, Teiid API provides a getSchema() method to return this DDL for a number of data source types
So one work flow will be to:
- Define a data source (ProductsOarcle) connection (JDBC)
- Deploy the data source ProductsOarcle
- Komodo will discover the newly deployed data source
- retrieve the DDL via getSchema() method
- ProductsOarcle schema DDL will be displayed and available for use in View definitions
- Create a View containing a simple SQL query to productdata
- SELECT
So here's a scenario....
At a minimum, the following <model> element in a VDB.xml file will allow Teiid to load the schema/metadata from the deployed sqlserver Books data source named CustomerAccountsDS.
<model name="CustomerAccounts" type="PHYSICAL" visible="true">
<source name="CustomerAccounts" translator-name="sqlserver" connection-jndi-name="java:/CustomerAccountsDS" />
</model>
By calling the getSchema() api method we'll get the following DDL:
Importing CustomerAccounts from SQL Server pulls a CATALOG == CustomerAccounts and a SCHEMA == dbo
So importing DDL from the source results in named tables: CATALOG_NAME.SCHEMA_NAME.TABLE_NAME
CREATE FOREIGN TABLE "CustomerAccounts.dbo.ACCOUNT" (
ACCTID bigdecimal(18,2147483647) NOT NULL OPTIONS (NAMEINSOURCE '"ACCTID"', NATIVE_TYPE 'numeric'),
CUSTID string(12) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTID"', NATIVE_TYPE 'varchar'),
TYPE string(10) OPTIONS (NAMEINSOURCE '"TYPE"', NATIVE_TYPE 'char'),
STATUS string(10) OPTIONS (NAMEINSOURCE '"STATUS"', NATIVE_TYPE 'char'),
DATEOPENED timestamp NOT NULL OPTIONS (NAMEINSOURCE '"DATEOPENED"', NATIVE_TYPE 'datetime'),
DATECLOSED timestamp OPTIONS (NAMEINSOURCE '"DATECLOSED"', NATIVE_TYPE 'datetime'),
CONSTRAINT PK_ACCOUNT PRIMARY KEY(ACCTID),
CONSTRAINT PK_ACCOUNT UNIQUE(ACCTID)
) OPTIONS (NAMEINSOURCE '"CustomerAccounts"."dbo"."ACCOUNT"', UPDATABLE TRUE, CARDINALITY 37);
CREATE FOREIGN TABLE "CustomerAccounts.dbo.ACCOUNTHOLDINGS" (
TRANID string(12) NOT NULL OPTIONS (NAMEINSOURCE '"TRANID"', NATIVE_TYPE 'varchar'),
ACCTID bigdecimal(18,2147483647) NOT NULL OPTIONS (NAMEINSOURCE '"ACCTID"', NATIVE_TYPE 'numeric'),
INSTRID string(12) NOT NULL OPTIONS (NAMEINSOURCE '"INSTRID"', NATIVE_TYPE 'varchar'),
PURCHASEDATE timestamp NOT NULL OPTIONS (NAMEINSOURCE '"PURCHASEDATE"', NATIVE_TYPE 'datetime'),
SHARES double NOT NULL OPTIONS (NAMEINSOURCE '"SHARES"', RADIX 2, NATIVE_TYPE 'float'),
CONSTRAINT PK_ACCOUNTHOLDINGS PRIMARY KEY(TRANID),
CONSTRAINT PK_ACCOUNTHOLDINGS UNIQUE(TRANID)
) OPTIONS (NAMEINSOURCE '"CustomerAccounts"."dbo"."ACCOUNTHOLDINGS"', UPDATABLE TRUE, CARDINALITY 74);
CREATE FOREIGN TABLE "CustomerAccounts.dbo.CUSTOMER" (
CUSTID string(12) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTID"', NATIVE_TYPE 'varchar'),
FIRSTNAME string(25) NOT NULL OPTIONS (NAMEINSOURCE '"FIRSTNAME"', NATIVE_TYPE 'varchar'),
LASTNAME string(25) NOT NULL OPTIONS (NAMEINSOURCE '"LASTNAME"', NATIVE_TYPE 'varchar'),
MIDDLENAME string(15) OPTIONS (NAMEINSOURCE '"MIDDLENAME"', NATIVE_TYPE 'varchar'),
ADDRESS1 string(50) NOT NULL OPTIONS (NAMEINSOURCE '"ADDRESS1"', NATIVE_TYPE 'varchar'),
ADDRESS2 string(50) OPTIONS (NAMEINSOURCE '"ADDRESS2"', NATIVE_TYPE 'varchar'),
CITY string(25) NOT NULL OPTIONS (NAMEINSOURCE '"CITY"', NATIVE_TYPE 'varchar'),
STATEPROVINCE string(25) OPTIONS (NAMEINSOURCE '"STATEPROVINCE"', NATIVE_TYPE 'varchar'),
POSTALCODE string(15) NOT NULL OPTIONS (NAMEINSOURCE '"POSTALCODE"', NATIVE_TYPE 'varchar'),
COUNTRY string(10) OPTIONS (NAMEINSOURCE '"COUNTRY"', NATIVE_TYPE 'varchar'),
PHONE string(30) OPTIONS (NAMEINSOURCE '"PHONE"', NATIVE_TYPE 'varchar'),
SS_NUMBER string(9) NOT NULL OPTIONS (NAMEINSOURCE '"SS_NUMBER"', NATIVE_TYPE 'varchar'),
CONSTRAINT PK_CUSTOMER PRIMARY KEY(CUSTID),
CONSTRAINT PK_CUSTOMER UNIQUE(CUSTID)
) OPTIONS (NAMEINSOURCE '"CustomerAccounts"."dbo"."CUSTOMER"', UPDATABLE TRUE, CARDINALITY 37);
So in order to define a VIEW using one or more of these source tables, we'd write a DDL file named AccountViews.ddl containing:
CREATE VIEW AccountInfo (
AccountName varchar(50),
AccountType varchar(50) ),
AccountID bigdecimal(18, 2147483647)
) AS
SELECT LASTNAME,TYPE, CustomerAccounts.dbo.ACCTID FROM CustomerAccounts.dbo.ACCOUNT, CustomerAccounts.dbo.CUSTOMER;
The vdb.xml <model> entry for this ddl would be:
<model name="AccountViews" type="VIRTUAL" visible="true"/>
In theory the Komodo VDB would only need to contain a vdb.xml manifest containing both CustomerAccounts and AccountViews <model> entries AND only the AccountViews.ddl file. Since no properties in the CustomerAccounts DDL were overridden, Teiid can access the schema metadata for CustomerAccounts at VDB deployment.
Data Source Library Concept
Komodo users will be defining and managing VIEW's that reference other VIEW's and source TABLE's in the SQL. Unlike Teiid Designer & EMF, there will be no concept of a "virtual" TABLE. So it makes sense to look at "source" TABLE's as representing only sources.
There will be 2 kinds of source TABLE's:
- BASE SOURCE TABLE - TABLE's that are NOT defined in the VDB, but utilize the fact that Teiid's metadata will get loaded and be available to a VIEW at deploy time.
- OVERRIDE SOURCE TABLE: TABLE's that have properties that are changed by the user. These TABLE's (DDL) will need to be added to a VDB in order for Teiid to properly push down the property values.
- Example property is SUPPORTS UPDATE. If database table value is TRUE a user may wish to set it to FALSE for their TABLE. This will result in a CREATE FOREIGN TABLE XXXXXX ( ...... ) OPTIONS( "rel:supportsUpdate" = "false") DDL statement, or something like this.
- User logged JIRA (TEIIDDES-2982) and asked for ability to override schema/table properties for a model in an imported VDB. So in Komodo, the schema ( and Tables, etc.) for the model x in VDB foo, would also be an override case
<import-vdb name="foo" version="1">
<model name="x" visible="false">
</import-vdb>
Sample DS Tree view
- CustomerAccountsDS (properties include connection info etc..., all tables are updatable)
- ACCOUNT
- ACCOUNTHOLDINGS
- ACCOUNTHOLDINGS (Read-Only)
- ACCOUNTHOLDINGS (one or more columns is set to Read-Only)
- CUSTOMER
- CUSTOMER (Read-Only)
- CUSTOMER (SS_NUMBER column is removed from the table definition)
So in the DS above, there is a base ACCOUNTHOLDINGS table and 2 overridden tables, and the same for CUSTOMER. User may use the base table, or an overridden table, depending on the behavior/security the wish to expose in their Views (SQL) or VDB.
As discussed above
- if a base table is used, the DDL for that table is NOT required in the VDB definition.
- If an override table is used, then the DDL IS required.
- When using either table, they both represent the same schema/DS so showing the hiearchy/relationship in the DS tree is critical
Data Type Mapping
Because Teiid DDL dialect supports their own runtime data types, it's critical that the user has control over how their actual data types map to the Teiid runtime times.
In the current Designer, the JDBC importer assumes type mapping based off the type mappinges defined in Teiid's JDBCSQLTypeInfo. For some databases, Designer has implemented vendor-specific overrides via JDBC model processing. (See
Comments