5 ms·
I figured out how this works. The secret sauce is this code, which uses regular expressions to rewrite the MySQL SQL queries for compatibility with SQLite! htt
by simonw 5y ago
I figured out how this works. The secret sauce is this code, which uses regular expressions to rewrite the MySQL SQL queries for compatibility with SQLite!
https://github.com/aaemnnosttv/wp-sqlite-db/blob/14f9e917c5538673e3ff9111cede09361777e4a3/src/db.php#L2888 https://github.com/aaemnnosttv/wp-sqlite-db/blob/14f9e917c55...
- arichard123 5y agoYikes. That looks dangerous.
- jkingsman 5y agoYeah plugins' abilities to issue arbitrary sql queries are a bit of an Achille's Heel for any database reworking within Wordpress -- there's no telling what quirks a plugin might be using. I don't know if there's a neat way around this... maybe something that can intercept a soft parse and have a one-to-one conversion to a different syntax. I don't think there's a non-brittle solution here.
- woodruffw 5y agoThis is a disaster waiting to happen. Regular expressions should never be used to parse non-regular languages, of which SQL is one. There are a variety of mature MySQL dialect parsers available[1][2], and MySQL should have its own public APIs for transforming a query into an AST. Any of those would be a safer and more correct alternative. [1]: https://github.com/pingcap/parser https://github.com/pingcap/parser [2]: https://github.com/square/mysql-parser https://github.com/square/mysql-parser
- anonymousDan 5y agoOut of interest, do you know if there is any similar tool/library for getting a MySQL query plan (either logical or physical) in a serialiable format?
- woodruffw 5y agoNot that I know of, sorry. There’s probably something in the MySQL APIs, but it’ll probably need additional work to get it into a serializable format.
- sk5t 5y agohttps://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags https://stackoverflow.com/questions/1732348/regex-match-open...
- madeofpalk 5y agoWordpress isn't database agnostic by default? That seems strange to me!
- deleted 5y ago[deleted]
- anonydsfsfs 5y agoWordpress was first released in 2003. There wasn't a database abstraction layer for PHP at the time (PDO was first released in 2005), so it was tightly coupled to MySQL.
- 0x0 5y agoAnd PDO isn't even a proper database abstraction layer, it's just a way to connect to various databases and issue prepared statements. You still have to write your own SQL queries and create table statements, which will most certainly not be portable.
- madeofpalk 5y agoFair. It's 2022 now. I figured in the 19 years since they would have done something like this.
- ceejayoz 5y agoThey’ve consistently prioritized backwards compatibility over pretty much anything else.
- tyingq 5y agoIt doesn't even use placeholders and prepared statements. What looks like prepared statements, for example, are not. Behold: https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php#L1395 https://github.com/WordPress/WordPress/blob/master/wp-includ...
- mickael-kerjean 5y ago
- raverbashing 5y agoI wonder how much can we call SQL a standard with so many differences between two different servers like this. Or at least how much WP could be changed to meet at a common ground. How hard would be to run WP on Postgres? MSSQL? Or that legal company that sells a DB?
- paulryanrogers 5y agoThere have been many attempts to support PostgreSQL. Generalized SQL is difficult because WP also has plugins that assume MySQL, and RDBMS vary widely in their dialects even for relatively simple things like adding columns.
- exhilaration 5y agoFantastic! The author just provided a path for a lot of us to quickly convert our MySQL-driven PHP apps to SQLite.