Per user SA-Bayes tokens in SQL

[Mark Martinec]

Nick,

> >      @sa_username_maps = (
> >        { 'user1 at example.com' =>  'user1',
> >          'user2 at example.com' =>  'user2',
> >          '.example.com'      =>  'user_ex',
> >        }
> >      );
> 
> This is the information I've found so far. It's only not very usefull if 
> you would like to consult an SQL-DB. I've got all local users defined in 
> my DB so I'd just like to do a 1 on 1 translation:
> user1 at domain.com => user2 at domain.com
> user2 at domain.com => user2 at domain.com
> unknown-user at domain.com => NULL
> someone at external-domain.com => NULL
> 
> without having to define them all. Just a simple "select user from 
> mailbox" would suffice but I cannot find any documentation on how to do 
> that.


In any @*_maps config setting you can use any lookup mechanisms
you want: hash, list, regexp, SQL, LDAP.

If you have records for each user in the amavis 'users' table
you can use the 'sa_username' field as Rob is suggesting.
These records can even be synthesised on the fly by a SELECT
clause and need not exist in a database.

For simple mappings like you describe you can use the right-hand-side
substitutions as offered by hash/list/regexp lookups,

e.g. using a hash-type lookup:

  @sa_username_maps = (
    { '.example.com' => '$3 at example.com' },
  );

or using a regexp lookup:

  @sa_username_maps = (
    new_RE( [ qr'^(.*)@example\com$'i => '$1 at example.com' ] ),
  );




README.lookups:
  REGULAR EXPRESSION LOOKUPS

The pattern allows for capturing of parenthesized substrings, which can
then be referenced from the result string using the $1, $2, ... notation,
as with the Perl m// operator. The number after a $ may be a multi-digit
decimal number. To avoid possible ambiguity the ${n} or $(n) form may be used.
Substring numbering starts with 1. Nonexistent references evaluate to empty
strings. If any substitution is done, the result inherits the taintedness
of the key. Keep in mind that $ and @ characters needs to be backslash-quoted
in qq() strings. Example:
  $virus_quarantine_to = new_RE(
    [ qr'^(.*)@example\.com$'i => 'virus-${1}@example.com' ],
    [ qr'^(.*)(@[^@]*)?$'i     => 'virus-${1}${2}' ] );


Similar to $1, $2, ... rhs replacements in a regexp-based lookups,
a couple of these ($1 .. $5) is also simulated and provided by
a hash-type lookup:

  # the rhs replacement strings are similar to what would be obtained
  # by lookup_re() given the following regular expression:
  # /^( ( ( [^\@]*? ) ( \Q$delim\E [^\@]* )? ) (?: \@ (.*) ) )$/xs
  my $rhs = [   # a list of right-hand side replacement strings
    $addr,                  # $1 = User+Foo at Sub.Example.COM
    $saved_full_localpart,  # $2 = User+Foo
    $localpart,             # $3 = user  (lc if localpart_is_case_sensitive)
    $extension,             # $4 = +foo  (lc if localpart_is_case_sensitive)
    $domain,                # $5 = sub.example.com (lowercased unconditionally)
  ];


Mark