postfix-log-sql.pl

#!/usr/bin/env perl

use strict;
use warnings;
use Fcntl;

my $filename = '/var/log/maillog';
#my $sqltemp = '/tmp/.sqltemp-emails.sql';
my %hash = ();
my $data;

sysopen(my $fh, $filename, O_RDONLY)
    or die "Não foi possível abrir o arquivo: $filename .";

# Arrumar essa bigodagem da data não ter o ano, converter mês para número, colocar 0 no dia...
my %mon = ('Jan' => '01', 'Feb' => '02',  'Mar' => '03', 'Apr' => '04', 'May' => '05', 'Jun' => '06',
            'Jul' => '07', 'Aug' => '08', 'Sep' => '09', 'Oct' => '10', 'Nov' => '11', 'Dec' => '12');
#'Jan 2 24:14:12' -> '01-02 24:14:12'
#my $in = $fields[0] . "-" . $fields[1];
#if ($in =~ m/(...)-(.*)/) {
#    my ($m, $d) = ($1,$2);
#$data = sprintf '%02d-%02d %s', $mon{$m}, $d, $fields[2];
#}

while (<$fh>) {
    chomp;
    if ($_ =~ /\s([0-9A-F]+): message-id=<(.+?)>/) {
             $hash{$1} = $2;
    }
}

seek($fh, 0, 0);

while (<$fh>){
    if ($_  =~ /^([A-Z][a-z]{2})\s(.+?)\s(..:..:..)\s.*?\s([0-9A-F]+):\s.*?status=(.+?)[,]?\s(\(.+?\))/){
        if(exists $hash{$4}) {
            $data = sprintf '%02d-%02d %s', $mon{$1}, $2, $3;
            my $queue_id = $4;
#           print  "$data" . " " . "$4" . " " . "$5" . " " . "$hash{$4}" . "\n" if ( $5 !~ /deferred/);
            if ($5 =~ /sent/){
                print "update system.email_sent set delivery_at=(EXTRACT(year from created_at)||'-'||'$data')::TIMESTAMP where message_id='$hash{$queue_id}';\n";
#                "$data" . " " .  "$hash{$queue_id}" . "\n";
            }
            else{
                my $string = $6;
                $string =~ s/(['"])/\\$1/g;
                print "update system.email_sent set delivery_at=(EXTRACT(year from created_at)||'-'||'$data')::TIMESTAMP, status='$5' ,error='$6' where message_id='$hash{$queue_id}';\n" if ($5 !~ /deferred/) ;
#                "$data" . " " . "$hash{$queue_id}" . " " . "$5" . "\n" if ( $5 !~ /deferred/);
            }
        }
    }
}

#
# Printa message_id e status das linhas que queue_id forem comuns
#
# se status=sent insere data where id da mensagem,
# se ! status=sent insere data, error where id da mensagem.
#
# SQL "update system.email_sent set delivery_at=(EXTRACT(year from created_at)||'-'||'$data')::TIMESTAMP where message_id='$message_id'"
# SQL "update system.email_sent set delivery_at=(EXTRACT(year from created_at)||'-'||'$data')::TIMESTAMP set error= '$status' where message_id='$message_id'"
#