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

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

1,135 lines 36.9 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	if($odb_class->odb_rvg_options['revision_mode'] == 1)
535	{ // MAX NUMBER OF REVISIONS TO KEEP
536	$max_revisions = $odb_class->odb_rvg_options['nr_of_revisions'];
537
538	$index = 0;
539	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
540	{ $sql = sprintf ("
541	SELECT `post_parent`, `post_title`, COUNT(*) cnt
542	FROM %sposts
543	WHERE `post_type` = 'revision'
544	GROUP BY `post_parent`
545	HAVING COUNT(*) > %d
546	ORDER BY UCASE(`post_title`)
547	", $odb_class->odb_ms_prefixes[$i], $max_revisions);
548	$res = $wpdb->get_results($sql, ARRAY_A);
549
550	for($j=0; $j<count($res); $j++)
551	{ if(isset($res[$j]))
552	{ $res_arr[$index] = $res[$j];
553	$res_arr[$index]['site'] = $odb_class->odb_ms_prefixes[$i];
554	$index++;
555	}
556	} // for($j=0; $j<count($res); $j++)
557	} // for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
558	}
559	else
560	{ // DELETE REVISIONS OLDER THAN x DAYS
561	$older_than = $odb_class->odb_rvg_options['older_than'];
562
563	$index = 0;
564	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
565	{ $sql = sprintf("
566	SELECT `ID`, `post_parent`, `post_title`, `post_modified`
567	FROM %sposts
568	WHERE `post_type` = 'revision'
569	AND `post_modified` < date_sub(now(), INTERVAL %d DAY)
570	ORDER BY UCASE(`post_title`)
571	", $odb_class->odb_ms_prefixes[$i], $older_than);
572	$res = $wpdb->get_results($sql, ARRAY_A);
573
574	for($j=0; $j<count($res); $j++)
575	{ if(isset($res[$j]))
576	{ $res_arr[$index] = $res[$j];
577	$res_arr[$index]['site'] = $odb_class->odb_ms_prefixes[$i];
578	$index++;
579	}
580	}
581	}
582	} // if($odb_class->odb_rvg_options['revision_mode'] == 1)
583
584	return $res_arr;
585
586	} // odb_get_revisions()
587
588
589	/********************************************************************************************
590	* DELETE THE REVISIONS
591	********************************************************************************************/
592	function odb_delete_revisions($results, $scheduler)
593	{
594	global $odb_class, $wpdb;
595
596	$total_deleted = 0;
597	$nr = 1;
598
599	if($odb_class->odb_rvg_options['revision_mode'] == 1)
600	{
601	// KEEP A MAX NUMBER OF REVISIONS
602	$max_revisions = $odb_class->odb_rvg_options['nr_of_revisions'];
603
604	for($i=0; $i<count($results); $i++)
605	{ $nr_to_delete = $results[$i]['cnt'] - $max_revisions;
606	$total_deleted += $nr_to_delete;
607
608	if (!$scheduler)
609	{
610	?>
611	<tr>
612	<td align="right" valign="top"><?php echo $nr?>.</td>
613	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
614	<td valign="top" class="odb-bold"><?php echo $results[$i]['post_title']?></td>
615	<td valign="top"><?php
616	} // if (!$scheduler)
617
618	$sql_get_posts = sprintf( "
619	SELECT `ID`, `post_modified`
620	FROM %sposts
621	WHERE `post_parent`=%d
622	AND `post_type`='revision'
623	ORDER BY `post_modified` ASC
624	", $results[$i]['site'], $results[$i]['post_parent']);
625	$results_get_posts = $wpdb->get_results($sql_get_posts);
626
627	for($j=0; $j<$nr_to_delete; $j++)
628	{
629	if(!$scheduler) echo $results_get_posts[$j]->post_modified.'<br />';
630
631	$sql_delete = sprintf ("
632	DELETE FROM %sposts
633	WHERE `ID` = %d
634	", $results[$i]['site'], $results_get_posts[$j]->ID);
635	$wpdb->get_results($sql_delete);
636
637	} // for($j=0; $j<$nr_to_delete; $j++)
638
639	$nr++;
640	if(!$scheduler)
641	{
642	?></td>
643	<td align="right" valign="top" class="odb-bold"><?php echo $nr_to_delete?></td>
644	</tr>
645	<?php
646	} // if(!$scheduler)
647	} // for($i=0; $i<count($results); $i++)
648	}
649	else
650	{ // DELETE REVISIONS OLDER THAN x DAYS
651	$older_than = $odb_class->odb_rvg_options['older_than'];
652	$total_deleted = count($results);
653
654	for($i=0; $i<count($results); $i++)
655	{
656	if (!$scheduler)
657	{
658	?>
659	<tr>
660	<td align="right" valign="top"><?php echo $nr?> <?php echo $results[$i]['ID']?>.</td>
661	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
662	<td valign="top" class="odb-bold"><?php echo $results[$i]['post_title']?></td>
663	<td valign="top" valign="top"><?php echo $results[$i]['post_modified']?></td><?php
664	} // if (!$scheduler)
665
666	$sql_delete = sprintf ("
667	DELETE FROM %sposts
668	WHERE `ID` = %d
669	", $results[$i]['site'], $results[$i]['ID']);
670	$wpdb->get_results($sql_delete);
671
672	$nr++;
673	if(!$scheduler)
674	{
675	?>
676	<td align="right" valign="top" class="odb-bold">1</td>
677	</tr>
678	<?php
679	} // if(!$scheduler)
680	} // for($i=0; $i<count($results); $i++)
681	} // if($odb_class->odb_rvg_options['revision_mode'] == 1)
682
683	return $total_deleted;
684	} // odb_delete_revisions()
685
686
687	/********************************************************************************************
688	* GET TRASHED POSTS / PAGES AND COMMENTS
689	********************************************************************************************/
690	function odb_get_trash()
691	{
692	global $wpdb, $odb_class;
693
694	$res_arr = array();
695
696	$index = 0;
697	// LOOP TROUGH SITES
698	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
699	{
700	$sql = sprintf ("
701	SELECT `ID` AS id, 'post' AS post_type, `post_title` AS title, `post_modified` AS modified
702	FROM %sposts
703	WHERE `post_status` = 'trash'
704	UNION ALL
705	SELECT `comment_ID` AS id, 'comment' AS post_type, `comment_author_IP` AS title, `comment_date` AS modified
706	FROM %scomments
707	WHERE `comment_approved` = 'trash'
708	ORDER BY post_type, UCASE(title)
709	", $odb_class->odb_ms_prefixes[$i], $odb_class->odb_ms_prefixes[$i]);
710	$res = $wpdb->get_results($sql, ARRAY_A);
711
712	if($res != null)
713	{ $res_arr[$index] = $res[0];
714	$res_arr[$index]['site'] = $odb_class->odb_ms_prefixes[$i];
715	$index++;
716	}
717	}
718
719	return $res_arr;
720	} // odb_get_trash()
721
722
723	/********************************************************************************************
724	* DELETE TRASHED POSTS AND PAGES
725	********************************************************************************************/
726	function odb_delete_trash($results, $scheduler)
727	{
728	global $wpdb;
729
730	$nr = 1;
731	$total_deleted = count($results);
732
733	for($i=0; $i<$total_deleted; $i++)
734	{ if(!$scheduler)
735	{
736	?>
737	<tr>
738	<td align="right" valign="top"><?php echo $nr; ?></td>
739	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
740	<td valign="top"><?php echo $results[$i]['post_type']; ?></td>
741	<td valign="top"><?php echo $results[$i]['title']; ?></td>
742	<td valign="top" nowrap="nowrap"><?php echo $results[$i]['modified']; ?></td>
743	</tr>
744	<?php
745	} // if(!$scheduler)
746
747	if($results[$i]['post_type'] == 'comment')
748	{ // DELETE META DATA (IF ANY...)
749	$sql_delete = sprintf ("
750	DELETE FROM %scommentmeta
751	WHERE `comment_id` = %d
752	", $results[$i]['site'], $results[$i]['id']);
753	$wpdb->get_results($sql_delete);
754	}
755
756	// DELETE TRASHED POSTS / PAGES
757	$sql_delete = sprintf ("
758	DELETE FROM %sposts
759	WHERE `post_status` = 'trash'
760	", $results[$i]['site']);
761	$wpdb->get_results($sql_delete);
762
763	// DELETE TRASHED COMMENTS
764	$sql_delete = sprintf ("
765	DELETE FROM %scomments
766	WHERE `comment_approved` = 'trash'
767	", $results[$i]['site']);
768	$wpdb->get_results($sql_delete);
769
770	$nr++;
771	} // for($i=0; $i<count($results); $i++)
772
773	return $total_deleted;
774	} // odb_delete_trash()
775
776
777	/********************************************************************************************
778	* GET SPAMMED COMMENTS
779	********************************************************************************************/
780	function odb_get_spam()
781	{
782	global $wpdb, $odb_class;
783
784	$res_arr = array();
785
786	$index = 0;
787	// LOOP THROUGH SITES
788	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
789	{
790	$sql = sprintf ("
791	SELECT `comment_ID`, `comment_author`, `comment_author_email`, `comment_date`
792	FROM %scomments
793	WHERE `comment_approved` = 'spam'
794	ORDER BY UCASE(`comment_author`)
795	", $odb_class->odb_ms_prefixes[$i]);
796	$res = $wpdb->get_results($sql, ARRAY_A);
797
798	if($res != null)
799	{ $res_arr[$index] = $res[0];
800	$res_arr[$index]['site'] = $odb_class->odb_ms_prefixes[$i];
801	$index++;
802	}
803	}
804
805	return $res_arr;
806
807	} // odb_get_spam()
808
809
810	/********************************************************************************************
811	* DELETE SPAMMED ITEMS
812	********************************************************************************************/
813	function odb_delete_spam($results, $scheduler)
814	{
815	global $wpdb;
816
817	$nr = 1;
818	$total_deleted = count($results);
819	for($i=0; $i<count($results); $i++)
820	{ if (!$scheduler)
821	{
822	?>
823	<tr>
824	<td align="right" valign="top"><?php echo $nr; ?></td>
825	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
826	<td valign="top"><?php echo $results[$i]['comment_author']; ?></td>
827	<td valign="top"><?php echo $results[$i]['comment_author_email']; ?></td>
828	<td valign="top" nowrap="nowrap"><?php echo $results[$i]['comment_date']; ?></td>
829	</tr>
830	<?php
831	} // if (!$scheduler)
832
833	$sql_delete = sprintf ("
834	DELETE FROM %scommentmeta
835	WHERE `comment_id` = %d
836	", $results[$i]['site'], $results[$i]['comment_ID']);
837	$wpdb->get_results($sql_delete);
838
839	$sql_delete = sprintf ("
840	DELETE FROM %scomments
841	WHERE `comment_approved` = 'spam'
842	", $results[$i]['site']);
843	$wpdb->get_results($sql_delete);
844
845	$nr++;
846	} // for($i=0; $i<count($results); $i++)
847
848	return $total_deleted;
849
850	} // odb_delete_spam()
851
852
853	/********************************************************************************************
854	* DELETE UNUSED TAGS
855	********************************************************************************************/
856	function odb_delete_tags()
857	{
858	global $wpdb, $odb_class;
859
860	$total_deleted = 0;
861
862	// LOOP THROUGH THE NETWORK
863	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
864	{
865	$sql = sprintf ("
866	SELECT a.term_id AS term_id, a.name AS name
867	FROM `%sterms` a, `%sterm_taxonomy` b
868	WHERE a.term_id = b.term_id
869	AND b.taxonomy = 'post_tag'
870	AND b.term_taxonomy_id NOT IN (
871	SELECT term_taxonomy_id
872	FROM %sterm_relationships
873	)
874	", $odb_class->odb_ms_prefixes[$i], $odb_class->odb_ms_prefixes[$i], $odb_class->odb_ms_prefixes[$i]);
875
876	$res = $wpdb->get_results($sql);
877	for($j=0; $j<count($res); $j++)
878	{ if(!$this->odb_delete_tags_is_scheduled($res[$j]->term_id, $odb_class->odb_ms_prefixes[$i]))
879	{ // TAG NOT USED IN SCHEDULED POSTS: CAN BE DELETED
880	$total_deleted++;
881
882	$sql_del = sprintf ("
883	DELETE FROM %sterm_taxonomy
884	WHERE term_id = %d
885	", $odb_class->odb_ms_prefixes[$i], $res[$j]->term_id);
886	$wpdb->get_results($sql_del);
887
888	$sql_del = sprintf ("
889	DELETE FROM %sterms
890	WHERE term_id = %d
891	", $odb_class->odb_ms_prefixes[$i], $res[$j]->term_id);
892	$wpdb->get_results($sql_del);
893	}
894	} // for($j=0; $j<count($res); $j++)
895	} // for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
896
897	return $total_deleted;
898	} // odb_delete_tags()
899
900
901	/********************************************************************************************
902	* IS THE UNUSED TAG USED IN ONE OR MORE SCHEDULED POSTS?
903	********************************************************************************************/
904	function odb_delete_tags_is_scheduled($term_id, $odb_prefix)
905	{
906	global $wpdb;
907
908	$sql_get_posts = sprintf ("
909	SELECT p.post_status
910	FROM %sterm_relationships t, %sposts p
911	WHERE t.term_taxonomy_id = '%s'
912	AND t.object_id = p.ID
913	", $odb_prefix, $odb_prefix, $term_id);
914
915	$results_get_posts = $wpdb->get_results($sql_get_posts);
916	for($i=0; $i<count($results_get_posts); $i++)
917	if($results_get_posts[$i]->post_status == 'future') return true;
918
919	return false;
920
921	} // odb_delete_tags_is_scheduled()
922
923
924	/********************************************************************************************
925	* DELETE EXPIRED TRANSIENTS
926	********************************************************************************************/
927	function odb_delete_transients()
928	{
929	global $wpdb, $odb_class;
930
931	$delay = time() - 60; // ONE MINUTE DELAY
932
933	$total_deleted = 0;
934
935	// LOOP THROUGH THE NETWORK
936	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
937	{
938	// FIND EXPIRED TRANSIENTS
939	$sql = "
940	SELECT `option_name`
941	FROM ".$odb_class->odb_ms_prefixes[$i]."options
942	WHERE (
943	option_name LIKE '_transient_timeout_%'
944	OR option_name LIKE '_site_transient_timeout_%'
945	)
946	AND option_value < '".$delay."'
947	";
948
949	$results = $wpdb->get_results($sql);
950	$total_deleted += count($results);
951
952	// LOOP THROUGH THE RESULTS
953	for($j=0; $j<count($results); $j++)
954	{
955	if(substr($results[$j]->option_name, 0, 19) == '_transient_timeout_')
956	{ // _transient_timeout_%
957	$transient = substr($results[$j]->option_name, 19);
958	// DELETE THE TRANSIENT
959	delete_transient($transient);
960	}
961	else
962	{ // _site_transient_timeout_%
963	$transient = substr($results[$j]->option_name, 24);
964	// DELETE THE TRANSIENT
965	delete_site_transient($transient);
966	}
967	} // for($j=0; $j<count($results); $j++)
968
969	} // for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
970	return $total_deleted;
971	} // odb_delete_transients()
972
973
974	/********************************************************************************************
975	* DELETE PINGBACKS AND TRACKBACKS
976	********************************************************************************************/
977	function odb_delete_pingbacks()
978	{
979	global $wpdb, $odb_class;
980
981	$total_deleted = 0;
982
983	// LOOP THROUGH THE NETWORK
984	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
985	{
986	$sql = sprintf ("
987	SELECT `comment_ID`
988	FROM %scomments
989	WHERE (
990	`comment_type` = 'pingback'
991	OR `comment_type` = 'trackback'
992	)
993	", $odb_class->odb_ms_prefixes[$i]);
994
995	$results = $wpdb->get_results($sql);
996	$total_deleted = count($results);
997
998	for($j=0; $j<count($results); $j++)
999	{ // DELETE METADATA FOR THIS COMMENT (IF ANY)
1000	$sql_delete_meta = sprintf ("
1001	DELETE FROM %scommentmeta
1002	WHERE `comment_id` = %d
1003	", $odb_class->odb_ms_prefixes[$i], $results[$j]->comment_ID);
1004	$wpdb->get_results($sql_delete_meta);
1005	}
1006
1007	// DELETE COMMENTS
1008	$sql_delete_comments = sprintf ("
1009	DELETE FROM %scomments
1010	WHERE (
1011	`comment_type` = 'pingback'
1012	OR `comment_type` = 'trackback'
1013	)
1014	", $odb_class->odb_ms_prefixes[$i]);
1015	$wpdb->get_results($sql_delete_comments);
1016	} // for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
1017
1018	return $total_deleted;
1019	} // odb_delete_pingbacks()
1020
1021
1022	/********************************************************************************************
1023	* DELETE ORPHAN POSTMETA RECORDS
1024	********************************************************************************************/
1025	function odb_delete_orphans()
1026	{
1027	global $wpdb, $odb_class;
1028
1029	$meta_orphans = 0;
1030	$post_orphans = 0;
1031
1032	// LOOP THROUGH THE NETWORK
1033	for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
1034	{
1035	// DELETE POST ORPHANS (AUTO DRAFTS)
1036	$sql_delete = sprintf ("
1037	SELECT COUNT(*) cnt
1038	FROM %sposts
1039	WHERE ID NOT IN (SELECT post_id FROM %spostmeta)
1040	AND post_status = 'auto-draft'
1041	", $odb_class->odb_ms_prefixes[$i], $odb_class->odb_ms_prefixes[$i]);
1042
1043	$results = $wpdb->get_results($sql_delete);
1044
1045	$post_orphans = $results[0]->cnt;
1046
1047	if($post_orphans > 0)
1048	{ $sql_delete = sprintf ("
1049	DELETE FROM %sposts
1050	WHERE ID NOT IN (SELECT post_id FROM %spostmeta)
1051	AND post_status = 'auto-draft'
1052	", $odb_class->odb_ms_prefixes[$i], $odb_class->odb_ms_prefixes[$i]);
1053	$wpdb->get_results($sql_delete);
1054	}
1055
1056	// DELETE POSTMETA ORPHANS
1057	$sql_delete = sprintf ("
1058	SELECT COUNT(*) cnt
1059	FROM %spostmeta
1060	WHERE post_id NOT IN (SELECT ID FROM %sposts)
1061	", $odb_class->odb_ms_prefixes[$i], $odb_class->odb_ms_prefixes[$i]);
1062
1063	$results = $wpdb->get_results($sql_delete);
1064
1065	$meta_orphans = $results[0]->cnt;
1066
1067	if($meta_orphans > 0)
1068	{ $sql_delete = sprintf ("
1069	DELETE FROM %spostmeta
1070	WHERE post_id NOT IN (SELECT ID FROM %sposts)
1071	", $odb_class->odb_ms_prefixes[$i], $odb_class->odb_ms_prefixes[$i]);
1072	$wpdb->get_results($sql_delete);
1073	}
1074	} // for($i=0; $i<count($odb_class->odb_ms_prefixes); $i++)
1075
1076	return ($meta_orphans + $post_orphans);
1077	} // odb_delete_orphans()
1078
1079
1080	/********************************************************************************************
1081	* OPTIMIZE DATABASE TABLES
1082	********************************************************************************************/
1083	function odb_optimize_tables($scheduler)
1084	{
1085	global $odb_class, $wpdb;
1086
1087	$cnt = 0;
1088	for ($i=0; $i<count($odb_class->odb_tables); $i++)
1089	{
1090	if(!isset($odb_class->odb_rvg_excluded_tabs[$odb_class->odb_tables[$i][0]]))
1091	{ # TABLE NOT EXCLUDED
1092	$cnt++;
1093
1094	$sql = sprintf ("
1095	SELECT engine, (data_length + index_length) AS size, table_rows
1096	FROM information_schema.TABLES
1097	WHERE table_schema = '%s'
1098	AND table_name = '%s'
1099	", DB_NAME, $odb_class->odb_tables[$i][0]);
1100	$table_info = $wpdb->get_results($sql);
1101
1102	if($odb_class->odb_rvg_options["optimize_innodb"] == 'N' && strtolower($table_info[0]->engine) == 'innodb')
1103	{ // SKIP InnoDB tables
1104	$msg = __('InnoDB table: skipped...', 'rvg-optimize-database');
1105	}
1106	else
1107	{ $query = "OPTIMIZE TABLE ".$odb_class->odb_tables[$i][0];
1108	$result = $wpdb->get_results($query);
1109	$msg = $result[0]->Msg_text;
1110	$msg = str_replace('OK', __('<span class="odb-optimized">TABLE OPTIMIZED</span>', 'rvg-optimize-database'), $msg);
1111	$msg = str_replace('Table is already up to date', __('Table is already up to date', 'rvg-optimize-database'), $msg);
1112	$msg = str_replace('Table does not support optimize, doing recreate + analyze instead', __('<span class="odb-optimized">TABLE OPTIMIZED</span>', 'rvg-optimize-database'), $msg);
1113	}
1114
1115	if (!$scheduler)
1116	{ // NOT FROM THE SCEDULER
1117	?>
1118	<tr>
1119	<td align="right" valign="top"><?php echo $cnt?>.</td>
1120	<td valign="top" class="odb-bold"><?php echo $odb_class->odb_tables[$i][0] ?></td>
1121	<td valign="top"><?php echo $msg ?></td>
1122	<td valign="top"><?php echo $table_info[0]->engine ?></td>
1123	<td align="right" valign="top"><?php echo $table_info[0]->table_rows ?></td>
1124	<td align="right" valign="top"><?php echo $odb_class->odb_utilities_obj->odb_format_size($table_info[0]->size) ?></td>
1125	</tr>
1126	<?php
1127	} // if (!$scheduler)
1128	} // if(!$excluded)
1129	} // for ($i=0; $i<count($tables); $i++)
1130	return $cnt;
1131
1132	} // odb_optimize_tables()
1133
1134	} // ODB_Cleaner
1135	?>