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

[ https://issues.jboss.org/browse/TEIID-5285?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-5285: --------------------------------------- Here is a proposal to redirect updates (with no later resolution/merge to the underlying source) down to row level. Given tbl with a primary key, we need to create tbl_modifications (jdg, pg?) that contains the same columns (and potentially indexes) with an additional state column. State could be one of three values (created, deleted, updated), which could just be numeric values as we don't have an enum concept. A view will be created as the new access point for tbl: {code} SELECT * from tbl left outer join tbl_modifications on (pk predicates) where tbl_modifications.state is null union all SELECT col, ... from tbl_modifications where state <> deleted {code} An issue with the above is if a created row end is also then populated in the actual store. We can leave it as a TODO to resolve that. Performance could be an issue as any joins or operations performed above the view would likely need to have federated processing as the optimizer would not generally be able to remove the union or the left outer join. The update triggers on the view would look like: {code} create trigger on ... instead of insert begin atomic merge into tbl_modifications (col, ..., state) values (new.col, ..., created); end {code} {code} create trigger on ... instead of delete begin atomic merge into tbl_modifications (col, ..., state) values (null, ..., deleted); end {code} An enhancement for delete is to differentiate between rows that were created and those that were updated - the former can be deleted completely. It would be easy to have: {code} create trigger on ... instead of update begin atomic merge into tbl_modifications (col, ..., state) values (new.col, .., updated); end {code} However that update is too simplistic as a modification of the primary key would need to be treated as delete and a create. [~rareddy] does something based upon the above seem workable? -- This message was sent by Atlassian JIRA (v7.5.0#75005)