SQLite Forum

V3.36
Login

V3.36

(1) By anonymous on 2021-09-25 20:08:02 [link] [source]

Can I get a dictionary (name = database name, value = handle) for all opened databases?

(2) By Larry Brasfield (larrybr) on 2021-09-25 20:57:02 in reply to 1 updated by 2.1 [link] [source]

Such information is not kept by the SQLite library. It should be easy for an application to keep track of this. (I'm not sure what a "database name" or "handle" are in this context, but can answer nonetheless because the library does not rely on in-process data other than what is kept in per-connection structs or in structs held by them.)

(2.1) By Larry Brasfield (larrybr) on 2021-09-25 21:00:11 edited from 2.0 in reply to 1 [link] [source]

Such information is not kept by the SQLite library. It should be easy for an application to keep track of this. (I'm not sure what a "database name" or "handle" are in this context, but can answer nonetheless because the library does not rely on in-process data other than what is kept in per-connection structs or in structs held by them.)

Also: Please choose more descriptive thread titles going forward. Yours is ambiguous at best and misleading at worst.

(3) By Simon Slavin (slavin) on 2021-09-25 21:06:43 in reply to 1 [link] [source]

Your question doesn't quite correspond with how SQLite manages open databases. When you want SQLite to access a database you create a 'connection'. That database will be the 'main' database for that connection. If you want SQLite to access a second database you can either attach another database to the first connection, or open another connection. So …

One instance of SQLite can have many connections. Each connection has a main database and can have many attached databases.

Given the above, to discover all databases you currently have access to, for each SQLite connection, use

https://sqlite.org/pragma.html#pragma_database_list

PRAGMA database_list

It returns one line per database, second column is the connection's name for that database, third column is the name of the database file.

Your next natural question is how to find out what tables are in each of those databases, and you'll find it on the above page, under

PRAGMA schema.table_xinfo(table-name)

(I spend a few minutes trying to find out how to iterate through the connections for an instance of SQLite and failed. Contributions welcome.)

(4) By anonymous on 2021-09-25 21:37:06 in reply to 3 [link] [source]

In the CLI

,databases

or

PRAGMA database_list

tells me which databases are in scope in the current session (as Simon Slavin points out) ... but I see the alias as main and not the handle i.e. as returned by the out parameter of sqlite3_open(dbname, out handle).

Programmatically, I will need to use sqlite3_open--which creates handles and not aliases as the CLI--to open databases.

To paraphrase, my original question was simply to figure out whether I could get the list of databases I've opened together with their corresponding handles ... programmatically .

It looks like I have to manage this within my application. Correct?

(5) By Larry Brasfield (larrybr) on 2021-09-25 21:40:32 in reply to 3 [link] [source]

(I spend a few minutes trying to find out how to iterate through the connections for an instance of SQLite and failed. Contributions welcome.)

My only contribution is this advice: Be sure to limit that effort to just a few minutes.

The nature of SQLite library "instances" is one clue to when that search should terminate. The library code will normally be limited to one copy per process when statically linked (without special renaming to avoid link-time collisions.) The instantiation of data managed by the library is precisely the allocation and initialization of the opaque (to applications) struct whose typename is "sqlite3". There are no instances broader than that, except that one could contemplate all such instances in a process, on a computer, in some user's processes, in the world, across the universe, etc. Those broader sets are not tracked in any way or related to each other by the library.

Given that the OP's inquiry was about "all opened databases", the obvious answer is "No" but that's too easy to just say.

(6) By Larry Brasfield (larrybr) on 2021-09-25 21:57:05 in reply to 4 [source]

In the CLI ... tells me which databases are in scope in the current session (as Simon Slavin points out) ... but I see the alias as main and not the handle i.e. as returned by the out parameter of sqlite3_open(dbname, out handle).

What you (or the System.Data.SQLite .Net library, I suppose) are calling a "handle" is just that pointer to the (externally opaque) sqlite3 object. That .Net library has no way of knowing about any other "handle" it may have passed to its caller(s). The CLI (in recent versions) does track a small set of database connections (another term for references to those opaque objects) it has open, but does not traffic in raw pointer values at its external interface.

What you are calling an "alias" is the schema name, significant with just the connection which might have more than one database attached to the "main" one. This attachment is different from having a whole, separate connection.

To paraphrase, my original question was simply to figure out whether I could get the list of databases I've opened together with their corresponding handles ... programmatically

No.

It looks like I have to manage this within my application. Correct?

I think my answer to this was clear in post #2. It amounts to "yes" here.