Re: [AMaViS-user] sql-storage - policy field

[Mark M. <Mar...@ij...>]

Stefan,

> > So you'd want a copy of users.policy_id to end up in table
> > msgrcpt (not msgs).
>
> Exactly.

The patch below implements it. Patch is against 2.6.4-rc2.

You need to add two fields into table msgrcpt:

  ALTER TABLE msgrcpt ADD content char(1);
  ALTER TABLE msgrcpt ADD sql_policy_id integer;

(the msgrcpt.content is from another story,
but I wanted to keep both changes in one place)

I'll keep some of this change for 2.6.4 (infrastructure),
except for the 'ins_rcp' change and change of the
arguments in the $conn_h->execute($ins_rcp) call.


--- amavisd.orig	2009-06-19 21:04:21.000000000 +0200
+++ amavisd	2009-06-22 16:49:52.000000000 +0200
@@ -1190,6 +1190,9 @@
       ' WHERE partition_tag=? AND mail_id=?',
     'ins_rcp' =>
+#     'INSERT INTO msgrcpt (partition_tag, mail_id, rid,'.
+#     ' ds, rs, bl, wl, bspam_level, smtp_resp) VALUES (?,?,?,?,?,?,?,?,?)',
       'INSERT INTO msgrcpt (partition_tag, mail_id, rid,'.
-      ' ds, rs, bl, wl, bspam_level, smtp_resp) VALUES (?,?,?,?,?,?,?,?,?)',
+      ' ds, rs, content, bl, wl, bspam_level, sql_policy_id, smtp_resp)'.
+      ' VALUES (?,?,?,?,?,?,?,?,?,?,?)',
     'ins_quar' =>
       'INSERT INTO quarantine (partition_tag, mail_id, chunk_ind, mail_text)'.
@@ -1204,10 +1207,10 @@
       "SELECT msgs.time_num, msgs.mail_id, subject".
       " FROM msgs JOIN msgrcpt USING (partition_tag,mail_id)".
-      " WHERE sid=? AND rid=? AND content!='V' AND ds='P'".
+      " WHERE sid=? AND rid=? AND msgs.content!='V' AND ds='P'".
       " ORDER BY msgs.time_num DESC",  # LIMIT 1
     'sel_penpals_msgid' =>  # with a nonempty message-id references list
       "SELECT msgs.time_num, msgs.mail_id, subject, message_id, rid".
       " FROM msgs JOIN msgrcpt USING (partition_tag,mail_id)".
-      " WHERE sid=? AND content!='V' AND ds='P' AND message_id IN (%m)".
+      " WHERE sid=? AND msgs.content!='V' AND ds='P' AND message_id IN (%m)".
         " AND rid!=sid".
       " ORDER BY rid=? DESC, msgs.time_num DESC",  # LIMIT 1
@@ -5850,5 +5853,5 @@
 
 sub new     # NOTE: this class is a list for historical reasons, not a hash
-  { my($class) = @_; bless [(undef) x 33], $class }
+  { my($class) = @_; bless [(undef) x 35], $class }
 
 # subs to set or access individual elements of a n-tuple by name
@@ -5915,8 +5918,13 @@
 sub blocking_ccat   # category type most responsible for blocking msg, or undef
   { my($self)=shift; !@_ ? $$self[30] : ($$self[30]=shift) }
-sub courier_control_file # path to control file containing this recipient
+sub user_id   # listref of recipient IDs from a lookup, e.g. SQL field users.id
   { my($self)=shift; !@_ ? $$self[31] : ($$self[31]=shift) }
-sub courier_recip_index # index of recipient within control file
+sub user_policy_id  # recipient's policy ID, e.g. SQL field users.policy_id
   { my($self)=shift; !@_ ? $$self[32] : ($$self[32]=shift) }
+sub courier_control_file # path to control file containing this recipient
+  { my($self)=shift; !@_ ? $$self[33] : ($$self[33]=shift) }
+sub courier_recip_index # index of recipient within control file
+  { my($self)=shift; !@_ ? $$self[34] : ($$self[34]=shift) }
+
 
 sub recip_final_addr {  # return recip_addr_modified if set, else recip_addr
@@ -8516,5 +8524,6 @@
 
 use vars qw(%modules_basic %got_signals);
-use vars qw($user_id_sql $wb_listed_sql $implicit_maps_inserted);
+use vars qw($user_id_sql $user_policy_id_sql $wb_listed_sql);
+use vars qw($implicit_maps_inserted);
 use vars qw($db_env $snmp_db);
 use vars qw($body_digest_cache);
@@ -9585,5 +9594,6 @@
       #            B1: boolean, nonexistent field treated as true
       my $nf = sub{Amavis::Lookup::SQLfield->new($sql_policy,@_)}; #shorthand
-      $user_id_sql =                                    $nf->('id',                   'S');
+      $user_id_sql =        $nf->('id',        'S');
+      $user_policy_id_sql = $nf->('policy_id', 'S-');
       unshift(@Amavis::Conf::local_domains_maps,        $nf->('local',                'B1'));
 
@@ -10333,4 +10343,14 @@
         $r->bypass_spam_checks($bypassed_s);
       }
+#     if (defined $user_id_sql) {   #(for future version)
+#       my($user_id_ref,$mk_ref) =  # list of all id's that match
+#         lookup2(1, $recip, [$user_id_sql], Label=>"users.id");
+#       $r->user_id($user_id_ref)  if ref $user_id_ref;  # listref or undef
+#     }
+      if (defined $user_policy_id_sql) {
+        my($user_policy_id) = lookup2(0, $recip, [$user_policy_id_sql],
+                                      Label=>"users.policy_id");
+        $r->user_policy_id($user_policy_id);  # just the first match
+      }
     }
     # update message count and mesage size snmp counters
@@ -19702,9 +19722,11 @@
           $msginfo->partition_tag, $mail_id, untaint($rid),
         # int($msginfo->rx_time),
-        # $r_content_type,
           substr($d,0,1), ' ',
+          $r_content_type,
           $r->recip_blacklisted_sender ? 'Y' : 'N',
           $r->recip_whitelisted_sender ? 'Y' : 'N',
-          0+untaint($spam_level+$r->recip_score_boost), untaint($resp) );
+          0+untaint($spam_level+$r->recip_score_boost),
+          untaint($r->user_policy_id),
+          untaint($resp) );
       }
       my($q_to) = $msginfo->quarantined_to;  # ref to a list of quar. locations




Mark