Are MySQL Tables gone?

[jackrandle]

pbxiaf = 1.7.5.5
FreePBX Version = 2.8.0.4
AsteriskVersion=Asterisk1.8.2.3

I can't seem to find the mysql tables. I plan to do some custom database lookup using callerid info. What Happenned?

I'm logging in to MYSQL as user and no password.
Only test and system_schema databases exist.
root, maint, admin does NOT work with the Master Password
I used to be able to view MySQL data

Is SQLITE the new default manager?
If so how can I get mysql back?
Or point me me in the right direction.
Thanks

 

[tm1000]

You are using the wrong credentials.

user=asteriskuser
pass=amp109
database=asterisk

The asterisk database will only show up to root and asteriskuser (and NO that's not the root you use to login to ssh)

root@pbx:~ $ mysql -u asteriskuser -pamp109
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| asterisk           |
| asteriskcdrdb      |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

 

[darmock]

sqlite is used for the PIAF registry only and nothing else. (unless some other third party addon uses it)


Tom

 

[jackrandle]

Thank You,
I want to update an external mysql database, with incoming callerid info. Should I use sperfecta?

Where do I find documentation on how to update mysql from superfecta?

 

[jackrandle]

Also could you give me a clue as to where I can find the script that writes the asteriskcdrb database?

 

[jackrandle]

I have done some PHP/MySQL coding, I use MySQL a lot, but mostly using Linux/Gambas.

I would love to contribute, plus it wouldn't be a bad way to find my way around asterisk/and freepbx.



thanks.

 

[jackrandle]

2 newbie stupid questions

1. How to upgrage from superfecta 2.2.3 to 2.2.4

2. Where am I supposed to be when I get this 'mouseover' help?

I looked at the old 2.2.3 php code, pretty stright forward, but to get up to spped I need to see the script that calls it.

Thanks, and I will sign up at the dev site, but I want to get grounded in the code.

 

[jackrandle]

Thanks, I'm ready to roll up my sleeves and get cracking, but got to go out of town for a quick install and $$$. When I get back later this week, I'm all over this.

One last request, where in the dial plan does the callerid.php script get called? This should help me better get a grasp and get me up to speed.

Along the same lines, do you know what script gets called when the cdr table in the asteriskcdrdb database gets updated? And also where in the DialPlan?

This should enlighten me to when where and how to create new MySQL magic when a call comes in and also when a call closes.

Once again, thanks, I really am JAZZED about this opportunity to create something useful.

 

[tm1000]

What are you trying to do? callerid.php is part of the module caller id superfecta. Did you know there is already a mysql sub module for superfecta where you can do any query you want? Or you could write your own to do something else. I wouldn't try to re-create the wheel here but you haven't even explained what you are trying to do so I have no idea.......

It also depends on what version of superfecta you have as one modifies the dialplan directly and the other version does not.

And BTW the CDR table is updated well after superfecta is run. The values for superfecta are stored in the database (the ASTERISK database NOT mysql)

Anyways if you could be less vague that will help many of us.

Thanks, I'm ready to roll up my sleeves and get cracking, but got to go out of town for a quick install and $$$. When I get back later this week, I'm all over this.

One last request, where in the dial plan does the callerid.php script get called? This should help me better get a grasp and get me up to speed.

Along the same lines, do you know what script gets called when the cdr table in the asteriskcdrdb database gets updated?

This should enlighten me to when where and how to create new MySQL magic at when a call comes in and also when a call closes.

Once again, thanks, I really am JAZZED about this opportunity to create something useful.

 

[jackrandle]

Okay not to be vague,

when a call comes in or is dialed out, I need to update a mysql table on another server. This table keeps the status of each call and callerid/callername,

when a call is closed I also need to update the same table.

Each user at the customer site has a popup application running on his linux workstation that reads this status table and performs further queries based on calls/sales analysis. This information is displayed in real time before anyone answers the call.

There is another application that reads an RS232 serial line coming from a callerid box tethered to an ancient 5 line POTS/rollover system. That application updates the status table that in turn trigger the client application.

I need to be able to deliver the same information as we migrate to an Asterisk system.

So that is why I need the hints on when/where in the dialplan that superfecta does its' magic, and why I need to know where the final Mysql update to the cdr table is done.

I thank you guys for helping me figure out and learn more about superfecta and the great work already done, and look forward to being able to contribute.

Randy

 

[jackrandle]

Here is an image of the user's pop up of the application. If the caller is identified as a customer the order button turns green and the operator can click and take orders.

I know I will benefit from the work already done. My thinking is,

1. I can use superfecta's code to update the call status on the remote mysql server for incoming calls.

2. Figure out when/where in the dialplan the cdr table is updated, and mimic that to update the status and write call history when ALL calls terminate. (inbound and outbound)

3. The final piece to my puzzle is to figure out where in the dial plan to update the remote server status for outbound calls. Probably call a custom script when the outbound call is answered. Do you have any ideas?

 

[jackrandle]

If not using Superfecta, Where is the earliest in the dialplan I can call a custom script to update my remote mysql server for an incoming call?

Basically ringgroup777 is when all phones start ringing, callerid should already be availble? so I should be able to call a script, right?

 

[tm1000]

You'd have to send the call to a Misc Destination which is then attached to a number (like 4000) which then runs an AGI script and then you can send them back to the dial plan.

It's a lot of work that you could just get away with using CallerID Superfecta for.

Furthermore I'm not sure why you aren't going to use CallerID Superfecta. I have PSTN lines so technically I don't have to use it, but the day I did my clients were amazed that they could see the CallerID for so many new people. I'd never go back now.

Also there is no possible way to tap into the outgoing dial plan (that I know of).

 

[wardmundy]

For inbound call processing, take a look at this Nerd Vittles article. For outbound call processing, see this article. Some of the inbound code no longer works because of Twitter changes, but it'll give you some ideas on how to do what you're looking to do.

 

[tm1000]

Yup. That will work great for the incoming stuff. But I still don't know how he can monitor outgoing.

Take a look at this Nerd Vittles article. Some of the code no longer works because of Twitter changes, but it'll give you some ideas on how to do what you're looking for.

 

[jackrandle]

I'm grasping this slowly. And am very grateful for your loyalty by sticking with me while I work this out.

Ultimately the Incoming calls are the priority of this application. Outbound is somewhat important, and I can probably piggyback on the CDR when the call completes, However It would be useful to have it in realtime.

The twitter article looks promising, and is very informative on how to create a PHP script, that should get me up to speed with the inbound solution. I'll keep you guys in the loop as things develop when I get back in town and revive the project.

As for supefecta lookup, all we are concerned about is getting the phone number, and writing the status of the line to the remote database. The existing client application running on each user's desktops queries our database to see if the incoming call is an existing customer and performs the sales queries. If not it then performs dialog to identify unrecognized phoneids and will create a new customer account if necessary.

I am thinking further improvement will show, instead of the static line number typical of POTS, that field will show the realtime destination, example (RINGING, Whose Extention, Parking Lot, etc.)

I know the operator's panel has that already. Isn't that PHP/MySQL?

Comments?

So why isn't AGI available during outbound calls? Shouldn't I be able to execute an AGI script as soon as an outbound call connects?

 

[jackrandle]

I've got incoming calls updating my Remote MySQL database!!! ( Sip and Google Trunks. Maybe even a Skype trunk next.)
I am well on my way to making our application work with PBXIAF. Thanks Lome and Tony.
I think we can let this thread age now.
One last thing, After our New AGI updates our MySQL I call ring groups777. Is there anyway to seize the dial plan again and spawn another AGI when an extension answers? And when a transfer takes place? And then when the call terminates?

It seem like the panel on the freepbx is doing it.

 

[jackrandle]

Thanx to Wardmundy's input I was able to use the AGI technique used by the Twiddldee twitter application. It is really stripped down. because I am only delivering the phoneid to one table on the remote mysql server.

Thats all that was needed, as the remote desktop clients already do all the extra queries to deliver real-time customer's sales data. Now I get to redesign the pop-up to allow for the extra sip channels versus the single channel POTS lines offer.

For further enhancement and functionality I need to figure out how to fire off more AGI after the call gets picked up by an extension or transferred to anywhere. Any Ideas?