RowLoader support in SQLFabric

RowLoader Invocation

SQLFabric RowLoaders will be invoked when a SELECT query is executed against a table with a WHERE clause that includes an equals (=) test for every component that is part of the table's primary key and an entry for that primary key does not exist in memory. It doesn't matter which columns are requested or used from the resulting row.

Example

For the table:

CREATE TABLE LoaderTest(id INTEGER, name VARCHAR(10), result VARCHAR(10), primary key (id, name))

These queries will invoke the loader:

SELECT * FROM LoaderTest WHERE id = 5 AND name='Joe';
SELECT result FROM LoaderTest WHERE id = 6 AND name='Sam';

These queries will not invoke the loader:

SELECT result FROM LoaderTest WHERE id <6 AND name='Sam';
SELECT * FROM LoaderTest;
SELECT * FROM LoaderTest WHERE id = 5;

RowLoader Implementation

To create a RowLoader, the user has to define a class implementing the interface com.gemstone.sqlfabric.callbacks.RowLoader.

Additionally, the implementation of RowLoader must also include a public static method, that takes no parameters, that returns an instance of the loader. This method may either create a new loader for each table, or access a static, common instance that can service a number of tables - this functionality depends highly upon how the loader is written.

When the loader is registered, initialization parameters can be included in the registration. The init() method will always be invoked on the loader, and any supplied parameters will be passed, when the loader is registered to a table (see below).

The getRow(String schemaName, String tableName, Object[] primarykey) method provides the schema name, table name and an array of objects that are the primary key values (in the order presented by the table definition) each time the loader is invoked to fetch data from the archive database. This method should return one of the following:

• An object array with one element with the value for each column, including the primary key, in the order defined by the SQLFabric table definition
• null, if there is no element found
• An instance of java.sql.ResultSet, possibly the result of a query against another database. Only the first row will be used. The result columns should match the SQLFabric table.
• An empty java.sql.ResultSet if no element is found

There are two example RowLoaders provided:

• ExampleRowLoader shows how you might implement a row loader if your archive datastore is not a relational database. It can also be used to test RowLoaders without having an archive datastore available.
• JDBCRowLoader is a generic Row Loader that can be used to load data from any relational database when a JDBC driver is provided on the SQLFabric classpath.

RowLoader Registration

RowLoaders are registered to the table when the table is created, as part of the CREATE TABLE DDL.

 CREATE TABLE table-name
 {({ column-definition | table-constraint }
 [ , { column-definition | table-constraint } ] * ) }
 loader ( <fully qualified loader class name>.<get instance method> parameter [, parameter] * )

The class specified as the loader must be available on the classpath of all JVMs where the table will be created. String parameters should be enclosed in single quotation marks.

Example of CREATE TABLE DDL with a row loader:

CREATE TABLE LoaderTest (id INTEGER, name CHAR(10), result CHAR(10), primary key (id, name))
 LOADER (example.gemstone.sqlfabric.loader.SimpleRowLoader.create 'myString',1)