Test_sqlite3 failing with SQLite 3.49.0

[fazalmajid]

Bug report

Bug description:

test_sqlite3/test_dump.py relies on fts4 for test_dump_virtual_tables. However, SQLite 3.49.0 apparently no longer builds withENABLE_FTS4 by default, presumably because it was superseded by FTS5. This causes this unit test to fail.

CPython versions tested on:

3.13

Operating systems tested on:

Linux

Linked PRs

• gh-129870: Skip test_dump_virtual_tables if SQLite lacks FTS4 support #129913
• [3.13] gh-129870: Skip test_dump_virtual_tables if SQLite lacks FTS4 support (GH-129913) #129918

[fazalmajid]

I believe sqlite3 in Python used to be a separate project that was incorporated into the stdlib, but not sure if it is still maintained independently or now by the core Python team. I've only ever used fts5, and I have no idea why anyone would still want to use the older versions, but Chesterton's Fence applies, and we should not break things gratuitously.

[fazalmajid]

The people involved in #64662 would seem like the best ones to ask. I don't think sqlite3 has any specific fts support, it was just using fts4 as a way to create a virtual table to test dumping it. I am not sure what other extension is always included and can generate a virtual table for the test. FTS5 is not automatically built either.

[ghost]

Hi @fazalmajid,

Thanks for your insights! It makes sense to be cautious before switching to FTS5 if it’s not automatically built either. Given that FTS4 was used primarily for creating a virtual table in the test, do you think we should explore an alternative extension that is always included for generating the virtual table?

Also, I’ll check issue #64662 to see if we can get input from those involved there. If there are any other considerations before making changes, I’d love to understand them.

Looking forward to your thoughts!

Thanks again!

[picnixz]

However, SQLite 3.49.0 apparently no longer builds with ENABLE_FTS4 by default, presumably because it was superseded by FTS5

I can't find anything referrencing this in the sqlite3 changelog. Could it be a local issue? Note that FTS3/4 are still supported by sqlite3 so I don't think it's because it was superseded (they are still maintained and not indicated as superseded; note that FTS3 and 4 come by pair according to https://www.sqlite.org/fts3.html#compiling_and_enabling_fts3_and_fts4).

---

AFAIR, there are few built-in virtual tables enabled by default. JSON support is by default enabled as of SQLite 3.38 (https://www.sqlite.org/json1.html#compiling_in_json_support) but can be disabled. So we could use the json_each and json_tree vtables.

Now, since we don't ship our own copy of sqlite3, I think we only rely on a pre-compiled sqlite3 lib. So, we may not have that one either. An alternative is to first check which ones are available, and if none is, then we skip the test (because that means we can't use vtables as well!). Modules can be listed via PRAGMA module_list.

@erlend-aasland As the current sqlite3's maintainer, what can we do on our side now?

[erlend-aasland]

I believe sqlite3 in Python used to be a separate project that was incorporated into the stdlib, but not sure if it is still maintained independently or now by the core Python team.

sqlite3 was added from pysqlite¹ in 2006. pysqlite is no longer maintained. sqlite3 is, as all of the CPython code base, maintained by the CPython core team.

Footnotes

1. https://github.com/ghaering/pysqlite ↩

[fazalmajid]

I agree with the approach of testing for extension presence. As for the local build issue, I have SQLite built using:

(cd sqlite-autoconf-3490000; sed -e 's/awk -f/gawk -f/g' Makefile.in |grep -v SQLITE_OMIT_LOAD_EXTENSION > sop; mv sop Makefile.in; env CC="gcc -D_REENTRANT -I /usr/local/include -O2" ./configure --prefix=/usr/local --enable-threadsafe --enable-fts5)

Here is the diff between the output of pragma compile_options;:

--- 3.48
+++ 3.49
@@ -14,17 +14,8 @@
 DEFAULT_WAL_SYNCHRONOUS=2
 DEFAULT_WORKER_THREADS=0
 DIRECT_OVERFLOW_READ
-DQS=0
-ENABLE_DBPAGE_VTAB
-ENABLE_DBSTAT_VTAB
-ENABLE_EXPLAIN_COMMENTS
-ENABLE_FTS3
-ENABLE_FTS4
 ENABLE_FTS5
