Way to test SQLite 3.37 (and potentially other versions) in CI

[simonw]

Need to figure out a good pattern for testing this in CI too - it will currently skip the new tests if it doesn't have SQLite 3.37 or higher.

Originally posted by @simonw in https://github.com/simonw/sqlite-utils/issues/344#issuecomment-982076924

[simonw]

Ideally I'd like an extra set of matrix options for different versions of SQLite.

I can use pysqlite3 for this, but it isn't a completely compatible drop-in replacement - turns out it doesn't support the iterdump() method for example, see https://github.com/coleifer/pysqlite3/issues/24

[simonw]

Here's a modified version of the dump command that works even with pysqlite3:

@cli.command()
@click.argument(
    "path",
    type=click.Path(exists=True, file_okay=True, dir_okay=False, allow_dash=False),
    required=True,
)
@load_extension_option
def dump(path, load_extension):
    """Output a SQL dump of the schema and full contents of the database"""
    db = sqlite_utils.Database(path)
    _load_extensions(db, load_extension)
    # pysqlite3 doesn't implement .iterdump()
    from sqlite3.dump import _iterdump

    for line in _iterdump(db.conn):
        click.echo(line)

[simonw]

For the moment I think I'll combine two problems into one, and just add a single matrix alternative that uses pysqlite3 running SQLite 3.37.0 - only on macOS and Linux so I don't have to figure out how to compile it for Windows.

[simonw]

I have a working recipe for compiling it for macOS here: https://github.com/simonw/sqlite-utils/issues/344#issuecomment-982006544

[simonw]

To keep things simple for the moment I'm only going to add one extra thing to the matrix, and it will be a run of the tests against SQLite 3.37.0 using pysqlite3 on Linux only.

I can use this mechanism: https://docs.github.com/en/actions/learn-github-actions/workflow-syntax-for-github-actions#example-including-new-combinations