Actually now that I'm looking into this more deeply, turns out I'm already doing exactly that. At least on OS X. There I can't get that :memory: to work with ?cache=shared, so it always uses a file in the end. I get this with your latest code:
$ ls -l file*
ls: file*: No such file or directory
$ ./test.pl > /dev/null
$ ls -l file*
-rw-r--r-- 1 19456 Jul 24 22:46 file:memdb2?mode=memory&cache=shared
$ ./test3.pl > /dev/null
$ ls -l file*
-rw-r--r-- 1 36864 Jul 24 22:46 file:memdb2?mode=memory&cache=shared
When I use 'dbi:SQLite:dbname=:memory:' alone, it creates the database in-memory (no file), but I can't share it so that's no use.
I've also tried all of these different URIs, but no joy (below are the corresponding files that get created):
CONNECT=> 'dbi:SQLite:dbname=file:memory:?mode=memory&cache=shared'
19456 Jul 24 23:15 file:memory:?mode=memory&cache=shared
CONNECT=> 'dbi:SQLite:dbname=:memory:?cache=shared'
19456 Jul 24 23:18 :memory:?cache=shared
CONNECT=> 'dbi:SQLite:dbname=:memory:\?cache=shared'
19456 Jul 24 23:19 :memory:\?cache=shared
CONNECT=> 'dbi:SQLite:dbname=:memory:cache=shared'
19456 Jul 24 23:20 :memory:cache=shared
CONNECT=> 'dbi:SQLite:dbname=:memory?cache=shared:'
19456 Jul 24 23:21 :memory?cache=shared:
So far when I use the file it's fast enough for what I need. But just out of curiosity, any chance that ?cache=share can be set outside of the URI in some way?