Attachment #4410 for bug #6037

View | Details | Raw Unified | Return to bug 6037
Collapse All | Expand All




  my $too_old = $vars[10] - $newdelta; # tooold = newest - delta

  # if token atime > newest, reset to newest ...
  my $sql = "UPDATE $$self{_token_table} SET atime = ?
              WHERE id  = ?
                AND atime > ?";


  }

  # Check to make sure the expire won't remove too many tokens
  $sql = "SELECT count(token) FROM $$self{_token_table}
           WHERE id = ?
             AND atime < ?";


  }
  else {
    # Do the expire
    $sql = "DELETE from $$self{_token_table}
             WHERE id = ?
               AND atime < ?";


                                last_atime_delta = ?,
                                last_expire_reduce = ?,
                                oldest_token_age = (SELECT min(atime)
                                                      FROM $$self{_token_table}
                                                     WHERE id = ?)
				WHERE id = ?";



  # shortcut, will only update atime for the token if the atime is less than
  # what we are updating to
  my $sql = "UPDATE $$self{_token_table}
                SET atime = ?
              WHERE id = ?
                AND token = ?


  return 1 unless (scalar(@{$tokens}));

  my $sql = "UPDATE $$self{_token_table} SET atime = ? WHERE id = ? AND token IN (";

  my @bindings = ($atime, $self->{_userid});
  foreach my $token (@{$tokens}) {

  # cleanup was needed, go ahead and clear the cleanup flag
  $self->{needs_cleanup} = 0;

  my $sql = "DELETE from $$self{_token_table}
              WHERE id = ?
                AND spam_count <= 0
                AND ham_count <= 0";

    return 0;
  }

  $rows = $self->{_dbh}->do("DELETE FROM $$self{_token_table} WHERE id = ?",
			    undef,
			    $self->{_userid});
  unless (defined($rows)) {

    # counts may have both reached 0
    $self->{needs_cleanup} = 1;

    my $sql = "UPDATE $$self{_token_table} SET spam_count = GREATEST(spam_count + ?, 0),
                                      ham_count = GREATEST(ham_count + ?, 0)
                WHERE id = ?
                  AND token = ?";

    }
  }
  else {
    my $sql = "INSERT INTO $$self{_token_table}
               (id, token, spam_count, ham_count, atime)
               VALUES (?,?,?,?,?)
               ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + ?, 0),

    # counts may have both reached 0
    $self->{needs_cleanup} = 1;

    my $sql = "UPDATE $$self{_token_table} SET spam_count = GREATEST(spam_count + ?, 0),
                                      ham_count = GREATEST(ham_count + ?, 0)
                WHERE id = ?
                  AND token = ?";

    }
  }
  else {
    my $sql = "INSERT INTO $$self{_token_table}
               (id, token, spam_count, ham_count, atime)
               VALUES (?,?,?,?,?)
               ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + ?, 0),




  my $too_old = $vars[10] - $newdelta; # tooold = newest - delta

  # if token atime > newest, reset to newest ...
  my $sql = "UPDATE $$self{_token_table} SET atime = ?
              WHERE id  = ?
                AND atime > ?";


  }

  # Check to make sure the expire won't remove too many tokens
  $sql = "SELECT count(token) FROM $$self{_token_table}
           WHERE id = ?
             AND atime < ?";


  }
  else {
    # Do the expire
    $sql = "DELETE from $$self{_token_table}
             WHERE id = ?
               AND atime < ?";


                                last_atime_delta = ?,
                                last_expire_reduce = ?,
                                oldest_token_age = (SELECT min(atime)
                                                      FROM $$self{_token_table}
                                                     WHERE id = ?)
				WHERE id = ?";


  return (0,0,0) unless (defined($self->{_dbh}));

  my $sql = "SELECT spam_count, ham_count, atime
               FROM $$self{_token_table}
              WHERE id = ?
                AND token = ?";


  my $bunch_end;

  my $multi_sql = "SELECT token, spam_count, ham_count, atime
                     FROM $$self{_token_table}
                    WHERE id = ?
                      AND token IN ";



  # shortcut, will only update atime for the token if the atime is less than
  # what we are updating to
  my $sql = "UPDATE $$self{_token_table}
                SET atime = ?
              WHERE id = ?
                AND token = ?


  return 1 unless (scalar(@{$tokens}));

  my $sql = "UPDATE $$self{_token_table} SET atime = ? WHERE id = ? AND token IN (";

  my @bindings;
  foreach my $token (sort @{$tokens}) {

  # cleanup was needed, go ahead and clear the cleanup flag
  $self->{needs_cleanup} = 0;

  my $sql = "DELETE from $$self{_token_table}
              WHERE id = ?
                AND spam_count <= 0
                AND ham_count <= 0";

    return 0;
  }

  $rows = $self->{_dbh}->do("DELETE FROM $$self{_token_table} WHERE id = ?",
			    undef,
			    $self->{_userid});
  unless (defined($rows)) {




use Mail::SpamAssassin::BayesStore;
use Mail::SpamAssassin::Logger;
use Digest::SHA1 qw(sha1);
use String::CRC32;

use vars qw( @ISA );


  return %delta unless (defined($self->{_dbh}));

  my $sql = "SELECT count(*)
               FROM $$self{_token_table}
              WHERE id = ?
                AND atime < ?";


  my $too_old = $vars[10] - $newdelta; # tooold = newest - delta

  # if token atime > newest, reset to newest ...
  my $sql = "UPDATE $$self{_token_table} SET atime = ?
              WHERE id  = ?
                AND atime > ?";


  }

  # Check to make sure the expire won't remove too many tokens
  $sql = "SELECT count(token) FROM $$self{_token_table} 
           WHERE id = ?
             AND atime < ?";


  }
  else {
    # Do the expire
    $sql = "DELETE from $$self{_token_table} 
             WHERE id = ?
               AND atime < ?";


  my $token_select = $self->_token_select_string();

  my $sql = "SELECT $token_select, spam_count, ham_count, atime
               FROM $$self{_token_table} 
              WHERE id = ?
                AND (spam_count > 0 OR ham_count > 0)";


  return (0,0,0) unless (defined($self->{_dbh}));

  my $sql = "SELECT spam_count, ham_count, atime
               FROM $$self{_token_table} 
              WHERE id = ?
                AND token = ?";


  my $token_select = $self->_token_select_string();

  my $multi_sql = "SELECT $token_select, spam_count, ham_count, atime
                     FROM $$self{_token_table}
                    WHERE id = ?
                      AND token IN ";



  # shortcut, will only update atime for the token if the atime is less than
  # what we are updating to
  my $sql = "UPDATE $$self{_token_table} 
                SET atime = ?
              WHERE id = ?
                AND token = ?


  return 1 unless (scalar(@{$tokens}));

  my $sql = "UPDATE $$self{_token_table} SET atime = ? WHERE id = ? AND token IN (";

  my @bindings = ($atime, $self->{_userid});
  foreach my $token (@{$tokens}) {

  # cleanup was needed, go ahead and clear the cleanup flag
  $self->{needs_cleanup} = 0;

  my $sql = "DELETE from $$self{_token_table}
              WHERE id = ?
                AND spam_count = 0
                AND ham_count = 0";

    return 0;
  }

  $rows = $self->{_dbh}->do("DELETE FROM $$self{_token_table} WHERE id = ?",
			    undef,
			    $self->{_userid});
  unless (defined($rows)) {

  my $token_select = $self->_token_select_string();

  my $token_sql = "SELECT spam_count, ham_count, atime, $token_select
                     FROM $$self{_token_table}
                    WHERE id = ?
                      AND (spam_count > 0 OR ham_count > 0)";


    }
  }

  my $sqlselect = "SELECT id, token_table FROM bayes_vars WHERE username = ?";

  my $sthselect = $self->{_dbh}->prepare_cached($sqlselect);


    return 0;
  }

  my ($id, $token_table) = $sthselect->fetchrow_array();

  if ($id) {
    $self->{_userid} = $id;
    $self->{_token_table} = $token_table;
    dbg("bayes: Using userid: ".$self->{_userid}.", token table: ".$self->{_token_table});
    $sthselect->finish();
    return 1;
  }

  $self->{_token_table} = "bayes_token";
  # Do not create an entry for this user unless we were specifically asked to
  return 0 unless ($create_entry_p);

  # For now let the database setup the other variables as defaults
  my $sqlinsert = "INSERT INTO bayes_vars (username, token_table) VALUES (?, ?)";
  if ($self->{bayes}->{conf}->{bayes_sql_token_table_count}
    && $self->{bayes}->{conf}->{bayes_sql_token_table_count} > 1) {
      $self->{_token_table} = "bayes_token_"
        . (crc32($self->{_username}) % $self->{bayes}->{conf}->{token_table_count});
  }

  my $rows = $self->{_dbh}->do($sqlinsert,
			       undef,
			       $self->{_username},
			       $self->{_token_table});
  unless (defined($rows)) {
    dbg("bayes: _initialize_db: SQL error: ".$self->{_dbh}->errstr());
    return 0;

    # if we are unable to find an entry.
    return 1 if ($spam_count < 0 || $ham_count < 0);

    my $sql = "INSERT INTO $$self{_token_table}
               (id, token, spam_count, ham_count, atime)
               VALUES (?,?,?,?,?)";


      my $sql;
      my @args;
      if ($update_atime_p) {
	$sql = "UPDATE $$self{_token_table}
                   SET spam_count = spam_count + ?,
                       atime = ?
                 WHERE id = ?

	$updated_atime_p = 1; # note the fact that we did do it
      }
      else {
	$sql = "UPDATE $$self{_token_table}
                   SET spam_count = spam_count + ?
                 WHERE id = ?
                   AND token = ?

      my $sql;
      my @args;
      if ($update_atime_p && !$updated_atime_p) {
	$sql = "UPDATE $$self{_token_table}
                   SET ham_count = ham_count + ?,
                       atime = ?
                 WHERE id = ?

	$updated_atime_p = 1; # note the fact that we did do it
      }
      else {
	$sql = "UPDATE $$self{_token_table}
                   SET ham_count = ham_count + ?
                 WHERE id = ?
                   AND token = ?

  my $atime_inserted_p = 0;
  my $new_tokens = 0;

  my $insertsql = "INSERT INTO $$self{_token_table}
                   (id, token, spam_count, ham_count, atime)
                   VALUES (?,?,?,?,?)";


	my $sql;
	my @args;
	if ($update_atime_p) {
	  $sql = "UPDATE $$self{_token_table}
                     SET spam_count = spam_count + ?,
                         atime = ?
                   WHERE id = ?

	  $atime_updated_p = 1;
	}
	else {
	  $sql = "UPDATE $$self{_token_table}
                     SET spam_count = spam_count + ?
                   WHERE id = ?
                     AND token = ?

	my @args;
	# if $spam_count then we already updated the atime
	if ($update_atime_p && !$spam_count) { 
	  $sql = "UPDATE $$self{_token_table}
                     SET ham_count = ham_count + ?,
                         atime = ?
                   WHERE id = ?

	  $atime_updated_p = 1;
	}
	else {
	  $sql = "UPDATE $$self{_token_table}
                     SET ham_count = ham_count + ?
                   WHERE id = ?
                     AND token = ?


  return 0 unless (defined($self->{_dbh}));

  my $sql = "SELECT min(atime) FROM $$self{_token_table}
              WHERE id = ?";

  my $sth = $self->{_dbh}->prepare_cached($sql);

  return 0 unless (defined($self->{_dbh}));

  my $sql = "SELECT count(*)
               FROM $$self{_token_table}
              WHERE id = ?
                AND spam_count + ham_count = 1";


  return 0 unless (defined($self->{_dbh}));

  my $sql = "SELECT count(*)
               FROM $$self{_token_table}
              WHERE id = ?
                AND (spam_count >= 0 AND spam_count < 8)
                AND (ham_count >= 0 AND ham_count < 8)




    type => $CONF_TYPE_BOOL
  });

=item bayes_sql_token_table_count

Used by BayesStore::SQL storage implementation.

With this option you can spread the token information over several tables if
the single table will otherwise get too large.

After settings this to a value > 1 you must create the tables
bayes_token_0 through bayes_token_[number - 1] by yourself! They
have the same structure as the original bayes_token table.

=cut

  push (@cmds, {
    setting => 'bayes_sql_token_table_count',
    is_admin => 1,
    default => 1,
    type => $CONF_TYPE_NUMERIC
  });


=item user_scores_dsn DBI:databasetype:databasename:hostname:port

If you load user scores from an SQL database, this will set the DSN

Lines 66-71 Link Here

(-)Mail-SpamAssassin-3.2.4.orig/sql/README.bayes (+9 lines)
66	share bayesian filter data. You can also use this config option to	66	share bayesian filter data. You can also use this config option to
67	trick sa-learn to learn data as a specific user.	67	trick sa-learn to learn data as a specific user.
68		68
		69	If your token table will get really large you may spread the bayes
		70	tokens over several tables. You can set this in the config file with
		71
		72	bayes_sql_token_table_count number
		73
		74	After settings this to a value > 1 you must create the tables
		75	bayes_token_0 through bayes_token_[number - 1] by yourself! They
		76	have the same structure as the original bayes_token table.
		77
69		78
70	Requirements	79	Requirements
71	------------	80	------------

Lines 42-47 Link Here

(-)Mail-SpamAssassin-3.2.4.orig/sql/bayes_mysql.sql (+1 lines)
42	last_expire_reduce int(11) NOT NULL default '0',	42	last_expire_reduce int(11) NOT NULL default '0',
43	oldest_token_age int(11) NOT NULL default '2147483647',	43	oldest_token_age int(11) NOT NULL default '2147483647',
44	newest_token_age int(11) NOT NULL default '0',	44	newest_token_age int(11) NOT NULL default '0',
		45	token_table varchar(20) NOT NULL default 'bayes_token',
45	PRIMARY KEY (id),	46	PRIMARY KEY (id),
46	UNIQUE bayes_vars_idx1 (username)	47	UNIQUE bayes_vars_idx1 (username)
47	) TYPE=MyISAM;	48	) TYPE=MyISAM;




  last_expire_reduce integer NOT NULL default '0',
  oldest_token_age integer NOT NULL default '2147483647',
  newest_token_age integer NOT NULL default '0',
  token_table varchar(20) NOT NULL default 'bayes_token',
  PRIMARY KEY  (id)
) WITHOUT OIDS;


                                      inspam_count INTEGER,
                                      inham_count INTEGER,
                                      inatime INTEGER)