-ENABLE_GEOPOLY
 ENABLE_MATH_FUNCTIONS
-ENABLE_RTREE
-ENABLE_STMTVTAB
 MALLOC_SOFT_LIMIT=1024
 MAX_ATTACHED=10
 MAX_COLUMN=2000

[erlend-aasland]

Bénédikt:

Now, since we don't ship our own copy of sqlite3, I think we only rely on a pre-compiled sqlite3 lib.

The Windows and macOS installers ship with a copy of SQLite¹.

Footnotes

1. note the difference between SQLite (the underlying library) and sqlite3 (the stdlib module) ↩

[erlend-aasland]

Fazal:

I've only ever used fts5, and I have no idea why anyone would still want to use the older versions, but Chesterton's Fence applies, and we should not break things gratuitously.

FTS5 is a breaking change from FTS4; see the SQLite docs for more details.

[fazalmajid]

sqlite3 was added from pysqlite in 2006

Yep, I'm that old. In fact, I am an old enough Python user that I was one of the people who voted to establish the comp.lang.python Usenet newsgroup back in 1994

[picnixz]

The Windows and macOS installers ship with a copy of SQLite

Oh, yes, I'm sorry but I always forget about them :') Let me rephrase it as "Now, since on Linux, we don't ship the library but use the system-wide if any" [...]. I knew the difference but I thought that macOS had packages for that (though I totally forgot about Windows).

By the way, I said "sqlite3" because of the name of the library that is libsqlite3 (I wasn't actually talking about the sqlite3 module)

[erlend-aasland]

Fazal:

In fact, I am an old enough Python user that I was one of the people who voted to establish the comp.lang.python Usenet newsgroup back in 1994

Cool, and thanks for sharing :)

Let's solve this by skipping the test if FST4 is not available. Feel free to beat me with a PR to fix this.

[fazalmajid]

FTS5 is a breaking change from FTS4; see the SQLite docs for more details.

Yes, but Python or the sqlite3 module do not actually use either FTS4 or FTS5. The only place either are referenced is the Mac installer (which builds both) and test_dump.py, which only uses FTS4 to create a virtual table. Neither FTS4 nor FTS5 can be assumed to be present in the system-wide SQLite. It would be a bad idea to vendor SQLite in Python as the system may have additional extensions like encryption. Thus picnixz's approach of testing known virtual table extensions seems the soundest to me.

[erlend-aasland]

Hm, looking at our "build installer" scripts¹², I see that we're actually defining both the SQLITE_ENABLE_FTS4 and the SQLITE_ENABLE_FTS5 pre-processor "flags".

Footnotes

1. Mac/BuildScript/build-installer.py ↩

2. PCbuild/sqlite3.vcxproj ↩

[fazalmajid]

Yes, but as you pointed out, that won't work on Linux, or BSD, or Solaris, etc.

[picnixz]

I'm not on my dev session, so feel free to do it. Is there a simple way to detect if fts4 is available without using pragma? or is this the only way?

[fazalmajid]

We could just try each module in turn until one manages to create the virtual table. I tried naively doing a sed s/fts4/fts5/g but the test actually verifies the dump contents so that didn't work, we'd need to create a test vector for each of the possible extensions.

I'm currently travelling to India due to a family medical emergency, with abysmal connectivity, I'd be happy to submit a PR next week when I am back in London. Likely the first ones to notice will be Linux distros like Alpine, or people crazy enough to rebuild their entire stack from source like myself.

[erlend-aasland]

We could just try each module in turn until one manages to create the virtual table.

No, that would be too fragile. The dump can vary too much between the various virtual tables.

[erlend-aasland]

I'm currently travelling to India due to a family medical emergency, with abysmal connectivity, I'd be happy to submit a PR next week when I am back in London. Likely the first ones to notice will be Linux distros like Alpine, or people crazy enough to rebuild their entire stack from source like myself.

No worries, I made a quick PR that autodetects features and skips the test if needed. I may also update the PR to use FTS5 iso. FTS4.

[erlend-aasland]

Thanks for the report, @fazalmajid, and thanks for your input, @picnixz!