VoIP Mailing List Archives :: View topic - [asterisk-users] need examples of asterisk and mysql integration

Sponsor: VoiceMeUp - Corporate & Wholesale VoIP Services

VoIP Mailing List Archives
Mailing list archives for the VoIP community

[asterisk-users] need examples of asterisk and mysql integration

VoIP Mailing List Archives Forum Index -> Asterisk Users

View previous topic :: View next topic

Author

Message

eric.fort at gmail.com
Guest

Posted: Tue Apr 22, 2008 3:13 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

I'm presently working on a project to build a scheduling system accessible by both web and phone. on the web side one can query what items are available when by using the time or the item as a key then reserve for an available time slot. reservations may also be modified by the user that made them or an admin. Where may I find examples of doing similar things with asterisk? all I've been able to find thus far is examples of how to store call detail records and voicemail using a database. Thanks in advance, Eric P.S. Has anyone already built an asterisk/web based scheduling system like this?

Mike at Trest.COM
Guest

Posted: Tue Apr 22, 2008 3:30 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

Hi,

I suggest you look at writing a PERL agi program to handle all of
the MYSQL / DB
access and just pass variables between your CONTEXT/dialplan. I have done
a lot of these things. You can get PERL examples for DBI and use one of
provided agi scripts as a prototype.

..mike..

At 04:13 PM 4/22/2008, you wrote:

Quote:

I'm presently working on a project to build a scheduling system
accessible by both web and phone. on the web side one can query what
items are available when by using the time or the item as a key then
reserve for an available time slot. reservations may also be modified
by the user that made them or an admin. Where may I find examples of
doing similar things with asterisk? all I've been able to find thus
far is examples of how to store call detail records and voicemail
using a database.

Thanks in advance,

Eric

P.S.

Has anyone already built an asterisk/web based scheduling system like this?

_______________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --

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

michiel at vanbaak.info
Guest

Posted: Tue Apr 22, 2008 3:49 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

On 13:13, Tue 22 Apr 08, Eric Fort wrote:

Quote:

Hi,

With AGI you can write logic in whatever language fits you best.
if you like perl, use the asterisk-perl stuff.
if you like php, you can use the native mysql stuff
if you like bash, you can use the mysql commandline client
etc etc
You can even use the MYSQL dialplan functions. Not sure where they are,
I think in -addons.

--

Michiel van Baak
michiel at vanbaak.eu
http://michiel.vanbaak.eu
GnuPG key: http://pgp.mit.edu:11371/pks/lookup?op=get&search=0x71C946BD

"Why is it drug addicts and computer aficionados are both called users?"

bwentdg at pipeline.com
Guest

Posted: Wed Apr 23, 2008 12:11 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

Why would you go to the trouble of writing a PERL AGI and take the
Performance Hit of using AGI as opposed to using the "built-in" MYSQL
from the dial plan ?

Mike Trest - On Travel wrote:

Quote:

_______________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --

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

asterisk.org at sedwar...
Guest

Posted: Wed Apr 23, 2008 1:07 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

Quote:

At 04:13 PM 4/22/2008, you wrote:

Quote:

Mike Trest - On Travel wrote:

Quote:

I suggest you look at writing a PERL agi program to handle all of
the MYSQL / DB
access and just pass variables between your CONTEXT/dialplan. I have done
a lot of these things. You can get PERL examples for DBI and use one of
provided agi scripts as a prototype.

On Wed, 23 Apr 2008, Al Baker wrote:

Quote:

Why would you go to the trouble of writing a PERL AGI and take the
Performance Hit of using AGI as opposed to using the "built-in" MYSQL
from the dial plan ?

AGIs do not have a substantial "performance hit" and I think people need
to get this misconception out of their heads.

Writing AGIs in a scripting, non-compiled language may be great for
prototyping and proving concepts where performance is not expected to be
an issue. Personally, I don't write AGIs in anything but C. It's the
sharpest tool in my kit and I know it best.

A lowly 1.6GHz Celeron can execute over 100 AGIs per second. I strongly
suspect that the "startup hit" will pale to the "interpretation hit" of
the dialplan script quickly.

AGIs are a wonderful resource. You get the execution speed of an
application (minus the usually insignificant startup hit) in a "sandbox"
environment where you can't crash Asterisk and debugging is a couple of
orders of magnitude easier. You can even write multi-threaded AGIs if
needed. If you don't need to access the internals of Asterisk an AGI beats
an application in my book. Maybe I should write one...

I guess some day I'm going to have to cobble up one of my 2,000 line AGIs
(written in C) in "dialplan" so I can speak with authority from actual
experience. However, the absence of facts has never stopped me from
expressing an opinion Smile

It seems that "compartmentalizing" a boat load of complex code in "a real
language" (ducking quickly out of Tilghman's death glare) to be so natural
to me for so many reasons I don't know where to start. So I won't. Unless
asked Smile

Thanks in advance,
------------------------------------------------------------------------
Steve Edwards sedwards at sedwards.com Voice: +1-760-468-3867 PST
Newline Fax: +1-760-731-3000

tzafrir.cohen at xorco...
Guest

Posted: Wed Apr 23, 2008 1:20 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

On Wed, Apr 23, 2008 at 11:07:01AM -0700, Steve Edwards wrote:

Quote:

At 04:13 PM 4/22/2008, you wrote:

Quote:

Mike Trest - On Travel wrote:

Quote:

On Wed, 23 Apr 2008, Al Baker wrote:

Quote:

Why would you go to the trouble of writing a PERL AGI and take the
Performance Hit of using AGI as opposed to using the "built-in" MYSQL
from the dial plan ?

The dialplan isn't compiled either. Now, if it is simple enough to be
implemented with the dialplan, then you don't need to execute half the
logic in a separate environment and don't need the performance hit.

Anyway, what about dialplan logic in lua? Anybody actually uses that
thing?

--
Tzafrir Cohen
icq#16849755 jabber:tzafrir.cohen at xorcom.com
+972-50-7952406 mailto:tzafrir.cohen at xorcom.com
http://www.xorcom.com iax:guest at local.xorcom.com/tzafrir

asterisk.org at sedwar...
Guest

Posted: Wed Apr 23, 2008 1:58 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

Quote:

On Wed, Apr 23, 2008 at 11:07:01AM -0700, Steve Edwards wrote:

Quote:

On Wed, 23 Apr 2008, Tzafrir Cohen wrote:

Quote:

The dialplan isn't compiled either.

I guess I didn't make that point forcefully enough.

Quote:

Now, if it is simple enough to be implemented with the dialplan, then
you don't need to execute half the logic in a separate environment and
don't need the performance hit.

I'd like to put this "performance hit" thing to rest. I respect your
knowledge and expertise -- I always seem to learn something new (to me)
from your posts.

How would you "quantify" the "hit?" On my recently retired dev box (1.6GHz
Celeron), I could execute 100 AGIs per second but that doesn't really
answer the question.

Quote:

Anyway, what about dialplan logic in lua? Anybody actually uses that
thing?

Oh great -- another scripting language to learn Smile

Thanks in advance,
------------------------------------------------------------------------
Steve Edwards sedwards at sedwards.com Voice: +1-760-468-3867 PST
Newline Fax: +1-760-731-3000

Mike at Trest.COM
Guest

Posted: Wed Apr 23, 2008 2:29 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

