'Old post revisions', 'autodrafts' => 'Auto-drafts (older than 7 days)', 'spam' => 'Comments: spam and trash', 'transients' => 'Expired transients', 'orphans' => 'Orphaned metadata', 'trash' => 'Trashed posts', 'optimize' => 'Table optimization', ); } public static function selected_tasks() { $out = array(); foreach ( array_keys( self::tasks() ) as $t ) { if ( RavenCache_Settings::get( 'db_t_' . $t ) ) { $out[] = $t; } } return $out; } public static function reschedule() { wp_clear_scheduled_hook( self::HOOK ); $sch = RavenCache_Settings::get( 'db_schedule' ); if ( in_array( $sch, array( 'weekly', 'monthly' ), true ) ) { $ts = strtotime( 'tomorrow 03:30' ); wp_schedule_event( $ts ? $ts : time() + DAY_IN_SECONDS, 'weekly' === $sch ? 'weekly' : 'ravencache_monthly', self::HOOK ); } } public static function scheduled() { self::run( self::selected_tasks() ); } public static function counts() { global $wpdb; $c = array(); $c['revisions'] = (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_type='revision'" ); $c['autodrafts'] = (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_status='auto-draft'" ); $c['spam'] = (int) $wpdb->get_var( "SELECT COUNT(comment_ID) FROM {$wpdb->comments} WHERE comment_approved IN ('spam','trash')" ); $c['transients'] = (int) $wpdb->get_var( "SELECT COUNT(option_id) FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP()" ); $c['orphans'] = (int) $wpdb->get_var( "SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" ) + (int) $wpdb->get_var( "SELECT COUNT(cm.meta_id) FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL" ); $c['trash'] = (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_status='trash'" ); $c['optimize'] = count( $wpdb->tables() ); return $c; } public static function run( array $tasks ) { global $wpdb; @set_time_limit( 120 ); // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- optimizing large tables can exceed the default PHP time limit. $res = array(); $cap = 5000; if ( in_array( 'revisions', $tasks, true ) ) { $keep = max( 0, (int) RavenCache_Settings::get( 'db_keep_revisions' ) ); $deleted = 0; $parents = $wpdb->get_col( $wpdb->prepare( "SELECT post_parent FROM {$wpdb->posts} WHERE post_type='revision' GROUP BY post_parent HAVING COUNT(ID) > %d LIMIT 2000", $keep ) ); foreach ( $parents as $parent ) { $ids = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_type='revision' AND post_parent=%d ORDER BY post_date_gmt DESC, ID DESC LIMIT 100000 OFFSET %d", $parent, $keep ) ); foreach ( $ids as $id ) { wp_delete_post_revision( (int) $id ); $deleted++; if ( $deleted >= $cap ) { break 2; } } } $res['revisions'] = $deleted; } if ( in_array( 'autodrafts', $tasks, true ) ) { $ids = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_status='auto-draft' AND post_date < %s LIMIT %d", gmdate( 'Y-m-d H:i:s', time() - 7 * DAY_IN_SECONDS ), $cap ) ); foreach ( $ids as $id ) { wp_delete_post( (int) $id, true ); } $res['autodrafts'] = count( $ids ); } if ( in_array( 'spam', $tasks, true ) ) { $ids = $wpdb->get_col( $wpdb->prepare( "SELECT comment_ID FROM {$wpdb->comments} WHERE comment_approved IN ('spam','trash') LIMIT %d", $cap ) ); foreach ( $ids as $id ) { wp_delete_comment( (int) $id, true ); } $res['spam'] = count( $ids ); } if ( in_array( 'transients', $tasks, true ) ) { delete_expired_transients( true ); $res['transients'] = 'ok'; } if ( in_array( 'orphans', $tasks, true ) ) { $a = $wpdb->query( "DELETE pm FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" ); $b = $wpdb->query( "DELETE cm FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL" ); $res['orphans'] = (int) $a + (int) $b; } if ( in_array( 'trash', $tasks, true ) ) { $ids = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_status='trash' LIMIT %d", $cap ) ); foreach ( $ids as $id ) { wp_delete_post( (int) $id, true ); } $res['trash'] = count( $ids ); } if ( in_array( 'optimize', $tasks, true ) ) { $n = 0; foreach ( $wpdb->tables() as $table ) { if ( false !== $wpdb->query( "OPTIMIZE TABLE `{$table}`" ) ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table names come from $wpdb->tables(); identifiers cannot use placeholders before WordPress 6.2. $n++; } } $res['optimize'] = $n; } return $res; } /** Raport największych opcji autoload. */ public static function autoload_report( $limit = 15 ) { global $wpdb; $values = function_exists( 'wp_autoload_values_to_autoload' ) ? wp_autoload_values_to_autoload() : array( 'yes' ); $values = array_values( array_map( 'strval', $values ) ); $marks = implode( ',', array_fill( 0, count( $values ), '%s' ) ); // $marks only contains %s placeholders (one per value), so the query stays fully prepared. // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT SUM(LENGTH(option_value)) FROM {$wpdb->options} WHERE autoload IN ({$marks})", $values ) ); $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(option_id) FROM {$wpdb->options} WHERE autoload IN ({$marks})", $values ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT option_name, LENGTH(option_value) AS size FROM {$wpdb->options} WHERE autoload IN ({$marks}) ORDER BY size DESC LIMIT %d", array_merge( $values, array( (int) $limit ) ) ), ARRAY_A ); // phpcs:enable return array( 'total' => $total, 'count' => $count, 'rows' => $rows ); } public static function option_is_protected( $name ) { global $wpdb; $exact = array( 'siteurl', 'home', 'blogname', 'blogdescription', 'admin_email', 'active_plugins', 'template', 'stylesheet', 'rewrite_rules', 'permalink_structure', 'sidebars_widgets', 'cron', 'WPLANG', 'users_can_register', 'default_role', 'timezone_string', 'gmt_offset', 'uninstall_plugins', 'recently_activated', 'fresh_site', 'db_version', 'initial_db_version', 'show_on_front', 'page_on_front', 'page_for_posts', 'upload_path', 'upload_url_path', RavenCache_Settings::OPTION, $wpdb->prefix . 'user_roles' ); return in_array( $name, $exact, true ) || 0 === strpos( $name, 'theme_mods_' ) || 0 === strpos( $name, 'widget_' ) || 0 === strpos( $name, '_site_transient' ) || 0 === strpos( $name, 'woocommerce_' ); } } add_filter( 'cron_schedules', function ( $s ) { $s['ravencache_monthly'] = array( 'interval' => 30 * DAY_IN_SECONDS, 'display' => 'Once a month' ); return $s; } );