Make ConnectionConfig#autoCommit configurable via fake pragma

[exoego]

Problem

I am using Kotlin's SQL Framework https://github.com/JetBrains/Exposed with sqlite-jdbc.

import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.transactions.transaction
(snip)

val sqLiteConfig = SQLiteConfig()
val ds = SQLiteDataSource(sqLiteConfig)
val database = Database.connect(ds)

import org.jetbrains.exposed.sql.transactions.transaction
import org.sqlite.SQLiteErrorCode
import org.sqlite.SQLiteException
import java.time.temporal.Temporal

transaction(db) {
  connection.autoCommit = false
  // do something with DB
}

I want to disable auto-commit for better performance, but it appears that it is not configurable via SQLiteConfig nor SQLiteDataSource. SQLiteConnectionConfig offers setAutoCommit, but SQLiteConfig#defaultConnectionConfig always use autoCommit = true. https://github.com/xerial/sqlite-jdbc/blob/263c296f0057b1de9d758fbdef5ccfd2762add26/src/main/java/org/sqlite/SQLiteConnectionConfig.java#L21

One can specify connection.autoCommit = false in transaction { ... } DSL, but it is error-prone because one might forget.

Proposal

This PR adds "fake" pragma jdbc.auto_commit, so auto-commit can be configurable via SQLiteConfig.

val prop = new Properties()
prop.setProperty(SQLiteConfig.Pragma.JDBC_AUTO_COMMIT.pragmaName, "true")
val sqLiteConfig = SQLiteConfig(prop)
val ds = SQLiteDataSource(sqLiteConfig)
val database = Database.connect(ds)

[gotson]

It seems JDBC expects the default to always be true, according to https://stackoverflow.com/a/11022406

It would probably make sense for the exposed framework to allow for such option so that new connections are always set to false.

[exoego]

I agree autoCommit = true should be the default. This PR does not change that. It just allows users to configure autoCommit so they can use this driver for any framework in autoCommit = false mode.

Several JDBC libs offer a configuration point of autoCommit:

• HikariCP, a JDBC connection pool, supports https://github.com/brettwooldridge/HikariCP/blob/b40e6842080d2e7e5f8d56350aec20c49df0c9dd/src/main/java/com/zaxxer/hikari/HikariConfig.java#L532-L536 in Config for DataSource
• PostgreSQL JDBC https://github.com/pgjdbc/pgjdbc/blob/30d4035c814e625932199568b1014e2010e21e27/pgjdbc/src/main/java/org/postgresql/ds/PGConnectionPoolDataSource.java#L88-L90 in DataSource

[exoego]

Please take a look.