dovecot - Oct 2012 - Clarifications on Pigeonhole and MySQL lookups

Hello,
we're scouting if it's possible to use Pigeonhole (currently v0.3.1, as 
this will be provided with an upcoming Debian package) with MySQL dict 
lookups with the mail setup we're designing.

Our (main) goals are:

1. store the filters on the database
2. allow each user to enable/disable any of the filters set we provide 
(it's a static set of some general filters, available to all the users; 
we're currently not providing the possibility to users to write their 
own filters)

For point 1) we already see[1] that's possible, but it uses the map 
construct that might not fit with our current database structure: we 
have a domain table (storing the domain info) and a mailbox table 
(storing the mailbox info, but the username is composed by the local 
part, stored in this table, and the domain part is a FK to the domain 
table, using an id).

Do you think it's possible to run a join query on domain+mailbox to 
retrieve the mailbox_id needed to query the table for the filters? Or do 
we have to create the filter table and store the local at domain.ext info 
there ("relaxing" the integrity relationships between tables)?

How do we specify which filters are enabled for any given user? We 
originally thought of an "Enabled" field on the filter table, but in
the
example in the doc[1] I hadn't seen a way to do that: it seems like the 
filter list is specified in the proxy definition - am I wrong? How can 
we do that?

[1] 
http://hg.rename-it.nl/dovecot-2.1-pigeonhole/file/e9ed5d5cef4b/doc/script-location-dict.txt

I think it's enough for now, maybe additional questions will arise going 
deeper in details :)

Thanks in advance,
-- 
Sandro Tosi
Product Engineer
Shared Hosting Products
R&D | Dada.pro
eml sandro.tosi at register.it

On 10/10/2012 11:23 AM, Sandro Tosi wrote:
> Hello,
> we're scouting if it's possible to use Pigeonhole (currently
v0.3.1,
> as this will be provided with an upcoming Debian package) with MySQL 
> dict lookups with the mail setup we're designing.
>
> Our (main) goals are:
>
> 1. store the filters on the database
That is possible with some limitations.
> 2. allow each user to enable/disable any of the filters set we provide 
> (it's a static set of some general filters, available to all the 
> users; we're currently not providing the possibility to users to write 
> their own filters)
Will one or multiple scripts be active at the same time?
> For point 1) we already see[1] that's possible, but it uses the map 
> construct that might not fit with our current database structure: we 
> have a domain table (storing the domain info) and a mailbox table 
> (storing the mailbox info, but the username is composed by the local 
> part, stored in this table, and the domain part is a FK to the domain 
> table, using an id).
>
> Do you think it's possible to run a join query on domain+mailbox to 
> retrieve the mailbox_id needed to query the table for the filters? Or 
> do we have to create the filter table and store the local at domain.ext 
> info there ("relaxing" the integrity relationships between
tables)?
My SQL is a bit rusty, but afaik this is possible with a JOIN or a 
nested query.
> How do we specify which filters are enabled for any given user? We 
> originally thought of an "Enabled" field on the filter table, but
in
> the example in the doc[1] I hadn't seen a way to do that: it seems 
> like the filter list is specified in the proxy definition - am I 
> wrong? How can we do that? 
The above suggests that you would like to activate multiple Sieve 
scripts at the same time. That is currently not possible with the dict 
Script location. It is on my TODO list, but I am not sure when it will 
be ready (definitely not for coming Debian stable).

For Dovecot v2.2 the new :optional tag for the Sieve include command 
could be used in - combination with the dict Sieve script location type 
- to provide some hackish solution. Unfortunately, in your case that is 
still not helpful, because v2.2 is not even in beta stage. :/

Regards,

Stephan.