#!/usr/bin/perl -w
use strict;
use English;
use Data::Dumper;
use Time::HiRes qw(sleep time alarm);
use POSIX qw(strftime);
use DBI;
die "You must provide at least one filename!" if scalar @ARGV < 1;
my $dbc = DBI->connect("DBI:Pg:dbname=wiley;host=localhost;port=5432;sslmode=prefer", "quixadhal", "tardis69", { AutoCommit => 0, PrintError => 0, });
my $insert_query = $dbc->prepare( qq!
INSERT INTO board_messages ( board_id, message_id, message_date, message_sender, message_header, message_text )
VALUES ( ?, ?, to_timestamp( ?, 'Dy Mon DD HH24:MI:SS YYYY'), trim(?), trim(?), ?);
!);
foreach my $filename (@ARGV) {
$filename =~ /^(\d+)\.txt$/;
my $vnum = $1;
die "Invalid file: $filename" if !defined $vnum;
open FP, $filename or die "Cannot open $filename: $!";
my $id = 0;
my $rv = $dbc->do( qq!
DELETE FROM board_messages WHERE board_id = ?
!, undef, $vnum);
if($rv) {
print "CLEARED: $vnum\n";
$dbc->commit;
} else {
$dbc->rollback;
}
print "Processing $filename...\n";
while(my $line = <FP>) {
chomp $line;
$line =~ /^([^\(]+)\((\w+)\)\s+(.+)$/;
my ($subject, $sender, $date) = ($1, $2, $3);
next if !defined $subject;
next if !defined $sender;
next if !defined $date;
my $body = "";
while(my $bodyline = <FP>) {
chomp $bodyline;
last if $bodyline eq ".";
$body .= "$bodyline\r\n";
}
my $rv = $insert_query->execute($vnum, $id, $date, $sender, $subject, $body);
if($rv) {
print "ADDED: $subject ($sender) $date\n";
$dbc->commit;
$id++;
} else {
print "FAILED: $subject ($sender) $date\n";
$dbc->rollback;
}
}
print "Done!\n";
close FP;
}
$dbc->disconnect();
1;
