[ https://issues.jboss.org/browse/ISPN-2980?page=com.atlassian.jira.plugin.... ] Aleksandar Kostadinov updated ISPN-2980: ---------------------------------------- Description: It would be very nice is we have SQLite support for infinispan. SQLite is a powerful database supporting terabyte sized databases in a file with competitive performance. I tried to use it as a JDBC store but the best driver I find in the internet ([xerial sqlite jdbc driver|https://bitbucket.org/xerial/sqlite-jdbc]) does not implement full jdbc specification and trying to use it results in exceptions. I think that perhaps using the [non-jdbc wrapper sqlite4java|http://code.google.com/p/sqlite4java/] may make sense for infinispan because: 1. it promises better performance 2. it allows using the sqlite library from OS (xerial driver uses a customized build of sqlite) FYI here is how I setup sqlite for infinispan (unsuccessfully): {code}jboss as cli commands: /subsystem=datasources/jdbc-driver=sqlite:add(driver-name="sqlite",driver-module-name="org.xerial",driver-class-name=org.sqlite.JDBC) data-source add --name=SQLiteDS --connection-url="jdbc:sqlite:${sqlite.database.string}" --jndi-name=java:jboss/datasources/SQLiteDS --driver-name="sqlite" /subsystem=datasources/data-source=SQLiteDS/connection-properties=journal_mode:add(value="WAL") /subsystem=datasources/data-source=SQLiteDS:enable {code} {code}JBoss AS module definition (modules/org/xerial/main/module.xml): <?xml version="1.0" encoding="UTF-8"?> <module xmlns="urn:jboss:module:1.0" name="org.xerial"> <resources> <resource-root path="sqlite-jdbc.jar" /> </resources> <dependencies> <module name="javax.api" /> <module name="javax.transaction.api"/> </dependencies> </module> {code} {code}cache store/loader configuration snippet: <stringKeyedJdbcStore xmlns="urn:infinispan:config:jdbc:5.2" fetchPersistentState="false" ignoreModifications="false" purgeOnStartup="false" key2StringMapper="com.jboss.datagrid.chunchun.util.TwoWayKey2StringChunchunMapper"> <dataSource jndiUrl="java:jboss/datasources/SQLiteDS" /> <stringKeyedTable dropOnExit="false" createOnStart="true" prefix="ispn"> <idColumn name="ID_COLUMN" type="VARCHAR(255)" /> <dataColumn name="DATA_COLUMN" type="BLOB" /> <timestampColumn name="TIMESTAMP_COLUMN" type="BIGINT" /> </stringKeyedTable> </stringKeyedJdbcStore> </loaders> {code} sql driver needs to be copied in the same directory as module.xml *UPDATE:* the exception is fixed with latest dev code of xerial jdbc driver, please look at comments to see remaining problems. The Exception I'm getting is:{code} 12:53:10,683 ERROR [org.infinispan.interceptors.InvocationContextInterceptor] (MSC service thread 1-3) ISPN000136: Execution error: org.infinispan.loaders.CacheLoaderException: Error while storing string key to database; key: 'user41', buffer size of value: 4918 bytes at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.storeLockSafe(JdbcStringBasedCacheStore.java:253) [infinispan-cachestore-jdbc-5.2.5.Final.jar:5.2.5.Final] ... Caused by: java.sql.SQLException: not implemented by SQLite JDBC driver at org.sqlite.Unused.unused(Unused.java:29) [sqlite-jdbc-3.7.2.jar:] at org.sqlite.Unused.setBinaryStream(Unused.java:60) [sqlite-jdbc-3.7.2.jar:] at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setBinaryStream(WrappedPreparedStatement.java:871) at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.storeLockSafe(JdbcStringBasedCacheStore.java:247) [infinispan-cachestore-jdbc-5.2.5.Final.jar:5.2.5.Final] ... 73 more{code} The driver [does not support|http://code.google.com/p/xerial/issues/detail?id=99] setBinaryStream(), only setBytes(). Not sure if there are any other methods required by infinispan but not implemented. As a simple comparison between JDBC and direct storage, I tried an app that caches 3000 records of around 5k and 60000 records of around 0.5k (total of less than 60MiB). Bdbje store operation completes in less than a minute. With a local mysql server it takes 10 minutes. And this is on a machine with plenty of CPU and memory over an SSD. Unfortunately bdbje does not work clustered for me (ISPN-2968). So my point is that a local disk based, fast, reliable, transactional engine is highly needed. was: It would be very nice is we have SQLite support for infinispan. SQLite is a powerful database supporting terabyte sized databases in a file with competitive performance. I tried to use it as a JDBC store but the best driver I find in the internet ([xerial sqlite jdbc driver|https://bitbucket.org/xerial/sqlite-jdbc]) does not implement full jdbc specification and trying to use it results in exceptions. I think that perhaps using the [non-jdbc wrapper sqlite4java|http://code.google.com/p/sqlite4java/] may make sense for infinispan because: 1. it promises better performance 2. it allows using the sqlite library from OS (xerial driver uses a customized build of sqlite) FYI here is how I setup sqlite for infinispan (unsuccessfully): {code}jboss as cli commands: /subsystem=datasources/jdbc-driver=sqlite:add(driver-name="sqlite",driver-module-name="org.xerial",driver-class-name=org.sqlite.JDBC) data-source add --name=SQLiteDS --connection-url="jdbc:sqlite:${sqlite.database.string}" --jndi-name=java:jboss/datasources/SQLiteDS --driver-name="sqlite" /subsystem=datasources/data-source=SQLiteDS:enable {code} {code}JBoss AS module definition (modules/org/xerial/main/module.xml): <?xml version="1.0" encoding="UTF-8"?> <module xmlns="urn:jboss:module:1.0" name="org.xerial"> <resources> <resource-root path="sqlite-jdbc.jar" /> </resources> <dependencies> <module name="javax.api" /> <module name="javax.transaction.api"/> </dependencies> </module> {code} {code}cache store/loader configuration snippet: <stringKeyedJdbcStore xmlns="urn:infinispan:config:jdbc:5.2" fetchPersistentState="false" ignoreModifications="false" purgeOnStartup="false" key2StringMapper="com.jboss.datagrid.chunchun.util.TwoWayKey2StringChunchunMapper"> <dataSource jndiUrl="java:jboss/datasources/SQLiteDS" /> <stringKeyedTable dropOnExit="false" createOnStart="true" prefix="ispn"> <idColumn name="ID_COLUMN" type="VARCHAR(255)" /> <dataColumn name="DATA_COLUMN" type="BLOB" /> <timestampColumn name="TIMESTAMP_COLUMN" type="BIGINT" /> </stringKeyedTable> </stringKeyedJdbcStore> </loaders> {code} sql driver needs to be copied in the same directory as module.xml *UPDATE:* the exception is fixed with latest dev code of xerial jdbc driver, please look at comments to see remaining problems. The Exception I'm getting is:{code} 12:53:10,683 ERROR [org.infinispan.interceptors.InvocationContextInterceptor] (MSC service thread 1-3) ISPN000136: Execution error: org.infinispan.loaders.CacheLoaderException: Error while storing string key to database; key: 'user41', buffer size of value: 4918 bytes at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.storeLockSafe(JdbcStringBasedCacheStore.java:253) [infinispan-cachestore-jdbc-5.2.5.Final.jar:5.2.5.Final] ... Caused by: java.sql.SQLException: not implemented by SQLite JDBC driver at org.sqlite.Unused.unused(Unused.java:29) [sqlite-jdbc-3.7.2.jar:] at org.sqlite.Unused.setBinaryStream(Unused.java:60) [sqlite-jdbc-3.7.2.jar:] at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setBinaryStream(WrappedPreparedStatement.java:871) at org.infinispan.loaders.jdbc.stringbased.JdbcStringBasedCacheStore.storeLockSafe(JdbcStringBasedCacheStore.java:247) [infinispan-cachestore-jdbc-5.2.5.Final.jar:5.2.5.Final] ... 73 more{code} The driver [does not support|http://code.google.com/p/xerial/issues/detail?id=99] setBinaryStream(), only setBytes(). Not sure if there are any other methods required by infinispan but not implemented. As a simple comparison between JDBC and direct storage, I tried an app that caches 3000 records of around 5k and 60000 records of around 0.5k (total of less than 60MiB). Bdbje store operation completes in less than a minute. With a local mysql server it takes 10 minutes. And this is on a machine with plenty of CPU and memory over an SSD. Unfortunately bdbje does not work clustered for me (ISPN-2968). So my point is that a local disk based, fast, reliable, transactional engine is highly needed. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira