10 ms·

I'm highly in favour of this, but wanted to point out an important implementation detail in case people don't want to look through the code. Since WordPress do

I'm highly in favour of this, but wanted to point out an important implementation detail in case people don't want to look through the code. Since WordPress doesn't have a database abstraction, SQLite integration is done by transforming the SQL query strings meant for MySQL. This not only means doing regexp matches with string replacement, but trying to emulate MySQL functions with either SQLite equivalents, or in the worst case, in PHP application code.

maxloh 4y ago
PHP is ultra slow anyways.
MuffinFlavored 4y ago
> Since WordPress doesn't have a database abstraction How hard would it be to "add one"/refactor?
- bretthopper 4y ago
  I'd guess very hard. But just refactoring the WordPress codebase isn't enough, you'd ideally want every plugin to adopt the new API as well... Although you could always keep this implementation as the fallback behaviour.
- CharlesW 4y ago
  > How hard would it be to "add one"/refactor? From a technical POV: This is potentially straightforward if WordPress leverages (and "blesses", for plug-in developers) a proven abstraction layer like Doctrine DBAL that supports both MySQL and SQLite. From a non-technical POV: There are tens of thousands of WordPress plug-ins, and even updating the top 1,000 that are good/popular will be a multi-year lift.
  - georgyo 4y ago
    plug-ins are likely the real burn. MediaWiki technically supports PostgreSQL and SQLite for many years, but extensions are really MySQL focused making it the only real choice.
- tyingq 4y ago
  It doesn't even currently use real parameterized queries...it has a method that sounds like they are real, but they aren't...just a really hairy bunch of string escaping. They really need to re-write the database layer from scratch. Behold: https://github.com/WordPress/WordPress/blob/master/wp-includes/class-wpdb.php#L1417 https://github.com/WordPress/WordPress/blob/master/wp-includ...
  - ezekg 4y ago
    This brings back bad memories. I shouldn’t have reminded myself of what WP code looks like.
- unity1001 4y ago
  The problem is not adding one. The problem is that it would complicate the infrastructure. From adding more performance requirement to WP wherever it is hosted to the compatibility concerns with plugins. Its a tall tale.
- AdamJacobMuller 4y ago
  > How hard would it be to "add one"/refactor? To add one? Trivial. To refactor all of WP core to use it? I could probably do it in a week or two of focused work. To enable all plugins and wordpress themes to use it? It is, literally, probably a million times more LOC.
- bombcar 4y ago
  Probably easier to use MySQL as the db layer and have it use SQLite as the backend store somehow heh.
ilyt 4y ago
... of course it would be fucking cursed, it's wordpress
rawoke083600 4y ago
Lol after decades of programming, there actually WAS a need in THIS project to 'abstract/subclass' the db.code. You know in case we want to switch dbs in the future.:)
noahtallen 4y ago
Interestingly, WordPress does (kind of) have a database abstraction. The way you get it is to format your data model as a “custom post type,” and then you basically get an object store with various predefined functions to access it… kinda works for more use cases than you’d think, but also weird!
smcnally 4y ago
This sqlite abstraction has been working well for me: https://github.com/aaemnnosttv/wp-sqlite-db https://github.com/aaemnnosttv/wp-sqlite-db it’s one file — ‘db.php’ — you swap in for the core file. From there, it’s mostly been seamless. ~20% of the 5k LOC, is “Method to emulate MySQL XXX() function.” Less than that for query parsing, regex & rewrites https://github.com/aaemnnosttv/wp-sqlite-db/blob/master/src/db.php https://github.com/aaemnnosttv/wp-sqlite-db/blob/master/src/...

maxloh 4y ago

PHP is ultra slow anyways.

MuffinFlavored 4y ago

> Since WordPress doesn't have a database abstraction How hard would it be to "add one"/refactor?

bretthopper 4y ago
I'd guess very hard. But just refactoring the WordPress codebase isn't enough, you'd ideally want every plugin to adopt the new API as well... Although you could always keep this implementation as the fallback behaviour.
CharlesW 4y ago
> How hard would it be to "add one"/refactor? From a technical POV: This is potentially straightforward if WordPress leverages (and "blesses", for plug-in developers) a proven abstraction layer like Doctrine DBAL that supports both MySQL and SQLite. From a non-technical POV: There are tens of thousands of WordPress plug-ins, and even updating the top 1,000 that are good/popular will be a multi-year lift.
- georgyo 4y ago
  plug-ins are likely the real burn. MediaWiki technically supports PostgreSQL and SQLite for many years, but extensions are really MySQL focused making it the only real choice.
tyingq 4y ago
It doesn't even currently use real parameterized queries...it has a method that sounds like they are real, but they aren't...just a really hairy bunch of string escaping. They really need to re-write the database layer from scratch. Behold: https://github.com/WordPress/WordPress/blob/master/wp-includes/class-wpdb.php#L1417 https://github.com/WordPress/WordPress/blob/master/wp-includ...
- ezekg 4y ago
  This brings back bad memories. I shouldn’t have reminded myself of what WP code looks like.
unity1001 4y ago
The problem is not adding one. The problem is that it would complicate the infrastructure. From adding more performance requirement to WP wherever it is hosted to the compatibility concerns with plugins. Its a tall tale.
AdamJacobMuller 4y ago
> How hard would it be to "add one"/refactor? To add one? Trivial. To refactor all of WP core to use it? I could probably do it in a week or two of focused work. To enable all plugins and wordpress themes to use it? It is, literally, probably a million times more LOC.
bombcar 4y ago
Probably easier to use MySQL as the db layer and have it use SQLite as the backend store somehow heh.

ilyt 4y ago

... of course it would be fucking cursed, it's wordpress

rawoke083600 4y ago

Lol after decades of programming, there actually WAS a need in THIS project to 'abstract/subclass' the db.code. You know in case we want to switch dbs in the future.:)

noahtallen 4y ago

Interestingly, WordPress does (kind of) have a database abstraction. The way you get it is to format your data model as a “custom post type,” and then you basically get an object store with various predefined functions to access it… kinda works for more use cases than you’d think, but also weird!

smcnally 4y ago

This sqlite abstraction has been working well for me: https://github.com/aaemnnosttv/wp-sqlite-db https://github.com/aaemnnosttv/wp-sqlite-db it’s one file — ‘db.php’ — you swap in for the core file. From there, it’s mostly been seamless. ~20% of the 5k LOC, is “Method to emulate MySQL XXX() function.” Less than that for query parsing, regex & rewrites https://github.com/aaemnnosttv/wp-sqlite-db/blob/master/src/db.php https://github.com/aaemnnosttv/wp-sqlite-db/blob/master/src/...