# 
# This patch adds SQL statement caching to the SQLBasedAddrList factory, letting
# it use the same SELECT/INSERT/UPDATE statements over and over instead of re-preparing
# them each time through.
# 
# We also patch AutoWhitelist.pm itself to ensure that the $main->{conf} context is available to
# us when the factory is being initialized.
# 
# Provided a small, but noticable increase in AWL speed for me. YMMV.
# Tested on MySQL v5.0.20a, but should work for any DBD modules that supported the original syntax.
# Tested in a compile_now() environment.
# 
# 
# Japheth Cleaver <cleaver@rohan.sdsu.edu> <cleaver@redwire.net>
# 5/2/2006
# Version 1.1 of the earlier patch
# 
# 

--- SpamAssassin/AutoWhitelist.pm.true_persistent_awl_sql	2006-05-02 12:28:12.000000000 -0700
+++ SpamAssassin/AutoWhitelist.pm	2006-05-02 12:37:15.000000000 -0700
@@ -76,7 +76,7 @@
       $type = $1;
       eval '
   	    require '.$type.';
-            $factory = '.$type.'->new();
+            $factory = '.$type.'->new($main);
            ';
       if ($@) { 
 	warn "auto-whitelist: $@";
--- SpamAssassin/SQLBasedAddrList.pm.true_persistent_awl_sql	2006-05-01 21:35:48.000000000 -0700
+++ SpamAssassin/SQLBasedAddrList.pm	2006-05-02 12:56:11.000000000 -0700
@@ -96,29 +96,11 @@
 =cut
 
 sub new {
-  my ($proto) = @_;
+  my ($proto, $main) = @_;
   my $class = ref($proto) || $proto;
   my $self = $class->SUPER::new(@_);
   $self->{class} = $class;
   bless ($self, $class);
-  $self;
-}
-
-=head2 new_checker
-
-public instance (Mail::SpamAssassin::SQLBasedAddrList) new_checker (\% $main)
-
-Description:
-This method is called to setup a new checker interface and return a blessed
-copy of itself.  Here is where we setup the SQL database connection based
-on the config values.
-
-=cut
-
-sub new_checker {
-  my ($self, $main) = @_;
-
-  my $class = $self->{class};
 
   if (!$main->{conf}->{user_awl_dsn} ||
       !$main->{conf}->{user_awl_sql_table}) {
@@ -139,11 +121,30 @@
 
   dbg("auto-whitelist: sql-based connected to $dsn");
 
-  $self = { 'main'      => $main,
-            'dsn'       => $dsn,
-            'dbh'       => $dbh,
-            'tablename' => $main->{conf}->{user_awl_sql_table},
-          };
+  $self->{dsn}		= $dsn;
+  $self->{dbh}		= $dbh;
+  $self->{tablename}	= $main->{conf}->{user_awl_sql_table};
+
+  $self;
+}
+
+=head2 new_checker
+
+public instance (Mail::SpamAssassin::SQLBasedAddrList) new_checker (\% $main)
+
+Description:
+This method is called to setup a new checker interface and return a blessed
+copy of itself.  Here is where we setup the SQL database connection based
+on the config values.
+
+=cut
+
+sub new_checker {
+  my ($self, $main) = @_;
+
+  my $class = $self->{class};
+
+  $self->{main} = $main;
 
   if ($main->{conf}->{user_awl_sql_override_username}) {
     $self->{_username} = $main->{conf}->{user_awl_sql_override_username};
@@ -190,10 +191,12 @@
 
   return $entry unless ($email && $ip);
 
-  my $sql = "SELECT count, totscore FROM $self->{tablename}
+  $self->{select_sth} || do {
+  	my $sql = "SELECT count, totscore FROM $self->{tablename}
               WHERE username = ? AND email = ? AND ip = ?";
-  my $sth = $self->{dbh}->prepare($sql);
-  my $rc = $sth->execute($self->{_username}, $email, $ip);
+  	$self->{select_sth} = $self->{dbh}->prepare($sql);
+  };
+  my $rc = $self->{select_sth}->execute($self->{_username}, $email, $ip);
 
   if (!$rc) { # there was an error, but try to go on
     my $err = $self->{dbh}->errstr;
@@ -202,7 +205,7 @@
     $entry->{totscore} = 0;
   }
   else {
-    my $aryref = $sth->fetchrow_arrayref();
+    my $aryref = $self->{select_sth}->fetchrow_arrayref();
 
     if (defined($aryref)) { # we got some data back
       $entry->{count} = $aryref->[0] || 0;
@@ -214,7 +217,6 @@
       dbg("auto-whitelist: sql-based get_addr_entry: no entry found for $addr");
     }
   }
-  $sth->finish();
 
   dbg("auto-whitelist: sql-based $addr scores ".$entry->{count}.'/'.$entry->{totscore});
 
@@ -249,12 +251,14 @@
   return $entry unless ($email && $ip);
 
   if ($entry->{exists_p}) { # entry already exists, so just update
-    my $sql = "UPDATE $self->{tablename} SET count = count + 1,
+    $self->{update_sth} || do {
+    	my $sql = "UPDATE $self->{tablename} SET count = count + 1,
                                              totscore = totscore + ?
                 WHERE username = ? AND email = ? AND ip = ?";
     
-    my $sth = $self->{dbh}->prepare($sql);
-    my $rc = $sth->execute($score, $self->{_username}, $email, $ip);
+    	$self->{update_sth} = $self->{dbh}->prepare($sql);
+    };
+    my $rc = $self->{update_sth}->execute($score, $self->{_username}, $email, $ip);
     
     if (!$rc) {
       my $err = $self->{dbh}->errstr;
@@ -263,19 +267,19 @@
     else {
       dbg("auto-whitelist: sql-based add_score: new count: ". $entry->{count} .", new totscore: ".$entry->{totscore}." for ".$entry->{addr});
     }
-    $sth->finish();
   }
   else { # no entry yet, so insert a new entry
-    my $sql = "INSERT INTO $self->{tablename} (username,email,ip,count,totscore) VALUES (?,?,?,?,?)";
-    my $sth = $self->{dbh}->prepare($sql);
-    my $rc = $sth->execute($self->{_username},$email,$ip,1,$score);
+    $self->{insert_sth} || do {
+    	my $sql = "INSERT INTO $self->{tablename} (username,email,ip,count,totscore) VALUES (?,?,?,?,?)";
+    	$self->{insert_sth} = $self->{dbh}->prepare($sql);
+    };
+    my $rc = $self->{insert_sth}->execute($self->{_username},$email,$ip,1,$score);
     if (!$rc) {
       my $err = $self->{dbh}->errstr;
       dbg("auto-whitelist: sql-based add_score: SQL error: $err");
     }
     $entry->{exists_p} = 1;
     dbg("auto-whitelist: sql-based add_score: created new entry for ".$entry->{addr}." with totscore: $score");
-    $sth->finish();
   }
   
   return $entry;
@@ -338,8 +342,7 @@
 
 sub finish {
   my ($self) = @_;
-  dbg("auto-whitelist: sql-based finish: disconnected from " . $self->{dsn});
-  $self->{dbh}->disconnect();
+  dbg("auto-whitelist: sql-based finish: did nothing since we're persistent");
 }
 
 =head2 _unpack_addr