powered-cache / includes / classes / Async / DatabaseOptimizer.php

DatabaseOptimizer.php in Powered Cache – Caching and Optimization for WordPress – Easily Improve PageSpeed & Web Vitals Score 3.7, at includes/classes/Async/DatabaseOptimizer.php

278 lines 9.7 KB

Raw Download Zip

1	<?php
2	/**
3	* Database optimization tasks
4	*
5	* @package PoweredCache\Async
6	*/
7
8	namespace PoweredCache\Async;
9
10	use const PoweredCache\Constants\DB_CLEANUP_COUNT_CACHE_KEY;
11	use \Powered_Cache_WP_Background_Process as Powered_Cache_WP_Background_Process;
12
13	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
14	// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
15
16
17	/**
18	* Class DatabaseOptimizer
19	*/
20	class DatabaseOptimizer extends Powered_Cache_WP_Background_Process {
21
22	/**
23	* string
24	*
25	* @var $action
26	*/
27	protected $action = 'powered_cache_db_optimizer';
28
29	/**
30	* Supported db optimization options
31	*
32	* @return array
33	*/
34	public function get_supported_options() {
35	return [
36	'db_cleanup_post_revisions',
37	'db_cleanup_auto_drafts',
38	'db_cleanup_trashed_posts',
39	'db_cleanup_spam_comments',
40	'db_cleanup_trashed_comments',
41	'db_cleanup_expired_transients',
42	'db_cleanup_all_transients',
43	'db_cleanup_optimize_tables',
44	];
45	}
46
47	/**
48	* Get counts for db entities
49	*
50	* @return array
51	*/
52	public static function get_db_cleanup_counts() {
53	global $wpdb;
54
55	if ( POWERED_CACHE_IS_NETWORK ) {
56	$count = get_site_transient( DB_CLEANUP_COUNT_CACHE_KEY );
57	} else {
58	$count = get_transient( DB_CLEANUP_COUNT_CACHE_KEY );
59	}
60
61	if ( false === $count ) {
62	$db_cleanup_post_revisions = 0;
63	$db_cleanup_auto_drafts = 0;
64	$db_cleanup_trashed_posts = 0;
65	$db_cleanup_spam_comments = 0;
66	$db_cleanup_trashed_comments = 0;
67	$db_cleanup_expired_transients = 0;
68	$db_cleanup_all_transients = 0;
69	$db_cleanup_optimize_tables = 0;
70
71	if ( POWERED_CACHE_IS_NETWORK ) {
72	$sites = get_sites();
73	foreach ( $sites as $site ) {
74	switch_to_blog( $site->blog_id );
75	$db_cleanup_post_revisions += $wpdb->get_var( "SELECT count(ID) FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_status = 'inherit'" );
76	$db_cleanup_auto_drafts += $wpdb->get_var( "SELECT count(ID) FROM $wpdb->posts WHERE post_status = 'auto-draft'" );
77	$db_cleanup_trashed_posts += $wpdb->get_var( "SELECT count(ID) FROM $wpdb->posts WHERE post_status = 'trash'" );
78	$db_cleanup_spam_comments += $wpdb->get_var( "SELECT count(comment_ID) FROM $wpdb->comments WHERE comment_approved = 'spam'" );
79	$db_cleanup_trashed_comments += $wpdb->get_var( "SELECT count(comment_ID) FROM $wpdb->comments WHERE (comment_approved = 'trash' OR comment_approved = 'post-trashed')" );
80	$db_cleanup_expired_transients += $wpdb->get_var( "SELECT count(option_name) FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_timeout\__%%' AND option_value < UNIX_TIMESTAMP()" );
81	$db_cleanup_all_transients += $wpdb->get_var( "SELECT count(option_name) FROM $wpdb->options WHERE option_name LIKE '%_transient_%'" );
82	$db_cleanup_optimize_tables += $wpdb->get_var( $wpdb->prepare( "SELECT count(*) FROM information_schema.tables WHERE table_schema = %s and Engine <> 'InnoDB' and data_free > 0", DB_NAME ) );
83	restore_current_blog();
84	}
85	} else {
86	$db_cleanup_post_revisions = $wpdb->get_var( "SELECT count(ID) FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_status = 'inherit'" );
87	$db_cleanup_auto_drafts = $wpdb->get_var( "SELECT count(ID) FROM $wpdb->posts WHERE post_status = 'auto-draft'" );
88	$db_cleanup_trashed_posts = $wpdb->get_var( "SELECT count(ID) FROM $wpdb->posts WHERE post_status = 'trash'" );
89	$db_cleanup_spam_comments = $wpdb->get_var( "SELECT count(comment_ID) FROM $wpdb->comments WHERE comment_approved = 'spam'" );
90	$db_cleanup_trashed_comments = $wpdb->get_var( "SELECT count(comment_ID) FROM $wpdb->comments WHERE (comment_approved = 'trash' OR comment_approved = 'post-trashed')" );
91	$db_cleanup_expired_transients = $wpdb->get_var( "SELECT count(option_name) FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_timeout\__%%' AND option_value < UNIX_TIMESTAMP()" );
92	$db_cleanup_all_transients = $wpdb->get_var( "SELECT count(option_name) FROM $wpdb->options WHERE option_name LIKE '%_transient_%'" );
93	$db_cleanup_optimize_tables = $wpdb->get_var( $wpdb->prepare( "SELECT count(*) FROM information_schema.tables WHERE table_schema = %s and Engine <> 'InnoDB' and data_free > 0", DB_NAME ) );
94	}
95
96	$count = [
97	'db_cleanup_post_revisions' => $db_cleanup_post_revisions,
98	'db_cleanup_auto_drafts' => $db_cleanup_auto_drafts,
99	'db_cleanup_trashed_posts' => $db_cleanup_trashed_posts,
100	'db_cleanup_spam_comments' => $db_cleanup_spam_comments,
101	'db_cleanup_trashed_comments' => $db_cleanup_trashed_comments,
102	'db_cleanup_expired_transients' => $db_cleanup_expired_transients,
103	'db_cleanup_all_transients' => $db_cleanup_all_transients,
104	'db_cleanup_optimize_tables' => $db_cleanup_optimize_tables,
105	];
106
107	if ( POWERED_CACHE_IS_NETWORK ) {
108	set_site_transient( DB_CLEANUP_COUNT_CACHE_KEY, $count, MINUTE_IN_SECONDS * 5 );
109	} else {
110	set_transient( DB_CLEANUP_COUNT_CACHE_KEY, $count, MINUTE_IN_SECONDS );
111	}
112	}
113
114	return $count;
115	}
116
117
118	/**
119	* Perform DB cleanup tasks.
120	*
121	* @param mixed $item Queue item to iterate over
122	*
123	* @return mixed
124	*/
125	protected function task( $item ) {
126	global $wpdb;
127
128	\PoweredCache\Utils\log( sprintf( 'Optimizing...%s', $item ) );
129
130	switch ( $item ) {
131	case 'db_cleanup_post_revisions':
132	$query = $wpdb->get_col( "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_status = 'inherit'" );
133	if ( $query ) {
134	foreach ( $query as $post_id ) {
135	wp_delete_post_revision( absint( $post_id ) );
136	}
137	}
138	break;
139	case 'db_cleanup_auto_drafts':
140	$query = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE post_status = 'auto-draft'" );
141	if ( $query ) {
142	foreach ( $query as $post_id ) {
143	wp_delete_post( absint( $post_id ) );
144	}
145	}
146	break;
147	case 'db_cleanup_trashed_posts':
148	$query = $wpdb->get_col( "SELECT ID FROM $wpdb->posts WHERE post_status = 'trash'" );
149	if ( $query ) {
150	foreach ( $query as $post_id ) {
151	wp_delete_post( absint( $post_id ) );
152	}
153	}
154	break;
155	case 'db_cleanup_spam_comments':
156	$query = $wpdb->get_col( "SELECT comment_ID FROM $wpdb->comments WHERE comment_approved = 'spam'" );
157	if ( $query ) {
158	foreach ( $query as $comment_id ) {
159	wp_delete_comment( absint( $comment_id ), true );
160	}
161	}
162	break;
163	case 'db_cleanup_trashed_comments':
164	$query = $wpdb->get_col( "SELECT comment_ID FROM $wpdb->comments WHERE (comment_approved = 'trash' OR comment_approved = 'post-trashed')" );
165	if ( $query ) {
166	foreach ( $query as $comment_id ) {
167	wp_delete_comment( absint( $comment_id ), true );
168	}
169	}
170	break;
171	case 'db_cleanup_expired_transients':
172	$query = $wpdb->get_col( "SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_timeout\__%%' AND option_value < UNIX_TIMESTAMP()" );
173	if ( $query ) {
174	foreach ( $query as $transient ) {
175	$key = str_replace( '_transient_timeout_', '', $transient );
176	delete_transient( $key );
177	}
178	}
179	break;
180	case 'db_cleanup_all_transients':
181	$query = $wpdb->get_col( "SELECT option_name FROM $wpdb->options WHERE option_name LIKE '%_transient_%'" );
182	if ( $query ) {
183	foreach ( $query as $transient ) {
184	if ( strpos( $transient, '_site_transient_' ) !== false ) {
185	$transient = str_replace( '_site_transient_', '', $transient );
186	delete_site_transient( $transient );
187	if ( wp_using_ext_object_cache() ) { // make sure transients deleted from db
188	$option_timeout = '_site_transient_timeout_' . $transient;
189	$option = '_site_transient_' . $transient;
190	$result = delete_site_option( $option );
191
192	if ( $result ) {
193	delete_site_option( $option_timeout );
194	}
195	}
196	} else {
197	$transient = str_replace( '_transient_', '', $transient );
198	delete_transient( $transient );
199	if ( wp_using_ext_object_cache() ) { // make sure transients deleted from db
200	$option_timeout = '_transient_timeout_' . $transient;
201	$option = '_transient_' . $transient;
202	$result = delete_option( $option );
203
204	if ( $result ) {
205	delete_option( $option_timeout );
206	}
207	}
208	}
209	}
210	}
211	break;
212	case 'db_cleanup_optimize_tables':
213	$query = $wpdb->get_results( $wpdb->prepare( "SELECT table_name, data_free FROM information_schema.tables WHERE table_schema = %s and Engine <> 'InnoDB' and data_free > 0", DB_NAME ) );
214	if ( $query ) {
215	foreach ( $query as $table ) {
216	if ( ! empty( $table->table_name ) ) {
217	$wpdb->query( 'OPTIMIZE TABLE `' . esc_sql( $table->table_name ) . '`' ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
218	}
219	}
220	}
221	break;
222	}
223
224	return false;
225	}
226
227	/**
228	* Sometimes canceling a process is glitchy
229	* Try to cancel all items in the queue up to $max_attempt
230	*/
231	public function cancel_process() {
232	$max_attempt = 5;
233	$cancelled = 0;
234	while ( ! parent::is_queue_empty() ) {
235	if ( $cancelled >= $max_attempt ) {
236	break;
237	}
238	parent::cancel();
239	$cancelled ++;
240	}
241	}
242
243	/**
244	* Complete
245	*
246	* Override if applicable, but ensure that the below actions are
247	* performed, or, call parent::complete().
248	*/
249	protected function complete() {
250	\PoweredCache\Utils\log( sprintf( 'Optimization completed...' ) );
251
252	parent::complete();
253
254	if ( POWERED_CACHE_IS_NETWORK ) {
255	delete_site_transient( DB_CLEANUP_COUNT_CACHE_KEY );
256	} else {
257	delete_transient( DB_CLEANUP_COUNT_CACHE_KEY );
258	}
259	}
260
261	/**
262	* Return an instance of the current class
263	*
264	* @return DatabaseOptimizer
265	* @since 2.0
266	*/
267	public static function factory() {
268
269	static $instance;
270
271	if ( ! $instance ) {
272	$instance = new self();
273	}
274
275	return $instance;
276	}
277	}
278