The readOnly queries don't work with Snowflake/SQLite JDBC drivers

[seratch]

The same problem appears in SQLite (xerial/sqlite-jdbc#247), where setReadOnly also triggers an exception. This actually seems like correct behaviour on their part, since transparently ignoring concurrency directives seems like a recipe for trouble. Given how extensively readOnly appears throughout the scalikejdbc internals, would it be possible to add a flag on the ConnectionPool (or similar) that allows users to manually request that readOnly be transparently replaced with a read/write transaction mode?

Originally posted by @okennedy in #1071 (comment)

[seratch]

I am not planning to take action for this in the short term but if someone has thoughts or inputs on this issue, please share the knowledge with the community!

[okennedy]

Thanks for having a look @seratch I'm not sure this will help, but in case anyone comes across this issue in the future, here's a workaround:

• https://github.com/VizierDB/vizier-scala/blob/master/src/main/scala/info/vizierdb/catalog/workarounds/SQLiteNoReadOnlyDriver.scala

The main gimmick is here where it silently drops calls to SQLiteConnection.setReadOnly

The driver needs to be registered with JDBC, and then you set up Scalikejdbc by just prefixing the normal SQLite URL with no-read-only::

DriverManager.registerDriver(SQLiteNoReadOnlyDriver)
ConnectionPool.singleton(
  url = "no-read-only:jdbc:sqlite:" + new File(config.basePath(), db).toString,
  ...
)

See here for an example.

[seratch]

If having a new option like GlobalSettings.setReadOnlyDisabled, which is similar to jtaDataSourceCompatible but it does not do anything for autoCommit, may be helpful for the following JDBC driver users:

• Snowflake
• SQLite3

Anything else?