Attaching a named in-memory database from C api

Question

Is it possible to attach a named SQLite3 :memory: database using the C API? There is of course sqlite3_open, but how do I set the name to be used in queries? Also, I could attach it by executing the ATTACH DATABASE statement, but then how do I get hold of its DB pointer?

Background:

I am using virtual tables to drive a SQLite interface to a set of external files. I am using SQLite mainly to get access to decent SQL querying and existing drivers for e.g. Python. I have defined a function open_myfiles which currently creates all the virtual tables based on the files in a given directory. The application just has to do SELECT open_myfiles('/path/to/tables') and the database is populated with the correct virtual tables.

However, I am attempting an improvement. Currently, the virtual tables are persisted to the current database and it is difficult to load multiple directories. I think that a better approach would be to attach each directory as a separate, named, :memory: database.

Raffi · Accepted Answer · 2020-12-23 18:55:30Z

1

It is possible by passing the correct connection string. The important read is this part of the SQLite documentation:

If you specify an URI in the form "file::memory:?cache=shared", then all memory DB will be shared
If you specify an URI in the form "file:memdb1?mode=memory&cache=shared" then the DB will be a named in memory DB

answered Dec 23, 2020 at 18:55

Raffi

3,50136 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krumelur Over a year ago

Actually, the first one does not work according to the docs (sqlite.org/sharedcache.html). The question is quite old so I am not sure shared cache existed back then.

Krumelur · Accepted Answer · 2012-07-18 12:32:39Z

0

After some research, I found out that there is no clear connection between a C api database ptr and an attached database. I had to modify my initial approach, and here is how I solved it in case anyone else stumbles upon this.

Instead of registering the virtual table module on extension load, I register a virtual table module for each invocation of the open_myfiles function. In addition to registering the module, this function also executes ATTACH DATABASE ':memory:' AS 'mymod' to put the virtual tables in their respective namespace (CREATE VIRTUAL TABLE works with qualified names as well, it turns out).

All in all, it works very well. I hope this helps someone else!

answered Jul 18, 2012 at 12:32

Krumelur

32.8k10 gold badges82 silver badges123 bronze badges

Collectives™ on Stack Overflow

Attaching a named in-memory database from C api

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related