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

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

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