Add native support for SQLite

[aristath]

A few months ago, a proposal was published to natively support SQLite in WordPress Core (​https://make.wordpress.org/core/2022/09/12/lets-make-wordpress-officially-support-sqlite/).
That proposal outlines a lot of the benefits of supporting SQLite in Core.

The proposal has received a lot of positive feedback, both from the community and the WordPress leadership so this Trac ticket is the next step, to promote collaboration and discussion.

The performance team has been working on an implementation that adds support for SQLite via the SQLite Database Integration plugin (​https://wordpress.org/plugins/sqlite-database-integration/). Before being published as a stand-alone plugin, it was implemented as a module in the Performance Lab plugin (​https://wordpress.org/plugins/performance-lab/) where it received a lot of user-testing.

[aristath]

With the plugin active, all WordPress PHPUnit tests pass (with just a few exceptions that need patching in Core when the featured plugin gets merged, see ​https://github.com/WordPress/sqlite-database-integration/pull/9#issuecomment-1437018625 for details).

This is a POC for porting ​https://wordpress.org/plugins/sqlite-database-integration/ to Core.

Trac ticket: https://core.trac.wordpress.org/ticket/57793

[swissspidy]

In the blog post I see lots of unanswered questions about keeping it as a canonical plugin rather than merging it into core. Given that the post is already a few months old, I'd suggest writing a follow-up post with the latest updates (like the improved security thanks to the new parser implementation, which is pretty neat), which can then be discussed in dev chat as well.

[aristath]

Thank you for the feedback @swisspidy!

In the blog post I see lots of unanswered questions about keeping it as a canonical plugin rather than merging it into core.

Using the featured plugin is a great way to allow users to test the implementation, iron-out any issues etc. However, long-term it doesn't make sense to use this as a plugin.
Keeping it as a canonical plugin basically negates most of the SQLite benefits... A plugin can only be installed on an existing site - which would require having a MySQL database.

Given that the post is already a few months old, I'd suggest writing a follow-up post with the latest updates (like the improved security thanks to the new parser implementation, which is pretty neat), which can then be discussed in dev chat as well.

That's a good idea, adding it to my TODO list 👍

Update: This PR now translates queries using the ​MySQL lexer from sqlite-database-integration instead of ​regular expressions from wp-sqlite-db. The new implementation is a lot safer and more reliable.

I pushed a change as a proof-of-concept for how the installation screen can change.
It's not perfect, but it works and showcases how users can pick a database-type, and what that may look like.
If we don't want to provide users the option to select the db-type via the UI, we can revert the changes in these 2 files:

• src/wp-admin/setup-config.php
• wp-config-sample.php

I'm excited for this. I'd be happy to test this PR on my personal website whenever it's nearing readiness.

[joemcgill]

Adding this to the Future Release milestone for consideration once a stronger use case is presented for why this should be in core and not simply continue as a feature plugin.