RETURNS VOID AS $$ 
DECLARE
  _token BYTEA;
  new_tokens INTEGER := 0;
  tt VARCHAR(20);
BEGIN
  SELECT token_table INTO tt FROM bayes_vars WHERE (id = inuserid);

  for i in array_lower(intokenary, 1) .. array_upper(intokenary, 1)
  LOOP
    _token := intokenary[i];
    EXECUTE 'UPDATE ' || tt || '
               SET spam_count = greatest_int(spam_count + ' || inspam_count || ', 0),
                   ham_count = greatest_int(ham_count + ' || inham_count || ', 0),
                   atime = greatest_int(atime, ' || inatime || ')
               WHERE id = inuserid 
                   AND token = ' || quote_literal(token) || ';';
    IF NOT FOUND THEN 
      -- we do not insert negative counts, just return true
      IF NOT (inspam_count < 0 OR inham_count < 0) THEN
        EXECUTE 'INSERT INTO ' || tt | ' (id, token, spam_count, ham_count, atime) 
                 VALUES (' || inuserid || ', ' || quote_literal(_token) || ', ' 
		           || inspam_count  || ', ' || inham_count || ', ' || inatime || ');';
        IF FOUND THEN
          new_tokens := new_tokens + 1;
        END IF;

  END IF;
  RETURN;
END; 
$$ LANGUAGE 'plpgsql'; 

Return to bug 6037

Lines 75-81 Link Here

(-)Mail-SpamAssassin-3.2.4.orig/lib/Mail/SpamAssassin/BayesStore/MySQL.pm (-12 / +12 lines)
75	my $too_old = $vars[10] - $newdelta; # tooold = newest - delta	75	my $too_old = $vars[10] - $newdelta; # tooold = newest - delta
76		76
77	# if token atime > newest, reset to newest ...	77	# if token atime > newest, reset to newest ...
78	my $sql = "UPDATE bayes_token SET atime = ?	78	my $sql = "UPDATE $$self{_token_table} SET atime = ?
79	WHERE id = ?	79	WHERE id = ?
80	AND atime > ?";	80	AND atime > ?";
81		81
Lines 89-95 Link Here
89	}	89	}
90		90
91	# Check to make sure the expire won't remove too many tokens	91	# Check to make sure the expire won't remove too many tokens
92	$sql = "SELECT count(token) FROM bayes_token	92	$sql = "SELECT count(token) FROM $$self{_token_table}
93	WHERE id = ?	93	WHERE id = ?
94	AND atime < ?";	94	AND atime < ?";
95		95
Lines 124-130 Link Here
124	}	124	}
125	else {	125	else {
126	# Do the expire	126	# Do the expire
127	$sql = "DELETE from bayes_token	127	$sql = "DELETE from $$self{_token_table}
128	WHERE id = ?	128	WHERE id = ?
129	AND atime < ?";	129	AND atime < ?";
130		130
Lines 146-152 Link Here
146	last_atime_delta = ?,	146	last_atime_delta = ?,
147	last_expire_reduce = ?,	147	last_expire_reduce = ?,
148	oldest_token_age = (SELECT min(atime)	148	oldest_token_age = (SELECT min(atime)
149	FROM bayes_token	149	FROM $$self{_token_table}
150	WHERE id = ?)	150	WHERE id = ?)
151	WHERE id = ?";	151	WHERE id = ?";
152		152
Lines 415-421 Link Here
415		415
416	# shortcut, will only update atime for the token if the atime is less than	416	# shortcut, will only update atime for the token if the atime is less than
417	# what we are updating to	417	# what we are updating to
418	my $sql = "UPDATE bayes_token	418	my $sql = "UPDATE $$self{_token_table}
419	SET atime = ?	419	SET atime = ?
420	WHERE id = ?	420	WHERE id = ?
421	AND token = ?	421	AND token = ?
Lines 477-483 Link Here
477		477
478	return 1 unless (scalar(@{$tokens}));	478	return 1 unless (scalar(@{$tokens}));
479		479
480	my $sql = "UPDATE bayes_token SET atime = ? WHERE id = ? AND token IN (";	480	my $sql = "UPDATE $$self{_token_table} SET atime = ? WHERE id = ? AND token IN (";
481		481
482	my @bindings = ($atime, $self->{_userid});	482	my @bindings = ($atime, $self->{_userid});
483	foreach my $token (@{$tokens}) {	483	foreach my $token (@{$tokens}) {
Lines 538-544 Link Here
538	# cleanup was needed, go ahead and clear the cleanup flag	538	# cleanup was needed, go ahead and clear the cleanup flag
539	$self->{needs_cleanup} = 0;	539	$self->{needs_cleanup} = 0;
540		540
541	my $sql = "DELETE from bayes_token	541	my $sql = "DELETE from $$self{_token_table}
542	WHERE id = ?	542	WHERE id = ?
543	AND spam_count <= 0	543	AND spam_count <= 0
544	AND ham_count <= 0";	544	AND ham_count <= 0";
Lines 616-622 Link Here
616	return 0;	616	return 0;
617	}	617	}
618		618
619	$rows = $self->{_dbh}->do("DELETE FROM bayes_token WHERE id = ?",	619	$rows = $self->{_dbh}->do("DELETE FROM $$self{_token_table} WHERE id = ?",
620	undef,	620	undef,
621	$self->{_userid});	621	$self->{_userid});
622	unless (defined($rows)) {	622	unless (defined($rows)) {
Lines 785-791 Link Here
785	# counts may have both reached 0	785	# counts may have both reached 0
786	$self->{needs_cleanup} = 1;	786	$self->{needs_cleanup} = 1;
787		787
788	my $sql = "UPDATE bayes_token SET spam_count = GREATEST(spam_count + ?, 0),	788	my $sql = "UPDATE $$self{_token_table} SET spam_count = GREATEST(spam_count + ?, 0),
789	ham_count = GREATEST(ham_count + ?, 0)	789	ham_count = GREATEST(ham_count + ?, 0)
790	WHERE id = ?	790	WHERE id = ?
791	AND token = ?";	791	AND token = ?";
Lines 810-816 Link Here
810	}	810	}
811	}	811	}
812	else {	812	else {
813	my $sql = "INSERT INTO bayes_token	813	my $sql = "INSERT INTO $$self{_token_table}
814	(id, token, spam_count, ham_count, atime)	814	(id, token, spam_count, ham_count, atime)
815	VALUES (?,?,?,?,?)	815	VALUES (?,?,?,?,?)
816	ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + ?, 0),	816	ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + ?, 0),
Lines 918-924 Link Here
918	# counts may have both reached 0	918	# counts may have both reached 0
919	$self->{needs_cleanup} = 1;	919	$self->{needs_cleanup} = 1;
920		920
921	my $sql = "UPDATE bayes_token SET spam_count = GREATEST(spam_count + ?, 0),	921	my $sql = "UPDATE $$self{_token_table} SET spam_count = GREATEST(spam_count + ?, 0),
922	ham_count = GREATEST(ham_count + ?, 0)	922	ham_count = GREATEST(ham_count + ?, 0)
923	WHERE id = ?	923	WHERE id = ?
924	AND token = ?";	924	AND token = ?";
Lines 952-958 Link Here
952	}	952	}
953	}	953	}
954	else {	954	else {
955	my $sql = "INSERT INTO bayes_token	955	my $sql = "INSERT INTO $$self{_token_table}
956	(id, token, spam_count, ham_count, atime)	956	(id, token, spam_count, ham_count, atime)
957	VALUES (?,?,?,?,?)	957	VALUES (?,?,?,?,?)
958	ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + ?, 0),	958	ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + ?, 0),

Lines 77-83 Link Here

(-)Mail-SpamAssassin-3.2.4.orig/lib/Mail/SpamAssassin/BayesStore/PgSQL.pm (-10 / +10 lines)
77	my $too_old = $vars[10] - $newdelta; # tooold = newest - delta	77	my $too_old = $vars[10] - $newdelta; # tooold = newest - delta
78		78
79	# if token atime > newest, reset to newest ...	79	# if token atime > newest, reset to newest ...
80	my $sql = "UPDATE bayes_token SET atime = ?	80	my $sql = "UPDATE $$self{_token_table} SET atime = ?
81	WHERE id = ?	81	WHERE id = ?
82	AND atime > ?";	82	AND atime > ?";
83		83
Lines 91-97 Link Here
91	}	91	}
92		92
93	# Check to make sure the expire won't remove too many tokens	93	# Check to make sure the expire won't remove too many tokens
94	$sql = "SELECT count(token) FROM bayes_token	94	$sql = "SELECT count(token) FROM $$self{_token_table}
95	WHERE id = ?	95	WHERE id = ?
96	AND atime < ?";	96	AND atime < ?";
97		97
Lines 126-132 Link Here
126	}	126	}
127	else {	127	else {
128	# Do the expire	128	# Do the expire
129	$sql = "DELETE from bayes_token	129	$sql = "DELETE from $$self{_token_table}
130	WHERE id = ?	130	WHERE id = ?
131	AND atime < ?";	131	AND atime < ?";
132		132
Lines 148-154 Link Here
148	last_atime_delta = ?,	148	last_atime_delta = ?,
149	last_expire_reduce = ?,	149	last_expire_reduce = ?,
150	oldest_token_age = (SELECT min(atime)	150	oldest_token_age = (SELECT min(atime)
151	FROM bayes_token	151	FROM $$self{_token_table}
152	WHERE id = ?)	152	WHERE id = ?)
153	WHERE id = ?";	153	WHERE id = ?";
154		154
Lines 358-364 Link Here
358	return (0,0,0) unless (defined($self->{_dbh}));	358	return (0,0,0) unless (defined($self->{_dbh}));
359		359
360	my $sql = "SELECT spam_count, ham_count, atime	360	my $sql = "SELECT spam_count, ham_count, atime
361	FROM bayes_token	361	FROM $$self{_token_table}
362	WHERE id = ?	362	WHERE id = ?
363	AND token = ?";	363	AND token = ?";
364		364
Lines 415-421 Link Here
415	my $bunch_end;	415	my $bunch_end;
416		416
417	my $multi_sql = "SELECT token, spam_count, ham_count, atime	417	my $multi_sql = "SELECT token, spam_count, ham_count, atime
418	FROM bayes_token	418	FROM $$self{_token_table}
419	WHERE id = ?	419	WHERE id = ?
420	AND token IN ";	420	AND token IN ";
421		421
Lines 558-564 Link Here
558		558
559	# shortcut, will only update atime for the token if the atime is less than	559	# shortcut, will only update atime for the token if the atime is less than
560	# what we are updating to	560	# what we are updating to
561	my $sql = "UPDATE bayes_token	561	my $sql = "UPDATE $$self{_token_table}
562	SET atime = ?	562	SET atime = ?
563	WHERE id = ?	563	WHERE id = ?
564	AND token = ?	564	AND token = ?
Lines 644-650 Link Here
644		644
645	return 1 unless (scalar(@{$tokens}));	645	return 1 unless (scalar(@{$tokens}));
646		646
647	my $sql = "UPDATE bayes_token SET atime = ? WHERE id = ? AND token IN (";	647	my $sql = "UPDATE $$self{_token_table} SET atime = ? WHERE id = ? AND token IN (";
648		648
649	my @bindings;	649	my @bindings;
650	foreach my $token (sort @{$tokens}) {	650	foreach my $token (sort @{$tokens}) {
Lines 770-776 Link Here
770	# cleanup was needed, go ahead and clear the cleanup flag	770	# cleanup was needed, go ahead and clear the cleanup flag
771	$self->{needs_cleanup} = 0;	771	$self->{needs_cleanup} = 0;
772		772
773	my $sql = "DELETE from bayes_token	773	my $sql = "DELETE from $$self{_token_table}
774	WHERE id = ?	774	WHERE id = ?
775	AND spam_count <= 0	775	AND spam_count <= 0
776	AND ham_count <= 0";	776	AND ham_count <= 0";
Lines 849-855 Link Here
849	return 0;	849	return 0;
850	}	850	}
851		851
852	$rows = $self->{_dbh}->do("DELETE FROM bayes_token WHERE id = ?",	852	$rows = $self->{_dbh}->do("DELETE FROM $$self{_token_table} WHERE id = ?",
853	undef,	853	undef,
854	$self->{_userid});	854	$self->{_userid});
855	unless (defined($rows)) {	855	unless (defined($rows)) {

Lines 2683-2688 Link Here

(-)Mail-SpamAssassin-3.2.4.orig/lib/Mail/SpamAssassin/Conf.pm (+21 lines)
2683	type => $CONF_TYPE_BOOL	2683	type => $CONF_TYPE_BOOL
2684	});	2684	});
2685		2685
		2686	=item bayes_sql_token_table_count
		2687
		2688	Used by BayesStore::SQL storage implementation.
		2689
		2690	With this option you can spread the token information over several tables if
		2691	the single table will otherwise get too large.
		2692
		2693	After settings this to a value > 1 you must create the tables
		2694	bayes_token_0 through bayes_token_[number - 1] by yourself! They
		2695	have the same structure as the original bayes_token table.
		2696
		2697	=cut
		2698
		2699	push (@cmds, {
		2700	setting => 'bayes_sql_token_table_count',
		2701	is_admin => 1,
		2702	default => 1,
		2703	type => $CONF_TYPE_NUMERIC
		2704	});
		2705
		2706
2686	=item user_scores_dsn DBI:databasetype:databasename:hostname:port	2707	=item user_scores_dsn DBI:databasetype:databasename:hostname:port
2687		2708
2688	If you load user scores from an SQL database, this will set the DSN	2709	If you load user scores from an SQL database, this will set the DSN

Lines 36-41 Link Here

(-)Mail-SpamAssassin-3.2.4.orig/lib/Mail/SpamAssassin/BayesStore/SQL.pm (-30 / +39 lines)
36	use Mail::SpamAssassin::BayesStore;	36	use Mail::SpamAssassin::BayesStore;
37	use Mail::SpamAssassin::Logger;	37	use Mail::SpamAssassin::Logger;
38	use Digest::SHA1 qw(sha1);	38	use Digest::SHA1 qw(sha1);
		39	use String::CRC32;
39		40
40	use vars qw( @ISA );	41	use vars qw( @ISA );
41		42
Lines 238-244 Link Here
238	return %delta unless (defined($self->{_dbh}));	239	return %delta unless (defined($self->{_dbh}));
239		240
240	my $sql = "SELECT count(*)	241	my $sql = "SELECT count(*)
241	FROM bayes_token	242	FROM $$self{_token_table}
242	WHERE id = ?	243	WHERE id = ?
243	AND atime < ?";	244	AND atime < ?";
244		245
Lines 290-296 Link Here
290	my $too_old = $vars[10] - $newdelta; # tooold = newest - delta	291	my $too_old = $vars[10] - $newdelta; # tooold = newest - delta
291		292
292	# if token atime > newest, reset to newest ...	293	# if token atime > newest, reset to newest ...
293	my $sql = "UPDATE bayes_token SET atime = ?	294	my $sql = "UPDATE $$self{_token_table} SET atime = ?
294	WHERE id = ?	295	WHERE id = ?
295	AND atime > ?";	296	AND atime > ?";
296		297
Lines 303-309 Link Here
303	}	304	}
304		305
305	# Check to make sure the expire won't remove too many tokens	306	# Check to make sure the expire won't remove too many tokens
306	$sql = "SELECT count(token) FROM bayes_token	307	$sql = "SELECT count(token) FROM $$self{_token_table}
307	WHERE id = ?	308	WHERE id = ?
308	AND atime < ?";	309	AND atime < ?";
309		310
Lines 336-342 Link Here
336	}	337	}
337	else {	338	else {
338	# Do the expire	339	# Do the expire
339	$sql = "DELETE from bayes_token	340	$sql = "DELETE from $$self{_token_table}
340	WHERE id = ?	341	WHERE id = ?
341	AND atime < ?";	342	AND atime < ?";
342		343
Lines 628-634 Link Here
628	my $token_select = $self->_token_select_string();	629	my $token_select = $self->_token_select_string();
629		630
630	my $sql = "SELECT $token_select, spam_count, ham_count, atime	631	my $sql = "SELECT $token_select, spam_count, ham_count, atime
631	FROM bayes_token	632	FROM $$self{_token_table}
632	WHERE id = ?	633	WHERE id = ?
633	AND (spam_count > 0 OR ham_count > 0)";	634	AND (spam_count > 0 OR ham_count > 0)";
634		635
Lines 806-812 Link Here
806	return (0,0,0) unless (defined($self->{_dbh}));	807	return (0,0,0) unless (defined($self->{_dbh}));
807		808
808	my $sql = "SELECT spam_count, ham_count, atime	809	my $sql = "SELECT spam_count, ham_count, atime
809	FROM bayes_token	810	FROM $$self{_token_table}
810	WHERE id = ?	811	WHERE id = ?
811	AND token = ?";	812	AND token = ?";
812		813
Lines 861-867 Link Here
861	my $token_select = $self->_token_select_string();	862	my $token_select = $self->_token_select_string();
862		863
863	my $multi_sql = "SELECT $token_select, spam_count, ham_count, atime	864	my $multi_sql = "SELECT $token_select, spam_count, ham_count, atime
864	FROM bayes_token	865	FROM $$self{_token_table}
865	WHERE id = ?	866	WHERE id = ?
866	AND token IN ";	867	AND token IN ";
867		868
Lines 1058-1064 Link Here
1058		1059
1059	# shortcut, will only update atime for the token if the atime is less than	1060	# shortcut, will only update atime for the token if the atime is less than
1060	# what we are updating to	1061	# what we are updating to
1061	my $sql = "UPDATE bayes_token	1062	my $sql = "UPDATE $$self{_token_table}
1062	SET atime = ?	1063	SET atime = ?
1063	WHERE id = ?	1064	WHERE id = ?
1064	AND token = ?	1065	AND token = ?
Lines 1117-1123 Link Here
1117		1118
1118	return 1 unless (scalar(@{$tokens}));	1119	return 1 unless (scalar(@{$tokens}));
1119		1120
1120	my $sql = "UPDATE bayes_token SET atime = ? WHERE id = ? AND token IN (";	1121	my $sql = "UPDATE $$self{_token_table} SET atime = ? WHERE id = ? AND token IN (";
1121		1122
1122	my @bindings = ($atime, $self->{_userid});	1123	my @bindings = ($atime, $self->{_userid});
1123	foreach my $token (@{$tokens}) {	1124	foreach my $token (@{$tokens}) {
Lines 1176-1182 Link Here
1176	# cleanup was needed, go ahead and clear the cleanup flag	1177	# cleanup was needed, go ahead and clear the cleanup flag
1177	$self->{needs_cleanup} = 0;	1178	$self->{needs_cleanup} = 0;
1178		1179
1179	my $sql = "DELETE from bayes_token	1180	my $sql = "DELETE from $$self{_token_table}
1180	WHERE id = ?	1181	WHERE id = ?
1181	AND spam_count = 0	1182	AND spam_count = 0
1182	AND ham_count = 0";	1183	AND ham_count = 0";
Lines 1299-1305 Link Here
1299	return 0;	1300	return 0;
1300	}	1301	}
1301		1302
1302	$rows = $self->{_dbh}->do("DELETE FROM bayes_token WHERE id = ?",	1303	$rows = $self->{_dbh}->do("DELETE FROM $$self{_token_table} WHERE id = ?",
1303	undef,	1304	undef,
1304	$self->{_userid});	1305	$self->{_userid});
1305	unless (defined($rows)) {	1306	unless (defined($rows)) {
Lines 1338-1344 Link Here
1338	my $token_select = $self->_token_select_string();	1339	my $token_select = $self->_token_select_string();
1339		1340
1340	my $token_sql = "SELECT spam_count, ham_count, atime, $token_select	1341	my $token_sql = "SELECT spam_count, ham_count, atime, $token_select
1341	FROM bayes_token	1342	FROM $$self{_token_table}
1342	WHERE id = ?	1343	WHERE id = ?
1343	AND (spam_count > 0 OR ham_count > 0)";	1344	AND (spam_count > 0 OR ham_count > 0)";
1344		1345
Lines 1745-1751 Link Here
1745	}	1746	}
1746	}	1747	}
1747		1748
1748	my $sqlselect = "SELECT id FROM bayes_vars WHERE username = ?";	1749	my $sqlselect = "SELECT id, token_table FROM bayes_vars WHERE username = ?";
1749		1750
1750	my $sthselect = $self->{_dbh}->prepare_cached($sqlselect);	1751	my $sthselect = $self->{_dbh}->prepare_cached($sqlselect);
1751		1752
Lines 1761-1784 Link Here
1761	return 0;	1762	return 0;
1762	}	1763	}
1763		1764
1764	my ($id) = $sthselect->fetchrow_array();	1765	my ($id, $token_table) = $sthselect->fetchrow_array();
1765		1766
1766	if ($id) {	1767	if ($id) {
1767	$self->{_userid} = $id;	1768	$self->{_userid} = $id;
1768	dbg("bayes: Using userid: ".$self->{_userid});	1769	$self->{_token_table} = $token_table;
		1770	dbg("bayes: Using userid: ".$self->{_userid}.", token table: ".$self->{_token_table});
1769	$sthselect->finish();	1771	$sthselect->finish();
1770	return 1;	1772	return 1;
1771	}	1773	}
1772		1774
		1775	$self->{_token_table} = "bayes_token";