At 02:58 PM 4/23/2008, you wrote: Re performance hit. I actually re-wrote one of my frequently used AGI in "C" and even set the STICKEY-BIT to avoid reloading the static text portions. I noticed slightly lower disk activity level (but the perl file was probably in memory cache too). So keeping it memory resident with stickey-bit probably was not really needed. But I did see a drop in over-all memory usage. Alas, none of these 'savings' were big enough to matter. I notice same performance level regards number of simultaneous calls - - - but that too was moderated by eternal load balancing distribution to multiple Asterisk gateways. For a slightly different twist. I re-designed the AGI to be only a message-passing function that talked via SOCKET to a always running service. I let that service do all the heavy lifting DB work and the AGI itself only asked questions and got replies. Again, no measurable difference in performance. So, I summarize to say that with over 6,000+ simultaneous call on 60 different Asterisk boxes, there was no difference in OPERATIONAL performance that the human operators could measure. Since any one box was running a balanced distribution, the large peaks of inbound calls did cause a problem either. The CPU cores (dual core Xeon) were seeing about the same utilization levels with lots of idle time left over. Probably because I was using more lower cost boxes spread about the US rather than fewer large boxes with a greater number of calls per box. As several folks have commented, everyone should write your AGI in whatever language you feel is best for your long term support needs because that is where your real costs savings are to be found. Getting a few more cycles from the CPU by squeezing the programming is not high on my list of priorities. IMHO, My suggestion to use perl AGI still stands as good advice on any current technology machine. ..mike..

tzafrir.cohen at xorco...
Guest

Posted: Wed Apr 23, 2008 3:32 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

On Wed, Apr 23, 2008 at 03:29:47PM -0400, Mike Trest - On Travel wrote:

Quote:

At 02:58 PM 4/23/2008, you wrote:
Re performance hit.

I actually re-wrote one of my frequently used AGI in "C" and even set
the STICKEY-BIT to avoid reloading the static text portions.

Doesn't Linux ignore the sticky bit?

Quote:

I noticed slightly lower disk activity level (but the perl file was
probably in memory cache too). So keeping it memory resident with
stickey-bit probably was not really needed. But I did see a drop in
over-all memory usage. Alas, none of these 'savings' were big
enough to matter.

There's a difference between C and a dynamic language engine (such as
perl and PHP).

To give you some idea of that, let's look at some "do nothing" loops.

# For a base line: pure internal shell:

$ cnt_list=`seq 100`; time ( for i in $cnt_list; do :; done )

real 0m0.001s
user 0m0.000s
sys 0m0.000s

# What happens if I run a subshell?

