Support for Loading and Querying Multiple Instances of the Same Extension Type

[toliver38]

Description:

Currently, Steampipe-SQLite does not support the ability to load and query multiple instances of the same extension type either within the same database or across multiple databases using the ATTACH DATABASE method. This limitation hinders the ability to apply separate configurations for each instance and to differentiate between tables from different connections.

Ideally I'd like to be able do this all in memory rather than persisting the dbs and/or keeping the creds in a .sql file to be loaded at query time.

Steps to Reproduce:

1. Attempt to set up two AWS connections, each with its own configuration.
2. Load the first connection into the primary database.
3. Attach a second database and load the second connection into this attached database.
4. Install the extension for both databases.
5. Apply configurations to each connection.

Expected Behavior:

• Each connection should be able to maintain its own configuration. I understand this is not how sqlite works at this stage.
• Tables from each connection should be uniquely identifiable, possibly through the use of table prefixes which would be equivalent to the database name listed in .DATABASES

Actual Behavior:

• The configuration applied to one connection gets applied to all connections.
• Queries like SELECT * FROM aws_account UNION ALL SELECT * FROM db2.aws_account return the same results twice, indicating that the configurations are not being applied separately.

Proposed Solution:

• Modify the table, module, and register code to allow application of configurations to attached databases.
• Implement a mechanism to add table prefixes during virtual table creation to differentiate between tables from different connections this should be based on the database name maybe identified in the config.

References:

• Discussion in the Steampipe community

Additional Context:
SQLite doesn't have a concept of schemas, and thus requires tables to be uniquely named. Allowing the application of different configurations to attached databases and using table prefixes would address the current limitations and provide more flexibility in handling multiple connections of the same type.