Re: [sqlite] Feature request, sqlite3_stmt_action
Working with RediSQL another use case comes to mind for some implementation of the interface we were discussing. How to detect SELECT statements that return empty. SQLite simply return SQLITE_DONE in all cases, and it makes impossible to know if it is an empty SELECT or something else. A possible solution that I tried was to check if the query executed was read_only, unfortunately also things like `CREATE TABLE IF NOT EXISTS foo(a,b);` are read_only if the table do exists. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
On 28 Mar 2019, at 10:25am, Dominique Devienne wrote: > Some info about the statement from EXPLAIN QUERY PLAN, that DRH would agree > to, and accept to "publicly document" and thus support would be nice, You want something like EXPLAIN EFFECTS OF and it should answer with zero or more lines. Each line contains a single string column. The strings are things like 'change data', 'change schema', 'change pragma', 'return nothing', 'return table', 'return one row'. The 'change' results do not mean that anything actually changes, they mean that the command is the kind of command intended to make that change. For example, an UPDATE command that changes no rows (or perhaps even refers to a table which doesn't exist) still returns 'changes data'. The 'return' results are similar. 'return table' means the command can return any number of rows, not how many rows it actually will return. If 'changes pragma' appears, then perhaps another line could say which one, like 'changes pragma journal_mode'. This would be useful for people writing a SQLite tool, or those with a setup which might involve an injection vulnerability. Whether it's actually worth building into SQLite I have no idea. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
On Thu, Mar 28, 2019 at 10:59 AM R Smith wrote: > 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. :) > What I ended-up doing is introspecting the VDBE program of the statement. It's not exactly easy, and can be brittle too, since the output is not "publicly documented" so subject to change w/o notice, but I consider this approach less brittle than parsing the SQL. > 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. Right. Some info about the statement from EXPLAIN QUERY PLAN, that DRH would agree to, and accept to "publicly document" and thus support would be nice, good idea. No an AST of course, but would go a long way already, for those of us that need/wish for that. I'll put it on my Xmas list :). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
On 2019/03/28 9:07 AM, Olivier Mascia wrote: Le 27 mars 2019 à 18:04, siscia 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
Re: [sqlite] Feature request, sqlite3_stmt_action
On Thu 28 Mar 2019 at 08:07, Olivier Mascia wrote: > > > Le 27 mars 2019 à 18:04, siscia 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? Because it’s never as simple as it looks... CTEs anyone ? It can be approximated sure. But will typically be brittle. I’ve long wished for an AST for SQLite statements but in fact the grammar actions directly build the internal data structures, it’s not two phase > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
> Le 27 mars 2019 à 18:04, siscia 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? -- Best regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia (from mobile device) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request, sqlite3_stmt_action
I'm not familiar with the C API, but the question I'll ask is this: How should this work with triggers? Running a statement as simple as "delete from foo;" could result in any number of different updates, deletes or inserts from any number of different tables, so how should that be reported? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of siscia Sent: Wednesday, March 27, 2019 1:05 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Feature request, sqlite3_stmt_action Hi List, I would like to propose a feature and I believe here is the best place. Please keep in mind that I am ready to work on a patch for this feature if needed, but I would like to discuss it here first. 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. Similar functionalities are provided by the authorizer which has a quite non-ergonomic interface relying on callbacks. Indeed the use of the authorizer for this is challenging, especially in a multi-threaded environments with several databases. The prototype that I am envisioning for the function would be something like: int sqlite3_stmt_action(sqlite3_stmt* stmt) where the function will return the action code (https://www.sqlite.org/c3ref/c_alter_table.html) of the statement passed as input. We could go even a little further and return something similar to the authorizer input itself: int sqlite3_stmt_action(sqlite_stmt* stmt, const char**, const char**, const char**, const char**) where the extra `const char**` will point to the NULL terminated string -- just like the authorizer -- that indicates tables name and index names where it makes sense. This new interface will make possible to even deprecate the authorizer itself, since it can be implemented on top of `sqlite3_stmt_action` while being more ergonomic especially in multi-threaded, multi-database environments. This same feature is already been required in the list itself: o) http://sqlite.1065341.n5.nabble.com/Determine-type-of-prepared-statement-via-C-Interface-td82075.html o) http://sqlite.1065341.n5.nabble.com/Determine-query-type-td83553.html#a83554 o) http://sqlite.1065341.n5.nabble.com/Distinguish-type-of-statements-td106281.html#a106282 (myself) even if most of those use cases have been solved using the stmt_readonly interface. Thanks for your attention! Cheers, Simone -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request, sqlite3_stmt_action
Hi List, I would like to propose a feature and I believe here is the best place. Please keep in mind that I am ready to work on a patch for this feature if needed, but I would like to discuss it here first. 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. Similar functionalities are provided by the authorizer which has a quite non-ergonomic interface relying on callbacks. Indeed the use of the authorizer for this is challenging, especially in a multi-threaded environments with several databases. The prototype that I am envisioning for the function would be something like: int sqlite3_stmt_action(sqlite3_stmt* stmt) where the function will return the action code (https://www.sqlite.org/c3ref/c_alter_table.html) of the statement passed as input. We could go even a little further and return something similar to the authorizer input itself: int sqlite3_stmt_action(sqlite_stmt* stmt, const char**, const char**, const char**, const char**) where the extra `const char**` will point to the NULL terminated string -- just like the authorizer -- that indicates tables name and index names where it makes sense. This new interface will make possible to even deprecate the authorizer itself, since it can be implemented on top of `sqlite3_stmt_action` while being more ergonomic especially in multi-threaded, multi-database environments. This same feature is already been required in the list itself: o) http://sqlite.1065341.n5.nabble.com/Determine-type-of-prepared-statement-via-C-Interface-td82075.html o) http://sqlite.1065341.n5.nabble.com/Determine-query-type-td83553.html#a83554 o) http://sqlite.1065341.n5.nabble.com/Distinguish-type-of-statements-td106281.html#a106282 (myself) even if most of those use cases have been solved using the stmt_readonly interface. Thanks for your attention! Cheers, Simone -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
