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