Given any SQL query - such as:
select a.*, b.* from a join b on a.b_id = b.id
I would like to know the columns that would be returned by the query. Not just their names, but also which table those columns live in.
For the above example this might be:
a.id
a.name
a.b_id
b.id
b.name
But you could imagine this getting significantly more complex for joins across many different tables.
I want this so my software, Datasette, can display columns from specific tables in different ways, even when those columns are returned as part of an arbitrary query.
My biggest constraint is that I am using the sqlite3 module from the Python standard library. It looks like this family of C functions: <https://www.sqlite.org/c3ref/column_database_name.html> can solve my problem, but those are not exposed in the Python library I am using.
I wrote a bit more about my research into this here: <https://github.com/simonw/datasette/issues/1293>
So three questions:
1. Is there a mechanism for this that I might be missing?
2. Would it be feasible to achieve this using a custom SQLite extension? One that exposes the column_database_name() C API functions in a way that I can use them to answer my question? I was thinking something like this:
select database, table, column from analyze_query(:sql_query)
3. Feature request: could SQLite itself add some kind of SQL-level mechanism for accessing this information, such that even if the Python sqlite3 module doesn't add support this information would become available via a PRAGMA or similar?