That could be a useful approach, and automatable. It might get complicated in the presence of table aliases, like
sqlite> select a.test from b as a; Error: no such table: b sqlite> create table b(hello); sqlite> select a.test from b as a; Error: no such column: a.test In the end, the query engine needs to resolve to 'real' tables? -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Raymond Sent: Tuesday, February 12, 2019 3:01 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Retrieve tables accessed by query If you want the bare minimum of what will satisfy a statement I suppose there's always the iterative approach of: Start with empty database try your statement -> fix the error message -> repeat until it doesn't give an error SQLite version 3.27.1 2019-02-08 13:17:39 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on tbl1.field4 = tbl2.field2; Error: no such table: tbl1 sqlite> create table tbl1 (rowid integer primary key); sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on tbl1.field4 = tbl2.field2; Error: no such table: tbl2 sqlite> create table tbl2 (rowid integer primary key); sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on tbl1.field4 = tbl2.field2; Error: no such column: tbl1.field1 sqlite> alter table tbl1 add column field1; sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on tbl1.field4 = tbl2.field2; Error: no such column: tbl2.field3 sqlite> alter table tbl2 add column field3; sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on tbl1.field4 = tbl2.field2; Error: no such column: tbl1.field4 sqlite> alter table tbl1 add column field4; sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on tbl1.field4 = tbl2.field2; Error: no such column: tbl2.field2 sqlite> alter table tbl2 add column field2; sqlite> select tbl1.field1, tbl2.field3 from tbl1 inner join tbl2 on tbl1.field4 = tbl2.field2; QUERY PLAN |--SCAN TABLE tbl1 `--SEARCH TABLE tbl2 USING AUTOMATIC COVERING INDEX (field2=?) sqlite> .schema CREATE TABLE tbl1 (rowid integer primary key, field1, field4); CREATE TABLE tbl2 (rowid integer primary key, field3, field2); sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Nelson, Erik - 2 Sent: Tuesday, February 12, 2019 2:29 PM To: SQLite mailing list Subject: Re: [sqlite] Retrieve tables accessed by query For >Could you expound on that a bit more? If the tables don't exist then it >becomes a moot point. The question I'm hoping to answer is "what tables do I need to make in order to fulfil this query?". For >select * from foo; I was wondering if I could get a call for 'foo' on the authorizer API, even though this query may not be able to be compiled to completion. Regarding the other complexities you mentioned, point taken. At the end of the day, though, the query results be based on zero or more actual tables- I was hoping there might be some robust way to detect what those are. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Raymond Sent: Tuesday, February 12, 2019 2:12 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Retrieve tables accessed by query Could you expound on that a bit more? If the tables don't exist then it becomes a moot point. If you have the extremely simple select * from foo; Then with no context "foo" is what's accessed. But inside an actual database, "foo" could be a view involving other tables or views. And the query planner might use covering indexes that don't touch base tables at all, etc etc. And if it's a modifying statement there might be triggers that access their own things, etc. So are you looking for "parse this SQL", or "what specific bits of this specific database would get accessed were I to run this SQL"? As a side point, if I recall, "the parsed AST" is something you can't get, specifically so that they're free to completely change it in any way they want at any time without the issue of "oops, this might break someone else's code." (Or I may be thinking of something else here) -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Nelson, Erik - 2 Sent: Tuesday, February 12, 2019 1:00 PM To: SQLite mailing list Subject: Re: [sqlite] Retrieve tables accessed by query Clemens Ladisch Sent: Tuesday, February 12, 2019 12:38 PM >Zach Wasserman wrote: >> Is anyone aware of an API I can use to determine which tables are accessed >> by a given query? Both Clemens and Warren had good comments. I'm curious if there's a way to do it in a database where the tables *don't exist*. For example, I have a query and I want to find out which tables the query makes so I can create only those. This query is obviously not valid in the current database... will it still (for example) call the authorizer interface? Thanks Erik ---------------------------------------------------------------------- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=z_60ozV957S1aN7o8jya-G09AGlze-BReDPKaQJHJqM&s=Laupns_-TUEj7GOdta_BY0AA-BpZ-yEuUfwRLHz-Z_I&e= _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=z_60ozV957S1aN7o8jya-G09AGlze-BReDPKaQJHJqM&s=Laupns_-TUEj7GOdta_BY0AA-BpZ-yEuUfwRLHz-Z_I&e= ---------------------------------------------------------------------- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=Nsmr7qjLK0TwkhsnHeLa3dTCSpogAPcobXtHIKrqki8&s=zmuZCCZp_jEXSVrCtPBIsP9tuukuE6-iVWugUkt-PQk&e= _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=Nsmr7qjLK0TwkhsnHeLa3dTCSpogAPcobXtHIKrqki8&s=zmuZCCZp_jEXSVrCtPBIsP9tuukuE6-iVWugUkt-PQk&e= ---------------------------------------------------------------------- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
