X-Git-Url: https://defaria.com/gitweb/?a=blobdiff_plain;f=maps%2Fbin%2FMAPSDB.pm;h=5836e2a8dafbc1586810b3072c18332bf0317bc9;hb=9f51fc95d361092058a17611614f31318a3fad0c;hp=74b1302f6a9bbc726e9480e3363a4468439721da;hpb=020a4a5ea2be725b155cae3a2cadc9aba3911b9b;p=clearscm.git

diff --git a/maps/bin/MAPSDB.pm b/maps/bin/MAPSDB.pm
index 74b1302..5836e2a 100644
--- a/maps/bin/MAPSDB.pm
+++ b/maps/bin/MAPSDB.pm
@@ -2,7 +2,7 @@
 #################################################################################
 #
 # 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
@@ -17,6 +17,7 @@ package MAPSDB;
 use strict;
 use vars qw (@ISA @EXPORT);
 use DBI;
+use Carp;
 
 use MAPSUtil;
 
@@ -95,11 +96,13 @@ sub AddEmail ($$$) {
 
   $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;
@@ -123,16 +126,18 @@ sub AddList ($$$;$$) {
   } # 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 ($$$) {
@@ -152,6 +157,8 @@ sub AddLog ($$$) {
 
   $DB->do ($statement)
     or DBError 'AddLog: Unable to do statement', $statement;
+
+  return;
 } # AddLog
 
 sub AddUser ($$$$) {
@@ -195,15 +202,19 @@ sub RecordHit ($$$) {
 
   $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'";
 
@@ -216,18 +227,18 @@ sub CheckOnList ($$) {
   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
@@ -255,7 +266,7 @@ sub CheckOnList ($$) {
       $rule  .= " - $comment" if $comment and $comment ne '';
       $status = 1;
 
-      RecordHit $listtype, $sequence, ++$hit_count;
+      RecordHit $listtype, $sequence, ++$hit_count if $update;
 
       last;
     } # if
@@ -263,7 +274,7 @@ sub CheckOnList ($$) {
 
   $sth->finish;
 
-  return ($status, $rule);
+  return ($status, $rule, $sequence, $hit_count);
 } # CheckOnList
 
 sub CleanEmail ($) {
@@ -296,7 +307,7 @@ 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'";
@@ -342,7 +353,7 @@ sub CleanLog  ($) {
   } # 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'";
@@ -386,7 +397,7 @@ sub CleanList ($;$) {
   $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'";
@@ -404,16 +415,16 @@ sub CleanList ($;$) {
   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
@@ -430,14 +441,14 @@ sub CleanList ($;$) {
       # $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
 
@@ -448,11 +459,13 @@ sub CleanList ($;$) {
 
 sub CloseDB () {
   $DB->disconnect;
+
+  return;
 } # CloseDB
 
 sub CountMsg ($) {
   my ($sender) = @_;
-  
+
   return count ('email', "userid = '$userid' and sender like '%$sender%'");
 } # CountMsg
 
@@ -504,7 +517,7 @@ sub DeleteEmail ($) {
   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;
 
@@ -521,7 +534,7 @@ sub DeleteList ($$) {
   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'";
 
@@ -547,7 +560,7 @@ sub DeleteLog ($) {
   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;
 
@@ -676,7 +689,7 @@ sub GetEmail ($) {
     my $userid    = pop @email;
     return $userid, $sender, $subject, $timestamp, $message;
   } else {
-    return undef;
+    return;
   } # if
 } # GetEmail
 
@@ -686,17 +699,17 @@ sub GetList ($) {
   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
 
@@ -713,7 +726,7 @@ sub GetLog ($) {
     my $userid    = pop @log;
     return $userid, $timestamp, $sender, $type, $message;
   } else {
-    return undef;
+    return;
   } # if
 } # GetLog
 
@@ -731,13 +744,13 @@ sub GetUser ($) {
   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
 
@@ -778,8 +791,8 @@ sub GetUserOptions ($) {
   %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
@@ -814,12 +827,12 @@ sub OpenDB ($$) {
 
   my $dbname   = 'MAPS';
   my $dbdriver = 'mysql';
-  my $dbserver = $ENV{MAPS_SERVER} ? $ENV{MAPS_SERVER} : 'jupiter';
+  my $dbserver = $ENV{MAPS_SERVER} || 'localhost';
 
   if (!$DB || $DB eq '') {
-    $dbserver='localhost';
+    #$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;
@@ -853,6 +866,8 @@ sub OptimizeDB () {
 
   $sth->execute
     or DBError 'OptimizeDB: Unable to execute statement', $statement;
+  
+  return;
 } # OptimizeDB
 
 sub ResequenceList ($$) {
@@ -883,10 +898,10 @@ 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++;
@@ -921,7 +936,7 @@ sub ReturnSenders ($$$;$$) {
     $dateCond = "and timestamp > '$sod' and timestamp < '$eod'";
   } # if
 
-  my $statement = <<END;
+  my $statement = <<"END";
 select
   sender,
   timestamp
@@ -983,27 +998,17 @@ END
 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)
@@ -1038,7 +1043,7 @@ sub ReturnEmails ($$$;$$) {
     my $eod = $date . ' 23:59:59';
 
     if ($type eq 'returned') {
-      $statement = <<END;
+      $statement = <<"END";
 select
   log.sender
 from
@@ -1056,7 +1061,7 @@ limit
   $start_at, $nbr_emails
 END
     } else {
-      $statement = <<END;
+      $statement = <<"END";
 select
   sender
 from
@@ -1074,7 +1079,7 @@ END
     } # if
   } else {
     if ($type eq 'returned') {
-      $statement = <<END;
+      $statement = <<"END";
 select
   log.sender
 from
@@ -1092,7 +1097,7 @@ limit
   $start_at, $nbr_emails
 END
     } else {
-      $statement = <<END;
+      $statement = <<"END";
 select
   sender
 from
@@ -1138,13 +1143,13 @@ END
       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;
@@ -1162,7 +1167,7 @@ END
       push @emails, [$earliestDate, [$sender, @messages]];
     } else {
       push @emails, [$earliestDate, [$sender, @messages]]
-	if @messages > 0;
+        if @messages > 0;
     } # unless
   } # while
 
@@ -1180,11 +1185,11 @@ sub ReturnList ($$$) {
   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
 
@@ -1202,14 +1207,14 @@ sub ReturnList ($$$) {
 
     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
 
@@ -1219,7 +1224,7 @@ sub ReturnList ($$$) {
 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)
@@ -1241,8 +1246,8 @@ sub ReturnListEntry ($$) {
   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';
@@ -1261,10 +1266,16 @@ sub UpdateList ($$$$$$) {
   } 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)
@@ -1319,7 +1330,7 @@ sub SetContext ($) {
 sub Space ($) {
   my ($userid) = @_;
 
-  my $total_space	= 0;
+  my $total_space        = 0;
   my %msg_space;
 
   my $statement = "select * from email where userid = '$userid'";
@@ -1331,21 +1342,21 @@ sub Space ($) {
 
   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;