Add SQLite compatibility to `wp db` commands

[janw-me]

Describe the current, buggy behavior

Several wp db * commands don't work with sqlite.

SQLite is in testing fase

Describe how other contributors can replicate this bug

• Install a new WP,
• wp plugin install performance-lab --activate
• In the performance-lab plugin settings activate the sqlite setting.
• wp-content/databases/.ht.sqlite should exist.
• wp db drop --yes the mysql gets dropped,
  This is fine for this test to make sure you don't read the mysql.
  But shouldn't it drop the sqlite file?
• Click around in the admin/frontend. The website WordPress still works.
• wp post list
  Shows the hello world
• wp db query "SELECT * FROM wp_posts;"
  Gives an error.
• wp db search 'hello'
  Does work, with a warning.

It appears wp-content/db.php doesn't get loaded.
I don't know when/where it should be included. And if it even would help.

Keep in mind, FQDBDIR and FQDB constants can be set to change the location & filename of the sqlite file.

[schlessera]

@janw-me Currently, a lot of the wp db * commands do not use the WPDB abstraction, but rather the mysql or mysqldump binaries, as the DB could be in a non-working state and you still want to have control over it.

To support SQLite, we'd need to find a standardized way to act on the SQLite file through the shell, not the WPDB wrapper.

[danielbachhuber]

Removing the bug label because WP-CLI doesn't formally support SQLite. It would be cool if it did though!

[swissspidy]

wp-cli/wp-cli-tests now supports running tests against SQLite with the SQLite integration plugin. Usage: WP_CLI_TEST_DBTYPE=sqlite composer behat
So that's a start.

If the command detects FQDBDIR / FQDB / DB_ENGINE it could switch to SQLite mode and run raw SQLite queries where needed. And when using $wpdb, the MySQL queries would get translated by the plugin.

Edit: I'm actually running into some infinite loop when running tests right now 🤔 Tests just keep hanging and can't be cancelled.

Edit 2: Fixing wp db create would help unskip a test in the config-command repo, see wp-cli/config-command#168

[swissspidy]

OK, now that tests are properly running I have a better picture of things. I am not super familiar with the db command though, so please bear with me.

One problem is that most commands (e.g. wp db query or wp db create) run on after_wp_config_load and thus only know constants like DB_NAME and ABSPATH from wp-config.php. Since they are loaded before WordPress, they do not know anything about SQLite. Not sure how to address this one. Maybe we need to manually look for FQDB or assume its location in order to access the database.

wp db tables almost works. There are some extra tables added by the plugin, but that can be caught in tests. Extra args like --scope don't work. The raw SQL queries for this are in wp-cli/php (wp_get_table_names()) and might require changing there. Or maybe this is something that needs to be fixed in the plugin.

wp db size doesn't work out of the box. Might require some custom queries that use dbstat if that is available (depends on SQLITE_ENABLE_DBSTAT_VTAB compile flag)

wp db prefix or wp db columns are simple enough that they should just work.

---

To support SQLite, we'd need to find a standardized way to act on the SQLite file through the shell, not the WPDB wrapper.

The SQLite plugin requires PDO, so we could rely on that too to interact with SQLite

[danielbachhuber]

One problem is that most commands (e.g. wp db query or wp db create) run on after_wp_config_load and thus only know constants like DB_NAME and ABSPATH from wp-config.php. Since they are loaded before WordPress, they do not know anything about SQLite. Not sure how to address this one. Maybe we need to manually look for FQDB or assume its location in order to access the database.

For this particular scenario, I think we could replicate WordPress' db loading logic inside of WP-CLI (i.e. assume db drop-in/SQLite given specific conditions).

wp db tables almost works. There are some extra tables added by the plugin, but that can be caught in tests. Extra args like --scope don't work. The raw SQL queries for this are in wp-cli/php (wp_get_table_names()) and might require changing there. Or maybe this is something that needs to be fixed in the plugin.

wp db size doesn't work out of the box. Might require some custom queries that use dbstat if that is available (depends on SQLITE_ENABLE_DBSTAT_VTAB compile flag)

These sound like great Hack Day projects! 😁

[swissspidy]

For this particular scenario, I think we could replicate WordPress' db loading logic inside of WP-CLI (i.e. assume db drop-in/SQLite given specific conditions).

Yeah we can try that. Could be as simple as:

1. Get WP_CONTENT_DIR, default to wp-content if not defined. Ditto for WPINC
2. If WP_CONTENT_DIR . '/db.php' exists
   1. Load class-wpdb.php
   2. Load db.php
3. If DB_ENGINE defined && 'sqlite' === DB_ENGINE, use SQLite

Might need to load some more files in (2) because the plugin's drop-in calls functions like add_action() that might not be defined yet. So need to load the ones that could be needed. compat.php and plugin.php for example.

Caveat: what if the drop-in is not actually for SQLite but for something else? What do we do then?

Edit: perhaps just do WP_CLI::get_runner()->load_wordpress(); if SQLite is somehow detected?