Test against multiple SQLite versions

[simonw]

Borrowed from simonw/datasette@8f86d2af6

Refs:

• Use UPSERT (aka INSERT ... ON CONFLICT SET) in .upsert() and .upsert_all() when available #652

cherry-picked from:

• Use ON CONFLICT for upsert #653

---

📚 Documentation preview 📚: https://sqlite-utils--654.org.readthedocs.build/en/654/

[simonw]

Ouch, looks like it really doesn't like that ancient SQLite version:

undefined symbol: sqlite3_create_window_function

Traceback (most recent call last):
  File "<string>", line 1, in <module>
    import sqlite3; print(sqlite3.sqlite_version)
    ^^^^^^^^^^^^^^
  File "/opt/hostedtoolcache/Python/3.13.3/x64/lib/python3.13/sqlite3/__init__.py", line 57, in <module>
    from sqlite3.dbapi2 import *
  File "/opt/hostedtoolcache/Python/3.13.3/x64/lib/python3.13/sqlite3/dbapi2.py", line 27, in <module>
    from _sqlite3 import *
ImportError: /opt/hostedtoolcache/Python/3.13.3/x64/lib/python3.13/lib-dynload/_sqlite3.cpython-313-x86_64-linux-gnu.so: undefined symbol: sqlite3_create_window_function
Error: Process completed with exit code 1.

[codecov]

Codecov Report

✅ All modified and coverable lines are covered by tests.
✅ Project coverage is 95.64%. Comparing base (0aefbb6) to head (d83d7a0).
⚠️ Report is 7 commits behind head on main.

Additional details and impacted files

@@            Coverage Diff             @@
##             main     #654      +/-   ##
==========================================
- Coverage   95.65%   95.64%   -0.02%     
==========================================
  Files           8        8              
  Lines        2874     2867       -7     
==========================================
- Hits         2749     2742       -7     
  Misses        125      125              

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

🚀 New features to boost your workflow:

• ❄️ Test Analytics: Detect flaky tests, report on failures, and find test suite problems.

[simonw]

Now this fails:

        else:
>           return self.conn.execute(sql)
E           sqlite3.OperationalError: no such table: pragma_function_list
sqlite_utils/db.py:535: OperationalError
=========================== short test summary info ============================
FAILED tests/test_plugins.py::test_prepare_connection - sqlite3.OperationalError: no such table: pragma_function_list
================== 1 failed, 988 passed, 22 skipped in 16.14s ==================

[simonw]

I think this is wrong:

select distinct name from pragma_function_list

Should be:

select distinct name from pragma_function_list()

[simonw]

Turns out other tests also skip if no pragma_function_list(), so I should do that.