[Dovecot] Clarifications on Pigeonhole and MySQL lookups

[Sandro Tosi]

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:
>>>>
>>>> 1. 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_Scripts_Sequentially)
> 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 at register.it