Re: [sqlite] How can I know which table is operated by one SQL?

2010-01-12 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

zhangzhenggui wrote:
> When I execute a SQL with C/C++ API, I want to know which table is 
> operated by this SQL. Is there any way to do this except parse the SQL?

Using "explain" in front of your query will give the byte code and be most
complete.  If you use "explain query plan" then you'll get a quick listing
of tables involved and as a bonus which indices are used to access the table.

As an alternate approach you can make an authorizer which you have present
while running sqlite3_prepare.  That way you'll get details too.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAktMtdwACgkQmOOfHg372QQauACg1ws9zX9mkjNQe2bLgHZ9AU9S
XpIAoL9JXK5xIsLfHS+VJ1K8+ZCAlSJh
=fz3O
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I know which table is operated by one SQL?

2010-01-12 Thread Max Vlasov 
On Tue, Jan 12, 2010 at 10:22 AM, zhangzhenggui wrote:

> Dear friend,
>When I execute a SQL with C/C++ API, I want to know which table is
> operated by this SQL. Is there any way to do this except parse the SQL?
>
>
Every approach is not perfect, here is another one.

add EXPLAIN to the query you examine, iterate through the results while
collecting p2 field where opcode field is "OpenRead" or "OpenWrite" (you
have to iterate since you can not use Explain prefixed query as a subquery).
Concatenate the values as a comma-separated string like "3, 4, 7". These are
the root pages of the tables used.

After that use query

SELECT tbl_name FROM sqlite_master WHERE type="table" and rootpage in (%s)

(replace %s with your comma-seprated list). Now you have the list of tables
used as a query result.
I have not high level of certainty this will always work as expected, but
compare this with other "undocumented", "subject to change" variants and
choose the best )

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I know which table is operated by one SQL?

2010-01-12 Thread Ilya Pokolev 
No, in general. Only parsing.
You can use sqlite3_column_table_name API call in case you have SELECT
statement. But it returns the result of parsing SQL query internally when
prepare.

--
Best regards,
Ilya N. Pokolevmailto:i.poko...@compassplus.ru


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of zhangzhenggui
Sent: Tuesday, January 12, 2010 12:23 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How can I know which table is operated by one SQL?

Dear friend,
When I execute a SQL with C/C++ API, I want to know which table is 
operated by this SQL. Is there any way to do this except parse the SQL?

Thanks,
zhangzhenggui

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can I know which table is operated by one SQL?

2010-01-12 Thread Igor Tandetnik 
zhangzhenggui wrote:
>When I execute a SQL with C/C++ API, I want to know which table is
> operated by this SQL. Is there any way to do this except parse the SQL?

Note that, in the presence of triggers and/or foreign keys, more than one table 
may be modified when running a single statement.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users