[Dovecot] 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:
- store the filters on the database
- 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@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-l...
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@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:
- store the filters on the database That is possible with some limitations.
- 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@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.
Hi Stephan, thanks a lot for your reply.
On 10/11/2012 10:35 PM, Stephan Bosch wrote:
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:
- store the filters on the database That is possible with some limitations.
Are the ones below the only limitatios (ie one script per user) or are there any other worth knowing?
- 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?
Yep, the idea is that any user could have multiple scripts active at the same time, and we'd like also to give them an ordering, so like managing a sort of priority (the lower the priority the sooner the script is executed, or the other way around, doesn't matter).
Ideally, we have a set of several scripts and each user can select to enable only some of them, and choose the order of their executions.
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@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.
Ah no well, I mean, using map { } constructs :)
The example for Sieve-MySQL only shows 2 maps, but given we've never used them, we'd want to know if a "map cascade" would work, so implementing the joins in multiple steps: selecting the ids with a map and the subsequent would use that id to exec the join and so on.
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).
I see, I think that some others would wonder the same, so you might also want to extend the doc to state that explicitly.
Maybe you may want to include something in your TODO list to handle the ordering in case of multiple scripts.
In our situation, what would you suggest? We're now thinking of keeping the scripts list on a separate table, and merge the "user selected ones" in a single script to write in the filters table. Is that what would you suggest? Is there a better solution?
Cheers,
Sandro Tosi Product Engineer Shared Hosting Products R&D | Dada.pro eml sandro.tosi@register.it
On 10/15/2012 9:40 AM, Sandro Tosi wrote:
Hi Stephan, thanks a lot for your reply.
On 10/11/2012 10:35 PM, Stephan Bosch wrote:
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:
- store the filters on the database That is possible with some limitations.
Are the ones below the only limitatios (ie one script per user) or are there any other worth knowing?
You cannot currently use ManageSieve when the active script is located in a dict database.
And 'one script per user' is not an fully accurate description. It is technically possible to access multiple different scripts from the dict database. It is however not possible to use dict support combination with multiscript support ( http://wiki2.dovecot.org/Pigeonhole/Sieve/Configuration#Executing_Multiple_S...) to execute multiple scripts in a sequence. Multiscript currently only works for Sieve scripts that are located in the filesystem.
In our situation, what would you suggest? We're now thinking of keeping the scripts list on a separate table, and merge the "user selected ones" in a single script to write in the filters table. Is that what would you suggest? Is there a better solution?
You can use the include extension (https://tools.ietf.org/html/draft-ietf-sieve-include-05) to access scripts in a dict database from a main active script to combine them. I believe you could even dynamically construct that main script in SQL using some string manipulation in the query, but that is a bit ugly.
Could you send me an overview of your configuration, including your database layout? Provided that I have some time in the next week, I could investigate building a simple working configuration for the sake of example.
Regards,
Stephan.
Hello Stephan, sorry for this late reply.
On 10/19/2012 01:01 AM, Stephan Bosch wrote:
On 10/15/2012 9:40 AM, Sandro Tosi wrote:
Hi Stephan, thanks a lot for your reply.
On 10/11/2012 10:35 PM, Stephan Bosch wrote:
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:
- store the filters on the database That is possible with some limitations.
Are the ones below the only limitatios (ie one script per user) or are there any other worth knowing?
You cannot currently use ManageSieve when the active script is located in a dict database.
And 'one script per user' is not an fully accurate description. It is technically possible to access multiple different scripts from the dict database. It is however not possible to use dict support combination with multiscript support ( http://wiki2.dovecot.org/Pigeonhole/Sieve/Configuration#Executing_Multiple_S...) to execute multiple scripts in a sequence. Multiscript currently only works for Sieve scripts that are located in the filesystem.
In our situation, what would you suggest? We're now thinking of keeping the scripts list on a separate table, and merge the "user selected ones" in a single script to write in the filters table. Is that what would you suggest? Is there a better solution?
You can use the include extension (https://tools.ietf.org/html/draft-ietf-sieve-include-05) to access scripts in a dict database from a main active script to combine them. I believe you could even dynamically construct that main script in SQL using some string manipulation in the query, but that is a bit ugly.
Could you send me an overview of your configuration, including your database layout? Provided that I have some time in the next week, I could investigate building a simple working configuration for the sake of example.
I will follow this up privately (you know, we can't disclose too much) but JFTR we decided to follow a half-and-half solution:
- we keep on the backend database all the scripts the customer could activate in separate rows
- from them, we merge into a single sieve script file all the filter the customer has decided to activate.
This way we still record the script separately in the db, so once we'll be able to feed pigeonhole with multiple lines, it's already there, and then merging into a single file is the most straightforward and simple solution to make what we need to work.
Thanks for the support,
Sandro Tosi Product Engineer Shared Hosting Products R&D | Dada.pro eml sandro.tosi@register.it
participants (2)
-
Sandro Tosi
-
Stephan Bosch