Identifying and Managing Attached Databases in SQLite

Enumerating Attached Databases Using PRAGMA and Introspection Queries

SQLite provides a powerful mechanism for working with multiple databases simultaneously through the use of attached databases. Attaching a database allows you to access its tables and schemas as if they were part of the main database, enabling complex queries and operations across multiple database files. However, managing these attached databases can sometimes be challenging, especially when you need to programmatically determine which databases are currently attached to a connection. This issue becomes particularly relevant when working with the SQLite C API, where direct access to the list of attached databases is not natively provided.

The primary method for identifying attached databases in SQLite involves using the PRAGMA database_list; command or its equivalent SQL query, SELECT * FROM pragma_database_list;. These commands return a table that includes details about each attached database, such as its sequence number, name, and file path. The sequence number (seq) indicates the order in which the databases were attached, while the name column provides the alias used to reference the database in queries. The file column shows the path to the database file, if applicable.

For example, consider the following output from the PRAGMA database_list; command:

In this example, the main database is the primary database, the temp database is used for temporary tables, and the _tz_ database is an attached database located at d:\source\sqlite\tz.db. This information is crucial for understanding the current state of the database connection and for performing operations that span multiple databases.

Unqualified Table Search Order and Database Attachment Sequence

One of the nuances of working with attached databases in SQLite is the concept of the unqualified table search order. When a table is referenced in a query without a database qualifier (e.g., SELECT * FROM my_table;), SQLite searches for the table in a specific order across the attached databases. This search order is not directly reflected in the output of PRAGMA database_list;, which lists databases in the order they were attached. Instead, the search order follows a specific rule: the temp database is always searched first, followed by the main database, and then any other attached databases in the order they were attached.

To illustrate this, consider the following example where multiple databases are attached:

ATTACH 'a.db' AS a;
ATTACH 'b.db' AS b;
DETACH a;
ATTACH 'c.db' AS c;

Executing the query SELECT row_number() OVER (ORDER BY name != 'temp', seq) AS search, * FROM pragma_database_list; yields the following result:

In this output, the search column reflects the unqualified table search order. The temp database is always searched first (search = 1), followed by the main database (search = 2), and then the remaining attached databases in the order they were attached (search = 3, 4, 5). This information is vital for understanding how SQLite resolves unqualified table references and for debugging queries that involve multiple databases.

Implementing Database Attachment Management in the C API

While SQLite provides robust support for attached databases through its SQL interface, managing these databases programmatically via the C API requires a more nuanced approach. The C API does not offer a direct function to enumerate attached databases, but you can achieve this by executing the PRAGMA database_list; command or the equivalent SQL query SELECT * FROM pragma_database_list; and processing the results.

To implement this in C, you would typically use the sqlite3_exec function to execute the query and a callback function to process each row of the result set. The callback function can extract the sequence number, database name, and file path from each row and store them in a data structure for further use. Here’s a simplified example of how this might be implemented:

#include <sqlite3.h>
#include <stdio.h>

static int callback(void *data, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int main(int argc, char **argv) {
    sqlite3 *db;
    char *err_msg = 0;

    int rc = sqlite3_open(":memory:", &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }

    rc = sqlite3_exec(db, "ATTACH 'a.db' AS a; ATTACH 'b.db' AS b;", 0, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
    }

    rc = sqlite3_exec(db, "SELECT * FROM pragma_database_list;", callback, 0, &err_msg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
    }

    sqlite3_close(db);
    return 0;
}

In this example, the callback function is used to print the details of each attached database. The sqlite3_exec function is used to execute the ATTACH commands and the SELECT * FROM pragma_database_list; query. The results are processed by the callback function, which prints the sequence number, database name, and file path for each attached database.

This approach allows you to programmatically determine which databases are attached to a connection and manage them accordingly. It is particularly useful in scenarios where you need to dynamically attach and detach databases based on runtime conditions or where you need to ensure that queries are executed against the correct database.

Conclusion

Managing attached databases in SQLite requires a deep understanding of both the SQL interface and the C API. By leveraging the PRAGMA database_list; command and the SELECT * FROM pragma_database_list; query, you can programmatically enumerate attached databases and understand their relationship to the main database. Additionally, understanding the unqualified table search order is crucial for writing correct and efficient queries that span multiple databases. Implementing these techniques in the C API allows you to build robust applications that can dynamically manage and interact with multiple SQLite databases.