Draft/POC for SQLite support in Core

[aristath]

trafficstars

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

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

---

This Pull Request is for code review only. Please keep all other discussion in the Trac ticket. Do not merge this Pull Request. See GitHub Pull Requests for Code Review in the Core Handbook for more details.

[adamziel]

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.

[aristath]

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

[sangaman]

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

[github-actions[bot]]

Test using WordPress Playground

The changes in this pull request can previewed and tested using a WordPress Playground instance.

WordPress Playground is an experimental project that creates a full WordPress instance entirely within the browser.

Some things to be aware of

• The Plugin and Theme Directories cannot be accessed within Playground.
• All changes will be lost when closing a tab with a Playground instance.
• All changes will be lost when refreshing the page.
• A fresh instance is created each time the link below is clicked.
• Every time this pull request is updated, a new ZIP file containing all changes is created. If changes are not reflected in the Playground instance, it's possible that the most recent build failed, or has not completed. Check the list of workflow runs to be sure.

For more details about these limitations and more, check out the Limitations page in the WordPress Playground documentation.

Test this pull request with WordPress Playground.

[vadimkantorov]

This is really nice also because it would better enable usage of WordPress as a Static Site Generator (as it simplifies operating WordPress using file system only, for both settings and content) for simple content-only WordPress-based blogs

[mrchrisadams]

hi folks.

I've seen a bit of pushback against SQLite because of both the license it uses, along with their policy for accepting contributions.

If this is still a bone of contention, it's worth knowing that there is a API compatible fork of SQLite called libSQL, which has a code of conduct, and accepts contributions, and uses an OSI approved open source licence.

Here's the manifesto published, explaining why it exists: https://turso.tech/libsql-manifesto

Here's the project: https://github.com/tursodatabase/libsql

It's also worth knowing that there is work ongoing to support libSQL in Laravel, and they detail their reasons for doing so too: https://github.com/laravel/framework/discussions/49340

For what it's worth, I'm a fan of SQLite (or SQLite compatible) support making it into Wordpress, so I'm happy to see it in core, as I actually think it could make for a nicer (albeit different) onboarding experience than the famous 5 minute install.

[dingo-d]

Just a quick question, when using the SQLite drop-in, does anybody get errors on wp 6.5 regarding the minimum MySQL version?

[vadimkantorov]

This would also lead a way to SSG mode where the content is stored in individual text files (as markdown or json). Writing a script for populating a temp SQLite database from a bunch of text/json files/config files in directories should be quite easy

An important functionality for wpcli should be conversion/export from an existing MySQL Wordpress db to a SQLite db file. Handrolled hacks are easy to come by, but having a more or a less CI-tested one on large realistic databases would be of great value

[aristath]

An important functionality for wpcli should be conversion from an existing MySQL Wordpress db to a SQLite db file

That's already possible from the terminal, but I completely agree... A WP-CLI script specifically for that task would simplify things a lot 👍

[vadimkantorov]

Also, maybe a UI admin button for downloading/exporting existing MySQL db as a SQLite file would be useful (for the cases where we don't have an ssh access to the hosted installation and e.g. only sftp access)

[vadimkantorov]

Also, an interesting idea could be implementing a SQLite vtab extension which would allow directly working with posts/pages stored as files in a file directory and config in a json file on disk even without importing them to a temp SQLite database file

[vadimkantorov]

Also, maybe a UI admin button for downloading/exporting existing MySQL db as a SQLite file would be useful

And maybe this export-everything-as-a-single-sqlite-db-file admin UI functionality (and wp-cli functionality) could be merged in the mainline even before the merge of the full Sqlite support changeset...

[vadimkantorov]

In general, curious what is the status of this feature/merging-into-the-mainline with the core Wordpress team? Looking forward to benefit from this feature in mainline :)

Oh I see, it appears that first the parser migration needs to be done:

• https://github.com/WordPress/sqlite-database-integration/pull/157

[ecotechie]

Just a quick question, when using the SQLite drop-in, does anybody get errors on wp 6.5 regarding the minimum MySQL version?

Yup. It seems we are now looking for MySQL and fail otherwise? https://github.com/aaemnnosttv/wp-sqlite-db/issues/62