macports.sqlext may need patching and could have more complete usage instructions

[RJVB (René Bertin)]

As a database n00b it wasn't exactly clear to me how to use this extension

---

The macports.sqlext extension provided by port:macports.sqlext no longer works, idem for the version of the code in the git master branch.

As already posted in a comment in #62365:

> port installed macports.sqlext
The following ports are currently installed:
  macports.sqlext @2.6.3_0 (active)

> sudo cp -p /opt/local/var/macports/registry/registry{,-20240920}.db
> sqlite3
SQLite version 3.41.0 2023-02-21 18:09:37
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load /opt/local/lib/sqlite3/macports.sqlext
sqlite> .open /opt/local/var/macports/registry/registry-20240920.db
sqlite> VACUUM;
Runtime error: no such collation sequence: VERSION

Indeed, looking at the sqlext.c code one explanation is obvious: the HAVE_SQLITE3EXT_H macro has been replaced by SQLITE3EXT_H(and I don't find any evidence of HAVE_SQLITE3EXT_H being set by the build system either). A better check would a simple #ifdef sqlite3_create_collation but even with that change made it seems that the sqlite3_extension_init() function is simply not being called when the extension is loaded.

[RJVB (René Bertin)]

Correction: the init function does get called, but is doesn't appear to have the intended effect:

> sqlite3
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load /alt/local/libexec/macports/lib/registry2.0/macports.sqlext
creating collation sequence VERSION to sql_version()
sqlite> .open /opt/local/var/macports/registry/registry-20240920.db
sqlite> VACUUM;
Runtime error: no such collation sequence: VERSION

With a bit more info printed it finally becomes clear that loading extensions only applies to already attached database(s)...

> sqlite3
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load /alt/local/libexec/macports/lib/registry2.0/macports.sqlext
creating collation sequence VERSION to sql_version() for database ""
sqlite> .open /opt/local/var/macports/registry/registry-20240920.db
sqlite> .load /alt/local/libexec/macports/lib/registry2.0/macports.sqlext
creating collation sequence VERSION to sql_version() for database "/opt/local/var/macports/registry/registry-20240920.db"
sqlite> VACUUM;
sqlite> ^D

I'll attach my modifications as a patch but this ticket can be closed. Or rather, maybe only after updating the instructions for the sake of other database n00bs.