Should db_version be greater than 5.5?

[nickchomey]

The db_version function returns 5.5, noting that it is the newest version of MySQL. However 8.0 is the latest, and there's also 5.7. Perhaps it won't matter at all, but I do know that some plugins check db versions and set different schemas based on that. Presuming that this function overrides the default one, it seems to me that it should retuen at least 5.7, if not 8.0.

[OllieJones]

The plugin also says "myisam" for storage engine instead of "innodb".

It would be good to expose the actual SQLite version, but we can't do that directly with db_version because those sqlite versions are numbers like 3.7.4 or 3.27.5. Those will fail the minimum MySQL version check.

In SQLite there are feature-difference breakpoints at

• 3.8.2 2013-12-06 Before this there are no clustered primary keys, that is, no WITHOUT ROWID syntax in the DDL.
• 3.24. 2018-06-04 Before this there's no UPSERT (ON CONFLICT DO UPDATE) syntax in SQLite.
• 3.28 2022-02-22 Before this there's no built-in unixepoch function. This doesn't matter for this plugin because it implements user-defined functions for timestamp based operations

I propose:

1. make the access method returned, instead of MyISAM, be "sqlite"
2. make the db_version number reflect the SQLITE version, so version 3.8.2 of SQLite looks like 5.8.8.2. That is, sleazily specified, start the version number with 5. Next number is 5+3 (for SQLite 3). Next two numbers are the last two numbers of the SQLite version.

This semantic version number kludge is unambiguous and durable because we know there will never be a MySQL or MariaDB 5.8, 5.9, 5.10 etc.

[OllieJones]

The actual version of SQLite in use depends on the version installed as the php extension on each host. I've never seen anything before 3.7 in the wild. A variety of other versions are out there.

Caution: sometimes the command-line sqlite3 utility on a box has a different version than the php extension. If you use the command line to debug or do other things, this causes the confusion (and bizarre to people accustomed to client-server DBMS operation) situation where two different versions of the software are accessing the same database container files simultaneously. Watch for that.

[nickchomey]

I defer to Ollie! Glad that I was able to point out something that needs attention.

[ironprogrammer]

The return for db_version() was updated to 8.0 in https://github.com/WordPress/sqlite-database-integration/commit/768f8cc6d2a2707f744bba31cb0e5421a68c0f4f (following WordPress's minimum required version bump). Should this issue be marked as resolved?

Regarding the proposals mentioned in https://github.com/WordPress/sqlite-database-integration/issues/24#issuecomment-1466896373, I think that WordPress Core's native support for SQLite would promise more accurate version info.