Let’s make WordPress officially support SQLite

https://make.wordpress.org/core/2022/09/12/lets-make-wordpress-officially-support-sqlite/

by sqlsite • 4 years ago

152 94 4 years ago

make.wordpress.org

mmerlin3• 4 years ago

Combine SQLite WordPress with the PeachPie Blazor.NET Wordpress (step debugging the PHP in Visual Studio) and ding ding ding we have a winner for security scalability and resource efficiency

https://github.com/iolevel/wpdotnet-sdk

https://docs.peachpie.io/scenarios/blazor/debugging/

chiefalchemist• 4 years ago

What about xDebug?

mmerlin3• 4 years ago

it works for debugging but xdebug is definitely harder to configure and...

- feels 5x more sluggishly slower than PeachPie in Visual Studio

- not giving you any of the other runtime-speedup or deployment and security and scalability advantages that PeachPie gives you...

Peachpie + wordpress + SQLite will be a very portable stack

nowhere as efficient as Redbean, except the functional convenience of giving you most of PHP and Wordpress now locally deployable as a "desktop sidecar" in your .NET project publish agglomerations

ThinkBeat2• 4 years ago

I have been trying to figure out how this works, by looking around the repo.

Does this compile PHP to dotNet? Looking at the csproj files I cant find the magic dust that enables this.

if it does not compile PHP -> dotNet I find the documentation to be confusing.

Does this create a dotnet proxy / bridge to a regularly running PHP Wordpress site?

the_only_law• 4 years ago

Yes, PeachPie is a PHP -> .NET compiler/runtime.

tommica• 4 years ago

Seems like a good idea - use [WAL mode](https://sqlite.org/wal.html), and UUID based filename to provide obfuscation for the path and it should be a neat encapsulated installation.

retcore2• 4 years ago

Time series would be more beneficial for a significant user base of WordPress which is using WP as a de facto company record described as a CMS-CRM.

omnimus• 4 years ago

And completely useless for the most significant user base of WordPress which is using WP for their small marketing website or blog.

Something1234• 4 years ago

Why would a time series database be better for a CMS-CRM? Or am I completely miss reading your comment?

zapt022• 4 years ago

You could also place the database outside the web root. As far as I know WordPress already supports putting `wp-config.php` one directory up from the main installation.

forinti2• 4 years ago

It would be great to use sqlite during development and then MariaDB in production.

It's not hard to clone MariaDB, but sqlite is much more practical.

onli• 4 years ago

For most sites you do not need to switch away from sqlite for production. It is fast enough.

forinti2• 4 years ago

Yes, I'm pretty sure SQLite would be more than enough for a lot of sites.

The point I was trying to make was that, if you are going to add SQLite support, you might as well add some tooling to help you switch between databases.

sofixa• 4 years ago

There are other considerations besides speed for production - like redundancy.

technion3• 4 years ago

I can see that the difficulty will be the plethora of plugins that for whatever reason won't end up being compatible, and the Wordpress community will quickly consider SQLite a "broken database" or similar.

For example, one of the most common plugins is Yoast, I'm sure somewhere in this spaghetti something will be MySQL specific.

https://github.com/Yoast/wordpress-seo/blob/0efeda377ea09931...

retcore2• 4 years ago

Remembering the horror that was my first encounter with a description of MySQL as a DBMS, by a far from newly minted* WordPress hosting entrepreneur, the greater difficulty may be overcoming cultural cognitive dissonance that Database != MySQL.

*as a Flash website developer my friend wrote a blood insulin device graphic from spec so completely down to knowing time drift, his unintended emulation was used for regulatory approval purposes.

How Oracle never capitalized on acquiring MySQL as leverage into small businesses via WordPress beggars my belief. But I'll wager that the kind of problems discussed here come into it.

Cthulhu_• 4 years ago

WP could make a test suite mandatory and run tests on their hardware under their supported configurations; if it doesn't work on sqlite, it is not accepted in the plugin index.

Likewise, security checks.

dawnerd• 4 years ago

They really need to force all plugins to pass the VIP phpcs rules (would need to be modified a bit). It wouldn’t be perfect but I bet it would greatly increase security. I’d also like to see them switch to git for the plugin repos.

Ayesh• 4 years ago

And the plugin authors would simply self-host them.

Some of the top plugins available (and are linted) on WordPress.org tend to be stubs containing a lite version plus an ad, and those plugins will have less friction if they entirely self host on their own servers.

jillesvangurp• 4 years ago

Would not be a bad idea. With Github actions, doing CI builds for plugins and wordpress should be fairly easy to do with either dockerized mariadb or sqllite. Set up a little matrix build. And for good measure, maybe support nginx and posgresql as well out of the box. Nothing to incentivize plugin developers as having a few red warnings next to their name in the plugin registry.

I setup builds for most of my oss projects. It's not that hard. Of course at the scale the wordpress community operates, this would require some non trivial amount of resources.

unity100117• 4 years ago

Yeah. You can be sure that 60,000+ plugin authors and 50% of the web that use WordPress and those plugins will just allow that.

shp0ngle• 4 years ago

seeing this code, I’m happy that I no longer do PHP

12907835202• 4 years ago

"Happy you no longer do Wordpress"

I work with PHP every single day and thankfully never encounter anything like this and certainly don't write it.

You should see some of the C++ I see everyday. My favourite is a single 20,000 line God file called extractor_manager.cpp that merges data from MS Access and MySQL using a variety of typo'd command line arguments.

Doesn't mean C++ is a bad language.

iLoveOncall4• 4 years ago

The culprit is not PHP, the culprit is WordPress being developed with antiquated software engineering practises (or rather, lack of any good practice).

Just take a look at their coding guidelines: https://developer.wordpress.org/coding-standards/wordpress-c... they're laughably bad and nonsensical.

capableweb• 4 years ago

seeing this code, I’m happy that I no longer do PHP

The file linked is relatively OK though compared to what's out there (in any language). Or maybe I'm just used to diving knee-deep into decade old 10,000 LOC files and a bit jaded...

liotier• 4 years ago

Should the creation of prepared statement be shifted down from that code to adaptation layer between it and each database's library ?

weird-eye-issue• 4 years ago

I was expecting some really esoteric SQL used in that file but the most advanced query I see contains a subquery with a union. Does SQLite not support these basic features?

technion3• 4 years ago

I didn't spend time looking for specific features, as much as pointing out the standard is to be extremely hand wrangled and therefore prone to such issues. I have no idea if this sort of nonsense (this is an official Automattic plugin) is going to work in SQLite:

https://github.com/Automattic/jetpack/blob/2447e267e40f2d133...

But certainly creating a new table with ENGINE= which you can also find in that file isn't likely to work as intended on other platforms.

rmccue2• 4 years ago

One of the people cited as being involved in the discussion at WCEU (a major WordPress conference) is Joost de Valk, the creator of Yoast (and currently interim CTO of Yoast). I would suspect if any plugin invests in making sure it's compatible, it'll be Yoast SEO. :)

