[Home]

Summary:ASTERISK-02916: pbx_realtime.c - Docs and Minor Tweaks
Reporter:drmac (drmac)Labels:
Date Opened:2004-12-01 23:56:15.000-0600Date Closed:2011-06-07 14:11:59
Priority:TrivialRegression?No
Status:Closed/CompleteComponents:Core/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:Its hard to figure something out without seeing an example so I did this by trial and error and now I am sharing(karma++).

(This is going to be awesome.)

To pull your extension dialplan out of a database:

Create a context in your extensions.conf a la below.
Add the family name you added in extensions.conf to your extconfig.conf and map it somewhere.
Create a table in your database with at least the following columns: context, exten, priority, app, appdata
Insert some data into table.
Send a call to the context you created.
If all goes well, * should query the database driver and table in your extconfig.conf and select out all the rows that contain ( the context you specified in your switch=> line ) AND ( where exten equals the extension you passed to this switch)

NOTE: I don't believe pattern matching works.

[test]
;
; switch => Realtime/[context]@[family][/options]
; If context is not given, defaults to current context
; If family is not given, defaults to 'extensions'
; There aren't currently any options
;
switch => Realtime/mycontext@mytable


****** ADDITIONAL INFORMATION ******

Firstly, In My Very Humble Opinion, all references to "table" inside the pbx_realtime.c code need to be changed to "family" as that is how RealTime stuff is refered to in extconfig.conf

2ndly, on line 51 it says your table needs to provide args. But in realtime_exec, its actually looking for a column called 'appdata'.

3rdly, pattern matching doesn't seem to work. I put "_281569XXXX" into the extension column of my table and I tried to call 2815698574, and (using SJPhone) I got back a "404 Not Found" message. Additionally, my debug showed the following SQL attempts:

MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815698574' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten LIKE '2815698574%' AND context = 'mycontext' AND priority = '1'

4th. I can't find this anywhere but I'm not that familiar with the PBX code, so perhaps Mark will have better luck; Here is what my MySQL RealTime debug shows when I dial an exact extension (which worked BTW):

Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM customer_lines WHERE name = '3047'
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:22 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:26 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '2'
Dec  1 22:41:26 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:26 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:26 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:26 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten LIKE '_%' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:26 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:36 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:36 DEBUG[32411]: MySQL RealTime: Everything is fine.
Dec  1 22:41:36 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = 't' AND context = 'mycontext' AND priority = '1'
Dec  1 22:41:36 DEBUG[32411]: MySQL RealTime: Everything is fine.
###### Somewhere around here is where I hung up the call ######
Dec  1 22:41:36 DEBUG[32411]: MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = 'h' AND context = 'mycontext' AND priority = '1'

I'm curious as to why realtime tries to select priority 1 several times like that.

OK. Ranted on long enough. Bedtime. Will post more as I delve more into it.
Comments:By: drmac (drmac) 2004-12-01 23:58:48.000-0600

Side note: The line about "customer_lines" is sipfriends RealTime doing is awesome job...
By: drmac (drmac) 2004-12-02 00:17:30.000-0600

(why am I not asleep...)

After looking at more code (rather than sleeping...) I'm thinking that pattern matching isn't working because my MySQL RealTime driver doesn't have a realtime_multi function. Hmm...

will be back tomorrow with realtime_multi for MySQL..then will test again..
By: drmac (drmac) 2004-12-02 11:37:42.000-0600

Wow. Now that I got some sleep I can work right.

I added in realtime_multi support to my MySQL driver and (suprise, suprise) pattern matching works. Yea!

Still don't know why there are so many attempts. Here is the debug output of me dialing 2815699914 from SJPhone (I allowed it to timeout instead of clicking on 'hang up'):

[root@dev-asterisk asterisk]# tail -f /var/log/asterisk/debug | grep MySQL
MySQL RealTime: Update SQL: UPDATE customer_lines SET ipaddr = '64.72.107.1', port = '2401', regseconds = '1102004286', username = '3047' WHERE name = '3047'
MySQL RealTime: Updated 1 rows on table: customer_lines
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '2'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten RLIKE '_.*' AND context = 'mycontext' AND priority = '2' ORDER BY exten
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '2'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten RLIKE '_.*' AND context = 'mycontext' AND priority = '2' ORDER BY exten
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '2'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten RLIKE '_.*' AND context = 'mycontext' AND priority = '2' ORDER BY exten
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '3'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten RLIKE '_.*' AND context = 'mycontext' AND priority = '3' ORDER BY exten
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '2815699914' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten LIKE '_%' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = '' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten RLIKE '_.*' AND context = 'mycontext' AND priority = '1' ORDER BY exten
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = 't' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten RLIKE '_.*' AND context = 'mycontext' AND priority = '1' ORDER BY exten
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten = 'h' AND context = 'mycontext' AND priority = '1'
MySQL RealTime: Retrieve SQL: SELECT * FROM realtime WHERE exten RLIKE '_.*' AND context = 'mycontext' AND priority = '1' ORDER BY exten

and the entire contents of the table in question:

mysql> select * from realtime;
+----+-----------+-------------+----------+-----------+---------+
| id | context   | exten       | priority | app       | appdata |
+----+-----------+-------------+----------+-----------+---------+
|  1 | mycontext | 2815699914  |        1 | SayNumber | 1543    |
|  2 | mycontext | _281569XXXX |        2 | SayNumber | 8574    |
+----+-----------+-------------+----------+-----------+---------+

