[solved] Re: Catch all for Metadata storage in SQL database

[Steffen]

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 529 bytes
Desc: OpenPGP digital signature
URL: <https://dovecot.org/pipermail/dovecot/attachments/20210503/ce165794/attachment.sig>