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

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

272 lines 9.5 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	$transient = str_replace( '_site_transient_', '', $transient );
182	delete_site_transient( $transient );
183	if ( wp_using_ext_object_cache() ) { // make sure transients deleted from db
184	$option_timeout = '_site_transient_timeout_' . $transient;
185	$option = '_site_transient_' . $transient;
186	$result = delete_site_option( $option );
187
188	if ( $result ) {
189	delete_site_option( $option_timeout );
190	}
191	}
192	} else {
193	$transient = str_replace( '_transient_', '', $transient );
194	delete_transient( $transient );
195	if ( wp_using_ext_object_cache() ) { // make sure transients deleted from db
196	$option_timeout = '_transient_timeout_' . $transient;
197	$option = '_transient_' . $transient;
198	$result = delete_option( $option );
199
200	if ( $result ) {
201	delete_option( $option_timeout );
202	}
203	}
204	}
205	}
206	}
207	break;
208	case 'db_cleanup_optimize_tables':
209	$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 ) );
210	if ( $query ) {
211	foreach ( $query as $table ) {
212	$wpdb->query( "OPTIMIZE TABLE $table->table_name" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
213	}
214	}
215	break;
216	}
217
218	return false;
219	}
220
221	/**
222	* Sometimes canceling a process is glitchy
223	* Try to cancell all items in the queue up to $max_attempt
224	*/
225	public function cancel_process() {
226	$max_attempt = 5;
227	$cancelled = 0;
228	while ( ! parent::is_queue_empty() ) {
229	if ( $cancelled >= $max_attempt ) {
230	break;
231	}
232	parent::cancel_process();
233	$cancelled ++;
234	}
235	}
236
237	/**
238	* Complete
239	*
240	* Override if applicable, but ensure that the below actions are
241	* performed, or, call parent::complete().
242	*/
243	protected function complete() {
244	\PoweredCache\Utils\log( sprintf( 'Optimization completed...' ) );
245
246	parent::complete();
247
248	if ( POWERED_CACHE_IS_NETWORK ) {
249	delete_site_transient( DB_CLEANUP_COUNT_CACHE_KEY );
250	} else {
251	delete_transient( DB_CLEANUP_COUNT_CACHE_KEY );
252	}
253	}
254
255	/**
256	* Return an instance of the current class
257	*
258	* @return DatabaseOptimizer
259	* @since 2.0
260	*/
261	public static function factory() {
262
263	static $instance;
264
265	if ( ! $instance ) {
266	$instance = new self();
267	}
268
269	return $instance;
270	}
271	}
272