| #!/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; |
| my ( %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(); |