You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spamassassin.apache.org by pa...@apache.org on 2005/08/07 22:09:40 UTC
svn commit: r230697 - in /spamassassin/trunk: lib/Mail/SpamAssassin/
lib/Mail/SpamAssassin/BayesStore/ sql/
Author: parker
Date: Sun Aug 7 13:09:36 2005
New Revision: 230697
URL: http://svn.apache.org/viewcvs?rev=230697&view=rev
Log:
Bug 4519: Adding multi_tok_count_change API to BayesStore as well as making the PgSQL code use transactions better
Modified:
spamassassin/trunk/lib/Mail/SpamAssassin/Bayes.pm
spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore.pm
spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/DBM.pm
spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/MySQL.pm
spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/PgSQL.pm
spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/SQL.pm
spamassassin/trunk/sql/bayes_pg.sql
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/Bayes.pm
URL: http://svn.apache.org/viewcvs/spamassassin/trunk/lib/Mail/SpamAssassin/Bayes.pm?rev=230697&r1=230696&r2=230697&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/Bayes.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/Bayes.pm Sun Aug 7 13:09:36 2005
@@ -800,12 +800,10 @@
my $tokens = $self->tokenize($msg, $msgdata);
- for my $token (keys %{$tokens}) {
- if ($isspam) {
- $self->{store}->tok_count_change (1, 0, $token, $msgatime);
- } else {
- $self->{store}->tok_count_change (0, 1, $token, $msgatime);
- }
+ if ($isspam) {
+ $self->{store}->multi_tok_count_change(1, 0, $tokens, $msgatime);
+ } else {
+ $self->{store}->multi_tok_count_change(0, 1, $tokens, $msgatime);
}
$self->{store}->seen_put ($msgid, ($isspam ? 's' : 'h'));
@@ -912,12 +910,10 @@
my $tokens = $self->tokenize($msg, $msgdata);
- for my $token (keys %{$tokens}) {
- if ($isspam) {
- $self->{store}->tok_count_change (-1, 0, $token);
- } else {
- $self->{store}->tok_count_change (0, -1, $token);
- }
+ if ($isspam) {
+ $self->{store}->multi_tok_count_change (-1, 0, $tokens);
+ } else {
+ $self->{store}->multi_tok_count_change (0, -1, $tokens);
}
$self->{store}->seen_delete ($msgid);
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore.pm
URL: http://svn.apache.org/viewcvs/spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore.pm?rev=230697&r1=230696&r2=230697&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore.pm Sun Aug 7 13:09:36 2005
@@ -657,6 +657,25 @@
die "bayes: tok_count_change: not implemented\n";
}
+=head2 multi_tok_count_change
+
+public instance (Boolean) multi_tok_count_change (Integer $spam_count,
+ Integer $ham_count,
+ \% $tokens,
+ String $atime)
+
+Description:
+This method takes a C<$spam_count> and C<$ham_count> and adds it to all
+of the tokens in the C<$tokens> hash ref along with updating each tokens
+atime with C<$atime>.
+
+=cut
+
+sub multi_tok_count_change {
+ my ($self, $spam_count, $ham_count, $tokens, $atime) = @_;
+ die "bayes: multi_tok_count_change: not implemented\n";
+}
+
=item nspam_nham_get
public instance (Integer, Integer) nspam_nham_get ()
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/DBM.pm
URL: http://svn.apache.org/viewcvs/spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/DBM.pm?rev=230697&r1=230696&r2=230697&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/DBM.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/DBM.pm Sun Aug 7 13:09:36 2005
@@ -1029,6 +1029,23 @@
}
}
+sub multi_tok_count_change {
+ my ($self, $ds, $dh, $tokens, $atime) = @_;
+
+ $atime = 0 unless defined $atime;
+
+ foreach my $tok (keys %{$tokens}) {
+ if ($self->{bayes}->{main}->{learn_to_journal}) {
+ # we can't store the SHA1 binary value in the journal to convert it
+ # to a printable value that can be converted back later
+ my $encoded_tok = unpack("H*",$tok);
+ $self->defer_update ("c $ds $dh $atime $encoded_tok");
+ } else {
+ $self->tok_sync_counters ($ds, $dh, $atime, $tok);
+ }
+ }
+}
+
sub nspam_nham_get {
my ($self) = @_;
my @vars = $self->get_storage_variables();
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/MySQL.pm
URL: http://svn.apache.org/viewcvs/spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/MySQL.pm?rev=230697&r1=230696&r2=230697&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/MySQL.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/MySQL.pm Sun Aug 7 13:09:36 2005
@@ -881,6 +881,161 @@
return 1;
}
+=head2 _put_tokens
+
+private instance (Boolean) _put_tokens (\% $tokens,
+ integer $spam_count,
+ integer $ham_count,
+ string $atime)
+
+Description:
+This method performs the work of either inserting or updating tokens in
+the database.
+
+=cut
+
+sub _put_tokens {
+ my ($self, $tokens, $spam_count, $ham_count, $atime) = @_;
+
+ return 0 unless (defined($self->{_dbh}));
+
+ $spam_count ||= 0;
+ $ham_count ||= 0;
+
+ if ($spam_count == 0 && $ham_count == 0) {
+ return 1;
+ }
+
+ # the case where spam_count of ham_count is < 0 is special, it assumes
+ # that there already exists a token (although there might actually not be
+ # be one) that will be updated. So we just do the update, being careful
+ # to not allow the spam_count or ham_count to not drop below 0
+ # In addition, when lowering the spam_count or ham_count we will not be
+ # updating the atime value
+ if ($spam_count < 0 || $ham_count < 0) {
+ # we only need to cleanup when we subtract counts for a token and the
+ # counts may have both reached 0
+ $self->{needs_cleanup} = 1;
+
+ my $sql = "UPDATE bayes_token SET spam_count = GREATEST(spam_count + ?, 0),
+ ham_count = GREATEST(ham_count + ?, 0)
+ WHERE id = ?
+ AND token = ?";
+
+ my $sth = $self->{_dbh}->prepare_cached($sql);
+
+ unless (defined($sth)) {
+ dbg("bayes: _put_tokens: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ my $error_p = 0;
+ foreach my $token (keys %{$tokens}) {
+ my $rc = $sth->execute($spam_count,
+ $ham_count,
+ $self->{_userid},
+ $token);
+
+ unless ($rc) {
+ dbg("bayes: _put_tokens: SQL error: ".$self->{_dbh}->errstr());
+ $error_p = 1;
+ }
+ }
+
+ $sth->finish();
+
+ if ($error_p) {
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+ }
+ else {
+ my $sql = "INSERT INTO bayes_token
+ (id, token, spam_count, ham_count, atime)
+ VALUES (?,?,?,?,?)
+ ON DUPLICATE KEY UPDATE spam_count = GREATEST(spam_count + ?, 0),
+ ham_count = GREATEST(ham_count + ?, 0),
+ atime = GREATEST(atime, ?)";
+
+ my $sth = $self->{_dbh}->prepare_cached($sql);
+
+ unless (defined($sth)) {
+ dbg("bayes: _put_tokens: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ my $error_p = 0;
+ my $new_tokens = 0;
+ my $need_atime_update_p = 0;
+ foreach my $token (keys %{$tokens}) {
+ my $rc = $sth->execute($self->{_userid},
+ $token,
+ $spam_count,
+ $ham_count,
+ $atime,
+ $spam_count,
+ $ham_count,
+ $atime);
+
+ if (!$rc) {
+ dbg("bayes: _put_tokens: SQL error: ".$self->{_dbh}->errstr());
+ $error_p = 1;
+ }
+ else {
+ my $num_rows = $rc;
+
+ $need_atime_update_p = 1 if ($num_rows == 1 || $num_rows == 2);
+ $new_tokens++ if ($num_rows == 1);
+ }
+ }
+
+ $sth->finish();
+
+ if ($error_p) {
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ if ($need_atime_update_p) {
+ my $token_count_update = '';
+
+ $token_count_update = "token_count = token_count + $new_tokens," if ($new_tokens);
+ $sql = "UPDATE bayes_vars SET
+ $token_count_update
+ newest_token_age = GREATEST(newest_token_age, ?),
+ oldest_token_age = LEAST(oldest_token_age, ?)
+ WHERE id = ?";
+
+ $sth = $self->{_dbh}->prepare_cached($sql);
+
+ unless (defined($sth)) {
+ dbg("bayes: _put_tokens: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ my $rc = $sth->execute($atime, $atime, $self->{_userid});
+
+ unless ($rc) {
+ dbg("bayes: _put_tokens: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+ }
+ else {
+ # $num_rows was not what we expected
+ dbg("bayes: _put_tokens: Updated an unexpected number of rows.");
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+ }
+
+ $self->{_dbh}->commit();
+ return 1;
+}
+
sub sa_die { Mail::SpamAssassin::sa_die(@_); }
1;
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/PgSQL.pm
URL: http://svn.apache.org/viewcvs/spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/PgSQL.pm?rev=230697&r1=230696&r2=230697&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/PgSQL.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/PgSQL.pm Sun Aug 7 13:09:36 2005
@@ -44,12 +44,303 @@
@ISA = qw( Mail::SpamAssassin::BayesStore::SQL );
+use constant HAS_DBI => eval { require DBI; };
+
# We need this so we can import the pg_types, since this is a DBD::Pg specific module it should be ok
# YUCK! This little require/import trick is required for the rpm stuff
BEGIN { require DBD::Pg; import DBD::Pg qw(:pg_types); }
=head1 METHODS
+=head2 token_expiration
+
+public instance (Integer, Integer,
+ Integer, Integer) token_expiration(\% $opts,
+ Integer $newdelta,
+ @ @vars)
+
+Description:
+This method performs the database specific expiration of tokens based on
+the passed in C<$newdelta> and C<@vars>.
+
+=cut
+
+sub token_expiration {
+ my ($self, $opts, $newdelta, @vars) = @_;
+
+ my $num_hapaxes;
+ my $num_lowfreq;
+ my $deleted;
+
+ # Figure out how old is too old...
+ my $too_old = $vars[10] - $newdelta; # tooold = newest - delta
+
+ # if token atime > newest, reset to newest ...
+ my $sql = "UPDATE bayes_token SET atime = ?
+ WHERE id = ?
+ AND atime > ?";
+
+ my $rows = $self->{_dbh}->do($sql, undef, $vars[10], $self->{_userid}, $vars[10]);
+
+ unless (defined($rows)) {
+ dbg("bayes: token_expiration: SQL error: ".$self->{_dbh}->errstr());
+ $deleted = 0;
+ $self->{_dbh}->rollback();
+ goto token_expiration_final;
+ }
+
+ # Check to make sure the expire won't remove too many tokens
+ $sql = "SELECT count(token) FROM bayes_token
+ WHERE id = ?
+ AND atime < ?";
+
+ my $sth = $self->{_dbh}->prepare_cached($sql);
+
+ unless (defined($sth)) {
+ dbg("bayes: token_expiration: SQL error: ".$self->{_dbh}->errstr());
+ $deleted = 0;
+ $self->{_dbh}->rollback();
+ goto token_expiration_final;
+ }
+
+ my $rc = $sth->execute($self->{_userid}, $too_old);
+
+ unless ($rc) {
+ dbg("bayes: token_expiration: SQL error: ".$self->{_dbh}->errstr());
+ $deleted = 0;
+ $self->{_dbh}->rollback();
+ goto token_expiration_final;
+ }
+
+ my ($count) = $sth->fetchrow_array();
+
+ $sth->finish();
+
+ # Sanity check: if we expired too many tokens, abort!
+ if ($vars[3] - $count < 100000) {
+ dbg("bayes: token expiration would expire too many tokens, aborting");
+ # set these appropriately so the next expire pass does the first pass
+ $deleted = 0;
+ $newdelta = 0;
+ }
+ else {
+ # Do the expire
+ $sql = "DELETE from bayes_token
+ WHERE id = ?
+ AND atime < ?";
+
+ $rows = $self->{_dbh}->do($sql, undef, $self->{_userid}, $too_old);
+
+ unless (defined($rows)) {
+ dbg("bayes: token_expiration: SQL error: ".$self->{_dbh}->errstr());
+ $deleted = 0;
+ $self->{_dbh}->rollback();
+ goto token_expiration_final;
+ }
+
+ $deleted = $rows;
+ }
+
+ # Update the magic tokens as appropriate
+ $sql = "UPDATE bayes_vars SET token_count = token_count - ?,
+ last_expire = ?,
+ last_atime_delta = ?,
+ last_expire_reduce = ?,
+ oldest_token_age = (SELECT min(atime)
+ FROM bayes_token
+ WHERE id = ?)
+ WHERE id = ?";
+
+ $rows = $self->{_dbh}->do($sql, undef, $deleted, time(), $newdelta, $deleted, $self->{_userid}, $self->{_userid});
+
+ unless (defined($rows)) {
+ # Very bad, we actually deleted the tokens, but were unable to update
+ # bayes_vars with the new data.
+ dbg("bayes: token_expiration: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ $deleted = 0;
+ goto token_expiration_final;
+ }
+
+ $self->{_dbh}->commit();
+
+token_expiration_final:
+ my $kept = $vars[3] - $deleted;
+
+ $num_hapaxes = $self->_get_num_hapaxes() if ($opts->{verbose});
+ $num_lowfreq = $self->_get_num_lowfreq() if ($opts->{verbose});
+
+ # Call untie_db() first so we unlock correctly etc. first
+ $self->untie_db();
+
+ return ($kept, $deleted, $num_hapaxes, $num_lowfreq);
+}
+
+=head2 seen_put
+
+public (Boolean) seen_put (string $msgid, char $flag)
+
+Description:
+This method records C<$msgid> as the type given by C<$flag>. C<$flag> is one of
+two values 's' for spam and 'h' for ham.
+
+=cut
+
+sub seen_put {
+ my ($self, $msgid, $flag) = @_;
+
+ return 0 if (!$msgid);
+ return 0 if (!$flag);
+
+ return 0 unless (defined($self->{_dbh}));
+
+ my $sql = "INSERT INTO bayes_seen (id, msgid, flag)
+ VALUES (?,?,?)";
+
+ my $rows = $self->{_dbh}->do($sql,
+ undef,
+ $self->{_userid}, $msgid, $flag);
+
+ unless (defined($rows)) {
+ dbg("bayes: seen_put: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ dbg("bayes: seen ($msgid) put");
+ $self->{_dbh}->commit();
+ return 1;
+}
+
+=head2 seen_delete
+
+public instance (Boolean) seen_delete (string $msgid)
+
+Description:
+This method removes C<$msgid> from the database.
+
+=cut
+
+sub seen_delete {
+ my ($self, $msgid) = @_;
+
+ return 0 if (!$msgid);
+
+ return 0 unless (defined($self->{_dbh}));
+
+ my $sql = "DELETE FROM bayes_seen
+ WHERE id = ?
+ AND msgid = ?";
+
+ my $rows = $self->{_dbh}->do($sql,
+ undef,
+ $self->{_userid}, $msgid);
+
+ unless (defined($rows)) {
+ dbg("bayes: seen_delete: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $self->{_dbh}->commit();
+ return 1;
+}
+
+=head2 set_last_expire
+
+public instance (Boolean) set_last_expire (Integer $time)
+
+Description:
+This method sets the last expire time.
+
+=cut
+
+sub set_last_expire {
+ my ($self, $time) = @_;
+
+ return 0 unless (defined($time));
+
+ return 0 unless (defined($self->{_dbh}));
+
+ my $sql = "UPDATE bayes_vars SET last_expire = ? WHERE id = ?";
+
+ my $rows = $self->{_dbh}->do($sql,
+ undef,
+ $time,
+ $self->{_userid});
+
+ unless (defined($rows)) {
+ dbg("bayes: set_last_expire: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $self->{_dbh}->commit();
+ return 1;
+}
+
+=head2 set_running_expire_tok
+
+public instance (String $time) set_running_expire_tok ()
+
+Description:
+This method sets the time that an expire starts running.
+
+=cut
+
+sub set_running_expire_tok {
+ my ($self) = @_;
+
+ return 0 unless (defined($self->{_dbh}));
+
+ my $sql = "INSERT INTO bayes_expire (id,runtime) VALUES (?,?)";
+
+ my $time = time();
+
+ my $rows = $self->{_dbh}->do($sql,
+ undef,
+ $self->{_userid}, $time);
+ unless (defined($rows)) {
+ dbg("bayes: set_running_expire_tok: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return undef;
+ }
+
+ $self->{_dbh}->commit();
+ return $time;
+}
+
+=head2 remove_running_expire_tok
+
+public instance (Boolean) remove_running_expire_tok ()
+
+Description:
+This method removes the row in the database that indicates that
+and expire is currently running.
+
+=cut
+
+sub remove_running_expire_tok {
+ my ($self) = @_;
+
+ return 0 unless (defined($self->{_dbh}));
+
+ my $sql = "DELETE from bayes_expire
+ WHERE id = ?";
+
+ my $rows = $self->{_dbh}->do($sql, undef, $self->{_userid});
+
+ unless (defined($rows)) {
+ dbg("bayes: remove_running_expire_tok: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $self->{_dbh}->commit();
+
+ return 1;
+}
+
=head2 tok_get
public instance (Integer, Integer, Integer) tok_get (String $token)
@@ -190,6 +481,63 @@
return \@tok_results;
}
+=head2 nspam_nham_change
+
+public instance (Boolean) nspam_nham_change (Integer $num_spam,
+ Integer $num_ham)
+
+Description:
+This method updates the number of spam and the number of ham in the database.
+
+=cut
+
+sub nspam_nham_change {
+ my ($self, $num_spam, $num_ham) = @_;
+
+ return 0 unless (defined($self->{_dbh}));
+
+ my $sql;
+ my @bindings;
+
+ if ($num_spam != 0 && $num_ham != 0) {
+ $sql = "UPDATE bayes_vars
+ SET spam_count = spam_count + ?,
+ ham_count = ham_count + ?
+ WHERE id = ?";
+ @bindings = ($num_spam, $num_ham, $self->{_userid});
+ }
+ elsif ($num_spam != 0) {
+ $sql = "UPDATE bayes_vars
+ SET spam_count = spam_count + ?
+ WHERE id = ?";
+ @bindings = ($num_spam, $self->{_userid});
+ }
+ elsif ($num_ham != 0) {
+ $sql = "UPDATE bayes_vars
+ SET ham_count = ham_count + ?
+ WHERE id = ?";
+ @bindings = ($num_ham, $self->{_userid});
+ }
+ else {
+ # For some reason called with no delta, it's ok though so just return
+ dbg("bayes: nspam_nham_change: Called with no delta on spam or ham");
+ return 1;
+ }
+
+ my $rows = $self->{_dbh}->do($sql,
+ undef,
+ @bindings);
+
+ unless (defined($rows)) {
+ dbg("bayes: nspam_nham_change: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $self->{_dbh}->commit();
+ return 1;
+}
+
=head2 tok_touch
public instance (Boolean) tok_touch (String $token,
@@ -219,6 +567,7 @@
unless (defined($sth)) {
dbg("bayes: tok_touch: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
return 0;
}
@@ -231,6 +580,7 @@
unless ($rc) {
dbg("bayes: tok_touch: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
return 0;
}
@@ -238,11 +588,15 @@
unless (defined($rows)) {
dbg("bayes: tok_touch: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
return 0;
}
# if we didn't update a row then no need to update newest_token_age
- return 1 if ($rows eq '0E0');
+ if ($rows eq '0E0') {
+ $self->{_dbh}->commit();
+ return 1;
+ }
# need to check newest_token_age
# no need to check oldest_token_age since we would only update if the
@@ -256,9 +610,12 @@
unless (defined($rows)) {
dbg("bayes: tok_touch: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
return 0;
}
+ $self->{_dbh}->commit();
+
return 1;
}
@@ -289,7 +646,7 @@
my $sql = "UPDATE bayes_token SET atime = ? WHERE id = ? AND token IN (";
my @bindings;
- foreach my $token (@{$tokens}) {
+ foreach my $token (sort @{$tokens}) {
$sql .= "?,";
push(@bindings, $token);
}
@@ -357,11 +714,186 @@
}
$self->{_dbh}->commit();
+
+ return 1;
+}
+
+
+sub tok_touch_allold {
+ my ($self, $tokens, $atime) = @_;
+
+ return 0 unless (defined($self->{_dbh}));
+
+ return 1 unless (scalar(@{$tokens}));
+
+ my $tokenarray = join(",", map { '"' . _quote_bytea($_) . '"' } sort @{$tokens});
+
+ my $sth = $self->{_dbh}->prepare("select touch_tokens($self->{_userid}, '{$tokenarray}', $atime)");
+
+ unless (defined($sth)) {
+ dbg("bayes: tok_touch_all: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ my $rc = $sth->execute();
+
+ unless ($rc) {
+ dbg("bayes: tok_touch_all: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $sth->finish();
+
+ $self->{_dbh}->commit();
+
+ return 1;
+}
+
+=head2 cleanup
+
+public instance (Boolean) cleanup ()
+
+Description:
+This method peroms any cleanup necessary before moving onto the next
+operation.
+
+=cut
+
+sub cleanup {
+ my ($self) = @_;
+
+ return 1 unless ($self->{needs_cleanup});
+
+ # cleanup was needed, go ahead and clear the cleanup flag
+ $self->{needs_cleanup} = 0;
+
+ my $sql = "DELETE from bayes_token
+ WHERE id = ?
+ AND spam_count <= 0
+ AND ham_count <= 0";
+
+ my $toks_deleted = $self->{_dbh}->do($sql, undef, $self->{_userid});
+
+ unless (defined($toks_deleted)) {
+ dbg("bayes: cleanup: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ # check to see if any tokens where deleted
+ return 1 if ($toks_deleted eq '0E0');
+
+ $sql = "UPDATE bayes_vars SET token_count = token_count - ? WHERE id = ?";
+
+ my $rows = $self->{_dbh}->do($sql, undef, $toks_deleted, $self->{_userid});
+
+ unless (defined($rows)) {
+ dbg("bayes: cleanup: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $self->{_dbh}->commit();
+
+ return 1;
+}
+
+=head2 clear_database
+
+public instance (Boolean) clear_database ()
+
+Description:
+This method deletes all records for a particular user.
+
+Callers should be aware that any errors returned by this method
+could causes the database to be inconsistent for the given user.
+
+=cut
+
+sub clear_database {
+ my ($self) = @_;
+
+ # We want to open readonly first, because if they don't already have
+ # a db entry, we want to avoid creating one, just to delete it in a few secs
+ if ($self->tie_db_readonly()) {
+ # Ok, they must have had a db entry, so now make the connection writable
+ $self->tie_db_writable();
+ }
+ else {
+ # If we were unable to create a readonly connection then they must
+ # not have a db entry, so no need to clear.
+ # But it should be considered a success.
+ return 1;
+ }
+
+ return 0 unless (defined($self->{_dbh}));
+
+ my $rows = $self->{_dbh}->do("DELETE FROM bayes_vars WHERE id = ?",
+ undef,
+ $self->{_userid});
+ unless (defined($rows)) {
+ dbg("bayes: SQL error removing user (bayes_vars) data: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $rows = $self->{_dbh}->do("DELETE FROM bayes_seen WHERE id = ?",
+ undef,
+ $self->{_userid});
+ unless (defined($rows)) {
+ dbg("bayes: SQL error removing seen data: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $rows = $self->{_dbh}->do("DELETE FROM bayes_token WHERE id = ?",
+ undef,
+ $self->{_userid});
+ unless (defined($rows)) {
+ dbg("bayes: SQL error removing token data: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $self->{_dbh}->commit();
return 1;
}
=head1 Private Methods
+=head2 _connect_db
+
+private instance (Boolean) _connect_db ()
+
+Description:
+This method connects to the SQL database.
+
+=cut
+
+sub _connect_db {
+ my ($self) = @_;
+
+ $self->{_dbh} = undef;
+
+ # Turn off PrintError and explicitly set AutoCommit to off
+ my $dbh = DBI->connect($self->{_dsn}, $self->{_dbuser}, $self->{_dbpass},
+ {'PrintError' => 0, 'AutoCommit' => 0});
+
+ if (!$dbh) {
+ dbg("bayes: unable to connect to database: ".DBI->errstr());
+ return 0;
+ }
+ else {
+ dbg("bayes: database connection established");
+ }
+
+ $self->{_dbh} = $dbh;
+
+ return 1;
+}
+
=head2 _put_token
private instance (Boolean) _put_token (string $token,
@@ -399,28 +931,92 @@
$self->{needs_cleanup} = 1;
}
- my $sth = $self->{_dbh}->prepare("select put_token(?,?,?,?,?)");
+ my $escaped_token = _quote_bytea($_);
+ my $sth = $self->{_dbh}->prepare("select put_tokens($self->{_userid},'{$escaped_token}',
+ $spam_count,$ham_count,$atime)");
unless (defined($sth)) {
dbg("bayes: _put_token: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
return 0;
}
- $sth->bind_param(1, $self->{_userid});
- $sth->bind_param(2, $token, { pg_type => DBD::Pg::PG_BYTEA });
- $sth->bind_param(3, $spam_count);
- $sth->bind_param(4, $ham_count);
- $sth->bind_param(5, $atime);
-
my $rc = $sth->execute();
unless ($rc) {
dbg("bayes: _put_token: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
return 0;
}
$sth->finish();
+ $self->{_dbh}->commit();
+
+ return 1;
+}
+
+
+=head2 _put_tokens
+
+private instance (Boolean) _put_tokens (\% $token,
+ integer $spam_count,
+ integer $ham_count,
+ string $atime)
+
+Description:
+This method performs the work of either inserting or updating tokens in
+the database.
+
+=cut
+
+sub _put_tokens {
+ my ($self, $tokens, $spam_count, $ham_count, $atime) = @_;
+
+ return 0 unless (defined($self->{_dbh}));
+
+ $spam_count ||= 0;
+ $ham_count ||= 0;
+
+ if ($spam_count == 0 && $ham_count == 0) {
+ return 1;
+ }
+
+ if ($spam_count < 0 || $ham_count < 0) {
+ # we only need to cleanup when we subtract counts for a token and the
+ # counts may have both reached 0
+ # XXX - future optimization, since we have the existing spam/ham counts
+ # we can make an educated guess on if the count would reach 0, for
+ # instance, if we are decreasing spam_count but spam_count is currently
+ # > 1000, then there is no possible why this update or any others that
+ # might currently be happening could reduce that value to 0, so there
+ # would be no need to set the needs_cleanup flag
+ $self->{needs_cleanup} = 1;
+ }
+
+ my $tokenarray = join(",", map { '"' . _quote_bytea($_) . '"' } sort keys %{$tokens});
+
+ my $sth = $self->{_dbh}->prepare("select put_tokens($self->{_userid}, '{$tokenarray}',
+ $spam_count, $ham_count, $atime)");
+
+ unless (defined($sth)) {
+ dbg("bayes: _put_tokens: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ my $rc = $sth->execute();
+
+ unless ($rc) {
+ dbg("bayes: _put_tokens: SQL error: ".$self->{_dbh}->errstr());
+ $self->{_dbh}->rollback();
+ return 0;
+ }
+
+ $sth->finish();
+
+ $self->{_dbh}->commit();
+
return 1;
}
@@ -437,5 +1033,19 @@
sub _token_select_string {
return "token";
}
+
+sub _quote_bytea {
+ my ($str) = @_;
+ my $buf = "";
+ foreach my $char (split(//,$str)) {
+ my $oct = sprintf ("%lo", ord($char));
+ if (length( $oct ) < 2 ) { $oct = '0' . $oct; }
+ if (length( $oct ) < 3 ) { $oct = '0' . $oct; }
+ $buf .= '\\\\\\\\' . $oct;
+ }
+ return $buf;
+}
+
+sub sa_die { Mail::SpamAssassin::sa_die(@_); }
1;
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/SQL.pm
URL: http://svn.apache.org/viewcvs/spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/SQL.pm?rev=230697&r1=230696&r2=230697&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/SQL.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/BayesStore/SQL.pm Sun Aug 7 13:09:36 2005
@@ -137,7 +137,7 @@
return 0;
}
- unless ($self->_initialize_db()) {
+ unless ($self->_initialize_db(0)) {
dbg("bayes: unable to initialize database for ".$self->{_username}." user, aborting!");
$self->untie_db();
return 0;
@@ -186,6 +186,7 @@
unless ($self->_initialize_db(1)) {
dbg("bayes: unable to initialize database for ".$self->{_username}." user, aborting!");
+
$self->untie_db();
return 0;
}
@@ -937,7 +938,29 @@
$atime = 0 unless defined $atime;
- $self->_put_token ($token, $spam_count, $ham_count, $atime);
+ $self->_put_token($token, $spam_count, $ham_count, $atime);
+}
+
+=head2 multi_tok_count_change
+
+public instance (Boolean) multi_tok_count_change (Integer $spam_count,
+ Integer $ham_count,
+ \% $tokens,
+ String $atime)
+
+Description:
+This method takes a C<$spam_count> and C<$ham_count> and adds it to all
+of the tokens in the C<$tokens> hash ref along with updating each tokens
+atime with C<$atime>.
+
+=cut
+
+sub multi_tok_count_change {
+ my ($self, $spam_count, $ham_count, $tokens, $atime) = @_;
+
+ $atime = 0 unless defined $atime;
+
+ $self->_put_tokens($tokens, $spam_count, $ham_count, $atime);
}
=head2 nspam_nham_get
Modified: spamassassin/trunk/sql/bayes_pg.sql
URL: http://svn.apache.org/viewcvs/spamassassin/trunk/sql/bayes_pg.sql?rev=230697&r1=230696&r2=230697&view=diff
==============================================================================
--- spamassassin/trunk/sql/bayes_pg.sql (original)
+++ spamassassin/trunk/sql/bayes_pg.sql Sun Aug 7 13:09:36 2005
@@ -48,97 +48,65 @@
CREATE UNIQUE INDEX bayes_vars_idx1 ON bayes_vars (username);
-CREATE OR REPLACE FUNCTION put_token(integer, bytea, integer, integer, integer) RETURNS bool AS '
-DECLARE
- inuserid ALIAS for $1;
- intoken ALIAS for $2;
- inspam_count ALIAS for $3;
- inham_count ALIAS for $4;
- inatime ALIAS for $5;
- got_token record;
- updated_atime_p bool;
+CREATE OR REPLACE FUNCTION greatest_int (integer, integer)
+ RETURNS INTEGER
+ IMMUTABLE STRICT
+ AS 'SELECT CASE WHEN $1 < $2 THEN $2 ELSE $1 END;'
+ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION least_int (integer, integer)
+ RETURNS INTEGER
+ IMMUTABLE STRICT
+ AS 'SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END;'
+ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION put_tokens(inuserid INTEGER,
+ intokenary BYTEA[],
+ inspam_count INTEGER,
+ inham_count INTEGER,
+ inatime INTEGER)
+RETURNS VOID AS '
+DECLARE
+ _token BYTEA;
+ new_tokens INTEGER := 0;
BEGIN
- updated_atime_p := FALSE;
- SELECT INTO got_token spam_count, ham_count, atime
- FROM bayes_token
- WHERE id = inuserid
- AND token = intoken;
- IF NOT FOUND THEN
- -- we do not insert negative counts, just return true
- IF (inspam_count < 0 OR inham_count < 0) THEN
- RETURN TRUE;
- END IF;
- INSERT INTO bayes_token (id, token, spam_count, ham_count, atime)
- VALUES (inuserid, intoken, inspam_count, inham_count, inatime);
- IF NOT FOUND THEN
- RAISE EXCEPTION ''unable to insert into bayes_token'';
- return FALSE;
- END IF;
- UPDATE bayes_vars SET token_count = token_count + 1
- WHERE id = inuserid;
- IF NOT FOUND THEN
- RAISE EXCEPTION ''unable to update token_count in bayes_vars'';
- return FALSE;
- END IF;
- UPDATE bayes_vars SET newest_token_age = inatime
- WHERE id = inuserid AND newest_token_age < inatime;
- IF NOT FOUND THEN
- UPDATE bayes_vars
- SET oldest_token_age = inatime
- WHERE id = inuserid
- AND oldest_token_age > inatime;
- END IF;
- return TRUE;
- ELSE
- IF (inspam_count != 0) THEN
- -- no need to update atime if it is < the existing value
- IF (inatime < got_token.atime) THEN
- UPDATE bayes_token
- SET spam_count = spam_count + inspam_count
- WHERE id = inuserid
- AND token = intoken
- AND spam_count + inspam_count >= 0;
- ELSE
- UPDATE bayes_token
- SET spam_count = spam_count + inspam_count,
- atime = inatime
- WHERE id = inuserid
- AND token = intoken
- AND spam_count + inspam_count >= 0;
+ for i in array_lower(intokenary, 1) .. array_upper(intokenary, 1)
+ LOOP
+ _token := intokenary[i];
+ UPDATE bayes_token
+ 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 = _token;
+ IF NOT FOUND THEN
+ -- we do not insert negative counts, just return true
+ IF NOT (inspam_count < 0 OR inham_count < 0) THEN
+ INSERT INTO bayes_token (id, token, spam_count, ham_count, atime)
+ VALUES (inuserid, _token, inspam_count, inham_count, inatime);
IF FOUND THEN
- updated_atime_p := TRUE;
+ new_tokens := new_tokens + 1;
END IF;
END IF;
END IF;
- IF (inham_count != 0) THEN
- -- no need to update atime is < the existing value or if it was already updated
- IF inatime < got_token.atime OR updated_atime_p THEN
- UPDATE bayes_token
- SET ham_count = ham_count + inham_count
- WHERE id = inuserid
- AND token = intoken
- AND ham_count + inham_count >= 0;
- ELSE
- UPDATE bayes_token
- SET ham_count = ham_count + inham_count,
- atime = inatime
- WHERE id = inuserid
- AND token = intoken
- AND ham_count + inham_count >= 0;
- IF FOUND THEN
- updated_atime_p := TRUE;
- END IF;
- END IF;
- END IF;
- IF updated_atime_p THEN
- UPDATE bayes_vars
- SET oldest_token_age = inatime
- WHERE id = inuserid
- AND oldest_token_age > inatime;
- END IF;
- return TRUE;
+ END LOOP;
+
+ IF new_tokens > 0 AND inatime > 0 THEN
+ UPDATE bayes_vars
+ SET token_count = token_count + new_tokens,
+ newest_token_age = greatest_int(newest_token_age, inatime),
+ oldest_token_age = least_int(oldest_token_age, inatime)
+ WHERE id = inuserid;
+ ELSEIF new_tokens > 0 AND NOT inatime > 0 THEN
+ UPDATE bayes_vars
+ SET token_count = token_count + new_tokens
+ WHERE id = inuserid;
+ ELSEIF NOT new_tokens > 0 AND inatime > 0 THEN
+ UPDATE bayes_vars
+ SET newest_token_age = greatest_int(newest_token_age, inatime),
+ oldest_token_age = least_int(oldest_token_age, inatime)
+ WHERE id = inuserid;
END IF;
+ RETURN;
END;
' LANGUAGE 'plpgsql';
-
-