Optimize Database after Deleting Revisions - Version 1.3.1

Version Description

No Upgrade Notice available.

Download this release

Release Info

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

Code changes from version 1.3 to 1.3.1

Files changed (2) hide show
  1. readme.txt +4 -3
  2. rvg-optimize-db.php +57 -27
readme.txt CHANGED
@@ -9,8 +9,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.2
12
- Stable tag: 1.3
13
- Version: 1.3
14
 
15
  == Description ==
16
 
@@ -54,11 +54,12 @@ No Screenshots available.
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
 
58
  == Frequently Asked Questions ==
59
 
60
  <p><b>Q:</b> <em>How can I change the settings of this plugin?</em></p>
61
- <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>
62
  <p><b>Q:</b> <em>How do I run this plugin?</em></p>
63
  <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>
64
  <p><b>Q:</b> <em>Why do I see 'Table does not support optimize, doing recreate + analyze instead' while optimizing my database?</em></p>
9
  Author: Rolf van Gelder, Eindhoven, The Netherlands
10
  Requires at least: 2.0
11
  Tested up to: 3.4.2
12
+ Stable tag: 1.3.1
13
+ Version: 1.3.1
14
 
15
  == Description ==
16
 
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
 
59
  == Frequently Asked Questions ==
60
 
61
  <p><b>Q:</b> <em>How can I change the settings of this plugin?</em></p>
62
+ <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>
63
  <p><b>Q:</b> <em>How do I run this plugin?</em></p>
64
  <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>
65
  <p><b>Q:</b> <em>Why do I see 'Table does not support optimize, doing recreate + analyze instead' while optimizing my database?</em></p>
rvg-optimize-db.php CHANGED
@@ -1,16 +1,16 @@
1
  <?php
2
- $version = '1.3';
3
- $release_date = '10/06/2012';
4
  /**
5
  * @package Optimize Database after Deleting Revisions
6
- * @version 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="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
14
  Author URI: http://cagewebdev.com
15
  */
16
  ?>
@@ -166,6 +166,8 @@ function rvg_optimize_db()
166
  DELETE REVISIONS
167
 
168
  ******************************************************************************************/
 
 
169
  $max_revisions = get_option('rvg_odb_number');
170
  if(!$max_revisions)
