Datasette is not compatible with SQLite's strict quoting compilation option #2001
Description
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.