[teiid-issues] [JBoss JIRA] (TEIID-5285) Add high-level feature for redirection of updates

[Ramesh Reddy (JIRA)]

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

Ramesh Reddy commented on TEIID-5285:
-------------------------------------

[~shawkins] I started with

{code}
<vdb name="redirection" version="1">
    <!--1-insert, 2-update, 3-delete for status-->
    <model visible="false" name="master">
        <source name="master" translator-name="postgresql" connection-jndi-name="java:/pgMaster"/>
        <metadata type = "DDL"><![CDATA[        
            CREATE FOREIGN TABLE CUSTOMER
            (
               SSN varchar(10),
               FIRSTNAME varchar(64),
               LASTNAME varchar(64),
               ST_ADDRESS varchar(256),
               APT_NUMBER varchar(32),
               CITY varchar(64),
               STATE varchar(32),
               ZIPCODE varchar(10),
               PHONE varchar(15),
               PRIMARY KEY(SSN)
            ) OPTIONS (UPDATABLE 'FALSE');	

            CREATE FOREIGN TABLE CUSTOMER_MODIFIED
            (
               SSN varchar(10),
               FIRSTNAME varchar(64),
               LASTNAME varchar(64),
               ST_ADDRESS varchar(256),
               APT_NUMBER varchar(32),
               CITY varchar(64),
               STATE varchar(32),
               ZIPCODE varchar(10),
               PHONE varchar(15),
               STATUS integer,
               PRIMARY KEY(SSN)
            ) OPTIONS (UPDATABLE 'TRUE');            	
        ]]>
       </metadata>   
    </model>
    
    <model name="whatever" type="VIRTUAL">
        <metadata type = "DDL"><![CDATA[        
            CREATE VIEW customer OPTIONS (UPDATABLE 'TRUE') AS
               SELECT o.SSN, o.FIRSTNAME, o.LASTNAME, o.PHONE FROM master.CUSTOMER o LEFT OUTER JOIN master.CUSTOMER_MODIFIED m ON (o.SSN=m.SSN) where m.STATUS is null
                UNION ALL
               SELECT SSN, FIRSTNAME, LASTNAME, PHONE FROM master.CUSTOMER_MODIFIED where STATUS <> 3;
            
            CREATE TRIGGER ON customer INSTEAD OF INSERT AS
            FOR EACH ROW
            BEGIN ATOMIC
                UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (NEW.SSN, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PHONE, 1);
            END;
            
            CREATE TRIGGER ON customer INSTEAD OF DELETE AS
            FOR EACH ROW
            BEGIN ATOMIC
                UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (OLD.SSN, OLD.FIRSTNAME, OLD.LASTNAME, OLD.PHONE, 3);
            END;
            
            CREATE TRIGGER ON customer INSTEAD OF UPDATE AS
            FOR EACH ROW
            BEGIN ATOMIC
            IF (CHANGING.SSN)
            BEGIN
                DECLARE string VARIABLES.X = (SELECT SSN FROM master.CUSTOMER_MODIFIED);
                IF (VARIABLES.X is NULL)
                BEGIN
                    UPSERT INTO master.CUSTOMER_MODIFIED (SSN, STATUS) values (OLD.SSN, 3);
                    UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (NEW.SSN, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PHONE, 1);
                END ELSE
                BEGIN
                    RAISE SQLEXCEPTION 'duplicate key';
                END
            END ELSE
                BEGIN
                    UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (NEW.SSN, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PHONE, 2);
                END
            END;                      
        ]]>
       </metadata>       
    </model>
</vdb>
{code}

and executed 

{code}
delete from customer where SSN = 'CST01006'

insert into customer (SSN, FIRSTNAME, LASTNAME, PHONE) values ('1111', 'Ramesh', 'Reddy', '(314)-555-1212');

update customer set FIRSTNAME = 'Bob' where SSN = 'CST01035';
update customer set FIRSTNAME = 'Henry' where SSN = 'CST01035';
{code}

When it came to second update it was not taking place. Not sure, what needs to be changed?

> Add high-level feature for redirection of updates
> -------------------------------------------------
>
>                 Key: TEIID-5285
>                 URL: https://issues.jboss.org/browse/TEIID-5285
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine, Teiid Spring Boot
>            Reporter: Steven Hawkins
>            Assignee: Ramesh Reddy
>             Fix For: 11.x
>
>
> In microservices testing it is desirable to test against production/live data but not commit any updates.  We should offer a simple solution that can defined by extension metadata and enabled/disabled by a feature flag.  We may need to make simplifying assumptions about the scope (per session, per application, etc.) and durability of the updates.
> Under the covers this will be achieved by using views, update triggers, and a store for the updates and when not enabled the expectation is that all operations should pass through.  However the application will be limited to using Teiid SQL and will be required to use the Teiid or pg driver, or Teiid spring boot.



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)