CI: Monitor support for all the SQL queries used by all the WordPress plugins

[adamziel]

Goals

• Quantify the improvements we'll get with the new approach to SQL
• Publish the data in an open way everyone can see
• Monitor MySQL support over time

Let's go through all the open-source PHP code in the WordPress ecosystem, extract SQL queries where we can recognize them, and run them all through the SQLite on the upcoming MySQL driver.

Implementation ideas

Sourcing plugins

WordPress plugins data sources:

• https://wordpress.org/plugins
• https://github.com/dd32/wp-public-data
• https://wpdirectory.net/

Parsing queries

Here's a few ways to approach this:

• @bgrgicak mentioned there's an open-source Woo quality toolkit that can potentially help here.
• Use token_get_all or the PHP-Parser to find all the $wpdb->query() calls in the plugin (and, over time, also mysql_query etc.)
• Create a custom PHPCS rule

Let's only process the queries that can easily be analyzed statically, like $wpdb->query("SELECT * FROM table WHERE a=b");. For v1 let's completely ignore plugins stitching SQL from multiple strings like `$query .= " WHERE a=b";.

Publishing the data

Let's publish structured data on GitHub. Maybe as JSON? Let's assign each report a date and keep the old reports in place so everyone can see the progress each change makes. We could eventually include these reports in the SQLite integration CHANGELOG.

Let's lean on the error reporting tools and publishing pipelines explored by @bgrgicak here: https://github.com/bgrgicak/playground-tester.

cc @akirk @JanJakes @wojtekn @jeroenpf @fuilddot @danielbachhuber

[bgrgicak]

@bgrgicak mentioned there's an open-source Woo quality toolkit that can potentially help here.

QIT is the project I was thinking of.