Context Navigation

← Previous Revision
Next Revision →
Blame
Revision Log

db-optm.cls.php

Last change on this file was 3031822, checked in by LiteSpeedTech, 5 months ago
Release v6.1
File size: 9.6 KB

Line
1	<?php
2
3	/**
4	* The admin optimize tool
5	*
6	*
7	* @since 1.2.1
8	* @package LiteSpeed
9	* @subpackage LiteSpeed/src
10	* @author LiteSpeed Technologies <info@litespeedtech.com>
11	*/
12
13	namespace LiteSpeed;
14
15	defined('WPINC') \|\| exit();
16
17	class DB_Optm extends Root
18	{
19	private static $_hide_more = false;
20
21	private static $TYPES = array(
22	'revision',
23	'orphaned_post_meta',
24	'auto_draft',
25	'trash_post',
26	'spam_comment',
27	'trash_comment',
28	'trackback-pingback',
29	'expired_transient',
30	'all_transients',
31	'optimize_tables',
32	);
33	const TYPE_CONV_TB = 'conv_innodb';
34
35	/**
36	* Show if there are more sites in hidden
37	*
38	* @since 3.0
39	*/
40	public static function hide_more()
41	{
42	return self::$_hide_more;
43	}
44
45	/**
46	* Clean/Optimize WP tables
47	*
48	* @since 1.2.1
49	* @access public
50	* @param string $type The type to clean
51	* @param bool $ignore_multisite If ignore multisite check
52	* @return int The rows that will be affected
53	*/
54	public function db_count($type, $ignore_multisite = false)
55	{
56	if ($type === 'all') {
57	$num = 0;
58	foreach (self::$TYPES as $v) {
59	$num += $this->db_count($v);
60	}
61	return $num;
62	}
63
64	if (!$ignore_multisite) {
65	if (is_multisite() && is_network_admin()) {
66	$num = 0;
67	$blogs = Activation::get_network_ids();
68	foreach ($blogs as $k => $blog_id) {
69	if ($k > 3) {
70	self::$_hide_more = true;
71	break;
72	}
73
74	switch_to_blog($blog_id);
75	$num += $this->db_count($type, true);
76	restore_current_blog();
77	}
78	return $num;
79	}
80	}
81
82	global $wpdb;
83
84	switch ($type) {
85	case 'revision':
86	$rev_max = (int) $this->conf(Base::O_DB_OPTM_REVISIONS_MAX);
87	$rev_age = (int) $this->conf(Base::O_DB_OPTM_REVISIONS_AGE);
88	$sql_add = '';
89	if ($rev_age) {
90	$sql_add = " and post_modified < DATE_SUB( NOW(), INTERVAL $rev_age DAY ) ";
91	}
92	$sql = "SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_type = 'revision' $sql_add";
93	if (!$rev_max) {
94	return $wpdb->get_var($sql);
95	}
96	// Has count limit
97	$sql = "SELECT COUNT()-$rev_max FROM `$wpdb->posts` WHERE post_type = 'revision' $sql_add GROUP BY post_parent HAVING count()>$rev_max";
98	$res = $wpdb->get_results($sql, ARRAY_N);
99
100	Utility::compatibility();
101	return array_sum(array_column($res, 0));
102
103	case 'orphaned_post_meta':
104	return $wpdb->get_var("SELECT COUNT(*) FROM `$wpdb->postmeta` a LEFT JOIN `$wpdb->posts` b ON b.ID=a.post_id WHERE b.ID IS NULL");
105
106	case 'auto_draft':
107	return $wpdb->get_var("SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_status = 'auto-draft'");
108
109	case 'trash_post':
110	return $wpdb->get_var("SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_status = 'trash'");
111
112	case 'spam_comment':
113	return $wpdb->get_var("SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_approved = 'spam'");
114
115	case 'trash_comment':
116	return $wpdb->get_var("SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_approved = 'trash'");
117
118	case 'trackback-pingback':
119	return $wpdb->get_var("SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_type = 'trackback' OR comment_type = 'pingback'");
120
121	case 'expired_transient':
122	return $wpdb->get_var("SELECT COUNT(*) FROM `$wpdb->options` WHERE option_name LIKE '_transient_timeout%' AND option_value < " . time());
123
124	case 'all_transients':
125	return $wpdb->get_var("SELECT COUNT(*) FROM `$wpdb->options` WHERE option_name LIKE '%_transient_%'");
126
127	case 'optimize_tables':
128	return $wpdb->get_var("SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA = '" . DB_NAME . "' and ENGINE <> 'InnoDB' and DATA_FREE > 0");
129	}
130
131	return '-';
132	}
133
134	/**
135	* Clean/Optimize WP tables
136	*
137	* @since 1.2.1
138	* @since 3.0 changed to private
139	* @access private
140	*/
141	private function _db_clean($type)
142	{
143	if ($type === 'all') {
144	foreach (self::$TYPES as $v) {
145	$this->_db_clean($v);
146	}
147	return __('Clean all successfully.', 'litespeed-cache');
148	}
149
150	global $wpdb;
151	switch ($type) {
152	case 'revision':
153	$rev_max = (int) $this->conf(Base::O_DB_OPTM_REVISIONS_MAX);
154	$rev_age = (int) $this->conf(Base::O_DB_OPTM_REVISIONS_AGE);
155
156	$postmeta = "`$wpdb->postmeta`";
157	$posts = "`$wpdb->posts`";
158
159	$sql_postmeta_join = function ($table) use ($postmeta, $posts) {
160	return "
161	$postmeta
162	CROSS JOIN $table
163	ON $posts.ID = $postmeta.post_id
164	";
165	};
166
167	$sql_where = "WHERE $posts.post_type = 'revision'";
168
169	$sql_add = $rev_age ? "AND $posts.post_modified < DATE_SUB( NOW(), INTERVAL $rev_age DAY )" : '';
170
171	if (!$rev_max) {
172	$sql_where = "$sql_where $sql_add";
173	$sql_postmeta = $sql_postmeta_join($posts);
174	$wpdb->query("DELETE $postmeta FROM $sql_postmeta $sql_where");
175	$wpdb->query("DELETE FROM $posts $sql_where");
176	} else {
177	// Has count limit
178	$sql = "
179	SELECT COUNT(*) - $rev_max
180	AS del_max, post_parent
181	FROM $posts
182	WHERE post_type = 'revision'
183	$sql_add
184	GROUP BY post_parent
185	HAVING COUNT(*) > $rev_max
186	";
187	$res = $wpdb->get_results($sql);
188	$sql_where = "
189	$sql_where
190	AND post_parent = %d
191	ORDER BY ID
192	LIMIT %d
193	";
194	$sql_postmeta = $sql_postmeta_join("(SELECT ID FROM $posts $sql_where) AS $posts");
195	foreach ($res as $v) {
196	$args = array($v->post_parent, $v->del_max);
197	$sql = $wpdb->prepare("DELETE $postmeta FROM $sql_postmeta", $args);
198	$wpdb->query($sql);
199	$sql = $wpdb->prepare("DELETE FROM $posts $sql_where", $args);
200	$wpdb->query($sql);
201	}
202	}
203
204	return __('Clean post revisions successfully.', 'litespeed-cache');
205
206	case 'orphaned_post_meta':
207	$wpdb->query("DELETE a FROM `$wpdb->postmeta` a LEFT JOIN `$wpdb->posts` b ON b.ID=a.post_id WHERE b.ID IS NULL");
208	return __('Clean orphaned post meta successfully.', 'litespeed-cache');
209
210	case 'auto_draft':
211	$wpdb->query("DELETE FROM `$wpdb->posts` WHERE post_status = 'auto-draft'");
212	return __('Clean auto drafts successfully.', 'litespeed-cache');
213
214	case 'trash_post':
215	$wpdb->query("DELETE FROM `$wpdb->posts` WHERE post_status = 'trash'");
216	return __('Clean trashed posts and pages successfully.', 'litespeed-cache');
217
218	case 'spam_comment':
219	$wpdb->query("DELETE FROM `$wpdb->comments` WHERE comment_approved = 'spam'");
220	return __('Clean spam comments successfully.', 'litespeed-cache');
221
222	case 'trash_comment':
223	$wpdb->query("DELETE FROM `$wpdb->comments` WHERE comment_approved = 'trash'");
224	return __('Clean trashed comments successfully.', 'litespeed-cache');
225
226	case 'trackback-pingback':
227	$wpdb->query("DELETE FROM `$wpdb->comments` WHERE comment_type = 'trackback' OR comment_type = 'pingback'");
228	return __('Clean trackbacks and pingbacks successfully.', 'litespeed-cache');
229
230	case 'expired_transient':
231	$wpdb->query("DELETE FROM `$wpdb->options` WHERE option_name LIKE '_transient_timeout%' AND option_value < " . time());
232	return __('Clean expired transients successfully.', 'litespeed-cache');
233
234	case 'all_transients':
235	$wpdb->query("DELETE FROM `$wpdb->options` WHERE option_name LIKE '%\\_transient\\_%'");
236	return __('Clean all transients successfully.', 'litespeed-cache');
237
238	case 'optimize_tables':
239	$sql = "SELECT table_name, DATA_FREE FROM information_schema.tables WHERE TABLE_SCHEMA = '" . DB_NAME . "' and ENGINE <> 'InnoDB' and DATA_FREE > 0";
240	$result = $wpdb->get_results($sql);
241	if ($result) {
242	foreach ($result as $row) {
243	$wpdb->query('OPTIMIZE TABLE ' . $row->table_name);
244	}
245	}
246	return __('Optimized all tables.', 'litespeed-cache');
247	}
248	}
249
250	/**
251	* Get all myisam tables
252	*
253	* @since 3.0
254	* @access public
255	*/
256	public function list_myisam()
257	{
258	global $wpdb;
259	$q = "SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = '" . DB_NAME . "' and ENGINE = 'myisam' AND TABLE_NAME LIKE '{$wpdb->prefix}%'";
260	return $wpdb->get_results($q);
261	}
262
263	/**
264	* Convert tables to InnoDB
265	*
266	* @since 3.0
267	* @access private
268	*/
269	private function _conv_innodb()
270	{
271	global $wpdb;
272
273	if (empty($_GET['tb'])) {
274	Admin_Display::error('No table to convert');
275	return;
276	}
277
278	$tb = false;
279
280	$list = $this->list_myisam();
281	foreach ($list as $v) {
282	if ($v->TABLE_NAME == $_GET['tb']) {
283	$tb = $v->TABLE_NAME;
284	break;
285	}
286	}
287
288	if (!$tb) {
289	Admin_Display::error('No existing table');
290	return;
291	}
292
293	$q = 'ALTER TABLE ' . DB_NAME . '.' . $tb . ' ENGINE = InnoDB';
294	$wpdb->query($q);
295
296	Debug2::debug("[DB] Converted $tb to InnoDB");
297
298	$msg = __('Converted to InnoDB successfully.', 'litespeed-cache');
299	Admin_Display::succeed($msg);
300	}
301
302	/**
303	* Count all autoload size
304	*
305	* @since 3.0
306	* @access public
307	*/
308	public function autoload_summary()
309	{
310	global $wpdb;
311
312	$summary = $wpdb->get_row("SELECT SUM(LENGTH(option_value)) AS autoload_size,COUNT(*) AS autload_entries FROM `$wpdb->options` WHERE autoload='yes'");
313
314	$summary->autoload_toplist = $wpdb->get_results(
315	"SELECT option_name, LENGTH(option_value) AS option_value_length FROM `$wpdb->options` WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 20"
316	);
317
318	return $summary;
319	}
320
321	/**
322	* Handle all request actions from main cls
323	*
324	* @since 3.0
325	* @access public
326	*/
327	public function handler()
328	{
329	$type = Router::verify_type();
330
331	switch ($type) {
332	case 'all':
333	case in_array($type, self::$TYPES):
334	if (is_multisite() && is_network_admin()) {
335	$blogs = Activation::get_network_ids();
336	foreach ($blogs as $blog_id) {
337	switch_to_blog($blog_id);
338	$msg = $this->_db_clean($type);
339	restore_current_blog();
340	}
341	} else {
342	$msg = $this->_db_clean($type);
343	}
344	Admin_Display::succeed($msg);
345	break;
346
347	case self::TYPE_CONV_TB:
348	$this->_conv_innodb();
349	break;
350
351	default:
352	break;
353	}
354
355	Admin::redirect();
356	}
357	}

Note: See TracBrowser for help on using the repository browser.

Trac UI Preferences

Plugin Directory

Context Navigation

source: litespeed-cache/trunk/src/db-optm.cls.php

Download in other formats: