[Amavisadmin-svn] SF.net SVN: amavisadmin: [28] amavisadmin/trunk/src/java/de/sreindl/ amavisadmin/jobs/MessagesCleanupJob.java

[<str...@us...>]

Revision: 28
          http://amavisadmin.svn.sourceforge.net/amavisadmin/?rev=28&view=rev
Author:   streindl
Date:     2007-01-18 12:04:46 -0800 (Thu, 18 Jan 2007)

Log Message:
-----------
Fix for bugs #1638825 and #1638917:
* Performance improvements on cleanup
* Do not delete mail addresses where users are connected to.

Modified Paths:
--------------
    amavisadmin/trunk/src/java/de/sreindl/amavisadmin/jobs/MessagesCleanupJob.java

Modified: amavisadmin/trunk/src/java/de/sreindl/amavisadmin/jobs/MessagesCleanupJob.java
===================================================================
--- amavisadmin/trunk/src/java/de/sreindl/amavisadmin/jobs/MessagesCleanupJob.java	2007-01-18 19:53:33 UTC (rev 27)
+++ amavisadmin/trunk/src/java/de/sreindl/amavisadmin/jobs/MessagesCleanupJob.java	2007-01-18 20:04:46 UTC (rev 28)
@@ -43,6 +43,9 @@
     
     /**
      * remove all messages which contain the status 'D' in field 'msgrcpt.rs'.
+     * and further deletions
+     *
+     * fix for #1638917
      */
     private void removeDeletedMessages(Session session) {
         log.trace("start removeDeletedMessages");
@@ -51,25 +54,28 @@
         msgsMarked += numRows;
         log.info("Removed " + Integer.toString(numRows) + 
                 " message receipients marked as deleted from the database");
-        qry = session.createSQLQuery("DELETE FROM msgrcpt WHERE rid IN (SELECT id FROM maddr WHERE user_id IS NULL)");
+        qry = session.createSQLQuery("DELETE FROM msgrcpt " +
+                "WHERE rid IN (SELECT DISTINCT id FROM maddr WHERE user_id IS NULL)");
         numRows = qry.executeUpdate();
         msgsMarked += numRows;
         log.info("Removed " + Integer.toString(numRows) + 
                 " message receipients with nonlocal recipients from the database");
-        qry = session.createSQLQuery("delete from quarantine " +
-                "where mail_id not in (select mail_id from msgrcpt)");
+        qry = session.createSQLQuery("DELETE FROM quarantine " +
+                "WHERE NOT EXISTS (SELECT 1 FROM msgrcpt WHERE mail_id = quarantine.mail_id)");
         numRows = qry.executeUpdate();
         msgsMarked += numRows;
         log.info("Removed " + Integer.toString(numRows) + 
                 " quarantine entries from the database");
-        qry = session.createSQLQuery("delete from msgs " +
-                "where mail_id not in (select mail_id from msgrcpt)");
+        qry = session.createSQLQuery("delete FROM msgs " +
+                "WHERE NOT EXISTS (SELECT 1 FROM msgrcpt WHERE mail_id = msgs.mail_id)");
         numRows = qry.executeUpdate();
         msgsMarked += numRows;
         log.info("Removed " + Integer.toString(numRows) + 
                 " messages from the database");
+        // #1638825
         qry = session.createSQLQuery("DELETE FROM maddr " +
-                "WHERE NOT EXISTS (SELECT 1 FROM msgs    WHERE sid=id) "+
+                "WHERE user_id IS NULL " +
+                "  AND NOT EXISTS (SELECT 1 FROM msgs    WHERE sid=id) "+
                 "  AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id)");
         numRows = qry.executeUpdate();
         msgsMarked += numRows;


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.