[Dovecot] MySQL, map files

[Timo Sirainen]

On Mon, 2011-09-12 at 11:23 -0700, Steve Fatula wrote:
> >>  http://dovecot.org/patches/1.2/ldap/HOWTO_dictldap+extdata.txt
> >> 
> >> 
> >>  You will see lots of statements, not sure which ones can be used for
> >>  MySQL (instead of LDAP). 
> > 
> > That patch's mapping references to Pigeonhole's extdata plugin. If you
> > install that plugin you can use SQL or flat file for the same maps.
> > 
> 
> 
> Ok, so, now we get to the issue. What syntax can be used for the extdata plugin? 
> 
> If I have an existing MySQL table with the structure:
> 
> username
> preference
> value
> 
> Username would be the dovecot user name. preference would be a constant, and, value is the variable I want to set via the extdata plugin. The primary key is username + preference (constant, in this case, say it's set to "POP3"). I want to use extdata to retrieve that value. Can it be done using extdata and MySQL map syntax, and if so, can you provide an example? Can I specify the query itself (i.e., select value from table t, another table at where....)

Well, there are two fields that you need to map: responder_mode and
responder_text (explained in the .txt file). Lets assume you have those
two fields in an SQL table named responders. The maps would then be:

map {
  pattern = priv/responder_mode
  table = responders
  username_field = username
  value_field = responder_mode
}
map {
  pattern = priv/responder_text
  table = responders
  username_field = username
  value_field = responder_text
}

You can change anything above except the patterns.