SQLite Compatibility

[swissspidy]

When I manually run the tests locally with SQLite, the following scenarios are failing:

001 Scenario: Deleting all transients on single site # features/transient.feature:58
      Then STDOUT should be:                         # features/transient.feature:76
        $ wp transient delete --all
        Success: No transients found.

002 Scenario: Deleting expired transients on single site # features/transient.feature:123
      Then STDOUT should be:                             # features/transient.feature:134
        $ wp transient delete --expired
        Success: No expired transients found.

003 Scenario: Deleting all transients on multisite # features/transient.feature:194
      Then STDOUT should be:                       # features/transient.feature:215
        $ wp transient delete --all
        Success: No transients found.

004 Scenario: Deleting expired transients on multisite # features/transient.feature:286
      Then STDOUT should be:                           # features/transient.feature:301
        $ wp transient delete --expired
        Success: No expired transients found.

005 Scenario: List transients on single site # features/transient.feature:386
      Then STDOUT should contain:            # features/transient.feature:400
        $ wp transient list --format=csv
        name,value,expiration

006 Scenario: List transients on multisite # features/transient.feature:431
      Then STDOUT should contain:          # features/transient.feature:445
        $ wp transient list --format=csv
        name,value,expiration

007 Scenario: List transients with search and exclude pattern # features/transient.feature:476
      Then STDOUT should be:                                  # features/transient.feature:485
        $ wp transient list --format=csv --fields=name --search="foo"
        name

The transient command uses lots of custom MySQL queries. The SQLite integration plugin is supposed to make those SQLite-compatible, but perhaps something is not working there.

Or, since deletion and listing are failing, maybe the issue is that the insertion using set_transient() is failing in the first place.

Let's figure it out together. This could involve changes to the command, the tests, core, or the SQLite plugin.

[swissspidy]

OK, so for the transient delete command there seem to be issue with the way LIKEs are being escaped by WordPress. The SQLite plugin might not be translating those properly and ending up deleting 0 rows. "Raw" queries without $wpdb->prepare do seem to work.
That said, the returned deletion counts seem to be wrong. Not sure if we need something like $wpdb->query( "SELECT changes() FROM {$wpdb->options}" ) as well.

[petitphp]

@swissspidy I took a look at this, I'm not familiar with SQLite so I might be mistaking, but it seems that SQLite doesn't support DELETE statement for multiple tables like MySQL does :

DELETE a, b FROM {$wpdb->options} a, {$wpdb->options} b
WHERE a.option_name LIKE %s
AND a.option_name NOT LIKE %s
AND b.option_name = CONCAT( '_transient_timeout_', SUBSTRING( a.option_name, 12 ) )

I did manage to get the tests to pass by doing three queries to replicate what this DELETE statement, but maybe the SQLite WordPress plugin could handle that automatically.

[swissspidy]

Interesting! Do you perhaps have a draft PR with that solution so that we could take a look?

It does seem like a case the SQLite plugin could handle, so If you could open an issues I'm sure the maintainers can take a look.

[petitphp]

Turn out it's related to the escaping, like you suspected.

Looking at the wpdb::esc_like method in the SQLite integration plugin, they purposely remove the addslashes since SQLite doesn't care for the _% characters.

I'm able to fix the issue by modifying the Utils::esc_like to use the native wpdb method if available. But this will break the associated test in SQLite mode. We would need to create a new test to run in that mode.

function esc_like( $text ) {
	global $wpdb;

	if ( method_exists( $wpdb, 'esc_like' ) ) {
		return $wpdb->esc_like( $text );
	}

	return addcslashes( $text, '_%\\' );
}

[swissspidy]

Let‘s try that 👍

[schlessera]

Or, since deletion and listing are failing, maybe the issue is that the insertion using set_transient() is failing in the first place.

Looking at the database after a wp transient set foo bar, you can see that it is properly added to wp_options table.

Also, if you try the same transient again, it correctly fails with an error because this particular transient was already set and conly be updated.

This means that the insertion succeeded, and the list fails because it does not see the existing transients.

[schlessera]

Fixed via wp-cli/wp-cli#6088

[bgrgicak]

Is there maybe an issue where you track overall SQLite compatibility?
I'm curious what other commands fail in WP-CLI.