I opening the thread to discuss the design options in for TEIID-1280 which is to define views and procedures without Teiid Designer. The requirement is this needs to be defined inside the "vdb.xml" file. I see there couple of options.
Option 1: XML Based
Since we are defining these definitions in the XML file, the natural thinking is define the view and procedures in XML. Teiid Designer has defined XSD definition for Relational Metadata model, which is designed for TEIIDDES-1086 into Designer. Teiid can use the same as starting point and can be extended with view and procedure extensions.
For example, in "vdb.xml" some xml fragment like
<model visible="true" type="VIEW" name="Customers"> <source name="chicago" translator-name="oracle" connection-jndi-name="chicago-customers"/> <metadata import-type = "xml"> <view name="PARTS"> <column name="PART_ID" datatype="INTERGER" nullable="NO_NULLS"/> <column name="PART_NAME" datatype="STRING" length="255"/> <column name="PART_COLOR" datatype="STRING" length="30"/> <column name="PART_WEIGHT" datatype="STRING" length="255"/> <primary-key name="PK_PARTS"> <column-reference name="PART_ID"/> </primary-key> <transformation><!CDATA[select a.id as PART_ID, a.name as PART_NAME, b.color as PART_COLOR, b.weight as PART_WEIGHT from modelA.part a, modelB.part b where a.id = b.id]]<transformation> <insert-plan enabled=true/> <update-plan enabled=true/> <delete-plan enabled=true/> </view> <procedure name="GetPrice"> <parameter name="id" dataType=INTEGER> <parameter name="quantity" dataType=INTEGER> <result-set name="result"> <column name = "name" datatype="STRING"/> <column name = "price" datatype="DECIMAL"/> <result-set> <transformation><!CDATA[ CREATE VIRTUAL PROCEDURE BEGIN .... END ]]</transformation> <procedure> </metadata> </model>
The pros here is it easy to deal with XML parsing, however it does not seem be as natural fit and verbose.
Option 2: DDL Based
Designer also defined import option based on DDL, which has capability to import multiple different dialects and turn them into relational models. They have the corresponding DDL parser code. Teiid also could go DDL way, which is much more closer to SQL/MED and natural way to define the schema of a database. We still need to extend the DDL to define view and procedure transformations. For example this could be like the following
<model visible = "true" type = "VIEW" name = "customers"> <source name = "chicago" translator-name = "oracle" connection-jndi-name = "chicage-customers"/> <metadata import-type = "DDL"><![CDATA[ CREATE VIEW PARTS ( PART_ID integer PRIMARY KEY, PART_NAME varchar(255), PART_COLOR varchar(30), PART_WEIGHT varchar(255) ) AS ( select a.id as PART_ID, a.name as PART_NAME, b.color as PART_COLOR, b.weight as PART_WEIGHT from modelA.part a, modelB.part b where a.id = b.id ) INSERTPLAN ON, DELETEPLAN ON, UPDATEPLAN (...) CREATE PROCEDURE GetPrice (id integer, quantity integer) RETURNS VIEW result (name varchar(255), price decimal) AS BEGIN ... END ]] <metadata> </model>
The basic parsing code is already available in the ModeShape that we could fork and adopt to a particular dialect like PG or H2 and further extend for Teiid purposes. This feels much more natural. The implementation time might be litter more on this, but this seems like the correct approach.
I would like iron out what approach to take, or hear any pros or cons against each approach, then we can further discuss the format or implementation details.