sqlite3 double quote behavior

[Thomas Passin]

There is a Python adapter for SQLITE called "APSW".  It has a config() 
function.  I looked in the codebase and it defines the two configuration 
constants needed to turn off the double quote behavior (see 
https://sqlite.org/quirks.html).  These constants are 
SQLITE_DBCONFIG_DQS_DDL and SQLITE_DBCONFIG_DQS_DML.

This makes me think that the double-quote behavior can be turned off by 
Python code, though I haven't tried it.

 From the APSW docs (see 
https://rogerbinns.github.io/apsw/tips.html#about-python-apsw-and-sqlite-versions):

"APSW wraps the SQLite C API. That means when SQLite adds new constant 
or API, then so does APSW. You can think of APSW as the Python 
expression of SQLite’s C API. You can lookup SQLite APIs to find which 
APSW functions and attributes call them."

On 12/15/2022 2:18 PM, John K. Parejko wrote:
> 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
>