sqlite3 double quote behavior

[John K. Parejko]

Thanks for the discussion. I’m aware that SQLite has several different options for identifier quoting, but they’re not cross-compatible with other SQL, whereas double quotes are (modulo this strange SQLite behavior).

Is anyone here familiar with the python sqlite3 implementation? I wonder how hard it would be to raise up the `sqlite3_db_config` generically, or have a specific function to set just the DQS_DDL and DQS_DML settings? It looks like everything interesting is in `Modules/_sqlite/module.c`, but I’m not familiar with the cpython internals.

John

> On 13Dec 2022, at 13:58, Chris Angelico <rosuav at gmail.com> wrote:
> 
> On Wed, 14 Dec 2022 at 08:19, Roel Schroeven <roel at roelschroeven.net> wrote:
>> 
>> Chris Angelico schreef op 13/12/2022 om 20:01:
>>> On Wed, 14 Dec 2022 at 06:00, Roel Schroeven <roel at roelschroeven.net> wrote:
>>>> 
>>>> Stefan Ram schreef op 13/12/2022 om 8:42:
>>>>> "John K. Parejko" <parejkoj at gmail.com> writes:
>>>>>> I was just burned by this, where some tests I’d written
>>>>>> against an sqlite database did not fail in the way that they
>>>>>> “should” have, because of this double-quoted string issue.
>>>>> 
>>>>>   In standard SQL, double quotes denote identifiers that are
>>>>>   allowed to contain special characters.
>>>> Or that are equal SQL keywords, which can be a reason to double-quote
>>>> them. SQL engines sometimes add new keywords; explicitly marking string
>>>> literals as string literals prevents future conflicts and confusion.
>>>> 
>>>> Perhaps it's a better idea to use [identifier] or `identifier` instead
>>>> though (I just learned about those on
>>>> https://sqlite.org/lang_keywords.html). Both are not standard SQL ([] is
>>>> used in MS Access and SQL Server, `` is used in MySQL) but both work in
>>>> SQLite. That should prevent any ambiguity and confusion, if it doesn't
>>>> bother you too much that it's not standard SQL.
>>>> 
>>> 
>>> Why not just use "identifier" which is standard SQL?
>> 
>> If you accidentally type [identifire] or `identifire`, SQLite will
>> produce an unknown identifier error, alerting you immediately to your typo.
>> If you accidentally type "identifire", SQLite will silently treat it as
>> a string literal instead of an identifier, causing more difficult to
>> diagnose problems.
>> 
> 
> Okay, so..... exactly the same as if you use standard double quotes,
> but change the configuration option. So the options are: make
> everything worse for everyone by exacerbating the problem of
> non-standard identifier quoting, or get this API so SQLite can be
> configured, like the OP actually asked for.
> 
> Yeah. Let's not do the wrong thing.
> 
> ChrisA
> -- 
> https://mail.python.org/mailman/listinfo/python-list