1773	# Do not create an entry for this user unless we were specifically asked to	1776	# Do not create an entry for this user unless we were specifically asked to
1774	return 0 unless ($create_entry_p);	1777	return 0 unless ($create_entry_p);
1775		1778
1776	# For now let the database setup the other variables as defaults	1779	# For now let the database setup the other variables as defaults
1777	my $sqlinsert = "INSERT INTO bayes_vars (username) VALUES (?)";	1780	my $sqlinsert = "INSERT INTO bayes_vars (username, token_table) VALUES (?, ?)";
		1781	if ($self->{bayes}->{conf}->{bayes_sql_token_table_count}
		1782	&& $self->{bayes}->{conf}->{bayes_sql_token_table_count} > 1) {
		1783	$self->{_token_table} = "bayes_token_"
		1784	. (crc32($self->{_username}) % $self->{bayes}->{conf}->{token_table_count});
		1785	}
1778		1786
1779	my $rows = $self->{_dbh}->do($sqlinsert,	1787	my $rows = $self->{_dbh}->do($sqlinsert,
1780	undef,	1788	undef,
1781	$self->{_username});	1789	$self->{_username},
		1790	$self->{_token_table});
1782	unless (defined($rows)) {	1791	unless (defined($rows)) {
1783	dbg("bayes: _initialize_db: SQL error: ".$self->{_dbh}->errstr());	1792	dbg("bayes: _initialize_db: SQL error: ".$self->{_dbh}->errstr());
1784	return 0;	1793	return 0;
Lines 1843-1849 Link Here
1843	# if we are unable to find an entry.	1852	# if we are unable to find an entry.
1844	return 1 if ($spam_count < 0 \|\| $ham_count < 0);	1853	return 1 if ($spam_count < 0 \|\| $ham_count < 0);
1845		1854
1846	my $sql = "INSERT INTO bayes_token	1855	my $sql = "INSERT INTO $$self{_token_table}
1847	(id, token, spam_count, ham_count, atime)	1856	(id, token, spam_count, ham_count, atime)
1848	VALUES (?,?,?,?,?)";	1857	VALUES (?,?,?,?,?)";
1849		1858
Lines 1930-1936 Link Here
1930	my $sql;	1939	my $sql;
1931	my @args;	1940	my @args;
1932	if ($update_atime_p) {	1941	if ($update_atime_p) {
1933	$sql = "UPDATE bayes_token	1942	$sql = "UPDATE $$self{_token_table}
1934	SET spam_count = spam_count + ?,	1943	SET spam_count = spam_count + ?,
1935	atime = ?	1944	atime = ?
1936	WHERE id = ?	1945	WHERE id = ?
Lines 1940-1946 Link Here
1940	$updated_atime_p = 1; # note the fact that we did do it	1949	$updated_atime_p = 1; # note the fact that we did do it
1941	}	1950	}
1942	else {	1951	else {
1943	$sql = "UPDATE bayes_token	1952	$sql = "UPDATE $$self{_token_table}
1944	SET spam_count = spam_count + ?	1953	SET spam_count = spam_count + ?
1945	WHERE id = ?	1954	WHERE id = ?
1946	AND token = ?	1955	AND token = ?
Lines 1960-1966 Link Here
1960	my $sql;	1969	my $sql;
1961	my @args;	1970	my @args;
1962	if ($update_atime_p && !$updated_atime_p) {	1971	if ($update_atime_p && !$updated_atime_p) {
1963	$sql = "UPDATE bayes_token	1972	$sql = "UPDATE $$self{_token_table}
1964	SET ham_count = ham_count + ?,	1973	SET ham_count = ham_count + ?,
1965	atime = ?	1974	atime = ?
1966	WHERE id = ?	1975	WHERE id = ?
Lines 1970-1976 Link Here
1970	$updated_atime_p = 1; # note the fact that we did do it	1979	$updated_atime_p = 1; # note the fact that we did do it
1971	}	1980	}
1972	else {	1981	else {
1973	$sql = "UPDATE bayes_token	1982	$sql = "UPDATE $$self{_token_table}
1974	SET ham_count = ham_count + ?	1983	SET ham_count = ham_count + ?
1975	WHERE id = ?	1984	WHERE id = ?
1976	AND token = ?	1985	AND token = ?
Lines 2034-2040 Link Here
2034	my $atime_inserted_p = 0;	2043	my $atime_inserted_p = 0;
2035	my $new_tokens = 0;	2044	my $new_tokens = 0;
2036		2045
2037	my $insertsql = "INSERT INTO bayes_token	2046	my $insertsql = "INSERT INTO $$self{_token_table}
2038	(id, token, spam_count, ham_count, atime)	2047	(id, token, spam_count, ham_count, atime)
2039	VALUES (?,?,?,?,?)";	2048	VALUES (?,?,?,?,?)";
2040		2049
Lines 2101-2107 Link Here
2101	my $sql;	2110	my $sql;
2102	my @args;	2111	my @args;
2103	if ($update_atime_p) {	2112	if ($update_atime_p) {
2104	$sql = "UPDATE bayes_token	2113	$sql = "UPDATE $$self{_token_table}
2105	SET spam_count = spam_count + ?,	2114	SET spam_count = spam_count + ?,
2106	atime = ?	2115	atime = ?
2107	WHERE id = ?	2116	WHERE id = ?
Lines 2111-2117 Link Here
2111	$atime_updated_p = 1;	2120	$atime_updated_p = 1;
2112	}	2121	}
2113	else {	2122	else {
2114	$sql = "UPDATE bayes_token	2123	$sql = "UPDATE $$self{_token_table}
2115	SET spam_count = spam_count + ?	2124	SET spam_count = spam_count + ?
2116	WHERE id = ?	2125	WHERE id = ?
2117	AND token = ?	2126	AND token = ?
Lines 2131-2137 Link Here
2131	my @args;	2140	my @args;
2132	# if $spam_count then we already updated the atime	2141	# if $spam_count then we already updated the atime
2133	if ($update_atime_p && !$spam_count) {	2142	if ($update_atime_p && !$spam_count) {
2134	$sql = "UPDATE bayes_token	2143	$sql = "UPDATE $$self{_token_table}
2135	SET ham_count = ham_count + ?,	2144	SET ham_count = ham_count + ?,
2136	atime = ?	2145	atime = ?
2137	WHERE id = ?	2146	WHERE id = ?
Lines 2141-2147 Link Here
2141	$atime_updated_p = 1;	2150	$atime_updated_p = 1;
2142	}	2151	}
2143	else {	2152	else {
2144	$sql = "UPDATE bayes_token	2153	$sql = "UPDATE $$self{_token_table}
2145	SET ham_count = ham_count + ?	2154	SET ham_count = ham_count + ?
2146	WHERE id = ?	2155	WHERE id = ?
2147	AND token = ?	2156	AND token = ?
Lines 2219-2225 Link Here
2219		2228
2220	return 0 unless (defined($self->{_dbh}));	2229	return 0 unless (defined($self->{_dbh}));
2221		2230
2222	my $sql = "SELECT min(atime) FROM bayes_token	2231	my $sql = "SELECT min(atime) FROM $$self{_token_table}
2223	WHERE id = ?";	2232	WHERE id = ?";
2224		2233
2225	my $sth = $self->{_dbh}->prepare_cached($sql);	2234	my $sth = $self->{_dbh}->prepare_cached($sql);
Lines 2260-2266 Link Here
2260	return 0 unless (defined($self->{_dbh}));	2269	return 0 unless (defined($self->{_dbh}));
2261		2270
2262	my $sql = "SELECT count(*)	2271	my $sql = "SELECT count(*)
2263	FROM bayes_token	2272	FROM $$self{_token_table}
2264	WHERE id = ?	2273	WHERE id = ?
2265	AND spam_count + ham_count = 1";	2274	AND spam_count + ham_count = 1";
2266		2275
Lines 2302-2308 Link Here
2302	return 0 unless (defined($self->{_dbh}));	2311	return 0 unless (defined($self->{_dbh}));
2303		2312
2304	my $sql = "SELECT count(*)	2313	my $sql = "SELECT count(*)
2305	FROM bayes_token	2314	FROM $$self{_token_table}
2306	WHERE id = ?	2315	WHERE id = ?
2307	AND (spam_count >= 0 AND spam_count < 8)	2316	AND (spam_count >= 0 AND spam_count < 8)
2308	AND (ham_count >= 0 AND ham_count < 8)	2317	AND (ham_count >= 0 AND ham_count < 8)

Lines 43-48 Link Here

(-)Mail-SpamAssassin-3.2.4.orig/sql/bayes_pg.sql (-10 / +15 lines)
43	last_expire_reduce integer NOT NULL default '0',	43	last_expire_reduce integer NOT NULL default '0',
44	oldest_token_age integer NOT NULL default '2147483647',	44	oldest_token_age integer NOT NULL default '2147483647',
45	newest_token_age integer NOT NULL default '0',	45	newest_token_age integer NOT NULL default '0',
		46	token_table varchar(20) NOT NULL default 'bayes_token',
46	PRIMARY KEY (id)	47	PRIMARY KEY (id)
47	) WITHOUT OIDS;	48	) WITHOUT OIDS;
48		49
Lines 65-89 Link Here
65	inspam_count INTEGER,	66	inspam_count INTEGER,
66	inham_count INTEGER,	67	inham_count INTEGER,
67	inatime INTEGER)	68	inatime INTEGER)
68	RETURNS VOID AS '	69	RETURNS VOID AS $$
69	DECLARE	70	DECLARE
70	_token BYTEA;	71	_token BYTEA;
71	new_tokens INTEGER := 0;	72	new_tokens INTEGER := 0;
		73	tt VARCHAR(20);
72	BEGIN	74	BEGIN
		75	SELECT token_table INTO tt FROM bayes_vars WHERE (id = inuserid);
		76
73	for i in array_lower(intokenary, 1) .. array_upper(intokenary, 1)	77	for i in array_lower(intokenary, 1) .. array_upper(intokenary, 1)
74	LOOP	78	LOOP
75	_token := intokenary[i];	79	_token := intokenary[i];
76	UPDATE bayes_token	80	EXECUTE 'UPDATE ' \|\| tt \|\| '
77	SET spam_count = greatest_int(spam_count + inspam_count, 0),	81	SET spam_count = greatest_int(spam_count + ' \|\| inspam_count \|\| ', 0),
78	ham_count = greatest_int(ham_count + inham_count, 0),	82	ham_count = greatest_int(ham_count + ' \|\| inham_count \|\| ', 0),
79	atime = greatest_int(atime, inatime)	83	atime = greatest_int(atime, ' \|\| inatime \|\| ')
80	WHERE id = inuserid	84	WHERE id = inuserid
81	AND token = _token;	85	AND token = ' \|\| quote_literal(token) \|\| ';';
82	IF NOT FOUND THEN	86	IF NOT FOUND THEN
83	-- we do not insert negative counts, just return true	87	-- we do not insert negative counts, just return true
84	IF NOT (inspam_count < 0 OR inham_count < 0) THEN	88	IF NOT (inspam_count < 0 OR inham_count < 0) THEN
85	INSERT INTO bayes_token (id, token, spam_count, ham_count, atime)	89	EXECUTE 'INSERT INTO ' \|\| tt \| ' (id, token, spam_count, ham_count, atime)
86	VALUES (inuserid, _token, inspam_count, inham_count, inatime);	90	VALUES (' \|\| inuserid \|\| ', ' \|\| quote_literal(_token) \|\| ', '
		91	\|\| inspam_count \|\| ', ' \|\| inham_count \|\| ', ' \|\| inatime \|\| ');';
87	IF FOUND THEN	92	IF FOUND THEN
88	new_tokens := new_tokens + 1;	93	new_tokens := new_tokens + 1;
89	END IF;	94	END IF;
Lines 109-112 Link Here
109	END IF;	114	END IF;
110	RETURN;	115	RETURN;
111	END;	116	END;
112	' LANGUAGE 'plpgsql';	117	$$ LANGUAGE 'plpgsql';