It all worked. I heard Allison say one thousand..blah blah..and then 8 thousand..blah blah..

This is going to be awesome for customers. Soon they will be able to change their own dialplan (to an extent) via website.
By: Brian West (bkw918) 2004-12-02 12:41:42.000-0600

all of those requests are becuse the switch API is really anal.. its just checking again and again to make sure the extension didn't go away... CAN WE SAY CACHE IT.. COPY IT.. or something...

bkw
By: Terry Wilson (twilson) 2004-12-10 14:46:38.000-0600

I don't know if I should add this a separate bug, or add it to this one, but I suppose it is obvious which I chose to do... :-)

If I have a situation like:

[user]
exten => 123,1,Goto(outbound|123|1)

[outbound]
include => free-outbound
include => toll-outbound

[free-outbound]
switch => Realtime

and have a realtime extensions entry for exten 123 in [free-outbound], it fails to find the extension.

if I have:

[user]
exten => 123,1,Goto(free-outbound|123|1)

it works fine.

The SQL logs show: SELECT * FROM extensions WHERE exten = '123' AND context = 'free-outbound' AND priority = '1'
SELECT * FROM extensions WHERE exten = '123' AND context = 'free-outbound' AND priority = '1'
SELECT * FROM extensions WHERE exten = '123' AND context = 'outbound' AND priority = '1'
SELECT * FROM extensions WHERE exten RLIKE '_.*' AND context = 'outbound' AND priority = '1' ORDER BY exten
SELECT * FROM extensions WHERE exten = 'h' AND context = 'free-outbound' AND priority = '1'
SELECT * FROM extensions WHERE exten RLIKE '_.*' AND context = 'free-outbound' AND priority = '1'

obviously the query looking for 123 with context outbound fails.  As a side note: Postgres doesn't like RLIKE.  The equivalent operator for it is '~'.
By: Brian West (bkw918) 2004-12-10 14:50:30.000-0600

BZZT thanks for playing.

switch => Realtime/context@family

NEXT!!!
By: Terry Wilson (twilson) 2004-12-10 15:04:27.000-0600

Well, I suppose that does fix it... doesn't it.  :-)  In my defense, the README.extconfig and pbx_realtime.c said I could do it without the specifying the table/context...

Any ideas on how to make it portable for both MySQL and PostgreSQL to work through ODBC?
By: Brian West (bkw918) 2004-12-10 15:31:41.000-0600

ODBC already works with MySQL and PGSQL www.unixodbc.org

bkw
By: Terry Wilson (twilson) 2004-12-10 15:37:45.000-0600

See above: The RLIKE query fails with postgres.  No RLIKE operator.  Operator for regexing is '~'.  Should Mysql/Postgres be a compile time option to fix this?
By: drmac (drmac) 2004-12-10 15:57:29.000-0600

> In my defense, the README.extconfig and pbx_realtime.c said I
> could do it without the specifying the table/context...

You are correct sir, but you missed the part on how the @ sign is NOT optional.

switch => Realtime/@
By: drmac (drmac) 2004-12-10 16:03:26.000-0600

> See above: The RLIKE query fails with postgres. No RLIKE operator.
> Operator for regexing is '~'. Should Mysql/Postgres be a compile
> time option to fix this?

If its a compile time option, how will you support those who need dual MySQL/PostgreSQL support from ODBC?

RLIKE works with MySQL. Is RLIKE not an ANSI standard thingy? Is ~ ANSI standard?

Does postgres have any other regex stuff? MySQL has several aliases for RLIKE: http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html

Perhaps one of them works with Postgres?
By: drmac (drmac) 2004-12-10 17:11:26.000-0600

Oh crap. He was right:

README.extconfig
-------------------
<snip>

In the dialplan you just use the Realtime switch:

[foo]
switch => Realtime

or

[bar]
switch => Realtime/bar@extensions


Woopps. Either the README is wrong or something..
By: Terry Wilson (twilson) 2004-12-10 17:21:57.000-0600

As far as I can tell, regular expression searching isn't part of SQL92.  It is part of the SQL3 (SQL99) standard under the "SIMILAR TO" construct.  It looks like  Postgres supports this, while (to my knowledge) MySQL does not.
By: Terry Wilson (twilson) 2004-12-10 17:23:39.000-0600

The shortcut way shown in the README works fine, but only if you are not doing a goto to a context thant includes another context that has your switch in it.  :-)
By: Mark Spencer (markster) 2004-12-10 22:53:08.000-0600

Actually this really was a bug, your example should have worked and will now work in CVS.
By: Brian West (bkw918) 2004-12-12 20:47:13.000-0600

http://www.voip-info.org/tiki-index.php?page=Asterisk+RealTime
By: Digium Subversion (svnbot) 2008-01-15 15:16:15.000-0600

Repository: asterisk
Revision: 4423

U   trunk/pbx.c

------------------------------------------------------------------------
r4423 | markster | 2008-01-15 15:16:14 -0600 (Tue, 15 Jan 2008) | 2 lines

Make sure context is passed properly on _exec (bug ASTERISK-2916)

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk?view=rev&revision=4423