RE: Enabling per user rules in SQL db

[Don O'Neil]

Thanks for the patch... I'll see what I can find. Sounds to me like 3.1.0 is
broken. Since I've never run anything earlier, I can't confirm if it's
something specific with 3.1.0.

Don 

-----Original Message-----
From: Dale Morin [mailto:[EMAIL PROTECTED] 
Sent: Sunday, February 12, 2006 7:34 PM
To: users@spamassassin.apache.org
Cc: Don O'Neil
Subject: RE: Enabling per user rules in SQL db

Hello Don,

I ran into exactly the same problem and I have not been able to get per-user
rules to work from mysql.  I hacked a workaround but have not revisited it
lately.

Anyway, here is a patch file for SQL.pm that will give you additional output
that may be helpful while you're debugging.  This presumes that you're
running v3.1.0.

--- SQL.pm.org  2005-11-20 12:39:22.000000000 -0600
+++ SQL.pm      2005-11-20 12:45:06.000000000 -0600
@@ -125,8 +125,10 @@
    my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0});

    if ($dbh) {
+     dbg("config: Conf::SQL: database connection established");
      my $sql;
      if (defined($custom_query)) {
+       dbg("config: Conf::SQL: custom query defined");
        $sql = $custom_query;
        my $quoted_username = $dbh->quote($username);
        my ($mailbox, $domain) = split('@', $username); @@ -139,19 +141,24
@@
        $sql =~ s/_DOMAIN_/$quoted_domain/g;
      }
      else {
+       dbg("config: Conf::SQL: using deliverd query");
        $sql = "select $f_preference, $f_value  from $f_table where ".
         "$f_username = ".$dbh->quote($username).
-        " or $f_username = '[EMAIL PROTECTED]' order by $f_username asc";
+        " or $f_username = '\$GLOBAL' order by $f_username asc";
      }
-     dbg("config: Conf::SQL: executing SQL: $sql");
+     dbg("config: Conf::SQL: preparing SQL: $sql");
      my $sth = $dbh->prepare($sql);
      if ($sth) {
+       dbg("config: Conf::SQL: executing SQL: $sql");
        my $rv  = $sth->execute();
        if ($rv) {
-        dbg("config: retrieving prefs for $username from SQL server");
+        dbg("config: Conf::SQL: retrieving prefs for $username from SQL
server");
+         my $numrows = $sth->rows;
+        dbg("config: Conf::SQL: retrieved $numrows rows from SQL 
+ server");
         my @row;
         my $text = '';
         while (@row = $sth->fetchrow_array()) {
+            dbg("config: Conf::SQL: fetched row: $row[0] $row[1]");
           $text .= (defined($row[0]) ? $row[0] : '') . "\t" .
               (defined($row[1]) ? $row[1] : '')  . "\n";
         }



--
Dale Morin, Mustang Internet Services, Inc.
"Support Without Compromise"
main office: 847.541.2811
direct line: 815.496.9853
email: [EMAIL PROTECTED]