Open swissspidy opened 11 months ago
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.
@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.
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.
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, '_%\\' );
}
Let‘s try that 👍
When I manually run the tests locally with SQLite, the following scenarios are failing:
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.