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 | } |
---|