[Asterisk-Users] options for mysql query from dialplan

[Damon Estep]

Could we not do away with PHP and AGI if realtime extensions had the
ability to extend the pattern match query from _ to _ plus (n) number of
dialed digits from the left?

Damon

> -----Original Message-----
> From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-
> bounces at lists.digium.com] On Behalf Of Matthew Boehm
> Sent: Thursday, August 18, 2005 10:35 AM
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Subject: Re: [Asterisk-Users] options for mysql query from dialplan
> 
> Hi Damon,
>   You are basically doing EXACTLY what we are doing right now; except
we
> are doing more.
> 
> We now have an AGI PHP script that does the following for every call:
> 
> - Connect to MySQL over LAN
> - If the dialed number begins with 1, strip it.
> - SELECT State FROM lcr_lata WHERE NPA = $dial_npa AND NXX = $dial_nxx
> - Do some PHP logic to determine if Interstate vs Intrastate
> - SELECT rate, address, technology, prefixes FROM lcr_rates
>      LEFT JOIN lcr_carriers USING(carrierid)
>      WHERE NPA = $dial_npa AND NXX = $dial_nxx
>      AND carrier_active = 1 ORDER BY rate ASC;
> - Loop thru results.
> 
> lcr_rates has 329,530 rows.
> lcr_carriers has 8 rows.
> lcr_lata has over 150,000 rows.
> 
> Everything preforms in real time.
> 
> Here is a sample query of a call that just went thru:
> 
> SELECT r.Interstate, rc.name, rc.technology, rc.address, rc.prefix
FROM
> lcr_rates r LEFT JOIN lcr_carriers rc ON r.CarrierId = rc.id WHERE
r.NPA
> = '254' AND r.NXX = '463' AND r.active = 1 ORDER BY r.Intrastate ASC,
> r.NPA DESC, r.NXX DESC
> 
> Query took 0.0025 sec.
> 
> I don't see how your table with 300K rows is preforming worse than
ours.
> You got indexes?
> 
> To make this even better, our MySQL server is a Quad P3 500 Mhz
machine.
> 
> Works great here.
> 
> -Matthew
> 
> Damon Estep wrote:
> > I am using realtime mysql for extensions, sip, and voicemail.
> >
> > Outbound call routing does not really perform well in realtime
> > extensions due to the high number of rows in the database (300k), so
I
> > can not use it. It appears with my limited knowledge that the query
> > method is not robust enough for large databases.
> >
> > Given the fact that I already have realtime and mysql configured,
what
> > are my options for running a mysql query from the dialplan to find
the
> > provider I want to use for outbound.
> >
> > I am not looking for a complete solution, just a hint on the best
way to
> > query my existing mysql database from the dialplan.
> >
> > I have looked at the MySQL command, and there are a lot of notes
about
> > connection closing and other scary stuff? Does it work?
> >
> > Are there other native options given the fact that realtime is
> > configured and in use?
> >
> > The goal is to run a query against a database like this
> >
> > SELECT provideralias FROM ldproviders WHERE npa = (digits 2 thru 4
of
> > dialed number) AND nxx = (digits 5 thru 7)
> >
> > Then take the provider alias returned and
> > Dial(SIP/${EXTEN}@${provideralias},60).
> >
> > Next step would be to add a loop for multiple providers, starting
with
> > the lowest cost.
> >
> > Any hints or comments from the pros?
> >
> > TIA
> >
> > Damon
> >
> >
> > _______________________________________________
> > Asterisk-Users mailing list
> > Asterisk-Users at lists.digium.com
> > http://lists.digium.com/mailman/listinfo/asterisk-users
> > To UNSUBSCRIBE or update options visit:
> >    http://lists.digium.com/mailman/listinfo/asterisk-users
> >
> 
> 
> _______________________________________________
> Asterisk-Users mailing list
> Asterisk-Users at lists.digium.com
> http://lists.digium.com/mailman/listinfo/asterisk-users
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users