[ previous ] [ next ] [ threads ]
 To :  "aucyxob" <aucyxob@y...>
 From :  "Ganapathi Chidambaram" <ganapathi.rj@g...>
 Subject :  Re :Re: [yate] [ YATE 6.0 ] Routing call using PgSQL Storedfunction
 Date :  Sun, 01 Oct 2017 18:08:03 +0000
Thanks . I applied patch manually on source code. It's works perfectly.

And any idea about lazyrecording Module(lazyrecorder.cpp) to install to get recording the call completely.
Regards,
Ganapathi
From : aucyxob  
 TO : Ganapathi Chidambaram , Yate mailing list  
 Sent : Sun, Oct 1, 2017 12:59 PM, 
Subject: Re: [yate] [ YATE 6.0 ] Routing call using PgSQL Stored function 
Hello Ganapathi Chidambaram.
Your answer from the database should consist of one line and many columns with corresponding to parameters names. So your pgsql function should return something like this:
| location | callto.1 | dtmfpass | pbxassist | tonedetect_out | callto.1.secure | copyparams |
------------------------------------------------------------------------------------------------------------------------
| fork | sip/sip:.. | false | true | true | yes | pbxassist,dt..|
This is not very convenient if you need to return a different number of parameters each time.
If you want to return a result from the database as a key-value set
| key | value |
------------------------
| location | fork |
| callto.1 | sip/sip |
| dtmfpas | false |
....
you should consider the following solutions:
1. You can use extmodule or javascript module as proxy for database which will convert your resultset's columns key-value to yate's message param-value.
2. Apply to yate this patch https://github.com/vir/yate/commit/0fa7f90f87e76ff67ea7fb83433a18ae489f687e (https://github.com/vir/yate/commit/0fa7f90f87e76ff67ea7fb83433a18ae489f687e)
or use following yate's very useful fork https://github.com/vir/yate/wiki/MyChanges (https://github.com/vir/yate/wiki/MyChanges)
register.conf
[call.route]
account=default
query=SELECT * FROM route_master('${billid}','${called}','${trace}');
result=location
priority=100
namevaluepairs=yes
PostgreSQL
CREATE OR REPLACE FUNCTION route_master(
    IN billid text,
    IN caller text,
    IN called text,
  )
  RETURNS TABLE(key text, value text) AS
$BODY$
DECLARE
    -- variables
BEGIN
-- do something
-- append lines to resultset
    key := 'location'; value := 'fork'; RETURN NEXT;
    key := 'copyparams'; value := 'pbxassist,dtmfpass'; RETURN NEXT;
    RETURN;
END;
$BODY$
  LANGUAGE plpgsql;
30.09.2017, 15:43, "Ganapathi Chidambaram" :
Hi
As i used pgsql stored function to route a call to user/group. Whereas i am getting result properly but even though calls are not routing to the user .
find the below mentioned configuration for the same.
register.conf
query=SELECT * FROM route_master('${billid}','${called}','${trace}');
result=location
 Output of PgSQL:
field value
trace 4444407
callto.1 sip/sip:4444407@1... (mailto:4444407@1...):5060
dtmfpass false
location fork
pbxassist true
copyparams pbxassist,dtmfpass
tonedetect_out true
callto.1.secure yes
Logs :
 Query for 'db.1' returned 8 rows, 8 affected [0xcbd510]
Returned true 'database' delay=0.230988
  thread=0x7f6e54002c10 'Call Router'
  data=0x7f6e4c006200
  retval='(null)'
  param['account'] = 'technobureau_db'
  param['query'] = 'SELECT * FROM route_master('1506767265-8','4444407','');'
  param['results'] = 'true'
  param['handlers'] = 'pgsqldb:100'
  param['columns'] = '2'
  param['rows'] = '8'
  param['affected'] = '8'
  param['dbtype'] = 'pgsqldb'
2017-09-30_17:34:43.806147  Skipping void route #2
2017-09-30_17:34:43.806158  Skipping void route #3
2017-09-30_17:34:43.806172  Skipping void route #4
2017-09-30_17:34:43.806182  Skipping void route #5
2017-09-30_17:34:43.806192  Skipping void route #6
2017-09-30_17:34:43.806202  Skipping void route #7
2017-09-30_17:34:43.806211  Skipping void route #8
Call.cdr :
Status :hangup
Reason : Offline
 But actually user logged-in. Kindly help me out.
Regards,
Ganapathi



Thanks . I applied patch manually on source code. It's works perfectly.

And any idea about lazyrecording Module(lazyrecorder.cpp) to install to get recording the call completely.

Regards,
Ganapathi


From : aucyxob <aucyxob@y...>
TO : Ganapathi Chidambaram <ganapathi.rj@g...>, Yate mailing list <yate@v...>
Sent : Sun, Oct 1, 2017 12:59 PM,
Subject: Re: [yate] [ YATE 6.0 ] Routing call using PgSQL Stored function

Hello Ganapathi Chidambaram.
 
Your answer from the database should consist of one line and many columns with corresponding to parameters names. So your pgsql function should return something like this:
| location | callto.1 | dtmfpass | pbxassist | tonedetect_out | callto.1.secure | copyparams |
------------------------------------------------------------------------------------------------------------------------
| fork | sip/sip:.. | false | true | true | yes | pbxassist,dt..|
This is not very convenient if you need to return a different number of parameters each time.
If you want to return a result from the database as a key-value set
| key | value |
------------------------
| location | fork |
| callto.1 | sip/sip |
| dtmfpas | false |
....
you should consider the following solutions:
 
1. You can use extmodule or javascript module as proxy for database which will convert your resultset's columns key-value to yate's message param-value.
 
or use following yate's very useful fork https://github.com/vir/yate/wiki/MyChanges
 
register.conf
 
[call.route]
account=default
query=SELECT * FROM route_master('${billid}','${called}','${trace}');
result=location
priority=100
namevaluepairs=yes
 
PostgreSQL
 
CREATE OR REPLACE FUNCTION route_master(
    IN billid text,
    IN caller text,
    IN called text,
  )
  RETURNS TABLE(key text, value text) AS
$BODY$
DECLARE
    -- variables
BEGIN
-- do something
-- append lines to resultset
    key := 'location'; value := 'fork'; RETURN NEXT;
    key := 'copyparams'; value := 'pbxassist,dtmfpass'; RETURN NEXT;
 
    RETURN;
 
END;
$BODY$
  LANGUAGE plpgsql;
 
30.09.2017, 15:43, "Ganapathi Chidambaram" <ganapathi.rj@g...>:
Hi
 
As i used pgsql stored function to route a call to user/group. Whereas i am getting result properly but even though calls are not routing to the user .
 
find the below mentioned configuration for the same.
 
register.conf
 
query=SELECT * FROM route_master('${billid}','${called}','${trace}');
result=location
 
Output of PgSQL:
 
field value
trace 4444407
callto.1 sip/sip:4444407@1...:5060
dtmfpass false
location fork
pbxassist true
copyparams pbxassist,dtmfpass
tonedetect_out true
callto.1.secure yes
 
Logs :
 
<pgsqldb:ALL> Query for 'db.1' returned 8 rows, 8 affected [0xcbd510]
Returned true 'database' delay=0.230988
  thread=0x7f6e54002c10 'Call Router'
  data=0x7f6e4c006200
  retval='(null)'
  param['account'] = 'technobureau_db'
  param['query'] = 'SELECT * FROM route_master('1506767265-8','4444407','');'
  param['results'] = 'true'
  param['handlers'] = 'pgsqldb:100'
  param['columns'] = '2'
  param['rows'] = '8'
  param['affected'] = '8'
  param['dbtype'] = 'pgsqldb'
2017-09-30_17:34:43.806147 <register:WARN> Skipping void route #2
2017-09-30_17:34:43.806158 <register:WARN> Skipping void route #3
2017-09-30_17:34:43.806172 <register:WARN> Skipping void route #4
2017-09-30_17:34:43.806182 <register:WARN> Skipping void route #5
2017-09-30_17:34:43.806192 <register:WARN> Skipping void route #6
2017-09-30_17:34:43.806202 <register:WARN> Skipping void route #7
2017-09-30_17:34:43.806211 <register:WARN> Skipping void route #8
 
Call.cdr :
 
Status :hangup
Reason : Offline
 
But actually user logged-in.
 
Kindly help me out.

Regards,
Ganapathi