Datasette is not compatible with SQLite's strict quoting compilation option

I have linked Python3.11 on macOS against recent SQLite that was compiled using -DSQLITE_DQS=0. This option disables interpretation of double-quoted identifiers as string literals, described in the SQLite docs as a "MySQL 3.x misfeature". See https://www.sqlite.org/quirks.html#dblquote for background.

Datasette uses the double-quote syntax in a number of key places, and is thus completely broken in this environment.

My experience was to pip install datasette, then run datasette serve -I my-data.db. When I visit http://127.0.0.1:8001 I get a 500 response.

The error: sqlite3.OperationalError: no such column: geometry_columns

The responsible SQL: 'select 1 from sqlite_master where tbl_name = "geometry_columns"'

I then installed datasette from GitHub master in development mode and changed the offending SQL to use correct quotes: "select 1 from sqlite_master where tbl_name = 'geometry_columns'".

With this change, I get a little further, but have the same problem with the first table name in my database (in my case, "Meta"):

OperationalError: no such column: Meta
Traceback (most recent call last):
  File "/Users/gwk/external/datasette/datasette/app.py", line 1522, in route_path
    response = await view(request, send)
               ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/gwk/external/datasette/datasette/views/base.py", line 151, in view
    return await self.dispatch_request(request)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/gwk/external/datasette/datasette/views/base.py", line 105, in dispatch_request
    response = await handler(request)
               ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/gwk/external/datasette/datasette/views/index.py", line 70, in get
    "fts_table": await db.fts_table(table),
                 ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/gwk/external/datasette/datasette/database.py", line 363, in fts_table
    return await self.execute_fn(lambda conn: detect_fts(conn, table))
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/gwk/external/datasette/datasette/database.py", line 213, in execute_fn
    return await asyncio.get_event_loop().run_in_executor(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/py/Python.framework/Versions/3.11/lib/python3.11/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/gwk/external/datasette/datasette/database.py", line 211, in in_thread
    return fn(conn)
           ^^^^^^^^
  File "/Users/gwk/external/datasette/datasette/database.py", line 363, in <lambda>
    return await self.execute_fn(lambda conn: detect_fts(conn, table))
                                              ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/gwk/external/datasette/datasette/utils/__init__.py", line 588, in detect_fts
    rows = conn.execute(detect_fts_sql(table)).fetchall()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: no such column: Meta
INFO:     127.0.0.1:50258 - "GET / HTTP/1.1" 500 Internal Server Error

I will try to continue playing with this, but I also hope that the datasette developers will enable this mode in a test environment as I am unlikely to be able to exercise all of the SQL in the codebase, or make a pull request very soon.

Note that the DQS setting compile-time option can be overridden at runtime with calls to the C API:

sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DDL, 0, (void*)0);
sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DML, 0, (void*)0);

As far as I can tell, sqlite3_db_config is not exposed in Python, but perhaps we could figure out how to invoke it using ctypes.

Start by reproducing datasette serve -I my-data.db and the 500 response, then inspect the SQLite queries in datasette/utils/__init__.py, including detect_fts_sql(), and the fts_table() path in datasette/database.py. Done means startup, table detection, and FTS detection work when SQLite double-quoted string interpretation is disabled, including the reported geometry_columns and table-name failures.