6 Replies Latest reply on Feb 7, 2012 1:23 PM by rareddy

    Define View Definitions in Dynamic VDB (TEIID-1280)

    rareddy

      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

          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)
            rareddy

            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)
              shawkins

              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)
                rareddy

                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)
                  shawkins

                  "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)
                    rareddy

                    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