deterministic=True fails on versions of SQLite prior to 3.8.3

[learning4life]

Hi, love your work.

I am unable to lookup indexes in a database using sqlite-utils:

sqlite-utils indexes city_spec.db --table

or

sqlite-utils indexes city_spec.db MyTable

Software
sqlite-utils, version 3.24
sqlite3 --version: 3.36.0

Output:

Traceback (most recent call last):
File "/opt/app-root/bin/sqlite-utils", line 8, in
sys.exit(cli())
File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 1128, in call
return self.main(*args, **kwargs)
File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 1053, in main
rv = self.invoke(ctx)
File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 1659, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 1395, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 754, in invoke
return __callback(*args, **kwargs)
File "/opt/app-root/lib64/python3.8/site-packages/click/decorators.py", line 26, in new_func
return f(get_current_context(), *args, **kwargs)
File "/opt/app-root/lib64/python3.8/site-packages/sqlite_utils/cli.py", line 2123, in indexes
ctx.invoke(
File "/opt/app-root/lib64/python3.8/site-packages/click/core.py", line 754, in invoke
return __callback(*args, **kwargs)
File "/opt/app-root/lib64/python3.8/site-packages/sqlite_utils/cli.py", line 1624, in query
db.register_fts4_bm25()
File "/opt/app-root/lib64/python3.8/site-packages/sqlite_utils/db.py", line 403, in register_fts4_bm25
self.register_function(rank_bm25, deterministic=True)
File "/opt/app-root/lib64/python3.8/site-packages/sqlite_utils/db.py", line 399, in register_function
register(fn)
File "/opt/app-root/lib64/python3.8/site-packages/sqlite_utils/db.py", line 392, in register
self.conn.create_function(name, arity, fn, **kwargs)
sqlite3.NotSupportedError: deterministic=True requires SQLite 3.8.3 or higher

[simonw]

I thought I'd made it so deterministic=True would be silently ignored in environments that don't support it, but clearly I missed a case here!

[simonw]

Here's the code in question:

sqlite-utils/sqlite_utils/db.py

Lines 384 to 394 in 521921b

	def register(fn):
	name = fn.__name__
	arity = len(inspect.signature(fn).parameters)
	if not replace and (name, arity) in self._registered_functions:
	return fn
	kwargs = {}
	if deterministic and sys.version_info >= (3, 8):
	kwargs["deterministic"] = True
	self.conn.create_function(name, arity, fn, **kwargs)
	self._registered_functions.add((name, arity))
	return fn

It's checking for Python 3.8, because that's the version of Python that added the deterministic=True option: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function

But from your error message it looks like it should be checking the SQLite version too.

[simonw]

Here's the SQLite changelog mentioning that it was added in 3.8.3: https://www.sqlite.org/changes.html#version_3_8_3

[simonw]

I need a db.sqlite_version property to implement this check.

[learning4life]

Thanks @simonw

I will test it after my vacation 👍

[learning4life]

@simonw

Now I get this:

(app-root) sqlite-utils indexes global.db --table
Error: near "(": syntax error
(app-root) sqlite-utils --version
sqlite-utils, version 3.25.1
(app-root) sqlite3 --version
3.36.0 2021-06-18 18:36:39
(app-root) python --version
Python 3.8.11

Dockerfile

FROM centos/python-38-centos7

USER root

RUN yum update -y
RUN yum upgrade -y


# epel
RUN yum -y install epel-release && yum clean all

# SQLite
RUN yum -y install zlib-devel geos geos-devel proj proj-devel freexl freexl-devel libxml2-devel 

WORKDIR /build/
COPY sqlite-autoconf-3360000.tar.gz ./
RUN tar -zxf sqlite-autoconf-3360000.tar.gz
WORKDIR /build/sqlite-autoconf-3360000
RUN ./configure
RUN make
RUN make install

# 
RUN /opt/app-root/bin/python3.8 -m pip install --upgrade pip
RUN pip install sqlite-utils