#################################################################################
#
# 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) = @_;
- my $status = 0;
- my $rule;
+ $update //= 1;
+
+ my $status = 0;
+ my ($rule, $sequence, $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);
- my $sequence = pop (@row);
- my $comment = pop (@row);
- my $domain = pop (@row);
- my $pattern = pop (@row);
+ $hit_count = pop (@row);
+ $sequence = pop (@row);
+ my $comment = pop (@row);
+ my $domain = pop (@row);
+ my $pattern = pop (@row);
my $email_on_file;
unless ($domain) {
$email_on_file = $pattern;
} else {
unless ($pattern) {
- $email_on_file = '@' . $domain;
+ $email_on_file = '@' . $domain;
} else {
$email_on_file = $pattern . '@' . $domain;
} # if
$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, $sequence, $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'";
while (my @row = $sth->fetchrow_array) {
last if !@row;
- my $hit_count = pop (@row);
- my $sequence = pop (@row);
- my $listtype = pop (@row);
+ my $hit_count = pop (@row);
+ my $sequence = pop (@row);
+ my $listtype = pop (@row);
if ($hit_count == 0) {
$count++;
$statement = "delete from list where userid='$userid' and type='$listtype' and sequence=$sequence";
$DB->do ($statement)
- or DBError 'CleanList: Unable to execute statement', $statement;
+ or DBError 'CleanList: Unable to execute statement', $statement;
} else {
# Age entry: Sometimes entries are initially very popular and
# the $hit_count gets very high quickly. Then the domain is
# $hit_count's too quickly, therefore once their numbers drop to
# < 30 we revert to the old method of subtracting 1.
if ($hit_count < 30) {
- $hit_count--;
+ $hit_count--;
} else {
- $hit_count = $hit_count / 2;
+ $hit_count = $hit_count / 2;
} # if
$statement = "update list set hit_count=$hit_count where userid='$userid' and type='$listtype' and sequence=$sequence;";
$DB->do ($statement)
- or DBError 'CleanList: Unable to execute statement', $statement;
+ or DBError 'CleanList: Unable to execute statement', $statement;
} # if
} # while
sub CloseDB () {
$DB->disconnect;
+
+ return;
} # CloseDB
sub CountMsg ($) {
my ($sender) = @_;
-
+
return count ('email', "userid = '$userid' and sender like '%$sender%'");
} # 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 @list;
if (@list = $sth->fetchrow_array) {
- my $last_hit = pop @list;
- my $hit_count = pop @list;
- my $sequence = pop @list;
- my $comment = pop @list;
- my $domain = pop @list;
- my $pattern = pop @list;
- my $type = pop @list;
- my $userid = pop @list;
+ my $last_hit = pop @list;
+ my $hit_count = pop @list;
+ my $sequence = pop @list;
+ my $comment = pop @list;
+ my $domain = pop @list;
+ my $pattern = pop @list;
+ my $type = pop @list;
+ 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 @user;
if (@user = $sth->fetchrow_array) {
- my $password = pop @user;
- my $email = pop @user;
- my $name = pop @user;
- my $userid = pop @user;
+ my $password = pop @user;
+ my $email = pop @user;
+ my $name = pop @user;
+ my $userid = pop @user;
return ($userid, $name, $email, $password);
} else {
- return undef;
+ return;
} # if
} # GetUser
%useropts = ();
while (@useropts = $sth->fetchrow_array) {
- my $value = pop @useropts;
- my $name = pop @useropts;
+ my $value = pop @useropts;
+ my $name = pop @useropts;
pop @useropts;
$useropts{$name} = $value;
} # while
my $dbname = 'MAPS';
my $dbdriver = 'mysql';
- my $dbserver = $ENV{MAPS_SERVER} || 'jupiter';
+ my $dbserver = $ENV{MAPS_SERVER} || 'localhost';
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 ($$) {
if ($old_sequence != $sequence) {
my $update_statement = "update list set sequence = $sequence " .
- "where userid = '$userid' and " .
- "type = '$type' and sequence = $old_sequence";
+ "where userid = '$userid' and " .
+ "type = '$type' and sequence = $old_sequence";
$DB->do ($update_statement)
- or DBError 'ResequenceList: Unable to do statement', $statement;
+ or DBError 'ResequenceList: Unable to do statement', $statement;
} # if
$sequence++;
$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
my $date = pop @row;
if ($earliestDate) {
- my $earliestDateShort = substr $earliestDate, 0, 10;
+ my $earliestDateShort = substr $earliestDate, 0, 10;
my $dateShort = substr $date, 0, 10;
if ($earliestDateShort eq $dateShort and
- $earliestDate > $date) {
+ $earliestDate > $date) {
$earliestDate = $date
- if $earliestDateShort eq $dateShort;
+ if $earliestDateShort eq $dateShort;
} # if
} else {
$earliestDate = $date;
push @emails, [$earliestDate, [$sender, @messages]];
} else {
push @emails, [$earliestDate, [$sender, @messages]]
- if @messages > 0;
+ if @messages > 0;
} # unless
} # while
my $statement;
if ($start_at) {
- $statement = "select * from list where userid = '$userid' " .
- "and type = '$type' order by sequence " .
- "limit $start_at, $lines";
+ $statement = "select * from list where userid = '$userid' " .
+ "and type = '$type' order by sequence " .
+ "limit $start_at, $lines";
} else {
- $statement = "select * from list where userid = '$userid' " .
+ $statement = "select * from list where userid = '$userid' " .
"and type = '$type' order by sequence";
} # if
my %list;
- $list {last_hit} = pop @row;
- $list {hit_count} = pop @row;
- $list {sequence} = pop @row;
- $list {comment} = pop @row;
- $list {domain} = pop @row;
- $list {pattern} = pop @row;
- $list {type} = pop @row;
- $list {userid} = pop @row;
+ $list {last_hit} = pop @row;
+ $list {hit_count} = pop @row;
+ $list {sequence} = pop @row;
+ $list {comment} = pop @row;
+ $list {domain} = pop @row;
+ $list {pattern} = pop @row;
+ $list {type} = pop @row;
+ $list {userid} = pop @row;
push @list, \%list;
} # for
sub ReturnListEntry ($$) {
my ($type, $sequence) = @_;
- my $statement = "select * from list where userid = '$userid' " .
+ my $statement = "select * from list where userid = '$userid' " .
"and type = '$type' and sequence = '$sequence'";
my $sth = $DB->prepare ($statement)
return %list;
} # ReturnListEntry
-sub UpdateList ($$$$$$) {
- my ($userid, $type, $pattern, $domain, $comment, $sequence) = @_;
+sub UpdateList ($$$$$$$) {
+ my ($userid, $type, $pattern, $domain, $comment, $hit_count, $sequence) = @_;
if (!$pattern || $pattern eq '') {
$pattern = 'NULL';
} else {
$comment = "'" . quotemeta ($comment) . "'";
} # if
-
+
+ if (!$hit_count || $hit_count eq '') {
+ $hit_count = 0;
+ #} else {
+ # TODO: Check if numeric
+ } # fi
+
my $statement =
'update list set ' .
- "pattern = $pattern, domain = $domain, comment = $comment " .
+ "pattern = $pattern, domain = $domain, comment = $comment, hit_count = $hit_count " .
"where userid = '$userid' and type = '$type' and sequence = $sequence";
$DB->do ($statement)
sub Space ($) {
my ($userid) = @_;
- my $total_space = 0;
+ my $total_space = 0;
my %msg_space;
my $statement = "select * from email where userid = '$userid'";
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