convert_awl_dbm_to_sql   [plain text]

#!/usr/bin/perl

# WARNING: This script is VERY rough and provided only as a template
# for moving a DB based autowhitelist to a SQL based one.  You should
# do backups and that sort of thing before attempting to use this
# script.

use strict;
use Fcntl;

use Getopt::Long;

use DBI;

use DB_File ;
use vars qw( %h $k $v ) ;

sub usage {
  print "This program takes the following required arguments:\n";
  print "--username <username> - This is who's whitelist you are loading.\n";
  print "                        It should match exactly what spassassin or\n";
  print "                        spamd will be using.\n";
  print "--dsn <dsn> - This is the database DSN.  It should be in the form:\n";
  print "              DBI:driver:database:hostname[:port]\n";
  print "              Consult your database drivers docs for more info.\n";
  print "--ok - Basically a sanity check that you understand how dangerous this script is.\n";
  print "\n";
  print "This program take the following optional arguments:\n";
  print "--dbautowhitelist <path>  - path to the auto-whitelist you wish to\n";
  print "                            convert. Default is to use \n";
  print "                            \$ENV{HOME}/.spamassassin/auto-whitelist\n";
  print "--sqlusername <username> - Needed if your DBI driver requires a username.\n";
  print "--sqlpassword <password> - Needed if your DBI driver requires a password.\n";
  print "\n\n";
  print "WARNING: This script is VERY rough and not well tested.  You should\n";
  print "use extreme caution when working with it.  Including backing up your\n";
  print "data and all that other good stuff.\n";
  print "Passing of the --ok flag means you read this warning.\n";
  print "\n";
  exit;
}

my %opt;

GetOptions('dsn=s' => \$opt{'dsn'},
           'sqlusername=s' => \$opt{'sqlusername'},
           'sqlpassword=s' => \$opt{'sqlpassword'},
           'dbautowhitelist=s' => \$opt{'dbautowhitelist'},
           'username=s' => \$opt{'username'},
           'help' => \$opt{'help'},
           'ok' => \$opt{'ok'},
           );

if ($opt{'help'}) {
  usage();
}

if (!$opt{'ok'}) {
  usage();
}


if (!$opt{'username'} || !$opt{'dsn'}) {
  usage();
}

my $db;
if ($opt{'dbautowhitelist'}) {
  $db = $opt{'dbautowhitelist'};
}
else {
  $db = $ENV{HOME}."/.spamassassin/auto-whitelist";
}

tie %h, "DB_File",$db, O_RDONLY,0600
    or die "Cannot open file $db: $!\n";

my $dbh = DBI->connect($opt{'dsn'}, $opt{'sqlusername'}, $opt{'sqlpassword'})
    or die $DBI::errstr;

my $sth = $dbh->prepare("DELETE FROM awl WHERE username = ?");
$sth->execute($opt{'username'});

my $sth = $dbh->prepare("INSERT INTO awl (username,email,ip,count,totscore) VALUES (?,?,?,?,?)");

my @k = grep(!/totscore$/,keys(%h));
for my $key (@k) {
  my $totscore = $h{"$key|totscore"};
  my $count = $h{$key};
  if(defined($totscore)) {
    my ($email, $ip) = split(/\|ip=/, $key);

    if ($email && $ip) {
      my $rc = $sth->execute($opt{'username'}, $email, $ip, $count, $totscore);
      printf "% 8.1f %15s  --  %s\n", $totscore/$count, (sprintf "(%.1f/%d)",$totscore/$count, $count), $key;
    }
  }
}
untie %h;
$dbh->disconnect();