Optimize Database after Deleting Revisions - Version 1.1

Version Description

No Upgrade Notice available.

Download this release

Release Info

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

Code changes from version 1.0.5 to 1.1

Files changed (2) hide show
  1. readme.txt +10 -6
  2. rvg-optimize-db.php +183 -44
readme.txt CHANGED
@@ -9,20 +9,20 @@ Author URI: http://cagewebdev.com
9
  Author: Rolf van Gelder
10
  Requires at least: 2.0
11
  Tested up to: 3.4.1
12
- Stable tag: 1.0.5
13
- Version: 1.0.5
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 ==
@@ -40,9 +40,13 @@ No Screenshots available.
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
  <p><b>1.0.4</b> 06/06/2012 Now also works with non short_open_tag's</p>
43
- <p><b>1.0.5</b> 21/08/2012 Depreciated item ('has_cap') replaced, abandoned line of code removed</p>
 
44
 
45
  == Frequently Asked Questions ==
46
 
 
 
 
47
  <p><b>Q:</b> How do I run this plugin?</p>
48
- <p><b>A:</b> In the WordPress Admin panel go to '<b>Settings</b>'. Click on '<b>Optimize Database</b>'. Et voila!</p>
9
  Author: Rolf van Gelder
10
  Requires at least: 2.0
11
  Tested up to: 3.4.1
12
+ Stable tag: 1.1
13
+ Version: 1.1
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
 
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 the WordPress Dashboard</li>
26
  </ol>
27
 
28
  == Upgrade Notice ==
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
  <p><b>1.0.4</b> 06/06/2012 Now also works with non short_open_tag's</p>
43
+ <p><b>1.0.5</b> 08/21/2012 Depreciated item ('has_cap') replaced, abandoned line of code removed</p>
44
+ <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>
45
 
46
  == Frequently Asked Questions ==
47
 
48
+ <p><b>Q:</b> How can I change the settings of this plugin?</p>
49
+ <p><b>A:</b> In the WordPress Dashboard go to '<b>Plugins / Optimize DB Options</b>'. There you can define the maximum number of - most recent - revisions you want to keep per post or page.</p>
50
+ <br /><br />
51
  <p><b>Q:</b> How do I run this plugin?</p>
52
+ <p><b>A:</b> In the WordPress Dashboard go to '<b>Settings</b>'. Click on '<b>Optimize Database</b>'. Et voila!</p>
rvg-optimize-db.php CHANGED
@@ -1,95 +1,234 @@
1
  <?php
2
- $version = '1.0.5';
 
3
  /**
4
  * @package Optimize Database after Deleting Revisions
5
- * @version 1.0.5
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.5
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','administrator' ,'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;"><?php echo ($i+1) ?></td>
54
- <td><?php echo $results[$i]->post_modified ?></td>
55
- <td style="font-weight:bold;"><?php echo $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;"><?php echo $Tables[$i]->$Tables_in_DB_NAME ?></td>
92
- <td style="font-weight:bold;">=&gt;</td>
93
  <td><?php echo $result[0]->Msg_text ?></td>
94
  </tr>
95
  <?php
@@ -100,4 +239,4 @@ function rvg_optimize_db()
100
  <span style="font-weight:bold;color:#00F;padding-left:5px;">DONE!</span>
101
  <?php
102
  }
103
- ?>
1
  <?php
2
+ $version = '1.1';
3
+ $release_date = '08/30/2012';
4
  /**
5
  * @package Optimize Database after Deleting Revisions
6
+ * @version 1.1
7
  */
8
  /*
9
  Plugin Name: Optimize Database after Deleting Revisions
10
  Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/
11
+ Description: Optimizes the Wordpress Database after 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
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
+ 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 get_option('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
239
  <span style="font-weight:bold;color:#00F;padding-left:5px;">DONE!</span>
240
  <?php
241
  }
242
+ ?>