Optimize Database after Deleting Revisions - Version 2.0

Version Description

[12/18/2012] = * NEW: Logging of the Optimizations (optional) * NEW: Scheduling Optimizations for Automatic Execution (optional) * Many other (technical and cosmetical) changes and improvements

Download this release

Release Info

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

Code changes from version 1.3.4 to 2.0

Files changed (2) hide show
  1. readme.txt +105 -47
  2. rvg-optimize-db.php +732 -209
readme.txt CHANGED
@@ -1,69 +1,127 @@
1
- === Plugin Name ===
2
-
3
- Contributors: Rolf van Gelder
4
  Donate link: http://cagewebdev.com
5
  Plugin Name: Optimize Database after Deleting Revisions
6
  Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin
7
- Tags: database, delete, revisions, optimize, post, posts, page, pages, clean up, trash, spam, trashed, spammed
8
  Author URI: http://cagewebdev.com
9
- Author: Rolf van Gelder, Eindhoven, The Netherlands
10
  Requires at least: 2.0
11
  Tested up to: 3.5
12
- Stable tag: 1.3.4
13
- Version: 1.3.4
14
 
15
  == Description ==
16
 
17
- <p><b>This plugin is a 'one click' database optimizer.</b></p>
18
- <p>It deletes the redundant revisions of posts and pages, trashed items and/or spammed items and, after that, optimizes all database tables.</p>
19
- <p>http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin</p>
20
- <p>Author: Rolf van Gelder, Eindhoven, The Netherlands - http://cagewebdev.com</p>
21
 
22
- == Installation ==
 
 
 
 
 
 
 
 
 
 
 
 
 
23
 
24
- <ol>
25
- <li>Upload `rvg-optimize-db.php` to the `/wp-content/plugins/` directory</li>
26
- <li>Activate the plugin through the 'Plugins' menu in the WordPress Dashboard</li>
27
- <li>Change the settings (if needed) through WordPress Dashboard, Settings, Optimize DB Options</li>
28
- </ol>
29
 
30
- == Upgrade Notice ==
 
31
 
32
- No Upgrade Notice available.
 
 
 
 
 
 
 
33
 
34
  == Screenshots ==
 
 
35
 
36
- No Screenshots available.
37
 
38
  == Changelog ==
39
 
40
- <p><b>1.0</b> 11/22/2011 Initial release</p>
41
- <p><b>1.0.1</b> 11/24/2011 A few updates for the readme.txt file</p>
42
- <p><b>1.0.2</b> 12/02/2011 Some minor updates</p>
43
- <p><b>1.0.3</b> 12/15/2011 Some minor layout updates</p>
44
- <p><b>1.0.4</b> 06/06/2012 Now also works with non short_open_tag's</p>
45
- <p><b>1.0.5</b> 08/21/2012 Depreciated item ('has_cap') replaced, abandoned line of code removed</p>
46
- <p><b>1.1</b> 08/29/2012 Added: a new option page, in de plugins section, where you can define the maximum number of - most recent - revisions you want to keep per post or page</p>
47
- <p><b>1.1.2</b> 08/30/2012 Minor bug fix for the new option page</p>
48
- <p><b>1.1.3</b> 09/01/2012 Moved the 'Optimize DB Options' item to Dashboard 'Settings' Menu and the 'Optimize Database' item to the Dashboard 'Tools' Menu. That makes more sense!</p>
49
- <p><b>1.1.4</b> 09/01/2012 Something went wrong deploying 1.1.3, so I deployed it again as 1.1.4</p>
50
- <p><b>1.1.5</b> 09/01/2012 Something went wrong deploying 1.1.4, so I deployed it again as 1.1.5. *sigh*</p>
51
- <p><b>1.1.6</b> 09/01/2012 Fixed the link to the options page</p>
52
- <p><b>1.1.7</b> 09/03/2012 Some textual and link fixes</p>
53
- <p><b>1.1.8</b> 09/08/2012 Another link fix</p>
54
- <p><b>1.1.9</b> 09/27/2012 Using a different method for retrieving database table names</p>
55
- <p><b>1.2</b> 10/03/2012 Major update: new options 'delete trash', 'delete spam', 'only optimize WordPress tables'</p>
56
- <p><b>1.3</b> 10/06/2012 Extra button for starting optimization, shows savings (in bytes) now</p>
57
- <p><b>1.3.1</b> 10/07/2012 Minor changes</p>
58
- <p><b>1.3.2</b> 11/14/2012 Shows more information about the optimized tables + other minor changes</p>
59
- <p><b>1.3.3</b> 12/01/2012 Some layout changes</p>
60
- <p><b>1.3.4</b> 12/14/2012 Changed the buttons for WP 3.5</p>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
61
 
62
  == Frequently Asked Questions ==
63
 
64
- <p><b>Q:</b> <em>How can I change the settings of this plugin?</em></p>
65
- <p><b>A:</b> In the WordPress Dashboard go to '<b>Settings / Optimize DB Options</b>'. There you can define the maximum number of - most recent - revisions you want to keep per post or page and some more options.</p>
66
- <p><b>Q:</b> <em>How do I run this plugin?</em></p>
67
- <p><b>A:</b> In the WordPress Dashboard go to '<b>Tools</b>'. Click on '<b>Optimize Database</b>'. Then click the 'Start Optimization'-button. Et voila!</p>
68
- <p><b>Q:</b> <em>Why do I see 'Table does not support optimize, doing recreate + analyze instead' while optimizing my database?</em></p>
69
- <p><b>A:</b> That is because the table type of that table is not 'MyISAM'</p>
 
 
 
1
+ === Optimize Database after Deleting Revisions ===
2
+ Contributors: CAGE Web Design | Rolf van Gelder
 
3
  Donate link: http://cagewebdev.com
4
  Plugin Name: Optimize Database after Deleting Revisions
5
  Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin
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.0
10
  Tested up to: 3.5
11
+ Stable tag: 2.0
12
+ Version: 2.0
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
+
26
+ = Settings =
27
+ You can find the settings page in the WP Admin Panel &raquo; Settings &raquo; Optimize DB Options.
28
+
29
+ = Starting the Optimization =
30
+ You can start the Optimization in the WP Admin Panel &raquo; Tools &raquo; Optimize Database.
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
38
 
39
+ = Download URL =
40
+ http://wordpress.org/extend/plugins/rvg-optimize-database/
41
+
42
+ == Installation ==
43
+
44
+ * Upload the Plugin to the `/wp-content/plugins/` directory
45
+ * Activate the plugin in the WP Admin Panel &raquo; Plugins
46
+ * Change the settings (if needed) in the WP Admin Panel &raquo; Settings &raquo; Optimize DB Options
47
 
48
  == Screenshots ==
49
+ 1. Optimize Database after Deleting Revisions - Options
50
+ 2. Run the Optimizer
51
 
 
52
 
53
  == Changelog ==
54
 
55
+ = 2.0 [12/18/2012] =
56
+ * NEW: Logging of the Optimizations (optional)
57
+ * NEW: Scheduling Optimizations for Automatic Execution (optional)
58
+ * Many other (technical and cosmetical) changes and improvements
59
+
60
+ = 1.3.4 [12/14/2012] =
61
+ * Changed the buttons for WP 3.5
62
+
63
+ = 1.3.3 [12/01/2012] =
64
+ * Some layout changes
65
+
66
+ = 1.3.2 [11/14/2012] =
67
+ * Shows more information about the optimized tables + other minor changes
68
+
69
+ = 1.3.1 [10/07/2012] =
70
+ * Minor changes
71
+
72
+ = 1.3 [10/06/2012] =
73
+ * Extra button for starting optimization, shows savings (in bytes) now
74
+
75
+ = 1.2 [10/03/2012] =
76
+ * Major update: new options 'delete trash', 'delete spam', 'only optimize WordPress tables'
77
+
78
+ = 1.1.9 [09/27/2012] =
79
+ * Using a different method for retrieving database table names
80
+
81
+ = 1.1.8 [09/08/2012] =
82
+ * Another link fix
83
+
84
+ = 1.1.7 [09/03/2012] =
85
+ * Some textual and link fixes
86
+
87
+ = 1.1.6 [09/01/2012] =
88
+ * Fixed the link to the options page
89
+
90
+ = 1.1.3 [09/01/2012] =
91
+ * Moved the 'Optimize DB Options' item to Dashboard 'Settings' Menu and the 'Optimize Database' item to the Dashboard 'Tools' Menu. That makes more sense!
92
+
93
+ = 1.1.2 [08/30/2012] =
94
+ * Minor bug fix for the new option page
95
+
96
+ = 1.1 [08/29/2012] =
97
+ * Added: a new option page, in de plugins section, where you can define the maximum number of - most recent - revisions you want to keep per post or page
98
+
99
+ = 1.0.5 [08/21/2012] =
100
+ * Depreciated item ('has_cap') replaced, abandoned line of code removed
101
+
102
+ = 1.0.4 [06/06/2012] =
103
+ * Now also works with non short_open_tag's
104
+
105
+ = 1.0.3 [12/15/2011] =
106
+ * Some minor layout updates
107
+
108
+ = 1.0.2 [12/02/2011] =
109
+ * Some minor updates
110
+
111
+ = 1.0.1 [11/24/2011] =
112
+ * A few updates for the readme.txt file
113
+
114
+ = 1.0 [11/22/2011] =
115
+ * Initial release
116
 
117
  == Frequently Asked Questions ==
118
 
119
+ = How can I change the settings of this plugin? =
120
+ * WP Admin Panel &raquo; Settings &raquo; 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.
121
+
122
+ = How do I run this plugin? =
123
+ * WP Admin Panel &raquo; Tools &raquo; Optimize Database. Then click the 'Start Optimization'-button. Et voila!
124
+
125
+ = Why do I see 'Table does not support optimize, doing recreate + analyze instead' while optimizing my database? =
126
+ * That is because the table type of that table is not 'MyISAM'
127
+
rvg-optimize-db.php CHANGED
@@ -1,16 +1,16 @@
1
  <?php
2
- $odb_version = '1.3.4';
3
- $odb_release_date = '12/14/2012';
4
  /**
5
  * @package Optimize Database after Deleting Revisions
6
- * @version 1.3.4
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 Deleting Revisions - <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: 1.3.4
14
  Author URI: http://cagewebdev.com
15
  */
16
  ?>
@@ -41,6 +41,46 @@ function rvg_odb_admin_menu()
41
  add_action( 'admin_menu', 'rvg_odb_admin_menu' );
42
 
43
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
44
  /********************************************************************************************
45
 
46
  CREATE THE OPTIONS PAGE
@@ -49,6 +89,9 @@ add_action( 'admin_menu', 'rvg_odb_admin_menu' );
49
  function rvg_odb_options_page() {
50
  global $odb_version, $odb_release_date;
51
 
 
 
 
52
  // SAVE THE OPTIONS
53
  if ( isset( $_POST['info_update'] ) ) {
54
  check_admin_referer();
@@ -66,11 +109,30 @@ function rvg_odb_options_page() {
66
 
67
  $rvg_wp_only = 'N';
68
  if($_POST['rvg_wp_only'] == 'Y') $rvg_wp_only = 'Y';
69
- update_option('rvg_wp_only', $rvg_wp_only);
70
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
71
  // UPDATED MESSAGE
72
- echo "<div class='updated'><p><strong>Optimize Database after Deleting Revisions options updated</strong> - Click <a href='tools.php?page=rvg-optimize-db.php&action=run' style='font-weight:bold'>HERE</a> to run the optimization</p></div>";
73
  }
 
74
  $rvg_odb_number = get_option('rvg_odb_number');
75
  if(!$rvg_odb_number) $rvg_odb_number = '0';
76
 
@@ -81,32 +143,30 @@ function rvg_odb_options_page() {
81
  if(!$rvg_clear_spam) $rvg_clear_spam = 'N';
82
 
83
  $rvg_wp_only = get_option('rvg_wp_only');
84
- if(!$rvg_wp_only) $rvg_wp_only = 'N';
85
-
86
- // CREATE THE OPTIONS PAGE
 
 
 
 
87
  ?>
88
 
89
- <form method="post" action="">
90
  <div class="wrap">
91
  <h2>Using Optimize Database after Deleting Revisions</h2>
92
  <blockquote>
93
- <p><strong>'<em>Optimize Database after Deleting Revisions</em>' is an one-click plugin to optimize your WordPress database.<br />
94
- It deletes redundant revisions of posts and pages, trashed and/or spammed items and, after that, optimizes all database tables.</strong></p>
95
- <p>Below you can define the <u>maximum number</u> of - most recent - revisions you want to <u>keep</u> per post or page.<br />
96
- If you set the maximum number to '<strong>0</strong>' it means <strong>ALL REVISIONS</strong> will be deleted for all posts and pages.</p>
97
- <p>You also can choose if you want to <u>delete</u> all <u>trashed items</u> and/or <u>spammed items</u> during the optimization.</p>
98
  <p>To start the optimization:<br />
99
- In the WordPress Dashboard go to &lsquo;<strong>Tools</strong>&lsquo;.<br />
100
- Click on &lsquo;<strong>Optimize Database</strong>&lsquo;, then click on the '<strong>Start Optimization</strong>'-button. Et voila! </p>
101
  <p>Plugin version:<br />
102
  <strong>v<?php echo $odb_version ?> (<?php echo $odb_release_date?>)</strong> </p>
103
- <p>Author:<br />
104
- <strong><a href="http://cage.nl/" target="_blank">Rolf van Gelder</a>, <a href="http://cagewebdev.com/" target="_blank">CAGE Web Design</a></strong>, Eindhoven, The Netherlands<br />
105
- <br />
106
- Plugin URL:<br />
107
- <a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank"><strong>http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/</strong></a><strong><br />
108
- </strong><br />
109
- Download URL:<br />
110
  <strong><a href="http://wordpress.org/extend/plugins/rvg-optimize-database/" target="_blank">http://wordpress.org/extend/plugins/rvg-optimize-database/</a></strong></p>
111
  <p>&nbsp;</p>
112
  </blockquote>
@@ -115,30 +175,50 @@ function rvg_odb_options_page() {
115
  <fieldset class='options'>
116
  <table class="editform" cellspacing="2" cellpadding="5">
117
  <tr>
118
- <td align="right"><label for="<?php echo rvg_odb_number; ?>" style="font-weight:bold;">Maximum number of - most recent - revisions to keep per post / page<br />
119
- ('0' means: delete ALL revisions) </label></td>
120
- <td><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>
121
  </tr>
122
  <?php
123
  if($rvg_clear_trash == 'Y') $rvg_clear_trash_checked = ' checked="checked"'; else $rvg_clear_trash_checked = '';
124
  if($rvg_clear_spam == 'Y') $rvg_clear_spam_checked = ' checked="checked"'; else $rvg_clear_spam_checked = '';
125
  if($rvg_wp_only == 'Y') $rvg_wp_only_checked = ' checked="checked"'; else $rvg_wp_only_checked = '';
 
126
  ?>
127
  <tr>
128
- <td align="right"><label for="rvg_clear_trash" style="font-weight:bold;">Delete all trashed items<br />
129
  </label></td>
130
- <td><input name="rvg_clear_trash" type="checkbox" value="Y" <?php echo $rvg_clear_trash_checked?> /></td>
131
  </tr>
132
  <tr>
133
- <td align="right"><label for="rvg_clear_spam" style="font-weight:bold;">Delete all spammed items<br />
134
  </label></td>
135
- <td><input name="rvg_clear_spam" type="checkbox" value="Y" <?php echo $rvg_clear_spam_checked?> /></td>
136
  </tr>
137
  <tr>
138
- <td align="right"><label for="rvg_wp_only" style="font-weight:bold;">Only optimize WordPress tables<br />
139
- (if not checked ALL tables in the database will be optimized)<br />
140
  </label></td>
141
- <td><input name="rvg_wp_only" type="checkbox" value="Y" <?php echo $rvg_wp_only_checked?> /></td>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
142
  </tr>
143
  </table>
144
  </fieldset>
@@ -161,6 +241,9 @@ function rvg_optimize_db()
161
  {
162
  global $wpdb, $odb_version, $table_prefix;
163
 
 
 
 
164
  /****************************************************************************************
165
 
166
  DELETE REVISIONS
@@ -193,50 +276,117 @@ function rvg_optimize_db()
193
  { $wp_only = 'N';
194
  update_option('rvg_wp_only', $wp_only);
195
  }
196
- $wp_only_yn = ($wp_only == 'N') ? 'NO' : 'YES';
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
197
  ?>
198
  <div style="padding-left:8px;">
199
  <h2>Optimize your WordPress Database</h2>
 
 
 
 
 
200
  <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>
201
  <p>Current options:<br />
202
  <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 />
203
  <strong>Delete trashed items:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $clear_trash_yn?></span><br />
204
  <strong>Delete spammed items:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $clear_spam_yn?></span><br />
205
- <strong>Only optimize WordPress tables:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $wp_only_yn?></span>
 
 
206
  <?php
207
- if($_REQUEST['action'] != 'run')
208
- {
 
 
 
 
 
 
 
209
  ?>
 
 
 
 
 
210
  <p class="submit">
211
  <input class="button" type="button" name="change_options" value="Change Options" onclick="self.location='options-general.php?page=rvg_odb_admin'" style="font-weight:normal;" />
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
212
  &nbsp;
213
  <input class="button-primary button-large" type="button" name="start_optimization" value="Start Optimization" onclick="self.location='tools.php?page=rvg-optimize-db.php&action=run'" style="font-weight:bold;" />
214
- </p>
215
- <?php
216
- }
217
  ?>
 
218
  </div>
219
  <?php
220
- if($_REQUEST['action'] != 'run')
221
- { return;
222
- }
223
  ?>
224
- <br />
225
  <h2 style="padding-left:8px;">Starting Optimization...</h2>
226
  <?php
227
  // GET THE SIZE OF THE DATABASE BEFORE OPTIMIZATION
228
  $start_size = rvg_get_db_size();
229
 
230
- $sql = "
231
- SELECT `post_parent`, `post_title`, COUNT(*) cnt
232
- FROM $wpdb->posts
233
- WHERE `post_type` = 'revision'
234
- GROUP BY `post_parent`
235
- HAVING COUNT(*) > ".$max_revisions."
236
- ORDER BY UCASE(`post_title`)
237
- ";
238
- $results = $wpdb -> get_results($sql);
239
-
240
  if(count($results)>0)
241
  { // WE HAVE REVISIONS TO DELETE!
242
  ?>
@@ -250,45 +400,10 @@ if($_REQUEST['action'] != 'run')
250
  <th align="left" style="border-bottom:solid 1px #999;">revision date</th>
251
  <th align="right" style="border-bottom:solid 1px #999;">revisions deleted</th>
252
  </tr>
253
- <?php
254
- $nr = 1;
255
- $total_deleted = 0;
256
- for($i=0; $i<count($results); $i++)
257
- { $nr_to_delete = $results[$i]->cnt - $max_revisions;
258
- $total_deleted += $nr_to_delete;
259
- ?>
260
- <tr>
261
- <td align="right" valign="top"><?php echo $nr?>.</td>
262
- <td valign="top" style="font-weight:bold;"><?php echo $results[$i]->post_title?></td>
263
- <td valign="top"><?php
264
- $sql_get_posts = "
265
- SELECT `ID`, `post_modified`
266
- FROM $wpdb->posts
267
- WHERE `post_parent`=".$results[$i]->post_parent."
268
- AND `post_type`='revision'
269
- ORDER BY `post_modified` ASC
270
- ";
271
- $results_get_posts = $wpdb -> get_results($sql_get_posts);
272
-
273
- for($j=0; $j<$nr_to_delete; $j++)
274
- {
275
- echo $results_get_posts[$j]->post_modified.'<br />';
276
-
277
- $sql_delete = "
278
- DELETE FROM $wpdb->posts
279
- WHERE `ID` = ".$results_get_posts[$j]->ID."
280
- ";
281
- $wpdb -> get_results($sql_delete);
282
-
283
- } // for($j=0; $j<$nr_to_delete; $j++)
284
-
285
- $nr++;
286
- ?></td>
287
- <td align="right" valign="top" style="font-weight:bold;"><?php echo $nr_to_delete?></td>
288
- </tr>
289
- <?php
290
- }
291
- ?>
292
  <tr>
293
  <td colspan="3" align="right" style="border-top:solid 1px #999;font-weight:bold;">total number of revisions deleted</td>
294
  <td align="right" style="border-top:solid 1px #999;font-weight:bold;"><?php echo $total_deleted?></td>
@@ -306,6 +421,9 @@ if($_REQUEST['action'] != 'run')
306
  </table>
307
  <?php
308
  } // if(count($results)>0)
 
 
 
309
  ?>
310
  <?php
311
  /****************************************************************************************
@@ -318,18 +436,9 @@ if($_REQUEST['action'] != 'run')
318
  if($clear_trash == 'Y')
319
  {
320
  // GET TRASHED POSTS / PAGES AND COMMENTS
321
- $sql = "
322
- SELECT `ID` AS id, 'post' AS post_type, `post_title` AS title, `post_modified` AS modified
323
- FROM $wpdb->posts
324
- WHERE `post_status` = 'trash'
325
- UNION ALL
326
- SELECT `comment_ID` AS id, 'comment' AS post_type, `comment_author_IP` AS title, `comment_date` AS modified
327
- FROM $wpdb->comments
328
- WHERE `comment_approved` = 'trash'
329
- ORDER BY post_type, UCASE(title)
330
- ";
331
- $results = $wpdb -> get_results($sql);
332
-
333
  if(count($results)>0)
334
  { // WE HAVE TRASH TO DELETE!
335
  ?>
@@ -344,41 +453,9 @@ if($_REQUEST['action'] != 'run')
344
  <th align="left" style="border-bottom:solid 1px #999;">IP address / title</th>
345
  <th align="left" nowrap="nowrap" style="border-bottom:solid 1px #999;">date</th>
346
  </tr>
347
- <?php
348
- $nr = 1;
349
- $total_deleted = count($results);
350
- for($i=0; $i<count($results); $i++)
351
- {
352
- ?>
353
- <tr>
354
- <td align="right" valign="top"><?php echo $nr; ?></td>
355
- <td valign="top"><?php echo $results[$i]->post_type; ?></td>
356
- <td valign="top"><?php echo $results[$i]->title; ?></td>
357
- <td valign="top" nowrap="nowrap"><?php echo $results[$i]->modified; ?></td>
358
- </tr>
359
- <?php
360
- if($results[$i]->post_type == 'comment')
361
- { // DELETE META DATA (IF ANY...)
362
- $sql_delete = "
363
- DELETE FROM $wpdb->commentmeta WHERE `comment_id` = ".$results[$i]->id."
364
- ";
365
- $wpdb -> get_results($sql_delete);
366
- }
367
-
368
- $nr++;
369
- } // for($i=0; $i<count($results); $i++)
370
-
371
- // DELETE TRASHED POSTS / PAGES
372
- $sql_delete = "
373
- DELETE FROM $wpdb->posts WHERE `post_status` = 'trash'
374
- ";
375
- $wpdb -> get_results($sql_delete);
376
-
377
- // DELETE TRASHED COMMENTS
378
- $sql_delete = "
379
- DELETE FROM $wpdb->comments WHERE `comment_approved` = 'trash'
380
- ";
381
- $wpdb -> get_results($sql_delete);
382
  ?>
383
  </table>
384
  <?php
@@ -395,6 +472,9 @@ if($_REQUEST['action'] != 'run')
395
  <?php
396
  } // if(count($results)>0)
397
 
 
 
 
398
  } // if($clear_trash == 'Y')
399
  ?>
400
  <?php
@@ -407,14 +487,10 @@ if($_REQUEST['action'] != 'run')
407
  <?php
408
  if($clear_spam == 'Y')
409
  {
410
- $sql = "
411
- SELECT `comment_ID`, `comment_author`, `comment_author_email`, `comment_date`
412
- FROM $wpdb->comments
413
- WHERE `comment_approved` = 'spam'
414
- ORDER BY UCASE(`comment_author`)
415
- ";
416
- $results = $wpdb -> get_results($sql);
417
-
418
  if(count($results)>0)
419
  { // WE HAVE SPAM TO DELETE!
420
  ?>
@@ -429,30 +505,9 @@ if($_REQUEST['action'] != 'run')
429
  <th align="left" style="border-bottom:solid 1px #999;">comment author email</th>
430
  <th align="left" nowrap="nowrap" style="border-bottom:solid 1px #999;">comment date</th>
431
  </tr>
432
- <?php
433
- $nr = 1;
434
- $total_deleted = count($results);
435
- for($i=0; $i<count($results); $i++)
436
- {
437
- ?>
438
- <tr>
439
- <td align="right" valign="top"><?php echo $nr; ?></td>
440
- <td valign="top"><?php echo $results[$i]->comment_author; ?></td>
441
- <td valign="top"><?php echo $results[$i]->comment_author_email; ?></td>
442
- <td valign="top" nowrap="nowrap"><?php echo $results[$i]->comment_date; ?></td>
443
- </tr>
444
  <?php
445
- $sql_delete = "
446
- DELETE FROM $wpdb->commentmeta WHERE `comment_id` = ".$results[$i]->comment_ID."
447
- ";
448
- $wpdb -> get_results($sql_delete);
449
- $nr++;
450
- } // for($i=0; $i<count($results); $i++)
451
-
452
- $sql_delete = "
453
- DELETE FROM $wpdb->comments WHERE `comment_approved` = 'spam'
454
- ";
455
- $wpdb -> get_results($sql_delete);
456
  ?>
457
  </table>
458
  <?php
@@ -470,6 +525,9 @@ if($_REQUEST['action'] != 'run')
470
  } // if(count($results)>0)
471
 
472
  } // if($clear_spam == 'Y')
 
 
 
473
  ?>
474
  <?php
475
  /****************************************************************************************
@@ -492,41 +550,26 @@ if($_REQUEST['action'] != 'run')
492
  <th style="border-bottom:solid 1px #999;" align="right">table size</th>
493
  </tr>
494
  <?php
495
- # GET TABLE NAMES
496
- $names = mysql_list_tables(DB_NAME);
497
- $cnt = 0;
498
- while($row = mysql_fetch_row($names))
499
- {
500
- if($wp_only == 'N' || ($wp_only == 'Y' && substr($row[0],0,strlen($table_prefix)) == $table_prefix))
501
- { # ALL TABLES OR THIS IS A WORDPRESS TABLE
502
- $cnt++;
503
- $query = "OPTIMIZE TABLE ".$row[0];
504
- $result = $wpdb -> get_results($query);
505
-
506
- $sql = "
507
- SELECT engine, SUM(data_length + index_length) AS size, table_rows
508
- FROM information_schema.TABLES
509
- WHERE table_schema = '".strtolower(DB_NAME)."'
510
- AND table_name = '".$row[0]."'
511
- ";
512
-
513
- $table_info = $wpdb -> get_results($sql);
514
- ?>
515
- <tr>
516
- <td align="right" valign="top"><?php echo $cnt?>.</td>
517
- <td valign="top" style="font-weight:bold;"><?php echo $row[0] ?></td>
518
- <td valign="top"><?php echo $result[0]->Msg_text ?></td>
519
- <td valign="top"><?php echo $table_info[0]->engine ?></td>
520
- <td align="right" valign="top"><?php echo $table_info[0]->table_rows ?></td>
521
- <td align="right" valign="top"><?php echo rvg_format_size($table_info[0]->size) ?></td>
522
- </tr>
523
- <?php
524
- } // if($wp_only == 'N' || ($wp_only == 'Y' && substr($row[0],0,strlen($table_prefix)) == $table_prefix))
525
- } // while($row = mysql_fetch_row($names))
526
  ?>
527
  </table>
528
  <?php
529
- $end_size = rvg_get_db_size();
 
 
 
 
 
 
 
 
 
 
 
 
 
 
530
  ?>
531
  <span style="font-weight:bold;color:#000;padding-left:8px;">~~~~~</span>
532
  <table border="0" cellspacing="8" cellpadding="2">
@@ -546,16 +589,496 @@ $end_size = rvg_get_db_size();
546
  <td align="right" style="font-weight:bold;"><?php echo rvg_format_size($end_size,3); ?></td>
547
  </tr>
548
  <tr>
549
- <td align="right" style="font-weight:bold;">TOTAL SAVINGS</td>
550
  <td align="right" style="font-weight:bold;border-top:solid 1px #999;"><?php echo rvg_format_size(($start_size - $end_size),3); ?></td>
551
  </tr>
 
 
 
 
552
  </table>
 
 
 
553
  <br />
554
- <span style="font-weight:bold;color:#00F;padding-left:8px;">D O N E !</span>
 
 
 
 
 
 
 
 
 
 
555
  <?php
556
  } // rvg_optimize_db()
557
  ?>
558
  <?php
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
559
  /********************************************************************************************
560
 
561
  CALCULATE THE SIZE OF THE WORDPRESS DATABASE (IN BYTES)
@@ -586,9 +1109,9 @@ function rvg_get_db_size()
586
  function rvg_format_size($size, $precision=1)
587
  {
588
  if($size>1024*1024)
589
- $table_size = (round($size / (1024*1024),$precision)).' MB';
590
  else
591
- $table_size = (round($size / 1024,$precision)).' KB';
592
 
593
  return $table_size;
594
  } // rvg_format_size()
1
  <?php
2
+ $odb_version = '2.0';
3
+ $odb_release_date = '12/18/2012';
4
  /**
5
  * @package Optimize Database after Deleting Revisions
6
+ * @version 2.0
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.0
14
  Author URI: http://cagewebdev.com
15
  */
16
  ?>
41
  add_action( 'admin_menu', 'rvg_odb_admin_menu' );
42
 
43
 
44
+ /********************************************************************************************
45
+
46
+ ACTIONS FOR THE SCHEDULER
47
+
48
+ *********************************************************************************************/
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'
60
+ )
61
+ );
62
+ }
63
+ add_action( 'rvg_optimize_database', 'rvg_optimize_db_cron' );
64
+
65
+ // REMOVE SCHEDULED TASK WHEN DEACTIVATED
66
+ register_deactivation_hook( __FILE__, 'rvg_deactivate_plugin' );
67
+ function rvg_deactivate_plugin()
68
+ { // CLEAR CURRENT SCHEDULE (IF ANY)
69
+ wp_clear_scheduled_hook('rvg_optimize_database');
70
+ }
71
+
72
+ // RE-SCHEDULE TASK WHEN RE-ACTIVATED (OR AFTER UPDATE)
73
+ register_activation_hook( __FILE__, 'rvg_activate_plugin' );
74
+ function rvg_activate_plugin()
75
+ { $rvg_odb_schedule = get_option('rvg_odb_schedule');
76
+ if($rvg_odb_schedule)
77
+ { // PLUGIN RE-ACTIVATED: START SCHEDULER
78
+ if( !wp_next_scheduled( 'rvg_optimize_database' ))
79
+ wp_schedule_event( time(), $rvg_odb_schedule, 'rvg_optimize_database' );
80
+ }
81
+ }
82
+
83
+
84
  /********************************************************************************************
85
 
86
  CREATE THE OPTIONS PAGE
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();
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);
117
+
118
+ $rvg_odb_schedule = '';
119
+ if($_POST['rvg_odb_schedule']) $rvg_odb_schedule = $_POST['rvg_odb_schedule'];
120
+ update_option('rvg_odb_schedule', $rvg_odb_schedule);
121
+
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>";
134
  }
135
+
136
  $rvg_odb_number = get_option('rvg_odb_number');
137
  if(!$rvg_odb_number) $rvg_odb_number = '0';
138
 
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
+
151
+ $rvg_odb_schedule = get_option('rvg_odb_schedule');
152
+ if(!$rvg_odb_schedule) $rvg_odb_schedule = '';
153
  ?>
154
 
155
+ <form name="options" method="post" action="">
156
  <div class="wrap">
157
  <h2>Using Optimize Database after Deleting Revisions</h2>
158
  <blockquote>
159
+ <p><strong>'<em>Optimize Database after Deleting Revisions</em>' is an one-click plugin to clean and optimize your WordPress database.</strong></p>
 
 
 
 
160
  <p>To start the optimization:<br />
161
+ <strong>WP Admin Panel</strong> &raquo; <strong>Tools</strong> &raquo; <strong>Optimize Database</strong>. Then click the '<strong>Start Optimization</strong>'-button. Et voila!<br />
162
+ Note: if you use the Scheduler the Optimization will run automatically!
163
  <p>Plugin version:<br />
164
  <strong>v<?php echo $odb_version ?> (<?php echo $odb_release_date?>)</strong> </p>
165
+ <p><strong>Author:</strong><br />
166
+ <strong><a href="http://cagewebdev.com/" target="_blank">CAGE Web Design</a> | <a href="http://cage.nl/" target="_blank">Rolf van Gelder</a></strong>, Eindhoven, The Netherlands<br />
167
+ <strong>Plugin URL:</strong><br />
168
+ <a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank"><strong>http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/</strong></a><br />
169
+ <strong>Download URL:</strong><br />
 
 
170
  <strong><a href="http://wordpress.org/extend/plugins/rvg-optimize-database/" target="_blank">http://wordpress.org/extend/plugins/rvg-optimize-database/</a></strong></p>
171
  <p>&nbsp;</p>
172
  </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>
241
  {
242
  global $wpdb, $odb_version, $table_prefix;
243
 
244
+ if($_REQUEST['action'] == "delete_log")
245
+ @unlink(dirname(__FILE__).'/rvg-optimize-db-log.html');
246
+
247
  /****************************************************************************************
248
 
249
  DELETE REVISIONS
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)
283
+ { $rvg_odb_logging_on = 'N';
284
+ update_option('rvg_odb_logging_on', $rvg_odb_logging_on);
285
+ }
286
+ $rvg_odb_logging_on_yn = ($rvg_odb_logging_on == 'N') ? 'NO' : 'YES';
287
+
288
+ $rvg_odb_schedule = get_option('rvg_odb_schedule');
289
+ if(!$rvg_odb_schedule)
290
+ { $rvg_odb_schedule = '';
291
+ update_option('rvg_odb_schedule', $rvg_odb_schedule);
292
+ }
293
+
294
+ if($rvg_odb_schedule == 'hourly')
295
+ $rvg_odb_schedule_txt = 'ONCE HOURLY';
296
+ else if($rvg_odb_schedule == 'twicedaily')
297
+ $rvg_odb_schedule_txt = 'TWICE DAILY';
298
+ else if($rvg_odb_schedule == 'daily')
299
+ $rvg_odb_schedule_txt = 'ONCE DAILY';
300
+ else if($rvg_odb_schedule == 'weekly')
301
+ $rvg_odb_schedule_txt = 'ONCE WEEKLY';
302
+ else if($rvg_odb_schedule == 'once_per_5_minutes')
303
+ $rvg_odb_schedule_txt = 'EVERY FIVE MINUTES';
304
+
305
+ $nextrun = '';
306
+ if(!$rvg_odb_schedule_txt)
307
+ { $rvg_odb_schedule_txt = 'NOT SCHEDULED';
308
+ }
309
+ else
310
+ { $timestamp = wp_next_scheduled('rvg_optimize_database');
311
+ $nextrun = date_i18n('M j, Y @ G:i', $timestamp);
312
+ }
313
+
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>
320
+ <?php
321
+ if($_REQUEST['action'] == "delete_log")
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)
335
+ {
336
+ ?>
337
+ <br />
338
+ <strong>Next scheduled run:</strong> <span style="font-weight:bold;color:#00F;"><?php echo $nextrun?></span>
339
+ <?php
340
+ }
341
+ if($total_savings)
342
+ {
343
  ?>
344
+ <br />
345
+ <strong>Total savings so far:</strong> <span style="font-weight:bold;color:#00F;"><?php echo rvg_format_size($total_savings); ?></span>
346
+ <?php
347
+ }
348
+ ?>
349
  <p class="submit">
350
  <input class="button" type="button" name="change_options" value="Change Options" onclick="self.location='options-general.php?page=rvg_odb_admin'" style="font-weight:normal;" />
351
+ <?php
352
+ if(file_exists(dirname(__FILE__).'/rvg-optimize-db-log.html'))
353
+ {
354
+ ?>
355
+ &nbsp;
356
+ <input class="button" type="button" name="view_log" value="View Log File" onclick="window.open('<?php echo $log_url?>','log','width=800,height=800,scrollbars=1')" style="font-weight:normal;" />
357
+ &nbsp;
358
+ <input class="button" type="button" name="delete_log" value="Delete Log File" onclick="self.location='tools.php?page=rvg-optimize-db.php&action=delete_log'" style="font-weight:normal;" />
359
+ <?php
360
+ }
361
+ ?>
362
+ <?php
363
+ if($_REQUEST['action'] != 'run')
364
+ {
365
+ ?>
366
  &nbsp;
367
  <input class="button-primary button-large" type="button" name="start_optimization" value="Start Optimization" onclick="self.location='tools.php?page=rvg-optimize-db.php&action=run'" style="font-weight:bold;" />
368
+ <?php
369
+ }
 
370
  ?>
371
+ </p>
372
  </div>
373
  <?php
374
+ if($_REQUEST['action'] != 'run')
375
+ { return;
376
+ }
377
  ?>
 
378
  <h2 style="padding-left:8px;">Starting Optimization...</h2>
379
  <?php
380
  // GET THE SIZE OF THE DATABASE BEFORE OPTIMIZATION
381
  $start_size = rvg_get_db_size();
382
 
383
+ // TIMESTAMP FOR LOG FILE
384
+ $log_arr = array("time" => date("m/d/Y").'<br />'.date("H:i:s"));
385
+
386
+ // FIND REVISIONS
387
+ $results = rvg_get_revisions($max_revisions);
388
+
389
+ $total_deleted = 0;
 
 
 
390
  if(count($results)>0)
391
  { // WE HAVE REVISIONS TO DELETE!
392
  ?>
400
  <th align="left" style="border-bottom:solid 1px #999;">revision date</th>
401
  <th align="right" style="border-bottom:solid 1px #999;">revisions deleted</th>
402
  </tr>
403
+ <?php
404
+ // LOOP THROUGH THE REVISIONS AND DELETE THEM
405
+ $total_deleted = rvg_delete_revisions($results, true);
406
+ ?>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
407
  <tr>
408
  <td colspan="3" align="right" style="border-top:solid 1px #999;font-weight:bold;">total number of revisions deleted</td>
409
  <td align="right" style="border-top:solid 1px #999;font-weight:bold;"><?php echo $total_deleted?></td>
421
  </table>
422
  <?php
423
  } // if(count($results)>0)
424
+
425
+ // NUMBER OF DELETED REVISIONS FOR LOG FILE
426
+ $log_arr["revisions"] = $total_deleted;
427
  ?>
428
  <?php
429
  /****************************************************************************************
436
  if($clear_trash == 'Y')
437
  {
438
  // GET TRASHED POSTS / PAGES AND COMMENTS
439
+ $results = rvg_get_trash();
440
+
441
+ $total_deleted = 0;
 
 
 
 
 
 
 
 
 
442
  if(count($results)>0)
443
  { // WE HAVE TRASH TO DELETE!
444
  ?>
453
  <th align="left" style="border-bottom:solid 1px #999;">IP address / title</th>
454
  <th align="left" nowrap="nowrap" style="border-bottom:solid 1px #999;">date</th>
455
  </tr>
456
+ <?php
457
+ // LOOP THROUGH THE TRASHED ITEMS AND DELETE THEM
458
+ $total_deleted = rvg_delete_trash($results, true);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
459
  ?>
460
  </table>
461
  <?php
472
  <?php
473
  } // if(count($results)>0)
474
 
475
+ // NUMBER OF DELETED TRASH FOR LOG FILE
476
+ $log_arr["trash"] = $total_deleted;
477
+
478
  } // if($clear_trash == 'Y')
479
  ?>
480
  <?php
487
  <?php
488
  if($clear_spam == 'Y')
489
  {
490
+ // GET SPAMMED COMMENTS
491
+ $results = rvg_get_spam();
492
+
493
+ $total_deleted = 0;
 
 
 
 
494
  if(count($results)>0)
495
  { // WE HAVE SPAM TO DELETE!
496
  ?>
505
  <th align="left" style="border-bottom:solid 1px #999;">comment author email</th>
506
  <th align="left" nowrap="nowrap" style="border-bottom:solid 1px #999;">comment date</th>
507
  </tr>
 
 
 
 
 
 
 
 
 
 
 
 
508
  <?php
509
+ // LOOP THROUGH SPAMMED ITEMS AND DELETE THEM
510
+ $total_deleted = rvg_delete_spam($results, true);
 
 
 
 
 
 
 
 
 
511
  ?>
512
  </table>
513
  <?php
525
  } // if(count($results)>0)
526
 
527
  } // if($clear_spam == 'Y')
528
+
529
+ // NUMBER OF SPAM DELETED FOR LOG FILE
530
+ $log_arr["spam"] = $total_deleted;
531
  ?>
532
  <?php
533
  /****************************************************************************************
550
  <th style="border-bottom:solid 1px #999;" align="right">table size</th>
551
  </tr>
552
  <?php
553
+ # OPTIMIZE THE DATABASE TABLES
554
+ $cnt = rvg_optimize_tables(true);
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
555
  ?>
556
  </table>
557
  <?php
558
+ // NUMBER OF TABLES
559
+ $log_arr["tables"] = $cnt;
560
+ // DATABASE SIZE BEFORE OPTIMIZATION
561
+ $log_arr["before"] = rvg_format_size($start_size,3);
562
+ // DATABASE SIZE AFTER OPTIMIZATION
563
+ $end_size = rvg_get_db_size();
564
+ $log_arr["after"] = rvg_format_size($end_size,3);
565
+ // TOTAL SAVING
566
+ $log_arr["savings"] = rvg_format_size(($start_size - $end_size),3);
567
+ // WRITE RESULTS TO LOG FILE
568
+ rvg_write_log($log_arr);
569
+
570
+ $total_savings = get_option('rvg_odb_total_savings');
571
+ $total_savings += ($start_size - $end_size);
572
+ update_option('rvg_odb_total_savings',$total_savings);
573
  ?>
574
  <span style="font-weight:bold;color:#000;padding-left:8px;">~~~~~</span>
575
  <table border="0" cellspacing="8" cellpadding="2">
589
  <td align="right" style="font-weight:bold;"><?php echo rvg_format_size($end_size,3); ?></td>
590
  </tr>
591
  <tr>
592
+ <td align="right" style="font-weight:bold;">SAVINGS THIS TIME</td>
593
  <td align="right" style="font-weight:bold;border-top:solid 1px #999;"><?php echo rvg_format_size(($start_size - $end_size),3); ?></td>
594
  </tr>
595
+ <tr>
596
+ <td align="right" style="font-weight:bold;">TOTAL SAVINGS SO FAR</td>
597
+ <td align="right" style="font-weight:bold;border-top:solid 1px #999;"><?php echo rvg_format_size($total_savings,3); ?></td>
598
+ </tr>
599
  </table>
600
+ <span style="font-weight:bold;color:#000;padding-left:8px;">~~~~~</span><br />
601
+ <br />
602
+ <span style="font-weight:bold;color:#00F;padding-left:8px;">DONE!</span><br />
603
  <br />
604
+ <?php
605
+ if(file_exists(dirname(__FILE__).'/rvg-optimize-db-log.html'))
606
+ {
607
+ ?>
608
+ &nbsp;
609
+ <input class="button" type="button" name="view_log" value="View Log File" onclick="window.open('<?php echo $log_url?>','log','width=800,height=800,scrollbars=1')" style="font-weight:normal;" />
610
+ &nbsp;
611
+ <input class="button" type="button" name="delete_log" value="Delete Log File" onclick="self.location='tools.php?page=rvg-optimize-db.php&action=delete_log'" style="font-weight:normal;" />
612
+ <?php
613
+ }
614
+ ?>
615
  <?php
616
  } // rvg_optimize_db()
617
  ?>
618
  <?php
619
+ /********************************************************************************************
620
+
621
+ EXECUTE OPTIMIZATION VIA CRON JOB
622
+
623
+ *********************************************************************************************/
624
+ function rvg_optimize_db_cron()
625
+ {
626
+ global $wpdb, $odb_version, $table_prefix;
627
+
628
+ // GET OPTIONS AND SET DEFAULT VALUES
629
+ $max_revisions = get_option('rvg_odb_number');
630
+ if(!$max_revisions)
631
+ { $max_revisions = 0;
632
+ update_option('rvg_odb_number', $max_revisions);
633
+ }
634
+
635
+ $clear_trash = get_option('rvg_clear_trash');
636
+ if(!$clear_trash)
637
+ { $clear_trash = 'N';
638
+ update_option('rvg_clear_trash', $clear_trash);
639
+ }
640
+
641
+ $clear_spam = get_option('rvg_clear_spam');
642
+ if(!$clear_spam)
643
+ { $clear_spam = 'N';
644
+ update_option('rvg_clear_spam', $clear_spam);
645
+ }
646
+
647
+ // GET THE SIZE OF THE DATABASE BEFORE OPTIMIZATION
648
+ $start_size = rvg_get_db_size();
649
+
650
+ // TIMESTAMP FOR LOG FILE
651
+ $log_arr = array("time" => date("m/d/Y").'<br />'.date("H:i:s"));
652
+
653
+ // FIND THE REVISIONS
654
+ $results = rvg_get_revisions($max_revisions);
655
+
656
+ $total_deleted = 0;
657
+ if(count($results)>0)
658
+ // WE HAVE REVISIONS TO DELETE!
659
+ $total_deleted = rvg_delete_revisions($results, false);
660
+
661
+ // NUMBER OF DELETED REVISIONS FOR LOG FILE
662
+ $log_arr["revisions"] = $total_deleted;
663
+
664
+ $total_deleted = 0;
665
+ if($clear_trash == 'Y')
666
+ {
667
+ // GET TRASHED POSTS / PAGES AND COMMENTS
668
+ $results = rvg_get_trash();
669
+
670
+ if(count($results)>0)
671
+ // WE HAVE TRASH TO DELETE!
672
+ $total_deleted = rvg_delete_trash($results, false);
673
+
674
+ } // if($clear_trash == 'Y')
675
+
676
+ // NUMBER OF DELETED TRASH FOR LOG FILE
677
+ $log_arr["trash"] = $total_deleted;
678
+
679
+ $total_deleted = 0;
680
+ if($clear_spam == 'Y')
681
+ {
682
+ // GET SPAMMED COMMENTS
683
+ $results = rvg_get_spam();
684
+
685
+ if(count($results)>0)
686
+ // WE HAVE SPAM TO DELETE!
687
+ $total_deleted = rvg_delete_spam($results, false);
688
+
689
+ } // if($clear_spam == 'Y')
690
+
691
+ // NUMBER OF SPAM DELETED FOR LOG FILE
692
+ $log_arr["spam"] = $total_deleted;
693
+
694
+ // OPTIMIZE DATABASE TABLES
695
+ $cnt = rvg_optimize_tables(false);
696
+
697
+ // NUMBER OF TABLES
698
+ $log_arr["tables"] = $cnt;
699
+ // DATABASE SIZE BEFORE OPTIMIZATION
700
+ $log_arr["before"] = rvg_format_size($start_size,3);
701
+ // DATABASE SIZE AFTER OPTIMIZATION
702
+ $end_size = rvg_get_db_size();
703
+ $log_arr["after"] = rvg_format_size($end_size,3);
704
+ // TOTAL SAVING
705
+ $log_arr["savings"] = rvg_format_size(($start_size - $end_size),3);
706
+ // WRITE RESULTS TO LOG FILE
707
+ rvg_write_log($log_arr);
708
+
709
+ $total_savings = get_option('rvg_odb_total_savings');
710
+ $total_savings += ($start_size - $end_size);
711
+ update_option('rvg_odb_total_savings',$total_savings);
712
+
713
+ } // rvg_optimize_db_cron()
714
+ ?>
715
+ <?php
716
+ /********************************************************************************************
717
+
718
+ DELETE THE REVISIONS
719
+
720
+ *********************************************************************************************/
721
+ function rvg_delete_revisions($results, $display)
722
+ {
723
+ global $wpdb;
724
+
725
+ $nr = 1;
726
+ $total_deleted = 0;
727
+ for($i=0; $i<count($results); $i++)
728
+ { $nr_to_delete = $results[$i]->cnt - $max_revisions;
729
+ $total_deleted += $nr_to_delete;
730
+ if($display)
731
+ {
732
+ ?>
733
+ <tr>
734
+ <td align="right" valign="top"><?php echo $nr?>.</td>
735
+ <td valign="top" style="font-weight:bold;"><?php echo $results[$i]->post_title?></td>
736
+ <td valign="top"><?php
737
+ } // if($display)
738
+
739
+ $sql_get_posts = "
740
+ SELECT `ID`, `post_modified`
741
+ FROM $wpdb->posts
742
+ WHERE `post_parent`=".$results[$i]->post_parent."
743
+ AND `post_type`='revision'
744
+ ORDER BY `post_modified` ASC
745
+ ";
746
+ $results_get_posts = $wpdb -> get_results($sql_get_posts);
747
+
748
+ for($j=0; $j<$nr_to_delete; $j++)
749
+ {
750
+ if($display) echo $results_get_posts[$j]->post_modified.'<br />';
751
+
752
+ $sql_delete = "
753
+ DELETE FROM $wpdb->posts
754
+ WHERE `ID` = ".$results_get_posts[$j]->ID."
755
+ ";
756
+ $wpdb -> get_results($sql_delete);
757
+
758
+ } // for($j=0; $j<$nr_to_delete; $j++)
759
+
760
+ $nr++;
761
+ if($display)
762
+ {
763
+ ?></td>
764
+ <td align="right" valign="top" style="font-weight:bold;"><?php echo $nr_to_delete?></td>
765
+ </tr>
766
+ <?php
767
+ } // if($display)
768
+ } // for($i=0; $i<count($results); $i++)
769
+ return $total_deleted;
770
+ } // rvg_delete_revisions()
771
+ ?>
772
+ <?php
773
+ /********************************************************************************************
774
+
775
+ DELETE TRASHED POSTS AND PAGES
776
+
777
+ *********************************************************************************************/
778
+ function rvg_delete_trash($results, $display)
779
+ {
780
+ global $wpdb;
781
+
782
+ $nr = 1;
783
+ $total_deleted = count($results);
784
+ for($i=0; $i<count($results); $i++)
785
+ { if($display)
786
+ {
787
+ ?>
788
+ <tr>
789
+ <td align="right" valign="top"><?php echo $nr; ?></td>
790
+ <td valign="top"><?php echo $results[$i]->post_type; ?></td>
791
+ <td valign="top"><?php echo $results[$i]->title; ?></td>
792
+ <td valign="top" nowrap="nowrap"><?php echo $results[$i]->modified; ?></td>
793
+ </tr>
794
+ <?php
795
+ }
796
+ if($results[$i]->post_type == 'comment')
797
+ { // DELETE META DATA (IF ANY...)
798
+ $sql_delete = "
799
+ DELETE FROM $wpdb->commentmeta WHERE `comment_id` = ".$results[$i]->id."
800
+ ";
801
+ $wpdb -> get_results($sql_delete);
802
+ }
803
+
804
+ $nr++;
805
+ } // for($i=0; $i<count($results); $i++)
806
+
807
+ // DELETE TRASHED POSTS / PAGES
808
+ $sql_delete = "
809
+ DELETE FROM $wpdb->posts WHERE `post_status` = 'trash'
810
+ ";
811
+ $wpdb -> get_results($sql_delete);
812
+
813
+ // DELETE TRASHED COMMENTS
814
+ $sql_delete = "
815
+ DELETE FROM $wpdb->comments WHERE `comment_approved` = 'trash'
816
+ ";
817
+ $wpdb -> get_results($sql_delete);
818
+
819
+ return $total_deleted;
820
+
821
+ } // rvg_delete_trash()
822
+ ?>
823
+ <?php
824
+ /********************************************************************************************
825
+
826
+ DELETE SPAMMED ITEMS
827
+
828
+ *********************************************************************************************/
829
+ function rvg_delete_spam($results, $display)
830
+ {
831
+ global $wpdb;
832
+
833
+ $nr = 1;
834
+ $total_deleted = count($results);
835
+ for($i=0; $i<count($results); $i++)
836
+ { if($display)
837
+ {
838
+ ?>
839
+ <tr>
840
+ <td align="right" valign="top"><?php echo $nr; ?></td>
841
+ <td valign="top"><?php echo $results[$i]->comment_author; ?></td>
842
+ <td valign="top"><?php echo $results[$i]->comment_author_email; ?></td>
843
+ <td valign="top" nowrap="nowrap"><?php echo $results[$i]->comment_date; ?></td>
844
+ </tr>
845
+ <?php
846
+ } // if($display)
847
+ $sql_delete = "
848
+ DELETE FROM $wpdb->commentmeta WHERE `comment_id` = ".$results[$i]->comment_ID."
849
+ ";
850
+ $wpdb -> get_results($sql_delete);
851
+ $nr++;
852
+ } // for($i=0; $i<count($results); $i++)
853
+
854
+ $sql_delete = "
855
+ DELETE FROM $wpdb->comments WHERE `comment_approved` = 'spam'
856
+ ";
857
+ $wpdb -> get_results($sql_delete);
858
+
859
+ return $total_deleted;
860
+
861
+ } // rvg_delete_spam()
862
+ ?>
863
+ <?php
864
+ /********************************************************************************************
865
+
866
+ OPTIMIZE DATABASE TABLES
867
+
868
+ *********************************************************************************************/
869
+ function rvg_optimize_tables($display)
870
+ {
871
+ global $wpdb, $table_prefix;
872
+
873
+ $wp_only = get_option('rvg_wp_only');
874
+ if(!$wp_only)
875
+ { $wp_only = 'N';
876
+ update_option('rvg_wp_only', $wp_only);
877
+ }
878
+
879
+ $names = mysql_list_tables(DB_NAME);
880
+ $cnt = 0;
881
+ while($row = mysql_fetch_row($names))
882
+ {
883
+ if($wp_only == 'N' || ($wp_only == 'Y' && substr($row[0],0,strlen($table_prefix)) == $table_prefix))
884
+ { # ALL TABLES OR THIS IS A WORDPRESS TABLE
885
+ $cnt++;
886
+ $query = "OPTIMIZE TABLE ".$row[0];
887
+ $result = $wpdb -> get_results($query);
888
+
889
+ $sql = "
890
+ SELECT engine, SUM(data_length + index_length) AS size, table_rows
891
+ FROM information_schema.TABLES
892
+ WHERE table_schema = '".strtolower(DB_NAME)."'
893
+ AND table_name = '".$row[0]."'
894
+ ";
895
+
896
+ $table_info = $wpdb -> get_results($sql);
897
+
898
+ if($display)
899
+ {
900
+ ?>
901
+ <tr>
902
+ <td align="right" valign="top"><?php echo $cnt?>.</td>
903
+ <td valign="top" style="font-weight:bold;"><?php echo $row[0] ?></td>
904
+ <td valign="top"><?php echo $result[0]->Msg_text ?></td>
905
+ <td valign="top"><?php echo $table_info[0]->engine ?></td>
906
+ <td align="right" valign="top"><?php echo $table_info[0]->table_rows ?></td>
907
+ <td align="right" valign="top"><?php echo rvg_format_size($table_info[0]->size) ?></td>
908
+ </tr>
909
+ <?php
910
+ } // if($display)
911
+ } // if($wp_only == 'N' || ($wp_only == 'Y' && substr($row[0],0,strlen($table_prefix)) == $table_prefix))
912
+ } // while($row = mysql_fetch_row($names))
913
+
914
+ return $cnt;
915
+
916
+ } // rvg_optimize_tables()
917
+ ?>
918
+ <?php
919
+ /********************************************************************************************
920
+
921
+ WRITE LINE TO LOG FILE
922
+
923
+ *********************************************************************************************/
924
+ function rvg_write_log($log_arr)
925
+ {
926
+ global $odb_version;
927
+
928
+ $rvg_odb_logging_on = get_option('rvg_odb_logging_on');
929
+ if(!$rvg_odb_logging_on)
930
+ { $rvg_odb_logging_on = 'N';
931
+ update_option('rvg_odb_logging_on', $rvg_odb_logging_on);
932
+ }
933
+
934
+ if($rvg_odb_logging_on == "Y")
935
+ { $file = dirname(__FILE__).'/rvg-optimize-db-log.html';
936
+ if(!file_exists($file))
937
+ {
938
+ // NEW LOG FILE
939
+ $html = '
940
+ <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
941
+ <html xmlns="http://www.w3.org/1999/xhtml">
942
+ <head>
943
+ <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
944
+ <title>Untitled Document</title>
945
+ <style type="text/css">
946
+ body, td, th {
947
+ font-family: Arial, Helvetica, sans-serif;
948
+ font-size: 12px;
949
+ }
950
+ th {
951
+ border-top:solid 1px #000;
952
+ border-bottom:solid 1px #000;
953
+ }
954
+ td {
955
+ padding-bottom:4px;
956
+ border-bottom:dotted 1px #CCC;
957
+ }
958
+ #header {
959
+ margin-left:6px;
960
+ margin-bottom:8px;
961
+ }
962
+ #header a {
963
+ text-decoration:none;
964
+ font-weight:bold;
965
+ }
966
+ </style>
967
+ </head>
968
+ <body>
969
+ <div id="header">
970
+ <h2><a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank">Optimize Database after Deleting Revisions v2.0</a></h2>
971
+ A WordPress Plugin by <a href="http://cagewebdev.com" target="_blank"><strong>CAGE Web Design | Rolf van Gelder</strong></a>, Eindhoven, The Netherlands</strong>
972
+ </div>
973
+ <table width="100%" border="0" cellspacing="6" cellpadding="1">
974
+ <tr>
975
+ <th width="12%" align="left" valign="top">time</th>
976
+ <th width="12%" align="right" valign="top">deleted<br />
977
+ revisions</th>
978
+ <th width="12%" align="right" valign="top">deleted<br />
979
+ trash</th>
980
+ <th width="12%" align="right" valign="top">deleted<br />
981
+ spam</th>
982
+ <th width="12%" align="right" valign="top">nr of optimized tables</th>
983
+ <th width="12%" align="right" valign="top">database size BEFORE</th>
984
+ <th width="12%" align="right" valign="top">database size AFTER</th>
985
+ <th width="12%" align="right" valign="top">SAVINGS</th>
986
+ </tr>
987
+ </table>
988
+ ';
989
+
990
+ // file_put_contents($file,'<strong><a href="http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/" target="_blank" style="font-weight:bold;text-decoration:none;">Optimize Database after Deleting Revisions v'.$odb_version.'</a><br />A WordPress Plugin by <a href="http://cagewebdev.com" target="_blank" style="text-decoration:none;">CAGE Web Design | Rolf van Gelder</a>, Eindhoven, The Netherlands</strong><hr noshade="noshade" size="1">');
991
+ file_put_contents($file,$html,FILE_APPEND);
992
+ }
993
+
994
+ $html = '
995
+ <table width="100%" border="0" cellspacing="6" cellpadding="0">
996
+ <tr>
997
+ <td width="12%" valign="top"><strong>'.$log_arr["time"].'</strong></td>
998
+ <td width="12%" align="right" valign="top">'.$log_arr["revisions"].'</td>
999
+ <td width="12%" align="right" valign="top">'.$log_arr["trash"].'</td>
1000
+ <td width="12%" align="right" valign="top">'.$log_arr["spam"].'</td>
1001
+ <td width="12%" align="right" valign="top">'.$log_arr["tables"].'</td>
1002
+ <td width="12%" align="right" valign="top">'.$log_arr["before"].'</td>
1003
+ <td width="12%" align="right" valign="top">'.$log_arr["after"].'</td>
1004
+ <td width="12%" align="right" valign="top">'.$log_arr["savings"].'</td>
1005
+ </tr>
1006
+ </table>
1007
+ ';
1008
+
1009
+ // print_r($log_arr);
1010
+ file_put_contents($file,$html,FILE_APPEND);
1011
+ }
1012
+
1013
+ } // rvg_write_log
1014
+ ?>
1015
+ <?php
1016
+ /********************************************************************************************
1017
+
1018
+ GET REVISIONS
1019
+
1020
+ *********************************************************************************************/
1021
+ function rvg_get_revisions($max_revisions)
1022
+ {
1023
+ global $wpdb;
1024
+
1025
+ $sql = "
1026
+ SELECT `post_parent`, `post_title`, COUNT(*) cnt
1027
+ FROM $wpdb->posts
1028
+ WHERE `post_type` = 'revision'
1029
+ GROUP BY `post_parent`
1030
+ HAVING COUNT(*) > ".$max_revisions."
1031
+ ORDER BY UCASE(`post_title`)
1032
+ ";
1033
+ return $wpdb -> get_results($sql);
1034
+
1035
+ } // rvg_get_revisions()
1036
+ ?>
1037
+ <?php
1038
+ /********************************************************************************************
1039
+
1040
+ GET TRASHED POSTS / PAGES AND COMMENTS
1041
+
1042
+ *********************************************************************************************/
1043
+ function rvg_get_trash()
1044
+ {
1045
+ global $wpdb;
1046
+
1047
+ $sql = "
1048
+ SELECT `ID` AS id, 'post' AS post_type, `post_title` AS title, `post_modified` AS modified
1049
+ FROM $wpdb->posts
1050
+ WHERE `post_status` = 'trash'
1051
+ UNION ALL
1052
+ SELECT `comment_ID` AS id, 'comment' AS post_type, `comment_author_IP` AS title, `comment_date` AS modified
1053
+ FROM $wpdb->comments
1054
+ WHERE `comment_approved` = 'trash'
1055
+ ORDER BY post_type, UCASE(title)
1056
+ ";
1057
+ return $wpdb -> get_results($sql);
1058
+
1059
+ } // rvg_get_trash()
1060
+ ?>
1061
+ <?php
1062
+ /********************************************************************************************
1063
+
1064
+ GET SPAMMED COMMENTS
1065
+
1066
+ *********************************************************************************************/
1067
+ function rvg_get_spam()
1068
+ {
1069
+ global $wpdb;
1070
+
1071
+ $sql = "
1072
+ SELECT `comment_ID`, `comment_author`, `comment_author_email`, `comment_date`
1073
+ FROM $wpdb->comments
1074
+ WHERE `comment_approved` = 'spam'
1075
+ ORDER BY UCASE(`comment_author`)
1076
+ ";
1077
+ return $wpdb -> get_results($sql);
1078
+
1079
+ } // rvg_get_trash()
1080
+ ?>
1081
+ <?php
1082
  /********************************************************************************************
1083
 
1084
  CALCULATE THE SIZE OF THE WORDPRESS DATABASE (IN BYTES)
1109
  function rvg_format_size($size, $precision=1)
1110
  {
1111
  if($size>1024*1024)
1112
+ $table_size = (round($size/(1024*1024),$precision)).' MB';
1113
  else
1114
+ $table_size = (round($size/1024,$precision)).' KB';
1115
 
1116
  return $table_size;
1117
  } // rvg_format_size()