6 Replies Latest reply: Feb 7, 2012 1:23 PM by Ramesh Reddy RSS

Define View Definitions in Dynamic VDB (TEIID-1280)

Ramesh Reddy Master

Hi,

 

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.

 

Thanks.

 

Ramesh..

  • 1. Re: Define View Definitions in Dynamic VDB (TEIID-1280)
    Van Halbert Apprentice

    I vote for the DDL.   Besides being SQL, hopefully down the road, this DDL could be executed thru the driver.  And using the same syntax goes towards usability.

  • 2. Re: Define View Definitions in Dynamic VDB (TEIID-1280)
    Ramesh Reddy Master

    Going in DDL route, any comments on the BNF for CREATE? I will follow up with procedures and functions as we go on.

     

     

    CREATE TABLE <table-name> <column-list>;
    
    CREATE VIEW <view-name> <column-list> AS <query-plan> 
                        [INSERT PLAN [ON|OFF] [<query-plan>]] 
                        [UPDATE PLAN [ON|OFF] [<query-plan>]] 
                        [DELETE PLAN [ON|OFF] [<query-plan>]]
                        [ACCESS PATTERN <column-name-list>]
                        [INDEX ON <column-name-list>]
                        [MATERIALIZED [ BY <table-name>]]
    
    <table-name> = schema-name<PERIOD>name [UUID <token>] [NAMEINSOURCE name]
    
    <view-name> = schema-name<PERIOD>name [UUID <token>]
    
    <column-list> = <left-paren> <column> [ {<comma> <column> ...}] <right-paren>
    
    <column>      = column-name [UUID <token>] [NAMEINSOURCE name] <data-type> ...
                                                                   [NOT NULL|NULL] 
                                                                   [AUTO_INCREMENT] 
                                                                   [CASE_SENSITIVE] 
                                                                   [UNIQUE] 
                                                                   [PRIMARY KEY] 
                                                                   [[NOT] SELECTABLE]
                                                                   [[NOT] UPDATABLE]
                                                                   [SIGNED]
                                                                   [CURRENCY]
                                                                   [DEFAULT default_value] 
                                                                   <column-constraints>
    
    <column-constraints> =  REFERENCES <table-name> <left-paren> <column-name-list> <right-paren>
    
    <query-plan> = <left-paren> sql-command <right-paren>
    
    <column-name-list> = <left-paren> column-name [{<comma> column-name ...}]
    
    <data-type> = varchar(length) | boolean | byte | smallint | char(length) | integer | long | decimal(length, decimals) | blob | clob | xml | date | time | timestamp | varbinary(length)
    
  • 3. Re: Define View Definitions in Dynamic VDB (TEIID-1280)
    Steven Hawkins Master

    Looking good.  Create table is not necessary unless we are offering it as an alternative to translator supplied metadata.  However if we do that, we should probably follow SQL/MED more closely.

     

    Why is the view query expression nested in parens?

     

    The view column list should be optional.  I know that's not per spec, but most vendors allow it.

     

    The term update plan should be considered legacy.  It should be known as an instead of trigger, the standard approach for this is CREATE TRIGGER vw_update INSTEAD OF UPDATE ON vw FOR EACH ROW ...

    Since we don't have a proper metadata construct for triggers, we could combine the syntax as you have shown above to a single statement - CREATE VIEW vw AS ... INSTEAD OF UPDATE FOR EACH ROW ...

     

    There can be any number of access patterns / indexes / unique constraints.

     

    Shouldn't the references clause be per foreign key, not on the column?

     

    We also have min/max/ndv/nnv/etc. that can be captured.  And in general you can have extension metadata on tables/views/columns/key records so it would be good to have something like a PROPERTIES clause (SQL/MED uses an OPTIONS clause for name/value pairs).

     

    Steve

  • 4. Re: Define View Definitions in Dynamic VDB (TEIID-1280)
    Ramesh Reddy Master

    Thank you. Here is revised syntax based on your input.

    Create table is not necessary unless we are offering it as an alternative to translator supplied metadata.  However if we do that, we should probably follow SQL/MED more closely.

    my intension was to provide metadata at source model level too, to compliment the jdbc translator supplied metadata. Taking your suggestion to SQL/MED it would look like.

     

    CREATE FOREIGN TABLE schemaname.tablename ( <column-list> ) [OPTIONS ( <options> )];
    
    CREATE VIEW <view-name> [(<column-list> <view-constraints>)] AS query_plan  [OPTIONS (<options>)];
      or
    CREATE VIRTUAL TABLE <view-name> [(<column-list> <view-constraints>)] AS query_plan  [OPTIONS (<options>)];
    
    CREATE TRIGGER trigger_name INSTEAD OF UPDATE ON <view-name> FOR EACH ROW [query_plan];
    
    CREATE TRIGGER trigger_name INSTEAD OF INSERT ON <view-name> FOR EACH ROW [query_plan];
    
    CREATE TRIGGER trigger_name INSTEAD OF DELETE ON <view-name> FOR EACH ROW [query_plan];
    
    <view-name> = schemaname.view_name
    
    <column-list> = ( <column> [ {<comma> <column> ...}])
    
    <column>      = column-name  <data-type> ...
                                [NOT NULL|NULL] 
                                [AUTO_INCREMENT] 
                                [UNIQUE|PRIMARY KEY] 
    
                                                            [DEFAULT default_value]
                                                            [OPTIONS (<options>)]
    
    <view-constraints>        = CONSTRAINT ( <constraint> [{<comma> <constraint> ...}]
    
    <constraint>                 = FOREIGN KEY column_name REFERENCES <view-name> (<column-name-list>)
                                          | ACCESS PATTERN ( <column_name_list> )
                                          | INDEX [UNIQUE] ( <column_name_list> )
                                          | PRIMARY KEY ( <column_name_list> )
    
    <column-name-list> = ( column-name [{<comma> column-name ...}] )
    
    <table-options>               =  property_name <space> property_value [ {<comma> property_name <space> property_value ...} ]
    
    OPTIONS ON TABLE:
                    UUID 'char'
                    NAMEINSOURCE 'char'
                     ANNOTATION 'char'
    
    OPTIONS ON VIEW:
                    UUID 'char'
                    MATERIALIZED 'YES|NO'
                    EXTERNAL_MATERIALIZED_TABLE '<table.name>'
                    CARDINALITY 'int'
                    UPDATABLE 'YES|NO'
                   ANNOTATION 'char'
    
    OPTIONS ON COLUMN:
                   UUID 'char'
                   NAMEINSOURCE 'char'
                   CASE_SENSITIVE 'YES|NO'
                   SELECTABLE 'YES|NO'
                   UPDATABLE 'YES|NO'
                   SIGNED 'YES|NO'
                   CURRENCY 'YES|NO'
                   FIXED_LENGTH 'YES|NO'
                   SEARCHABLE 'YES|NO|LIKE_ONLY|ALL_EXCEPT_LIKE'
                   MIN_VALUE 
                   MAX_VALUE 
                   CHAR_OCTET_LENGTH 'int'
                   ANNOTATION 'char'
                   NATIVE_TYPE 'char'
                   RADIX 'int'
                   NULL_VALUE_COUNT 'int'
                   
    <data-type> = varchar(length) | boolean | byte | smallint | 
                  char(length) | integer | long | decimal(length, decimals) | 
                  blob | clob | xml | date | time | timestamp | varbinary(length)
    

                   

    The notion of definition of the CREATE TIGGER either turns the respective plan on|off, irrespective of the query plan provided. By default, it would be off.

                   

    Thank you.

     

    Ramesh..

  • 5. Re: Define View Definitions in Dynamic VDB (TEIID-1280)
    Steven Hawkins Master

    "The notion of definition of the CREATE TIGGER either turns the respective plan on|off, irrespective of the query plan provided. By default, it would be off."

     

    The default comes from UPDATABLE.  If UPDATABLE, we'll use the inherent update logic - INSTEAD OF triggers are not required.   Also, the trigger action for an instead of trigger will be an atomic block, not just a query expression.  Although in the current Teiid grammar ATOMIC is optional, but implied.

     

    TABLE and VIEW OPTIONS would be the same except for materialization.  OPTIONS should also allow for any name value pair to capture extension metadata.

     

    How about just MATERIALIZED_TABLE rather than EXTERNAL_MATERIALIZED_TABLE?

     

    The length/precision/scale on the data types should be optional.  We haven't formally deprecated BIGINTEGER, but we could just use it for DECIMAL with a scale of 0.

     

    For the constraint it should be INDEX | UNIQUE.

     

    Steve

  • 6. Re: Define View Definitions in Dynamic VDB (TEIID-1280)
    Ramesh Reddy Master

    Ok, with your corrections + addtion of function stuff

     

    CREATE FOREIGN TABLE schemaname.tablename ( <column-list> ) [OPTIONS ( <options> )];
    
    CREATE VIEW <view-name> [(<column-list> <view-constraints>)] AS query_plan  [OPTIONS (<options>)];
    
    CREATE TRIGGER trigger_name INSTEAD OF UPDATE ON <view-name> FOR EACH ROW [<atomic-block>];
    
    CREATE TRIGGER trigger_name INSTEAD OF INSERT ON <view-name> FOR EACH ROW [<atomic-block>];
    
    CREATE TRIGGER trigger_name INSTEAD OF DELETE ON <view-name> FOR EACH ROW [<atomic-block>];
    
    <view-name> = schemaname.view_name
    
    <atomic-block> = BEGIN ATOMIC tigger_query END
    
    
    <column-list> = ( <column> [ {<comma> <column> ...}])
    
    <column>      = column-name  <data-type> ...
                                [NOT NULL|NULL] 
                                [AUTO_INCREMENT] 
                                [UNIQUE|PRIMARY KEY] 
                                [DEFAULT default_value]
                                [OPTIONS (<options>)]
    
    <view-constraints> = CONSTRAINT ( <constraint> [{<comma> <constraint> ...}]
    
    <constraint>  = FOREIGN KEY column_name REFERENCES <view-name> (<column-name-list>)
                                          | ACCESS PATTERN ( <column_name_list> )
                                          | [INDEX|UNIQUE] ( <column_name_list> )
                                          | PRIMARY KEY ( <column_name_list> )
    
    <column-name-list> = ( column-name [{<comma> column-name ...}] )
    
    <table-options> =  property_name <space> property_value [ {<comma> property_name <space> property_value ...} ]
    
    
    OPTIONS ON TABLE/VIEW: (the below are well known options, any others will be treated as extension metadata)
        UUID 'char'
        MATERIALIZED 'YES|NO'
        MATERIALIZED_TABLE '<table.name>'
        CARDINALITY 'int'
        UPDATABLE 'YES|NO'
        ANNOTATION 'char'
    
    OPTIONS ON COLUMN: (the below are well known options, any others will be treated as extension metadata)
        UUID 'char'
        NAMEINSOURCE 'char'
        CASE_SENSITIVE 'YES|NO'
        SELECTABLE 'YES|NO'
        UPDATABLE 'YES|NO'
        SIGNED 'YES|NO'
        CURRENCY 'YES|NO'
        FIXED_LENGTH 'YES|NO'
        SEARCHABLE 'YES|NO|LIKE_ONLY|ALL_EXCEPT_LIKE'
        MIN_VALUE 
        MAX_VALUE 
        CHAR_OCTET_LENGTH 'int'
        ANNOTATION 'char'
        NATIVE_TYPE 'char'
        RADIX 'int'
        NULL_VALUE_COUNT 'int'
                   
    <data-type> = varchar([length]) | boolean | byte | smallint | 
                  char(length) | integer | long | decimal([length[,decimals]]) | 
                  blob | clob | xml | date | time | timestamp | varbinary([length])
    

     

    Function Syntax

     

    CREATE [PUSHDOWN] FUNCTION schema_name.func_name ( <func_parameters> ) RETURNS <data_type> [OPTIONS (<options>)]
    
    <func_parameters> =  <func_parameter> [{<comma> <func_parameter> ...}]
    
    func-parameter = param_name <data-type>
    
    CREATE PROCEDURE schema_name.proc_name ( <parameters> ) RETURNS [<data_type> RESULTSET <resultset>] [OPTIONS (<options>)] AS <query_block>
    
    parameters = <parameter> [{<comma> <parameter> ...}]
    
    parameter = [IN|OUT|INOUT]  param_name <data-type>
    
    resultset = <column_def> [{<comma> <column_def> ...}]
    
    column_def = column_name <datatype>
    
    <query_block> = BEGIN proc_query END
    
    FUNCTION_OPTIONS:(the below are well known options, any others will be treated as extension metadata)
                   UUID
                   NAMEINSOURCE
                   ANNOTATION
                   CATEGORY
                   DETERMINISTIC
                   NULONNULL
                   JAVA_CLASS
                   JAVA_METHOD
    
    PROCEDURE_OPTIONS:(the below are well known options, any others will be treated as extension metadata)
                   UUID
                   NAMEINSOURCE
                   ANNOTATION
                   UPDATECOUNT