Problem with memory SQLite and multiple connections

[element-of-surprise]

Wanted to confirm a problem and then I can create a PR with a fix if your interested in taking changes for bugs.

Was having a problem where if I created a pool with more than one connection on a memory SQLite I couldn't see changes in any other connection, including table creation.

I assumed I was doing something terribly wrong. But apparently there is this error:

https://github.com/zombiezen/go-sqlite/blob/v1.4.2/sqlitex/pool.go#L72C1-L74C3

Unfortunately I'm not tripping that error when I do:

pool, err := sqlitex.NewPool(path, sqlitex.PoolOptions{Flags: flags, PoolSize: 1})

My path is not ":memory:" and there doesn't seem to be a requirement that if the flag sqlite.OpenMemory is used that the uri must be ":memory:", so you can bypass that warning.

There seems two ways to fix this:

• Enforce uri == ":memory:" if flag sqlite.OpenMemory is set, then do the check listed above
• Simply do the same check but have a uri == memory || sqlite.OpenMemory is set.

I'm happy to generate a PR that does whichever one your prefer, if you are so inclined.

And thank you for such a great set of packages. Really are just great.

[zombiezen]

This sort of thing has come up before, see #94. I'm generally aligned on making this less of a footgun, but it's a little subtle. I think you're on the right track, though.

uri == ":memory:" || opts&sqlite.OpenMemory != 0 mostly sounds reasonable to me, but in the case where the options also contain sqlite.OpenSharedCache or sqlite.OpenURI with a ?cache=shared parameter, it would prevent correct usages. I'm a bit hesitant to have this library start parsing SQLite URIs (especially on every connection open), but maybe that's what we need to do.

Here's what I think the logic should be (broadly speaking). If the arguments to Open specify all of:

• An in-memory connection (regardless of by option or filename)
• Without shared cache
• A pool size that is not exactly 1

Then we should present the error. WDYT? (I would accept a PR for this.)

[johnsiilver]

Sounds like a plan. I'll generate a PR and push it your way. Thanks for taking the time to have a look!

[element-of-surprise]

#125