Attachment #3194 for bug #4508

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





  $self->{supported_db_version} = 3;

  #checks if bayes_read_write_support is enabled
  my $bayes_read_write_support=$self->{bayes}->{conf}->{bayes_read_write_support};
  
  $self->{bayes_read_write_support} = $self->{bayes}->{conf}->{bayes_read_write_support};

  if($bayes_read_write_support) {
    if (!$self->{bayes}->{conf}->{bayes_sql_dsn_read} || !$self->{bayes}->{conf}->{bayes_sql_dsn_write} || $self->{bayes}->{conf}->{bayes_sql_dsn} ) {
      dbg("bayes: invalid config, must set bayes_sql_dsn_write and bayes_sql_dsn_read and not bayes_sql_dsn config variable.\n");
      return undef;
    } 	
  }
  else
  {
    if (!$self->{bayes}->{conf}->{bayes_sql_dsn} || $self->{bayes}->{conf}->{bayes_sql_dsn_write} || $self->{bayes}->{conf}->{bayes_sql_dsn_read} ) {
      dbg("bayes: invalid config, must set bayes_sql_dsn and not bayes_sql_dsn_write and not bayes_sql_dsn_read config variable.\n");
      return undef;
    }
  }

  $self->{_dsn} = $self->{bayes}->{conf}->{bayes_sql_dsn};
  $self->{_dbuser} = $self->{bayes}->{conf}->{bayes_sql_username};
  $self->{_dbpass} = $self->{bayes}->{conf}->{bayes_sql_password};

  $self->{_dsn_write} = $self->{bayes}->{conf}->{bayes_sql_dsn_write};
  $self->{_dbuser_write} = $self->{bayes}->{conf}->{bayes_sql_username_write};
  $self->{_dbpass_write} = $self->{bayes}->{conf}->{bayes_sql_password_write};

  $self->{_dsn_read} = $self->{bayes}->{conf}->{bayes_sql_dsn_read};
  $self->{_dbuser_read} = $self->{bayes}->{conf}->{bayes_sql_username_read};
  $self->{_dbpass_read} = $self->{bayes}->{conf}->{bayes_sql_password_read};

  $self->{_dbh} = undef;
  $self->{_dbh_write} = undef;
  $self->{_dbh_read} = undef;

  unless (HAS_DBI) {
    dbg("bayes: Unable to connect to database: DBI module not available: $!");


sub tie_db_writable {
  my ($self) = @_;
  my $dbh;
  my $dbh_read;
  my $dbh_write;

  return 0 unless (HAS_DBI);

  if($self->{bayes_read_write_support}) {
    if($self->{_dbh_write} && $self->{_dbh_read}) {
      return 1;#already connected readable and writeable 
    }
  }
  else
  {
    return 1 if ($self->{_dbh}); # already connected
  }

  my $main = $self->{bayes}->{main};

  $self->read_db_configs();

  if($self->{bayes_read_write_support}) {
    # connect writeable / Turn off PrintError and explicitly set AutoCommit to off
     $dbh_write = DBI->connect($self->{_dsn_write}, $self->{_dbuser_write}, $self->{_dbpass_write},
	    		   {'PrintError' => 0, 'AutoCommit' => 1});

    # connect readable / Turn off PrintError and explicitly set AutoCommit to off
     $dbh_read = DBI->connect($self->{_dsn_read}, $self->{_dbuser_read}, $self->{_dbpass_read},
	    		   {'PrintError' => 0, 'AutoCommit' => 1});
  }
  else {
    # Turn off PrintError and explicitly set AutoCommit to off
    $dbh = DBI->connect($self->{_dsn}, $self->{_dbuser}, $self->{_dbpass},
	    		   {'PrintError' => 0, 'AutoCommit' => 1});
  }


  if($self->{bayes_read_write_support}) {
    if ($dbh_read && $dbh_write) {
       dbg("bayes: Readable Database connection established with $self->{_dsn_read} ");
       dbg("bayes: Writeable Database connection established with $self->{_dsn_write} ");
    } 
    else {
       dbg("bayes: Unable to connect to database: $self->{_dsn_read} ");
       dbg("bayes: Unable to connect to database: $self->{_dsn_write} ");
       return 0;
    }
  }
  else {
    if (!$dbh) {
      dbg("bayes: Unable to connect to database: ".DBI->errstr());
      return 0;
    }
    else {
       dbg("bayes: Database connection established with $self->{_dsn}");
    }
  }

  if($self->{bayes_read_write_support}) {
    $self->{_dbh_read} = $dbh_read;
    $self->{_dbh_write} = $dbh_write;
  }
  else {
    $self->{_dbh} = $dbh;
  }

  # If the DB version is one we don't understand, abort!
  my $db_ver = $self->_get_db_version();

sub untie_db {
  my ($self) = @_;

  if($self->{bayes_read_write_support}) {
    return unless ( (defined($self->{_dbh_read})) && (defined($self->{_dbh_write})) );
    $self->{_dbh_write}->disconnect();
    $self->{_dbh_read}->disconnect();
    $self->{_dbh_read} = undef;
    $self->{_dbh_write} = undef;
  }
  else {
    return unless (defined($self->{_dbh}));
    $self->{_dbh}->disconnect();
    $self->{_dbh} = undef;
  }

  $self->{_dbh}->disconnect();
  $self->{_dbh} = undef;
}

=head2 calculate_expire_delta


sub calculate_expire_delta {
  my ($self, $newest_atime, $start, $max_expire_mult) = @_;
  my $sth;
  my %delta = (); # use a hash since an array is going to be very sparse

  if($self->{bayes_read_write_support}) {
    return %delta unless ( (defined($self->{_dbh_write})) && (defined($self->{_dbh_read})) );
  }
  else {
    return %delta unless (defined($self->{_dbh}));
  }

  my $sql = "SELECT count(*)
               FROM bayes_token
              WHERE id = ?
                AND (? - atime) > ?";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare_cached($sql);
  }
  else { 
    $sth = $self->{_dbh}->prepare_cached($sql);
  }
    
  for (my $i = 1; $i <= $max_expire_mult; $i<<=1) {
    my $rc = $sth->execute($self->{_userid}, $newest_atime, $start * $i);

    unless ($rc) {
      if($self->{bayes_read_write_support}) {
        dbg("bayes: calculate_expire_delta: SQL Error: ".$self->{_dbh_read}->errstr());
        return undef;
      }
      else {
        dbg("bayes: calculate_expire_delta: SQL Error: ".$self->{_dbh}->errstr());
        return undef;
      }
    }

    my ($count) = $sth->fetchrow_array();

  my $num_hapaxes;
  my $num_lowfreq;
  my $deleted;
  my $rows;
  my $sth;

  # Figure out how old is too old...
  my $too_old = $vars[10] - $newdelta; # tooold = newest - delta

              WHERE id  = ?
                AND atime > ?";

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $vars[10], $self->{_userid}, $vars[10]);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $vars[10], $self->{_userid}, $vars[10]);
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh}->errstr());
    }
 
    $deleted = 0;
    goto token_expiration_final;
  }

           WHERE id = ?
             AND atime < ?";


  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare_cached($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare_cached($sql);
  }

  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh}->errstr());
    }

    $deleted = 0;
    goto token_expiration_final;
  }

  my $rc = $sth->execute($self->{_userid}, $too_old);
  
  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh}->errstr());
    }

    $deleted = 0;
    goto token_expiration_final;
  }

             WHERE id = ?
               AND atime < ?";

    if($self->{bayes_read_write_support}) {
      $rows = $self->{_dbh_write}->do($sql, undef, $self->{_userid}, $too_old);
    }
    else {
      $rows = $self->{_dbh}->do($sql, undef, $self->{_userid}, $too_old);
    }

    unless (defined($rows)) {
      if($self->{bayes_read_write_support}) {
        dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh_write}->errstr());
      }
      else {
        dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh}->errstr());
      }

      $deleted = 0;
      goto token_expiration_final;
    }

                                last_expire_reduce = ?
				WHERE id = ?";


  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $deleted, time(), $newdelta, $deleted, $self->{_userid});
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $deleted, time(), $newdelta, $deleted, $self->{_userid});
  }

  unless (defined($rows)) {
    # Very bad, we actually deleted the tokens, but were unable to update
    # bayes_vars with the new data.
    if($self->{bayes_read_write_support}) {
      dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh}->errstr());
    }

    dbg("bayes: Bayes database now in inconsistent state, suggest a backup/restore.");
    goto token_expiration_final;
  }


    $sql = "UPDATE bayes_vars SET oldest_token_age = ? WHERE id = ?";

    if($self->{bayes_read_write_support}) {
      $rows = $self->{_dbh_write}->do($sql, undef, $oldest_token_age, $self->{_userid});
    }
    else {
      $rows = $self->{_dbh}->do($sql, undef, $oldest_token_age, $self->{_userid});
    }

    unless (defined($rows)) {
      # not much more we can do here, so just warn the user and bail out
      if($self->{bayes_read_write_support}) {
        dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh_write}->errstr());
      }
      else {
        dbg("bayes: token_expiration: SQL Error: ".$self->{_dbh}->errstr());
      }

      # yeah I know it's the next thing anyway, but here in case someone adds
      # additional code below this block
      goto token_expiration_final; 


