#! /usr/bin/perl -w
#
# Read the latest mailing list info and load it into the database.
# Input is either ASCII text formatted like:
#
# or XML like:
#
#
#
# sitename
# timestamp
#
# listname
# eddress...
# int
# int
# encoded-info
# int
#
# ...
#
#
#
use strict;
use Data::Dumper;
use DBI;
use DBD::mysql;
use Digest::MD5 qw(md5_hex);
use Getopt::Long;
use Symbol;
use XML::LibXML::Common qw(:encoding);
use XML::Simple qw(:strict);
my $fh = select(STDOUT);
$| = 1;
select(STDERR);
$| = 1;
select($fh);
my $dsn = "DBI:$ENV{PULSE_DBMS}"
. ":database=$ENV{PULSE_DB_NAME}"
. ":host=$ENV{PULSE_DB_HOST}";
my $dbx = DBI->connect($dsn, $ENV{PULSE_DB_USER}, $ENV{PULSE_DB_PASSWORD},
{
RaiseError => 1,
PrintError => 1,
});
Getopt::Long::Configure('bundling');
Getopt::Long::Configure('auto_abbrev');
Getopt::Long::Configure('pass_through');
my $debug = 0;
my $dry_run = 0;
my $infile = '-';
GetOptions(
'd|debug+' => \$debug,
'f|input-file=s' => \$infile,
'n|no-action' => \$dry_run,
);
my $asof = '1980-01-01';
my $sql;
my $status;
my $dbq;
my $xml = 0;
my $linenum = 0;
my $xp = new XML::Simple;
my @lines;
my $lastdomain;
my $domain;
my $list;
my $subscribers;
my $digesters;
my $moderators;
$infile = 'STDIN' if ($infile eq '-');
debug(2, "Reading from $infile");
my $ifh;
if ($infile ne 'STDIN') {
$ifh = gensym();
open($ifh, '<' . $infile) || die("Error opening '$infile': $!");
}
else {
$ifh = \*STDIN;
}
while (my $line = <$ifh>) {
$line =~ s§[\r\n\s]+$§§g;
chomp($line);
next if (! $line);
if ($line =~ /<\?xml/) {
debug(2, 'XML-format detected');
$xml++;
@lines = <$ifh>;
chomp(@lines);
unshift(@lines, $line);
next;
}
debug(5, "line='$line'");
($list, $subscribers, $digesters, $moderators) = split(m/\|/, $line);
$list =~ m§(.*)\@(.*)$§;
$list = $1;
$domain = $2;
$subscribers =~ s/\s+//g;
$digesters =~ s/\s+//g;
$digesters = -1 if ($digesters =~ /none/i);
$moderators =~ s/\s+/,/g;
if ($domain ne $lastdomain) {
push(@lines, ' ') if ($lastdomain);
push(@lines, ' ');
push(@lines, " $domain");
$lastdomain = $domain;
}
push(@lines,
' ',
" $list",
" $moderators",
" $subscribers",
" $digesters",
"
");
}
close($ifh) if ($ifh != \*STDIN);
if (! $xml) {
push(@lines, ' ') if ($domain);
push(@lines, "\n");
}
#
# Remove the 'XMLin($xml,
forcearray => 1,
keyattr => [ ],
);
debug('XML parsed');
#print Dumper($xml_parsed);
$asof = $xml_parsed->{asof}->[0];
debug(2, "AS-OF determined as '$asof'");
$sql = ('INSERT INTO loadinfo '
. "SET asof='$asof' ");
my $oldprinterr = $dbx->{PrintError};
$dbx->{PrintError} = 0;
if (! $dry_run) {
$dbq = $dbx->prepare($sql);
$status = $dbq->execute();
}
$dbx->{PrintError} = $oldprinterr;
for (my $i = 0; $i <= $#{$xml_parsed->{site}}; $i++) {
my $site = $xml_parsed->{site}->[$i];
debug(3, $site->{name}->[0]);
for (my $j = 0; $j <= $#{$site->{list}}; $j++) {
my @dbargs;
my $dmsg;
my $list = $site->{list}->[$j];
my $info_xid = 0;
$status = $list->{status}->[0];
$sql = "INSERT INTO list SET list=?, asof=?, domain=?, "
. "status=?";
my $domain_name = $site->{name}->[0];
my $list_name = $list->{name}->[0] . '@' . $domain_name;
push(@dbargs, $list_name, $asof, $domain_name, $status);
$dmsg = $sql . " (list='$list_name', asof='$asof', domain='$domain_name', status='$status') ";
if (defined($list->{subscribers})) {
$sql .= ', count=?';
$dmsg .= ', count=?';
push(@dbargs, $list->{subscribers}->[0]);
$dmsg .= " (count='" . $list->{subscribers}->[0] . "') ";
}
if (defined($list->{'digest-subscribers'})) {
$sql .= ', dcount=?';
$dmsg .= ', dcount=?';
push(@dbargs, $list->{'digest-subscribers'}->[0]);
$dmsg .= " (dcount='" . $list->{'digest-subscribers'}->[0] . "') ";
}
if (defined($list->{messages})) {
$sql .= ', posts=?';
$dmsg .= ', posts=?';
push(@dbargs, $list->{messages}->[0]);
$dmsg .= " (posts='" . $list->{messages}->[0] . "') ";
}
if (defined($list->{moderators})) {
$sql .= ', moderators=?';
$dmsg .= ', moderators=?';
push(@dbargs, $list->{moderators}->[0]);
$dmsg .= " (moderators='" . $list->{moderators}->[0] . "') ";
}
if (defined($list->{info})) {
debug(5, "Decoding info from UTF8: [['" . $list->{info}->[0] . "']]");
my $info = decodeFromUTF8('ISO-8859-1', $list->{info}->[0]);
my $sum = md5_hex($info);
my $lsql = 'SELECT xid FROM info_text WHERE sum=?';
if (! $dry_run) {
$dbq = $dbx->prepare($lsql);
$dbq->execute($sum);
if ($dbq->rows() == 0) {
debug(3, 'Adding a new list info record');
my $l2sql = 'INSERT INTO info_text SET sum=?, info=?';
$dbq = $dbx->prepare($l2sql);
$dbq->execute($sum, $info);
$dbq = $dbx->prepare($lsql);
$dbq->execute($sum);
}
my $hash = $dbq->fetchrow_hashref();
$info_xid = $hash->{xid};
}
}
if ($info_xid) {
$sql .= ', info=?';
push(@dbargs, $info_xid);
}
debug(5, "SQL='$dmsg'");
if (! $dry_run) {
$dbq = $dbx->prepare($sql);
$dbq->execute(@dbargs);
}
debug(3, ' ' . $list->{name}->[0]);
}
}
if (! $dry_run) {
$dbx->disconnect();
}
exit(0);
sub debug {
my $text = shift;
my $level = 1;
if ($text =~ /^\d+$/) {
$level = $text;
$text = shift;
}
if ($debug >= $level) {
chomp($text);
print STDERR "DEBUG: $text\n";
}
}
#
# Local Variables:
# mode: cperl
# tab-width: 4
# indent-tabs-mode: nil
# End:
#