scripts/mz_db_clean.pl

   1 #!/usr/local/bin/perl -w
   2
   3 use strict;
   4 use Getopt::Long;
   5
   6 ##### PLEASE CONFIGURE THIS SECTION ######
   7
   8 # Globals
   9 # Set this array for database authentication
  10 my(@storage_sql_dsn) = (
  11         'DBI:Pg:database=dbname;host=host.example.org',
  12         'user','password'
  13                        );
  14
  15 # Set this to 1 if you are using the new database schema
  16 # introduced with amavisd 2.4.0. Only do this if you also
  17 # have the foreign key references between the tables and use
  18 # time_iso as a real date type instead of a string.
  19 my($new_dd) = 1; # 1 or undef
  20
  21 # Purge old messages - One Week
  22 my($interval) = time - 7*24*60*60;
  23 # Purge incomplete messages - 1 hour
  24 my($partial_interval) = time - 60*60;
  25
  26 # These variables only matter if $new_dd is set to 1
  27 # The values for these variables must be syntactically
  28 # correct for your database. This value is passed to
  29 # the 'interval' keyword. Please check your database
  30 # documentation
  31
  32 # PostgreSQL
  33 my($new_interval) = '1 week';
  34 my($new_partial_interval) = '1 hour';
  35 # MySQL
  36 #my($new_interval) = '7 day';
  37 #my($new_partial_interval) = '1 hour';
  38
  39 # PostgreSQL specific options
  40 # Should we VACUUM ANALYZE the database after the purge?
  41 # Default is undef because we should be using autovacuum
  42 my($postop_vacuum) = undef; # 1 or undef
  43
  44 ##### END OF CONFIGURATION SECTION ######
  45
  46 # Options array
  47 my(%opt);
  48
  49 my(@modules);
  50 my(@missing);
  51
  52 my(@dsn) = split(/:/,$storage_sql_dsn[0],-1);
  53 push(@modules, 'DBD::'.$dsn[1])  if uc($dsn[0]) eq 'DBI';
  54
  55 for my $m (@modules) {
  56   local($_) = $m;
  57   $_ .= /^auto::/ ? '.al' : '.pm'  if !/\.(pm|pl|al)\z/;
  58   s[::][/]g;
  59   eval { require $_ } or push(@missing, $m);
  60 }
  61
  62 die "ERROR: MISSING module(s):\n" . join('', map { "  $_\n" } @missing) if @missing;
  63
  64 sub build_queries($) {
  65   my($dbtype) = shift;
  66   # Return a hash of queries to be run
  67   my(%query) = (
  68                # Old schema queries
  69                'del_d_flag' =>
  70                  'DELETE FROM msgrcpt ' .
  71                  'WHERE rs=\'D\'',
  72                'del_partial_msg' =>
  73                  'DELETE FROM msgs ' .
  74                  "WHERE time_num < $partial_interval " .
  75                  'AND content IS NULL',
  76                'del_old_mail_ids' =>
  77                  'DELETE FROM msgs ' .
  78                  "WHERE time_num < $interval",
  79                'del_msgs_mail_ids' =>
  80                  'DELETE FROM msgs ' .
  81                  'WHERE NOT EXISTS ' .
  82                  ' (SELECT 1 FROM msgrcpt ' .
  83                  '  WHERE msgrcpt.mail_id=msgs.mail_id)',
  84                'del_quarantine' =>
  85                  'DELETE FROM quarantine ' .
  86                  'WHERE NOT EXISTS '.
  87                  ' (SELECT 1 FROM msgs ' .
  88                  '  WHERE msgs.mail_id=quarantine.mail_id)',
  89                'del_msgrcpt' =>
  90                  'DELETE FROM msgrcpt ' .
  91                  'WHERE NOT EXISTS ' .
  92                  ' (SELECT 1 FROM msgs ' .
  93                  '  WHERE msgs.mail_id=msgrcpt.mail_id)',
  94
  95                # New schema queries
  96                'del_d_flag_new' =>
  97                  'DELETE FROM msgs ' .
  98                  'WHERE mail_id IN ' .
  99                  '  (SELECT DISTINCT mail_id ' .
 100                  '   FROM msgrcpt WHERE rs=\'D\')',
 101
 102                # Generic queries
 103                'del_maddr' =>
 104                  'DELETE FROM maddr ' .
 105                  'WHERE NOT EXISTS '  .
 106                  '  (SELECT sid FROM msgs WHERE sid=id) ' .
 107                  '   AND NOT EXISTS' .
 108                  '  (SELECT rid FROM msgrcpt WHERE rid=id)'
 109
 110              );
 111
 112   if ($dbtype eq 'pgsql') {
 113     $query{'vacuum_analyze'} = 'VACUUM ANALYZE';
 114     # New schema queries
 115     $query{'del_old_mail_ids_new'} = 'DELETE FROM msgs ' .
 116                                      'WHERE time_iso < now() ' .
 117                                      "- interval '$new_interval'";
 118     $query{'del_partial_msg_new'} = 'DELETE FROM msgs ' .
 119                                      'WHERE time_iso < now() ' .
 120                                      "- interval '$new_partial_interval' " .
 121                                      ' AND content IS NULL';
 122   }
 123
 124   if ($dbtype eq 'mysql') {
 125     # New schema queries
 126     $query{'del_old_mail_ids_new'} = 'DELETE FROM msgs ' .
 127                                      'WHERE time_iso < UTC_TIMESTAMP() ' .
 128                                      "- interval $new_interval";
 129     $query{'del_partial_msg_new'} = 'DELETE FROM msgs ' .
 130                                      'WHERE time_iso < UTC_TIMESTAMP() ' .
 131                                      "- interval $new_partial_interval " .
 132                                      ' AND content IS NULL';
 133
 134     # Old schema queries
 135     $query{'del_msgs_mail_ids'} = 'DELETE msgs FROM msgs ' .
 136                                   'LEFT JOIN msgrcpt USING(mail_id) ' .
 137                                   'WHERE msgrcpt.mail_id IS NULL';
 138     $query{'del_quarantine'} = 'DELETE quarantine FROM quarantine ' .
 139                                'LEFT JOIN msgs USING(mail_id) '.
 140                                'WHERE msgs.mail_id IS NULL';
 141     $query{'del_msgrcpt'} = 'DELETE msgrcpt FROM msgrcpt ' .
 142                             'LEFT JOIN msgs USING(mail_id) ' .
 143                             'WHERE msgs.mail_id IS NULL';
 144   }
 145
 146   my(%post_query) = (
 147                 'vacuum_analyze' => 'VACUUM ANALYZE'
 148                     );
 149
 150   # Order of execution IS IMPORTANT!
 151   my(@query_order) = qw (del_d_flag del_partial_msg del_msgs_mail_ids
 152                          del_old_mail_ids del_quarantine del_msgrcpt
 153                          del_maddr
 154                         );
 155
 156   @query_order = qw (del_d_flag_new del_partial_msg_new
 157                          del_old_mail_ids_new del_maddr
 158                         ) if $new_dd;
 159
 160   my(@post_query_order);
 161   push(@post_query_order, 'vacuum_analyze') if $dbtype eq 'pgsql' && $postop_vacuum;
 162
 163   return (\%query,\@query_order,\%post_query,\@post_query_order);
 164 }
 165
 166
 167 sub usage {
 168   print "Usage:\n";
 169   print "\tmz_db_clean.pl [--verbose|-v] [--database|-db <dbtype>]\n";
 170   print "\tmz_db_clean.pl --help|-h \n\n";
 171   print "\tThe database configuration parameter is REQUIRED!\n\n";
 172   print "\tPossible parameters for the \'--database\' option is \'mysql\'\n" .
 173         "\tand 'pgsql'.\n";
 174   exit;
 175 }
 176
 177 sub main {
 178   Getopt::Long::Configure('no_ignore_case');
 179   GetOptions(\%opt, 'help|h', 'database|db=s', 'verbose|v',
 180             ) or exit(1);
 181   usage if $opt{help};
 182   usage if not $opt{database};
 183   my($dbh) = connect_to_sql(@storage_sql_dsn);
 184   my($query,$query_order,$p_query,$p_query_order) = build_queries($opt{database});
 185   my($sth_ref) = prepare_queries($dbh,$query,$p_query);
 186   my($result) = exec_queries($dbh,$sth_ref,$query,$p_query,
 187                              $query_order,$p_query_order
 188                             );
 189   print "Database cleanup successful\n" if $result;
 190   $dbh->disconnect;
 191 }
 192
 193 sub exec_queries($$$$$) {
 194   my($dbh,$sth_ref,$query,$p_query,$query_o,$p_query_o) = @_;
 195   my($affected);
 196
 197   $dbh->begin_work;
 198   eval {
 199     foreach (@$query_o) {
 200       if ($opt{verbose}) {
 201         print "Executing... " . localtime() . "\n";
 202         print $query->{$_} . "\n";
 203       }
 204       $affected = $sth_ref->{$_}->execute or die "Query '$_' did not execute";
 205       print "$affected rows affected\n" if $opt{verbose};
 206     }
 207   };
 208   if ($@ ne '') {
 209     $dbh->rollback;
 210     print "There was an error executing a query! $@\n" .
 211           "No records modified by database maintenance\n" .
 212           "Rollback complete.\n";
 213     return undef
 214   } else {
 215     $dbh->commit;
 216   }
 217
 218   eval {
 219     foreach (@$p_query_o) {
 220       if ($opt{verbose}) {
 221         print "Executing... " . localtime() . "\n";
 222         print $p_query->{$_} . "\n";
 223       }
 224       $affected = $sth_ref->{$_}->execute or die "Query '$_' did not execute";
 225       print "$affected rows affected\n" if $opt{verbose};
 226     }
 227   };
 228   if ($@ ne '') {
 229     print "There was an error executing an optional query! $@\n" .
 230     return undef
 231   }
 232
 233   return 1;
 234
 235 }
 236
 237 sub connect_to_sql(@) {
 238   my(@sql_dsn) = @_;
 239   my($dsn, $username, $password) = @sql_dsn;
 240   print "Connecting to SQL database server\n" if $opt{verbose};
 241   print "Trying dsn '$dsn'\n" if $opt{verbose};
 242   my($dbh) = DBI->connect($dsn, $username, $password,
 243 #     {PrintError => 1, RaiseError => 0, Taint => 1, AutoCommit => 0} );
 244      {PrintError => 1, RaiseError => 0, Taint => 1} );
 245   if ($dbh) {
 246     print "Connection to '$dsn' succeeded\n" if $opt{verbose};
 247   } else {
 248     die "Unable to connect to '$dsn'!\n";
 249   }
 250   $dbh;
 251 }
 252
 253 sub prepare_queries($$$) {
 254   my($dbh) = shift;
 255   my($query) = shift;
 256   my($p_query) = shift;
 257   my(%sths);
 258   foreach my $query_set ($query, $p_query) {
 259     foreach (keys %$query) {
 260       $sths{$_} = $dbh->prepare($query->{$_});
 261     }
 262   }
 263   \%sths
 264 }
 265
 266 main;