[teiid-issues] [JBoss JIRA] (TEIID-2584) Add management features to materialization

[Ramesh Reddy (JIRA)]

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

Ramesh Reddy commented on TEIID-2584:
-------------------------------------

For Example if the DDL of view is like this (note all the OPTION properties with "teiid_rel:MATVIEW_" prefix are being proposed)

{code:lang=SQL}
        CREATE VIEW actor (
           actor_id integer,
           first_name varchar(45) NOT NULL,
           last_name varchar(45) NOT NULL,
           last_update timestamp NOT NULL
        ) OPTIONS (MATERIALIZED 'TRUE', 
                MATERIALIZED_TABLE 'pg.public.mat_actor', 
                teiid_rel:MATVIEW_STATUS_TABLE 'pg.public.status', 
                teiid_rel:MATVIEW_STAGING_TABLE 'pg.public.mat_actor_staging'
                teiid_rel:MATVIEW_TRUNCATE_SCRIPT 'truncate table pg.public.mat_actor_staging'
                teiid_rel:MATVIEW_SWAP_SCRIPT 'ALTER TABLE pg.public.mat_actor RENAME TO pg.public.mat_actor_temp; ALTER TABLE pg.public.mat_actor_staging RENAME TO pg.public.mat_actor; ALTER TABLE pg.public.mat_actor_temp RENAME TO pg.public.mat_actor_staging;') 
                teiid_rel:MATVIEW_LOAD_SCRIPT 'select * into pg.public.mat_actor from sakila.actor OPTION NOCACHE;') 
            AS SELECT actor_id, first_name, last_name, last_update from pg."public".actor;
{code}

then based on above metadata I will either execute a anonymous block or create a "load" procedure like

{code}
CREATE VIRTUAL PROCEDURE load()
BEGIN
    DECLARE string status;
    status = (select state FROM teiid_rel:MATVIEW_STATUS_TABLE WHERE tablename='actor');
    IF (status <> 'LOADING' or status <> 'LOADED')
        BEGIN
            update teiid_rel:MATVIEW_STATUS_TABLE set state = 'LOADING' where tablename = 'sakila.actor';                        
            teiid_rel:MATVIEW_TRUNCATE_SCRIPT
            teiid_rel:MATVIEW_LOAD_SCRIPT
            teiid_rel:MATVIEW_SWAP_SCRIPT
            update teiid_rel:MATVIEW_STATUS_TABLE set state = 'LOADED', valid=true, updated=now(), cardinality = VARIABLES.ROWCOUNT where tablename = 'sakila.actor';
       EXCEPTION e 
        update teiid_rel:MATVIEW_STATUS_TABLE set state = 'FAILED_LOAD', valid=false, updated=now(), cardinality = -1 where tablename = 'sakila.actor';
       END
END
{code}

and 

{code}
CREATE VIRTUAL PROCEDURE needsLoading(ttl, tblName) returns boolean
BEGIN
    DECLARE boolean valid;
    DECLARE long last_update;

    LOOP ON select valid, last_update FROM teiid_rel:MATVIEW_STATUS_TABLE WHERE tablename='tblName' AS status
    BEGIN
        IF (status.valid = null )
        BEGIN  
            insert into teiid_rel:MATVIEW_STATUS_TABLE (tablename, valid, state, cardinality, updated) values ('tblName', 'false', 'NEEDS_LOADING', -1, now());
        END
    ELSE (status.valid = false or (status.valid = true and status.last_update < now()-ttl)
        BEGIN
            update teiid_rel:MATVIEW_STATUS_TABLE set state = 'NEEDS_LOADING' where tablename = 'sakila.actor';
        END
    END
END
{code}

Where during the execution check the "needsLoading" if true, then execute the "load"
                
> Add management features to materialization
> ------------------------------------------
>
>                 Key: TEIID-2584
>                 URL: https://issues.jboss.org/browse/TEIID-2584
>             Project: Teiid
>          Issue Type: Feature Request
>            Reporter: Ramesh Reddy
>            Assignee: Ramesh Reddy
>             Fix For: 8.5
>
>
> Currently Teiid supports internal and external materialization features. The internal is managed completely by the Teiid query engine along with the infinispan cache. 
> External materialization is completely unmanaged and left out to the user to manage it externally. This goals for this feature are unify the materialization logic for internal and external, such that both are managed similarly irrespective of the type of materialization chosen. 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira