Optimize Database after Deleting Revisions - Version 1.0.3

Version Description

No Upgrade Notice available.

Download this release

Release Info

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

Code changes from version 1.1.3 to 1.0.3

Files changed (4) hide show
  1. readme.txt +8 -19
  2. rvg-optimize-db.php +47 -186
  3. trunk/readme.txt +57 -0
  4. trunk/rvg-optimize-db.php +242 -0
readme.txt CHANGED
@@ -6,25 +6,23 @@ Plugin Name: Optimize Database after Deleting Revisions
6
  Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin
7
  Tags: wp, database, delete, revisions, optimize, posts, pages
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.4.1
12
- Stable tag: 1.1.3
13
- Version: 1.1.3
14
 
15
  == Description ==
16
 
17
  <p>This plugin is a 'one click' database optimizer.</p>
18
- <p>It deletes the redundant revisions of posts and pages 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 ==
@@ -41,17 +39,8 @@ No Screenshots available.
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
 
50
  == Frequently Asked Questions ==
51
 
52
- <p><b>Q:</b> <em>How can I change the settings of this plugin?</em></p>
53
- <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.</p>
54
- <p><b>Q:</b> <em>How do I run this plugin?</em></p>
55
- <p><b>A:</b> In the WordPress Dashboard go to '<b>Settings</b>'. Click on '<b>Optimize Database</b>'. Et voila!</p>
56
- <p><b>Q:</b> <em>Why do I see 'Table does not support optimize, doing recreate + analyze instead' while optimizing my database?</em></p>
57
- <p><b>A:</b> That is because the table type of that table is not 'MyISAM'</p>
6
  Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin
7
  Tags: wp, database, delete, revisions, optimize, posts, pages
8
  Author URI: http://cagewebdev.com
9
+ Author: Rolf van Gelder
10
  Requires at least: 2.0
11
+ Tested up to: 3.3
12
+ Stable tag: 1.0.3
13
+ Version: 1.0.3
14
 
15
  == Description ==
16
 
17
  <p>This plugin is a 'one click' database optimizer.</p>
18
+ <p>It deletes the redundant revisions of posts and pages and then optimizes all Wordpress database tables.</p>
19
  <p>http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin</p>
 
20
 
21
  == Installation ==
22
 
23
  <ol>
24
  <li>Upload `rvg-optimize-db.php` to the `/wp-content/plugins/` directory</li>
25
+ <li>Activate the plugin through the 'Plugins' menu in WordPress</li>
 
26
  </ol>
27
 
28
  == Upgrade Notice ==
39
  <p><b>1.0.1</b> 11/24/2011 A few updates for the readme.txt file</p>
40
  <p><b>1.0.2</b> 12/02/2011 Some minor updates</p>
41
  <p><b>1.0.3</b> 12/15/2011 Some minor layout updates</p>
 
 
 
 
 
42
 
43
  == Frequently Asked Questions ==
44
 
45
+ <p><b>Q:</b> How do I run this plugin?</p>
46
+ <p><b>A:</b> In the WordPress Admin panel go to '<b>Settings</b>'. Click on '<b>Optimize Database</b>'. Et voila!</p>
 
 
 
 
rvg-optimize-db.php CHANGED
@@ -1,243 +1,104 @@
1
  <?php
2
- $version = '1.1.2';
3
- $release_date = '08/30/2012';
4
  /**
5
  * @package Optimize Database after Deleting Revisions
6
- * @version 1.1.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 Deleting Revisions - <a href="plugins.php?page=rvg_odb_admin"><strong>plugin options</strong></a>
12
- Author: Rolf van Gelder, Eindhoven, The Netherlands
13
- Version: 1.1.2
14
  Author URI: http://cagewebdev.com
15
  */
16
- ?>
17
- <?php
18
- /********************************************************************************************
19
-
20
- ADD THE 'OPTIMIZE DATABASE' ITEM TO THE SETTINGS MENU
21
-
22
- *********************************************************************************************/
23
  function optimize_db_main()
24
- { if (function_exists('add_options_page'))
25
- { add_options_page('Optimize Database', 'Optimize Database','administrator' ,'rvg-optimize-db.php', 'rvg_optimize_db');
26
  }
27
  }
28
  add_action('admin_menu', 'optimize_db_main');
29
 
30
-
31
- /********************************************************************************************
32
-
33
- ADD THE 'OPTIMIZE DATABASE OPTION' ITEM TO THE PLUGINS MENU
34
-
35
- *********************************************************************************************/
36
- function rvg_odb_admin_menu()
37
- { $hook = add_submenu_page('plugins.php', __('Optimize DB Options'), __('Optimize DB Options'), 'manage_options', 'rvg_odb_admin', 'rvg_odb_options_page');
38
- }
39
- add_action( 'admin_menu', 'rvg_odb_admin_menu' );
40
- function rvg_odb_option_page()
41
- { add_options_page('Optimize Database Options', 'rvg_odb', 'manage_options', basename(__FILE__), 'rvg_odb_options_page');
42
- }
43
-
44
-
45
- /********************************************************************************************
46
-
47
- CREATE THE OPTIONS PAGE
48
-
49
- *********************************************************************************************/
50
- function rvg_odb_options_page() {
51
- global $version, $release_date;
52
-
53
- // If we are a postback, store the options
54
- if ( isset( $_POST['info_update'] ) ) {
55
- check_admin_referer();
56
-
57
- // Update the Project ID
58
- $rvg_odb_number = trim($_POST['rvg_odb_number']);
59
- update_option('rvg_odb_number', $rvg_odb_number);
60
-
61
- // Give an updated message
62
- echo "<div class='updated'><p><strong>Optimize Database after Deleting Revisions options updated</strong> - Click <a href='options-general.php?page=rvg-optimize-db.php' style='font-weight:bold'>HERE</a> to run the optimization</p></div>";
63
- }
64
- $rvg_odb_number = get_option('rvg_odb_number');
65
- if(!$rvg_odb_number) $rvg_odb_number = '0';
66
-
67
- // Output the options page
68
- ?>
69
- <form method="post" action="">
70
- <div class="wrap">
71
- <h2>Using Optimize Database after Deleting Revisions</h2>
72
- <blockquote>
73
- <p><strong>'<em>Optimize Database after Deleting Revisions</em>' is an one-click plugin to optimize your WordPress database.<br />
74
- It deletes redundant revisions of posts and pages and, after that, optimizes all database tables.</strong></p>
75
- <p>Below you can define the <u>maximum number</u> of - most recent - revisions you want to <u>keep</u> per post or page.</p>
76
- <p>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>
77
- <p>To start the optimization:<br />
78
- In the WordPress Dashboard go to &lsquo;<strong>Settings</strong>&lsquo;.<br />
79
- Click on &lsquo;<strong>Optimize Database</strong>&lsquo;. Et voila! </p>
80
- <p>Plugin version:<br />
81
- <strong>v<?php echo $version ?> (<?php echo $release_date?>)</strong> </p>
82
- <p>Author:<br />
83
- <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 />
84
- <br />
85
- Plugin URL:<br />
86
- <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 />
87
- </strong><br />
88
- Download URL:<br />
89
- <strong><a href="http://wordpress.org/extend/plugins/rvg-optimize-database/" target="_blank">http://wordpress.org/extend/plugins/rvg-optimize-database/</a></strong></p>
90
- <p>&nbsp;</p>
91
- </blockquote>
92
- <h2>Optimize Database after Deleting Revisions - Options</h2>
93
- <blockquote>
94
- <fieldset class='options'>
95
- <table class="editform" cellspacing="2" cellpadding="5">
96
- <tr>
97
- <td><label for="<?php echo rvg_odb_number; ?>" style="font-weight:bold;">Maximum number of - most recent - revisions to keep per post / page<br />
98
- </label></td>
99
- <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>
100
- </tr>
101
- </table>
102
- </fieldset>
103
- </blockquote>
104
- <p class="submit">
105
- <input type='submit' name='info_update' value='Update Options' />
106
- </p>
107
- </div>
108
- </form>
109
- <?php
110
- }
111
-
112
-
113
- /********************************************************************************************
114
-
115
- MAIN FUNCTION FOR DELETING REVISIONS AND OPTIMIZING DATABASE TABLES
116
-
117
- *********************************************************************************************/
118
  function rvg_optimize_db()
119
  {
120
  global $wpdb, $version;
 
 
 
121
 
122
- /****************************************************************************************
123
-
124
  DELETE REVISIONS
125
-
126
- ******************************************************************************************/
127
- $max_revisions = get_option('rvg_odb_number');
128
- ?>
129
-
130
- <h2 style="padding-left:8px;">Optimizing your WordPress Database</h2>
131
- <p><span style="padding-left:8px;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 $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>
132
- <p><span style="padding-left:8px;font-style:normal;">Maximum number of - most recent - revisions to keep per post / page: <span style="font-weight:bold;color:#00F;"><?php echo $max_revisions?></span> - click <a href="plugins.php?page=rvg_odb_admin" style="font-weight:bold;">HERE</a> to change this value.</span></p>
133
- <?php
134
- $sql = "
135
- SELECT `post_parent`, `post_title`, COUNT(*) cnt
136
- FROM $wpdb->posts
137
- WHERE `post_type` = 'revision'
138
- GROUP BY `post_parent`
139
- HAVING COUNT(*) > ".$max_revisions."
140
- ORDER BY UCASE(`post_title`)
141
- ";
142
  $results = $wpdb -> get_results($sql);
143
-
144
- if(count($results)>0)
145
- { // WE HAVE REVISIONS TO DELETE!
146
  ?>
147
- <table border="0" cellspacing="8" cellpadding="2">
148
- <tr>
149
- <td colspan="4" style="font-weight:bold;color:#00F;">DELETING REVISIONS:</td>
150
- </tr>
151
  <tr>
152
- <th align="right" style="border-bottom:solid 1px #999;">#</th>
153
- <th align="left" style="border-bottom:solid 1px #999;">post / page</th>
154
- <th align="left" style="border-bottom:solid 1px #999;">revision date</th>
155
- <th align="right" style="border-bottom:solid 1px #999;">revisions deleted</th>
156
  </tr>
157
- <?php
158
- $nr = 1;
159
- $total_deleted = 0;
160
- for($i=0; $i<count($results); $i++)
161
- { $nr_to_delete = $results[$i]->cnt - $max_revisions;
162
- $total_deleted += $nr_to_delete;
163
  ?>
164
- <tr>
165
- <td align="right" valign="top"><?php echo $nr?>.</td>
166
- <td valign="top" style="font-weight:bold;"><?php echo $results[$i]->post_title?></td>
167
- <td valign="top"><?php
168
- $sql_get_posts = "
169
- SELECT `ID`, `post_modified`
170
- FROM $wpdb->posts
171
- WHERE `post_parent`=".$results[$i]->post_parent."
172
- AND `post_type`='revision'
173
- ORDER BY `post_modified` ASC
174
- ";
175
- $results_get_posts = $wpdb -> get_results($sql_get_posts);
176
- for($j=0; $j<$nr_to_delete; $j++)
177
- {
178
- echo $results_get_posts[$j]->post_modified.'<br />';
179
- $sql_delete = "
180
- DELETE FROM $wpdb->posts
181
- WHERE `ID` = ".$results_get_posts[$j]->ID."
182
- ";
183
- $results_delete = $wpdb -> get_results($sql_delete);
184
- }
185
- $nr++;
186
- ?></td>
187
- <td align="right" valign="top" style="font-weight:bold;"><?php echo $nr_to_delete?></td>
188
  </tr>
189
  <?php
190
  }
191
  ?>
192
- <tr>
193
- <td colspan="3" align="right" style="border-top:solid 1px #999;font-weight:bold;">total number of revisions deleted</td>
194
- <td align="right" style="border-top:solid 1px #999;font-weight:bold;"><?php echo $total_deleted?></td>
195
- </tr>
196
  </table>
197
  <br />
198
- <?php
 
 
 
199
  }
200
  else
201
- {
202
- echo '<br /><span style="font-weight:bold;color:#00F;padding-left:8px;">NO REVISIONS FOUND TO DELETE...</span><br /><br />';
203
- }
204
- ?>
205
- <?php
206
- /****************************************************************************************
207
 
 
208
  OPTIMIZE TABLES
209
-
210
- ******************************************************************************************/
211
  # GET TABLE NAMES
212
  $Tables = $wpdb -> get_results('SHOW TABLES IN '.DB_NAME);
213
  $Tables_in_DB_NAME = 'Tables_in_'.DB_NAME;
 
214
  ?>
215
- <table border="0" cellspacing="8" cellpadding="2">
216
  <tr>
217
  <td colspan="3" style="font-weight:bold;color:#00F;">OPTIMIZING DATABASE TABLES:</td>
218
  </tr>
219
- <tr>
220
- <th style="border-bottom:solid 1px #999;" align="right">#</th>
221
- <th style="border-bottom:solid 1px #999;" align="left">table name</th>
222
- <th style="border-bottom:solid 1px #999;" align="left">optimization result</th>
223
- </tr>
224
  <?php
225
  for ($i=0; $i<count($Tables); $i++)
226
  {
227
  $query = "OPTIMIZE TABLE ".$Tables[$i]->$Tables_in_DB_NAME;
228
  $result = $wpdb -> get_results($query);
 
229
  ?>
230
  <tr>
231
- <td align="right"><?php echo ($i+1)?>.</td>
232
- <td style="font-weight:bold;"><?php echo $Tables[$i]->$Tables_in_DB_NAME ?></td>
233
- <td><?php echo $result[0]->Msg_text ?></td>
234
  </tr>
235
  <?php
236
  }
 
237
  ?>
238
  </table>
239
  <br />
240
  <span style="font-weight:bold;color:#00F;padding-left:5px;">DONE!</span>
241
  <?php
242
  }
243
- ?>
1
  <?php
2
+ $version = '1.0.3';
 
3
  /**
4
  * @package Optimize Database after Deleting Revisions
5
+ * @version 1.0.3
6
  */
7
  /*
8
  Plugin Name: Optimize Database after Deleting Revisions
9
  Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/
10
+ Description: Optimizes the Wordpress Database after Deleting Revisions
11
+ Author: Rolf van Gelder
12
+ Version: 1.0.3
13
  Author URI: http://cagewebdev.com
14
  */
15
+ ?><?php
 
 
 
 
 
 
16
  function optimize_db_main()
17
+ { if (function_exists('add_options_page')) {
18
+ add_options_page('Optimize Database', 'Optimize Database',8 ,'rvg-optimize-db.php', 'rvg_optimize_db');
19
  }
20
  }
21
  add_action('admin_menu', 'optimize_db_main');
22
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
23
  function rvg_optimize_db()
24
  {
25
  global $wpdb, $version;
26
+
27
+ echo '<h2 style="padding-left:5px;">Optimizing your WordPress database</h2>';
28
+ echo '<span style="padding-left:5px;font-style:italic;">rvg-optimize-db v'.$version.' - A WordPress Plugin by Rolf van Gelder</span><br /><br />';
29
 
30
+ /***********************************
 
31
  DELETE REVISIONS
32
+ ***********************************/
33
+ $sql = "SELECT `ID`,`post_date`,`post_title`,`post_modified`
34
+ FROM $wpdb->posts
35
+ WHERE `post_type` = 'revision'
36
+ ORDER BY `ID` DESC";
 
 
 
 
 
 
 
 
 
 
 
 
37
  $results = $wpdb -> get_results($sql);
38
+ if($results)
39
+ { $cnt = count($results);
40
+ # print_r($results);
41
  ?>
42
+
43
+ <table border="0" cellspacing="0" cellpadding="4">
 
 
44
  <tr>
45
+ <td colspan="3" style="font-weight:bold;color:#00F;">DELETING REVISIONS:</td>
 
 
 
46
  </tr>
47
+ <?php
48
+ for($i=0;$i<$cnt;$i++)
49
+ { # MULTI LINGUAL?
50
+ $post_title = str_replace('--><!--','--> | <!--',$results[$i]->post_title);
 
 
51
  ?>
52
+ <tr valign="top">
53
+ <td align="right" style="font-weight:bold;"><?=($i+1)?></td>
54
+ <td><?=$results[$i]->post_modified?></td>
55
+ <td style="font-weight:bold;"><?=$post_title?></td>
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
56
  </tr>
57
  <?php
58
  }
59
  ?>
 
 
 
 
60
  </table>
61
  <br />
62
+ <?php
63
+ # DELETE THE REVISIONS
64
+ $sql = "DELETE FROM $wpdb->posts WHERE post_type = 'revision'";
65
+ $results = $wpdb -> get_results($sql);
66
  }
67
  else
68
+ { echo '<span style="font-weight:bold;color:#00F;padding-left:5px;">NO REVISIONS FOUND!</span><br /><br />';
69
+ } // if($results)
 
 
 
 
70
 
71
+ /***********************************
72
  OPTIMIZE TABLES
73
+ ***********************************/
 
74
  # GET TABLE NAMES
75
  $Tables = $wpdb -> get_results('SHOW TABLES IN '.DB_NAME);
76
  $Tables_in_DB_NAME = 'Tables_in_'.DB_NAME;
77
+ # print_r($Tables);
78
  ?>
79
+ <table border="0" cellspacing="0" cellpadding="4">
80
  <tr>
81
  <td colspan="3" style="font-weight:bold;color:#00F;">OPTIMIZING DATABASE TABLES:</td>
82
  </tr>
 
 
 
 
 
83
  <?php
84
  for ($i=0; $i<count($Tables); $i++)
85
  {
86
  $query = "OPTIMIZE TABLE ".$Tables[$i]->$Tables_in_DB_NAME;
87
  $result = $wpdb -> get_results($query);
88
+ # print_r($result);
89
  ?>
90
  <tr>
91
+ <td style="font-weight:bold;"><?=$Tables[$i]->$Tables_in_DB_NAME?></td>
92
+ <td style="font-weight:bold;">=&gt;</td>
93
+ <td><?=$result[0]->Msg_text?></td>
94
  </tr>
95
  <?php
96
  }
97
+ $query = "$operation TABLE ";
98
  ?>
99
  </table>
100
  <br />
101
  <span style="font-weight:bold;color:#00F;padding-left:5px;">DONE!</span>
102
  <?php
103
  }
