Re: [sqlite] Pragma to flag unknown pragma?

[Roger Binns]

On 22/11/16 16:08, Scott Hess wrote:
> Something like "PRAGMA pedantic_pragma = on" would be much slicker.

SQLite lets you do "natrual" joins too.  I'd argue all this falls under
a lint mode that helps conscientious developers make sure everything is
working correctly under the hood.


https://www.sqlite.org/src/tktview/25e09aa2ab20d67a241b0164ac6818a27ea248d0

Sadly the SQLite developers rejected it.

Roger




signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pragma to flag unknown pragma?

[James K. Lowden]

On Tue, 22 Nov 2016 16:08:49 -0800
Scott Hess  wrote:

> One could perhaps fake pedantic pragma with SQLITE_FCNTL_PRAGMA in a
> custom VFS, but that seems a little excessive for something like this.
> Something like "PRAGMA pedantic_pragma = on" would be much slicker.

You might take some comfort in knowing that the C standard mandates the
same behavior for C compilers: unrecognized pragma statements are
ignored.  

> PRAGMA pedantic_pragma = on

has the  same chicken-and-egg problem.  If you mistakenly type 

PRAGMA pedantic_pragmas = on

you're back at square one.  

I think perhaps a better solution would be to add something to
sqlite3_config.  That at least has error semantics if incorrectly
used.  

--jkl

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

Re: [sqlite] Pragma to flag unknown pragma?

[Paul Sanderson]

Most pragmas allow you to issue them in a mode such as to query a current state.

So to use your example
pragma journal_mode = persist;

followed by
pragma journal_mode;

with a subsequent check to see that the returned value is set to what
you want it to be set to (actually the new value is returned when
journal_mode is set).

