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

ADO .NET c# Connection Pooling with vFabric SQLFire

Whether it's accessing a database using JAVA or in this case c# I always want to use a connection pool and in this example I show and simple way to do this with a c# ADO .NET client accessing SQLFire.

1. Add a reference to your Visual Studio project in the VMware.Data.SQLFire.dll. This DLL is installed in the  

vFabric_SQLFire_11_bNNNNN\adonet\lib directory.

2. Reference the driver namespace in each source file where you want to use SQLFire components. For example, include this directive with all other references required in your application:

using VMware.Data.SQLFire;

3.  Create a c# console application as follows

  
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VMware.Data.SQLFire;
using System.Data;

namespace SQLFireDemo
{
    class QueryDemo
    {
        private string sqlfHost = "192.168.1.4";
        private int sqlfPort = 1527;

        public QueryDemo()
        {
        }

        private string GetConnectionString()
        {
            return string.Format(@"server={0}:{1}", sqlfHost, sqlfPort);
        }

        public void run()
        {
            using (SQLFClientConnection conn = new SQLFClientConnection(GetConnectionString()))
            {
                conn.Open();
                SQLFCommand command = new SQLFCommand
                      (string.Format("SELECT * FROM dept"), conn);
                SQLFDataReader reader = command.ExecuteReader();

                try
                {
                    StringBuilder row = new StringBuilder();
                    while (reader.Read())
                    {
                        Console.WriteLine(string.Format("Dept[deptno={0}, dname={1}]",
                                          reader.GetString(0),
                                          reader.GetString(1)));
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }
                finally
                {
                    reader.Close();
                }
            }

        }

        static void Main(string[] args)
        {
            QueryDemo test = new QueryDemo();
            test.run();
        }
    }
}

Output as follows

Dept[deptno=10, dname=ACCOUNTING]
Dept[deptno=20, dname=RESEARCH]
Dept[deptno=30, dname=SALES]
Dept[deptno=40, dname=OPERATIONS]
Dept[deptno=50, dname=MARKETING]
Dept[deptno=60, dname=DEV]
Dept[deptno=70, dname=SUPPORT]
Press any key to continue . . .

vFabric GemFire and the Native Client World using c#

Recently I had to step out of my comfort zone and learn how to create a c# client to access a GemFire 7 distributed system for a demo to a customer. OIf course there was more to it then just that but this outlines what you need to do to connect as a c# client to GemFire. I was using the following here.

• Visual Studio 2012
• GemFire 32 bit Naive Client

1. Install GemFire Native client 32 bit or 64 bit depending on your OS. It can be downloaded from the following location.

https://my.vmware.com/web/vmware/info/slug/application_platform/vmware_vfabric_gemfire/7_0

2. Once installed setup an ENV variable as shown below pointing to the location of the native client install.

C:\Windows\system32>echo %GFCPP%
C:\vFabric_NativeClient_32bit_7010

3. In your Visual Studio 2012 Project / Solution add a reference to GemFire DLL as shown below.

4. In Visual Studio 2012 create a cache.xml as shown below. This client cache is going to use a locator to connect to a cache server instance for the client itself.

xml/cache.xml

  
<?xml version="1.0"?>
<!DOCTYPE client-cache PUBLIC
    "-//GemStone Systems, Inc.//GemFire Declarative Caching 7.0//EN"
    "http://www.gemstone.com/dtd/cache7_0.dtd">

<client-cache>
  <pool name="client" subscription-enabled="true">
    <locator host="172.16.62.1" port="10334" />
  </pool>

  <region name="CommandRegion">
    <region-attributes refid="PROXY" pool-name="client">
    </region-attributes>
  </region>
  
  <region name="changeTrackingRegion">
    <region-attributes data-policy="normal" pool-name="client">
    </region-attributes>
  </region>
</client-cache>

5. Create 2 c# classes as shown below.

GemFireClient.cs

  
using GemStone.GemFire.Cache.Generic;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace pivotal.au.company.poc
{
    class GemFireClient
    {
        private static bool isStarted = false;
        private static GemFireClient instance = new GemFireClient();
        private string configFileLocation = "xml/cache.xml";
        private Properties<string, string> properties = Properties<string, string>.Create<string, string>();
        private CacheFactory cacheFactory;
        private Cache cache;
        IRegion<string, string> ctrRegion;

        private GemFireClient()
        {
            Console.WriteLine("Reading properties file xml/cache.xml...");
            string clientCacheXml = getCacheConfigLocation(configFileLocation);
            properties.Insert("cache-xml-file", clientCacheXml);
            Serializable.RegisterPdxSerializer(new ReflectionBasedAutoSerializer());

            cacheFactory = CacheFactory.CreateCacheFactory(properties);
            cache = cacheFactory.Create();
  
            ctrRegion = cache.GetRegion<string, string>("changeTrackingRegion");
            ctrRegion.GetSubscriptionService().RegisterRegex("."); 
            
            Console.WriteLine("ctrRegion size = " + ctrRegion.Count);
        }

        public static GemFireClient getInstance()
        {
            return instance;
        }

        public void closeClientCache()
        {
            cache.Close();
            Console.WriteLine("Client Cache closed...");
        }

        public Cache getCache()
        {
            return cache;
        }

        private static string getCacheConfigLocation(string cacheXml)
        {
            var directoryName = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
            if (File.Exists(System.Environment.GetEnvironmentVariable("COMPANY_CONFIG") + "/" + cacheXml) == true)
            {
                return System.Environment.GetEnvironmentVariable("COMPANY_CONFIG") + "/" + cacheXml;
            }
            else if (File.Exists(Path.Combine(directoryName, cacheXml)) == true)
            {
                return Path.Combine(directoryName, cacheXml);
            }
            else
            {
                throw new SystemException("Unable to find /" + cacheXml);
            }
        }
    }
}

GemFireTest.cs

  
using pivotal.au.company.poc.domain;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GemStone.GemFire.Cache.Generic;

namespace pivotal.au.company.poc
{
    class GemFireTest
    {
        GemFireClient gfClient;

        public void doInsert()
        {
            gfClient = GemFireClient.getInstance();

            // get command region
            IRegion<string, Command> commandRegion = gfClient.getCache().GetRegion<string, Command>("CommandRegion");

            // insert a Command Object into the region
            Command command = new Command();
            command.eventType = "INSERT";
            command.tableName = "Holiday";
            command.tableKey = "1";
            command.sequence = 31;
            command.payload = new Dictionary<object, object>()
                { 
                  {"Id", "1"},
               {"name", "apples"},
                  {"createdate", "10-10-2009"}
                };

            Console.WriteLine(command.ToString());

            commandRegion[command.tableKey] = command;

        }

        public void queryCommandRegion()
        {
            gfClient = GemFireClient.getInstance();

            Console.WriteLine("about to query commandRegion");

            QueryService<string, Command> queryService = gfClient.getCache().GetQueryService<string, Command>();
            Query<Command> qry = queryService.NewQuery("SELECT * FROM /CommandRegion");
            ISelectResults<Command> results = qry.Execute();
            SelectResultsIterator<Command> iter = results.GetIterator();
            while (iter.MoveNext())
            {
                Console.WriteLine(iter.Current.ToString());
            }

        }

        public void closeCache()
        {
            gfClient.closeClientCache();
        }

        public void run()
        {
            GemFireTest test = new GemFireTest();
            test.doInsert();
            test.queryCommandRegion();
            test.closeCache();
        }

    }
}

Output omitted but this should give you the general idea.