rw conns

[ruslandoga]

This PR is similar to #255 but instead of making writes exclusive it opens two databases, one for writing and one for reading. This allows for reads to continue being served while the write is happening thanks to WAL:

2. WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

[warmwaffles]

Some issues that come to mind that will need to be explored.

1. Native extension support. Can it be done with the two database approach?
2. Will this work with sqlcipher compiled with the library? We'll need to be able to pass configurations for it.
3. Will we be able to support all of the original connection options prior to this?

I wish we had a better benchmarking harness to compare using two database handles opened vs just one.

[ruslandoga]

@warmwaffles

1. Yeah... select load_extension(..., ...) or any runtime configuration (via pragmas) would need to be called on both dbs, not great.

def load_extension(conn, extension) dp
  with {:ok, _} <- query(conn, "select load_extension(#{extension})"),
       {:ok, _} <- read_query(conn, "select load_extension(#{extension})"), do: :ok
end

2. If this happens during database opening, then it's fine same as 3. In these cases we can rather easily duplicate all configuration over two databases and just fail the init if something doesn't work.

I wish we had a better benchmarking harness to compare using two database handles opened vs just one.

I'll prepare some benchmarks for the three approaches:

• current master with variable pool_size (I'm thinking 1, 2, 4, 8, 16) https://github.com/ruslandoga/exqlite/pull/2
• https://github.com/elixir-sqlite/exqlite/pull/255 where a single process owing single db is used
• this one: https://github.com/ruslandoga/exqlite/pull/1

[ruslandoga]

Closing this PR as it's too complicated. But I'm still trying to find a good replacement for the current db_connection approach :)

[warmwaffles]

Hah, it's not a problem. I'll find some time to really dive into this more.