sub seen_get {
  my ($self, $msgid) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return undef unless ( (defined($self->{_dbh_read})) && (defined($self->{_dbh_write})) );
  }
  else { 
    return undef unless (defined($self->{_dbh}));
  }
 
  my $sql = "SELECT flag FROM bayes_seen
              WHERE id = ?
                AND msgid = ?";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare_cached($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare_cached($sql);
  }

  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: seen_get: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: seen_get: SQL Error: ".$self->{_dbh}->errstr());
    }

    return undef;
  }

  my $rc = $sth->execute($self->{_userid}, $msgid);
  
  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: seen_get: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: seen_get: SQL Error: ".$self->{_dbh}->errstr());
    }

    return undef;
  }



sub seen_put {
  my ($self, $msgid, $flag) = @_;
  my $rows;

  return 0 if (!$msgid);
  return 0 if (!$flag);
  

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read})  && defined($self->{_dbh_write}) );
  }
  else {
    return 0 unless (defined($self->{_dbh}));
  }

  my $sql = "INSERT INTO bayes_seen (id, msgid, flag)
             VALUES (?,?,?)";

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $self->{_userid}, $msgid, $flag);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $self->{_userid}, $msgid, $flag);
  } 
 
  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: seen_put: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: seen_put: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub seen_delete {
  my ($self, $msgid) = @_;
  my $rows;

  return 0 if (!$msgid);

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }
  else {
    return 0 unless (defined($self->{_dbh}));
  }

  my $sql = "DELETE FROM bayes_seen
              WHERE id = ?
                AND msgid = ?";
  
  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $self->{_userid}, $msgid);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $self->{_userid}, $msgid);
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: seen_delete: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: seen_delete: SQL Error: ".$self->{_dbh}->errstr());
    }
    return 0;
  }


sub get_storage_variables {
  my ($self) = @_;
  my @values;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return (0,0,0,0,0,0,0,0,0,0,0) unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }
  else {
    return (0,0,0,0,0,0,0,0,0,0,0) unless (defined($self->{_dbh}));
  }

  my $sql = "SELECT spam_count, ham_count, token_count, last_expire,
                    last_atime_delta, last_expire_reduce, oldest_token_age,

               FROM bayes_vars
              WHERE id = ?";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_write}->prepare_cached($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare_cached($sql);
  }


  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: get_storage_variables: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: get_storage_variables: SQL Error: ".$self->{_dbh}->errstr());
    }

    return (0,0,0,0,0,0,0,0,0,0,0);
  }

  my $rc = $sth->execute($self->{_userid});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: get_storage_variables: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: get_storage_variables: SQL Error: ".$self->{_dbh}->errstr());
    }

    return (0,0,0,0,0,0,0,0,0,0,0);
  }



sub dump_db_toks {
  my ($self, $template, $regex, @vars) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }
  else {
    return unless (defined($self->{_dbh}));
  }

  # 0/0 tokens don't count, but in theory we shouldn't have any
  # use RPAD to make sure we get trailing spaces in the token value

              WHERE id = ?
                AND (spam_count > 0 OR ham_count > 0)";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare($sql);
  }


  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: dump_db_toks: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: dump_db_toks: SQL Error: ".$self->{_dbh}->errstr());
    }

    return;
  }

  my $rc = $sth->execute($self->{_userid});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: dump_db_toks: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: dump_db_toks: SQL Error: ".$self->{_dbh}->errstr());
    }

    return;
  }  



sub set_last_expire {
  my ($self, $time) = @_;
  my $rows;

  return 0 unless (defined($time));

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }
  else {
    return 0 unless (defined($self->{_dbh}));
  }

  my $sql = "UPDATE bayes_vars SET last_expire = ? WHERE id = ?";
 
  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $time, $self->{_userid});
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $time, $self->{_userid});
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: set_last_expire: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: set_last_expire: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub get_running_expire_tok {
  my ($self) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }
  else {
    return 0 unless (defined($self->{_dbh}));
  }

  my $sql = "SELECT max(runtime) from bayes_expire WHERE id = ?";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare_cached($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare_cached($sql);
  }

  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: get_running_expire_tok: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: get_running_expire_tok: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }

  my $rc = $sth->execute($self->{_userid});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: get_running_expire_tok: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: get_running_expire_tok: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub set_running_expire_tok {
  my ($self) = @_;
  my $rows;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  my $sql = "INSERT INTO bayes_expire (id,runtime) VALUES (?,?)";

  my $time = time();

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $self->{_userid}, $time);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $self->{_userid}, $time);
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: set_running_expire_tok: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: set_running_expire_tok: SQL Error: ".$self->{_dbh}->errstr());
    }

    return undef;
  }



