On 2019/03/28 9:07 AM, Olivier Mascia wrote:
Le 27 mars 2019 à 18:04, siscia <sisciamir...@yahoo.com> a écrit :
I would like to propose a function (named `sqlite3_stmt_action` for the sake
of discussion) that allow to understand if a specific statement is either a
SELECT, UPDATE, DELETE or INSERT.
There is probably a much more complex need that I did not grasp reading this
request. What stops you from parsing the beginning of the statement text to
decide if it is a select, update, delete or insert?
Having done this already, allow me to offer some recounting of the
difficulties:
First there are typically two things a programmer is interested in
(well, if you maintain an SQLite management utility or the like):
- First: Will the Query produce data output back that I need to show to
the user?, or will it silently execute?
- If it does produce output, is this confirming the state (such as when
calling a pragma command), or is this output that I need to show the
user, or perhaps log?
- Then: Will the query change the database?
- Put another way, will it work on a read-only file?
- or, will it alter the table content that is currently displayed? Do I
need to re-run the display query?
- or will it change the schema?
- Do I need to re-parse the schema to show the user the DB layout after
executing?
Some of these SQLite does cater for, but many not, and there are some
work-aroundy ways of accomplishing it.
For instance, you might reparse the schema after ANY non-select query.
But then - how do I know if it is anything other than a SELECT query?
The obvious answer is not to see if it's any of INSERT, UPDATE, CREATE,
etc... but to simply see if it is indeed a SELECT query. Right?
But then, what about CREATE TABLE t AS SELECT a,b,c, FROM.....
Or if it is a CTE, consider these two:
WITH X(z) AS (SELECT 1) SELECT z FROM X;
vs.
WITH X(z) AS (SELECT 1) INSERT INTO t(z) SELECT z FROM X;
These are already difficult to self-parse, and they are extremely simple
examples.
I would even be happy to have something like extending the EXPLAIN QUERY
PLAN sql interface to include something like:
EXPLAIN QUERY RESULT .... ;
which outputs a simple row of values that can tell me:
- This query produces results - YES/NO (even if those results may be
empty, is it the /intent/ of the query to produce results?),
- It updates the data - YES/NO,
- It updates the schema - YES/NO
- It is a pragma or setting adjustment - YES/NO
Maybe even, if possible, This query updates these tables: x1, x2, x3...
etc. (some of which might hide behind an FK relation or Trigger) but I
know this is pushing my luck. :)
Plus, I really do not mind if this explain takes some time, it will be
faster and more future-proof than any self-parsing one can do.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
