Identifying attached database
(1) By oneeyeman on 2020-10-06 23:18:21 [link]
Hi, This question kind of relates to the one I just asked. Is there a way from the C API to say that there is an attached database? Thank you.
(2) By Keith Medcalf (kmedcalf) on 2020-10-07 06:16:53 in reply to 1
There is no C API to directly enumerate the list of "schema" names recognized by a connection. There is, however, a pragma (SQL Query) to retrieve this information. You may either execute **PRAGMA database_list;** or **SELECT * FROM pragma_database_list;** to retrieve information about the schema's available in a connection. (As long as the version of SQLite3 you are using supports this introspection pragma and it has been either enabled or not disabled as is appropriate for that version). ``` >sqlite -- Loading resources from C:\Users\KMedcalf/.sqliterc SQLite version 3.34.0 2020-10-05 19:05:20 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> pragma database_list; ┌─────┬──────┬────────────────────────┐ │ seq │ name │ file │ ├─────┼──────┼────────────────────────┤ │ 0 │ main │ │ │ 1 │ temp │ │ │ 2 │ _tz_ │ d:\source\sqlite\tz.db │ └─────┴──────┴────────────────────────┘ sqlite> select * from pragma_database_list; ┌─────┬──────┬────────────────────────┐ │ seq │ name │ file │ ├─────┼──────┼────────────────────────┤ │ 0 │ main │ │ │ 1 │ temp │ │ │ 2 │ _tz_ │ d:\source\sqlite\tz.db │ └─────┴──────┴────────────────────────┘ sqlite> ```
(3) By Keith Medcalf (kmedcalf) on 2020-10-07 06:34:34 in reply to 2 [link]
Note that the return order does not reflect the unqualified table search order. The query **select row_number() over (order by name != 'temp', seq) as search, * from pragma_database_list;** will return the results with a "search" column that reflects the unqualified table search order. ``` >sqlite -- Loading resources from C:\Users\KMedcalf/.sqliterc SQLite version 3.34.0 2020-10-05 19:05:20 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> attach 'a.db' as a; sqlite> attach 'b.db' as b; sqlite> detach a; sqlite> attach 'c.db' as c; sqlite> select row_number() over (order by name != 'temp', seq) as search, * from pragma_database_list; ┌────────┬─────┬──────┬────────────────────────┐ │ search │ seq │ name │ file │ ├────────┼─────┼──────┼────────────────────────┤ │ 1 │ 1 │ temp │ │ │ 2 │ 0 │ main │ │ │ 3 │ 2 │ _tz_ │ d:\source\sqlite\tz.db │ │ 4 │ 3 │ b │ D:\b.db │ │ 5 │ 4 │ c │ D:\c.db │ └────────┴─────┴──────┴────────────────────────┘ sqlite> ```