> A better approach would probably be to use SQL lookup instead of a
> statical hash-based lookup. The policy.bypass_virus_checks SQL field
> is equivanent to @bypass_virus_checks_maps (actually it's the other
> way around, the SQL lookup table is implicitly prepended to a
> lists of lookup tables in @*_maps).
>
> I expect your existing database schema is not the same as
> that proposed by amavisd docs (tables users and policy).
> Nevertheless, with some SQL magic and existing schema could
> be presented to amavisd the way it expects it. The idea is
> to present a bypass_virus_checks='N' for all local domains,
> and provide a default of a yes for the rest (either by an
> SQL record or by a statical default).
>
> When amavisd does an SQL lookup, it executes the SELECT clause
> as specified in $sql_clause{'sel_policy'}, which is configurable
> in amavisd.conf. The default is:
>
> # The SQL select clause to fetch per-recipient policy settings.
> # The %k will be replaced by a comma-separated list of query
> addresses
> # for a recipient (e.g. full address, domain only, catchall), %a
will
> be
> # replaced by an exact recipient address (same as the first entry in
> %k,
> # suitable for pattern matching). Use ORDER, if there is a chance
> that
> # multiple records will match - the first match wins (i.e. the first
> # returned record). If field names are not unique (e.g. 'id'), the
> later
> # field overwrites the earlier in a hash returned by lookup, which
is
> # why we use 'users.*, policy.*, users.id'.
>
> SELECT users.*, policy.*, users.id
> FROM users LEFT JOIN policy ON users.policy_id=policy.id
> WHERE users.email IN (%k) ORDER BY users.priority DESC
>
> The resulting record (only the first matters) only needs to
> provide fields with names as in the docs (names are currently
> hard-coded, not configurable). Not all fields need to be present,
> in fact none are required - just provide the ones that you want
> to specify in SQL, others will fall back to statical lookups.
> Also table names do not matter. There is also a useful default
> for the 'local' field (as documented in README.lookups):
> when field name is missing, a mere presence of a resulting
> record implies local='Y' unless it is a wildcard '@.' record,
> providing a convenient way to specify @local_domains_maps in SQL.
>
> Setting the $sql_clause{'sel_policy'} to a suitable clause,
> aided with some SQL magic like subselects, union, aliases, views)
> can mould existing schema into what is desired. Some examples
> were discussed in the past on the ML - I keep some just
> as an illustration, without explaining it now:
>
> # $sql_select_policy =
> # 'SELECT 1 FROM domain WHERE CONCAT("@",domain_name) IN (%k)';
> #
> # $sql_select_policy =
> # 'SELECT *, extuser.id'.
> # ' FROM ((SELECT id,policy_id,priority,email FROM users) UNION'.
> # ' (SELECT 1,99,1,CONCAT("@",domain_name) FROM domain)) AS
> extuser'.
> # ' LEFT JOIN policy ON policy_id=policy.id'.
> # ' WHERE email IN (%k) ORDER BY priority DESC';
> #
> # $sql_select_policy =
> # 'SELECT *, user.id'.
> # ' FROM (SELECT 1 as id, 99 as policy_id, 1 as priority,'.
> # ' CONCAT("@",domain_name) as email FROM domain) AS
> user'.
> # ' LEFT JOIN policy ON policy_id=policy.id'.
> # ' WHERE email IN (%k) ORDER BY priority DESC';
> #
> # $sql_lookups_no_at_means_domain = 1;
> # $sql_select_policy =
> # 'SELECT *, user.id'.
> # ' FROM (SELECT 1 as id, 99 as policy_id, "Y" AS local'.
> # ' FROM domain WHERE domain_name IN (%k)) AS user'.
> # ' LEFT JOIN policy ON policy_id=policy.id';
Excuse me Mark,
I have felt that that I could apply avery interesting solution to the
issue that have raised, for my system.
But I have some doubt:
1) Above there is a veriable mentioned: "$sql_select_polic", while at
the top of tyhis message it seems to me that I have to set
$sql_clause{'sel_policy'} to the clause that have to return "0" (as for
previous solution based on associative array) if the domain is internal,
and "1" other wise.
2) Which field of the select has to contains the 0,1 values?
3) could you depict this solution with a practical example?
Thanks,
rocsca
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
