Optimize Database after Deleting Revisions - Version 2.2

Version Description

[01/11/2013] = * NEW: 'Orphan Postmeta items' will be automatically deleted * NEW: the possibility to exclude tables from Optimization (for instance for 'heavy traffic' tables)

Download this release

Release Info

Developer cageehv
Plugin Icon 128x128 Optimize Database after Deleting Revisions
Version 2.2
Comparing to
See all releases

Code changes from version 2.1 to 2.2

Files changed (2) hide show
  1. readme.txt +13 -14
  2. rvg-optimize-db.php +217 -85
readme.txt CHANGED
@@ -6,20 +6,21 @@ Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-rev
6
  Tags: database, delete, revisions, optimize, post, posts, page, pages, clean, clean up, trash, spam, trashed, spammed, database size
7
  Author URI: http://cagewebdev.com
8
  Author: CAGE Web Design | Rolf van Gelder, Eindhoven, The Netherlands
9
- Requires at least: 2.1
10
  Tested up to: 3.5
11
- Stable tag: 2.1
12
- Version: 2.1
13
 
14
  == Description ==
15
 
16
  This plugin is a 'One Click' WordPress Database Cleaner / Optimizer.
17
 
18
  = Main Features =
19
- * Deletes redundant revisions of posts and pages. (You optionally can keep an 'x'-amount of the most recent revisions)
20
  * Deletes trashed posts, pages and comments (optional)
21
  * Deletes spammed comments (optional)
22
- * Optimizes the database tables
 
23
  * Creates a log file of the optimizations (optional)
24
  * Optimization can be scheduled to automatically run once hourly, twice daily, once daily or once weekly (optional)
25
 
@@ -31,7 +32,7 @@ You can start the Optimization in the WP Admin Panel » Tools » Optim
31
  Note: if you use the Scheduler the Optimization will run automatically!
32
 
33
  = Author =
34
- CAGE Web Design | Rolf van Gelder, Eindhoven, The Netherlands - http://cagewebdev.com
35
 
36
  = Plugin URL =
37
  http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin
@@ -43,15 +44,14 @@ http://wordpress.org/extend/plugins/rvg-optimize-database/
43
 
44
  * Upload the Plugin to the `/wp-content/plugins/` directory
45
  * Activate the plugin in the WP Admin Panel » Plugins
46
- * Change the settings (if needed) in the WP Admin Panel » Settings » Optimize DB Options
47
-
48
- == Screenshots ==
49
-
50
- 1. Optimize Database after Deleting Revisions - Options
51
- 2. Run the Optimizer
52
 
53
  == Changelog ==
54
 
 
 
 
 
55
  = 2.1 [01/04/2013] =
56
  * Bug fix: keeping a maximum number of revisions didn't work correctly
57
 
@@ -120,11 +120,10 @@ http://wordpress.org/extend/plugins/rvg-optimize-database/
120
  == Frequently Asked Questions ==
121
 
122
  = How can I change the settings of this plugin? =
123
- * WP Admin Panel » Settings » Optimize DB Options'. There you can define the maximum number of - most recent - revisions you want to keep per post or page and more options.
124
 
125
  = How do I run this plugin? =
126
  * WP Admin Panel » Tools » Optimize Database. Then click the 'Start Optimization'-button. Et voila!
127
 
128
  = Why do I see 'Table does not support optimize, doing recreate + analyze instead' while optimizing my database? =
129
  * That is because the table type of that table is not 'MyISAM'
130
-
6
  Tags: database, delete, revisions, optimize, post, posts, page, pages, clean, clean up, trash, spam, trashed, spammed, database size
7
  Author URI: http://cagewebdev.com
8
  Author: CAGE Web Design | Rolf van Gelder, Eindhoven, The Netherlands
9
+ Requires at least: 2.2
10
  Tested up to: 3.5
11
+ Stable tag: 2.2
12
+ Version: 2.2
13
 
14
  == Description ==
15
 
16
  This plugin is a 'One Click' WordPress Database Cleaner / Optimizer.
17
 
18
  = Main Features =
19
+ * Deletes redundant revisions of posts and pages (you optionally can keep an 'x'-amount of the most recent revisions)
20
  * Deletes trashed posts, pages and comments (optional)
21
  * Deletes spammed comments (optional)
22
+ * Deletes 'orphan postmeta items'
23
+ * Optimizes the database tables (optionally you can exclude certain tables from optimization)
24
  * Creates a log file of the optimizations (optional)
25
  * Optimization can be scheduled to automatically run once hourly, twice daily, once daily or once weekly (optional)
26
 
32
  Note: if you use the Scheduler the Optimization will run automatically!
33
 
34
  = Author =
35
+ CAGE Web Design | Rolf van Gelder, Eindhoven, The Netherlands - http://cagewebdev.com - http://cage.nl
36
 
37
  = Plugin URL =
38
  http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin
44
 
45
  * Upload the Plugin to the `/wp-content/plugins/` directory
46
  * Activate the plugin in the WP Admin Panel » Plugins
47
+ * Change the settings (if needed) in the WP Admin Panel » Tools » Optimize Database
 
 
 
 
 
48
 
49
  == Changelog ==
50
 
51
+ = 2.2 [01/11/2013] =
52
+ * NEW: 'Orphan Postmeta items' will be automatically deleted
53
+ * NEW: the possibility to exclude tables from Optimization (for instance for 'heavy traffic' tables)
54
+
55
  = 2.1 [01/04/2013] =
56
  * Bug fix: keeping a maximum number of revisions didn't work correctly
57
 
120
  == Frequently Asked Questions ==
121
 
122
  = How can I change the settings of this plugin? =
123
+ * WP Admin Panel » Settings » Optimize DB Options'. There you can define the maximum number of - most recent - revisions you want to keep per post or page and some more options.
124
 
125
  = How do I run this plugin? =
126
  * WP Admin Panel » Tools » Optimize Database. Then click the 'Start Optimization'-button. Et voila!
127
 
128
  = Why do I see 'Table does not support optimize, doing recreate + analyze instead' while optimizing my database? =
129
  * That is because the table type of that table is not 'MyISAM'
 
rvg-optimize-db.php CHANGED
@@ -1,16 +1,16 @@
1
  <?php
2
- $odb_version = '2.1';
3
- $odb_release_date = '01/04/2013';
4
  /**
5
  * @package Optimize Database after Deleting Revisions
6
- * @version 2.1
7
  */
8
  /*
9
  Plugin Name: Optimize Database after Deleting Revisions
10
  Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/
11
  Description: Optimizes the Wordpress Database after Cleaning it out - <a href="options-general.php?page=rvg_odb_admin"><strong>plug in options</strong></a>
12
  Author: Rolf van Gelder, Eindhoven, The Netherlands
13
- Version: 2.1
14
  Author URI: http://cagewebdev.com
15
  */
16
  ?>
@@ -34,7 +34,8 @@ add_action('admin_menu', 'optimize_db_main');
34
 
35
  *********************************************************************************************/
36
  function rvg_odb_admin_menu()
37
- { if (function_exists('add_options_page'))
 
38
  { add_options_page(__('Optimize DB Options'), __('Optimize DB Options'), 'manage_options', 'rvg_odb_admin', 'rvg_odb_options_page');
39
  }
40
  }
@@ -49,11 +50,6 @@ add_action( 'admin_menu', 'rvg_odb_admin_menu' );
49
  add_filter('cron_schedules', 'rvg_extra_schedules');
