[sqlwrapper] session-scoped objects are left out of get_objects

[tokoko]

Came across this issue when trying to list tables from mariadb with direct query vs adbc get_objects. mariadb (unlike mysql) includes temporary tables in information_schema.tables and I was able to list active temp tables with the query, but get_objects (which runs the same query under the hood) didn't list them. Turns out sqlwrapper prepares two separate objects Conn and Db for connections, one that's supposed to be used by queries and another that's supposed to be used by get_objects. The use of a separate connection for get_objects means that temporary tables that are created as part of the dedicated connection's session won't show up in the metadata.

mysql driver can of course use Conn instead of Db, but the driverbase also runs get objects queries concurrently so right now even that's impossible. Thought I would open a ticket here first to discuss. Is there a good reason why metadata queries can't go over the same Conn? I understand the performance arguments, but feels like a safer option to me.

Also, if performance is the main issue, couldn't we instead look into changing driverbase such that sql drivers would be able to issue a single query for all metadata rather than having to run separate queries per database?

[lidavidm]

CC @Mandukhai-Alimaa

I do agree driverbase is a bit weird about issuing concurrent queries, and that we should consider fast paths when we know we can get all the data in one query

[Mandukhai-Alimaa]

I agree we should support the single-query fast path. After exploring options, I think the cleanest approach is to have drivers implement GetObjects directly when they can retrieve all metadata in one query. There is already a fallback mechanism for that (https://github.com/adbc-drivers/driverbase-go/blob/main/driverbase/connection.go#L416). Im thinking something like:

1. Driver builds a single query that JOINs all necessary metadata tables
2. Executes it on the session connection (c.Conn instead of c.Db)
3. Parses results and groups into GetObjectsInfo structures
4. Calls existing https://github.com/adbc-drivers/driverbase-go/blob/main/driverbase/connection.go#L761

I considered adding a new GetObjectsFastPath interface in driverbase, but that adds complexity without real benefit. Should i implement this approach for the MySQL driver? Thoughts?

[lidavidm]

Just a direct override sounds good to me.

[tokoko]

sounds good to me as well

[Mandukhai-Alimaa]

closed by adbc-drivers/mysql#87