billpg4• 4 years ago

Those plugins that don't work with SQLite will be replaced with plugins that do.

harvie• 4 years ago

No. Instead let's make dokuwiki officialy support git as its backend! So i can push/pull dokuwiki content for purposes of backup, deployment, offline access and commandline editing :-) Why use custom PHP based history tracking solution, when we already have state of the art version control in git?

iLoveOncall4• 4 years ago

I am all for WordPress building a database abstraction layer, but supporting explicitly one specific new database is a step in the opposite direction. It will just make it harder to implement such a layer in the future.

Abstract it or don't do it.

billpg4• 4 years ago

Absolutely. Abstract away those SELECT/etc and allow for someone to specify what handler is going to implement that abstract interface.

m-p-3• 4 years ago

So basically add SQLAlchemy as a middleware?

i_have_an_idea• 4 years ago

I'd rather have PostgreSQL.

margarina72• 4 years ago

well, there is a plugin for that, even if native support would be great

lloppal• 4 years ago

Awesome! I hope Mediawiki will follow suit!

rmccue2• 4 years ago

Just to note, Make/Core is a relatively open discussion site where contributors to WordPress can post proposals, and this does not necessarily indicate an official plan at this stage. It certainly might become one, but this is currently a proposal requesting discussion to form consensus.

billpg4• 4 years ago

I'd go even further and replace the database with a folder full of JSON files. (Or other similar structured text files.)

Why? Then I could store my WordPress folder in git and realistically merge changes between copies of my website.

tasuki• 4 years ago

Then why not use one of the myriads of static site generators?

billpg4• 4 years ago

I like WordPress.

ThinkBeat2• 4 years ago

I certainly hope they do not add an ORM into WordPress. It would add a big layer of complexity and break nearly all plugins that interact with the database.

This can be mitigated by maintaining and updating both the database access as it is today as well as the ORM layer.

Which would probably mean some plug-ins using one and some the other. Perhaps over a long enough time scale converging for the ORM.

Right now debugging problems with the interaction between database and application are blessed by it being fairly transparent

Adding a messy layer in between adds complexity not worth it unless it is required to add support for many other databases and I dont see any reason why it would be beneficial for enough users for that to be worth it.

Introducing just SQlite will expose a lot of problematic edge cases. since MySql provides a lot of feature that Sqlite does not. (which in most cases is a very good thing)

zapt022• 4 years ago

I don't think this is a correct take, because most plugins that interact with the database already use the abstraction functions, eg get/set_option(), WP_Query, get_post_meta() and so on.

These would all work out of the box.

The only plugins that would not work are:

