rvg-optimize-database / rvg-optimize-db.php

rvg-optimize-db.php in Optimize Database after Deleting Revisions 4.0.1, at rvg-optimize-db.php

2,165 lines 74.5 KB

Raw Download Zip

1	<?php
2	/********************************************************************************************
3	*
4	* THIS FILE IS JUST HERE TO FIX THE RE-ACTIVATION ISSUE AFTER UPDATE
5	*
6	********************************************************************************************/
7	$odb_version = '3.5.1';
8	$odb_release_date = '11/01/2015';
9
10	// v3.3 - MULTISITE
11	$odb_ms_prefixes = array();
12	$odb_ms_blogids = array();
13
14
15	/********************************************************************************************
16	*
17	* ADD THE LANGUAGE SUPPORT (LOCALIZATION)
18	*
19	* Since: v2.9
20	*
21	********************************************************************************************/
22	function rvg_odb_action_init()
23	{
24	// TEXT DOMAIN v3.1.1: language files moved to the '/language' directory
25	load_plugin_textdomain('rvg-optimize-database', false, dirname(plugin_basename(__FILE__)).'/language');
26	}
27	// INIT HOOK
28	add_action('init', 'rvg_odb_action_init');
29
30
31	/********************************************************************************************
32	*
33	* ADD THE 'OPTIMIZE DATABASE' ITEM TO THE TOOLS MENU
34	*
35	********************************************************************************************/
36	function optimize_db_main()
37	{ if (function_exists('add_management_page'))
38	{ add_management_page(
39	__('Optimize Database','rvg-optimize-database'),
40	__('Optimize Database','rvg-optimize-database'),
41	'administrator',
42	'rvg-optimize-db.php',
43	'rvg_optimize_db');
44	}
45	}
46	if(rvg_odb_get_option('rvg_odb_adminmenu') != "Y") add_action('admin_menu', 'optimize_db_main');
47
48
49	/********************************************************************************************
50	*
51	* 'ICON MODE': ADD A LINK TO THE ADMIN MENU
52	*
53	* Since: v3.1.1
54	*
55	********************************************************************************************/
56	function rvg_add_menu_page()
57	{
58	if (function_exists('add_menu_page'))
59	{
60	add_menu_page(
61	__('Optimize Database','rvg-optimize-database'),
62	__('Optimize Database','rvg-optimize-database'),
63	'administrator',
64	'rvg-optimize-db.php',
65	'rvg_optimize_db',
66	plugins_url('rvg-optimize-database/images/icon.png')
67	);
68	}
69	} // rvg_add_menu_page()
70	if(rvg_odb_get_option('rvg_odb_adminmenu') == "Y") add_action('admin_menu', 'rvg_add_menu_page');
71
72
73	/********************************************************************************************
74	*
75	* ADD THE 'OPTIMIZE DB SETTINGS' ITEM TO THE SETTINGS MENU (v3.1.3)
76	*
77	********************************************************************************************/
78	function rvg_odb_admin_menu()
79	{
80	if (function_exists('add_options_page'))
81	{ add_options_page(
82	__('Optimize Database', 'rvg-optimize-database'),
83	__('Optimize Database', 'rvg-optimize-database'),
84	'manage_options',
85	'rvg_odb_admin',
86	'rvg_odb_settings_page');
87	}
88	} // rvg_odb_admin_menu()
89	if(rvg_odb_get_option('rvg_odb_adminmenu') != "Y") add_action('admin_menu', 'rvg_odb_admin_menu');
90
91
92	/********************************************************************************************
93	*
94	* 'ICON MODE': REGISTER OPTION PAGE BUT HIDE IT FROM THE ADMIN MENU (v3.1.3)
95	*
96	********************************************************************************************/
97	function register_odb_options()
98	{
99	if (function_exists('add_submenu_page'))
100	{ add_submenu_page(
101	null, // HIDE FROM MENU!
102	__('Optimize Database', 'rvg-optimize-database'),
103	__('Optimize Database', 'rvg-optimize-database'),
104	'manage_options',
105	'rvg_odb_admin',
106	'rvg_odb_settings_page'
107	);
108	}
109	} // register_odb_options()
110	if(rvg_odb_get_option('rvg_odb_adminmenu') == "Y") add_action('admin_menu', 'register_odb_options');
111
112
113	/********************************************************************************************
114	*
115	* SHOW A LINK TO THE PLUGIN SETTINGS ON THE MAIN PLUGINS PAGE
116	*
117	* Since: v3.1
118	*
119	********************************************************************************************/
120	function odb_settings_link($links)
121	{
122	array_unshift($links, '<a href="options-general.php?page=rvg_odb_admin">Settings</a>');
123	return $links;
124	} // odb_settings_link()
125	add_filter('plugin_action_links_'.plugin_basename(__FILE__), 'odb_settings_link');
126
127
128	/********************************************************************************************
129	*
130	* ADD THE '1 CLICK OPTIMIZE DATABASE' ITEM TO THE ADMIN BAR (IF ACTIVATED)
131	*
132	********************************************************************************************/
133	function rvg_odb_admin_bar()
134	{ global $wp_admin_bar;
135	if (!is_super_admin() \|\| !is_admin_bar_showing()) return;
136
137	$siteurl = site_url('/');
138	$wp_admin_bar->add_menu(
139	array(
140	'id' => 'optimize',
141	'title' => __('Optimize DB (1 click)','rvg-optimize-database'),
142	'href' => $siteurl.'wp-admin/tools.php?page=rvg-optimize-db.php&action=run' ));
143	} // rvg_odb_admin_bar()
144	$rvg_odb_adminbar = rvg_odb_get_option('rvg_odb_adminbar');
145	if($rvg_odb_adminbar == "Y") add_action('wp_before_admin_bar_render', 'rvg_odb_admin_bar');
146
147
148	/********************************************************************************************
149	*
150	* ACTIONS FOR THE SCHEDULER
151	*
152	* http://codex.wordpress.org/Plugin_API/Filter_Reference/cron_schedules
153	*
154	********************************************************************************************/
155	function rvg_extra_schedules($schedules)
156	{ // ADD A WEEKLY SCHEDULE
157	$schedules['weekly'] = array(
158	'interval' => 604800,
159	'display' => __('Once Weekly')
160	);
161	return $schedules;
162	} // rvg_extra_schedules()
163	add_filter( 'cron_schedules', 'rvg_extra_schedules' );
164
165	add_action( 'rvg_optimize_database', 'rvg_optimize_db_cron' );
166
167	// REMOVE SCHEDULED TASK WHEN DEACTIVATED
168	register_deactivation_hook( __FILE__, 'rvg_deactivate_plugin' );
169	function rvg_deactivate_plugin()
170	{ // CLEAR CURRENT SCHEDULE (IF ANY)
171	wp_clear_scheduled_hook('rvg_optimize_database');
172	wp_clear_scheduled_hook('rvg_scheduled_run');
173	} // rvg_deactivate_plugin()
174
175	// RE-SCHEDULE TASK WHEN RE-ACTIVATED (OR AFTER UPDATE)
176	register_activation_hook( __FILE__, 'rvg_activate_plugin' );
177	function rvg_activate_plugin()
178	{ $rvg_odb_schedule = rvg_odb_get_option('rvg_odb_schedule');
179	if($rvg_odb_schedule)
180	{ // PLUGIN RE-ACTIVATED: START SCHEDULER
181	if( !wp_next_scheduled( 'rvg_optimize_database' ))
182	wp_schedule_event( time(), $rvg_odb_schedule, 'rvg_optimize_database' );
183	}
184	} // rvg_activate_plugin()
185
186	register_uninstall_hook( __FILE__, 'rvg_odb_uninstall');
187	function rvg_odb_uninstall()
188	{
189	global $wpdb, $odb_ms_prefixes;
190
191	$tables = $wpdb->get_results("SHOW FULL TABLES FROM `".DB_NAME."` WHERE table_type = 'BASE TABLE'", ARRAY_N);
192
193	// DELETE ALL POSSIBLY EXCLUDED TABLES FROM THE OPTIONS
194	for ($i=0; $i<count($tables); $i++) rvg_odb_delete_option('rvg_ex_'.$tables[$i][0].'');
195
196	// DELETE THE OTHER OPTIONS
197	rvg_odb_delete_option('rvg_clear_pingbacks');
198	rvg_odb_delete_option('rvg_clear_spam');
199	rvg_odb_delete_option('rvg_clear_tags');
200	rvg_odb_delete_option('rvg_clear_transients');
201	rvg_odb_delete_option('rvg_clear_trash');
202	rvg_odb_delete_option('rvg_odb_adminbar');
203	rvg_odb_delete_option('rvg_odb_adminmenu');
204	rvg_odb_delete_option('rvg_odb_logging_on');
205	rvg_odb_delete_option('rvg_odb_number');
206	rvg_odb_delete_option('rvg_odb_schedule');
207	rvg_odb_delete_option('rvg_odb_schedulehour');
208	rvg_odb_delete_option('rvg_odb_total_savings');
209	} // rvg_odb_uninstall()
210
211
212	/********************************************************************************************
213	*
214	* GET NETWORK INFORMATION (MULTISITE)
215	*
216	* Since: v3.3
217	*
218	********************************************************************************************/
219	function rvg_odb_network_info()
220	{
221	global $wpdb, $odb_ms_prefixes, $odb_ms_blogids;
222
223	// v3.2 - GET MULTISITE INFORMATION
224	$odb_ms_prefixes[0] = $wpdb->base_prefix;
225	$odb_ms_blogids[0] = 1;
226	if (function_exists('is_multisite') && is_multisite())
227	{ $odb_blogids = $wpdb->get_col("SELECT blog_id FROM ".$wpdb->base_prefix."blogs");
228	// FOR INSTANCE: mywp_2_, mywp_3_ etc.
229	for($i=1; $i<count($odb_blogids); $i++)
230	{ $odb_ms_prefixes[$i] = $wpdb->base_prefix.$odb_blogids[$i].'_';
231	$odb_ms_blogids[$i] = $odb_blogids[$i];
232	}
233	} // if (function_exists('is_multisite') && is_multisite())
234	} // rvg_odb_network_info()
235
236
237	/********************************************************************************************
238	*
239	* GET AN OPTION FROM THE ROOT SITE OPTION TABLE
240	*
241	* Revision: v3.4.2
242	*
243	********************************************************************************************/
244	function rvg_odb_get_option($option, $default = false)
245	{
246	if(is_multisite() &&
247	function_exists('is_plugin_active_for_network') &&
248	is_plugin_active_for_network('rvg-optimize-db/rvg-optimize-db.php'))
249	{
250	return get_site_option($option, $default);
251	} else
252	{
253	return get_option($option, $default);
254	}
255	} // rvg_odb_get_option()
256
257
258	/********************************************************************************************
259	*
260	* SAVE AN OPTION TO THE ROOT SITE OPTION TABLE
261	*
262	* Revision: v3.4.2
263	*
264	********************************************************************************************/
265	function rvg_odb_update_option($option, $value)
266	{
267	if(is_multisite() &&
268	function_exists('is_plugin_active_for_network') &&
269	is_plugin_active_for_network('rvg-optimize-db/rvg-optimize-db.php'))
270	{
271	return update_site_option( $option, $value);
272	} else
273	{
274	return update_option( $option, $value);
275	}
276	} // rvg_odb_update_option()
277
278
279	/********************************************************************************************
280	*
281	* DELETE AN OPTION TO THE ROOT SITE OPTION TABLE
282	*
283	* Since: v3.4.2
284	*
285	********************************************************************************************/
286	function rvg_odb_delete_option($option)
287	{
288	if(is_multisite() && function_exists('is_plugin_active_for_network') &&
289	is_plugin_active_for_network('rvg-optimize-db/rvg-optimize-db.php'))
290	{
291	return delete_site_option($option);
292	} else
293	{
294	return delete_option($option);
295	}
296	} // rvg_odb_delete_option()
297
298
299	/********************************************************************************************
300	*
301	* CREATE THE SETTINGS PAGE
302	*
303	********************************************************************************************/
304	function rvg_odb_settings_page()
305	{
306	global $odb_version, $odb_release_date, $wpdb, $table_prefix, $odb_ms_prefixes;
307
308	// v3.5.1 - SKIP VIEWS
309	$tables = $wpdb->get_results("SHOW FULL TABLES FROM `".DB_NAME."` WHERE table_type = 'BASE TABLE'", ARRAY_N);
310
311	// v3.3 - GET NETWORK INFORMATION (MULTISITE)
312	rvg_odb_network_info();
313
314	// v3.3 - GET THE OPTIONS FROM THE TABLES OF THE MAIN SITE (IN CASE OF MULTISITE)
315	$current_datetime = Date('YmdHis');
316	$current_date = substr($current_datetime, 0, 8);
317	$current_hour = substr($current_datetime, 8, 2);
318
319	if(isset($_REQUEST['delete_log']))
320	if($_REQUEST['delete_log'] == "Y") @unlink(dirname(__FILE__).'/rvg-optimize-db-log.html');
321
322	// SAVE THE SETTINGS
323	if (isset($_POST['info_update']))
324	{
325	// v2.8.3
326	check_admin_referer('odb_action', 'odb_nonce');
327
328	// DELETE ALL EXCLUDED TABLES
329	for ($i=0; $i<count($tables); $i++)
330	rvg_odb_delete_option('rvg_ex_'.$tables[$i][0].'');
331
332	// ADD EXCLUDED TABLES
333	foreach ($_POST as $key => $value)
334	{ if(substr($key,0,3) == 'cb_')
335	rvg_odb_update_option('rvg_ex_'.substr($key,3).'', 'excluded');
336	}
337
338	if(isset($_POST['rvg_odb_number']))
339	{ $rvg_odb_number = trim($_POST['rvg_odb_number']);
340	rvg_odb_update_option('rvg_odb_number', $rvg_odb_number);
341	}
342
343	$rvg_clear_trash = 'N';
344	if(isset($_POST['rvg_clear_trash']))
345	$rvg_clear_trash = $_POST['rvg_clear_trash'];
346	rvg_odb_update_option('rvg_clear_trash', $rvg_clear_trash);
347
348	$rvg_clear_spam = 'N';
349	if(isset($_POST['rvg_clear_spam']))
350	$rvg_clear_spam = $_POST['rvg_clear_spam'];
351	rvg_odb_update_option('rvg_clear_spam', $rvg_clear_spam);
352
353	$rvg_clear_tags = 'N';
354	if(isset($_POST['rvg_clear_tags']))
355	$rvg_clear_tags = $_POST['rvg_clear_tags'];
356	rvg_odb_update_option('rvg_clear_tags', $rvg_clear_tags);
357
358	$rvg_clear_transients = 'N';
359	if(isset($_POST['rvg_clear_transients']))
360	$rvg_clear_transients = $_POST['rvg_clear_transients'];
361	rvg_odb_update_option('rvg_clear_transients', $rvg_clear_transients);
362
363	// v3.1
364	$rvg_clear_pingbacks = 'N';
365	if(isset($_POST['rvg_clear_pingbacks']))
366	$rvg_clear_pingbacks = $_POST['rvg_clear_pingbacks'];
367	rvg_odb_update_option('rvg_clear_pingbacks', $rvg_clear_pingbacks);
368
369	$rvg_odb_adminbar = 'N';
370	if(isset($_POST['rvg_odb_adminbar']))
371	$rvg_odb_adminbar = $_POST['rvg_odb_adminbar'];
372	rvg_odb_update_option('rvg_odb_adminbar', $rvg_odb_adminbar);
373
374	// v3.1.3
375	$rvg_odb_adminmenu = 'N';
376	if(isset($_POST['rvg_odb_adminmenu']))
377	$rvg_odb_adminmenu = $_POST['rvg_odb_adminmenu'];
378	rvg_odb_update_option('rvg_odb_adminmenu', $rvg_odb_adminmenu);
379
380	$rvg_odb_logging_on = 'N';
381	if(isset($_POST['rvg_odb_logging_on']))
382	$rvg_odb_logging_on = $_POST['rvg_odb_logging_on'];
383	rvg_odb_update_option('rvg_odb_logging_on', $rvg_odb_logging_on);
384
385	$rvg_odb_schedule = '';
386	if(isset($_POST['rvg_odb_schedule']))
387	$rvg_odb_schedule = $_POST['rvg_odb_schedule'];
388	rvg_odb_update_option('rvg_odb_schedule', $rvg_odb_schedule);
389
390	$rvg_odb_schedulehour = '';
391	// v3.3.1
392	if($rvg_odb_schedule == 'daily' \|\| $rvg_odb_schedule == 'weekly')
393	{
394	if(isset($_POST['rvg_odb_schedulehour']))
395	$rvg_odb_schedulehour = $_POST['rvg_odb_schedulehour'];
396	rvg_odb_update_option('rvg_odb_schedulehour', $rvg_odb_schedulehour);
397	}
398	else
399	// WIPE THE HOUR
400	rvg_odb_update_option('rvg_odb_schedulehour', '');
401
402	// CLEAR CURRENT SCHEDULE (IF ANY)
403	wp_clear_scheduled_hook('rvg_optimize_database');
404
405	// HAS TO BE SCHEDULED
406	if($rvg_odb_schedule != '')
407	if( !wp_next_scheduled( 'rvg_optimize_database' ))
408	{
409	$time = 0;
410	if($rvg_odb_schedulehour == '')
411	// 'hourly', 'twicedaily'
412	$time = time();
413	else
414	{
415	// 'daily', 'weekly'
416	if($rvg_odb_schedulehour <= $current_hour)
417	// NEXT RUN TOMORROW
418	$newdatetime = date('YmdHis', strtotime($current_date.$rvg_odb_schedulehour.'0000'.' + 1 day'));
419	else
420	// NEXT RUN TODAY
421	$newdatetime = $current_date.$rvg_odb_schedulehour.'0000';
422	// DATE TO UNIX TIMESTAMP (EPOCH)
423	$time = strtotime($newdatetime);
424	}
425	// SCHEDULE THE EVENT
426	wp_schedule_event( $time, $rvg_odb_schedule, 'rvg_optimize_database' );
427	}
428
429	// UPDATED MESSAGE
430	echo "<div class='updated odb-bold'><p>".
431	__('Optimize Database after Deleting Revisions SETTINGS UPDATED','rvg-optimize-database').
432	" - ";
433	_e('Click <a href="tools.php?page=rvg-optimize-db.php" class="odb-bold">HERE</a> to run the optimization','rvg-optimize-database');
434	echo "</p></div>";
435	} // if (isset($_POST['info_update']))
436
437	$rvg_odb_number = rvg_odb_get_option('rvg_odb_number');
438	if(!$rvg_odb_number) $rvg_odb_number = '0';
439
440	$rvg_clear_trash = rvg_odb_get_option('rvg_clear_trash');
441	if(!$rvg_clear_trash) $rvg_clear_trash = 'N';
442
443	$rvg_clear_spam = rvg_odb_get_option('rvg_clear_spam');
444	if(!$rvg_clear_spam) $rvg_clear_spam = 'N';
445
446	$rvg_clear_tags = rvg_odb_get_option('rvg_clear_tags');
447	if(!$rvg_clear_tags) $rvg_clear_tags = 'N';
448
449	$rvg_clear_transients = rvg_odb_get_option('rvg_clear_transients');
450	if(!$rvg_clear_transients) $rvg_clear_transients = 'N';
451
452	// v3.1
453	$rvg_clear_pingbacks = rvg_odb_get_option('rvg_clear_pingbacks');
454	if(!$rvg_clear_pingbacks) $rvg_clear_pingbacks = 'N';
455
456	$rvg_odb_logging_on = rvg_odb_get_option('rvg_odb_logging_on');
457	if(!$rvg_odb_logging_on) $rvg_odb_logging_on = 'N';
458
459	$rvg_odb_schedule = rvg_odb_get_option('rvg_odb_schedule');
460	if(!$rvg_odb_schedule) $rvg_odb_schedule = '';
461
462	$rvg_odb_schedulehour = rvg_odb_get_option('rvg_odb_schedulehour');
463
464	$rvg_odb_adminbar = rvg_odb_get_option('rvg_odb_adminbar');
465	if(!$rvg_odb_adminbar) $rvg_odb_adminbar = 'N';
466
467	$rvg_odb_adminmenu = rvg_odb_get_option('rvg_odb_adminmenu');
468	if(!$rvg_odb_adminmenu) $rvg_odb_adminmenu = 'N';
469
470	?>
471	<?php /?>v3.5<?php /?>
472	<link rel="stylesheet" type="text/css" media="all" href="<?php echo plugin_dir_url(__FILE__).'css/style.css'?>" />
473	<script type="text/javascript">
474	function schedule_changed()
475	{ // v3.1.4
476	if(jQuery("#rvg_odb_schedule").val() == 'daily' \|\| jQuery("#rvg_odb_schedule").val() == 'weekly')
477	jQuery("#schedulehour").show();
478	else
479	jQuery("#schedulehour").hide();
480	}
481	</script>
482	<?php
483	if($rvg_odb_adminbar == 'Y') $rvg_odb_adminbar_checked = ' checked="checked"'; else $rvg_odb_adminbar_checked = '';
484	if($rvg_odb_adminmenu == 'Y') $rvg_odb_adminmenu_checked = ' checked="checked"'; else $rvg_odb_adminmenu_checked = '';
485	if($rvg_clear_trash == 'Y') $rvg_clear_trash_checked = ' checked="checked"'; else $rvg_clear_trash_checked = '';
486	if($rvg_clear_spam == 'Y') $rvg_clear_spam_checked = ' checked="checked"'; else $rvg_clear_spam_checked = '';
487	if($rvg_clear_tags == 'Y') $rvg_clear_tags_checked = ' checked="checked"'; else $rvg_clear_tags_checked = '';
488	if($rvg_clear_transients == 'Y') $rvg_clear_transients_checked = ' checked="checked"'; else $rvg_clear_transients_checked = '';
489	// v3.1
490	if($rvg_clear_pingbacks == 'Y') $rvg_clear_pingbacks_checked = ' checked="checked"'; else $rvg_clear_pingbacks_checked = '';
491	if($rvg_odb_logging_on == 'Y') $rvg_odb_logging_on_checked = ' checked="checked"'; else $rvg_odb_logging_on_checked = '';
492	?>
493	<div id="odb-options-form">
494	<form name="options" method="post" action="">
495	<?php // v2.8.3 ?>
496	<?php wp_nonce_field( 'odb_action','odb_nonce' ); ?>
497	<div id="odb-options-wrap">
498	<div id="odb-options-opening">
499	<div class="odb-title-bar">
500	<h2>
501	<?php _e('Optimize Database after Deleting Revisions','rvg-optimize-database');?>
502	</h2>
503	</div>
504	<p class="odb-bold">'<span class="odb-italic">Optimize Database after Deleting Revisions</span> '
505	<?php _e('is an one-click plugin to clean and optimize your WordPress database','rvg-optimize-database');?>
506	</p>
507	<span class="odb-bold">
508	<?php _e( 'Plugin version', 'rvg-optimize-database' ); ?>: v<?php echo $odb_version?> [<?php echo $odb_release_date?>] - <a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank">
509	<?php _e( 'Plugin page', 'rvg-optimize-database' ); ?></a> - <a href="http://wordpress.org/plugins/rvg-optimize-database/" target="_blank">
510	<?php _e( 'Download page', 'rvg-optimize-database' ); ?></a> - <a href="http://rvg.cage.nl/" target="_blank">
511	<?php _e( 'Author', 'rvg-optimize-database' ); ?></a> - <a href="http://cagewebdev.com/" target="_blank">
512	<?php _e( 'Company', 'rvg-optimize-database' ); ?></a> - <a href="http://cagewebdev.com/index.php/donations-odb/" target="_blank">
513	<?php _e( 'Donation page', 'rvg-optimize-database' ); ?></a><br />
514	</span>
515	</div>
516	<div id="odb-options-settings">
517	<div class="odb-title-bar">
518	<h2>
519	<?php _e('Settings','rvg-optimize-database');?>
520	</h2>
521	</div>
522	<br />
523	<br />
524	<table border="0" cellspacing="2" cellpadding="5" class="editform" align="center">
525	<tr>
526	<td width="50%" align="right" valign="top"><span class="odb-bold">
527	<?php _e('Maximum number of - most recent - revisions<br />to keep per post / page','rvg-optimize-database');?>
528	</span></td>
529	<td width="50%" valign="top"><input type="text" size="5" name="rvg_odb_number" id="rvg_odb_number" value="<?php echo $rvg_odb_number?>" class="odb-bold odb-blue" />
530
531	<?php _e('(\'0\' means: delete <u>ALL</u> revisions)','rvg-optimize-database');?></td>
532	</tr>
533	<tr>
534	<td width="50%" align="right" valign="top"><span class="odb-bold">
535	<?php _e('Delete trashed items','rvg-optimize-database');?>
536	</span></td>
537	<td width="50%" valign="top"><input name="rvg_clear_trash" type="checkbox" value="Y" <?php echo $rvg_clear_trash_checked?> /></td>
538	</tr>
539	<tr>
540	<td width="50%" align="right" valign="top"><span class="odb-bold">
541	<?php _e('Delete spammed items','rvg-optimize-database');?>
542	</span></td>
543	<td width="50%" valign="top"><input name="rvg_clear_spam" type="checkbox" value="Y" <?php echo $rvg_clear_spam_checked?> /></td>
544	</tr>
545	<tr>
546	<td width="50%" align="right" valign="top"><span class="odb-bold">
547	<?php _e('Delete unused tags','rvg-optimize-database');?>
548	</span></td>
549	<td width="50%" valign="top"><input name="rvg_clear_tags" type="checkbox" value="Y" <?php echo $rvg_clear_tags_checked?> /></td>
550	</tr>
551	<tr>
552	<td width="50%" align="right" valign="top"><span class="odb-bold">
553	<?php _e('Delete expired transients','rvg-optimize-database');?>
554	</span></td>
555	<td width="50%" valign="top"><input name="rvg_clear_transients" type="checkbox" value="Y" <?php echo $rvg_clear_transients_checked?> /></td>
556	</tr>
557	<tr>
558	<td width="50%" align="right" valign="top"><span class="odb-bold">
559	<?php _e('Delete pingbacks and trackbacks','rvg-optimize-database');?>
560	</span></td>
561	<td width="50%" valign="top"><input name="rvg_clear_pingbacks" type="checkbox" value="Y" <?php echo $rvg_clear_pingbacks_checked?> /></td>
562	</tr>
563	<tr>
564	<td width="50%" align="right" valign="top"><span class="odb-bold">
565	<?php _e('Keep a log','rvg-optimize-database');?>
566	</span></td>
567	<td width="50%" valign="top"><input name="rvg_odb_logging_on" type="checkbox" value="Y" <?php echo $rvg_odb_logging_on_checked?> /></td>
568	</tr>
569	<tr>
570	<td width="50%" align="right"><span class="odb-bold">
571	<?php _e('Scheduler','rvg-optimize-database');?>
572	</span></td>
573	<td width="50%"><select name="rvg_odb_schedule" id="rvg_odb_schedule" class="odb-schedule-select" onchange="schedule_changed();">
574	<option selected="selected" value="">
575	<?php _e('NOT SCHEDULED','rvg-optimize-database');?>
576	</option>
577	<option value="hourly">
578	<?php _e('run optimization HOURLY','rvg-optimize-database');?>
579	</option>
580	<option value="twicedaily">
581	<?php _e('run optimization TWICE A DAY','rvg-optimize-database');?>
582	</option>
583	<option value="daily">
584	<?php _e('run optimization DAILY','rvg-optimize-database');?>
585	</option>
586	<option value="weekly">
587	<?php _e('run optimization WEEKLY','rvg-optimize-database');?>
588	</option>
589	<?php /?><option value="test">run optimization TEST</option><?php /?>
590	</select>
591	<script type="text/javascript">
592	jQuery("#rvg_odb_schedule").val('<?php echo $rvg_odb_schedule; ?>');
593	</script>
594	<span id="schedulehour" class="odb-schedulehour"> <span class="odb-bold">
595	<?php _e('Time','rvg-optimize-database');?>
596	</span>
597	<select name="rvg_odb_schedulehour" id="rvg_odb_schedulehour" class="odb-schedulehour-select">
598	<?php
599	for($i=0; $i<=23; $i++)
600	{ if($i<10) $i = '0'.$i;
601	?>
602	<option value="<?php echo $i?>"><?php echo $i.':00 '.__('hrs','rvg-optimize-database')?></option>
603	<?php
604	}
605	?>
606	</select>
607	<script type="text/javascript">
608	jQuery("#rvg_odb_schedulehour").val('<?php echo $rvg_odb_schedulehour; ?>');
609	</script>
610	</span>
611	<script type="text/javascript">schedule_changed();</script></td>
612	</tr>
613	<tr>
614	<td align="right" valign="top"><span class="odb-bold">
615	<?php _e('Show \'1-click\' link in Admin Bar','rvg-optimize-database');?>
616	</span></td>
617	<td valign="top"><input name="rvg_odb_adminbar" type="checkbox" value="Y" <?php echo $rvg_odb_adminbar_checked?> />
618	<?php _e('(change will be visible after loading the next page)','rvg-optimize-database');?></td>
619	</tr>
620	<tr>
621	<td align="right" valign="top"><span class="odb-bold">
622	<?php _e('Show an icon in the Admin Menu','rvg-optimize-database');?>
623	</span></td>
624	<td valign="top"><input name="rvg_odb_adminmenu" type="checkbox" value="Y" <?php echo $rvg_odb_adminmenu_checked?> />
625	<?php _e('(change will be visible after loading the next page)','rvg-optimize-database');?></td>
626	</tr>
627	</table>
628	<br />
629	<div align="center"> <span class="odb-bold">
630	<?php _e('EXCLUDE DATABASE TABLES FROM OPTIMIZATION: <span class="odb-underline-red">CHECKED</span> TABLES <span class="odb-underline-red">WON\'T</span> BE OPTIMIZED!</span>','rvg-optimize-database');?>
631	<br />
632	<a href="javascript:;" onclick="jQuery('[id^=cb_]').attr('checked',true);">
633	<?php _e('check all tables','rvg-optimize-database');?>
634	</a> \| <a href="javascript:;" onclick="jQuery('[id^=cb_]').attr('checked',false);">
635	<?php _e('uncheck all tables','rvg-optimize-database');?>
636	</a> \| <a href="javascript:;" onclick="jQuery(':not([id^=cb_<?php echo $odb_ms_prefixes[0]; ?>])').filter('[id^=cb_]').attr('checked',true);">
637	<?php _e('check all NON-WordPress tables','rvg-optimize-database');?>
638	</a></span>
639	<div id="odb-options-tables-container">
640	<div id="odb-options-tables-wrapper">
641	<?php
642	for ($i=0; $i<count($tables); $i++)
643	{ $class = '';
644	// WORDPRESS TABLE? v3.3 (MULTISITE)
645	for($j=0; $j<count($odb_ms_prefixes); $j++)
646	if(substr($tables[$i][0], 0, strlen($odb_ms_prefixes[$j])) == $odb_ms_prefixes[$j]) $class = ' odb-wp-table';
647	$cb_checked = '';
648	$excluded = rvg_odb_get_option('rvg_ex_'.$tables[$i][0].'');
649	if($excluded == 'excluded') $cb_checked = ' checked';
650	?>
651	<div class="odb-options-table<?php echo $class;?>" title="<?php echo $tables[$i][0];?>">
652	<input id="cb_<?php echo $tables[$i][0];?>" name="cb_<?php echo $tables[$i][0];?>" type="checkbox" value="1"<?php echo $cb_checked; ?> />
653	<?php echo $tables[$i][0];?></span></div>
654	<?php
655	}
656	?>
657	</div>
658	<!-- odb-options-tables-wrapper -->
659	<div align="center">
660	<p>
661	<input class="button-primary button-large" type='submit' name='info_update' value='<?php _e('Save Settings','rvg-optimize-database');?>' class="odb-bold" />
662
663	<input class="button odb-normal" type="button" name="optimizer" value="<?php _e('Go To Optimizer','rvg-optimize-database');?>" onclick="self.location='tools.php?page=rvg-optimize-db.php'" />
664	</p>
665	</div>
666	<!-- odb-options-buttons -->
667	</div>
668	<!-- odb-options-tables-container -->
669	</div>
670	<!-- center -->
671	</div>
672	<!-- odb-options-settings -->
673	</div>
674	<!-- odb-options-wrap -->
675	</form>
676	</div>
677	<!-- odb-options-form -->
678	<?php
679	} // rvg_odb_settings_page()
680
681
682	/********************************************************************************************
683	*
684	* MAIN FUNCTION
685	* FOR DELETING UNWANTED ITEMS AND OPTIMIZING DATABASE TABLES
686	*
687	********************************************************************************************/
688	function rvg_optimize_db()
689	{
690	global $wpdb, $odb_version, $odb_release_date, $odb_ms_prefixes;
691
692	// v3.3 - GET NETWORK INFORMATION (MULTISITE)
693	rvg_odb_network_info();
694
695	// PAGE LOAD COUNTER
696	$time = microtime();
697	$time = explode(' ', $time);
698	$time = $time[1] + $time[0];
699	$odb_start_time = $time;
700
701	$current_hour = Date('H:i');
702
703	if(isset($_REQUEST['action']))
704	if($_REQUEST['action'] == "delete_log")
705	@unlink(dirname(__FILE__).'/rvg-optimize-db-log.html');
706
707	/****************************************************************************************
708	*
709	* DELETE UNWANTED ITEMS
710	*
711	****************************************************************************************/
712
713	// GET SETTINGS AND SET DEFAULT VALUES
714	$max_revisions = rvg_odb_get_option('rvg_odb_number');
715	if(!$max_revisions)
716	{ $max_revisions = 0;
717	rvg_odb_update_option('rvg_odb_number', $max_revisions);
718	}
719
720	$clear_trash = rvg_odb_get_option('rvg_clear_trash');
721	if(!$clear_trash)
722	{ $clear_trash = 'N';
723	rvg_odb_update_option('rvg_clear_trash', $clear_trash);
724	}
725	$clear_trash_yn = ($clear_trash == 'N') ? __('NO','rvg-optimize-database') : __('YES','rvg-optimize-database');
726
727	$clear_spam = rvg_odb_get_option('rvg_clear_spam');
728	if(!$clear_spam)
729	{ $clear_spam = 'N';
730	rvg_odb_update_option('rvg_clear_spam', $clear_spam);
731	}
732	$clear_spam_yn = ($clear_spam == 'N') ? __('NO','rvg-optimize-database') : __('YES','rvg-optimize-database');
733
734	$clear_tags = rvg_odb_get_option('rvg_clear_tags');
735	if(!$clear_tags)
736	{ $clear_tags = 'N';
737	rvg_odb_update_option('rvg_clear_tags', $clear_tags);
738	}
739	$clear_tags_yn = ($clear_tags == 'N') ? __('NO','rvg-optimize-database') : __('YES','rvg-optimize-database');
740
741	$clear_transients = rvg_odb_get_option('rvg_clear_transients');
742	if(!$clear_transients)
743	{ $clear_transients = 'N';
744	rvg_odb_update_option('rvg_clear_transients', $clear_transients);
745	}
746	$clear_transients_yn = ($clear_transients == 'N') ? __('NO','rvg-optimize-database') : __('YES','rvg-optimize-database');
747
748	// v3.1
749	$clear_pingbacks = rvg_odb_get_option('rvg_clear_pingbacks');
750	if(!$clear_pingbacks)
751	{ $clear_pingbacks = 'N';
752	rvg_odb_update_option('rvg_clear_pingbacks', $clear_pingbacks);
753	}
754	$clear_pingbacks_yn = ($clear_pingbacks == 'N') ? __('NO','rvg-optimize-database') : __('YES','rvg-optimize-database');
755
756	$rvg_odb_logging_on = rvg_odb_get_option('rvg_odb_logging_on');
757	if(!$rvg_odb_logging_on)
758	{ $rvg_odb_logging_on = 'N';
759	rvg_odb_update_option('rvg_odb_logging_on', $rvg_odb_logging_on);
760	}
761	$rvg_odb_logging_on_yn = ($rvg_odb_logging_on == 'N') ? __('NO','rvg-optimize-database') : __('YES','rvg-optimize-database');
762
763	$rvg_odb_schedule = rvg_odb_get_option('rvg_odb_schedule');
764	if(!$rvg_odb_schedule)
765	{ $rvg_odb_schedule = '';
766	rvg_odb_update_option('rvg_odb_schedule', $rvg_odb_schedule);
767	}
768
769	if($rvg_odb_schedule == 'hourly')
770	$rvg_odb_schedule_txt = __('ONCE HOURLY','rvg-optimize-database');
771	else if($rvg_odb_schedule == 'twicedaily')
772	$rvg_odb_schedule_txt = __('TWICE DAILY','rvg-optimize-database');
773	else if($rvg_odb_schedule == 'daily')
774	$rvg_odb_schedule_txt = __('ONCE DAILY','rvg-optimize-database');
775	else if($rvg_odb_schedule == 'weekly')
776	$rvg_odb_schedule_txt = __('ONCE WEEKLY','rvg-optimize-database');
777	else if($rvg_odb_schedule == 'test')
778	$rvg_odb_schedule_txt = 'TEST';
779
780	$nextrun = '';
781	if(!isset($rvg_odb_schedule_txt))
782	{ $rvg_odb_schedule_txt = __('NOT SCHEDULED','rvg-optimize-database');
783	}
784	else
785	{ $timestamp = wp_next_scheduled('rvg_optimize_database');
786	$nextrun = Date('M j, Y @ H:i', $timestamp);
787	}
788
789	$total_savings = rvg_odb_get_option('rvg_odb_total_savings');
790
791	$log_url = plugins_url().'/rvg-optimize-database/rvg-optimize-db-log.html';
792
793	$sql = "
794	SELECT COUNT(*) cnt
795	FROM $wpdb->options
796	WHERE option_name LIKE 'rvg_ex_%'
797	";
798	$results = $wpdb->get_results($sql);
799	$number_excluded = $results[0]->cnt;
800	?>
801	<?php /?>v3.5<?php /?>
802	<link rel="stylesheet" type="text/css" media="all" href="<?php echo plugin_dir_url(__FILE__).'css/style.css'?>" />
803	<div class="odb-padding-left">
804	<div class="odb-title-bar">
805	<h2>
806	<?php _e('Optimize your WordPress Database','rvg-optimize-database');?>
807	</h2>
808	</div>
809	<?php
810	if(isset($_REQUEST['action']))
811	if($_REQUEST['action'] == "delete_log")
812	echo '<div class="updated odb-updated"><p><span class="odb-bold">Optimize Database after Deleting Revisions - LOG FILE DELETED</span></p></div>';
813	?>
814	<p>
815	<span class="odb-bold">
816	<?php _e( 'Plugin version', 'rvg-optimize-database' ); ?>: v<?php echo $odb_version?> [<?php echo $odb_release_date?>] - <a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank">
817	<?php _e( 'Plugin page', 'rvg-optimize-database' ); ?></a> - <a href="http://wordpress.org/plugins/rvg-optimize-database/" target="_blank">
818	<?php _e( 'Download page', 'rvg-optimize-database' ); ?></a> - <a href="http://rvg.cage.nl/" target="_blank">
819	<?php _e( 'Author', 'rvg-optimize-database' ); ?></a> - <a href="http://cagewebdev.com/" target="_blank">
820	<?php _e( 'Company', 'rvg-optimize-database' ); ?></a> - <a href="http://cagewebdev.com/index.php/donations-odb/" target="_blank">
821	<?php _e( 'Donation page', 'rvg-optimize-database' ); ?></a><br />
822	</span>
823	</p>
824	<p>
825	<?php _e('Current settings','rvg-optimize-database');?>:<br />
826	<span class="odb-bold">
827	<?php _e('Maximum number of - most recent - revisions to keep per post / page','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $max_revisions?></span><br />
828	<span class="odb-bold">
829	<?php _e('Delete trashed items','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $clear_trash_yn?></span><br />
830	<span class="odb-bold">
831	<?php _e('Delete spammed items','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $clear_spam_yn?></span><br />
832	<span class="odb-bold">
833	<?php _e('Delete unused tags','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $clear_tags_yn?></span><br />
834	<span class="odb-bold">
835	<?php _e('Delete expired transients','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $clear_transients_yn?></span><br />
836	<span class="odb-bold">
837	<?php _e('Delete pingbacks and trackbacks','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $clear_pingbacks_yn?></span><br />
838	<span class="odb-bold">
839	<?php _e('Keep a log','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $rvg_odb_logging_on_yn?></span><br />
840	<span class="odb-bold">
841	<?php _e('Number of excluded tables','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $number_excluded?></span><br />
842	<span class="odb-bold">
843	<?php _e('Scheduler','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $rvg_odb_schedule_txt?></span>
844	<?php
845	if($nextrun)
846	{
847	?>
848	<br />
849	<span class="odb-bold">
850	<?php _e('Next scheduled run','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo $nextrun?> hrs (current server time: <?php echo $current_hour?>)</span>
851	<?php
852	}
853	if($total_savings)
854	{
855	?>
856	<br />
857	<span class="odb-bold">
858	<?php _e('Total savings since the first run','rvg-optimize-database');?>:</span> <span class="odb-bold odb-blue"><?php echo rvg_format_size($total_savings); ?></span>
859	<?php
860	}
861	?>
862
863	<p class="submit">
864	<input class="button odb-normal" type="button" name="change_options" value="<?php _e('Change Settings','rvg-optimize-database');?>" onclick="self.location='options-general.php?page=rvg_odb_admin'" />
865	<?php
866	if(file_exists(dirname(__FILE__).'/rvg-optimize-db-log.html'))
867	{
868	?>
869
870	<input class="button odb-normal" type="button" name="view_log" value="<?php _e('View Log File','rvg-optimize-database');?>" onclick="window.open('<?php echo $log_url?>')" />
871
872	<input class="button odb-normal" type="button" name="delete_log" value="<?php _e('Delete Log File','rvg-optimize-database');?>" onclick="self.location='tools.php?page=rvg-optimize-db.php&action=delete_log'" />
873	<?php
874	}
875	$action = '';
876	if(isset($_REQUEST['action'])) $action = $_REQUEST['action'];
877	if($action != 'run')
878	{
879	?>
880
881	<input class="button-primary button-large" type="button" name="start_optimization" value="<?php _e('Start Optimization','rvg-optimize-database');?>" onclick="self.location='tools.php?page=rvg-optimize-db.php&action=run'" class="odb-bold" />
882	<?php
883	}
884	?>
885	</p>
886	</div>
887	<?php
888	$action = '';
889	if(isset($_REQUEST['action'])) $action = $_REQUEST['action'];
890	if($action != 'run') return;
891	?>
892	<div class="odb-title-bar">
893	<h2>
894	<?php _e('Cleaning Database','rvg-optimize-database');?>
895	</h2>
896	</div>
897	<br />
898	<br />
899	<?php
900	// GET THE SIZE OF THE DATABASE BEFORE OPTIMIZATION
901	$start_size = rvg_get_db_size();
902
903	// TIMESTAMP FOR LOG FILE
904	$current_datetime = Date('m/d/YH:i:s');
905	$log_arr = array("time" => substr($current_datetime, 0, 10).'<br />'.substr($current_datetime,10));
906
907	// FIND REVISIONS
908	$results = rvg_get_revisions($max_revisions);
909
910	$total_deleted = 0;
911	if(count($results)>0)
912	{ // WE HAVE REVISIONS TO DELETE!
913	?>
914	<table border="0" cellspacing="8" cellpadding="2" class="odb-result-table">
915	<tr>
916	<td colspan="4"><div class="odb-found">
917	<?php _e('DELETED REVISIONS','rvg-optimize-database');?>
918	</div></td>
919	</tr>
920	<tr>
921	<th align="right" class="odb-border-bottom">#</th>
922	<th align="left" class="odb-border-bottom"><?php _e('prefix', 'rvg-optimize-database');?></th>
923	<th align="left" class="odb-border-bottom"><?php _e('post / page','rvg-optimize-database');?></th>
924	<th align="left" class="odb-border-bottom"><?php _e('revision date','rvg-optimize-database');?></th>
925	<th align="right" class="odb-border-bottom"><?php _e('revisions deleted','rvg-optimize-database');?></th>
926	</tr>
927	<?php
928	// LOOP THROUGH THE REVISIONS AND DELETE THEM
929	$total_deleted = rvg_delete_revisions($results, true, $max_revisions);
930	?>
931	<tr>
932	<td colspan="4" align="right" class="odb-border-top odb-bold"><?php _e('total number of revisions deleted','rvg-optimize-database');?></td>
933	<td align="right" class="odb-border-top odb-bold"><?php echo $total_deleted?></td>
934	</tr>
935	</table>
936	<?php
937	}
938	else
939	{
940	?>
941	<div class="odb-not-found">
942	<?php _e('No REVISIONS found to delete','rvg-optimize-database');?>
943	</div>
944	<?php
945	} // if(count($results)>0)
946
947	// NUMBER OF DELETED REVISIONS FOR LOG FILE
948	$log_arr["revisions"] = $total_deleted;
949
950
951	/****************************************************************************************
952	*
953	* DELETE TRASHED ITEMS
954	*
955	****************************************************************************************/
956	if($clear_trash == 'Y')
957	{
958	// GET TRASHED POSTS / PAGES AND COMMENTS
959	$results = rvg_get_trash();
960
961	$total_deleted = 0;
962	if(count($results)>0)
963	{ // WE HAVE TRASH TO DELETE!
964	?>
965	<table border="0" cellspacing="8" cellpadding="2" class="odb-result-table">
966	<tr>
967	<td colspan="4"><div class="odb-found">
968	<?php _e('DELETED TRASHED ITEMS','rvg-optimize-database');?>
969	</div></td>
970	</tr>
971	<tr>
972	<th align="right" class="odb-border-bottom">#</th>
973	<th align="left" class="odb-border-bottom"><?php _e('prefix', 'rvg-optimize-database');?></th>
974	<th align="left" class="odb-border-bottom"><?php _e('type','rvg-optimize-database');?></th>
975	<th align="left" class="odb-border-bottom"><?php _e('IP address / title','rvg-optimize-database');?></th>
976	<th align="left" nowrap="nowrap" class="odb-border-bottom"><?php _e('date','rvg-optimize-database');?></th>
977	</tr>
978	<?php
979	// LOOP THROUGH THE TRASHED ITEMS AND DELETE THEM
980	$total_deleted = rvg_delete_trash($results, true);
981	?>
982	<tr>
983	<td colspan="4" align="right" class="odb-border-top odb-bold"><?php _e('total number of trashed items deleted','rvg-optimize-database');?></td>
984	<td align="right" class="odb-border-top odb-bold"><?php echo $total_deleted?></td>
985	</tr>
986	</table>
987	<?php
988	}
989	else
990	{
991	?>
992	<div class="odb-not-found">
993	<?php _e('No TRASHED ITEMS found to delete','rvg-optimize-database');?>
994	</div>
995	<?php
996	} // if(count($results)>0)
997
998	// NUMBER OF DELETED TRASH FOR LOG FILE
999	$log_arr["trash"] = $total_deleted;
1000	} // if($clear_trash == 'Y')
1001
1002
1003	/****************************************************************************************
1004	*
1005	* DELETE SPAMMED ITEMS
1006	*
1007	****************************************************************************************/
1008	if($clear_spam == 'Y')
1009	{
1010	// GET SPAMMED COMMENTS
1011	$results = rvg_get_spam();
1012
1013	$total_deleted = 0;
1014	if(count($results)>0)
1015	{ // WE HAVE SPAM TO DELETE!
1016	?>
1017	<table border="0" cellspacing="8" cellpadding="2" class="odb-result-table">
1018	<tr>
1019	<td colspan="4"><div class="odb-found">
1020	<?php _e('DELETEED SPAMMED ITEMS','rvg-optimize-database');?>
1021	</div></td>
1022	</tr>
1023	<tr>
1024	<th align="right" class="odb-border-bottom">#</th>
1025	<th align="left" class="odb-border-bottom"><?php _e('prefix', 'rvg-optimize-database');?></th>
1026	<th align="left" class="odb-border-bottom"><?php _e('comment author','rvg-optimize-database');?></th>
1027	<th align="left" class="odb-border-bottom"><?php _e('comment author email','rvg-optimize-database');?></th>
1028	<th align="left" nowrap="nowrap" class="odb-border-bottom"><?php _e('comment date','rvg-optimize-database');?></th>
1029	</tr>
1030	<?php
1031	// LOOP THROUGH SPAMMED ITEMS AND DELETE THEM
1032	$total_deleted = rvg_delete_spam($results, true);
1033	?>
1034	<tr>
1035	<td colspan="4" align="right" class="odb-border-top odb-bold"><?php _e('total number of spammed items deleted','rvg-optimize-database');?></td>
1036	<td align="right" class="odb-border-top odb-bold"><?php echo $total_deleted?></td>
1037	</tr>
1038	</table>
1039	<?php
1040	}
1041	else
1042	{
1043	?>
1044	<div class="odb-not-found">
1045	<?php _e('No SPAMMED ITEMS found to delete','rvg-optimize-database');?>
1046	</div>
1047	<?php
1048	} // if(count($results)>0)
1049
1050	} // if($clear_spam == 'Y')
1051
1052	// NUMBER OF SPAM DELETED FOR LOG FILE
1053	$log_arr["spam"] = $total_deleted;
1054
1055
1056	/****************************************************************************************
1057	*
1058	* DELETE UNUSED TAGS
1059	*
1060	****************************************************************************************/
1061	if($clear_tags == 'Y')
1062	{
1063	// DELETE UNUSED TAGS
1064	$total_deleted = rvg_delete_tags();
1065	if($total_deleted>0)
1066	{ // TAGS DELETED
1067	?>
1068	<div class="odb-found-number">
1069	<?php _e('NUMBER OF UNUSED TAGS DELETED','rvg-optimize-database');?>
1070	: <span class="odb-blue"><?php echo $total_deleted;?></span> </div>
1071	<?php
1072	}
1073	else
1074	{
1075	?>
1076	<div class="odb-not-found">
1077	<?php _e('No UNUSED TAGS found to delete','rvg-optimize-database');?>
1078	</div>
1079	<?php
1080	} // if(count($results)>0)
1081
1082	} // if($clear_tags == 'Y')
1083
1084	// NUMBER OF tags DELETED FOR LOG FILE
1085	$log_arr["tags"] = $total_deleted;
1086
1087
1088	/****************************************************************************************
1089	*
1090	* DELETE EXPIRED TRANSIENTS
1091	*
1092	****************************************************************************************/
1093	if($clear_transients == 'Y')
1094	{
1095	// DELETE UNUSED TAGS
1096	$total_deleted = rvg_delete_transients();
1097	if($total_deleted>0)
1098	{ // TRANSIENTS DELETED
1099	?>
1100	<div class="odb-found-number">
1101	<?php _e('NUMBER OF EXPIRED TRANSIENTS DELETED','rvg-optimize-database');?>
1102	: <span class="odb-blue"><?php echo $total_deleted;?></span> </div>
1103	<?php
1104	}
1105	else
1106	{
1107	?>
1108	<div class="odb-not-found">
1109	<?php _e('No EXPIRED TRANSIENTS found to delete','rvg-optimize-database');?>
1110	</div>
1111	<?php
1112	} // if(count($results)>0)
1113
1114	} // if($clear_transients == 'Y')
1115
1116	// NUMBER OF transients DELETED FOR LOG FILE
1117	$log_arr["transients"] = $total_deleted;
1118
1119
1120	/****************************************************************************************
1121	*
1122	* DELETE PINGBACKS AND TRACKBACKS (v3.1)
1123	*
1124	****************************************************************************************/
1125	if($clear_pingbacks == 'Y')
1126	{
1127	// DELETE UNUSED TAGS
1128	$total_deleted = rvg_delete_pingbacks();
1129	if($total_deleted>0)
1130	{ // PINGBACKS / TRACKBACKS DELETED
1131	?>
1132	<div class="odb-found-number">
1133	<?php _e('NUMBER OF PINGBACKS AND TRACKBACKS DELETED','rvg-optimize-database');?>
1134	: <span class="odb-blue"><?php echo $total_deleted;?></span> </div>
1135	<?php
1136	}
1137	else
1138	{
1139	?>
1140	<div class="odb-not-found">
1141	<?php _e('No PINGBACKS nor TRACKBACKS found to delete','rvg-optimize-database');?>
1142	</div>
1143	<?php
1144	} // if(count($results)>0)
1145
1146	} // if($clear_pingbacks == 'Y')
1147
1148	// NUMBER OF pingbacks / trackbacks DELETED (FOR LOG FILE)
1149	$log_arr["pingbacks"] = $total_deleted;
1150
1151
1152	/****************************************************************************************
1153	*
1154	* DELETE ORPHANS
1155	*
1156	****************************************************************************************/
1157	$total_deleted = rvg_delete_orphans(true);
1158	if($total_deleted > 0)
1159	{
1160	?>
1161	<div class="odb-found-number">
1162	<?php _e('NUMBER OF POSTMETA ORPHANS DELETED','rvg-optimize-database');?>
1163	: <span class="odb-blue"><?php echo $total_deleted;?></span> </div>
1164	<?php
1165	}
1166	else
1167	{
1168	?>
1169	<div class="odb-not-found">
1170	<?php _e('No POSTMETA ORPHANS found to delete','rvg-optimize-database');?>
1171	</div>
1172	<?php
1173	}
1174	// FOR LOG FILE
1175	$log_arr["orphans"] = $total_deleted;
1176
1177
1178	/****************************************************************************************
1179	*
1180	* OPTIMIZE DATABASE TABLES
1181	*
1182	****************************************************************************************/
1183	?>
1184	<div class="odb-optimizing-table">
1185	<div class="odb-title-bar">
1186	<h2>
1187	<?php _e('Optimizing Database Tables','rvg-optimize-database');?>
1188	</h2>
1189	</div>
1190	<br />
1191	<br />
1192	<table border="0" cellspacing="8" cellpadding="2">
1193	<tr>
1194	<th class="odb-border-bottom" align="right">#</th>
1195	<th class="odb-border-bottom" align="left"><?php _e('table name','rvg-optimize-database');?></th>
1196	<th class="odb-border-bottom" align="left"><?php _e('optimization result','rvg-optimize-database');?></th>
1197	<th class="odb-border-bottom" align="left"><?php _e('engine','rvg-optimize-database');?></th>
1198	<th class="odb-border-bottom" align="right"><?php _e('table rows','rvg-optimize-database');?></th>
1199	<th class="odb-border-bottom" align="right"><?php _e('table size','rvg-optimize-database');?></th>
1200	</tr>
1201	<?php
1202	# OPTIMIZE THE DATABASE TABLES
1203	$cnt = rvg_optimize_tables(true);
1204	?>
1205	</table>
1206	<?php
1207	// NUMBER OF TABLES
1208	$log_arr["tables"] = $cnt;
1209	// DATABASE SIZE BEFORE OPTIMIZATION
1210	$log_arr["before"] = rvg_format_size($start_size,3);
1211	// DATABASE SIZE AFTER OPTIMIZATION
1212	$end_size = rvg_get_db_size();
1213	$log_arr["after"] = rvg_format_size($end_size,3);
1214	// TOTAL SAVING
1215	$log_arr["savings"] = rvg_format_size(($start_size - $end_size),3);
1216	// WRITE RESULTS TO LOG FILE
1217	rvg_write_log($log_arr);
1218
1219	$total_savings = rvg_odb_get_option('rvg_odb_total_savings');
1220	$total_savings += ($start_size - $end_size);
1221	rvg_odb_update_option('rvg_odb_total_savings',$total_savings);
1222	?>
1223	<div class="odb-title-bar">
1224	<h2>
1225	<?php _e('Savings','rvg-optimize-database');?>
1226	</h2>
1227	</div>
1228	<br />
1229	<br />
1230	<table border="0" cellspacing="8" cellpadding="2">
1231	<tr>
1232	<th> </th>
1233	<th class="odb-border-bottom"><?php _e('size of the database','rvg-optimize-database');?></th>
1234	</tr>
1235	<tr>
1236	<td align="right"><?php _e('BEFORE optimization','rvg-optimize-database');?></td>
1237	<td align="right" class="odb-bold"><?php echo rvg_format_size($start_size,3); ?></td>
1238	</tr>
1239	<tr>
1240	<td align="right"><?php _e('AFTER optimization','rvg-optimize-database');?></td>
1241	<td align="right" class="odb-bold"><?php echo rvg_format_size($end_size,3); ?></td>
1242	</tr>
1243	<tr>
1244	<td align="right" class="odb-bold"><?php _e('SAVINGS THIS TIME','rvg-optimize-database');?></td>
1245	<td align="right" class="odb-border-top odb-bold"><?php echo rvg_format_size(($start_size - $end_size),3); ?></td>
1246	</tr>
1247	<tr>
1248	<td align="right" class="odb-bold"><?php _e('TOTAL SAVINGS SINCE THE FIRST RUN','rvg-optimize-database');?></td>
1249	<td align="right" class="odb-border-top odb-bold"><?php echo rvg_format_size($total_savings,3); ?></td>
1250	</tr>
1251	</table>
1252	</div>
1253	<?php
1254	$time = microtime();
1255	$time = explode(' ', $time);
1256	$time = $time[1] + $time[0];
1257	$finish = $time;
1258
1259	$total_time = round(($finish - $odb_start_time), 4);
1260	?>
1261	<div class="odb-title-bar">
1262	<h2>
1263	<?php _e('DONE!','rvg-optimize-database');?>
1264	</h2>
1265	</div>
1266	<br />
1267	<br />
1268	<span class="odb-padding-left">
1269	<?php _e('Optimization took', 'rvg-optimize-database')?>
1270	<strong><?php echo $total_time;?></strong>
1271	<?php _e('seconds', 'rvg-optimize-database')?>
1272	.</span>
1273	<?php
1274	if(file_exists(dirname(__FILE__).'/rvg-optimize-db-log.html'))
1275	{
1276	?>
1277	<br />
1278	<br />
1279
1280	<input class="button odb-normal" type="button" name="view_log" value="<?php _e('View Log File','rvg-optimize-database');?>" onclick="window.open('<?php echo $log_url?>')" />
1281
1282	<input class="button odb-normal" type="button" name="delete_log" value="<?php _e('Delete Log File','rvg-optimize-database');?>" onclick="self.location='tools.php?page=rvg-optimize-db.php&action=delete_log'" />
1283	<?php
1284	}
1285	} // rvg_optimize_db()
1286
1287
1288	/********************************************************************************************
1289	*
1290	* EXECUTE OPTIMIZATION VIA CRON JOB
1291	*
1292	********************************************************************************************/
1293	function rvg_optimize_db_cron()
1294	{
1295	global $wpdb, $odb_version, $odb_ms_prefixes;
1296
1297	// v3.3.1 - GET NETWORK INFORMATION (MULTISITE)
1298	rvg_odb_network_info();
1299
1300	// GET SETTINGS AND SET DEFAULT VALUES
1301	$max_revisions = rvg_odb_get_option('rvg_odb_number');
1302	if(!$max_revisions)
1303	{ $max_revisions = 0;
1304	rvg_odb_update_option('rvg_odb_number', $max_revisions);
1305	}
1306
1307	$clear_trash = rvg_odb_get_option('rvg_clear_trash');
1308	if(!$clear_trash)
1309	{ $clear_trash = 'N';
1310	rvg_odb_update_option('rvg_clear_trash', $clear_trash);
1311	}
1312
1313	$clear_spam = rvg_odb_get_option('rvg_clear_spam');
1314	if(!$clear_spam)
1315	{ $clear_spam = 'N';
1316	rvg_odb_update_option('rvg_clear_spam', $clear_spam);
1317	}
1318
1319	$clear_tags = rvg_odb_get_option('rvg_clear_tags');
1320	if(!$clear_tags)
1321	{ $clear_tags = 'N';
1322	rvg_odb_update_option('rvg_clear_tags', $clear_tags);
1323	}
1324
1325	$clear_transients = rvg_odb_get_option('rvg_clear_transients');
1326	if(!$clear_transients)
1327	{ $clear_transients = 'N';
1328	rvg_odb_update_option('rvg_clear_transients', $clear_transients);
1329	}
1330
1331	// v3.1
1332	$clear_pingbacks = rvg_odb_get_option('rvg_clear_pingbacks');
1333	if(!$clear_pingbacks)
1334	{ $clear_pingbacks = 'N';
1335	rvg_odb_update_option('rvg_clear_pingbacks', $clear_pingbacks);
1336	}
1337
1338	// GET THE SIZE OF THE DATABASE BEFORE OPTIMIZATION
1339	$start_size = rvg_get_db_size();
1340
1341	// TIMESTAMP FOR LOG FILE
1342	$log_arr = array("time" => date("m/d/Y").'<br />'.date("H:i:s"));
1343
1344	// FIND THE REVISIONS
1345	$results = rvg_get_revisions($max_revisions);
1346
1347	$total_deleted = 0;
1348	if(count($results)>0)
1349	// WE HAVE REVISIONS TO DELETE!
1350	$total_deleted = rvg_delete_revisions($results, false, $max_revisions);
1351
1352	// NUMBER OF DELETED REVISIONS FOR LOG FILE
1353	$log_arr["revisions"] = $total_deleted;
1354
1355	$total_deleted = 0;
1356	if($clear_trash == 'Y')
1357	{
1358	// GET TRASHED POSTS / PAGES AND COMMENTS
1359	$results = rvg_get_trash();
1360
1361	if(count($results)>0)
1362	// WE HAVE TRASH TO DELETE!
1363	$total_deleted = rvg_delete_trash($results, false, $max_revisions);
1364
1365	} // if($clear_trash == 'Y')
1366
1367	// NUMBER OF DELETED TRASH FOR LOG FILE
1368	$log_arr["trash"] = $total_deleted;
1369
1370	$total_deleted = 0;
1371	if($clear_spam == 'Y')
1372	{
1373	// GET SPAMMED COMMENTS
1374	$results = rvg_get_spam();
1375
1376	if(count($results)>0)
1377	// WE HAVE SPAM TO DELETE!
1378	$total_deleted = rvg_delete_spam($results, false);
1379
1380	} // if($clear_spam == 'Y')
1381
1382	// NUMBER OF SPAM DELETED FOR LOG FILE
1383	$log_arr["spam"] = $total_deleted;
1384
1385	if($clear_tags == "Y")
1386	{ // DELETE UNUSED TAGS
1387	$total_deleted = rvg_delete_tags();
1388	}
1389
1390	// NUMBER OF DELETE TAGS FOR LOG FILE
1391	$log_arr["tags"] = $total_deleted;
1392
1393	if($clear_transients == "Y")
1394	{ // DELETE TRANSIENTS
1395	$total_deleted = rvg_delete_transients();
1396	}
1397
1398	// NUMBER OF DELETED TAGS FOR LOG FILE
1399	$log_arr["transients"] = $total_deleted;
1400
1401	// PINGBACKS AND TRACKBACKS (v3.1)
1402	if($clear_pingbacks == "Y")
1403	{ // DELETE PINGBACKS AND TRACKBACKS
1404	$total_deleted = rvg_delete_pingbacks();
1405	}
1406	// NUMBER OF DELETED PINGBACKS AND TRACKBACKS FOR LOG FILE
1407	$log_arr["pingbacks"] = $total_deleted;
1408
1409	// DELETE ORPHANS
1410	$total_deleted = rvg_delete_orphans(false);
1411	// NUMBER OF ORPHANS DELETED (FOR LOG FILE)
1412	$log_arr["orphans"] = $total_deleted;
1413
1414	// OPTIMIZE DATABASE TABLES
1415	$cnt = rvg_optimize_tables(false);
1416
1417	// NUMBER OF TABLES
1418	$log_arr["tables"] = $cnt;
1419	// DATABASE SIZE BEFORE OPTIMIZATION
1420	$log_arr["before"] = rvg_format_size($start_size,3);
1421	// DATABASE SIZE AFTER OPTIMIZATION
1422	$end_size = rvg_get_db_size();
1423	$log_arr["after"] = rvg_format_size($end_size,3);
1424	// TOTAL SAVING
1425	$log_arr["savings"] = rvg_format_size(($start_size - $end_size),3);
1426	// WRITE RESULTS TO LOG FILE
1427	rvg_write_log($log_arr);
1428
1429	$total_savings = rvg_odb_get_option('rvg_odb_total_savings');
1430	$total_savings += ($start_size - $end_size);
1431	rvg_odb_update_option('rvg_odb_total_savings',$total_savings);
1432
1433	} // rvg_optimize_db_cron()
1434
1435
1436	/********************************************************************************************
1437	*
1438	* DELETE THE REVISIONS
1439	*
1440	* v3.2: MULTISITE
1441	*
1442	********************************************************************************************/
1443	function rvg_delete_revisions($results, $display, $max_revisions)
1444	{
1445	global $wpdb;
1446
1447	$nr = 1;
1448	$total_deleted = 0;
1449
1450	for($i=0; $i<count($results); $i++)
1451	{ $nr_to_delete = $results[$i]['cnt'] - $max_revisions;
1452	$total_deleted += $nr_to_delete;
1453
1454	if($display)
1455	{
1456	?>
1457	<tr>
1458	<td align="right" valign="top"><?php echo $nr?>.</td>
1459	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
1460	<td valign="top" class="odb-bold"><?php echo $results[$i]['post_title']?></td>
1461	<td valign="top"><?php
1462	} // if($display)
1463
1464	$sql_get_posts = "
1465	SELECT `ID`, `post_modified`
1466	FROM ".$results[$i]['site']."posts
1467	WHERE `post_parent`=".$results[$i]['post_parent']."
1468	AND `post_type`='revision'
1469	ORDER BY `post_modified` ASC
1470	";
1471
1472	$results_get_posts = $wpdb->get_results($sql_get_posts);
1473
1474	for($j=0; $j<$nr_to_delete; $j++)
1475	{
1476	if($display) echo $results_get_posts[$j]->post_modified.'<br />';
1477
1478	$sql_delete = "
1479	DELETE FROM ".$results[$i]['site']."posts
1480	WHERE `ID` = ".$results_get_posts[$j]->ID."
1481	";
1482	$wpdb->get_results($sql_delete);
1483
1484	} // for($j=0; $j<$nr_to_delete; $j++)
1485
1486	$nr++;
1487	if($display)
1488	{
1489	?></td>
1490	<td align="right" valign="top" class="odb-bold"><?php echo $nr_to_delete?></td>
1491	</tr>
1492	<?php
1493	} // if($display)
1494	} // for($i=0; $i<count($results); $i++)
1495
1496	return $total_deleted;
1497	} // rvg_delete_revisions()
1498
1499
1500	/********************************************************************************************
1501	*
1502	* DELETE TRASHED POSTS AND PAGES
1503	*
1504	* v3.2: MULTISITE
1505	*
1506	********************************************************************************************/
1507	function rvg_delete_trash($results, $display)
1508	{
1509	global $wpdb;
1510
1511	$nr = 1;
1512	$total_deleted = count($results);
1513
1514	for($i=0; $i<$total_deleted; $i++)
1515	{ if($display)
1516	{
1517	?>
1518	<tr>
1519	<td align="right" valign="top"><?php echo $nr; ?></td>
1520	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
1521	<td valign="top"><?php echo $results[$i]['post_type']; ?></td>
1522	<td valign="top"><?php echo $results[$i]['title']; ?></td>
1523	<td valign="top" nowrap="nowrap"><?php echo $results[$i]['modified']; ?></td>
1524	</tr>
1525	<?php
1526	}
1527
1528	if($results[$i]['post_type'] == 'comment')
1529	{ // DELETE META DATA (IF ANY...)
1530	$sql_delete = "
1531	DELETE FROM ".$results[$i]['site']."commentmeta
1532	WHERE `comment_id` = ".$results[$i]['id']."
1533	";
1534	$wpdb->get_results($sql_delete);
1535	}
1536
1537	// DELETE TRASHED POSTS / PAGES
1538	$sql_delete = "
1539	DELETE FROM ".$results[$i]['site']."posts
1540	WHERE `post_status` = 'trash'
1541	";
1542	$wpdb->get_results($sql_delete);
1543
1544	// DELETE TRASHED COMMENTS
1545	$sql_delete = "
1546	DELETE FROM ".$results[$i]['site']."comments
1547	WHERE `comment_approved` = 'trash'
1548	";
1549	$wpdb->get_results($sql_delete);
1550
1551	$nr++;
1552	} // for($i=0; $i<count($results); $i++)
1553
1554	return $total_deleted;
1555
1556	} // rvg_delete_trash()
1557
1558
1559	/********************************************************************************************
1560	*
1561	* DELETE SPAMMED ITEMS
1562	*
1563	* v3.2: MULTISITE
1564	*
1565	********************************************************************************************/
1566	function rvg_delete_spam($results, $display)
1567	{
1568	global $wpdb;
1569
1570	$nr = 1;
1571	$total_deleted = count($results);
1572	for($i=0; $i<count($results); $i++)
1573	{ if($display)
1574	{
1575	?>
1576	<tr>
1577	<td align="right" valign="top"><?php echo $nr; ?></td>
1578	<td align="left" valign="top"><?php echo $results[$i]['site']?></td>
1579	<td valign="top"><?php echo $results[$i]['comment_author']; ?></td>
1580	<td valign="top"><?php echo $results[$i]['comment_author_email']; ?></td>
1581	<td valign="top" nowrap="nowrap"><?php echo $results[$i]['comment_date']; ?></td>
1582	</tr>
1583	<?php
1584	} // if($display)
1585
1586	$sql_delete = "
1587	DELETE FROM ".$results[$i]['site']."commentmeta
1588	WHERE `comment_id` = ".$results[$i]['comment_ID']."
1589	";
1590	$wpdb->get_results($sql_delete);
1591
1592	$sql_delete = "
1593	DELETE FROM ".$results[$i]['site']."comments
1594	WHERE `comment_approved` = 'spam'
1595	";
1596	$wpdb->get_results($sql_delete);
1597
1598	$nr++;
1599	} // for($i=0; $i<count($results); $i++)
1600
1601	return $total_deleted;
1602
1603	} // rvg_delete_spam()
1604
1605
1606	/********************************************************************************************
1607	*
1608	* DELETE UNUSED TAGS
1609	*
1610	* v3.2: MULTISITE
1611	* v3.4.9: BUG FIX ($i should have been $j)
1612	*
1613	********************************************************************************************/
1614	function rvg_delete_tags()
1615	{
1616	global $odb_ms_blogids, $odb_ms_prefixes;
1617
1618	$total_deleted = 0;
1619
1620	// LOOP THROUGH THE NETWORK
1621	for($i=0; $i<count($odb_ms_blogids); $i++)
1622	{
1623	$tags = get_terms('post_tag', array('hide_empty' => 0));
1624	for($j=0; $j<count($tags); $j++)
1625	{
1626	if($tags[$j]->count < 1)
1627	{ if(!rvg_delete_tags_is_scheduled($tags[$j]->term_id, $odb_ms_prefixes[$i]))
1628	{ // v3.0: TAG NOT USED IN SCHEDULED POSTS: CAN BE DELETED
1629	$total_deleted++;
1630	wp_delete_term($tags[$j]->term_id, 'post_tag');
1631	}
1632	}
1633	} // for($j=0; $j<count($tags); $j++)
1634	} // for($i=0; $i<count($odb_ms_blogids); $i++)
1635
1636	return $total_deleted;
1637	} // rvg_delete_tags()
1638
1639
1640	/********************************************************************************************
1641	*
1642	* IS THE UNUSED TAG USED IN ONE OR MORE SCHEDULED POSTS?
1643	*
1644	* Since: v3.0
1645	*
1646	* v3.2: MULTISITE
1647	*
1648	********************************************************************************************/
1649	function rvg_delete_tags_is_scheduled($term_id, $odb_prefix)
1650	{
1651	global $wpdb;
1652
1653	$sql_get_posts = "
1654	SELECT p.post_status
1655	FROM ".$odb_prefix."term_relationships t, ".$odb_prefix."posts p
1656	WHERE t.term_taxonomy_id = '".$term_id."'
1657	AND t.object_id = p.ID
1658	";
1659
1660	$results_get_posts = $wpdb->get_results($sql_get_posts);
1661	for($i=0; $i<count($results_get_posts); $i++)
1662	if($results_get_posts[$i]->post_status == 'future') return true;
1663
1664	return false;
1665
1666	} // rvg_delete_tags_is_scheduled()
1667
1668
1669	/********************************************************************************************
1670	*
1671	* DELETE EXPIRED TRANSIENTS
1672	*
1673	* v3.2: MULTISITE
1674	* v3.4.7: from now on it uses the delete_transient() and delete_site_transients() functions
1675	*
1676	********************************************************************************************/
1677	function rvg_delete_transients()
1678	{
1679	global $wpdb, $odb_ms_prefixes;
1680
1681	$delay = time() - 60; // ONE MINUTE DELAY
1682
1683	$total_deleted = 0;
1684
1685	// LOOP THROUGH THE NETWORK
1686	for($i=0; $i<count($odb_ms_prefixes); $i++)
1687	{
1688	// FIND EXPIRED TRANSIENTS
1689	$sql = "
1690	SELECT `option_name`
1691	FROM ".$odb_ms_prefixes[$i]."options
1692	WHERE (
1693	option_name LIKE '_transient_timeout_%'
1694	OR option_name LIKE '_site_transient_timeout_%'
1695	)
1696	AND option_value < '$delay'
1697	";
1698
1699	$results = $wpdb->get_results($sql);
1700	$total_deleted += count($results);
1701
1702	// LOOP THROUGH THE RESULTS
1703	for($j=0; $j<count($results); $j++)
1704	{
1705	if(substr($results[$j]->option_name, 0, 19) == '_transient_timeout_')
1706	{ // _transient_timeout_%
1707	$transient = substr($results[$j]->option_name, 19);
1708	// DELETE THE TRANSIENT
1709	delete_transient($transient);
1710	}
1711	else
1712	{ // _site_transient_timeout_%
1713	$transient = substr($results[$j]->option_name, 24);
1714	// DELETE THE TRANSIENT
1715	delete_site_transient($transient);
1716	}
1717	} // for($j=0; $j<count($results); $j++)
1718
1719	} // for($i=0; $i<count($odb_ms_prefixes); $i++)
1720	return $total_deleted;
1721	} // rvg_delete_transients()
1722
1723
1724	/********************************************************************************************
1725	*
1726	* DELETE PINGBACKS AND TRACKBACKS
1727	*
1728	* Since: v3.1
1729	*
1730	* v3.2: MULTISITE
1731	*
1732	********************************************************************************************/
1733	function rvg_delete_pingbacks()
1734	{
1735	global $wpdb, $odb_ms_prefixes;
1736
1737	$total_deleted = 0;
1738
1739	// LOOP THROUGH THE NETWORK
1740	for($i=0; $i<count($odb_ms_prefixes); $i++)
1741	{
1742	$sql = "
1743	SELECT `comment_ID`
1744	FROM ".$odb_ms_prefixes[$i]."comments
1745	WHERE (
1746	`comment_type` = 'pingback'
1747	OR `comment_type` = 'trackback'
1748	)
1749	";
1750
1751	$results = $wpdb->get_results($sql);
1752	$total_deleted = count($results);
1753
1754	for($j=0; $j<count($results); $j++)
1755	{ // DELETE METADATA FOR THIS COMMENT (IF ANY)
1756	$sql_delete_meta = "
1757	DELETE FROM ".$odb_ms_prefixes[$i]."commentmeta
1758	WHERE `comment_id` = ".$results[$j]->comment_ID."
1759	";
1760	$wpdb->get_results($sql_delete_meta);
1761	}
1762
1763	// DELETE COMMENTS
1764	$sql_delete_comments = "
1765	DELETE FROM ".$odb_ms_prefixes[$i]."comments
1766	WHERE (
1767	`comment_type` = 'pingback'
1768	OR `comment_type` = 'trackback'
1769	)
1770	";
1771	$wpdb->get_results($sql_delete_comments);
1772	}
1773
1774	return $total_deleted;
1775	} // rvg_delete_pingbacks()
1776
1777
1778	/********************************************************************************************
1779	*
1780	* DELETE ORPHAN POSTMETA RECORDS
1781	*
1782	* Since: v2.2.7
1783	*
1784	* v3.2: MULTISITE
1785	*
1786	********************************************************************************************/
1787	function rvg_delete_orphans($display)
1788	{
1789	global $wpdb, $odb_ms_prefixes;
1790
1791	$meta_orphans = 0;
1792	$post_orphans = 0;
1793
1794	// LOOP THROUGH THE NETWORK
1795	for($i=0; $i<count($odb_ms_prefixes); $i++)
1796	{
1797	// DELETE POST ORPHANS (AUTO DRAFTS)
1798	$sql_delete = "
1799	SELECT COUNT(*) cnt
1800	FROM ".$odb_ms_prefixes[$i]."posts
1801	WHERE ID NOT IN (SELECT post_id FROM ".$odb_ms_prefixes[$i]."postmeta)
1802	AND post_status = 'auto-draft'
1803	";
1804
1805	$results = $wpdb->get_results($sql_delete);
1806
1807	$post_orphans = $results[0]->cnt;
1808
1809	if($post_orphans > 0)
1810	{ $sql_delete = "
1811	DELETE FROM ".$odb_ms_prefixes[$i]."posts
1812	WHERE ID NOT IN (SELECT post_id FROM ".$odb_ms_prefixes[$i]."postmeta)
1813	AND post_status = 'auto-draft'
1814	";
1815	$wpdb->get_results($sql_delete);
1816	}
1817
1818	// DELETE POSTMETA ORPHANS
1819	$sql_delete = "
1820	SELECT COUNT(*) cnt
1821	FROM ".$odb_ms_prefixes[$i]."postmeta
1822	WHERE post_id NOT IN (SELECT ID FROM ".$odb_ms_prefixes[$i]."posts)
1823	";
1824
1825	$results = $wpdb->get_results($sql_delete);
1826
1827	$meta_orphans = $results[0]->cnt;
1828
1829	if($meta_orphans > 0)
1830	{ $sql_delete = "
1831	DELETE FROM ".$odb_ms_prefixes[$i]."postmeta
1832	WHERE post_id NOT IN (SELECT ID FROM ".$odb_ms_prefixes[$i]."posts)
1833	";
1834	$wpdb->get_results($sql_delete);
1835	}
1836	}
1837
1838	return ($meta_orphans + $post_orphans);
1839
1840	} // rvg_delete_orphans()
1841
1842
1843	/********************************************************************************************
1844	*
1845	* OPTIMIZE DATABASE TABLES
1846	*
1847	********************************************************************************************/
1848	function rvg_optimize_tables($display)
1849	{
1850	global $wpdb;
1851
1852	# v3.5.1 - SKIP VIEWS
1853	$tables = $wpdb->get_results("SHOW FULL TABLES FROM `".DB_NAME."` WHERE table_type = 'BASE TABLE'", ARRAY_N);
1854	// print_r($tables);
1855
1856	$cnt = 0;
1857	for ($i=0; $i<count($tables); $i++)
1858	{
1859	$excluded = rvg_odb_get_option('rvg_ex_'.$tables[$i][0]);
1860
1861	if(!$excluded)
1862	{ # TABLE NOT EXCLUDED
1863	$cnt++;
1864
1865	// v3.1.4
1866	$sql = "
1867	SELECT engine, (data_length + index_length) AS size, table_rows
1868	FROM information_schema.TABLES
1869	WHERE table_schema = '".DB_NAME."'
1870	AND table_name = '".$tables[$i][0]."'
1871	";
1872	$table_info = $wpdb->get_results($sql);
1873
1874	// v3.1.4
1875	if(strtolower($table_info[0]->engine) == 'innodb')
1876	{ // SKIP InnoDB tables
1877	$msg = __('InnoDB table: skipped...', 'rvg-optimize-database');
1878	}
1879	else
1880	{ $query = "OPTIMIZE TABLE ".$tables[$i][0];
1881	$result = $wpdb->get_results($query);
1882	$msg = $result[0]->Msg_text;
1883	$msg = str_replace('OK', __('<span class="odb-optimized">TABLE OPTIMIZED</span>', 'rvg-optimize-database'), $msg);
1884	$msg = str_replace('Table is already up to date', __('Table is already up to date', 'rvg-optimize-database'), $msg);
1885	}
1886
1887	if($display)
1888	{ // NOT FROM THE SCEDULER
1889	?>
1890	<tr>
1891	<td align="right" valign="top"><?php echo $cnt?>.</td>
1892	<td valign="top" class="odb-bold"><?php echo $tables[$i][0] ?></td>
1893	<td valign="top"><?php echo $msg ?></td>
1894	<td valign="top"><?php echo $table_info[0]->engine ?></td>
1895	<td align="right" valign="top"><?php echo $table_info[0]->table_rows ?></td>
1896	<td align="right" valign="top"><?php echo rvg_format_size($table_info[0]->size) ?></td>
1897	</tr>
1898	<?php
1899	} // if($display)
1900	} // if(!$excluded)
1901	} // for ($i=0; $i<count($tables); $i++)
1902	return $cnt;
1903
1904	} // rvg_optimize_tables()
1905
1906
1907	/********************************************************************************************
1908	*
1909	* WRITE LINE TO LOG FILE
1910	*
1911	********************************************************************************************/
1912	function rvg_write_log($log_arr)
1913	{
1914	global $odb_version;
1915
1916	$rvg_odb_logging_on = rvg_odb_get_option('rvg_odb_logging_on');
1917	if(!$rvg_odb_logging_on)
1918	{ $rvg_odb_logging_on = 'N';
1919	rvg_odb_update_option('rvg_odb_logging_on', $rvg_odb_logging_on);
1920	}
1921
1922	if($rvg_odb_logging_on == "Y")
1923	{ $file = dirname(__FILE__).'/rvg-optimize-db-log.html';
1924	if(!file_exists($file))
1925	{
1926	// NEW LOG FILE
1927	$html = '
1928	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1929	<html xmlns="http://www.w3.org/1999/xhtml">
1930	<head>
1931	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
1932	<title>Optimize Database after Deleting Revisions v'.$odb_version.' - LOG</title>
1933	<style type="text/css">
1934	body, td, th {
1935	font-family: Arial, Helvetica, sans-serif;
1936	font-size: 12px;
1937	}
1938	th {
1939	border-top:solid 1px #000;
1940	border-bottom:solid 1px #000;
1941	}
1942	td {
1943	padding-bottom:4px;
1944	border-bottom:dotted 1px #CCC;
1945	}
1946	#header {
1947	margin-left:6px;
1948	margin-bottom:8px;
1949	}
1950	#header a {
1951	text-decoration:none;
1952	font-weight:bold;
1953	}
1954	</style>
1955	</head>
1956	<body>
1957	<div id="header">
1958	<h2><a href="https://wordpress.org/plugins/rvg-optimize-database/" target="_blank">Optimize Database after Deleting Revisions v'.$odb_version.'</a></h2>
1959	'.__('A WordPress Plugin by','rvg-optimize-database').' <a href="http://cagewebdev.com" target="_blank"><span class="odb-bold">CAGE Web Design</span></a> \| <a href="http://rvg.cage.nl" target="_blank"><span class="odb-bold">Rolf van Gelder</span></a>, Eindhoven, '.__('The Netherlands','rvg-optimize-database').'</span>
1960	</div>
1961	<table width="100%" border="0" cellspacing="6" cellpadding="1">
1962	<tr>
1963	<th width="8%" align="left" valign="top">'.__('time','rvg-optimize-database').'</th>
1964	<th width="8%" align="right" valign="top">'.__('deleted<br />revisions','rvg-optimize-database').'</th>
1965	<th width="8%" align="right" valign="top">'.__('deleted<br />trash','rvg-optimize-database').'</th>
1966	<th width="8%" align="right" valign="top">'.__('deleted<br />spam','rvg-optimize-database').'</th>
1967	<th width="8%" align="right" valign="top">'.__('deleted<br />tags','rvg-optimize-database').'</th>
1968	<th width="8%" align="right" valign="top">'.__('deleted<br />transients','rvg-optimize-database').'</th>
1969	<th width="8%" align="right" valign="top">'.__('deleted<br />pingbacks<br />trackbacks','rvg-optimize-database').'</th>
1970	<th width="8%" align="right" valign="top">'.__('deleted<br />orphans','rvg-optimize-database').'</th>
1971	<th width="8%" align="right" valign="top">'.__('nr of optimized tables','rvg-optimize-database').'</th>
1972	<th width="8%" align="right" valign="top">'.__('database size BEFORE','rvg-optimize-database').'</th>
1973	<th width="8%" align="right" valign="top">'.__('database size AFTER','rvg-optimize-database').'</th>
1974	<th width="8%" align="right" valign="top">'.__('SAVINGS','rvg-optimize-database').'</th>
1975	</tr>
1976	</table>
1977	';
1978
1979	file_put_contents($file,$html,FILE_APPEND);
1980	}
1981
1982	$html = '
1983	<table width="100%" border="0" cellspacing="6" cellpadding="0">
1984	<tr>
1985	<td width="8%" valign="top"><span class="odb-bold">'.$log_arr["time"].'</span></td>
1986	<td width="8%" align="right" valign="top">'.$log_arr["revisions"].'</td>
1987	<td width="8%" align="right" valign="top">'.$log_arr["trash"].'</td>
1988	<td width="8%" align="right" valign="top">'.$log_arr["spam"].'</td>
1989	<td width="8%" align="right" valign="top">'.$log_arr["tags"].'</td>
1990	<td width="8%" align="right" valign="top">'.$log_arr["transients"].'</td>
1991	<td width="8%" align="right" valign="top">'.$log_arr["pingbacks"].'</td>
1992	<td width="8%" align="right" valign="top">'.$log_arr["orphans"].'</td>
1993	<td width="8%" align="right" valign="top">'.$log_arr["tables"].'</td>
1994	<td width="8%" align="right" valign="top">'.$log_arr["before"].'</td>
1995	<td width="8%" align="right" valign="top">'.$log_arr["after"].'</td>
1996	<td width="8%" align="right" valign="top">'.$log_arr["savings"].'</td>
1997	</tr>
1998	</table>
1999	';
2000
2001	// print_r($log_arr);
2002	file_put_contents($file,$html,FILE_APPEND);
2003	}
2004
2005	} // rvg_write_log()
2006
2007
2008	/********************************************************************************************
2009	*
2010	* GET REVISIONS
2011	*
2012	* V3.2: MULTISITE
2013	*
2014	********************************************************************************************/
2015	function rvg_get_revisions($max_revisions)
2016	{
2017	global $wpdb, $odb_ms_prefixes;
2018
2019	$res_arr = array();
2020
2021	$index = 0;
2022	for($i=0; $i<count($odb_ms_prefixes); $i++)
2023	{ $sql = "
2024	SELECT `post_parent`, `post_title`, COUNT(*) cnt
2025	FROM ".$odb_ms_prefixes[$i]."posts
2026	WHERE `post_type` = 'revision'
2027	GROUP BY `post_parent`
2028	HAVING COUNT(*) > ".$max_revisions."
2029	ORDER BY UCASE(`post_title`)
2030	";
2031	$res = $wpdb->get_results($sql, ARRAY_A);
2032
2033	for($j=0; $j<count($res); $j++)
2034	{ if(isset($res[$j]))
2035	{ $res_arr[$index] = $res[$j];
2036	$res_arr[$index]['site'] = $odb_ms_prefixes[$i];
2037	$index++;
2038	}
2039	}
2040	}
2041
2042	return $res_arr;
2043
2044	} // rvg_get_revisions()
2045
2046
2047	/********************************************************************************************
2048	*
2049	* GET TRASHED POSTS / PAGES AND COMMENTS
2050	*
2051	* v3.2: MULTISITE
2052	*
2053	********************************************************************************************/
2054	function rvg_get_trash()
2055	{
2056	global $wpdb, $odb_ms_prefixes;
2057
2058	$res_arr = array();
2059
2060	$index = 0;
2061
2062	// LOOP TROUGH SITES
2063	for($i=0; $i<count($odb_ms_prefixes); $i++)
2064	{
2065	$sql = "
2066	SELECT `ID` AS id, 'post' AS post_type, `post_title` AS title, `post_modified` AS modified
2067	FROM ".$odb_ms_prefixes[$i]."posts
2068	WHERE `post_status` = 'trash'
2069	UNION ALL
2070	SELECT `comment_ID` AS id, 'comment' AS post_type, `comment_author_IP` AS title, `comment_date` AS modified
2071	FROM ".$odb_ms_prefixes[$i]."comments
2072	WHERE `comment_approved` = 'trash'
2073	ORDER BY post_type, UCASE(title)
2074	";
2075	$res = $wpdb->get_results($sql, ARRAY_A);
2076
2077	if($res != null)
2078	{ $res_arr[$index] = $res[0];
2079	$res_arr[$index]['site'] = $odb_ms_prefixes[$i];
2080	$index++;
2081	}
2082	}
2083
2084	return $res_arr;
2085
2086	} // rvg_get_trash()
2087
2088
2089	/********************************************************************************************
2090	*
2091	* GET SPAMMED COMMENTS
2092	*
2093	* v3.2: MULTISITE
2094	*
2095	********************************************************************************************/
2096	function rvg_get_spam()
2097	{
2098	global $wpdb, $odb_ms_prefixes;
2099
2100	$res_arr = array();
2101
2102	$index = 0;
2103	// LOOP THROUGH SITES
2104	for($i=0; $i<count($odb_ms_prefixes); $i++)
2105	{
2106	$sql = "
2107	SELECT `comment_ID`, `comment_author`, `comment_author_email`, `comment_date`
2108	FROM ".$odb_ms_prefixes[$i]."comments
2109	WHERE `comment_approved` = 'spam'
2110	ORDER BY UCASE(`comment_author`)
2111	";
2112	$res = $wpdb->get_results($sql, ARRAY_A);
2113
2114	if($res != null)
2115	{ $res_arr[$index] = $res[0];
2116	$res_arr[$index]['site'] = $odb_ms_prefixes[$i];
2117	$index++;
2118	}
2119	}
2120
2121	return $res_arr;
2122
2123	} // rvg_get_spam()
2124
2125
2126	/********************************************************************************************
2127	*
2128	* CALCULATE THE SIZE OF THE WORDPRESS DATABASE (IN BYTES)
2129	*
2130	********************************************************************************************/
2131	function rvg_get_db_size()
2132	{
2133	global $wpdb;
2134
2135	// v3.1.4
2136	$sql = "
2137	SELECT SUM(data_length + index_length) AS size
2138	FROM information_schema.TABLES
2139	WHERE table_schema = '".DB_NAME."'
2140	GROUP BY table_schema
2141	";
2142
2143	$res = $wpdb->get_results($sql);
2144
2145	return $res[0]->size;
2146
2147	} // rvg_get_db_size()
2148
2149
2150	/********************************************************************************************
2151	*
2152	* FORMAT SIZES FROM BYTES TO KB OR MB
2153	*
2154	********************************************************************************************/
2155	function rvg_format_size($size, $precision=1)
2156	{
2157	if($size>1024*1024)
2158	$table_size = (round($size/(1024*1024),$precision)).' MB';
2159	else
2160	$table_size = (round($size/1024,$precision)).' KB';
2161
2162	return $table_size;
2163	} // rvg_format_size()
2164	?>
2165