[infinispan-issues] [JBoss JIRA] (ISPN-11875) JDBC Loader

JDBC Loader ----------- Key: ISPN-11875 URL: https://issues.redhat.com/browse/ISPN-11875 Project: Infinispan Issue Type: Feature Request Components: Loaders and Stores Reporter: Tristan Tarrant Priority: Optional 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 read-only cache loader will be implemented to allow users achieve that goal by following the steps. The cache loader 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” 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 complext 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: # Flush the cache # Run the SQL query # Generate Protobuf schema, if needed. Register it in the Protobuf schemas cache. # Transform every row of the resultset into a key and a simple value, or an object composed of simple types. # Put(key,value) into the cache # 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: # server restart # Invoke a JMX operation # Use CLI to invoke same operation # 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