Automatically purging the trashcan

Within the mail-database exchanger, an automated task can be launched periodically by way of a maintenance plugin. As an example of such a task, we'll see in this article how to set up a maintenance plugin to remove messages over X days old from the trashcan and delete them permanently.

Why not delete immediately?

In short, because trashing is faster and can be undone in the case of a mistake.

At the SQL level, moving a message into the trashcan means moving its entry from the mail table to the trashed_mail table. It is quite fast.

Deleting, on the contrary, is done by removing every explicit reference to the message in all tables, as well as removing the attachments, which makes it a comparatively much slower operation.

Also, once deleted, a message can't be recovered whereas in the trashcan any message can be moved back into the main mail table with no loss of information.

The SQL queries

The manitou database should contain a function delete_msg(integer) that performs the actual deletion. If not, the source code for the function can be found in the manitou-sql package (currently manitou-sql-0.9.7.tar.gz). All we have to do in the plugin is to select the messages to be deleted and pass their mail_id's one by one to this function.

Let's choose to select the messages that are older than 30 days. The SQL query is as easy as:

SELECT mail_id FROM trashed_mail WHERE msg_date>=now()-'30 days'::interval;

The plugin's code

Now we just have to put some code around our queries to call them in the context of a plugin.

package Manitou::Plugins::trashcan_purge;

use DBI;
sub init {
  bless {}
}
sub finish {
  1;
}

sub process {
  my $dbh=$_[1]->{dbh};
  local $dbh->{RaiseError}=1;
  local $dbh->{PrintError}=1;
  eval {
    $dbh->begin_work;
    my $q=$dbh->prepare("SELECT mail_id FROM trashed_mail WHERE msg_date<now()-'30 days'::interval");
    $q->execute; 
    my $d=$dbh->prepare("SELECT delete_msg(?)");
    while (my ($mail_id)=$q->fetchrow_array) {
      $d->execute($mail_id);
    }
  };
  if ($@) {
    # An error has occurred. The error message should have been printed
    # to STDERR already
    $dbh->rollback;   # clean up the transaction
  }
  else {
    $dbh->commit;
  }
}
1;

The declaration in manitou-mdx.conf

We set it up to be launched once per night, for example at 3am, by adding this line in the [common] section:

[common]
maintenance_plugins = 3:00 purge_trashcan