#################################################################################
#
# File: $RCSfile: MAPSDB.pm,v $
-# Revision: $Revision: 1.1 $
+# Revision: $Revision: 1.1 $
# Description: MAPS Database routines
# Author: Andrew@DeFaria.com
# Created: Fri Nov 29 14:17:21 2002
use strict;
use vars qw (@ISA @EXPORT);
use DBI;
+use Carp;
use MAPSUtil;
$DB->do ($statement)
or DBError 'AddEmail: Unable to do statement', $statement;
+
+ return;
} # AddEmail
-sub AddList ($$$;$$) {
- my ($listtype, $pattern, $sequence, $comment, $hitcount) = @_;
-
+sub AddList ($$$;$$$) {
+ my ($listtype, $pattern, $sequence, $comment, $hitcount, $last_hit) = @_;
+
$hitcount ||= 0;
my ($user, $domain) = split /\@/, $pattern;
} # if
# Get next sequence #
- if ($sequence eq 0) {
+ if ($sequence == 0) {
$sequence = GetNextSequenceNo $userid, $listtype;
} # if
- my $timestamp = UnixDatetime2SQLDatetime (scalar (localtime));
+ $last_hit //= UnixDatetime2SQLDatetime (scalar (localtime));
- my $statement = "insert into list values (\"$userid\", \"$listtype\", $pattern, $domain, $comment, $sequence, $hitcount, \"$timestamp\")";
+ my $statement = "insert into list values (\"$userid\", \"$listtype\", $pattern, $domain, $comment, $sequence, $hitcount, \"$last_hit\")";
$DB->do ($statement)
or DBError 'AddList: Unable to do statement', $statement;
+
+ return;
} # AddList
sub AddLog ($$$) {
$DB->do ($statement)
or DBError 'AddLog: Unable to do statement', $statement;
+
+ return;
} # AddLog
sub AddUser ($$$$) {
$DB->do ($statement)
or DBError 'AddList: Unable to do statement', $statement;
+
+ return;
} # RecordHit
-sub CheckOnList ($$) {
+sub CheckOnList ($$;$) {
# CheckOnList will check to see if the $sender is on the $listfile.
# Return 1 if found 0 if not.
- my ($listtype, $sender) = @_;
+ my ($listtype, $sender, $update) = @_;
+
+ $update //= 1;
- my $status = 0;
- my $rule;
+ my $status = 0;
+ my ($rule, $hit_count);
my $statement = "select pattern, domain, comment, sequence, hit_count from list where userid = '$userid' and type = '$listtype'";
while (my @row = $sth->fetchrow_array) {
last if !@row;
- my $hit_count = pop (@row);
+ $hit_count = pop (@row);
my $sequence = pop (@row);
my $comment = pop (@row);
my $domain = pop (@row);
$rule .= " - $comment" if $comment and $comment ne '';
$status = 1;
- RecordHit $listtype, $sequence, ++$hit_count;
+ RecordHit $listtype, $sequence, ++$hit_count if $update;
last;
} # if
$sth->finish;
- return ($status, $rule);
+ return ($status, $rule, $hit_count);
} # CheckOnList
sub CleanEmail ($) {
} # unless
# Just return if there's nothing to delete
- return $count if ($count eq 0);
+ return $count if ($count == 0);
# Delete emails for userid whose older than $timestamp
$statement = "delete from email where userid = '$userid' and timestamp < '$timestamp'";
} # unless
# Just return if there's nothing to delete
- return $count if ($count eq 0);
+ return $count if ($count == 0);
# Delete log entries for userid whose older than $timestamp
$statement = "delete from log where userid = '$userid' and timestamp < '$timestamp'";
$count = $row[0] ? $row[0] : 0;
# Just return if there's nothing to delete
- return $count if ($count eq 0);
+ return $count if ($count == 0);
# Get data for these entries
$statement = "select type, sequence, hit_count from list where userid = '$userid' and type = '$listtype' and last_hit < '$timestamp'";
sub CloseDB () {
$DB->disconnect;
+
+ return;
} # CloseDB
sub CountMsg ($) {
my $count = count ('email', $condition);
# Just return if there's nothing to delete
- return $count if ($count eq 0);
+ return $count if ($count == 0);
my $statement = 'delete from email where ' . $condition;
my $count = count ('list', "userid = '$userid' and type = '$type' and sequence = '$sequence'");
# Just return if there's nothing to delete
- return $count if ($count eq 0);
+ return $count if ($count == 0);
my $statement = "delete from list where userid = '$userid' and type = '$type' and sequence = '$sequence'";
my $count = count ('log', $condition);
# Just return if there's nothing to delete
- return $count if ($count eq 0);
+ return $count if ($count == 0);
my $statement = 'delete from log where ' . $condition;
my $userid = pop @email;
return $userid, $sender, $subject, $timestamp, $message;
} else {
- return undef;
+ return;
} # if
} # GetEmail
my $userid = pop @list;
return $userid, $type, $pattern, $domain, $comment, $sequence, $hit_count, $last_hit;
} else {
- return undef;
+ return;
} # if
} # GetList
my $userid = pop @log;
return $userid, $timestamp, $sender, $type, $message;
} else {
- return undef;
+ return;
} # if
} # GetLog
my $userid = pop @user;
return ($userid, $name, $email, $password);
} else {
- return undef;
+ return;
} # if
} # GetUser
if (!$DB || $DB eq '') {
#$dbserver='localhost';
$DB = DBI->connect("DBI:$dbdriver:$dbname:$dbserver", $username, $password, {PrintError => 0})
- or die "Couldn't connect to $dbname database as $username\n" . $DBI::errstr;
+ or croak "Couldn't connect to $dbname database as $username\n" . $DBI::errstr;
} # if
return $DB;
$sth->execute
or DBError 'OptimizeDB: Unable to execute statement', $statement;
+
+ return;
} # OptimizeDB
sub ResequenceList ($$) {
$dateCond = "and timestamp > '$sod' and timestamp < '$eod'";
} # if
- my $statement = <<END;
+ my $statement = <<"END";
select
sender,
timestamp
sub ReturnMessages ($$) {
my ($userid, $sender) = @_;
- # Note, the left(timestamp,16) chops off the seconds and the group
- # by effectively squashes two emails received in the same minute to
- # just one. We get a lot of double emails within the same minute. I
- # think it's a result of the mailer configuration and it attempting
- # to resend the message, not that it's the spammer sending just two
- # emails in under a minute then going away. This will mean we will
- # see fewer emails listed (essentially dups within one minute are
- # squashed) yet they still will count towards the number of hits
- # before we autonullist. We should squash these upon receipt, not
- # upon report. Maybe latter...
- my $statement = <<END;
+ my $statement = <<"END";
select
subject,
- left(timestamp,16)
+ timestamp
from
email
where
userid = '$userid' and
sender = '$sender'
group by
- left(timestamp,16) desc
+ timestamp desc
END
my $sth = $DB->prepare ($statement)
my $eod = $date . ' 23:59:59';
if ($type eq 'returned') {
- $statement = <<END;
+ $statement = <<"END";
select
log.sender
from
$start_at, $nbr_emails
END
} else {
- $statement = <<END;
+ $statement = <<"END";
select
sender
from
} # if
} else {
if ($type eq 'returned') {
- $statement = <<END;
+ $statement = <<"END";
select
log.sender
from
$start_at, $nbr_emails
END
} else {
- $statement = <<END;
+ $statement = <<"END";
select
sender
from
#} else {
# TODO: Check if numeric
} # fi
-
+
my $statement =
'update list set ' .
"pattern = $pattern, domain = $domain, comment = $comment, hit_count = $hit_count " .
while (my @row = $sth->fetchrow_array) {
last if !@row;
- my $data = pop @row;
- my $timestamp = pop @row;
- my $subject = pop @row;
- my $sender = pop @row;
- my $user = pop @row;
+ my $data = pop @row;
+ my $timestamp = pop @row;
+ my $subject = pop @row;
+ my $sender = pop @row;
+ my $user = pop @row;
my $msg_space =
- length ($userid) +
- length ($sender) +
- length ($subject) +
- length ($timestamp) +
+ length ($userid) +
+ length ($sender) +
+ length ($subject) +
+ length ($timestamp) +
length ($data);
- $total_space += $msg_space;
- $msg_space{$sender} += $msg_space;
+ $total_space += $msg_space;
+ $msg_space{$sender} += $msg_space;
} # while
$sth->finish;
projects / clearscm.git / blobdiff