Optimize Database after Deleting Revisions

Version Description

No Upgrade Notice available.

Release Info

Developer	cageehv
Plugin	Optimize Database after Deleting Revisions
Version	1.1.4
Comparing to
See all releases

Code changes from version 1.0.4 to 1.1.4

Files changed (2) hide show

readme.txt +19 -9
rvg-optimize-db.php +183 -45

readme.txt CHANGED Viewed

	@@ -6,23 +6,25 @@ 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
10	Requires at least: 2.0
11	- Tested up to: 3.3.2
12	- Stable tag: 1.0.4
13	- Version: 1.0.4
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,8 +42,16 @@ 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	-




44	== Frequently Asked Questions ==
45
46	- <p><b>Q:</b> How do I ~~run~~ this plugin?</p>
47	- <p><b>A:</b> In the WordPress ~~Admin~~ ~~panel~~ go to '<b>Settings~~</b>'.~~ ~~Click~~ ~~on '<b>~~Optimize ~~Database~~</b>'. Et ~~voila!</~~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, Eindhoven, The Netherlands
10	Requires at least: 2.0
11	+ Tested up to: 3.4.1
12	+ Stable tag: 1.1.4
13	+ Version: 1.1.4
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 ==

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	== 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>

rvg-optimize-db.php CHANGED Viewed

@@ -1,104 +1,242 @@
             <?php
-            -
            $version = '1.0.4';
             /**
              * @package Optimize Database after Deleting Revisions
-            -
             * @version 1.0.4
              */
             /*
             Plugin Name: Optimize Database after Deleting Revisions
             Plugin URI: http://cagewebdev.com/index.php/optimize-database-after-deleting-revisions-wordpress-plugin/
-            -
            Description: Optimizes the Wordpress Database after Deleting Revisions
-            -
            Author: Rolf van Gelder
-            -
            Version: 1.0.4
             Author URI: http://cagewebdev.com
             */
-            -
            ?><?php
             function optimize_db_main()
-            -
            {	if (function_exists('add_options_page')) {
-            -
            	add_options_page('Optimize Database', 'Optimize Database',8 ,'rvg-optimize-db.php', 'rvg_optimize_db');
+                }
+            }
             add_action('admin_menu', 'optimize_db_main');
             function rvg_optimize_db()
+            {
             	global $wpdb, $version;
-            -
-            -
            	echo '<h2 style="padding-left:5px;">Optimizing your WordPress database</h2>';
-            -
            	echo '<span style="padding-left:5px;font-style:italic;">rvg-optimize-db v'.$version.' - A WordPress Plugin by Rolf van Gelder</span><br /><br />';
-            -
            	/***********************************
             		DELETE REVISIONS
-            -
            	***********************************/
-            -
            	$sql = "SELECT `ID`,`post_date`,`post_title`,`post_modified`
-            -
            			FROM $wpdb->posts
-            -
            			WHERE `post_type` = 'revision'
-            -
            			ORDER BY `ID` DESC";
-            -
            	$results = $wpdb -> get_results($sql);
-            -
            	if($results)
-            -
            	{	$cnt = count($results);
-            -
            		# print_r($results);
             ?>
-            -
            <table border="0" cellspacing="0" cellpadding="4">
               <tr>
-            -
                <td colspan="3" style="font-weight:bold;color:#00F;">DELETING REVISIONS:</td>
               </tr>
-            -
              <?php
-            -
            		for($i=0;$i<$cnt;$i++)
-            -
            		{	# MULTI LINGUAL?
-            -
            			$post_title = str_replace('--><!--','--> | <!--',$results[$i]->post_title);
             ?>
-            -
              <tr valign="top">
-            -
                <td align="right" style="font-weight:bold;"><?php echo ($i+1) ?></td>
-            -
                <td><?php echo $results[$i]->post_modified ?></td>
-            -
                <td style="font-weight:bold;"><?php echo $post_title ?></td>
               </tr>
               <?php
+            		}
             ?>
             </table>
             <br />
-            -
            <?php
-            -
            		# DELETE THE REVISIONS
-            -
            		$sql = "DELETE FROM $wpdb->posts WHERE post_type = 'revision'";
-            -
            		$results = $wpdb -> get_results($sql);
+            	}
             	else
-            -
            	{	echo '<span style="font-weight:bold;color:#00F;padding-left:5px;">NO REVISIONS FOUND!</span><br /><br />';
-            -
            	} // if($results)
-            -
            	/***********************************
             		OPTIMIZE TABLES
-            -
            	***********************************/
             	# GET TABLE NAMES
             	$Tables = $wpdb -> get_results('SHOW TABLES IN '.DB_NAME);
             	$Tables_in_DB_NAME = 'Tables_in_'.DB_NAME;
-            -
            	# print_r($Tables);
             ?>
-            -
            <table border="0" cellspacing="0" cellpadding="4">
               <tr>
                 <td colspan="3" style="font-weight:bold;color:#00F;">OPTIMIZING DATABASE TABLES:</td>
               </tr>
               <?php
             	for ($i=0; $i<count($Tables); $i++)
+            	{
             		$query  = "OPTIMIZE TABLE ".$Tables[$i]->$Tables_in_DB_NAME;
             		$result = $wpdb -> get_results($query);
-            -
            		# print_r($result);
             ?>
               <tr>
                 <td style="font-weight:bold;"><?php echo $Tables[$i]->$Tables_in_DB_NAME ?></td>
-            -
                <td style="font-weight:bold;">=&gt;</td>
                 <td><?php echo $result[0]->Msg_text ?></td>
               </tr>
               <?php
+            	}
-            -
            	$query = "$operation TABLE ";
             ?>
             </table>
             <br />
             <span style="font-weight:bold;color:#00F;padding-left:5px;">DONE!</span>
             <?php
+            }
-            -
            ?>


1	<?php
2	+ $version = '1.1.4';
3	+ $release_date = '09/01/2012';
4	/**
5	* @package Optimize Database after Deleting Revisions
6	+ * @version 1.1.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="plugins.php?page=rvg_odb_admin"><strong>plug in options</strong></a>
12	+ Author: Rolf van Gelder, Eindhoven, The Netherlands
13	+ Version: 1.1.4
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 ‘<strong>Settings</strong>‘.<br />
78	+ Click on ‘<strong>Optimize Database</strong>‘. 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> </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	+ ?>

Optimize Database after Deleting Revisions - Version 1.1.4

Version Description

Release Info

Code changes from version 1.0.4 to 1.1.4