Poll vs Push in Database using JBoss Teiid

Sometimes you have to monitor a specific table or view in your database, and do some action when INSERT or UPDATE happens. There are two approaches - you can periodically poll the table using Quartz or any other scheduling tool. Or you can be notified by database itself when your table modified - database will push you the new data.
The second approach even more beneficial when your table get updates rarely (so you not waste CPU by returning empty results most of the time), or when you want to be notified immediately when the new data available.

The good solution to the task above on my mind is database trigger. The commercial database vendors (like Oracle, if I am not mistaken) provide a triggers which (when fired up) are capable to run an arbitrary java code. But what if you don't have Oracle, and the data source of your choice is mySQL, Apache Hive, H2, or even arbitrary CSV file, laying down somewhere on filesystem?

Here is my approach: I made an UPDATABLE VIEW for my table in H2 database under JBoss Teiid, then created a VIRTUAL FUNCTION (Teiid virtual functions are capable to run specified java class and method). Last step - define two triggers: INSTEAD OF UPDATE and INSTEAD OF INSERT. When my table in H2 got a new record, or existing one updated - my java class gets immediately executed by Teiid.

Implementation Details:

• Below is the Teiid 8.1 model definition with VIEW, VIRTUAL FUNCTION and two TRIGGERs defined. Note a property "lib" - it is a name of JBoss 7 Module with your java class in it (yes, you have to create a JBoss module. It is a simple task and well documented).

 <vdb name="DynamicPortfolio" version="1">  
   ...  
   <property name="lib" value="com.test.teiid.translator.async" />  
   <model name="TrigView" type="VIRTUAL">  
      <metadata type="DDL"><![CDATA[  
             CREATE VIRTUAL FUNCTION RepHealth(  
                     healthTime varchar,  
                     policyKey varchar,  
                     objKey varchar,  
                     healthState varchar)   
                RETURNS integer   
                OPTIONS (JAVA_CLASS 'com.test.teiid.udf.HealthStatesTeiidFunction',   
                     JAVA_METHOD 'reportHealthState');  
           CREATE VIEW HS_VIEW OPTIONS(UPDATABLE TRUE)  
                as select * from Accounts.HEALTHSTATE;  
             CREATE TRIGGER ON HS_VIEW INSTEAD OF INSERT  
                AS FOR EACH ROW  
                BEGIN ATOMIC  
                     SELECT RepHealth(  
                          New.HEALTHTIME, New.POLICYKEY, New.OBJKEY, New.HEALTHSTATE)   
                     from HS_VIEW;       
                END;  
             CREATE TRIGGER ON HS_VIEW INSTEAD OF UPDATE  
                AS FOR EACH ROW  
                BEGIN ATOMIC  
                     SELECT RepHealth(  
                          New.HEALTHTIME, New.POLICYKEY, New.OBJKEY, New.HEALTHSTATE)   
                     from HS_VIEW;       
                END;  
      ]]></metadata>  
   </model>  
 ...  
 </vdb>  

• The VDB changes above should work with Teiid's "dynamicvdb-portfolio" example, I installed this example on my Teiid in advance and created schema for additional "Accounts.HEALTHSTATE" table in H2. The DDL is below. You can add a new model right into portfolio-vdb.xml from the example.

 CREATE TABLE HEALTHSTATE  
 (  
   HS_ID integer,  
   HEALTHTIME varchar(8),  
   POLICYKEY varchar(8),  
   OBJKEY varchar(8),  
   HEALTHSTATE varchar(8),  
   CONSTRAINT HEALTHSTATE_PK PRIMARY KEY(HS_ID),  
 );  

• Write your java class and package it as JBoss Module. The called java method should be static.

 package com.test.teiid.udf;  
 public class HealthStatesTeiidFunction {  
      public static int reportHealthState(String healthTime, String policyKey, String objKey, String healthState)  
      {  
           String ret = "At:" + healthTime + " for Policy:" + policyKey + " and Object:" + objKey + " the State is:" + healthState;  
           System.out.println(ret);  
           return 1;  
      }  
 }  

• Start JBoss, make sure your module and VDB are deployed and active. Connect to your database and issue INSERT or UPDATE statement against your defined view.

 insert into HS_VIEW(HS_ID, HEALTHTIME, POLICYKEY, OBJKEY, HEALTHSTATE)  
 values(4, '10:28 AM', 'policy1', 'obj1', 'RED');   

• See the java output in console:

 13:51:18,336 INFO [stdout] (Worker0_QueryProcessorQueue0) At:10:28 AM for Policy:policy1 and Object:obj1 the State is:RED  

Note that since triggers defined as "INSTEAD OF..", the actual insert or update does not happen. Also, in order to be notified by the database changes, your underlying system should issue inserts or updates not to the actual H2 table, but to Teiid UPDATEABLE VIEW we defined (HS_VIEW in example above). Teiid itself is a federated database, available by JDBC, but connecting to it might be an added complexity to your solution.