Likewise you can use pragma secure_delete to either set or query the
secure_delete mode




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 23 November 2016 at 16:36, Scott Hess  wrote:
> On Tue, Nov 22, 2016 at 10:50 PM, R Smith  wrote:
>> On 2016/11/23 2:08 AM, Scott Hess wrote:
>>> https://www.sqlite.org/pragma.html has:
>>> "No error messages are generated if an unknown pragma is issued.
>>> Unknown pragmas are simply ignored. This means if there is a typo in a
>>> pragma statement the library does not inform the user of the fact."
>>>
>>> I just lost some time due to this, even though I was fully aware of
>>> it.  My code wasn't working, so I instrumented to report errors, and
>>> gradually dug things deeper and deeper.  It wasn't until I was
>>> verifying statements line-by-line against sqlite3 in a terminal window
>>> that I saw that I was setting journal_mod rather than journal_mode!
>>>
>>> I realize that pragma don't have the compatibility guarantees that
>>> other syntax has.  But that means I actually _would_ want my code to
>>> start barfing if a PRAGMA stops being supported.  Say I'm issuing
>>> "PRAGMA secure_delete=ON" - presumably it's for a reason, and I'm
>>> going to want to revisit that reason if it's no longer supported.
>>
>> The problem is more the other way round - Backward compatibility is hard
>> when you introduce a new pragma, and a previous version of SQLite "barfs"
>> suddenly when it encounters that statement. Recent posts here re-emphasize
>> the frequency with which old versions are still used out there
>>
>> Silently ignoring an unrecognized pragma is a great way to be able to
>> introduce new functionality without worrying that the old will break.
>
> I may be misunderstanding where you're going with that, but my point
> was that there are some situations where I'm calling a PRAGMA because
> it's important to some implementation detail.  For instance, if your
> app's schema requires operational foreign keys, then simply ignoring
> "PRAGMA foreign_keys = ON" is _not_ kosher (I mean, yes, there's
> nothing SQLite can do to fix being old or compiled that way, but
> signalling "I can't do that, Dave" would be helpful).  How the PRAGMA
> I am _not_ calling are handled doesn't seem super relevant to that
> case, because I'm not calling them.
>
> Of course, my "PRAGMA pedantic_pragma = ON" call would itself succeed
> by failing on an older version of SQLite.  You have to bootstrap
> someplace, so you'd have to make the call to turn it on, then do
> another query to verify that it is on.  Which I guess is basically
> what your PRAGMA should already be doing, though there are subtleties
> (PRAGMA result patterns aren't consistent across different PRAGMA).
>
> -scott
> ___
> 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

Re: [sqlite] Pragma to flag unknown pragma?

[Scott Hess]

On Tue, Nov 22, 2016 at 10:50 PM, R Smith  wrote:
> On 2016/11/23 2:08 AM, Scott Hess wrote:
>> https://www.sqlite.org/pragma.html has:
>> "No error messages are generated if an unknown pragma is issued.
>> Unknown pragmas are simply ignored. This means if there is a typo in a
>> pragma statement the library does not inform the user of the fact."
>>
>> I just lost some time due to this, even though I was fully aware of
>> it.  My code wasn't working, so I instrumented to report errors, and
>> gradually dug things deeper and deeper.  It wasn't until I was
>> verifying statements line-by-line against sqlite3 in a terminal window
>> that I saw that I was setting journal_mod rather than journal_mode!
>>
>> I realize that pragma don't have the compatibility guarantees that
>> other syntax has.  But that means I actually _would_ want my code to
>> start barfing if a PRAGMA stops being supported.  Say I'm issuing
>> "PRAGMA secure_delete=ON" - presumably it's for a reason, and I'm
>> going to want to revisit that reason if it's no longer supported.
>
> The problem is more the other way round - Backward compatibility is hard
> when you introduce a new pragma, and a previous version of SQLite "barfs"
> suddenly when it encounters that statement. Recent posts here re-emphasize
> the frequency with which old versions are still used out there
>
> Silently ignoring an unrecognized pragma is a great way to be able to
> introduce new functionality without worrying that the old will break.

I may be misunderstanding where you're going with that, but my point
was that there are some situations where I'm calling a PRAGMA because
it's important to some implementation detail.  For instance, if your
app's schema requires operational foreign keys, then simply ignoring
"PRAGMA foreign_keys = ON" is _not_ kosher (I mean, yes, there's
nothing SQLite can do to fix being old or compiled that way, but
signalling "I can't do that, Dave" would be helpful).  How the PRAGMA
I am _not_ calling are handled doesn't seem super relevant to that
case, because I'm not calling them.

Of course, my "PRAGMA pedantic_pragma = ON" call would itself succeed
by failing on an older version of SQLite.  You have to bootstrap
someplace, so you'd have to make the call to turn it on, then do
another query to verify that it is on.  Which I guess is basically
what your PRAGMA should already be doing, though there are subtleties
(PRAGMA result patterns aren't consistent across different PRAGMA).

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

Re: [sqlite] Pragma to flag unknown pragma?

[R Smith]

On 2016/11/23 2:08 AM, Scott Hess wrote:

https://www.sqlite.org/pragma.html has:
"No error messages are generated if an unknown pragma is issued.
Unknown pragmas are simply ignored. This means if there is a typo in a
pragma statement the library does not inform the user of the fact."

I just lost some time due to this, even though I was fully aware of
it.  My code wasn't working, so I instrumented to report errors, and
gradually dug things deeper and deeper.  It wasn't until I was
verifying statements line-by-line against sqlite3 in a terminal window
that I saw that I was setting journal_mod rather than journal_mode!

I realize that pragma don't have the compatibility guarantees that
other syntax has.  But that means I actually _would_ want my code to
start barfing if a PRAGMA stops being supported.  Say I'm issuing
"PRAGMA secure_delete=ON" - presumably it's for a reason, and I'm
going to want to revisit that reason if it's no longer supported.


The problem is more the other way round - Backward compatibility is hard 
when you introduce a new pragma, and a previous version of SQLite 
"barfs" suddenly when it encounters that statement. Recent posts here 
re-emphasize the frequency with which old versions are still used out 
there


Silently ignoring an unrecognized pragma is a great way to be able to 
introduce new functionality without worrying that the old will break.


That said - we have been lobbying a long time for a "Strict-Mode" in 
SQLite (though that would probably hurt the "lite-ness" of it), but this 
is another example where such a mode would prove very useful.


Also - there are many tools that will syntax-check and/or use 
code-highlighting for your SQL for SQLite (SQLitespeed found here: 
www.sqlc.rifin.co.za or sqliteexpert.com being two of them and others 
regularly pop up here) which would high-light such problems. If you do 
not wish to check ALL your statements, at least try those that break on 
one of these systems before starting a big dig.


Good luck!
Ryan

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