104
+ ?>
trunk/readme.txt ADDED
@@ -0,0 +1,57 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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: wp, database, delete, revisions, optimize, posts, pages
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.4.1
12
+ Stable tag: 1.1.3
13
+ Version: 1.1.3
14
+
15
+ == Description ==
16
+
17
+ <p>This plugin is a 'one click' database optimizer.</p>
18
+ <p>It deletes the redundant revisions of posts and pages 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
+
50
+ == Frequently Asked Questions ==
51
+
52
+ <p><b>Q:</b> <em>How can I change the settings of this plugin?</em></p>
53
+ <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.</p>
54
+ <p><b>Q:</b> <em>How do I run this plugin?</em></p>
55
+ <p><b>A:</b> In the WordPress Dashboard go to '<b>Settings</b>'. Click on '<b>Optimize Database</b>'. Et voila!</p>
56
+ <p><b>Q:</b> <em>Why do I see 'Table does not support optimize, doing recreate + analyze instead' while optimizing my database?</em></p>
57
+ <p><b>A:</b> That is because the table type of that table is not 'MyISAM'</p>
trunk/rvg-optimize-db.php ADDED
@@ -0,0 +1,242 @@
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
+ <?php
2
+ $version = '1.1.3';
3
+ $release_date = '09/01/2012';
4
+ /**
5
+ * @package Optimize Database after Deleting Revisions
6
+ * @version 1.1.3
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="plugins.php?page=rvg_odb_admin"><strong>plug in options</strong></a>
12
+ Author: Rolf van Gelder, Eindhoven, The Netherlands
13
+ Version: 1.1.3
14
+ Author URI: http://cagewebdev.com
15
+ */
16
+ ?>
17
+ <?php
18
+ /********************************************************************************************
19
+
20
+ ADD THE 'OPTIMIZE DATABASE' ITEM TO THE TOOLS MENU
21
+
22
+ *********************************************************************************************/
23
+ function optimize_db_main()
24
+ { if (function_exists('add_management_page'))
25
+ { add_management_page(__('Optimize Database'), __('Optimize Database'),'administrator' ,'rvg-optimize-db.php', 'rvg_optimize_db');
26
+ }
27
+ }
28
+ add_action('admin_menu', 'optimize_db_main');
29
+
30
+
31
+ /********************************************************************************************
32
+
33
+ ADD THE 'OPTIMIZE DB OPTIONS' ITEM TO THE SETTINGS MENU
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
+ }
41
+ add_action( 'admin_menu', 'rvg_odb_admin_menu' );
42
+
43
+
44
+ /********************************************************************************************
45
+
46
+ CREATE THE OPTIONS PAGE
47
+
48
+ *********************************************************************************************/
49
+ function rvg_odb_options_page() {
50
+ global $version, $release_date;
51
+
52
+ // If we are a postback, store the options
53
+ if ( isset( $_POST['info_update'] ) ) {
54
+ check_admin_referer();
55
+
56
+ // Update the Project ID
57
+ $rvg_odb_number = trim($_POST['rvg_odb_number']);
58
+ update_option('rvg_odb_number', $rvg_odb_number);
59
+
60
+ // Give an updated message
61
+ echo "<div class='updated'><p><strong>Optimize Database after Deleting Revisions options updated</strong> - Click <a href='options-general.php?page=rvg-optimize-db.php' style='font-weight:bold'>HERE</a> to run the optimization</p></div>";
62
+ }
63
+ $rvg_odb_number = get_option('rvg_odb_number');
64
+ if(!$rvg_odb_number) $rvg_odb_number = '0';
65
+
66
+ // Output the options page
67
+ ?>
68
+ <form method="post" action="">
69
+ <div class="wrap">
70
+ <h2>Using Optimize Database after Deleting Revisions</h2>
71
+ <blockquote>
72
+ <p><strong>'<em>Optimize Database after Deleting Revisions</em>' is an one-click plugin to optimize your WordPress database.<br />
73
+ It deletes redundant revisions of posts and pages and, after that, optimizes all database tables.</strong></p>
74
+ <p>Below you can define the <u>maximum number</u> of - most recent - revisions you want to <u>keep</u> per post or page.</p>
75
+ <p>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>
76
+ <p>To start the optimization:<br />
77
+ In the WordPress Dashboard go to &lsquo;<strong>Settings</strong>&lsquo;.<br />
78
+ Click on &lsquo;<strong>Optimize Database</strong>&lsquo;. Et voila! </p>
79
+ <p>Plugin version:<br />
80
+ <strong>v<?php echo $version ?> (<?php echo $release_date?>)</strong> </p>
81
+ <p>Author:<br />
82
+ <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 />
83
+ <br />
84
+ Plugin URL:<br />
85
+ <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 />
86
+ </strong><br />
87
+ Download URL:<br />
88
+ <strong><a href="http://wordpress.org/extend/plugins/rvg-optimize-database/" target="_blank">http://wordpress.org/extend/plugins/rvg-optimize-database/</a></strong></p>
89
+ <p>&nbsp;</p>
90
+ </blockquote>
91
+ <h2>Optimize Database after Deleting Revisions - Options</h2>
92
+ <blockquote>
93
+ <fieldset class='options'>
94
+ <table class="editform" cellspacing="2" cellpadding="5">
95
+ <tr>
96
+ <td><label for="<?php echo rvg_odb_number; ?>" style="font-weight:bold;">Maximum number of - most recent - revisions to keep per post / page<br />
97
+ </label></td>
98
+ <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>
99
+ </tr>
100
+ </table>
101
+ </fieldset>
102
+ </blockquote>
103
+ <p class="submit">
104
+ <input type='submit' name='info_update' value='Update Options' />
105
+ </p>
106
+ </div>
107
+ </form>
108
+ <?php
109
+ }
110
+
111
+
112
+ /********************************************************************************************
113
+
114
+ MAIN FUNCTION FOR DELETING REVISIONS AND OPTIMIZING DATABASE TABLES
115
+
116
+ *********************************************************************************************/
117
+ function rvg_optimize_db()
118
+ {
119
+ global $wpdb, $version;
120
+
121
+ /****************************************************************************************
122
+
123
+ DELETE REVISIONS
124
+
125
+ ******************************************************************************************/
126
+ $max_revisions = get_option('rvg_odb_number');
127
+ ?>
128
+
129
+ <h2 style="padding-left:8px;">Optimizing your WordPress Database</h2>
130
+ <p><span style="padding-left:8px;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 $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>
131
+ <p><span style="padding-left:8px;font-style:normal;">Maximum number of - most recent - revisions to keep per post / page: <span style="font-weight:bold;color:#00F;"><?php echo $max_revisions?></span> - click <a href="plugins.php?page=rvg_odb_admin" style="font-weight:bold;">HERE</a> to change this value.</span></p>
132
+ <?php
133
+ $sql = "
134
+ SELECT `post_parent`, `post_title`, COUNT(*) cnt
135
+ FROM $wpdb->posts
136
+ WHERE `post_type` = 'revision'
137
+ GROUP BY `post_parent`
138
+ HAVING COUNT(*) > ".$max_revisions."
139
+ ORDER BY UCASE(`post_title`)
140
+ ";
141
+ $results = $wpdb -> get_results($sql);
142
+
143
+ if(count($results)>0)
144
+ { // WE HAVE REVISIONS TO DELETE!
145
+ ?>
146
+ <table border="0" cellspacing="8" cellpadding="2">
147
+ <tr>
148
+ <td colspan="4" style="font-weight:bold;color:#00F;">DELETING REVISIONS:</td>
149
+ </tr>
150
+ <tr>
151
+ <th align="right" style="border-bottom:solid 1px #999;">#</th>
152
+ <th align="left" style="border-bottom:solid 1px #999;">post / page</th>
153
+ <th align="left" style="border-bottom:solid 1px #999;">revision date</th>
154
+ <th align="right" style="border-bottom:solid 1px #999;">revisions deleted</th>
155
+ </tr>
156
+ <?php
157
+ $nr = 1;
158
+ $total_deleted = 0;
159
+ for($i=0; $i<count($results); $i++)
160
+ { $nr_to_delete = $results[$i]->cnt - $max_revisions;
161
+ $total_deleted += $nr_to_delete;
162
+ ?>
163
+ <tr>
164
+ <td align="right" valign="top"><?php echo $nr?>.</td>
165
+ <td valign="top" style="font-weight:bold;"><?php echo $results[$i]->post_title?></td>
166
+ <td valign="top"><?php
167
+ $sql_get_posts = "
168
+ SELECT `ID`, `post_modified`
169
+ FROM $wpdb->posts
170
+ WHERE `post_parent`=".$results[$i]->post_parent."
171
+ AND `post_type`='revision'
172
+ ORDER BY `post_modified` ASC
173
+ ";
174
+ $results_get_posts = $wpdb -> get_results($sql_get_posts);
175
+ for($j=0; $j<$nr_to_delete; $j++)
176
+ {
177
+ echo $results_get_posts[$j]->post_modified.'<br />';
178
+ $sql_delete = "
179
+ DELETE FROM $wpdb->posts
180
+ WHERE `ID` = ".$results_get_posts[$j]->ID."
181
+ ";
182
+ $results_delete = $wpdb -> get_results($sql_delete);
183
+ }
184
+ $nr++;
185
+ ?></td>
186
+ <td align="right" valign="top" style="font-weight:bold;"><?php echo $nr_to_delete?></td>
187
+ </tr>
188
+ <?php
189
+ }
190
+ ?>
191
+ <tr>
192
+ <td colspan="3" align="right" style="border-top:solid 1px #999;font-weight:bold;">total number of revisions deleted</td>
193
+ <td align="right" style="border-top:solid 1px #999;font-weight:bold;"><?php echo $total_deleted?></td>
194
+ </tr>
195
+ </table>
196
+ <br />
197
+ <?php
198
+ }
199
+ else
200
+ {
201
+ echo '<br /><span style="font-weight:bold;color:#00F;padding-left:8px;">NO REVISIONS FOUND TO DELETE...</span><br /><br />';
202
+ }
203
+ ?>
204
+ <?php
205
+ /****************************************************************************************
206
+
207
+ OPTIMIZE TABLES
208
+
209
+ ******************************************************************************************/
210
+ # GET TABLE NAMES
211
+ $Tables = $wpdb -> get_results('SHOW TABLES IN '.DB_NAME);
212
+ $Tables_in_DB_NAME = 'Tables_in_'.DB_NAME;
213
+ ?>
214
+ <table border="0" cellspacing="8" cellpadding="2">
215
+ <tr>
216
+ <td colspan="3" style="font-weight:bold;color:#00F;">OPTIMIZING DATABASE TABLES:</td>
217
+ </tr>
218
+ <tr>
219
+ <th style="border-bottom:solid 1px #999;" align="right">#</th>
220
+ <th style="border-bottom:solid 1px #999;" align="left">table name</th>
221
+ <th style="border-bottom:solid 1px #999;" align="left">optimization result</th>
222
+ </tr>
223
+ <?php
224
+ for ($i=0; $i<count($Tables); $i++)
225
+ {
226
+ $query = "OPTIMIZE TABLE ".$Tables[$i]->$Tables_in_DB_NAME;
227
+ $result = $wpdb -> get_results($query);
228
+ ?>
229
+ <tr>
230
+ <td align="right"><?php echo ($i+1)?>.</td>
231
+ <td style="font-weight:bold;"><?php echo $Tables[$i]->$Tables_in_DB_NAME ?></td>
232
+ <td><?php echo $result[0]->Msg_text ?></td>
233
+ </tr>
234
+ <?php
235
+ }
236
+ ?>
237
+ </table>
238
+ <br />
239
+ <span style="font-weight:bold;color:#00F;padding-left:5px;">DONE!</span>
240
+ <?php
241
+ }
242
+ ?>