SQL Store

As a application developer or data grid administrator, I want to preload a cache from a relational database accessed by JDBC - via configuration without writing boilerplate code.

Solution
A cache store will be implemented to allow users achieve that goal by following the steps.
The cache store is configured per cache.

Step 1. Configuration - The required set of parameters are:
Data source configuration:

SQL query to retrieve I.e “select isin, title as t, description from books”

(optional) SQL insert/update (upsert) statements to persist data

A query that will retrieve all the keys and values to put into the cache, with the following structure and conventions:

• First column, will map to a key of type String
• Rest of columns, will map to a value of type: String, Byte[] or a complex Protobuf object

Mapping of every row to a cache key/value will work as follows:

• Simple key and values: If the query returns only 2 columns, and the second one is either String or Byte[], the value will be the content of the second column. No protobuf schema is needed.
  Example, 'select code, country from country_list"

• Simple key and complex value. If query returns 2+ columns, a Protobuf schema will be automatically generated and used to encode all the columns' values.
  Example, "select code, country, region, total_sales from sales_results"

Step 2. Execution on cache initialization
The cache loader will:

1. Flush the cache
2. Run the SQL query
3. Generate Protobuf schema, if needed. Register it in the Protobuf schemas cache.
4. Transform every row of the resultset into a key and a simple value, or an object composed of simple types.
5. Put(key,value) into the cache
6. The cache will not available during the initialization process

Optional, Step 3. Additional refresh
To refresh the data after the startup, several options will exist:

1. server restart
2. Invoke a JMX operation
3. Use CLI to invoke same operation
4. Use console to invoke same operation

Other usability considerations.
Generated Protobuf schemas will be available for usage by remote clients by:

• Download from console, for administration.
• Printed out in console during generation, for copy & paste, for regular development.
• Access the protobuf cache directly, for programmatic usage.
• Console will be updated with this capability