Handling DML Events Synchronously with vFabric SQLFire

SQLFire provides synchronous cache plug-in mechanisms to handle cache events. This example is a synchronous listener. A listener enables you to receive after-event notifications of changes to a table (insert, update and delete). Any number of listeners can be defined for the same table. Listener callbacks are called synchronously, so they will cause the DML operation to block if the callback blocks.

CommandTableEventCallBackListenerImpl.java

  
package pivotal.au.demo.poc.listener;

import java.sql.ResultSet;
import java.sql.SQLException;
import pivotal.au.demo.poc.domain.Command;
import pivotal.au.demo.poc.executor.ExecutorCommand;
import pivotal.au.demo.poc.executor.ExecutorFactory;

import com.vmware.sqlfire.callbacks.Event;
import com.vmware.sqlfire.callbacks.Event.Type;
import com.vmware.sqlfire.callbacks.EventCallback;

public class CommandTableEventCallBackListenerImpl implements EventCallback
{ 
 public void close() throws SQLException 
 {
 }

 public void init(String configuration) throws SQLException 
 {  
  System.out.println("configuration = " + configuration);
 
  System.out.println("CommandTableEventCallBackListenerImpl.init");
  
 }

 public void onEvent(Event event) throws SQLException 
 {
  if (event.getType() == Type.AFTER_INSERT)
  {
   ResultSet rset = event.getNewRowsAsResultSet();
   Command cmd = 
     new Command(rset.getInt(1), 
        rset.getString(2),
        rset.getString(3),
        rset.getString(4),
        rset.getString(5));
   
   System.out.println("Table[" + event.getTableName() + "] Command = " + cmd.toString());
   handleEvent(cmd); 
  }
  else
  {
   System.out.println("Not processing event " + event.getType().toString());
  }
  
 }
 
 private void handleEvent (Command cmd)
 {
  System.out.println("Handling event for Command with id = " + cmd.getId());
  
  ExecutorCommand execCommand = null;
  
  if (cmd.getType().equalsIgnoreCase("OS"))
  {
   execCommand = ExecutorFactory.getOSExecutorImpl();
   execCommand.runCommand(cmd.getCommand(), null);
  }
  else
  {
   // expecting to execute SQL so check if firing on sqlfire or greenplum at this stage
   execCommand = ExecutorFactory.getSQLExecutorImpl();
   if (cmd.getExecuteOnGreenplum().equalsIgnoreCase("Y"))
   {
    execCommand.runCommand(cmd.getCommand(), "GP");
   }
   
   if (cmd.getExecuteOnSqlfire().equalsIgnoreCase("Y"))
   {
    execCommand.runCommand(cmd.getCommand(), "SQLFIRE");
   }
  }

 }

}

Attach Listener to a table.

  
CREATE TABLE command_table 
(ID INT generated always as identity NOT NULL, 
 EXECUTE_ON_SQLFIRE VARCHAR(1) default 'N',
 EXECUTE_ON_GREENPLUM VARCHAR(1) default 'Y',
 command_type varchar(10),
 COMMAND VARCHAR(200) not null
 )
SERVER GROUPS (MYGROUP);

call sys.ADD_LISTENER('CommandTableEventCallBackListenerImpl', 'apples', 'command_table', 'pivotal.au.demo.poc.listener.CommandTableEventCallBackListenerImpl', '', 'MYGROUP');

More Information

http://pubs.vmware.com/vfabricNoSuite/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/caching_database/cache-plug-ins.html