sub remove_running_expire_tok {
  my ($self) = @_;
  my $rows;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  my $sql = "DELETE from bayes_expire
              WHERE id = ?";


  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $self->{_userid});
  }     
  else {
    $rows = $self->{_dbh}->do($sql, undef, $self->{_userid});
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: remove_running_expire_tok: SQL Error: ".$self->{_dbh_write}->errstr());
    }     
    else {
      dbg("bayes: remove_running_expire_tok: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub tok_get {
  my ($self, $token) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return (0,0,0) unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return (0,0,0) unless (defined($self->{_dbh}));
  }

  my $sql = "SELECT spam_count, ham_count, atime
               FROM bayes_token
              WHERE id = ?
                AND token = ?";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare_cached($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare_cached($sql);
  }

  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: tok_get: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: tok_get: SQL Error: ".$self->{_dbh}->errstr());
    }

    return (0,0,0);
  }

  my $rc = $sth->execute($self->{_userid}, $token);

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: tok_get: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: tok_get: SQL Error: ".$self->{_dbh}->errstr());
    }

    return (0,0,0);
  }



sub tok_get_all {
  my ($self, @tokens) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return [] unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }
  else { 
    return [] unless (defined($self->{_dbh}));
  }

  my $token_list_size = scalar(@tokens);
  dbg("bayes: tok_get_all: Token Count: $token_list_size");


      my $dynamic_sql = $multi_sql . $in_str;

      if($self->{bayes_read_write_support}) {
        $sth = $self->{_dbh_read}->prepare($dynamic_sql);
      }
      else {
        $sth = $self->{_dbh}->prepare($dynamic_sql);
      } 

      unless (defined($sth)) {
        if($self->{bayes_read_write_support}) {
	  dbg("bayes: tok_get_all: SQL Error: ".$self->{_dbh_read}->errstr());
        }
        else {
	  dbg("bayes: tok_get_all: SQL Error: ".$self->{_dbh}->errstr());
        }

	return [];
      }

      my $rc = $sth->execute($self->{_userid}, @bindings);

      unless ($rc) {
        if($self->{bayes_read_write_support}) {
	  dbg("bayes: tok_get_all: SQL Error: ".$self->{_dbh_read}->errstr());
        }
        else {
	  dbg("bayes: tok_get_all: SQL Error: ".$self->{_dbh}->errstr());
        }

	return [];
      }


  }

  while ($search_index < $token_list_size) {
    if($self->{bayes_read_write_support}) {
      $sth = $self->{_dbh_read}->prepare($single_sql);
    }
    else {
      $sth = $self->{_dbh}->prepare($single_sql);
    }


    unless (defined($sth)) {
        if($self->{bayes_read_write_support}) {
	  dbg("bayes: tok_get_all: SQL Error: ".$self->{_dbh_read}->errstr());
        }
        else {
	  dbg("bayes: tok_get_all: SQL Error: ".$self->{_dbh}->errstr());
        }

      return [];
    }

    my $rc = $sth->execute($self->{_userid}, $tokens[$search_index++]);

    unless ($rc) {
        if($self->{bayes_read_write_support}) {
	  dbg("bayes: tok_get_all: SQL Error: ".$self->{_dbh_read}->errstr());
        }
        else {
	  dbg("bayes: tok_get_all: SQL Error: ".$self->{_dbh}->errstr());
        }

      return [];
    }


sub nspam_nham_get {
  my ($self) = @_;

  if($self->{bayes_read_write_support}) { 
    return (0,0) unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }
  else {
    return (0,0) unless (defined($self->{_dbh}));
  }

  my @vars = $self->get_storage_variables();


sub nspam_nham_change {
  my ($self, $num_spam, $num_ham) = @_;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  my $sql;
  my $rows;
  my @bindings;

  if ($num_spam != 0 && $num_ham != 0) {

    return 1;
  }

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, @bindings);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, @bindings);
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: nspam_nham_change: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: nspam_nham_change: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub tok_touch {
  my ($self, $token, $atime) = @_;
  my $rows;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  # shortcut, will only update atime for the token if the atime is less than
  # what we are updating to

                AND token = ?
                AND atime < ?";

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $atime, $self->{_userid}, $token, $atime);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $atime, $self->{_userid}, $token, $atime);
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: tok_touch: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: tok_touch: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }


           WHERE id = ?
             AND newest_token_age < ?";

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $atime, $self->{_userid}, $atime);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $atime, $self->{_userid}, $atime);
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: tok_touch: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: tok_touch: SQL Error: ".$self->{_dbh}->errstr());
    }
    return 0;
  }



sub tok_touch_all {
  my ($self, $tokens, $atime) = @_;
  my $rows;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

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


  $sql .= ") AND atime < ?";
  push(@bindings, $atime);

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, @bindings);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, @bindings);
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: tok_touch_all: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: tok_touch_all: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }


           WHERE id = ?
             AND newest_token_age < ?";


  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $atime, $self->{_userid}, $atime);
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $atime, $self->{_userid}, $atime);
  }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: tok_touch_all: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: tok_touch_all: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub cleanup {
  my ($self) = @_;
  my $toks_deleted;
  my $rows;

  return 1 unless ($self->{needs_cleanup});


                AND spam_count = 0
                AND ham_count = 0";

  if($self->{bayes_read_write_support}) {
    $toks_deleted = $self->{_dbh_write}->do($sql, undef, $self->{_userid});
  }
  else {
    $toks_deleted = $self->{_dbh}->do($sql, undef, $self->{_userid});
  }

  unless (defined($toks_deleted)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: cleanup: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: cleanup: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }       


  $sql = "UPDATE bayes_vars SET token_count = token_count - $toks_deleted
           WHERE id = ?";

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sql, undef, $self->{_userid});
  }
  else {
    $rows = $self->{_dbh}->do($sql, undef, $self->{_userid});
  }


  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: cleanup: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: cleanup: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }       



