RE: SQL Backend for IMAP METADATA Dictionary

[Joseph D Wagner]

This works for /priv:

map {
  pattern = $key
  table = attr_priv
  fields {
     attr_name = $key
  }
  username_field = username
  value_field = attr_value
}

Not sure what to do about /shared.  I tried, for example, to comment out the 
/priv map and use just this /shared map:
map {
  pattern = $key
  table = attr_shared
  fields {
     attr_name = $key
  }
  value_field = attr_value
}

However, postgres logs showed it didn't even try to hit the database.

Joseph D. Wagner

-----Original Message-----
From: dovecot <dovecot-boun...@dovecot.org> On Behalf Of Aki Tuomi
Sent: Saturday, July 31, 2021 4:46 AM
To: dovecot@dovecot.org; Joseph D Wagner <j...@josephdwagner.info>
Subject: Re: SQL Backend for IMAP METADATA Dictionary



On 31 July 2021 9.12.53 UTC, Joseph D Wagner <j...@josephdwagner.info> wrote:
>I've seen documentation on using a SQL backend for quota dictionary and 
>passdb&userdb dictionary. However, I can't find any examples of a SQL 
>backend for IMAP METADATA / mail_attributes_dict.
>
>Is this combination possible?  If so, is there an example somewhere?
>
>I am unsure what data structures it's expected or how to create the map 
>entries for it.
>
>Thanks in advance.
>
>Joseph D. Wagner
>

Hi Joseph,

try

mail_attributes_dict = proxy::attr

dict {
  attr = sql:/path/to/config
}


map {
  pattern = /shared/$key
  table = attr_shared
  fields {
     attr_name = $key
  }
  value_field = attr_value
}

map {
  pattern = /priv/$key
  table = attr_priv
  fields {
     attr_name = $key
  }
  username_field = username
  value_field = attr_value
}

SQL

CREATE TABLE attr_shared (
   attr_name VARCHAR(255) PRIMARY KEY NOT NULL,
   attr_value VARCHAR(65535)
)

CREATE TABLE attr_priv (
   attr_name VARCHAR(255)  NOT NULL,
   username VARCHAR(255)  NOT NULL,
   attr_value VARCHAR(65535)
   PRIMARY KEY(attr_name, username)
)

Hopefully correct :)

Aki