[teiid-issues] [JBoss JIRA] (TEIID-5074) Support a way provide SEQUENCE in TeiidDialect

[Ramesh Reddy (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13467101#comment-13467101 ] 

Ramesh Reddy commented on TEIID-5074:
-------------------------------------

If source database like H2 defines a SEQUENCE like

{code}
CREATE SEQUENCE IF NOT EXISTS customer_seq START WITH 200 INCREMENT BY 1;
{code}

Then when you define a SEQUENCE based Entity in your JPA as

{code}
public class Customer {
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "customer_generator")
	@SequenceGenerator(name="customer_generator", sequenceName = "customer_seq")
	@Id
	Long id;
}
{code}

Then TeiidDialect expects a procedure defined in one of the visible virtual schemas as
{code}
CREATE VIRTUAL PROCEDURE customer_seq(OUT "return" long RESULT)
AS
BEGIN 
    -- Your code to retrieve the sequence from Oracle.
END;
{code}

The query that will be executed from the JPA layer will be like following to fetch teh sequence
{code}
select __x.return from (call customer_seq()) as __x
{code}

In Spring Boot implementation for auto-generation of code, sequence MUST be qualified with "schema" like "mydb.customer_seq" and then a following metadata is generated
{code}
-- in the source model "mydb" following will be generated
CREATE FOREIGN FUNCTION customer_seq(OUT "return" long RESULT)
OPTIONS ("teiid_rel:native-query" 'SELECT NEXTVAL(''customer_seq'');');

-- in the view model, the following will be generated
CREATE VIRTUAL PROCEDURE customer_seq(OUT "return" long RESULT)
AS
BEGIN 
  SELECT mydb.customer_seq(); 
END;
{code}

This model gives the flexibility to the user how they would like to implement the SEQUENCE through a virtual procedure.

> Support a way provide SEQUENCE in TeiidDialect
> ----------------------------------------------
>
>                 Key: TEIID-5074
>                 URL: https://issues.jboss.org/browse/TEIID-5074
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: JDBC Driver
>            Reporter: Ramesh Reddy
>            Assignee: Ramesh Reddy
>             Fix For: 10.0
>
>
> Currently, the sequence based Table identity generation support is not available in current TeiidDialect. The support will not be direct, but with additional constructs in VDB, we can make this available. Teiid needs to provide a way to call in source specific of way to insert a layer in Teiid to accomplish this.



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)