1. Plugins that register their own database tables (however there already exists prior art such as https://github.com/aaemnnosttv/wp-sqlite-db for handling these cases)

2. Plugins that do direct queries against the standard database schema (broadly either for invalid (bad code) or performance (valid but slim use case) reasons)

Also, WordPress would of course keep the old query functions around and they would likely add a tag to the plugin repository so authors can mark plugins as supporting thes new ORM features.

Great idea in my opinion!

unity100117• 4 years ago

The cons that the author of the post lists make it a no-go. From SQLite not going to work well on sites with multiple authors (a killer from the start) to 'web hosts NEEDING TO ACT RESPONSIBLY' for this to work, literally make this the effort of a OSS project unknown to the WP ecosystem (50% of the web) to force its way into that ecosystem withouth having found any widespread use and people having accommodated it.

If it was worth doing it, someone would have published a plugin for it and it would have been working already. WordPress provides filters for database functions as well. So whatever db you want to use, could be reliably integrated by injecting yourself in between the functions and the db.

Of course, this means that you will immediately face compatibility problems with all the 60,000+ plugins in the ecosystem. And those plugins wont make themselves compatible with SQLite, neither they will accept delirious propositions like 'Make it a requirement for getting accepted to plugin directory'.

People have businesses to run, lives to live. WP ecosystem is not a weekend OSS hobby project for 50% of the web that it runs. People's income, their livelihoods depend on it. Users and developers alike. Nobody will take such arrogance to force them to accommodate some obscure OSS project. Obscure, because you can be sure that 50% of the web that use WordPress have no idea what SQLite is. You, the other ecosystems that you are in and contributing, your professional circle, may have. But the average flower shop owner somewhere in the US and the anime blogger somewhere in Japan, have no freaking idea what SQLite is. And they don't care. They have other things to care about than an OSS project that is unknown to them.

The propositions and the mentality in this thread sound extremely arrogant, and it reminds one about the time when Google tried to force Google+ on the people by forcing them to link their Youtube accounts to Google+. We know how that ended up.

If the OSS project that you love is not gaining traction in a certain demographic, its because it doesn't have use cases there and it does not accommodate those people enough. You cant force people to use it in such a case. You must make it accommodate an actual need and make it easy to use it. Not try to force your way into another ecosystem.

klelatti4• 4 years ago

I can’t see anyone forcing SQLite into WP just an informed discussion of the pros and cons.

On the other hand you seem to be completely against the idea on principle despite any advantages.

unity100117• 4 years ago

There are those who seem to belong to that community and who seem to literally force themselves. This may be the result of the religious nature of the project as hinted by its religious code of conduct. Just check the different comments in this thread.

ac29• 4 years ago

From SQLite not going to work well on sites with multiple authors (a killer from the start)

This is taken out of context and blown out of proportion. The post says:

"If multiple authors are editing posts on a site and updating them at the same time, then their save requests would run one after the other instead of simultaneously – so there would be a small delay (probably milliseconds, but still a delay)."

So, sure, if your site is so busy that multiple authors are posting within milliseconds of each other, there might be some extremely minor performance issues with SQLite.

unity100117• 4 years ago

So, sure, if your site is so busy that multiple authors are posting within milliseconds of each other

Yeah. That's a lot of high tier wordpress sites. People seem to think that WP is just individual blogs, but that was 10 years ago.

liveoneggs• 4 years ago

You have misunderstood the "con" entirely and the author has (possibly?) missed sqlite PRAGMAS which would make it behave entirely equal to mysql.

unity100117• 4 years ago

There wasn't just one con listed in his post at WP org. I just took two of them. There are various others which he honestly lists without realizing that various of them are game-breakers.

missed sqlite PRAGMAS which would make it behave entirely equal to mysql

More the reason for this being done through a plugin instead of being pushed into WP core. If it provides noticeable improvement and people use it, it will get installed by millions of people. If it does not, it wont.

peter_retief• 4 years ago

I was surprised that WordPress didn't support SQLite when I used it recently since a long time. Makes sense that it does.

vouaobrasil• 4 years ago

I think Wordpress should do one thing well. There are plenty of small CMS platforms that support SQlite like Bolt.

vlunkr• 4 years ago

It doesn’t do anything that well, so this could be the chance!

bubblematrix• 4 years ago

If it didn't do anything well, 60% of CMS wouldn't run Wordpress.

It doesn't do what happens in your world well.

tlhunter• 4 years ago

I've been saying this for a decade!

whalesalad• 4 years ago

Would love to see an abstraction layer in WP. It’s always boggled my mind why Postgres or any SQL db can’t be used instead.

cloudking• 4 years ago

The proposal mentions improving performance, I'm curious if anyone has an "ideal" set up for WordPress architecture? There are so many server configurations and caching solutions out there.

josephscott• 4 years ago

This could spiral into some interesting combinations. Imagine Redbean with PHP support, and it already includes SQLite. You could keep your entire site: PHP, database, files - in one file that also runs your web server, and it works on multiple operating systems.

stakkur• 4 years ago

I would go further than the writer and say SQLite would be appropriate for almost all WP applications. Offhand, I can’t think of a meaningful use case that would make MySQL more preferable to SQLite.

Let’s make WordPress officially support SQLite

Related Stories