50
  function rvg_extra_schedules(){
51
  return array(
52
- /* 'once_per_5_minutes' => array(
53
- 'interval' => 60 * 5,
54
- 'display' => 'Every five minutes'
55
- )
56
- ,*/
57
  'weekly' => array(
58
  'interval' => 604800,
59
  'display' => 'Once weekly'
@@ -78,7 +74,7 @@ function rvg_activate_plugin()
78
  if( !wp_next_scheduled( 'rvg_optimize_database' ))
79
  wp_schedule_event( time(), $rvg_odb_schedule, 'rvg_optimize_database' );
80
  }
81
- }
82
 
83
 
84
  /********************************************************************************************
@@ -87,15 +83,39 @@ function rvg_activate_plugin()
87
 
88
  *********************************************************************************************/
89
  function rvg_odb_options_page() {
90
- global $odb_version, $odb_release_date;
 
 
 
 
 
 
91
 
92
- if($_REQUEST['delete_log'] == "Y")
93
- @unlink(dirname(__FILE__).'/rvg-optimize-db-log.html');
94
 
95
  // SAVE THE OPTIONS
96
- if ( isset( $_POST['info_update'] ) ) {
 
97
  check_admin_referer();
98
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
99
  $rvg_odb_number = trim($_POST['rvg_odb_number']);
100
  update_option('rvg_odb_number', $rvg_odb_number);
101
 
@@ -107,10 +127,6 @@ function rvg_odb_options_page() {
107
  if($_POST['rvg_clear_spam'] == 'Y') $rvg_clear_spam = 'Y';
108
  update_option('rvg_clear_spam', $rvg_clear_spam);
109
 
110
- $rvg_wp_only = 'N';
111
- if($_POST['rvg_wp_only'] == 'Y') $rvg_wp_only = 'Y';
112
- update_option('rvg_wp_only', $rvg_wp_only);
113
-
114
  $rvg_odb_logging_on = 'N';
115
  if($_POST['rvg_odb_logging_on'] == 'Y') $rvg_odb_logging_on = 'Y';
116
  update_option('rvg_odb_logging_on', $rvg_odb_logging_on);
@@ -122,12 +138,10 @@ function rvg_odb_options_page() {
122
  // CLEAR CURRENT SCHEDULE (IF ANY)
123
  wp_clear_scheduled_hook('rvg_optimize_database');
124
 
125
- if($rvg_odb_schedule != '')
126
- { // HAS TO BE SCHEDULED
127
- if( !wp_next_scheduled( 'rvg_optimize_database' )){
128
  wp_schedule_event( time(), $rvg_odb_schedule, 'rvg_optimize_database' );
129
- }
130
- }
131
 
132
  // UPDATED MESSAGE
133
  echo "<div class='updated'><p><strong>Optimize Database after Deleting Revisions OPTIONS UPDATED</strong> - Click <a href='tools.php?page=rvg-optimize-db.php' style='font-weight:bold'>HERE</a> to run the optimization</p></div>";
@@ -142,9 +156,6 @@ function rvg_odb_options_page() {
142
  $rvg_clear_spam = get_option('rvg_clear_spam');
143
  if(!$rvg_clear_spam) $rvg_clear_spam = 'N';
144
 
145
- $rvg_wp_only = get_option('rvg_wp_only');
146
- if(!$rvg_wp_only) $rvg_wp_only = 'N';
147
-
148
  $rvg_odb_logging_on = get_option('rvg_odb_logging_on');
149
  if(!$rvg_odb_logging_on) $rvg_odb_logging_on = 'N';
150
 
@@ -171,54 +182,86 @@ function rvg_odb_options_page() {
171
  <p>&nbsp;</p>
172
  </blockquote>
173
  <h2>Optimize Database after Deleting Revisions - Options</h2>
174
- <blockquote>
175
- <fieldset class='options'>
176
- <table class="editform" cellspacing="2" cellpadding="5">
177
- <tr>
178
- <td align="right" valign="top"><label for="<?php echo rvg_odb_number; ?>" style="font-weight:bold;">Maximum number of - most recent - revisions to keep per post / page<br />
179
- ('0' means: delete <u>ALL</u> revisions) </label></td>
180
- <td valign="top"><input type="text" size="5" name="rvg_odb_number" id="rvg_odb_number" value="<?php echo $rvg_odb_number?>" style="font-weight:bold;color:#00F;" /></td>
181
- </tr>
182
- <?php
183
  if($rvg_clear_trash == 'Y') $rvg_clear_trash_checked = ' checked="checked"'; else $rvg_clear_trash_checked = '';
184
  if($rvg_clear_spam == 'Y') $rvg_clear_spam_checked = ' checked="checked"'; else $rvg_clear_spam_checked = '';
185
- if($rvg_wp_only == 'Y') $rvg_wp_only_checked = ' checked="checked"'; else $rvg_wp_only_checked = '';
186
  if($rvg_odb_logging_on == 'Y') $rvg_odb_logging_on_checked = ' checked="checked"'; else $rvg_odb_logging_on = '';
187
  ?>
 
 
 
188
  <tr>
189
- <td align="right" valign="top"><label for="rvg_clear_trash" style="font-weight:bold;">Delete all trashed items<br />
190
- </label></td>
191
- <td valign="top"><input name="rvg_clear_trash" type="checkbox" value="Y" <?php echo $rvg_clear_trash_checked?> /></td>
192
- </tr>
193
- <tr>
194
- <td align="right" valign="top"><label for="rvg_clear_spam" style="font-weight:bold;">Delete all spammed items<br />
195
- </label></td>
196
- <td valign="top"><input name="rvg_clear_spam" type="checkbox" value="Y" <?php echo $rvg_clear_spam_checked?> /></td>
197
- </tr>
198
- <tr>
199
- <td align="right" valign="top"><label for="rvg_wp_only" style="font-weight:bold;">Only optimize WordPress tables<br />
200
- (if not checked <u>ALL</u> tables in the database will be optimized)<br />
201
- </label></td>
202
- <td valign="top"><input name="rvg_wp_only" type="checkbox" value="Y" <?php echo $rvg_wp_only_checked?> /></td>
203
- </tr>
204
- <tr>
205
- <td align="right" valign="top"><label for="rvg_odb_logging_on" style="font-weight:bold;">Logging on</label></td>
206
- <td valign="top"><input name="rvg_odb_logging_on" type="checkbox" value="Y" <?php echo $rvg_odb_logging_on_checked?> /></td>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
207
  </tr>
 
 
 
208
  <tr>
209
- <td align="right" valign="top"><label for="rvg_odb_schedule" style="font-weight:bold;">Scheduler</label></td>
210
- <td valign="top"><select name="rvg_odb_schedule" id="rvg_odb_schedule">
211
- <option selected="selected" value="">NOT SCHEDULED</option>
212
- <option value="hourly">run optimization HOURLY</option>
213
- <option value="twicedaily">run optimization TWICE A DAY</option>
214
- <option value="daily">run optimization DAILY</option>
215
- <option value="weekly">run optimization WEEKLY</option>
216
- <?php /*?> <option value="once_per_5_minutes">run optimization EVERY 5 MINUTES</option>
217
- <?php */?>
218
- </select>
219
- <script type="text/javascript">
220
- document.options.rvg_odb_schedule.value = '<?php echo $rvg_odb_schedule; ?>';
221
- </script></td>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
222
  </tr>
223
  </table>
224
  </fieldset>
@@ -270,13 +313,6 @@ function rvg_optimize_db()
270
  update_option('rvg_clear_spam', $clear_spam);
271
  }
272
  $clear_spam_yn = ($clear_spam == 'N') ? 'NO' : 'YES';
273
-
274
- $wp_only = get_option('rvg_wp_only');
275
- if(!$wp_only)
276
- { $wp_only = 'N';
277
- update_option('rvg_wp_only', $wp_only);
278
- }
279
- $wp_only_yn = ($wp_only == 'N') ? 'NO' : 'YES';
280
 
281
  $rvg_odb_logging_on = get_option('rvg_odb_logging_on');
282
  if(!$rvg_odb_logging_on)
@@ -314,6 +350,14 @@ function rvg_optimize_db()
314
  $total_savings = get_option('rvg_odb_total_savings');
315
 
316
  $log_url = plugins_url().'/rvg-optimize-database/rvg-optimize-db-log.html';
 
 
 
 
 
 
 
 
317
  ?>
318
  <div style="padding-left:8px;">
319
  <h2>Optimize your WordPress Database</h2>
@@ -322,13 +366,13 @@ function rvg_optimize_db()
322
  { echo '<div class="updated" style="position:relative;left:-15px;"><p><strong>Optimize Database after Deleting Revisions - LOG FILE DELETED</strong></p></div>';
323
  }
324
  ?>
325
- <p><span style="font-style:italic;"><a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank" style="font-weight:bold;">Optimize Database after Deleting Revisions v<?php echo $odb_version?></a> - A WordPress Plugin by <a href="http://cagewebdev.com/" target="_blank" style="font-weight:bold;">Rolf van Gelder</a>, Eindhoven, The Netherlands</span></p>
326
  <p>Current options:<br />
327
  <strong>Maximum number of - most recent - revisions to keep per post / page:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $max_revisions?></span><br />
328
  <strong>Delete trashed items:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $clear_trash_yn?></span><br />
329
  <strong>Delete spammed items:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $clear_spam_yn?></span><br />
330
- <strong>Only optimize WordPress tables:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $wp_only_yn?></span><br />
331
  <strong>Logging on:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $rvg_odb_logging_on_yn?></span><br />
 
332
  <strong>Scheduler:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $rvg_odb_schedule_txt?></span>
333
  <?php
334
  if($nextrun)
@@ -529,6 +573,31 @@ function rvg_optimize_db()
529
  // NUMBER OF SPAM DELETED FOR LOG FILE
530
  $log_arr["spam"] = $total_deleted;
531
  ?>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
532
  <?php
533
  /****************************************************************************************
534
 
@@ -862,6 +931,37 @@ function rvg_delete_spam($results, $display)
862
  } // rvg_delete_spam()
863
  ?>
864
  <?php
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
865
  /********************************************************************************************
866
 
867
  OPTIMIZE DATABASE TABLES
@@ -871,18 +971,17 @@ function rvg_optimize_tables($display)
871
  {
872
  global $wpdb, $table_prefix;
873
 
874
- $wp_only = get_option('rvg_wp_only');
875
- if(!$wp_only)
876
- { $wp_only = 'N';
877
- update_option('rvg_wp_only', $wp_only);
878
- }
879
 
880
  $names = mysql_list_tables(DB_NAME);
881
  $cnt = 0;
882
  while($row = mysql_fetch_row($names))
883
  {
884
- if($wp_only == 'N' || ($wp_only == 'Y' && substr($row[0],0,strlen($table_prefix)) == $table_prefix))
885
- { # ALL TABLES OR THIS IS A WORDPRESS TABLE
 
 
886
  $cnt++;
887
  $query = "OPTIMIZE TABLE ".$row[0];
888
  $result = $wpdb -> get_results($query);
@@ -917,6 +1016,39 @@ function rvg_optimize_tables($display)
917
  } // rvg_optimize_tables()
918
  ?>
919
  <?php
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
920
  /********************************************************************************************
921
 
922
  WRITE LINE TO LOG FILE
@@ -968,7 +1100,7 @@ td {
968
  </head>
969
  <body>
970
  <div id="header">
971
- <h2><a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank">Optimize Database after Deleting Revisions v2.1</a></h2>
972
  A WordPress Plugin by <a href="http://cagewebdev.com" target="_blank"><strong>CAGE Web Design | Rolf van Gelder</strong></a>, Eindhoven, The Netherlands</strong>
973
  </div>
974
  <table width="100%" border="0" cellspacing="6" cellpadding="1">
1
  <?php
2
+ $odb_version = '2.2';
3
+ $odb_release_date = '01/11/2013';
4
  /**
5
  * @package Optimize Database after Deleting Revisions
6
+ * @version 2.2
7
  */
8
  /*
9
  Plugin Name: Optimize Database after Deleting Revisions
10
  Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/
11
  Description: Optimizes the Wordpress Database after Cleaning it out - <a href="options-general.php?page=rvg_odb_admin"><strong>plug in options</strong></a>
12
  Author: Rolf van Gelder, Eindhoven, The Netherlands
13
+ Version: 2.2
14
  Author URI: http://cagewebdev.com
15
  */
16
  ?>
34
 
35
  *********************************************************************************************/
36
  function rvg_odb_admin_menu()
37
+ {
38
+ if (function_exists('add_options_page'))
39
  { add_options_page(__('Optimize DB Options'), __('Optimize DB Options'), 'manage_options', 'rvg_odb_admin', 'rvg_odb_options_page');
40
  }
41
  }
50
  add_filter('cron_schedules', 'rvg_extra_schedules');
51
  function rvg_extra_schedules(){
52
  return array(
 
 
 
 
 
53
  'weekly' => array(
54
  'interval' => 604800,
55
  'display' => 'Once weekly'
74
  if( !wp_next_scheduled( 'rvg_optimize_database' ))
75
  wp_schedule_event( time(), $rvg_odb_schedule, 'rvg_optimize_database' );
76
  }
77
+ } # rvg_activate_plugin()
78
 
79
 
80
  /********************************************************************************************
83
 
84
  *********************************************************************************************/
85
  function rvg_odb_options_page() {
86
+ global $odb_version, $odb_release_date, $wpdb, $table_prefix;
87
+
88
+ # jQuery FRAMEWORK
89
+ echo '<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>';
90
+
91
+ # RVG_WP_ONLY IS DEPRECIATED FROM v2.2
92
+ rvg_fix_wp_only();
93
 
94
+ if($_REQUEST['delete_log'] == "Y") @unlink(dirname(__FILE__).'/rvg-optimize-db-log.html');
 
95
 
96
  // SAVE THE OPTIONS
97
+ if (isset($_POST['info_update']))
98
+ {
99
  check_admin_referer();
100
 
101
+ # DELETE ALL EXCLUDED TABLES
102
+ $sql = "
103
+ DELETE FROM $wpdb->options
104
+ WHERE `option_name` LIKE 'rvg_ex_%'
105
+ ";
106
+ $wpdb -> get_results($sql);
107
+
108
+ # ADD EXCLUDED TABLES
109
+ foreach ($_POST as $key => $value)
110
+ { if(substr($key,0,3) == 'cb_')
111
+ { $sql = "
112
+ INSERT INTO $wpdb->options (option_name, option_value, autoload)
113
+ VALUES ('rvg_ex_".substr($key,3)."','excluded','yes')
114
+ ";
115
+ $wpdb -> get_results($sql);
116
+ }
117
+ }
118
+
119
  $rvg_odb_number = trim($_POST['rvg_odb_number']);
120
  update_option('rvg_odb_number', $rvg_odb_number);
121
 
127
  if($_POST['rvg_clear_spam'] == 'Y') $rvg_clear_spam = 'Y';
128
  update_option('rvg_clear_spam', $rvg_clear_spam);
129
 
 
 
 
 
130
  $rvg_odb_logging_on = 'N';
131
  if($_POST['rvg_odb_logging_on'] == 'Y') $rvg_odb_logging_on = 'Y';
132
  update_option('rvg_odb_logging_on', $rvg_odb_logging_on);
138
  // CLEAR CURRENT SCHEDULE (IF ANY)
139
  wp_clear_scheduled_hook('rvg_optimize_database');
140
 
141
+ // HAS TO BE SCHEDULED
142
+ if($rvg_odb_schedule != '')
143
+ if( !wp_next_scheduled( 'rvg_optimize_database' ))
144
  wp_schedule_event( time(), $rvg_odb_schedule, 'rvg_optimize_database' );
 
 
145
 
146
  // UPDATED MESSAGE
147
  echo "<div class='updated'><p><strong>Optimize Database after Deleting Revisions OPTIONS UPDATED</strong> - Click <a href='tools.php?page=rvg-optimize-db.php' style='font-weight:bold'>HERE</a> to run the optimization</p></div>";
156
  $rvg_clear_spam = get_option('rvg_clear_spam');
157
  if(!$rvg_clear_spam) $rvg_clear_spam = 'N';
158
 
 
 
 
159
  $rvg_odb_logging_on = get_option('rvg_odb_logging_on');
160
  if(!$rvg_odb_logging_on) $rvg_odb_logging_on = 'N';
161
 
182
  <p>&nbsp;</p>
183
  </blockquote>
184
  <h2>Optimize Database after Deleting Revisions - Options</h2>
185
+ <?php
 
 
 
 
 
 
 
 
186
  if($rvg_clear_trash == 'Y') $rvg_clear_trash_checked = ' checked="checked"'; else $rvg_clear_trash_checked = '';
187
  if($rvg_clear_spam == 'Y') $rvg_clear_spam_checked = ' checked="checked"'; else $rvg_clear_spam_checked = '';
 
188
  if($rvg_odb_logging_on == 'Y') $rvg_odb_logging_on_checked = ' checked="checked"'; else $rvg_odb_logging_on = '';
189
  ?>
190
+ <blockquote>
191
+ <fieldset class='options'>
192
+ <table class="editform" cellspacing="2" cellpadding="5" width="100%">
193
  <tr>
194
+ <td colspan="3" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="4">
195
+ <tr>
196
+ <td width="50%" align="right" valign="top"><span style="font-weight:bold;">Maximum number of - most recent - revisions to keep per post / page<br />
197
+ ('0' means: delete <u>ALL</u> revisions)</span></td>
198
+ <td width="50%" valign="top"><input type="text" size="5" name="rvg_odb_number" id="rvg_odb_number" value="<?php echo $rvg_odb_number?>" style="font-weight:bold;color:#00F;" /></td>
199
+ </tr>
200
+ <tr>
201
+ <td width="50%" align="right" valign="top"><span style="font-weight:bold;">Delete all trashed items</span></td>
202
+ <td width="50%" valign="top"><input name="rvg_clear_trash" type="checkbox" value="Y" <?php echo $rvg_clear_trash_checked?> /></td>
203
+ </tr>
204
+ <tr>
205
+ <td width="50%" align="right" valign="top"><span style="font-weight:bold;">Delete all spammed items</span></td>
206
+ <td width="50%" valign="top"><input name="rvg_clear_spam" type="checkbox" value="Y" <?php echo $rvg_clear_spam_checked?> /></td>
207
+ </tr>
208
+ <tr>
209
+ <td width="50%" align="right" valign="top"><span style="font-weight:bold;">Logging on</span></td>
210
+ <td width="50%" valign="top"><input name="rvg_odb_logging_on" type="checkbox" value="Y" <?php echo $rvg_odb_logging_on_checked?> /></td>
211
+ </tr>
212
+ <tr>
213
+ <td width="50%" align="right" valign="top"><span style="font-weight:bold;">Scheduler</span></td>
214
+ <td width="50%" valign="top"><select name="rvg_odb_schedule" id="rvg_odb_schedule">
215
+ <option selected="selected" value="">NOT SCHEDULED</option>
216
+ <option value="hourly">run optimization HOURLY</option>
217
+ <option value="twicedaily">run optimization TWICE A DAY</option>
218
+ <option value="daily">run optimization DAILY</option>
219
+ <option value="weekly">run optimization WEEKLY</option>
220
+ </select>
221
+ <script type="text/javascript">
222
+ document.options.rvg_odb_schedule.value = '<?php echo $rvg_odb_schedule; ?>';
223
+ </script></td>
224
+ </tr>
225
+ </table></td>
226
  </tr>
227
+ <?php
228
+ $names = mysql_list_tables(DB_NAME);
229
+ ?>
230
  <tr>
231
+ <td colspan="4" valign="top"><table id="table_list" width="100%" border="0" cellspacing="0" cellpadding="4" style="display:block;">
232
+ <tr>
233
+ <td colspan="4" align="center"><span style="font-weight:bold;">EXCLUDE DATABASE TABLES FROM OPTIMIZATION: CHECKED TABLES <u>WON'T</u> BE OPTIMIZED!</span><br />
234
+ <a href="javascript:;" onclick="$('[id^=cb_]').attr('checked',true);">check all tables</a> | <a href="javascript:;" onclick="$('[id^=cb_]').attr('checked',false);">uncheck all tables</a> | <a href="javascript:;" onclick="$(':not([id^=cb_<?php echo $table_prefix; ?>])').attr('checked',true);">check all NON-WordPress tables</a></td>
235
+ </tr>
236
+ <tr>
237
+ <?php
238
+ $c = 0;
239
+ $t = 0;
240
+ while($row = mysql_fetch_row($names))
241
+ { $t++;
242
+ $c++;
243
+ if($c>4)
244
+ { $c = 1;
245
+ echo '</tr>';
246
+ echo '<tr>';
247
+ }
248
+ $style = 'normal';
249
+ // WORDPRESS TABLE?
250
+ if(substr($row[0],0,strlen($table_prefix)) == $table_prefix) $style = 'bold;color:#00F;';
251
+
252
+ $cb_checked = '';
253
+ $sql = "
254
+ SELECT `option_value`
255
+ FROM $wpdb->options
256
+ WHERE `option_name` = 'rvg_ex_".$row[0]."'
257
+ ";
258
+ $results = $wpdb -> get_results($sql);
259
+ if($results[0]->option_value == 'excluded') $cb_checked = ' checked';
260
+ echo '<td width="25%" style="font-weight:'.$style.'"><input id="cb_'.$row[0].'" name="cb_'.$row[0].'" type="checkbox" value="1" '.$cb_checked.' /> '.$row[0].'</td>'."\n";
261
+ } # while($row = mysql_fetch_row($names))
262
+ ?>
263
+ </tr>
264
+ </table></td>
265
  </tr>
266
  </table>
267
  </fieldset>
313
  update_option('rvg_clear_spam', $clear_spam);
314
  }
315
  $clear_spam_yn = ($clear_spam == 'N') ? 'NO' : 'YES';
 
 
 
 
 
 
 
316
 
317
  $rvg_odb_logging_on = get_option('rvg_odb_logging_on');
318
  if(!$rvg_odb_logging_on)
350
  $total_savings = get_option('rvg_odb_total_savings');
351
 
352
  $log_url = plugins_url().'/rvg-optimize-database/rvg-optimize-db-log.html';
353
+
354
+ $sql = "
355
+ SELECT COUNT(*) cnt
356
+ FROM $wpdb->options op
357
+ WHERE op.option_name LIKE 'rvg_ex_%'
358
+ ";
359
+ $results = $wpdb -> get_results($sql);
360
+ $number_excluded = $results[0] -> cnt;
361
  ?>
362
  <div style="padding-left:8px;">
363
  <h2>Optimize your WordPress Database</h2>
366
  { echo '<div class="updated" style="position:relative;left:-15px;"><p><strong>Optimize Database after Deleting Revisions - LOG FILE DELETED</strong></p></div>';
367
  }
368
  ?>
369
+ <p><span style="font-style:italic;"><a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank" style="font-weight:bold;">Optimize Database after Deleting Revisions v<?php echo $odb_version?></a> - A WordPress Plugin by <a href="http://cagewebdev.com/" target="_blank" style="font-weight:bold;">CAGE Web Design</a> | <a href="http://cage.nl/" target="_blank" style="font-weight:bold;">Rolf van Gelder</a>, Eindhoven, The Netherlands</span></p>
370
  <p>Current options:<br />
371
  <strong>Maximum number of - most recent - revisions to keep per post / page:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $max_revisions?></span><br />
372
  <strong>Delete trashed items:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $clear_trash_yn?></span><br />
373
  <strong>Delete spammed items:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $clear_spam_yn?></span><br />
 
374
  <strong>Logging on:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $rvg_odb_logging_on_yn?></span><br />
375
+ <strong>Number of excluded tables:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $number_excluded?></span><br />
376
  <strong>Scheduler:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $rvg_odb_schedule_txt?></span>
377
  <?php
378
  if($nextrun)
573
  // NUMBER OF SPAM DELETED FOR LOG FILE
574
  $log_arr["spam"] = $total_deleted;
575
  ?>
576
+ <?php
577
+ $total_deleted = rvg_delete_orphans(true);
578
+ if($total_deleted)
579
+ {
580
+ ?>
581
+ <span style="font-weight:bold;color:#000;padding-left:8px;">~~~~~</span>
582
+ <table border="0" cellspacing="8" cellpadding="2">
583
+ <tr>
584
+ <td colspan="4"><span style="font-weight:bold;color:#00F;">NUMBER POSTMETA ORPHANS DELETED:</span> <span style="font-weight:bold;"><?php echo $total_deleted;?></span></td>
585
+ </tr>
586
+ </table>
587
+ <?php
588
+ }
589
+ else
590
+ {
591
+ ?>
592
+ <span style="font-weight:bold;color:#000;padding-left:8px;">~~~~~</span>
593
+ <table border="0" cellspacing="8" cellpadding="2">
594
+ <tr>
595
+ <td style="font-weight:bold;color:#21759b;">No POSTMETA ORPHANS found to delete...</td>
596
+ </tr>
597
+ </table>
598
+ <?php
599
+ }
600
+ ?>
601
  <?php
602
  /****************************************************************************************
603
 
931
  } // rvg_delete_spam()
932
  ?>
933
  <?php
934
+ /********************************************************************************************
935
+
936
+ DELETE ORPHAN POSTMETA RECORDS
937
+
938
+ *********************************************************************************************/
939
+ function rvg_delete_orphans($display)
940
+ {
941
+ global $wpdb;
942
+
943
+ $sql_delete = "
944
+ SELECT COUNT(*) cnt
945
+ FROM $wpdb->postmeta pm
946
+ WHERE pm.post_id NOT IN (SELECT p.ID FROM $wpdb->posts)
947
+ ";
948
+ $results = $wpdb -> get_results($sql_delete);
949
+
950
+ $total_deleted = $results[0] -> cnt;
951
+
952
+ if($total_deleted > 0)
953
+ { $sql_delete = "
954
+ DELETE FROM $wpdb->postmeta pm
955
+ WHERE pm.post_id NOT IN (SELECT p.ID FROM $wpdb->posts)
956
+ ";
957
+ $wpdb -> get_results($sql_delete);
958
+ }
959
+
960
+ return $total_deleted;
961
+
962
+ } // rvg_delete_orphans()
963
+ ?>
964
+ <?php
965
  /********************************************************************************************
966
 
967
  OPTIMIZE DATABASE TABLES
971
  {
972
  global $wpdb, $table_prefix;
973
 
974
+ # WP_ONLY IS DEPRECIATED FROM v2.2
975
+ rvg_fix_wp_only();
 
 
 
976
 
977
  $names = mysql_list_tables(DB_NAME);
978
  $cnt = 0;
979
  while($row = mysql_fetch_row($names))
980
  {
981
+ $excluded = get_option('rvg_ex_'.$row[0]);
982
+
983
+ if(!$excluded)
984
+ { # TABLE NOT EXCLUDED
985
  $cnt++;
986
  $query = "OPTIMIZE TABLE ".$row[0];
987
  $result = $wpdb -> get_results($query);
1016
  } // rvg_optimize_tables()
1017
  ?>
1018
  <?php
1019
+ /********************************************************************************************
1020
+
1021
+ FIX WP_ONLY (DEPRECIATED FROM v2.2)
1022
+
1023
+ *********************************************************************************************/
1024
+ function rvg_fix_wp_only()
1025
+ {
1026
+ global $wpdb, $table_prefix;
1027
+
1028
+ $wp_only = get_option('rvg_wp_only');
1029
+ if($wp_only == 'Y')
1030
+ {
1031
+ $names = mysql_list_tables(DB_NAME);
1032
+ while($row = mysql_fetch_row($names))
1033
+ { if(substr($row[0],0,strlen($table_prefix)) != $table_prefix)
1034
+ { // NOT A WORDPRESS TABLE: EXLUDE IT
1035
+ $sql = "
1036
+ INSERT INTO $wpdb->options (option_name, option_value, autoload)
1037
+ VALUES ('rvg_ex_".$row[0]."','excluded','yes')
1038
+ ";
1039
+ $wpdb -> get_results($sql);
1040
+ }
1041
+ }
1042
+ }
1043
+ $sql = "
1044
+ DELETE FROM $wpdb->options
1045
+ WHERE `option_name` = 'rvg_wp_only'
1046
+ ";
1047
+ $wpdb -> get_results($sql);
1048
+
1049
+ } # function rvg_fix_wp_only()
1050
+ ?>
1051
+ <?php
1052
  /********************************************************************************************
1053
 
1054
  WRITE LINE TO LOG FILE
1100
  </head>
1101
  <body>
1102
  <div id="header">
1103
+ <h2><a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank">Optimize Database after Deleting Revisions v'.$odb_version.'</a></h2>
1104
  A WordPress Plugin by <a href="http://cagewebdev.com" target="_blank"><strong>CAGE Web Design | Rolf van Gelder</strong></a>, Eindhoven, The Netherlands</strong>
1105
  </div>
1106
  <table width="100%" border="0" cellspacing="6" cellpadding="1">