Thanks. I had to change: username(255) NOT NULL to: username(255) NOT NULL DEFAULT ''
But I confirmed it works for both /private and /shared.
Thanks for all your help. I wish I could post this to the wiki. I hope this email on the mailing list archive will help people in the future.
Joseph D. Wagner
-----Original Message----- From: dovecot dovecot-bounces@dovecot.org On Behalf Of Aki Tuomi Sent: Sunday, August 1, 2021 12:47 AM To: dovecot@dovecot.org; Joseph D Wagner joe@josephdwagner.info Subject: RE: SQL Backend for IMAP METADATA Dictionary
Hi,
I realized that the priv map entry should work for shared too. So just use pattern = $key. The username field should always be there for IMAP metadata for both shared and priv.
Aki
On 1 August 2021 7.10.06 UTC, Joseph D Wagner joe@josephdwagner.info wrote:
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-bounces@dovecot.org On Behalf Of Aki Tuomi Sent: Saturday, July 31, 2021 4:46 AM To: dovecot@dovecot.org; Joseph D Wagner joe@josephdwagner.info Subject: Re: SQL Backend for IMAP METADATA Dictionary
On 31 July 2021 9.12.53 UTC, Joseph D Wagner joe@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