rvg-optimize-database / classes / odb-cleaner.php

odb-cleaner.php in Optimize Database after Deleting Revisions 4.0.1, at classes/odb-cleaner.php

1,073 lines 34.4 KB

Raw Download Zip

1	<?php
2	/************************************************************************************************
3	*
4	* CLEANER CLASS: DOING THE REAL CLEANING / OPTIMIZATION
5	*
6	************************************************************************************************/
7	?>
8	<?php
9	class ODB_Cleaner
10	{
11	var $start_size;
12	var $nr_of_optimized_tables;
13
14
15	/********************************************************************************************
16	* CONSTRUCTOR
17	********************************************************************************************/
18	function __construct()
19	{
20	} // __construct()
21
22
23	/********************************************************************************************
24	* RUN CLEANER
25	********************************************************************************************/
26	function odb_run_cleaner($scheduler)
27	{
28	global $odb_class;
29
30	if(!$scheduler)
31	{
32	echo '
33	<div id="odb-cleaner" class="odb-padding-left">
34	<div class="odb-title-bar">
35	<h2>'.__('Cleaning Database', $odb_class->odb_txt_domain).'</h2>
36	</div>
37	<br>
38	<br>
39	';
40	}
41
42	// GET THE SIZE OF THE DATABASE BEFORE OPTIMIZATION
43	$this->start_size = $odb_class->odb_utilities_obj->odb_get_db_size();
44
45	// TIMESTAMP FOR LOG FILE
46	$ct = ($scheduler) ? ' (cron)' : '';
47	$current_datetime = Date('m/d/YH:i:s');
48	$odb_class->log_arr = array("time" => substr($current_datetime, 0, 10).'<br>'.substr($current_datetime,10).$ct);
49
50	$odb_class->log_arr["after"] = 0;
51	$odb_class->log_arr["before"] = 0;
52	$odb_class->log_arr["orphans"] = 0;
53	$odb_class->log_arr["pingbacks"] = 0;
54	$odb_class->log_arr["revisions"] = 0;
55	$odb_class->log_arr["savings"] = 0;
56	$odb_class->log_arr["spam"] = 0;
57	$odb_class->log_arr["tables"] = 0;
58	$odb_class->log_arr["tags"] = 0;
59	$odb_class->log_arr["transients"] = 0;
60	$odb_class->log_arr["trash"] = 0;
61
62	/****************************************************************************************
63	* DELETE REVISIONS
64	****************************************************************************************/
65
66	// FIND REVISIONS
67	$results = $this->odb_get_revisions();
68
69	$total_deleted = 0;
70	if(count($results)>0)
71	{ // WE HAVE REVISIONS TO DELETE!
72	if(!$scheduler)
73	{
74	?>
75	<table border="0" cellspacing="8" cellpadding="2" class="odb-result-table">
76	<tr>
77	<td colspan="4"><div class="odb-found">
78	<?php _e('DELETED REVISIONS','rvg-optimize-database');?>
79	</div></td>
80	</tr>
81	<tr>
82	<th align="right" class="odb-border-bottom">#</th>
83	<th align="left" class="odb-border-bottom"><?php _e('prefix', $odb_class->odb_txt_domain);?></th>
84	<th align="left" class="odb-border-bottom"><?php _e('post / page', $odb_class->odb_txt_domain);?></th>
85	<th align="left" class="odb-border-bottom"><?php _e('revision date', $odb_class->odb_txt_domain);?></th>
86	<th align="right" class="odb-border-bottom"><?php _e('revisions deleted', $odb_class->odb_txt_domain);?></th>
87	</tr>
88	<?php
89	} // if(!$scheduler)
90
91	// LOOP THROUGH THE REVISIONS AND DELETE THEM
92	$total_deleted = $this->odb_delete_revisions($results, $scheduler);
93
94	if(!$scheduler)
95	{
96	?>
97	<tr>
98	<td colspan="4" align="right" class="odb-border-top odb-bold"><?php _e('total number of revisions deleted', $odb_class->odb_txt_domain);?></td>
99	<td align="right" class="odb-border-top odb-bold"><?php echo $total_deleted?></td>
100	</tr>
101	</table>
102	<?php
103	} // if(!$scheduler)
104	}
105	else
106	{ if(!$scheduler)
107	{
108	?>
109	<div class="odb-not-found">
110	<?php _e('No REVISIONS found to delete', $odb_class->odb_txt_domain);?>
111	</div>
112	<?php
113	} // if(!$scheduler)
114	} // if(count($results)>0)
115
116	// NUMBER OF DELETED REVISIONS FOR LOG FILE
117	$odb_class->log_arr["revisions"] = $total_deleted;
118
119
120	/****************************************************************************************
121	* DELETE TRASHED ITEMS
122	****************************************************************************************/
123	if($odb_class->odb_rvg_options['clear_trash'] == 'Y')
124	{
125	// GET TRASHED POSTS / PAGES AND COMMENTS
126	$results = $this->odb_get_trash();
127
128	$total_deleted = 0;
129	if(count($results)>0)
130	{ // WE HAVE TRASH TO DELETE!
131	if(!$scheduler)
132	{
133	?>
134	<table border="0" cellspacing="8" cellpadding="2" class="odb-result-table">
135	<tr>
136	<td colspan="4"><div class="odb-found">
137	<?php _e('DELETED TRASHED ITEMS', $odb_class->odb_txt_domain);?>
138	</div></td>
139	</tr>
140	<tr>
141	<th align="right" class="odb-border-bottom">#</th>
142	<th align="left" class="odb-border-bottom"><?php _e('prefix', $odb_class->odb_txt_domain);?></th>
143	<th align="left" class="odb-border-bottom"><?php _e('type', $odb_class->odb_txt_domain);?></th>
144	<th align="left" class="odb-border-bottom"><?php _e('IP address / title', $odb_class->odb_txt_domain);?></th>
145	<th align="left" nowrap="nowrap" class="odb-border-bottom"><?php _e('date', $odb_class->odb_txt_domain);?></th>
146	</tr>
147	<?php
148	} // if(!$scheduler)
149
150	// LOOP THROUGH THE TRASHED ITEMS AND DELETE THEM
151	$total_deleted = $this->odb_delete_trash($results, $scheduler);
152
153	if(!$scheduler)
154	{
155	?>
156	<tr>
157	<td colspan="4" align="right" class="odb-border-top odb-bold"><?php _e('total number of trashed items deleted', $odb_class->odb_txt_domain);?></td>
158	<td align="right" class="odb-border-top odb-bold"><?php echo $total_deleted?></td>
159	</tr>
160	</table>
161	<?php
162	} // if(!$scheduler)
163	}
164	else
165	{
166	if(!$scheduler)
167	{
168	?>
169	<div class="odb-not-found">
170	<?php _e('No TRASHED ITEMS found to delete', $odb_class->odb_txt_domain);?>
171	</div>
172	<?php
173	} // if(!$scheduler)
174	} // if(count($results)>0)
175
176	// NUMBER OF DELETED TRASH FOR LOG FILE
177	$odb_class->log_arr["trash"] = $total_deleted;
178	} // if($odb_class->odb_rvg_options['clear_trash'] == 'Y')
179
180
181	/****************************************************************************************
182	* DELETE SPAMMED ITEMS
183	****************************************************************************************/
184	if($odb_class->odb_rvg_options['clear_spam'] == 'Y')
185	{
186	// GET SPAMMED COMMENTS
187	$results = $this->odb_get_spam();
188
189	$total_deleted = 0;
190	if(count($results)>0)
191	{ // WE HAVE SPAM TO DELETE!
192	if (!$scheduler)
193	{
194	?>
195	<table border="0" cellspacing="8" cellpadding="2" class="odb-result-table">
196	<tr>
197	<td colspan="4"><div class="odb-found">
198	<?php _e('DELETEED SPAMMED ITEMS', $odb_class->odb_txt_domain);?>
199	</div></td>
200	</tr>
201	<tr>
202	<th align="right" class="odb-border-bottom">#</th>
203	<th align="left" class="odb-border-bottom"><?php _e('prefix', $odb_class->odb_txt_domain);?></th>
204	<th align="left" class="odb-border-bottom"><?php _e('comment author', $odb_class->odb_txt_domain);?></th>
205	<th align="left" class="odb-border-bottom"><?php _e('comment author email', $odb_class->odb_txt_domain);?></th>
206	<th align="left" nowrap="nowrap" class="odb-border-bottom"><?php _e('comment date', $odb_class->odb_txt_domain);?></th>
207	</tr>
208	<?php
209	} // if (!$scheduler)
210
211	// LOOP THROUGH SPAMMED ITEMS AND DELETE THEM
212	$total_deleted = $this->odb_delete_spam($results, $scheduler);
213
214	if (!$scheduler)
215	{
216	?>
217	<tr>
218	<td colspan="4" align="right" class="odb-border-top odb-bold"><?php _e('total number of spammed items deleted', $odb_class->odb_txt_domain);?></td>
219	<td align="right" class="odb-border-top odb-bold"><?php echo $total_deleted?></td>
220	</tr>
221	</table>
222	<?php
223	} // if (!$scheduler)
224	}
225	else
226	{
227	if (!$scheduler)
228	{
229	?>
230	<div class="odb-not-found">
231	<?php _e('No SPAMMED ITEMS found to delete', $odb_class->odb_txt_domain);?>
232	</div>
233	<?php
234	} // if (!$scheduler)
235	} // if(count($results)>0)
236
237	} // if($odb_class->odb_rvg_options['clear_spam'] == 'Y')
238
239	// NUMBER OF SPAM DELETED FOR LOG FILE
240	$odb_class->log_arr["spam"] = $total_deleted;
241
242
243	/****************************************************************************************
244	* DELETE UNUSED TAGS
245	****************************************************************************************/
246	if($odb_class->odb_rvg_options['clear_tags'] == 'Y')
247	{
248	// DELETE UNUSED TAGS
249	$total_deleted = $this->odb_delete_tags();
250	if($total_deleted>0)
251	{ // TAGS DELETED
252	if (!$scheduler)
253	{
254	?>
255	<div class="odb-found-number">
256	<?php _e('NUMBER OF UNUSED TAGS DELETED', $odb_class->odb_txt_domain);?>: <span class="odb-blue"><?php echo $total_deleted;?></span> </div>
257	<?php
258	} // if (!$scheduler)
259	}
260	else
261	{
262	if (!$scheduler)
263	{
264	?>
265	<div class="odb-not-found">
266	<?php _e('No UNUSED TAGS found to delete', $odb_class->odb_txt_domain);?>
267	</div>
268	<?php
269	} // if (!$scheduler)
270	} // if(count($results)>0)
271	} // if($odb_class->odb_rvg_options['clear_tags'] == 'Y')
272
273	// NUMBER OF tags DELETED FOR LOG FILE
274	$odb_class->log_arr["tags"] = $total_deleted;
275
276
277	/****************************************************************************************
278	* DELETE EXPIRED TRANSIENTS
279	****************************************************************************************/
280	if($odb_class->odb_rvg_options['clear_transients'] == 'Y')
281	{
282	// DELETE UNUSED TAGS
283	$total_deleted = $this->odb_delete_transients();
284	if($total_deleted>0)
285	{ // TRANSIENTS DELETED
286	if (!$scheduler)
287	{
288	?>
289	<div class="odb-found-number">
290	<?php _e('NUMBER OF EXPIRED TRANSIENTS DELETED', $odb_class->odb_txt_domain);?>: <span class="odb-blue"><?php echo $total_deleted;?></span> </div>
291	<?php
292	} // if (!$scheduler)
293	}
294	else
295	{
296	if (!$scheduler)
297	{
298	?>
299	<div class="odb-not-found">
300	<?php _e('No EXPIRED TRANSIENTS found to delete', $odb_class->odb_txt_domain);?>
301	</div>
302	<?php
303	} // if (!$scheduler)
304	} // if(count($results)>0)
305	} // if($odb_class->odb_rvg_options['clear_transients'] == 'Y')
306
307	// NUMBER OF transients DELETED FOR LOG FILE
308	$odb_class->log_arr["transients"] = $total_deleted;
309
310
311	/****************************************************************************************
312	* DELETE PINGBACKS AND TRACKBACKS
313	****************************************************************************************/
314	if($odb_class->odb_rvg_options['clear_pingbacks'] == 'Y')
315	{
316	// DELETE UNUSED TAGS
317	$total_deleted = $this->odb_delete_pingbacks();
318	if($total_deleted>0)
319	{ // PINGBACKS / TRACKBACKS DELETED\
320	if (!$scheduler)
321	{
322	?>
323	<div class="odb-found-number">
324	<?php _e('NUMBER OF PINGBACKS AND TRACKBACKS DELETED', $odb_class->odb_txt_domain);?>: <span class="odb-blue"><?php echo $total_deleted;?></span> </div>
325	<?php
326	} // if (!$scheduler)
327	}
328	else
329	{
330	if (!$scheduler)
331	{
332	?>
333	<div class="odb-not-found">
334	<?php _e('No PINGBACKS nor TRACKBACKS found to delete', $odb_class->odb_txt_domain);?>
335	</div>
336	<?php
337	} // if (!$scheduler)
338	} // if(count($results)>0)
339	} // if($odb_class->odb_rvg_options['clear_pingbacks'] == 'Y')
340
341	// NUMBER OF pingbacks / trackbacks DELETED (FOR LOG FILE)
342	$odb_class->log_arr["pingbacks"] = $total_deleted;
343
344
345	/****************************************************************************************
346	* DELETE ORPHANS
347	****************************************************************************************/
348	$total_deleted = $this->odb_delete_orphans();
349	if($total_deleted > 0)
350	{
351	if (!$scheduler)
352	{
353	?>
354	<div class="odb-found-number">
355	<?php _e('NUMBER OF POSTMETA ORPHANS DELETED', $odb_class->odb_txt_domain);?>: <span class="odb-blue"><?php echo $total_deleted;?></span> </div>
356	<?php
357	} // if (!$scheduler)
358	}
359	else
360	{
361	if (!$scheduler)
362	{
363	?>
364	<div class="odb-not-found">
365	<?php _e('No POSTMETA ORPHANS found to delete', $odb_class->odb_txt_domain);?>
366	</div>
367	<?php
368	} // if (!$scheduler)
369	} // if($total_deleted > 0)
370	// FOR LOG FILE
371	$odb_class->log_arr["orphans"] = $total_deleted;
372
373	if (!$scheduler)
374	{
375	?>
376	</div><!-- /odb-cleaner -->
377	<?php
378	}
379	} // odb_run_cleaner()
380
381
382	/********************************************************************************************
383	* RUN OPTIMIZER
384	********************************************************************************************/
385	function odb_run_optimizer($scheduler)
386	{ global $odb_class;
387
388	if(!$scheduler)
389	{
390	?>
391	<div class="odb-optimizing-table" class="odb-padding-left">
392	<div class="odb-title-bar">
393	<h2><?php _e('Optimizing Database Tables', $odb_class->odb_txt_domain);?></h2>
394	</div>
395	<br>
396	<br>
397	<table border="0" cellspacing="8" cellpadding="2">
398	<tr>
399	<th class="odb-border-bottom" align="right">#</th>
400	<th class="odb-border-bottom" align="left"><?php _e('table name', $odb_class->odb_txt_domain);?></th>
401	<th class="odb-border-bottom" align="left"><?php _e('optimization result', $odb_class->odb_txt_domain);?></th>
402	<th class="odb-border-bottom" align="left"><?php _e('engine', $odb_class->odb_txt_domain);?></th>
403	<th class="odb-border-bottom" align="right"><?php _e('table rows', $odb_class->odb_txt_domain);?></th>
404	<th class="odb-border-bottom" align="right"><?php _e('table size', $odb_class->odb_txt_domain);?></th>
405	</tr>
406	<?php
407	} // if(!$scheduler)
408
409	# OPTIMIZE THE DATABASE TABLES
410	$this->nr_of_optimized_tables = $this->odb_optimize_tables($scheduler);
411
412	if(!$scheduler)
413	{
414	?>
415	</table>
416	</div><!-- /odb-optimizing-table -->
417	<?php
418	} // if(!$scheduler)
419	} // odb_run_optimizer()
420
421
422	/********************************************************************************************
423	* CALCULATE AND DISPLAY SAVINGS
424	********************************************************************************************/
425	function odb_savings($scheduler)
426	{ global $odb_class;
427	global $odb_logger_obj;
428
429	// NUMBER OF TABLES
430	$odb_class->log_arr["tables"] = $this->nr_of_optimized_tables;
431	// DATABASE SIZE BEFORE OPTIMIZATION
432	$odb_class->log_arr["before"] = $odb_class->odb_utilities_obj->odb_format_size($this->start_size,3);
433	// DATABASE SIZE AFTER OPTIMIZATION
434	$end_size = $odb_class->odb_utilities_obj->odb_get_db_size();
435	$odb_class->log_arr["after"] = $odb_class->odb_utilities_obj->odb_format_size($end_size,3);
436	// TOTAL SAVING
437	$odb_class->log_arr["savings"] = $odb_class->odb_utilities_obj->odb_format_size(($this->start_size - $end_size),3);
438	// WRITE RESULTS TO LOG FILE
439	$odb_class->odb_logger_obj->write_log($odb_class->log_arr);
440
441	$total_savings = $odb_class->odb_rvg_options['total_savings'];
442	$total_savings += ($this->start_size - $end_size);
443	$odb_class->odb_rvg_options['total_savings'] = $total_savings;
444
445	$odb_class->odb_multisite_obj->odb_ms_update_option('odb_rvg_options', $odb_class->odb_rvg_options);
446
447	if(!$scheduler)
448	{
449	?>
450	<div id="odb-savings" class="odb-padding-left">
451	<div class="odb-title-bar">
452	<h2><?php _e('Savings', $odb_class->odb_txt_domain);?></h2>
453	</div>
454	<br>
455	<br>
456	<table border="0" cellspacing="8" cellpadding="2">
457	<tr>
458	<th> </th>
459	<th class="odb-border-bottom"><?php _e('size of the database', $odb_class->odb_txt_domain);?></th>
460	</tr>
461	<tr>
462	<td align="right"><?php _e('BEFORE optimization', $odb_class->odb_txt_domain);?></td>
463	<td align="right" class="odb-bold"><?php echo $odb_class->odb_utilities_obj->odb_format_size($this->start_size,3); ?></td>
464	</tr>
465	<tr>
466	<td align="right"><?php _e('AFTER optimization', $odb_class->odb_txt_domain);?></td>
467	<td align="right" class="odb-bold"><?php echo $odb_class->odb_utilities_obj->odb_format_size($end_size,3); ?></td>
468	</tr>
469	<tr>
470	<td align="right" class="odb-bold"><?php _e('SAVINGS THIS TIME', $odb_class->odb_txt_domain);?></td>
471	<td align="right" class="odb-border-top odb-bold"><?php echo $odb_class->odb_utilities_obj->odb_format_size(($this->start_size - $end_size),3); ?></td>
472	</tr>
473	<tr>
474	<td align="right" class="odb-bold"><?php _e('TOTAL SAVINGS SINCE THE FIRST RUN', $odb_class->odb_txt_domain);?></td>
475	<td align="right" class="odb-border-top odb-bold"><?php echo $odb_class->odb_utilities_obj->odb_format_size($total_savings,3); ?></td>
476	</tr>
477	</table>
478	</div><!-- /odb-savings -->
479	<?php
480	} // if(!$scheduler)
481	} // odb_savings()
482
483
484	/********************************************************************************************
485	* SHOW LOADING TIME
486	********************************************************************************************/
487	function odb_done()
488	{
489	global $odb_class;
490
491	$time = microtime();
492	$time = explode(' ', $time);
493	$time = $time[1] + $time[0];
494	$finish = $time;
495
496	$total_time = round(($finish - $odb_class->odb_start_time), 4);
497	?>
498	<div id="odb-done" class="odb-padding-left">
499	<div class="odb-title-bar">
500	<h2>
501	<?php _e('DONE!', $odb_class->odb_txt_domain);?>
502	</h2>
503	</div>
504	<br />
505	<br />
506	<span class="odb-padding-left"><?php _e('Optimization took', $odb_class->odb_txt_domain)?> <strong><?php echo $total_time;?></strong> <?php _e('seconds', $odb_class->odb_txt_domain)?>.</span>
507	<?php
508	if(file_exists($odb_class->odb_plugin_path.'logs/rvg-optimize-db-log.html'))
509	{
510	?>
511	<br />
512	<br />
513
514	<input class="button odb-normal" type="button" name="view_log" value="<?php _e('View Log File', $odb_class->odb_txt_domain);?>" onclick="window.open('<?php echo $odb_class->odb_logfile_url?>')" />
515
516	<input class="button odb-normal" type="button" name="delete_log" value="<?php _e('Delete Log File', $odb_class->odb_txt_domain);?>" onclick="self.location='tools.php?page=rvg-optimize-database&action=delete_log'" />
517	<?php
518	}
519	?>
520	</div><!-- /odb-done -->
521	<?php
522	} // odb_done()
523
524
525	/********************************************************************************************
526	* GET REVISIONS
527	********************************************************************************************/
528	function odb_get_revisions()
529	{
530	global $odb_class, $wpdb;
531
532	$res_arr = array();
533
534	$max_revisions = $odb_class->odb_rvg_options['nr_of_revisions'];
535
536	$index = 0;
537	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
538	{ $sql = "
539	SELECT `post_parent`, `post_title`, COUNT(*) cnt
540	FROM ".$odb_class->odb_ms_prefixes[$i]."posts
541	WHERE `post_type` = 'revision'
542	GROUP BY `post_parent`
543	HAVING COUNT(*) > ".$max_revisions."
544	ORDER BY UCASE(`post_title`)
545	";
546	$res = $wpdb->get_results($sql, ARRAY_A);
547
548	for($j=0; $j<count($res); $j++)
549	{ if(isset($res[$j]))
550	{ $res_arr[$index] = $res[$j];
551	$res_arr[$index]['site'] = $odb_class->odb_ms_prefixes[$i];
552	$index++;
553	}
554	}
555	}
556
557	return $res_arr;
558
559	} // odb_get_revisions()
560
561
562	/********************************************************************************************
563	* DELETE THE REVISIONS
564	********************************************************************************************/
565	function odb_delete_revisions($results, $scheduler)
566	{
567	global $odb_class, $wpdb;
568
569	$max_revisions = $odb_class->odb_rvg_options['nr_of_revisions'];
570
571	$nr = 1;
572	$total_deleted = 0;
573
574	for($i=0; $i<count($results); $i++)
575	{ $nr_to_delete = $results[$i]['cnt'] - $max_revisions;
576	$total_deleted += $nr_to_delete;
577
578	if (!$scheduler)
579	{
580	?>
581	<tr>
582	<td align="right" valign="top"><?php echo $nr?>.</td>
583	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
584	<td valign="top" class="odb-bold"><?php echo $results[$i]['post_title']?></td>
585	<td valign="top"><?php
586	} // if (!$scheduler)
587
588	$sql_get_posts = "
589	SELECT `ID`, `post_modified`
590	FROM ".$results[$i]['site']."posts
591	WHERE `post_parent`=".$results[$i]['post_parent']."
592	AND `post_type`='revision'
593	ORDER BY `post_modified` ASC
594	";
595
596	$results_get_posts = $wpdb->get_results($sql_get_posts);
597
598	for($j=0; $j<$nr_to_delete; $j++)
599	{
600	if(!$scheduler) echo $results_get_posts[$j]->post_modified.'<br />';
601
602	$sql_delete = "
603	DELETE FROM ".$results[$i]['site']."posts
604	WHERE `ID` = ".$results_get_posts[$j]->ID."
605	";
606	$wpdb->get_results($sql_delete);
607
608	} // for($j=0; $j<$nr_to_delete; $j++)
609
610	$nr++;
611	if(!$scheduler)
612	{
613	?></td>
614	<td align="right" valign="top" class="odb-bold"><?php echo $nr_to_delete?></td>
615	</tr>
616	<?php
617	} // if(!$scheduler)
618	} // for($i=0; $i<count($results); $i++)
619
620	return $total_deleted;
621	} // odb_delete_revisions()
622
623
624	/********************************************************************************************
625	* GET TRASHED POSTS / PAGES AND COMMENTS
626	********************************************************************************************/
627	function odb_get_trash()
628	{
629	global $wpdb, $odb_class;
630
631	$res_arr = array();
632
633	$index = 0;
634	// LOOP TROUGH SITES
635	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
636	{
637	$sql = "
638	SELECT `ID` AS id, 'post' AS post_type, `post_title` AS title, `post_modified` AS modified
639	FROM ".$odb_class->odb_ms_prefixes[$i]."posts
640	WHERE `post_status` = 'trash'
641	UNION ALL
642	SELECT `comment_ID` AS id, 'comment' AS post_type, `comment_author_IP` AS title, `comment_date` AS modified
643	FROM ".$odb_class->odb_ms_prefixes[$i]."comments
644	WHERE `comment_approved` = 'trash'
645	ORDER BY post_type, UCASE(title)
646	";
647	$res = $wpdb->get_results($sql, ARRAY_A);
648
649	if($res != null)
650	{ $res_arr[$index] = $res[0];
651	$res_arr[$index]['site'] = $odb_class->odb_ms_prefixes[$i];
652	$index++;
653	}
654	}
655
656	return $res_arr;
657	} // odb_get_trash()
658
659
660	/********************************************************************************************
661	* DELETE TRASHED POSTS AND PAGES
662	********************************************************************************************/
663	function odb_delete_trash($results, $scheduler)
664	{
665	global $wpdb;
666
667	$nr = 1;
668	$total_deleted = count($results);
669
670	for($i=0; $i<$total_deleted; $i++)
671	{ if(!$scheduler)
672	{
673	?>
674	<tr>
675	<td align="right" valign="top"><?php echo $nr; ?></td>
676	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
677	<td valign="top"><?php echo $results[$i]['post_type']; ?></td>
678	<td valign="top"><?php echo $results[$i]['title']; ?></td>
679	<td valign="top" nowrap="nowrap"><?php echo $results[$i]['modified']; ?></td>
680	</tr>
681	<?php
682	} // if(!$scheduler)
683
684	if($results[$i]['post_type'] == 'comment')
685	{ // DELETE META DATA (IF ANY...)
686	$sql_delete = "
687	DELETE FROM ".$results[$i]['site']."commentmeta
688	WHERE `comment_id` = ".$results[$i]['id']."
689	";
690	$wpdb->get_results($sql_delete);
691	}
692
693	// DELETE TRASHED POSTS / PAGES
694	$sql_delete = "
695	DELETE FROM ".$results[$i]['site']."posts
696	WHERE `post_status` = 'trash'
697	";
698	$wpdb->get_results($sql_delete);
699
700	// DELETE TRASHED COMMENTS
701	$sql_delete = "
702	DELETE FROM ".$results[$i]['site']."comments
703	WHERE `comment_approved` = 'trash'
704	";
705	$wpdb->get_results($sql_delete);
706
707	$nr++;
708	} // for($i=0; $i<count($results); $i++)
709
710	return $total_deleted;
711	} // odb_delete_trash()
712
713
714	/********************************************************************************************
715	* GET SPAMMED COMMENTS
716	********************************************************************************************/
717	function odb_get_spam()
718	{
719	global $wpdb, $odb_class;
720
721	$res_arr = array();
722
723	$index = 0;
724	// LOOP THROUGH SITES
725	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
726	{
727	$sql = "
728	SELECT `comment_ID`, `comment_author`, `comment_author_email`, `comment_date`
729	FROM ".$odb_class->odb_ms_prefixes[$i]."comments
730	WHERE `comment_approved` = 'spam'
731	ORDER BY UCASE(`comment_author`)
732	";
733	$res = $wpdb->get_results($sql, ARRAY_A);
734
735	if($res != null)
736	{ $res_arr[$index] = $res[0];
737	$res_arr[$index]['site'] = $odb_class->odb_ms_prefixes[$i];
738	$index++;
739	}
740	}
741
742	return $res_arr;
743
744	} // odb_get_spam()
745
746
747	/********************************************************************************************
748	* DELETE SPAMMED ITEMS
749	********************************************************************************************/
750	function odb_delete_spam($results, $scheduler)
751	{
752	global $wpdb;
753
754	$nr = 1;
755	$total_deleted = count($results);
756	for($i=0; $i<count($results); $i++)
757	{ if (!$scheduler)
758	{
759	?>
760	<tr>
761	<td align="right" valign="top"><?php echo $nr; ?></td>
762	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
763	<td valign="top"><?php echo $results[$i]['comment_author']; ?></td>
764	<td valign="top"><?php echo $results[$i]['comment_author_email']; ?></td>
765	<td valign="top" nowrap="nowrap"><?php echo $results[$i]['comment_date']; ?></td>
766	</tr>
767	<?php
768	} // if (!$scheduler)
769
770	$sql_delete = "
771	DELETE FROM ".$results[$i]['site']."commentmeta
772	WHERE `comment_id` = ".$results[$i]['comment_ID']."
773	";
774	$wpdb->get_results($sql_delete);
775
776	$sql_delete = "
777	DELETE FROM ".$results[$i]['site']."comments
778	WHERE `comment_approved` = 'spam'
779	";
780	$wpdb->get_results($sql_delete);
781
782	$nr++;
783	} // for($i=0; $i<count($results); $i++)
784
785	return $total_deleted;
786
787	} // odb_delete_spam()
788
789
790	/********************************************************************************************
791	* DELETE UNUSED TAGS
792	********************************************************************************************/
793	function odb_delete_tags()
794	{
795	global $wpdb, $odb_class;
796
797	$total_deleted = 0;
798
799	// LOOP THROUGH THE NETWORK
800	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
801	{
802	$sql = "
803	SELECT a.term_id AS term_id, a.name AS name
804	FROM `".$odb_class->odb_ms_prefixes[$i]."terms` a, `".$odb_class->odb_ms_prefixes[$i]."term_taxonomy` b
805	WHERE a.term_id = b.term_id
806	AND b.taxonomy = 'post_tag'
807	AND b.term_taxonomy_id NOT IN (
808	SELECT term_taxonomy_id
809	FROM ".$odb_class->odb_ms_prefixes[$i]."term_relationships
810	)
811	";
812
813	$res = $wpdb->get_results($sql);
814	for($j=0; $j<count($res); $j++)
815	{ if(!$this->odb_delete_tags_is_scheduled($res[$j]->term_id, $odb_class->odb_ms_prefixes[$i]))
816	{ // TAG NOT USED IN SCHEDULED POSTS: CAN BE DELETED
817	$total_deleted++;
818
819	$sql_del = "
820	DELETE FROM ".$odb_class->odb_ms_prefixes[$i]."term_taxonomy
821	WHERE term_id = ".$res[$j]->term_id."
822	";
823	$wpdb->get_results($sql_del);
824
825	$sql_del = "
826	DELETE FROM ".$odb_class->odb_ms_prefixes[$i]."terms
827	WHERE term_id = ".$res[$j]->term_id."
828	";
829	$wpdb->get_results($sql_del);
830	}
831	} // for($j=0; $j<count($res); $j++)
832	} // for($i=0; $i<count($odb_class->odb_ms_blogids); $i++)
833
834	return $total_deleted;
835	} // odb_delete_tags()
836
837
838	/********************************************************************************************
839	* IS THE UNUSED TAG USED IN ONE OR MORE SCHEDULED POSTS?
840	********************************************************************************************/
841	function odb_delete_tags_is_scheduled($term_id, $odb_prefix)
842	{
843	global $wpdb;
844
845	$sql_get_posts = "
846	SELECT p.post_status
847	FROM ".$odb_prefix."term_relationships t, ".$odb_prefix."posts p
848	WHERE t.term_taxonomy_id = '".$term_id."'
849	AND t.object_id = p.ID
850	";
851
852	$results_get_posts = $wpdb->get_results($sql_get_posts);
853	for($i=0; $i<count($results_get_posts); $i++)
854	if($results_get_posts[$i]->post_status == 'future') return true;
855
856	return false;
857
858	} // odb_delete_tags_is_scheduled()
859
860
861	/********************************************************************************************
862	* DELETE EXPIRED TRANSIENTS
863	********************************************************************************************/
864	function odb_delete_transients()
865	{
866	global $wpdb, $odb_class;
867
868	$delay = time() - 60; // ONE MINUTE DELAY
869
870	$total_deleted = 0;
871
872	// LOOP THROUGH THE NETWORK
873	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
874	{
875	// FIND EXPIRED TRANSIENTS
876	$sql = "
877	SELECT `option_name`
878	FROM ".$odb_class->odb_ms_prefixes[$i]."options
879	WHERE (
880	option_name LIKE '_transient_timeout_%'
881	OR option_name LIKE '_site_transient_timeout_%'
882	)
883	AND option_value < '$delay'
884	";
885
886	$results = $wpdb->get_results($sql);
887	$total_deleted += count($results);
888
889	// LOOP THROUGH THE RESULTS
890	for($j=0; $j<count($results); $j++)
891	{
892	if(substr($results[$j]->option_name, 0, 19) == '_transient_timeout_')
893	{ // _transient_timeout_%
894	$transient = substr($results[$j]->option_name, 19);
895	// DELETE THE TRANSIENT
896	delete_transient($transient);
897	}
898	else
899	{ // _site_transient_timeout_%
900	$transient = substr($results[$j]->option_name, 24);
901	// DELETE THE TRANSIENT
902	delete_site_transient($transient);
903	}
904	} // for($j=0; $j<count($results); $j++)
905
906	} // for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
907	return $total_deleted;
908	} // odb_delete_transients()
909
910
911	/********************************************************************************************
912	* DELETE PINGBACKS AND TRACKBACKS
913	********************************************************************************************/
914	function odb_delete_pingbacks()
915	{
916	global $wpdb, $odb_class;
917
918	$total_deleted = 0;
919
920	// LOOP THROUGH THE NETWORK
921	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
922	{
923	$sql = "
924	SELECT `comment_ID`
925	FROM ".$odb_class->odb_ms_prefixes[$i]."comments
926	WHERE (
927	`comment_type` = 'pingback'
928	OR `comment_type` = 'trackback'
929	)
930	";
931
932	$results = $wpdb->get_results($sql);
933	$total_deleted = count($results);
934
935	for($j=0; $j<count($results); $j++)
936	{ // DELETE METADATA FOR THIS COMMENT (IF ANY)
937	$sql_delete_meta = "
938	DELETE FROM ".$odb_class->odb_ms_prefixes[$i]."commentmeta
939	WHERE `comment_id` = ".$results[$j]->comment_ID."
940	";
941	$wpdb->get_results($sql_delete_meta);
942	}
943
944	// DELETE COMMENTS
945	$sql_delete_comments = "
946	DELETE FROM ".$odb_class->odb_ms_prefixes[$i]."comments
947	WHERE (
948	`comment_type` = 'pingback'
949	OR `comment_type` = 'trackback'
950	)
951	";
952	$wpdb->get_results($sql_delete_comments);
953	} // for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
954
955	return $total_deleted;
956	} // odb_delete_pingbacks()
957
958
959	/********************************************************************************************
960	* DELETE ORPHAN POSTMETA RECORDS
961	********************************************************************************************/
962	function odb_delete_orphans()
963	{
964	global $wpdb, $odb_class;
965
966	$meta_orphans = 0;
967	$post_orphans = 0;
968
969	// LOOP THROUGH THE NETWORK
970	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
971	{
972	// DELETE POST ORPHANS (AUTO DRAFTS)
973	$sql_delete = "
974	SELECT COUNT(*) cnt
975	FROM ".$odb_class->odb_ms_prefixes[$i]."posts
976	WHERE ID NOT IN (SELECT post_id FROM ".$odb_class->odb_ms_prefixes[$i]."postmeta)
977	AND post_status = 'auto-draft'
978	";
979
980	$results = $wpdb->get_results($sql_delete);
981
982	$post_orphans = $results[0]->cnt;
983
984	if($post_orphans > 0)
985	{ $sql_delete = "
986	DELETE FROM ".$odb_class->odb_ms_prefixes[$i]."posts
987	WHERE ID NOT IN (SELECT post_id FROM ".$odb_class->odb_ms_prefixes[$i]."postmeta)
988	AND post_status = 'auto-draft'
989	";
990	$wpdb->get_results($sql_delete);
991	}
992
993	// DELETE POSTMETA ORPHANS
994	$sql_delete = "
995	SELECT COUNT(*) cnt
996	FROM ".$odb_class->odb_ms_prefixes[$i]."postmeta
997	WHERE post_id NOT IN (SELECT ID FROM ".$odb_class->odb_ms_prefixes[$i]."posts)
998	";
999
1000	$results = $wpdb->get_results($sql_delete);
1001
1002	$meta_orphans = $results[0]->cnt;
1003
1004	if($meta_orphans > 0)
1005	{ $sql_delete = "
1006	DELETE FROM ".$odb_class->odb_ms_prefixes[$i]."postmeta
1007	WHERE post_id NOT IN (SELECT ID FROM ".$odb_class->odb_ms_prefixes[$i]."posts)
1008	";
1009	$wpdb->get_results($sql_delete);
1010	}
1011	}
1012
1013	return ($meta_orphans + $post_orphans);
1014	} // odb_delete_orphans()
1015
1016
1017	/********************************************************************************************
1018	* OPTIMIZE DATABASE TABLES
1019	********************************************************************************************/
1020	function odb_optimize_tables($scheduler)
1021	{
1022	global $odb_class, $wpdb;
1023
1024	$cnt = 0;
1025	for ($i=0; $i<count($odb_class->odb_tables); $i++)
1026	{
1027	if(!isset($odb_class->odb_rvg_excluded_tabs[$odb_class->odb_tables[$i][0]]))
1028	{ # TABLE NOT EXCLUDED
1029	$cnt++;
1030
1031	$sql = "
1032	SELECT engine, (data_length + index_length) AS size, table_rows
1033	FROM information_schema.TABLES
1034	WHERE table_schema = '".DB_NAME."'
1035	AND table_name = '".$odb_class->odb_tables[$i][0]."'
1036	";
1037	$table_info = $wpdb->get_results($sql);
1038
1039	if($odb_class->odb_rvg_options["optimize_innodb"] == 'N' && strtolower($table_info[0]->engine) == 'innodb')
1040	{ // SKIP InnoDB tables
1041	$msg = __('InnoDB table: skipped...', 'rvg-optimize-database');
1042	}
1043	else
1044	{ $query = "OPTIMIZE TABLE ".$odb_class->odb_tables[$i][0];
1045	$result = $wpdb->get_results($query);
1046	$msg = $result[0]->Msg_text;
1047	$msg = str_replace('OK', __('<span class="odb-optimized">TABLE OPTIMIZED</span>', 'rvg-optimize-database'), $msg);
1048	$msg = str_replace('Table is already up to date', __('Table is already up to date', 'rvg-optimize-database'), $msg);
1049	$msg = str_replace('Table does not support optimize, doing recreate + analyze instead', __('<span class="odb-optimized">TABLE OPTIMIZED</span>', 'rvg-optimize-database'), $msg);
1050
1051	}
1052
1053	if (!$scheduler)
1054	{ // NOT FROM THE SCEDULER
1055	?>
1056	<tr>
1057	<td align="right" valign="top"><?php echo $cnt?>.</td>
1058	<td valign="top" class="odb-bold"><?php echo $odb_class->odb_tables[$i][0] ?></td>
1059	<td valign="top"><?php echo $msg ?></td>
1060	<td valign="top"><?php echo $table_info[0]->engine ?></td>
1061	<td align="right" valign="top"><?php echo $table_info[0]->table_rows ?></td>
1062	<td align="right" valign="top"><?php echo $odb_class->odb_utilities_obj->odb_format_size($table_info[0]->size) ?></td>
1063	</tr>
1064	<?php
1065	} // if (!$scheduler)
1066	} // if(!$excluded)
1067	} // for ($i=0; $i<count($tables); $i++)
1068	return $cnt;
1069
1070	} // odb_optimize_tables()
1071
1072	} // ODB_Cleaner
1073	?>