[Asterisk-Users] Nested MySQL Commands

[Douglas Garstang]

We are trying to implement findme/followme. 

The dialplan first queries the dialled number in the database and determines if it is OnNet(IP) or OffNet(PSTN). If it is OnNet, it then queries a findme/followme table, which looks like this:

mysql> select * from ast_findme;                                
+----+-----------+----------+-------------------------------+-------------------------+
| id | extension | sequence | dial                          | playback                |
+----+-----------+----------+-------------------------------+-------------------------+
|  1 | 3254101   |        1 | SIP/3254102&SIP/3254101|10|tr | wait-offensive-sounds   |
|  2 | 3254101   |        2 | SIP/3254101|10|tr             | call-forward            |
|  3 | 3254101   |        3 | SIP/13033372500               | nbdy-avail-to-take-call |
+----+-----------+----------+-------------------------------+-------------------------+
3 rows in set (0.00 sec)

The dialplan will retrieve each record for the called number, one at a time, and attempt to dial the new number. The 'dial' column is passed directly to the Dial() command. However, before it can dial that number it has to check once again and see if the new number is OnNet or OffNet. If it is OffNet, send the call off to the proxy server. If it is OnNet, dial the number directly.

As you can see, this isn't trivial. I'm no MySQL expert, and I can't see a way to do this without first obtaining each new number, and then querying it individually to determine if it is OnNet or OffNet before dialling it.

Douglas.

-----Original Message-----
From: Chris Albertson [mailto:chrisalbertson90278 at yahoo.com]
Sent: Wednesday, January 11, 2006 12:40 PM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] Nested MySQL Commands





> ....Send a 
> query over to the MySql server with only the required 
> parameters and have it do all the processing for you and 
> only returns the results.

THe above describes the "whole point" of a SQL DBMS Server.
WHat else could you ask one to do for you?

Back to the original question:  "Can you use the result of
one query to feed another without going to the DBMS twice?
Yes, Of course SQL allows this.

In fact Data base experts
would be horrified to see any code that querries a value,
holds that value and then querries based on that value.
Doing this without holding a lock on the relevent tables is
just plain wrong and will result in bugs.  You should
_always_ write the SQL querry such that only one querry
gives you the results you need.  Manual Locks are not good, they
can be the source of very serious performance problems.

For more specific advice you would need to post the details
of what you are tring to do and a bit of the SQL you are
using.

> 
> I know that is a nice feature od Microsoft Sql. But have 
> not had a chance to read up on the performance of the new 
> version of MySql...

Basically MySQL is very fast when the load is light but scales
very poorly with either higher loads or higher conplexity
querries.  It is good for "flat file like" problems.  The
larger DBMSes are slower initially but scale better.

(Please CC any replies to my direct email)



Chris Albertson
  Home:   310-376-1029  chrisalbertson90278 at yahoo.com
  Cell:   310-990-7550
  Office: 310-336-5189  Christopher.J.Albertson at aero.org
  KG6OMK

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
_______________________________________________
--Bandwidth and Colocation provided by Easynews.com --

Asterisk-Users mailing list
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-users