Improve detection of missing AS tables

[piotrbak]

Before submitting an issue please check that you’ve completed the following steps:

• Made sure you’re on the latest version
• Used the search feature to ensure that the bug hasn’t been reported before

Describe the bug
In the current approach we have a bug related to the not expected characters in the database name. Our query here is not using `` causing fail of the check and displaying false message:

wp-rocket/inc/Engine/Preload/Activation/Activation.php

Line 117 in ba69c93

$wpdb->prepare( 'SHOW TABLES FROM ' . DB_NAME . ' WHERE Tables_in_' . DB_NAME . ' LIKE %s AND Tables_in_' . DB_NAME . ' REGEXP ' . $exp, '%actionscheduler%' )

Expected behavior
We should check the existence of the tables only in the specific conditions:

1. When activating the plugin, before Preload do any operation on the tables
2. When enabling RUCSS
3. When enabling Preload
4. During each update of the plugin

Backlog Grooming (for WP Media dev team use only)

• Reproduce the problem
• Identify the root cause
• Scope a solution
• Estimate the effort

[piotrbak]

This will resolve the following issue:
#5426

[Tabrisrp]

Are we removing the existing notice?

[piotrbak]

Yes, the notice won't be needed anymore.

[jeawhanlee]

Scope a solution ✅

I propose creating a new class and subscriber in https://github.com/wp-media/wp-rocket/tree/develop/inc/Engine/Admin

use ActionScheduler_StoreSchema;
use ActionScheduler_LoggerSchema;

public function maybe_recreate_as_tables() {
	if ( is_valid_as_tables() ) {
           return;
        }

	$store_schema  = new ActionScheduler_StoreSchema();
	$logger_schema = new ActionScheduler_LoggerSchema();
	$store_schema->register_tables( true );
	$logger_schema->register_tables( true );
}

private function is_valid_as_tables() {
        $cached_count = get_transient( 'rocket_rucss_as_tables_count' );
	if ( false !== $cached_count && ! is_admin() ) { // Stop caching in admin UI.
		return 4 === (int) $cached_count;
	}

	global $wpdb;

	$exp = "'^" . $wpdb->prefix . "actionscheduler_(logs|actions|groups|claims)$'";
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	$found_as_tables = $wpdb->get_col(
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->prepare( 'SHOW TABLES FROM `' . DB_NAME . '` WHERE `Tables_in_' . DB_NAME . '` LIKE %s AND `Tables_in_' . DB_NAME . '` REGEXP ' . $exp, '%actionscheduler%' )
	);

	set_transient( 'rocket_rucss_as_tables_count', count( $found_as_tables ), rocket_get_constant( 'DAY_IN_SECONDS', 24 * 60 * 60 ) );

	return 4 === count( $found_as_tables );
}

When activating the plugin, before Preload do any operation on the tables

In the new subscriber hook a new method to rocket_activation that calls maybe_recreate_as_tables()

• remove

  wp-rocket/inc/Engine/Preload/Activation/Activation.php

  Lines 9 to 10 in 3584bcc

  	use ActionScheduler_StoreSchema;
  	use ActionScheduler_LoggerSchema;

  and

  wp-rocket/inc/Engine/Preload/Activation/Activation.php

  Lines 110 to 121 in 3584bcc

  	public function is_valid_as_tables() {
  	global $wpdb;
  	$exp = "'^" . $wpdb->prefix . "actionscheduler_(logs|actions|groups|claims)$'";
  	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
  	$found_as_tables = $wpdb->get_col(
  	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
  	$wpdb->prepare( 'SHOW TABLES FROM ' . DB_NAME . ' WHERE Tables_in_' . DB_NAME . ' LIKE %s AND Tables_in_' . DB_NAME . ' REGEXP ' . $exp, '%actionscheduler%' )
  	);
  	return 4 === count( $found_as_tables );
  	}

When enabling RUCSS

in new subscriber hook a new method to
$slug = rocket_get_constant( 'WP_ROCKET_SLUG', 'wp_rocket_settings' );
'update_option_' . $slug and check if RUCSS is enabled then call maybe_recreate_as_tables()

When enabling Preload

in new subscriber hook a new method to
$slug = rocket_get_constant( 'WP_ROCKET_SLUG', 'wp_rocket_settings' );
'update_option_' . $slug and check if preload is enabled then call maybe_recreate_as_tables()

During each update of the plugin

in new subscriber hook a new method to wp_rocket_upgrade that calls maybe_recreate_as_tables()

• Remove https://github.com/wp-media/wp-rocket/blob/develop/inc/Engine/Admin/Notices.php and https://github.com/wp-media/wp-rocket/blob/develop/inc/Engine/Admin/NoticesSubscriber.php as we don't want to display the notice anymore.
• Update https://github.com/wp-media/wp-rocket/blob/develop/inc/Engine/Admin/ServiceProvider.php and https://github.com/wp-media/wp-rocket/blob/develop/inc/Plugin.php
• Remove this orphan method here also, something I skipped again.

  wp-rocket/inc/Engine/Optimization/RUCSS/Admin/Settings.php

  Lines 306 to 323 in 3584bcc

  	public function display_as_missed_tables_notice() {
  	$as_tools_link = menu_page_url( 'action-scheduler', false );
  	$message = sprintf(
  	// translators: %1$s = plugin name, %2$s = opening anchor tag, %3$s = closing anchor tag.
  	__( '%1$s: We detected missing database table related to Action Scheduler. Please visit the following %2$sURL%3$s to recreate it, as it is needed for WP Rocket to work correctly.', 'rocket' ),
  	'<strong>WP Rocket</strong>',
  	'<a href="' . $as_tools_link . '">',
  	'</a>'
  	);
  	rocket_notice_html(
  	[
  	'status' => 'error',
  	'message' => $message,
  	'id' => 'rocket-notice-as-missed-tables',
  	]
  	);
  	}

• Remove related tests
• Add new tests

Estimate the effort ✅

[S]

Can you validate this grooming @Tabrisrp 🙏

[Tabrisrp]

that sounds good to me 👍🏼

[camilamadronero-zz]

Related https://secure.helpscout.net/conversation/2013558411/369507/

[worldwildweb]

related: https://secure.helpscout.net/conversation/2016408467/370146

[vmanthos]

related: https://secure.helpscout.net/conversation/2017851035/370501?folderId=2135277