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

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-5285:
---------------------------------------

In general implementing the constraint handling will get tricky.  You have to consider how far it makes sense to go with it - enforce unique, check constraints, cascading of deletes, etc.

>  CREATE TRIGGER ON customer INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC DECLARE string VARIABLES.X = (SELECT SSN FROM master.CUSTOMER WHERE SSN = NEW.SSN); IF (VARIABLES.X IS NULL) BEGIN INSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (NEW.SSN, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PHONE, 1); END END;

There would be an else case that throws an exception if the key already exists.

> CREATE TRIGGER ON customer INSTEAD OF DELETE AS FOR EACH ROW BEGIN ATOMIC DECLARE boolean VARIABLES.X = (SELECT x.return FROM (EXECUTE CHECK_ACCOUNTS_CUSTOMER_DELETE(OLD.SSN)) AS x); IF (VARIABLES.X) BEGIN UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (OLD.SSN, OLD.FIRSTNAME, OLD.LASTNAME, OLD.PHONE, 3); END ELSE BEGIN RAISE SQLEXCEPTION 'constaint violation with Account table'; END END;

> CREATE VIRTUAL PROCEDURE CHECK_ACCOUNTS_CUSTOMER_DELETE(ssn_value string) RETURNS boolean AS BEGIN DECLARE string VARIABLES.X = (SELECT SSN FROM master.ACCOUNT_MODIFIED WHERE SSN = ssn_value AND STATUS <> 3); DECLARE string VARIABLES.Y = (SELECT SSN FROM master.ACCOUNT WHERE SSN = ssn_value); IF (VARIABLES.X IS NULL AND VARIABLES.Y IS NULL) BEGIN RETURN cast('TRUE'AS boolean); END RETURN cast('FALSE'AS boolean);

You should probably have a view for account as well, and just query it here.  Issuing these separate queries won't quite be correct as an account could already have been deleted and will still be detected in the second query.

> RETURN cast('TRUE'AS boolean); END RETURN cast('FALSE'AS boolean);

You can just use TRUE and FALSE keywords :)


> 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
>
>         Attachments: redirection-vdb.xml
>
>
> 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)