register instead of regfile

[cc08]

Hi!

I'm trying to move to the register module, but I can’t implement the functionality of "alternatives".

It is necessary to use the stored procedure of postgres, but my knowledge is not enough.

Tell me an example, please.

[Monica Tepelus]

Hi,

You can use both MySQL or PostgreSQL. Below is an example with mysql. With postgresql it's the same concept. Call stored procedure that must return record type with all the columns you need in your answer, so you can return parameters to be added, not just the call.route answer.

https://docs.yate.ro/wiki/Register_Database_Schema

[cc08]

Hi Monica!

Unfortunately, in your example, there is no routing, and especially the functionality of "alternatives".
Over the weekend, I made a working function in postgres, but it turned out so ugly!

I’ll leave this function here, maybe a professional will see it, will be horrified and advise how to ennoble it.

Code: [Select]

CREATE FUNCTION public.get_route(_caller text, _called text, _addres text) RETURNS SETOF record
    LANGUAGE plpgsql
    AS $$DECLARE
  _location text :='-';
  _tmp text;
  _sum int;
  _sumAlt int;
  _sumCld int;
  _result RECORD;
  _record RECORD;
/* max count of calls is 5*/
  _callto1 text;
  _callto2 text;
  _callto3 text;
  _callto4 text;
  _callto5 text;

BEGIN

  SELECT COUNT(id)
  FROM ( SELECT id
         FROM users
         WHERE username=_called
         AND location IS NOT NULL)
  AS sub
  INTO _sumCld;
  IF _sumCld IS NULL THEN
    _sumCld = 0;
  END IF;

  SELECT COUNT(id)
  FROM ( SELECT id
         FROM users
         WHERE alternatives ~ _called
         AND location IS NOT NULL)
  AS sub
  INTO _sumAlt;
  IF _sumAlt IS NULL THEN
    _sumCld = 0;
  END IF;

  _sum = _sumAlt + _sumCld;
  IF _sum > 1 THEN
    _location ='fork';
    IF _sumAlt > 1 THEN
      FOR _record IN
        SELECT location
        FROM users
        WHERE alternatives ~ _called
        AND location IS NOT NULL
      LOOP
        CASE _sumAlt
          WHEN 1 THEN _callto1 = _record.location;
          WHEN 2 THEN _callto2 = _record.location;
          WHEN 3 THEN _callto3 = _record.location;
          WHEN 4 THEN _callto4 = _record.location;
          WHEN 5 THEN _callto5 = _record.location;
        END CASE;
        _sumAlt = _sumAlt -1;
        EXIT WHEN _sumAlt = 0;
      END LOOP;

      IF _sumCld = 1 THEN
        SELECT location
        FROM users
        WHERE username=_called
        AND location IS NOT NULL
        INTO _tmp;
        CASE _sum
          WHEN 1 THEN _callto1 = _tmp;
          WHEN 2 THEN _callto2 = _tmp;
          WHEN 3 THEN _callto3 = _tmp;
          WHEN 4 THEN _callto4 = _tmp;
          WHEN 5 THEN _callto5 = _tmp;
        END CASE;       
      END IF;
    ELSE
      SELECT location
      FROM users
      WHERE username=_called
      AND location IS NOT NULL
      INTO _callto1;
      SELECT location
      FROM users
      WHERE alternatives ~ _called
      AND location IS NOT NULL
      INTO _callto2;
    END IF;
  ELSE
    IF _sumCld = 1 THEN
      SELECT location
      FROM users
      WHERE username=_called
      AND location IS NOT NULL
      INTO _location;
    ELSEIF _sumAlt = 1 THEN
      SELECT location
      FROM users
      WHERE alternatives ~ _called
      AND location IS NOT NULL
      INTO _location;
    ELSE
      RETURN;
    END IF;   
  END IF;

  SELECT _location,_callto1,_callto2,_callto3,_callto4,_callto5
  INTO _result;

  RETURN NEXT _result;
  RETURN;

END;
$$;


Table `users` based on your examples, but slightly modified.

Code: [Select]

CREATE TABLE public.users (
    username text NOT NULL,
    password text,
    vcard text,
    first_name text,
    last_name text,
    address text,
    inuse integer,
    location text,
    expires timestamp without time zone,
    alternatives text,
    realm text,
    reginterval integer DEFAULT 300,
    lnr boolean DEFAULT false,
    context text,
    id bigint DEFAULT nextval('public.user_id_seq'::regclass) NOT NULL,
    record text,
    skype text,
    mobile text,
    groupe text
);


And part of register.conf:

Code: [Select]

[call.route]
query=SELECT * FROM get_route('${caller}','${called}','${address}') AS t(location text,"callto.1" text,"callto.2" text,"callto.3" text,"callto.4" text,"callto.5" text)
result=location
priority=110