171
  { $max_revisions = 0;
@@ -226,12 +228,12 @@ if($_REQUEST['action'] != 'run')
226
  $start_size = rvg_get_db_size();
227
 
228
  $sql = "
229
- SELECT `post_parent`, `post_title`, COUNT(*) cnt
230
- FROM $wpdb->posts
231
- WHERE `post_type` = 'revision'
232
- GROUP BY `post_parent`
233
- HAVING COUNT(*) > ".$max_revisions."
234
- ORDER BY UCASE(`post_title`)
235
  ";
236
  $results = $wpdb -> get_results($sql);
237
 
@@ -260,22 +262,26 @@ if($_REQUEST['action'] != 'run')
260
  <td valign="top" style="font-weight:bold;"><?php echo $results[$i]->post_title?></td>
261
  <td valign="top"><?php
262
  $sql_get_posts = "
263
- SELECT `ID`, `post_modified`
264
- FROM $wpdb->posts
265
- WHERE `post_parent`=".$results[$i]->post_parent."
266
- AND `post_type`='revision'
267
- ORDER BY `post_modified` ASC
268
  ";
269
  $results_get_posts = $wpdb -> get_results($sql_get_posts);
 
270
  for($j=0; $j<$nr_to_delete; $j++)
271
  {
272
  echo $results_get_posts[$j]->post_modified.'<br />';
 
273
  $sql_delete = "
274
  DELETE FROM $wpdb->posts
275
  WHERE `ID` = ".$results_get_posts[$j]->ID."
276
  ";
277
- $results_delete = $wpdb -> get_results($sql_delete);
278
- }
 
 
279
  $nr++;
280
  ?></td>
281
  <td align="right" valign="top" style="font-weight:bold;"><?php echo $nr_to_delete?></td>
@@ -311,11 +317,16 @@ if($_REQUEST['action'] != 'run')
311
  <?php
312
  if($clear_trash == 'Y')
313
  {
 
314
  $sql = "
315
- SELECT `post_title`, `post_modified`
316
  FROM $wpdb->posts
317
  WHERE `post_status` = 'trash'
318
- ORDER BY UCASE(`post_title`)
 
 
 
 
319
  ";
320
  $results = $wpdb -> get_results($sql);
321
 
@@ -329,8 +340,9 @@ if($_REQUEST['action'] != 'run')
329
  </tr>
330
  <tr>
331
  <th align="right" style="border-bottom:solid 1px #999;">#</th>
332
- <th align="left" style="border-bottom:solid 1px #999;">post / page</th>
333
- <th align="left" style="border-bottom:solid 1px #999;">last modified</th>
 
334
  </tr>
335
  <?php
336
  $nr = 1;
@@ -340,16 +352,33 @@ if($_REQUEST['action'] != 'run')
340
  ?>
341
  <tr>
342
  <td align="right"><?php echo $nr; ?></td>
343
- <td><?php echo $results[$i]->post_title; ?></td>
344
- <td><?php echo $results[$i]->post_modified; ?></td>
 
345
  </tr>
346
  <?php
 
 
 
 
 
 
 
 
347
  $nr++;
348
- }
 
 
349
  $sql_delete = "
350
  DELETE FROM $wpdb->posts WHERE `post_status` = 'trash'
351
  ";
352
  $wpdb -> get_results($sql_delete);
 
 
 
 
 
 
353
  ?>
354
  </table>
355
  <?php
@@ -382,7 +411,7 @@ if($_REQUEST['action'] != 'run')
382
  SELECT `comment_ID`, `comment_author`, `comment_author_email`, `comment_date`
383
  FROM $wpdb->comments
384
  WHERE `comment_approved` = 'spam'
385
- ORDER BY UCASE(`comment_author`)
386
  ";
387
  $results = $wpdb -> get_results($sql);
388
 
@@ -418,7 +447,8 @@ if($_REQUEST['action'] != 'run')
418
  ";
419
  $wpdb -> get_results($sql_delete);
420
  $nr++;
421
- }
 
422
  $sql_delete = "
423
  DELETE FROM $wpdb->comments WHERE `comment_approved` = 'spam'
424
  ";
@@ -508,7 +538,7 @@ $end_size = rvg_get_db_size();
508
  <br />
509
  <span style="font-weight:bold;color:#00F;padding-left:8px;">D O N E !</span>
510
  <?php
511
- }
512
  ?>
513
  <?php
514
  /********************************************************************************************
1
  <?php
2
+ $version = '1.3.1';
3
+ $release_date = '10/07/2012';
4
  /**
5
  * @package Optimize Database after Deleting Revisions
6
+ * @version 1.3.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="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.1
14
  Author URI: http://cagewebdev.com
15
  */
16
  ?>
166
  DELETE REVISIONS
167
 
168
  ******************************************************************************************/
169
+
170
+ // GET OPTIONS AND SET DEFAULT VALUES
171
  $max_revisions = get_option('rvg_odb_number');
172
  if(!$max_revisions)
173
  { $max_revisions = 0;
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
 
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>
317
  <?php
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
 
340
  </tr>
341
  <tr>
342
  <th align="right" style="border-bottom:solid 1px #999;">#</th>
343
+ <th align="left" style="border-bottom:solid 1px #999;">type</th>
344
+ <th align="left" style="border-bottom:solid 1px #999;">IP address / title</th>
345
+ <th align="left" style="border-bottom:solid 1px #999;">date</th>
346
  </tr>
347
  <?php
348
  $nr = 1;
352
  ?>
353
  <tr>
354
  <td align="right"><?php echo $nr; ?></td>
355
+ <td><?php echo $results[$i]->post_type; ?></td>
356
+ <td><?php echo $results[$i]->title; ?></td>
357
+ <td><?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
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
 
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
  ";
538
  <br />
539
  <span style="font-weight:bold;color:#00F;padding-left:8px;">D O N E !</span>
540
  <?php
541
+ } // rvg_optimize_db()
542
  ?>
543
  <?php
544
  /********************************************************************************************