Erik,
Inside ConfSourceSql.pm just do 2 things
1) pull out the domain like
my ($local, $domain) = split(/\@/, $username);
2) then re-order the select query .. i used unions, there may be a
better way
my $sql = "select $f_preference, $f_value from $f_table where ".
" $f_username = 'GLOBAL' union ".
" select $f_preference, $f_value from $f_table where ".
" $f_username = '[EMAIL PROTECTED]' union ".
" select $f_preference, $f_value from $f_table where ".
" $f_username = ".$dbh->quote($domain). " union " .
" select $f_preference, $f_value from $f_table where ".
" $f_username = ".$dbh->quote($username);
this will order the preferences, with the more specific ones overriding
the previous ones. If no domain or user pref is found, the gloabal will
be used.. and so on.
SA will use the last one found so this will probably do what you
want.. it seems to work for us.
:adam
On Tue, 2004-02-24 at 15:49, Erik Wasser wrote:
> Hi SA friends,
>
> today I turned on the "MySQL-connection" of SA so I can save the config
> in a mysql database described at
> "http://www.spamassassin.org/full/2.6x/dist/sql/README";. If was
> satisfied with the results, but one little thing scratched my back. B-)
>
> There a global and a per user config like this example:
>
> +--------+-------------+-----------------+-------+
> | prefid | username | preference | value |
> +--------+-------------+-----------------+-------+
> | 1 | GLOBAL | rewrite_subject | 0 |
> | 2 | [EMAIL PROTECTED] | rewrite_subject | 1 |
> +--------+-------------+-----------------+-------+
>
> The subjects will be *not* rewritten except for the user [EMAIL PROTECTED] So
> far so good: but I want a per site config (one setting for a whole
> domain) too, so I extended the table a little bit:
>
> +--------+-------------+-----------------+-------+ The 'prefid'
> | prefid | username | preference | value | is not
> +--------+-------------+-----------------+-------+ important
> | 1 | GLOBAL | rewrite_subject | 0 | for the
> | 2 | [EMAIL PROTECTED] | rewrite_subject | 0 | ordering!
> | 3 | bar.com | rewrite_subject | 1 |
> +--------+-------------+-----------------+-------+
>
> Mails to "[EMAIL PROTECTED]" are not(!) marked.
> Mails to other members of bar.com are marked.
> All other's are not marked. Got it?
>
> 1) global (least important)
> 2) per site
> 3) per user (most important)
>
> Advantages:
> - backward compabtible to the "@GLOBAL" and "GLOBAL" settings.
> - only changes made in "Mail/SpamAssassin/ConfSourceSQL.pm"
> - no changes in calling the method 'load_with_dbi'
> - the modified SQL query is fully ANSI compatible (I hope so. B-)
> No weird MySQL stuff in here.
>
> Disadvantages:
> - The sorting of the parameter of the query is now done by perl
>
> My Questions:
>
> - Is anybody interessested in the patch?
> - Is there any chance that this patch goes in the offical release? I
> would like it!
>
> Result:
>
> - Please drop a mail if you are interessested I don't wan't to polute
> the ML more than it's needed.
>
> Feedback is welcome.
>
> BTW: Thanks to the SA maintainers for the great work. You guys saved my
> inbox. B-)
>
> P.S: I used SA 2.60 for the work but there is no change in the file
> "Mail/SpamAssassin/ConfSourceSQL.pm" between 2.60 and 2.63.
