> Le 10 mai 2017 à 15:06, Dominique Devienne <[email protected]> a écrit :
>
> On Wed, May 10, 2017 at 1:35 PM, Gwendal Roué <[email protected]>
> wrote:
>
>>> Le 9 mai 2017 à 15:41, Gwendal Roué <[email protected]> a écrit :
>>>> How are you going to handle TRIGGERs ?
>>>
>>> That's a very good question.
>>
>> Very good news: foreign keys and triggers are 100% handled by authorizer
>> callbacks, for free :-D
>>
>
> Thanks for confirming. That's what I thought, from previous discussions on
> this list.
>
> We haven't heard from Richard, but I hope we will eventually.
So do I.
Since my real topic is answering the question "were the results of statement S
modified by transaction T?", I can imagine how this SQLITE_READ_TABLE proposal
looks indirect, and potentially misled.
I tried to explain before that this question requires a lot of careful use of
commit/rollback/update hooks on top of authorizer callbacks in order to provide
as few false positives as possible.
A better written feature request could remain strictly at the statement level.
Here is a documentation for a potential sqlite3_stmt_independent() function,
inspired from the documentation of sqlite3_stmt_readonly:
# Statement Independence Test
int sqlite3_stmt_independent(sqlite3_stmt*, sqlite3_stmt*);
The sqlite3_stmt_independent(S1, S2) returns true (non-zero) if and
only if the prepared statement S1 makes no changes to the database table and
columns used by the prepared statement S2. Changes that are taken in account
are direct changes to the table and columns used by S2, but also indirect
changes through foreign keys cascades, and indirect changes through triggers.
For example, given the following SQL statement:
SELECT a, b FROM t1;
The following statements are considered independent:
SELECT * FROM t1;
INSERT INTO t2 (...);
UPDATE t1 SET c = c + 1;
The following statements are considered dependent:
DELETE FROM t1;
UPDATE t1 SET a = 1;
INSERT INTO t1 (a, b) VALUES (1, 2);
Note that application-defined SQL functions or virtual tables might
change the database indirectly as a side effect. For example, if an application
defines a function "eval()" that calls sqlite3_exec(), then the following SQL
statement would change the database through side-effects:
SELECT eval('DELETE FROM t1') FROM t2;
But because the SELECT statement does not change the database directly,
sqlite3_stmt_independent would still return true, regardless of the second
parameter.
> I very much agree with you the authorizer API should be "complete" and
> "exhaustive" in what is truly accessed.
That's my current strategy :-)
The core team may prefer the sqlite3_stmt_independent(S1, S2) function above.
But I'm not sure: currently authorizer callbacks are called during the parsing
phase: SQLite doesn't internally keep any information about the impact of a
statement besides its execution plan. An eventual sqlite3_stmt_independent
function may be difficult to implement, depending on how obsfuscated is the
information it needs after the statement has been compiled. I don't know the
inner SQLite guts enough.
With SQLITE_READ_TABLE, we have a way to let the library user gather the needed
information, with a simple implementation (the only one I could provide with my
current knowledge of the library).
> A flag could be set to not issue the new calls you propose, for backward
> compatibility purposes (and also perhaps because both FKs and TRIGGERs are an
> implementation "detail" of the schema, that not all clients should be aware
> of, unless explicitly requested by the client, perhaps even checked by the
> authorizer itself, to complete the circle :)). --DD
I don't know if backward compatibility is an issue here: code that did not
check for SQLITE_READ_TABLE has no reason to break after SQLITE_READ_TABLE has
been introduced.
Gwendal Roué
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