sub clear_database {
  my ($self) = @_;
  my $rows;

  $self->tie_db_writable();

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do("DELETE FROM bayes_vars WHERE id = ?", undef, $self->{_userid});
  }
  else {
    $rows = $self->{_dbh}->do("DELETE FROM bayes_vars WHERE id = ?", undef, $self->{_userid});
  }

  my $rows = $self->{_dbh}->do("DELETE FROM bayes_vars WHERE id = ?",
			       undef,
			       $self->{_userid});
  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("SQL Error removing user (bayes_vars) data: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("SQL Error removing user (bayes_vars) data: ".$self->{_dbh}->errstr());
    }

    return 0;
  }

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write }->do("DELETE FROM bayes_seen WHERE id = ?", undef, $self->{_userid});
  }
  else {
    $rows = $self->{_dbh}->do("DELETE FROM bayes_seen WHERE id = ?", undef, $self->{_userid});
  }


  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("SQL Error removing seen data: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("SQL Error removing seen data: ".$self->{_dbh}->errstr());
    }

    return 0;
  }


    if($self->{bayes_read_write_support}) {
      $rows = $self->{_dbh_write}->do("DELETE FROM bayes_token WHERE id = ?", undef, $self->{_userid});
    }
    else {
      $rows = $self->{_dbh}->do("DELETE FROM bayes_token WHERE id = ?", undef, $self->{_userid});
    }

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("SQL Error removing token data: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("SQL Error removing token data: ".$self->{_dbh}->errstr());
    }
    return 0;
  }



sub backup_database {
  my ($self) = @_;
  my $sth;

  return 0 unless ($self->tie_db_readonly());

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  my @vars = $self->get_storage_variables();


                    FROM bayes_seen
                   WHERE id = ?";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_write}->prepare($token_sql);
  }
  else {
    $sth = $self->{_dbh}->prepare($token_sql);
  }

  unless (defined ($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: backup_database: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: backup_database: SQL Error: ".$self->{_dbh}->errstr());
    } 

    return 0;
  }

  my $rc = $sth->execute($self->{_userid});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: backup_database: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: backup_database: SQL Error: ".$self->{_dbh}->errstr());
    } 

    return 0;
  }



  $sth->finish();

    if($self->{bayes_read_write_support}) {
      $sth = $self->{_dbh_read}->prepare($seen_sql);
    }
    else {
      $sth = $self->{_dbh}->prepare($seen_sql);
    }

  unless (defined ($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: backup_database: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: backup_database: SQL Error: ".$self->{_dbh}->errstr());
    } 

    return 0;
  }

  $rc = $sth->execute($self->{_userid});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: backup_database: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: backup_database: SQL Error: ".$self->{_dbh}->errstr());
    } 

    return 0;
  }



  return 0 unless ($self->tie_db_writable());

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     
 
  # This is the critical phase (moving sql around), so don't allow it
  # to be interrupted.
  local $SIG{'INT'} = 'IGNORE';


sub _get_db_version {
  my ($self) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  return ($self->{_db_version_cache}) if (defined($self->{_db_version_cache}));

  my $sql = "SELECT value FROM bayes_global_vars WHERE variable = 'VERSION'";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare_cached($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare_cached($sql);
  }

  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _get_db_version: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _get_db_version: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }

  my $rc = $sth->execute();

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _get_db_version: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _get_db_version: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub _initialize_db {
  my ($self) = @_;
  my $sthselect;
  my $rows;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  return 0 if (!$self->{_username});

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

  if($self->{bayes_read_write_support}) {
    $sthselect = $self->{_dbh_read}->prepare_cached($sqlselect);
  }
  else {
    $sthselect = $self->{_dbh}->prepare_cached($sqlselect);
  }

  unless (defined($sthselect)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _initialize_db: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _initialize_db: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }

  my $rc = $sthselect->execute($self->{_username});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _initialize_db: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _initialize_db: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }


  # For now let the database setup the other variables as defaults
  my $sqlinsert = "INSERT INTO bayes_vars (username) VALUES (?)";

  if($self->{bayes_read_write_support}) {
    $rows = $self->{_dbh_write}->do($sqlinsert, undef, $self->{_username});
  }
  else {
    $rows = $self->{_dbh}->do($sqlinsert, undef, $self->{_username});
  } 

  unless (defined($rows)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _initialize_db: SQL Error: ".$self->{_dbh_write}->errstr());
    }
    else {
      dbg("bayes: _initialize_db: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }


  $rc = $sthselect->execute($self->{_username});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _initialize_db: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _initialize_db: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub _put_token {
  my ($self, $token, $spam_count, $ham_count, $atime) = @_;
  my $sth;
  my $rows;


  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  $spam_count ||= 0;
  $ham_count ||= 0;

               (id, token, spam_count, ham_count, atime)
               VALUES (?,?,?,?,?)";

    if($self->{bayes_read_write_support}) {
        $sth = $self->{_dbh_write}->prepare_cached($sql);
    }
    else {
        $sth = $self->{_dbh}->prepare_cached($sql);
    }

    unless (defined($sth)) {
      if($self->{bayes_read_write_support}) {
        dbg("bayes: _put_token: SQL Error: ".$self->{_dbh_write}->errstr());
      }
      else {
        dbg("bayes: _put_token: SQL Error: ".$self->{_dbh}->errstr());
      }

      return 0;
    }


			   $atime);
    
    unless ($rc) {
      if($self->{bayes_read_write_support}) {
        dbg("bayes: _put_token: SQL Error: ".$self->{_dbh_write}->errstr());
      }
      else {
        dbg("bayes: _put_token: SQL Error: ".$self->{_dbh}->errstr());
      }

      return 0;
    }


    $sql = "UPDATE bayes_vars SET token_count = token_count + 1
             WHERE id = ?";

    if($self->{bayes_read_write_support}) {
      $rows = $self->{_dbh_write}->do($sql, undef, $self->{_userid});
    }
    else {
      $rows = $self->{_dbh}->do($sql, undef, $self->{_userid});
    }
    
    unless (defined($rows)) {
      if($self->{bayes_read_write_support}) {
        dbg("bayes: _put_token: SQL Error: ".$self->{_dbh_write}->errstr());
      }
      else {
        dbg("bayes: _put_token: SQL Error: ".$self->{_dbh}->errstr());
      }
     
      return 0;
    }

    $sql = "UPDATE bayes_vars SET newest_token_age = ?
             WHERE id = ? AND newest_token_age < ?";

    if($self->{bayes_read_write_support}) {
      $rows = $self->{_dbh_write}->do($sql, undef, $atime, $self->{_userid}, $atime);
    }
    else {
      $rows = $self->{_dbh}->do($sql, undef, $atime, $self->{_userid}, $atime);
    }

    unless (defined($rows)) {
      if($self->{bayes_read_write_support}) {
        dbg("bayes: _put_token: SQL Error: ".$self->{_dbh_write}->errstr());
      }
      else {
        dbg("bayes: _put_token: SQL Error: ".$self->{_dbh}->errstr());
      }

      return 0;
    }


      $sql = "UPDATE bayes_vars SET oldest_token_age = ?
               WHERE id = ? AND oldest_token_age > ?";

       if($self->{bayes_read_write_support}) {
         $rows = $self->{_dbh_write}->do($sql, undef, $atime, $self->{_userid}, $atime);
       }
       else { 
         $rows = $self->{_dbh}->do($sql, undef, $atime, $self->{_userid}, $atime);
       }
      
      unless (defined($rows)) {
        if($self->{bayes_read_write_support}) {
          dbg("bayes: _put_token: SQL Error: ".$self->{_dbh_write}->errstr());
        }
        else {
          dbg("bayes: _put_token: SQL Error: ".$self->{_dbh}->errstr());
        }

	return 0;
      }
    }

                   AND spam_count + ? >= 0";
	@args = ($spam_count, $self->{_userid}, $token, $spam_count);
      }
     
     if($self->{bayes_read_write_support}) {
        $rows = $self->{_dbh_write}->do($sql, undef, @args);
      }     
      else {
        $rows = $self->{_dbh}->do($sql, undef, @args);
      }

      unless (defined($rows)) {
        if($self->{bayes_read_write_support}) {
          dbg("bayes: _put_token: SQL Error: ".$self->{_dbh_write}->errstr());
        }
        else {
          dbg("bayes: _put_token: SQL Error: ".$self->{_dbh}->errstr());
        }

	return 0;
      }
    }

                   AND ham_count + ? >= 0";
	@args = ($ham_count, $self->{_userid}, $token, $ham_count);
      }
   
      if($self->{bayes_read_write_support}) {
        $rows = $self->{_dbh_write}->do($sql, undef, @args);
      }
      else {
        $rows = $self->{_dbh}->do($sql, undef, @args);
      }

      unless (defined($rows)) {
        if($self->{bayes_read_write_support}) {
          dbg("bayes: _put_token: SQL Error: ".$self->{_dbh_write}->errstr());
        }
        else {
          dbg("bayes: _put_token: SQL Error: ".$self->{_dbh}->errstr());
        }

	return 0;
      }
    }

      # only updated the atime if it was > the previous value
      my $sql = "UPDATE bayes_vars SET newest_token_age = ?
                  WHERE id = ? AND newest_token_age < ?";
      
      if($self->{bayes_read_write_support}) { 	
        $rows = $self->{_dbh_write}->do($sql, undef, $atime, $self->{_userid}, $atime);
      }
      else {
        $rows = $self->{_dbh}->do($sql, undef, $atime, $self->{_userid}, $atime);
      }

      my $rows = $self->{_dbh}->do($sql, undef, $atime, $self->{_userid}, $atime);

      unless (defined($rows)) {
        if($self->{bayes_read_write_support}) {
          dbg("bayes: _put_token: SQL Error: ".$self->{_dbh_write}->errstr());
        }
        else {
          dbg("bayes: _put_token: SQL Error: ".$self->{_dbh}->errstr());
        }

	return 0;
      }
    }


sub _get_oldest_token_age {
  my ($self) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  my $sql = "SELECT min(atime) FROM bayes_token
              WHERE id = ?";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare_cached($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare_cached($sql);
  }

  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _get_oldest_token_age: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _get_oldest_token_age: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }

  my $rc = $sth->execute($self->{_userid});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _get_oldest_token_age: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _get_oldest_token_age: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }


  return $atime;
}


=head2 _get_num_hapaxes

private instance (Integer) _get_num_hapaxes ()


sub _get_num_hapaxes {
  my ($self) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     
 
  my $sql = "SELECT count(*)
               FROM bayes_token
              WHERE id = ?
                AND spam_count + ham_count = 1";

   if($self->{bayes_read_write_support}) {
     $sth = $self->{_dbh_read}->prepare_cached($sql);
   }
   else {
     $sth = $self->{_dbh}->prepare_cached($sql);
   }

  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _get_num_hapaxes: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _get_num_hapaxes: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }

  my $rc = $sth->execute($self->{_userid});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _get_num_hapaxes: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _get_num_hapaxes: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }



sub _get_num_lowfreq {
  my ($self) = @_;
  my $sth;

  if($self->{bayes_read_write_support}) {
    return 0 unless ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return 0 unless (defined($self->{_dbh}));
  }     

  my $sql = "SELECT count(*)
               FROM bayes_token

                AND (ham_count >= 0 AND ham_count < 8)
                AND spam_count + ham_count != 1";

  if($self->{bayes_read_write_support}) {
    $sth = $self->{_dbh_read}->prepare_cached($sql);
  }
  else {
    $sth = $self->{_dbh}->prepare_cached($sql);
  }

  unless (defined($sth)) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _get_num_lowfreq: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _get_num_lowfreq: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }

  my $rc = $sth->execute($self->{_userid});

  unless ($rc) {
    if($self->{bayes_read_write_support}) {
      dbg("bayes: _get_num_lowfreq: SQL Error: ".$self->{_dbh_read}->errstr());
    }
    else {
      dbg("bayes: _get_num_lowfreq: SQL Error: ".$self->{_dbh}->errstr());
    }

    return 0;
  }


  my($self) = @_;

  # if there's a database handle, we can read...
  if($self->{bayes_read_write_support}) {
    return ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }     
  else {
    return defined($self->{_dbh});
  }     

}

sub db_writable {


  # since in the SA SQL code, there is no difference between R/O and
  # R/W access, we just care if we have DB access.
  if($self->{bayes_read_write_support}) {
    return ( defined($self->{_dbh_read}) && defined($self->{_dbh_write}) );
  }
  else {
    return defined($self->{_dbh});
  }
}

sub dbg { Mail::SpamAssassin::dbg (@_); }





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

To use 2 different Databases (for example : You have mysql replication. Than you can write to the master and read from the slave

in general localhost.) you can enable bayes_read_write_support.

New Bayes Vars here :

bayes_read_write_support BOOL

bayes_sql_dsn_read

bayes_sql_username_read

bayes_sql_password_read

bayes_sql_dsn_write

bayes_sql_username_write

bayes_sql_password_write

=cut

  push (@cmds, {
    setting => 'bayes_read_write_support',
    is_admin => 1,
    default => 0,
    type => $CONF_TYPE_BOOL
  });

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

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

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

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

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

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


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

Used for BayesStore::SQL storage implementation.

This option give the connect string used to connect to the SQL based Bayes storage.

=item user_scores_sql_custom_query query

This option gives you the ability to create a custom SQL query to
Retrieve user scores and preferences.  In order to work correctly your
query should return two values, the preference name and value, in that
order.  In addition, there are several "variables" that you can use
as part of your query, these variables will be substituted for the

    setting => 'user_awl_sql_table',
    is_admin => 1,
    default => 'awl',
    type => $CONF_TYPE_STRING
  });

=item user_awl_sql_read_write_support 

This patch make it possible to read from one host and write to another.
Very usefull for mysql replication.

if user_awl_sql_read_write_support is true, you have following extra options :

user_awl_dsn_read
user_awl_sql_username_read (optional) 
user_awl_sql_password_read (optional)
user_awl_dsn_write 
user_awl_sql_username_write (optional)
user_awl_sql_password_write (optional)

=cut

  push (@cmds, {
    setting => 'user_awl_read_write_support',
    is_admin => 1,
    default => 0,
    type => $CONF_TYPE_BOOL
  });

  push (@cmds, {
    setting => 'user_awl_dsn_read',
    is_admin => 1,
    type => $CONF_TYPE_STRING
  });

  push (@cmds, {
    setting => 'user_awl_sql_username_read',
    is_admin => 1,
    type => $CONF_TYPE_STRING
  });

  push (@cmds, {
    setting => 'user_awl_sql_password_read',
    is_admin => 1,
    type => $CONF_TYPE_STRING
  });

  push (@cmds, {
    setting => 'user_awl_dsn_write',
    is_admin => 1,
    type => $CONF_TYPE_STRING
  });

  push (@cmds, {
    setting => 'user_awl_sql_username_write',
    is_admin => 1,
    type => $CONF_TYPE_STRING
  });

  push (@cmds, {
    setting => 'user_awl_sql_password_write',
    is_admin => 1,
    type => $CONF_TYPE_STRING
  });


Lines 51-56 CREATE TABLE awl ( Link Here

(-)/home/as/old/SpamAssassin/SQLBasedAddrList.pm (-23 / +155 lines)
51		51
52	You're table definition may change depending on which database driver	52	You're table definition may change depending on which database driver
53	you choose. There is a config option to override the table name.	53	you choose. There is a config option to override the table name.
		54	The username and passwords for read /write support are optional.
		55	If they are not set user_awl_sql_username and user_awl_sql_password is used.
54		56
55	This module introduces several new config variables:	57	This module introduces several new config variables:
56		58
Lines 62-67 user_awl_sql_password Link Here
62		64
63	user_awl_sql_table	65	user_awl_sql_table
64		66
		67	user_awl_read_write_support
		68
		69	user_awl_dsn_read
		70
		71	user_awl_sql_username_read (optional)
		72
		73	user_awl_sql_password_read (optional)
		74
		75	user_awl_dsn_write
		76
		77	user_awl_sql_username_write (optional)
		78
		79	user_awl_sql_password_write (optional)
		80
65	see C<Mail::SpamAssassin::Conf> for more information.	81	see C<Mail::SpamAssassin::Conf> for more information.
66		82
67		83
Lines 116-145 sub new_checker { Link Here
116		132
117	my $class = $self->{class};	133	my $class = $self->{class};
118		134
119	if (!$main->{conf}->{user_awl_dsn} \|\|	135	#checks if user_awl_read_write_support is enabled
120	!$main->{conf}->{user_awl_sql_table}) {	136	my $user_awl_read_write_support=$main->{conf}->{user_awl_read_write_support};
121	dbg("auto-whitelist (sql-based): invalid config");	137
		138	if ($user_awl_read_write_support) {
		139	if (!$main->{conf}->{user_awl_dsn_read} \|\| !$main->{conf}->{user_awl_dsn_write} \|\| !$main->{conf}->{user_awl_sql_table} \|\| $main->{conf}->{user_awl_dsn}) {
		140	dbg("auto-whitelist (sql-based): invalid config 1");
122	return undef;	141	return undef;
		142	}
		143	}
		144	else {
		145	if (!$main->{conf}->{user_awl_dsn} \|\|
		146	!$main->{conf}->{user_awl_sql_table}) {
		147	dbg("auto-whitelist (sql-based): invalid config 2");
		148	return undef;
		149	}
123	}	150	}
124		151
		152	#original
125	my $dsn = $main->{conf}->{user_awl_dsn};	153	my $dsn = $main->{conf}->{user_awl_dsn};
126	my $dbuser = $main->{conf}->{user_awl_sql_username};	154	my $dbuser = $main->{conf}->{user_awl_sql_username};
127	my $dbpass = $main->{conf}->{user_awl_sql_password};	155	my $dbpass = $main->{conf}->{user_awl_sql_password};
128		156
129	my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0});	157	#new
		158	my $dsn_read = $main->{conf}->{user_awl_dsn_read};
		159	my $dbuser_read = $main->{conf}->{user_awl_sql_username_read};
		160	my $dbpass_read = $main->{conf}->{user_awl_sql_password_read};
		161
		162	my $dsn_write = $main->{conf}->{user_awl_dsn_write};
		163	my $dbuser_write = $main->{conf}->{user_awl_sql_username_write};
		164	my $dbpass_write = $main->{conf}->{user_awl_sql_password_write};
		165
		166	if( !$user_awl_read_write_support ) {
		167	my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0});
		168	if(!$dbh) {
		169	dbg("auto-whitelist (sql-based): Unable to Connect to DB");
		170	return undef;
		171	}
130		172
131	if(!$dbh) {	173	$self = { 'main' => $main,
132	dbg("auto-whitelist (sql-based): Unable to Connect to DB");	174	'dsn' => $dsn,
133	return undef;	175	'dbh' => $dbh,
		176	'tablename' => $main->{conf}->{user_awl_sql_table},
		177	};
		178	dbg("SQL Based AWL: Connected to $dsn");
134	}	179	}
		180	else
		181	{
		182	#use user_awl_sql_password and username if only one pass for both DBs (read/write) is used
		183	if($dbpass && $dbuser && !$dbuser_read && !$dbpass_read && !$dbuser_write && !$dbpass_write) {
		184	$dbuser_read=$dbuser;
		185	$dbpass_read=$dbpass;
		186	$dbuser_write=$dbuser;
		187	$dbpass_write=$dbpass;
		188	}
135		189
136	$self = { 'main' => $main,	190	my $dbh_to_read = DBI->connect($dsn_read, $dbuser_read, $dbpass_read, {'PrintError' => 0});
137	'dsn' => $dsn,	191	if(!$dbh_to_read){
138	'dbh' => $dbh,	192	dbg("auto-whitelist (sql-based): Unable to Connect to read DB");
139	'tablename' => $main->{conf}->{user_awl_sql_table},	193	return undef;
140	};	194	}
		195
		196	my $dbh_to_write = DBI->connect($dsn_write, $dbuser_write, $dbpass_write, {'PrintError' => 0});
		197	if(!$dbh_to_write){
		198	dbg("auto-whitelist (sql-based): Unable to Connect to write DB");
		199	return undef;
		200	}
		201
		202	$self = { 'main' => $main,
		203	'dsn_read' => $dsn_read,
		204	'dsn_write' => $dsn_write,
		205	'tablename' => $main->{conf}->{user_awl_sql_table},
		206	'dbh_to_read' => $dbh_to_read,
		207	'dbh_to_write'=> $dbh_to_write,
		208	'user_awl_read_write_support' => $user_awl_read_write_support,
		209	};
141		210
142	dbg("SQL Based AWL: Connected to $dsn");	211	dbg("SQL Based AWL: Connected readable to $dsn_read");
		212	dbg("SQL Based AWL: Connected writeable to $dsn_write");
		213	}