$ cnt_list=`seq 100`; time ( for i in $cnt_list; do ( Smile

; done )

real 0m0.240s
user 0m0.036s
sys 0m0.204s
# Now a small C program:

$ cnt_list=`seq 100`; time ( for i in $cnt_list; do /bin/true; done )

real 0m0.272s
user 0m0.052s
sys 0m0.216s

Slightly worse.

Now a very minimal perl script:

$ cnt_list=`seq 100`; time ( for i in $cnt_list; do perl -e ''; done )

real 0m0.570s
user 0m0.220s
sys 0m0.352s

# Not so bad, right? But it didn't actually have to compile anything.
# Let's give it something to compile:

$ cnt_list=`seq 100`; time ( for i in $cnt_list; do perl -MCGI -e ''; done
)

real 0m4.447s
user 0m2.884s
sys 0m1.564s

# Likewise PHP5

$ cnt_list=`seq 100`; time ( for i in $cnt_list; do php5 -r ''; done )

real 0m3.241s
user 0m1.524s
sys 0m1.716s

# Python:

$ cnt_list=`seq 100`; time ( for i in $cnt_list; do python -c ''; done )

real 0m2.618s
user 0m1.684s
sys 0m0.932s

As you can see, each of them used way more than one second of pure CPU
time for 100 runs (except the C one, and the perl-without-modules one).
And this is with very optimal caching and such.

My system is not top of the line:

processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 44
model name : AMD Sempron(tm) Processor 2600+
stepping : 2
cpu MHz : 1599.856
cache size : 128 KB
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext fxsr_opt
lm 3dnowext 3dnow up pni lahf_lm
bogomips : 3203.56
TLB size : 1024 4K pages
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management: ts ttp tm stc

Kernel: Linux frenkel 2.6.22-3-amd64 #1 SMP Tue Feb 12 09:22:35 UTC 2008
x86_64 GNU/Linux

--
Tzafrir Cohen
icq#16849755 jabber:tzafrir.cohen at xorcom.com
+972-50-7952406 mailto:tzafrir.cohen at xorcom.com
http://www.xorcom.com iax:guest at local.xorcom.com/tzafrir

bwentdg at pipeline.com
Guest

Posted: Wed Apr 23, 2008 4:10 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

I did not mean to stir up a hornet's nest or religious war Smile

The BASIC QUESTION I was trying to ask is this...
"Since the MYSQL add-on provides a way to interface with MySQL
what is it that one gains or is trying to gain by writing their OWN
AGI script to do the interface ?"

The only reason I mention this being a "performance hit" was that the
book ATFOT
mentioned that AGI scripts in general were less efficient and therefore
one should
do all possible work in the dial plan.

Maybe this is true, maybe this is not true.

Regardless.....

I would just like to borrow from the expertise and experience of other
and ask the BASIC QUESTION above and hope that folks will share their
experiences. I'm pretty sure they did what they did for very very good
reasons that I am just not experienced enough yet to know.

So.... The answer to the questions is........ ???

Steve Edwards wrote:

Quote:

On Wed, Apr 23, 2008 at 11:07:01AM -0700, Steve Edwards wrote:

Quote:

On Wed, 23 Apr 2008, Tzafrir Cohen wrote:

Quote:

The dialplan isn't compiled either.

I guess I didn't make that point forcefully enough.

Quote:

Now, if it is simple enough to be implemented with the dialplan, then
you don't need to execute half the logic in a separate environment and
don't need the performance hit.

Quote:

Anyway, what about dialplan logic in lua? Anybody actually uses that
thing?

Oh great -- another scripting language to learn Smile

Thanks in advance,
------------------------------------------------------------------------
Steve Edwards sedwards at sedwards.com Voice: +1-760-468-3867 PST
Newline Fax: +1-760-731-3000

_______________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --

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

asterisk.org at sedwar...
Guest

Posted: Wed Apr 23, 2008 6:29 pm Post subject: [asterisk-users] need examples of asterisk and mysql integra

On Wed, 23 Apr 2008, Al Baker wrote:

Quote:

I like doing "serious work" in an AGI instead of the dialplan because:

1) It allows me to use a compiled language. Not just for performance
reasons, but because a compiler (or a strict interpreter) helps me protect
me from me. My production dialplan is just over 600 lines. The sources to
my AGIs are a bit more than 15,000 lines. I like that if I "fat-finger" a
variable, the compiler will help me. If I "fat-finger" something in 16,000
lines of dialplan will I ever find it?

2) It allows me to hide complexity. I like having a single statement in my
dialplan that says "agi(block-ani)." I know that in this single statement
I am invoking code that acts as the gatekeeper to my system, allowing me
to block callers by area code, area code and prefix, and the complete
subscriber number. I know it works well and I don't have to look at it any
more.

3) I can "share" better. It is easier to integrate a single statement into
an existing dialplan than 2,000 statements with potential conflicts in
context, template, and variable names.

4) I don't have to learn (what appears to me to be) a really obtuse
syntax. Funky quoting and whitespace rules lead to accelerated hair loss.

5) I have a "full" toolbox. For instance, one of my AGIs (play-path) lets
me pass a path and it returns the name of a WAV file in that path at
random. Calling ftw() was a simple solution. Another AGI (auth-card) lets
me submit an authorization request to my credit card processor. While
waiting for the card response, I play "Please wait while we validate your
card" in another thread in the same AGI. By the time the "STREAM FILE" is
finished, I have the response so to the customer it appears
"instantaneous." How would I do either of these in "dialplan?"

Thanks in advance,
------------------------------------------------------------------------
Steve Edwards sedwards at sedwards.com Voice: +1-760-468-3867 PST
Newline Fax: +1-760-731-3000

bwentdg at pipeline.com
Guest

Posted: Thu Apr 24, 2008 12:39 am Post subject: [asterisk-users] need examples of asterisk and mysql integra

Thx so much for taking the time to share.
Damn Insightful && Damn Helpful
THANKS!

Steve Edwards wrote:

Quote:

On Wed, 23 Apr 2008, Al Baker wrote:

Quote:

Display posts from previous:

	VoIP Mailing List Archives Forum Index -> Asterisk Users	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum