On 03.05.21 08:10, Aki Tuomi wrote:

Thanks, Aki. Yes it helped. It never occured to me, that the last placeholder "$key" in your example is filled with the "tail" of the pattern.

for one, you need to use proxy::metadata

then define

dict { metadata = mysql:/path/to/config }

then you need the mapping file, which you could use something like:

connect = host=localhost dbname=dovecot user=dovecot password=dovecot map { pattern = priv/$key fields { meta_key = $key } table = meta username_field = username value_field = value }

Hope this helps.

yes, it did :-)

For the archive - using postgres:

-- TODO: TEXT for testing purpose - adapt later CREATE TABLE metadata ( username TEXT NOT NULL, meta_key TEXT NOT NULL, value TEXT ); CREATE UNIQUE INDEX metadata_pk ON metadata (username, meta_key);

-- Dovecot uses INSERT all the time -- Taken from merge_quota() CREATE OR REPLACE FUNCTION merge_metadata() RETURNS TRIGGER AS $$ BEGIN -- not working currently, because Dovecot passes "NIL" as string. -- we could test for NIL, but is it a bug? IF NEW.value ISNULL THEN DELETE FROM metadata WHERE username = NEW.username AND meta_key = NEW.meta_key; RETURN NULL; END IF; LOOP UPDATE metadata SET value = NEW.value WHERE username = NEW.username AND meta_key = NEW.meta_key; IF found THEN RETURN NULL; END IF;

BEGIN
  INSERT INTO metadata (value, meta_key, username)
      VALUES (NEW.value, NEW.meta_key, NEW.username);
  return NULL;
EXCEPTION WHEN unique_violation THEN
  -- someone just inserted the record, update it
END;

END LOOP; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER upd_metadata BEFORE INSERT ON metadata FOR EACH ROW EXECUTE PROCEDURE merge_metadata();

-- Steffen

On 03/05/2021 09:10 EEST Aki Tuomi aki.tuomi@open-xchange.com wrote:

...

On 30/04/2021 09:38 Steffen Kaiser skdovecot@smail.inf.h-brs.de wrote:

Hi,

the

https://doc.dovecot.org/configuration_manual/imap_metadata/

sample uses

mail_attribute_dict = file:%h/Maildir/dovecot-attributes

which stores all keys=value pairs in the file.

http://dovecot.2317879.n4.nabble.com/Dovecot-v2-3-9-3-HTTP-API-Endpoint-for-...

uses a SQL dict, however very specific ones. How can I use a SQL dict to store _all_ keys, as with a file based storage?

I cannot find a documentation for "pattern" specification, that works as "catch all", in order to store anything not catched by patterns into the database.

https://wiki.dovecot.org/Dictionary does not give any hint (in my eyes).

-- Steffen Kaiser

for one, you need to use proxy::metadata

then define

dict { metadata = mysql:/path/to/config }

then you need the mapping file, which you could use something like:

connect = host=localhost dbname=dovecot user=dovecot password=dovecot map { pattern = priv/$key fields { meta_key = $key } table = meta username_field = username value_field = value }

with

CREATE TABLE meta ( username VARCHAR(255) NOT NULL, meta_key VARCHAR(255) NOT NULL, value VARCHAR(255), PRIMARY KEY(username, key) );

Hope this helps.

Aki

Hi!

(sorry for prev email, MUA acted up somehow)

There is now better documentation on how to achieve this: https://doc.dovecot.org/configuration_manual/dict/#sql-dict-with-mail-attrib...

Aki