143		214
144	return bless ($self, $class);	215	return bless ($self, $class);
145	}	216	}
Lines 161-166 otherwise it is set to 0. Link Here
161		232
162	sub get_addr_entry {	233	sub get_addr_entry {
163	my ($self, $addr) = @_;	234	my ($self, $addr) = @_;
		235	my $sth;
		236	my $err;
164		237
165	my $entry = { addr => $addr,	238	my $entry = { addr => $addr,
166	exists_p => 0,	239	exists_p => 0,
Lines 176-186 sub get_addr_entry { Link Here
176		249
177	my $sql = "SELECT count, totscore FROM $self->{tablename}	250	my $sql = "SELECT count, totscore FROM $self->{tablename}
178	WHERE username = ? AND email = ? AND ip = ?";	251	WHERE username = ? AND email = ? AND ip = ?";
179	my $sth = $self->{dbh}->prepare($sql);	252
		253	#new
		254	if ( $self->{user_awl_read_write_support} ) {
		255	$sth = $self->{dbh_to_read}->prepare($sql);
		256	}
		257	else {
		258	$sth = $self->{dbh}->prepare($sql);
		259	}
		260
180	my $rc = $sth->execute($username, $email, $ip);	261	my $rc = $sth->execute($username, $email, $ip);
181		262
182	if (!$rc) { # there was an error, but try to go on	263	if (!$rc) { # there was an error, but try to go on
183	my $err = $self->{dbh}->errstr;	264	if( $self->{user_awl_read_write_support} ) {
		265	$err = $self->{dbh_to_read}->errstr;
		266	}
		267	else {
		268	$err = $self->{dbh}->errstr;
		269	}
184	dbg("auto-whitelist (sql-based) get_addr_entry: SQL Error: $err");	270	dbg("auto-whitelist (sql-based) get_addr_entry: SQL Error: $err");
185	$entry->{count} = 0;	271	$entry->{count} = 0;
186	$entry->{totscore} = 0;	272	$entry->{totscore} = 0;
Lines 222-228 itself a SQL database. Link Here
222		308
223	sub add_score {	309	sub add_score {
224	my($self, $entry, $score) = @_;	310	my($self, $entry, $score) = @_;
225		311	my $sth;
		312	my $err;
226	return if (!$entry->{addr});	313	return if (!$entry->{addr});
227		314
228	my ($email, $ip) = $self->_unpack_addr($entry->{addr});	315	my ($email, $ip) = $self->_unpack_addr($entry->{addr});
Lines 239-249 sub add_score { Link Here
239	totscore = totscore + ?	326	totscore = totscore + ?
240	WHERE username = ? AND email = ? AND ip = ?";	327	WHERE username = ? AND email = ? AND ip = ?";
241		328
242	my $sth = $self->{dbh}->prepare($sql);	329	#new
		330	if( $self->{user_awl_read_write_support} ) {
		331	$sth = $self->{dbh_to_write}->prepare($sql);
		332	}
		333	else {
		334	$sth = $self->{dbh}->prepare($sql);
		335	}
		336
243	my $rc = $sth->execute($score, $username, $email, $ip);	337	my $rc = $sth->execute($score, $username, $email, $ip);
244		338
245	if (!$rc) {	339	if (!$rc) {
246	my $err = $self->{dbh}->errstr;	340	if( $self->{user_awl_read_write_support} ) {
		341	$err = $self->{dbh_to_write}->errstr;
		342	}
		343	else {
		344	$err = $self->{dbh}->errstr;
		345	}
247	dbg("auto-whitelist (sql-based) add_score: SQL Error: $err");	346	dbg("auto-whitelist (sql-based) add_score: SQL Error: $err");
248	}	347	}
249	else {	348	else {
Lines 253-262 sub add_score { Link Here
253	}	352	}
254	else { # no entry yet, so insert a new entry	353	else { # no entry yet, so insert a new entry
255	my $sql = "INSERT INTO $self->{tablename} (username,email,ip,count,totscore) VALUES (?,?,?,?,?)";	354	my $sql = "INSERT INTO $self->{tablename} (username,email,ip,count,totscore) VALUES (?,?,?,?,?)";
256	my $sth = $self->{dbh}->prepare($sql);	355	#new
		356	if( $self->{user_awl_read_write_support} ) {
		357	$sth = $self->{dbh_to_write}->prepare($sql);
		358	}
		359	else {
		360	$sth = $self->{dbh}->prepare($sql);
		361	}
		362
257	my $rc = $sth->execute($username,$email,$ip,1,$score);	363	my $rc = $sth->execute($username,$email,$ip,1,$score);
258	if (!$rc) {	364	if (!$rc) {
259	my $err = $self->{dbh}->errstr;	365	if( $self->{user_awl_read_write_support} ) {
		366	$err = $self->{dbh_to_write}->errstr;
		367	}
		368	else {
		369	$err = $self->{dbh}->errstr;
		370	}
260	dbg("auto-whitelist (sql-based) add_score: SQL Error: $err");	371	dbg("auto-whitelist (sql-based) add_score: SQL Error: $err");
261	}	372	}
262	$entry->{exists_p} = 1;	373	$entry->{exists_p} = 1;
Lines 280-286 perl-IP entries for this address as well Link Here
280		391
281	sub remove_entry {	392	sub remove_entry {
282	my ($self, $entry) = @_;	393	my ($self, $entry) = @_;
283		394	my $sth;
		395	my $err;
284	my ($email, $ip) = $self->_unpack_addr($entry->{addr});	396	my ($email, $ip) = $self->_unpack_addr($entry->{addr});
285		397
286	return unless ($email && $ip);	398	return unless ($email && $ip);
Lines 304-314 sub remove_entry { Link Here
304	dbg("auto-whitelist (sql-based) remove_entry: Removing single entry matching ".$entry->{addr});	416	dbg("auto-whitelist (sql-based) remove_entry: Removing single entry matching ".$entry->{addr});
305	}	417	}
306		418
307	my $sth = $self->{dbh}->prepare($sql);	419	#new
		420	if( $self->{user_awl_read_write_support} ) {
		421	$sth = $self->{dbh_to_write}->prepare($sql);
		422	}
		423	else {
		424	$sth = $self->{dbh}->prepare($sql);
		425	}
308	my $rc = $sth->execute(@args);	426	my $rc = $sth->execute(@args);
309		427
310	if (!$rc) {	428	if (!$rc) {
311	my $err = $self->{dbh}->errstr;	429	if( $self->{user_awl_read_write_support} ) {
		430	$err = $self->{dbh_to_write}->errstr;
		431	}
		432	else {
		433	$err = $self->{dbh}->errstr;
		434	}
312	dbg("auto-whitelist (sql-based) remove_entry: SQL Error: $err");	435	dbg("auto-whitelist (sql-based) remove_entry: SQL Error: $err");
313	}	436	}
314	else {	437	else {
Lines 329-336 This method provides the necessary clean Link Here
329		452
330	sub finish {	453	sub finish {
331	my ($self) = @_;	454	my ($self) = @_;
		455	if( $self->{user_awl_read_write_support} ) {
		456	$self->{dbh_to_write}->disconnect();
		457	$self->{dbh_to_read}->disconnect();
		458	dbg("auto-whitelist (sql-based) finish: Disconnected from " . $self->{dsn_write});
		459	dbg("auto-whitelist (sql-based) finish: Disconnected from " . $self->{dsn_read});
		460	}
		461	else {
332	dbg("auto-whitelist (sql-based) finish: Disconnected from " . $self->{dsn});	462	dbg("auto-whitelist (sql-based) finish: Disconnected from " . $self->{dsn});
333	$self->{dbh}->disconnect();	463	$self->{dbh}->disconnect();
		464	}
		465
334	}	466	}
335		467
336	=head2 _unpack_addr	468	=head2 _unpack_addr

Return to bug 4508

Lines 2778-2783 specification (see Mail::SpamAssassin::B Link Here

(-)/home/as/old/SpamAssassin/Conf.pm (-1 / +134 lines)
2778		2778
2779	=item bayes_sql_dsn DBI::databasetype:databasename:hostname:port	2779	=item bayes_sql_dsn DBI::databasetype:databasename:hostname:port
2780		2780
		2781	To use 2 different Databases (for example : You have mysql replication. Than you can write to the master and read from the slave
		2782
		2783	in general localhost.) you can enable bayes_read_write_support.
		2784
		2785	New Bayes Vars here :
		2786
		2787	bayes_read_write_support BOOL
		2788
		2789	bayes_sql_dsn_read
		2790
		2791	bayes_sql_username_read
		2792
		2793	bayes_sql_password_read
		2794
		2795	bayes_sql_dsn_write
		2796
		2797	bayes_sql_username_write
		2798
		2799	bayes_sql_password_write
		2800
		2801	=cut
		2802
		2803	push (@cmds, {
		2804	setting => 'bayes_read_write_support',
		2805	is_admin => 1,
		2806	default => 0,
		2807	type => $CONF_TYPE_BOOL
		2808	});
		2809
		2810	push (@cmds, {
		2811	setting => 'bayes_sql_dsn_read',
		2812	is_admin => 1,
		2813	default => '',
		2814	type => $CONF_TYPE_STRING
		2815	});
		2816
		2817	push (@cmds, {
		2818	setting => 'bayes_sql_username_read',
		2819	is_admin => 1,
		2820	default => '',
		2821	type => $CONF_TYPE_STRING
		2822	});
		2823
		2824	push (@cmds, {
		2825	setting => 'bayes_sql_password_read',
		2826	is_admin => 1,
		2827	default => '',
		2828	type => $CONF_TYPE_STRING
		2829	});
		2830
		2831	push (@cmds, {
		2832	setting => 'bayes_sql_dsn_write',
		2833	is_admin => 1,
		2834	default => '',
		2835	type => $CONF_TYPE_STRING
		2836	});
		2837
		2838	push (@cmds, {
		2839	setting => 'bayes_sql_username_write',
		2840	is_admin => 1,
		2841	default => '',
		2842	type => $CONF_TYPE_STRING
		2843	});
		2844
2845	push (@cmds, {
2846	setting => 'bayes_sql_password_write',
2847	is_admin => 1,
2848	default => '',
2849	type => $CONF_TYPE_STRING
2850	});
2851
2852
2853	=item bayes_sql_dsn DBI::databasetype:databasename:hostname:port
2854
2781	Used for BayesStore::SQL storage implementation.	2855	Used for BayesStore::SQL storage implementation.
2782		2856
2783	This option give the connect string used to connect to the SQL based Bayes storage.	2857	This option give the connect string used to connect to the SQL based Bayes storage.
Lines 2876-2882 The password for the database username, Link Here
2876	=item user_scores_sql_custom_query query	2950	=item user_scores_sql_custom_query query
2877		2951
2878	This option gives you the ability to create a custom SQL query to	2952	This option gives you the ability to create a custom SQL query to
2879	retrieve user scores and preferences. In order to work correctly your	2953	Retrieve user scores and preferences. In order to work correctly your
2880	query should return two values, the preference name and value, in that	2954	query should return two values, the preference name and value, in that
2881	order. In addition, there are several "variables" that you can use	2955	order. In addition, there are several "variables" that you can use
2882	as part of your query, these variables will be substituted for the	2956	as part of your query, these variables will be substituted for the
Lines 2982-2987 The table user auto-whitelists are store Link Here
2982	setting => 'user_awl_sql_table',	3056	setting => 'user_awl_sql_table',
2983	is_admin => 1,	3057	is_admin => 1,
2984	default => 'awl',	3058	default => 'awl',
		3059	type => $CONF_TYPE_STRING
		3060	});
		3061
		3062	=item user_awl_sql_read_write_support
		3063
		3064	This patch make it possible to read from one host and write to another.
		3065	Very usefull for mysql replication.
		3066
		3067	if user_awl_sql_read_write_support is true, you have following extra options :
		3068
		3069	user_awl_dsn_read
		3070	user_awl_sql_username_read (optional)
		3071	user_awl_sql_password_read (optional)
		3072	user_awl_dsn_write
		3073	user_awl_sql_username_write (optional)
		3074	user_awl_sql_password_write (optional)
		3075
		3076	=cut
		3077
		3078	push (@cmds, {
		3079	setting => 'user_awl_read_write_support',
		3080	is_admin => 1,
		3081	default => 0,
		3082	type => $CONF_TYPE_BOOL
		3083	});
		3084
		3085	push (@cmds, {
		3086	setting => 'user_awl_dsn_read',
		3087	is_admin => 1,
		3088	type => $CONF_TYPE_STRING
		3089	});
		3090
		3091	push (@cmds, {
		3092	setting => 'user_awl_sql_username_read',
		3093	is_admin => 1,
		3094	type => $CONF_TYPE_STRING
		3095	});
		3096
		3097	push (@cmds, {
		3098	setting => 'user_awl_sql_password_read',
		3099	is_admin => 1,
		3100	type => $CONF_TYPE_STRING
		3101	});
		3102
		3103	push (@cmds, {
		3104	setting => 'user_awl_dsn_write',
		3105	is_admin => 1,
		3106	type => $CONF_TYPE_STRING
		3107	});
		3108
		3109	push (@cmds, {
		3110	setting => 'user_awl_sql_username_write',
		3111	is_admin => 1,
		3112	type => $CONF_TYPE_STRING
		3113	});
		3114
		3115	push (@cmds, {
		3116	setting => 'user_awl_sql_password_write',
		3117	is_admin => 1,
2985	type => $CONF_TYPE_STRING	3118	type => $CONF_TYPE_STRING
2986	});	3119	});
2987		3120