Managing Databases
Managing Databases
While it isn’t within the scope of this book to teach you about how to manage your databases, it is at least worth noting briefly some of the applications you could use to help with database management. Several exist, some of which are local client applications running from your computer and connecting to the database, and others being web-based applications that could be served from the same computer running the database itself, thereby allowing you to connect remotely.
Some of the ones we’ve used include:
• phpMyAdmin
• MySQL Workbench
• pgAdmin
• Navicat (commercial)
Troubleshooting Database Issues
When working with ODBC database connections and Asterisk, it is important to remember that the ODBC connection abstracts some of the information passed between Asterisk and the database. In cases where things are not working as expected, you may need to enable logging on your database platform to see what Asterisk is sending to the database (e.g., which SELECT , INSERT , or UPDATE statements are being triggered from Asterisk), what the database is seeing, and why the database may be rejecting the statements.
For example, one of the most common problems found with ODBC database integration is an incorrectly defined table or a missing column that Asterisk expects to exist. While great strides have been made in the form of adaptive modules, not all parts of Asterisk are adaptive. In the case of ODBC voicemail storage, you may have missed a column such as flag , which is a new column not found in versions of Asterisk prior to 11. 4 In order to debug why your data is not being written to the database as expected, you should enable statement logging on the database side, and then determine what statement is being executed and why the database is rejecting it.
SQL Injection
Security is always a consideration when building networked applications, and database security is no exception.
In the case of Asterisk, you have to give thought to what input you are accepting from users (typically what they are able to submit to the dialplan), and work to sanitize that input to ensure you are only allowing characters that are valid to your application. As an example, a typical telephone call would only allow digits as input (and possibly the * and # characters), so there would be no reason to accept any other characters. Bear in mind that the SIP protocol allows more than just numbers as part of an address, so don’t assume that somebody attempting to compromise your system is limited to just digits. A little extra time spent sanitizing your allowed input will improve the security of your application.
Powering Your Dialplan with func_odbc
The func_odbc dialplan function is arguably the coolest and most powerful dialplan function in Asterisk. It allows you to define and use relatively simple functions in your dialplan that will retrieve information from databases as calls are being processed. There
are all kinds of ways in which this might be used, such as managing users or allowing the sharing of dynamic information within a clustered set of Asterisk machines. We won’t claim that this will make designing and writing dialplan code easier, but we will promise that this will allow you to add a whole new level of power to your dialplans, especially if you are comfortable working with databases. So far we can’t think of anyone who does not love func_odbc .
What func_odbc allows you to do is define SQL queries to which you assign function names. In effect, you are creating custom functions that obtain their results by executing queries against a database. The func_odbc.conf file is where you specify the relationships between the function names you create and the SQL statements you wish them to perform. By referring to the named functions in the dialplan, you can retrieve and update values in the database.
In order to get you into the right frame of mind for what follows, we want you to picture a Dagwood sandwich. Can you relay the total experience of such a thing by showing someone a picture of atomato, or by waving a slice of cheese about? Not hardly. That is the conundrum we faced when trying to give useful examples of why func_odbc is so powerful. So, we decided to build the whole sandwich for you. It’s quite a mouthful, but after a few bites of this, peanut butter and jelly is never going to be the same.
A Gentle Introduction to func_odbc
Before we dive into func_odbc , we feel a wee bit of history is in order.
The very first use of func_odbc , which occurred while its author was still writing it, is also a good introduction to its use. A customer of one of the module’s authors noted that some people calling into his switch had figured out a way to make free calls with
his system. While his eventual intent was to change his dialplan to avoid those problems, he needed to blacklist certain caller IDs in the meantime, and the database he wanted to use for this was a Microsoft SQL Server database.
With a few exceptions, this is the actual dialplan:
[span3pri]
exten => _50054XX,1,NoOp()
same => n,Set(CDR(accountcode)=pricall)
; Does this callerID appear in the database?
same => n,GotoIf($[${ODBC_ANIBLOCK(${CALLERID(number)})}]?busy)
same => n(dial),Dial(DAHDI/G1/${EXTEN})
same => n(busy),Busy(10) ; Yes, you are on the blacklist.
same => n,Hangup
This dialplan, in a nutshell, passes all calls to another system for routing purposes, except those calls whose caller IDs are in a blacklist. The calls coming into this system used a block of 100 seven-digit DIDs. You will note a dialplan function is being used that you won’t find listed in any of the functions that ship with Asterisk: ODBC_ANIBLOCK() . This function was instead defined in another configuration file, func_odbc.conf:
[ANIBLOCK]
dsn=telesys
readsql=SELECT IF(COUNT(1)>0, 1, 0) FROM Aniblock WHERE NUMBER=’${ARG1}’
So, your ODBC_ANIBLOCK() 7 function connects to a data source in res_odbc.conf named telesys and selects a count of records that have the NUMBER specified by the argument, which is (referring to our dialplan above) the caller ID. Nominally, this function should return either a 1 (indicating the caller ID exists in the Aniblock table) or a 0 (if it does not). This value also evaluates directly to true or false, which means we don’t need to use an expression in our dialplan to complicate the logic.
And that, in a nutshell, is what func_odbc is all about: writing custom dialplan functions that return a result from a database. Next up, a more detailed example of how one might use func_odbc .
Getting Funky with func_odbc: Hot-Desking
OK, back to the Dagwood sandwich we promised.
We believe the value of func_odbc will become very clear to you if you work through the following example, which will produce a new feature on your Asterisk system that depends heavily on func_ocbc .
Picture a small company with a sales force of five people who have to share two desks. This is not as cruel as it seems, because these folks spend most of their time on the road, and they are each only in the office for at most one day each week.
Still, when they do get into the office, they’d like the system to know which desk they are sitting at, so that their calls can be directed there. Also, the boss wants to be able to track when they are in the office and control calling privileges from those phones when no one is there.
This need is typically solved by what is called a hot-desking feature. We have built one for you in order to show you the power of func_odbc .
Let’s start with the easy stuff, and create two desktop phones in the sip.conf file:
; sip.conf
; HOT DESK PHONES
[hot_desk_phone](!) ; template
type=friend
host=dynamic
context=hotdesk
qualify=yes
[0000FFFF0003](hot_desk_phone) ; first desk
secret=this_phone_needs_a_good_password
[0000FFFF0004](hot_desk_phone) ; second desk
secret=this_phone_also_needs_a_secret
; END HOT DESK PHONES
These two desk phones both enter the dialplan at the hotdesk context in extensions.conf, which we will define shortly. If you want to have these devices actually work, you will of course need to set the appropriate parameters in the devices themselves, but we covered all. If you are building these phones in your lab, the device names we use can be replaced by whatever you feel best serves your needs (we recommend using the MAC address). That’s all for sip.conf. We’ve got two slices of bread, which is hardly a sandwich yet.
Now let’s get the database part of it set up (we are assuming that you have an ODBC database created and working, as outlined in the earlier parts of this chapter). First, connect to the database console.
For PostgreSQL:
$ sudo su – postgres
$ psql -U asterisk -h localhost asterisk
Password:
Then create the table with the following bit of SQL:
CREATE TABLE ast_hotdesk
(
id serial NOT NULL,
extension int8,
first_name text,
last_name text,
cid_name text,
cid_number varchar(10),
pin int4,
context text,
status bool DEFAULT false,
“location” text,
CONSTRAINT ast_hotdesk_id_pk PRIMARY KEY (id)
)
WITHOUT OIDS;
For MySQL:
$ mysql -u asterisk -p asterisk
Enter password:
Then create the table with the following bit of SQL:
CREATE TABLE ast_hotdesk
(
id serial NOT NULL,
extension int8,
first_name text,
last_name text,
cid_name text,
cid_number varchar(10),
pin int4,
context text,
status bool DEFAULT false,
location text,
CONSTRAINT ast_hotdesk_id_pk PRIMARY KEY (id)
);
Table -1. Summary of ast_hotdesk table
After that, populate the database with the following information (some of the values that you see actually will change only after the dialplan work is done, but we include it here by way of example).
At the PostgreSQL console, run the following commands:
asterisk=> INSERT INTO ast_hotdesk (‘extension’, ‘first_name’, ‘last_name’,\ ‘cid_name’,’cid_number’, ‘pin’, ‘context’, ‘location’) \
VALUES (1101, ‘Leif’, ‘Madsen’, ‘Leif Madsen’, ‘4165551101’, ‘555’,\ ‘longdistance’,’0000FFFF0003′);
At the MySQL console, run the following commands:
mysql> INSERT INTO ast_hotdesk (extension, first_name, last_name, cid_name,
cid_number, pin, context, status, location)
VALUES (1101, ‘Leif’, ‘Madsen’, ‘Leif Madsen’,
‘4165551101’, ‘555’, ‘longdistance’, 1, ‘0000FFFF0003’);
mysql> INSERT INTO ast_hotdesk (extension, first_name,
last_name, cid_name, cid_number, pin, context)
VALUES (1104,’Mark’,’Spencer’,’Mark Spencer’,
‘4165551104’,’558′,’international’);
Repeat these commands, changing the VALUES as needed, for all entries you wish to have in the database. After you’ve input your sample data, you can view the data in the ast_hotdesk table by running a simple SELECT statement from the database console:
mysql> SELECT * FROM ast_hotdesk;
which might give you something like the following output:
+—-+———–+————+————–+——————+
| id | extension | first_name | last_name
| cid_name
|
+—-+———–+————+————–+——————+
| 1 | 1101 | Leif | Madsen | Leif Madsen |
| 2 | 1104 | Mark | Spencer | Mark Spencer |
| 3 | 1105 | Matt | Jordan | Matt Jordan |
| 4 | 1102 | Jim | Van Meggelen | Jim Van Meggelen |
| 5 | 1103 | Russell | Bryant | Russell Bryant|
+—-+———–+————+————–+——————+
+—————-+———–+—————-+——–+————–+
| cid_number | pin | context | status | location |
+————+——+—————+——–+————–+
| 4165551101 | 555 | longdistance | 1 | 0000FFFF0003 |
| 4165551104 | 558 | international | 0 | NULL |
| 4165551105 | 559 | local | 0 | NULL |
| 4165551102 | 556 | longdistance | 0 | NULL |
| 4165551103 | 557 | local | 0 | NULL |
+————+——+—————+——–+——————+
We’ve got the condiments now, so let’s get to our dialplan. This is where the magic is going to happen.
Somewhere in extensions.conf we are going to have to create the hotdesk context. To start, let’s define a pattern-match extension that will allow the users to log in:
; extensions.conf
; Hot-Desking Feature
[hotdesk]
; Hot Desk Login
exten => _99110[1-5],1,NoOp()
same => n,Set(E=${EXTEN:2}) ; strip off the leading 99
same => n,Verbose(1,Hot Desk Extension ${E} is changing status)
same => n,Verbose(1,Checking current status of extension ${E})
same => n,Set(${E}_STATUS=${HOTDESK_INFO(status,${E})})
same => n,Set(${E}_PIN=${HOTDESK_INFO(pin,${E})})
We’re not done writing this extension yet, but we need to digress for a few pages to discuss where we’re at so far.
When a sales agent sits down at a desk, he logs in by dialing 99 plus his own extension number. In this case we have allowed the 1101 through 1105 extensions to log in with our pattern match of _99110[1-5] . You could just as easily make this less restrictive by using _9911XX (allowing 1100 through 1199). This extension uses func_odbc to perform a lookup with the HOTDESK_INFO() dialplan function. This custom function (which we will define in the func_odbc.conf file) performs an SQL statement and returns whatever is retrieved from the database.
We would define the new function HOTDESK_INFO() in func_odbc.conf like so:
[INFO]
prefix=HOTDESK
dsn=asterisk
readsql=SELECT ${ARG1} FROM ast_hotdesk WHERE extension = ‘${ARG2}’
That’s a lot of stuff in just a few lines. Let’s quickly cover them before we move on. First of all, the prefix is optional (default prefix is ‘ODBC’ ). This means that if you don’t define a prefix , Asterisk adds ‘ODBC’ to the function name (in this case, INFO ), which means this function would become ODBC_INFO() . This is not very descriptive of what the function is doing, so it can be helpful to assign a prefix that helps to relate your ODBC functions to the tasks they are performing. We chose ‘HOTDESK’ , which means that this custom function will be named HOTDESK_INFO() in the dialplan.
The dsn attribute tells Asterisk which connection to use from res_odbc.conf. Since several database connections could be configured in res_odbc.conf, we specify which one to use here. we show the relationship between the various file configurations and how they reference down the chain to connect to the database.
Finally, we define our SQL statement with the readsql attribute. Dialplan functions can be called with two different formats: one for retrieving information, and one for setting information. The readsql attribute is used when we call the HOTDESK_INFO() function
with the retrieve format (we could execute a separate SQL statement with the writesql attribute; we’ll discuss the format for that attribute a little bit later in this chapter).
Reading values from this function would take this format in the dialplan:
exten => s,n,Set(RETURNED_VALUE=${HOTDESK_INFO(status,1101)})
This would return the value located in the database within the status column where the extension column equals 1101 . The status and 1101 we pass to the HOTDESK_INFO() function are then placed into the SQL statement we assigned to the readsql attribute, available as ${ARG1} and ${ARG2} , respectively. If we had passed a third option, this would have been available as ${ARG3} .
After the SQL statement is executed, the value returned (if any) is assigned to the RETURNED_VALUE channel variable.
So, in the first two lines of the following block of code, we are passing the value status and the value contained in the ${E} variable (e.g., 1101 ) to the HOTDESK_INFO() function. The two values are then replaced in the SQL statement with ${ARG1} and ${ARG2} , respectively, and the SQL statement is executed. Finally, the value returned is assigned to the ${E}_STATUS channel variable.
OK, let’s finish writing the pattern-match extension now:
same => n,Set(${E}_STATUS=${HOTDESK_INFO(status,${E})})
same => n,Set(${E}_PIN=${HOTDESK_INFO(pin,${E})})
same => n,GotoIf($[${ODBCROWS} < 0]?invalid_user,1)
same => n,GotoIf($[${${E}_STATUS} = 1]?logout,1:login,1)
After assigning the value of the status column to the ${E}_STATUS variable (if the user identifies themself as extension 1101 , the variable name will be 1101_STATUS ), we check if we’ve received a value back from the database (error checking) using the ${ODBCROWS} channel variable.
The last row in the block checks the status of the phone and, if the agent is currently logged in, logs him off. If the agent is not already logged in, it will go to extension login , priority 1 within the same context.
The login extension runs some initial checks to verify the PIN code entered by the agent. (Additionally, we’ve used the FILTER() function to make sure only numbers were entered to help avoid some SQL injection issues.) We allow him three tries to enter the correct PIN, and if all tries are invalid we send the call to the login_fail extension (which we will be writing later):
exten => login,1,NoOp() ; set initial counter values
same => n,Set(PIN_TRIES=1) ; pin tries counter
same => n,Set(MAX_PIN_TRIES=3) ; set max number of login attempts
same => n,Playback(silence/1) ; play back some silence so first prompt is
; not cut off
same => n(get_pin),NoOp()
same => n,Set(PIN_TRIES=$[${PIN_TRIES} + 1]) ; increase pin try counter
same => n,Read(PIN_ENTERED,enter-password,${LEN(${${E}_PIN})})
same => n,Read(PIN_ENTERED=${FILTER(0-9,${PIN_ENTERED})})
same => n,GotoIf($[“${PIN_ENTERED}” = “${${E}_PIN}”]?valid_login,1)
same => n,Playback(pin-invalid)
same => n,GotoIf($[${PIN_TRIES} <= ${MAX_PIN_TRIES}]?get_pin:login_fail,1)
If the PIN entered matches, we validate the login with the valid_login extension. First we utilize the CHANNEL variable to figure out which phone device the agent is calling from. The CHANNEL variable is usually populated with something like SIP/ 0000FFFF0001-ab4034c , so we make use of the CUT() function to first pull off the SIP/ portion of the string and assign that to LOCATION . We then strip off the -ab4034c part of the string, discard it, and assign the remainder ( 0000FFFF0001 ) to the LOCATION variable:
exten => valid_login,1,NoOp()
; CUT off the channel technology and assign it to the LOCATION variable
same => n,Set(LOCATION=${CUT(CHANNEL,/,2)})
; CUT off the unique identifier and save the remainder to the LOCATION variable
same => n,Set(LOCATION=${CUT(LOCATION,-,1)})
We utilize yet another custom function created in the func_odbc.conf file, HOT DESK_CHECK_PHONE_LOGINS() , to check if any other users were previously logged into this phone and forgot to log out. If the number of logged-in users is greater than 0 (it should never be more than 1 , but we check for higher values anyway and reset those, too), it runs the logic in the logout_login extension:
; func_odbc.conf
[CHECK_PHONE_LOGINS]
prefix=HOTDESK
dsn=asterisk
readsql=SELECT COUNT(status) FROM ast_hotdesk WHERE status = ‘1’
readsql+= AND location = ‘${ARG1}’
If there are no other agents logged into the device, we update the login status for this
user with the HOTDESK_STATUS() function:
; Continuation of the valid_login extension below
same => n,Set(USERS_LOGGED_IN=${HOTDESK_CHECK_PHONE_
LOGINS(${LOCATION})})
same => n,GotoIf($[${USERS_LOGGED_IN} > 0]?logout_login,1)
same => n(set_login_status),NoOp()
; Set the status for the phone to ‘1’ and where the agent is logged into
same => n,Set(HOTDESK_STATUS(${E})=1,${LOCATION})
same => n,GotoIf($[${ODBCROWS} < 1]?error,1)
same => n,Playback(agent-loginok)
same => n,Hangup()
We create a write function in func_odbc.conf like so:
[STATUS]
prefix=HOTDESK
dsn=asterisk
writesql=UPDATE ast_hotdesk SET status = ‘${SQL_ESC(${VAL1})}’,
writesql+= location = ‘${SQL_ESC(${VAL2})}’
writesql+= WHERE extension = ‘${SQL_ESC(${ARG1})}’
The syntax is very similar to the readsql syntax discussed earlier in the chapter, but there are a few new things here, so let’s discuss them before moving on.
The first thing you may have noticed is that we now have both ${VALx} and ${ARGx} variables in our SQL statement.
These contain the values we pass to the function from the dialplan. In this case, we have two VAL variables and a single ARG variable that were set from the dialplan via this statement:
Set(HOTDESK_STATUS(${E})=1,${LOCATION})
Notice the syntax is slightly different from that of the read-style function. This signals to Asterisk that you want to perform a write (this is the same syntax as that used for other dialplan functions).
We are passing the value of the ${E} variable to the HOTDESK_STATUS() function, whose value is then accessible in the SQL statement within func_odbc.conf with the ${ARG1} variable. We then pass two values: 1 and ${LOCATION} . These are available to the SQL statement in the ${VAL1} and ${VAL2} variables, respectively.
As mentioned previously, if we had to log out one or more agents before logging this one in, we would check this with the logout_login extension. This dialplan logic will utilize the ODBC_FETCH() function to pop information off the information stack returned by the HOTDESK_LOGGED_IN_USER() function. More than likely this will execute only one loop, but it’s a good example of how you might update or parse multiple rows in the database.
The first part of our dialplan returns an ID number that we can use with the ODBC_FETCH() function to iterate through the values returned. We’re going to write a section of dialplan to assign this ID to the LOGGED_IN_ID channel variable:
same => n,Set(LOGGED_IN_ID=${HOTDESK_LOGGED_IN_USER(${LOCATION})})
Here is the logout_login extension, which could potentially loop through multiple rows:
exten => logout_login,1,NoOp()
; set all logged-in users on this device to logged-out status
same => n,Set(LOGGED_IN_ID=${HOTDESK_LOGGED_IN_USER(${LOCATION})})
same => n(start_loop),NoOp()
same => n,Set(WHO=${ODBC_FETCH(${LOGGED_IN_ID})})
same => n,GotoIf($[“${ODBC_FETCH_STATUS}” = “FAILURE”]?cleanup)
same => n,Set(HOTDESK_STATUS(${WHO})=0 ; log out phone
same => n,Goto(start_loop)
same => n(cleanup),ODBCFinish(${LOGGED_IN_ID})
same => n,Goto(valid_login,set_login_status)
; return to logging in
And here’s the function we’d add to func_odbc.conf (remember to reload the func_odbc.so module):
[LOGGED_IN_USER]
prefix=HOTDESK
dsn=asterisk
mode=multirow
readsql=SELECT extension FROM ast_hotdesk
readsql+= WHERE status = ‘1’
readsql+= AND location = ‘${SQL_ESC(${ARG1})}’
We assign the first value returned from the database (e.g., the extension 1101 ) to the WHO channel. Before doing anything, though, we check to see if the ODBC_FETCH() function was successful in returning data. If the ODBC_FETCH_STATUS channel variable contains FAILURE , we have no data to work with, so we move to the cleanup priority label.
If we have data, we then pass the value of ${WHO} as an argument to the HOTDESK_STATUS() function, which contains a value of 0 . This is the first value passed to HOTDESK_STATUS() and is shown as ${VAL1} in func_odbc.conf, where the function is declared.
After using HOTDESK_STATUS() to log out the user, we return to the start_loop priority label to loop through all values, which simply executes a NoOp() . After attempting to retrieve a value, we again check ODBC_FETCH_STATUS for FAILURE . If that value is found, we move to the cleanup priority label, where we execute the ODBCFinish() dialplan application to perform cleanup. We then return to the valid_login extension at the set_login_status priority label.
The rest of the context should be fairly straightforward . Theone trick you may be unfamiliar with could be the usage of the ${ODBCROWS} channel variable, which is set by the HOTDESK_STATUS() function. This tells us how many rows were affected in the SQL UPDATE , which we assume to be 1 . If the value of ${ODBCROWS} is less than 1 , we assume an error and handle it appropriately:
exten => logout,1,NoOp()
same => n,Set(HOTDESK_STATUS(${E})=0)
same => n,GotoIf($[${ODBCROWS} < 1]?error,1)
same => n,Playback(silence/1&agent-loggedoff)
same => n,Hangup()
exten => login_fail,1,NoOp()
same => n,Playback(silence/1&login-fail)
same => n,Hangup()
exten => error,1,NoOp()
same => n,Playback(silence/1&connection-failed)
same => n,Hangup()
exten => invalid_user,1,NoOp()
same => n,Verbose(1,Hot Desk extension ${E} does not exist)
same => n,Playback(silence/2&invalid)
same => n,Hangup()
We also include the hotdesk_outbound context, which will handle our outgoing calls after we have logged the agent into the system:
include => hotdesk_outbound
The hotdesk_outbound context utilizes many of the same principles already discussed. This context uses a pattern match to catch any numbers dialed from the hot-desk phones. We first set our LOCATION variable using the CHANNEL variable, then determine which extension (agent) is logged into the system and assign that value to the WHO variable. If this variable is NULL , we reject the outgoing call. If it is not NULL , then we get the agent information using the HOTDESK_INFO() function and assign it to several CHANNEL variables, including the context to handle the call with, where we perform a Goto() to the context we have been assigned (which controls our outbound access).
We will make use of the HOTDESK_PHONE_STATUS() dialplan function, which you can define in func_odbc.conf like so:
[PHONE_STATUS]
prefix=HOTDESK
dsn=asterisk
readsql=SELECT extension FROM ast_hotdesk WHERE status = ‘1’
readsql+= AND location = ‘${SQL_ESC(${ARG1})}’
If we try to dial a number that is not handled by our context (or one of the transitive contexts—i.e., international contains long distance, which also contains local), the builtin extension i is executed, which plays back a message stating that the action cannot be
performed and hangs up the call:
[hotdesk_outbound]
exten => _X.,1,NoOp()
same => n,Set(LOCATION=${CUT(CHANNEL,/,2)})
same => n,Set(LOCATION=${CUT(LOCATION,-,1)})
same => n,Set(WHO=${HOTDESK_PHONE_STATUS(${LOCATION})})
same => n,GotoIf($[${ISNULL(${WHO})}]?no_outgoing,1)
same => n,Set(${WHO}_CID_NAME=${HOTDESK_INFO(cid_name,${WHO})})
same => n,Set(${WHO}_CID_NUMBER=${HOTDESK_INFO(cid_number,${WHO})})
same => n,Set(${WHO}_CONTEXT=${HOTDESK_INFO(context,${WHO})})
same => n,Goto(${${WHO}_CONTEXT},${EXTEN},1)
[international] ; as dialed from NANP
exten => _011.,1,NoOp()
same => n,Set(E=${EXTEN})
same => n,Goto(outgoing,call,1)
exten => i,1,NoOp()
same => n,Playback(silence/2&sorry-cant-let-you-do-that2)
same => n,Hangup()
include => longdistance
[longdistance] ; within NANP
exten => _1NXXNXXXXXX,1,NoOp()
same => n,Set(E=${EXTEN})
same => n,Goto(outgoing,call,1)
exten => _NXXNXXXXXX,1,Goto(1${EXTEN},1)
exten => i,1,NoOp()
same => n,Playback(silence/2&sorry-cant-let-you-do-that2)
same => n,Hangup()
include => local
[local] ; within NANP NPA 416
exten => _416NXXXXXX,1,NoOp()
same => n,Set(E=${EXTEN})
same => n,Goto(outgoing,call,1)
exten => i,1,NoOp()
same => n,Playback(silence/2&sorry-cant-let-you-do-that2)
same => n,Hangup()
If the call is allowed to be executed, it is sent to the [outgoing] context for processing, and the caller ID name and number are set with the CALLERID() function. The call is then placed via the SIP channel using the service_provider we created in the sip.conf
file:
[outgoing]
exten => call,1,NoOp()
same => n,Set(CALLERID(name)=${${WHO}_CID_NAME})
same => n,Set(CALLERID(number)=${${WHO}_CID_NUMBER})
same => n,Dial(SIP/service_provider/${E})
same => n,Playback(silence/2&pls-try-call-later)
same => n,Hangup()
Our service_provider might look something like this in sip.conf:
[service_provider]
type=friend
host=switch1.service_provider.net
defaultuser=my_username
fromuser=my_username
secret=welcome
context=incoming
canreinvite=no
disallow=all
allow=ulaw
Now that we’ve implemented a fairly complex feature in the dialplan with the help of func_odbc to retrieve and store data in a remote relational database, you can start to see that with a handful of self-defined dialplan functions in the func_odbc.conf file and a couple of tables in a database, you can create some powerful applications! How many things have you just thought of that you could apply func_odbc to?
Using Realtime
The Asterisk Realtime Architecture (ARA) allows you to store all the parameters normally stored in your Asterisk configuration files (commonly located in /etc/asterisk) in a database. There are two types of realtime: static and dynamic.
The static version is similar to the traditional method of reading a configuration file (information is only loaded when triggered from the CLI), except that the data is read from the database instead.
The dynamic realtime method, which loads and updates the information as it is used by the live system, is commonly used for things such as SIP (or IAX2, etc.) user and peer objects, as well as voicemail boxes.
Making changes to static information requires a reload, just as if you had changed a text file on the system, but dynamic information is polled by Asterisk as needed, so no reload is required when changes are made to this data. Realtime is configured in the extcon fig.conf file located in the /etc/asterisk directory. This file tells Asterisk what to load from the database and where to load it from, allowing certain files to be loaded from the database and other files to be loaded from the standard configuration files.
Static Realtime
Static realtime is useful when you want to load from a database the configuration that you would normally place in the configuration files in /etc/asterisk. The same rules that apply to flat files on your system still apply when using Static Realtime. For example, after making changes to the configuration you must either run the global reload command from the Asterisk CLI, or reload the specific module associated with the configuration file (e.g., module reload chan_sip.so).
When using Static Realtime, we tell Asterisk which files we want to load from the database using the following syntax in the extconfig.conf file:
; /etc/asterisk/extconfig.conf
[settings]
filename.conf => driver,database[,table]
The Static Realtime module uses a very specifically formatted table to allow Asterisk to read the various static files from the database.
A simple file we can load from Static Realtime is the musiconhold.conf 15 file. Let’s start by moving this file to a temporary location:
$ cd /etc/asterisk
$ mv musiconhold.conf musiconhold.conf.old
In order for the classes to be removed from memory, we need to restart Asterisk. Then we can verify that our classes are blank by running moh show classes:
*CLI> core restart now
*CLI> moh show classes
*CLI>
OK, now log into your database and create the following table:
mysql> create table ast_config (
-> id int(8) primary key auto_increment,
-> cat_metric int(8),
-> var_metric int(8),
-> filename varchar(128),
-> category varchar(128),
-> var_name varchar(128),
-> var_val varchar(128),
-> commented int default 0
-> );
Let’s put the [default] class back into Asterisk, but now we’ll load it from the database. Connect to your database and execute the following INSERT statements:
INSERT INTO ast_config
(cat_metric,var_metric,filename,category,var_name,var_val)
VALUES
(1,1,’musiconhold.conf’,’default’,’mode’,’files’);
> INSERT INTO ast_config
(cat_metric,var_metric,filename,category,var_name,var_val)
VALUES
(1,2’musiconhold.conf’,’default’,’directory’,’/var/lib/asterisk/moh’);
You can verify that your values have made it into the database by running a SELECT
statement:
asterisk=# SELECT filename,category,var_name,var_val FROM ast_config;
filename | category | var_name | var_val
———————–+—————-+————–+————————
musiconhold.conf | default | mode | files
musiconhold.conf | default | directory | /var/lib/asterisk/moh
(2 rows)
There’s one last thing to modify in the extconfig.conf file in the /etc/asterisk directory to tell Asterisk to get the data for musiconhold.conf from the database using the ODBC connection. The first column states that we’re using the ODBC drivers to connect (res_odbc.conf) and that the connection name is asterisk (as defined with [asterisk] in res_odbc.conf). Add the following line to the end of the extconfig.conf file, and then save it:
[settings]
musiconhold.conf => odbc,asterisk,ast_config
Then connect to the Asterisk console and perform a reload:
*CLI> module reload res_musiconhold.so
You can now verify that your music on hold classes are loading from the database by running moh show classes:
*CLI> moh show classes
Class: general
Mode: files
Directory: /var/lib/asterisk/moh
And there you go: musiconhold.conf loaded from the database. If you have issues with the reload of the module loading the data into memory, try restarting Asterisk. You can perform the same steps in order to load other flat files from the database, as needed.
Dynamic Realtime
The Dynamic Realtime system is used to load objects that may change often, such as SIP/IAX2 users and peers, queues and their members, and voicemail messages. Likewise, when new records are likely to be added on a regular basis, we can utilize the power of the database to let us load this information on an as-needed basis.
All of realtime is configured in the /etc/asterisk/extconfig.conf file; however, Dynamic Realtime has explicitly defined configuration names. All the predefined names should be configured under the [settings] header. For example, defining SIP peers is done using the following format:
; extconfig.conf
[settings]
sippeers => driver,database[,table]
The table name is optional. If it is omitted, Asterisk will use the predefined name (i.e., sippeers ) to identify the table in which to look up the data.
In our example, we’ll be using the ast_sippeers table to store our SIP peer information. So, to configure Asterisk to load all SIP peers from our database using realtime, we would define something like this:
; extconfig.conf
[settings]
sippeers => odbc,asterisk,ast_sippeers
Both SIP users and peers are loaded from the same table; defined via the sippeers section of extconfig.conf. This is because there will be a type field (just as if we were defining the type in the sip.conf file) that will let us define a type of user , peer , or friend . If you unload chan_sip.so and then load it back into memory (i.e., using module unload chan_sip.so followed by module load chan_sip.so) after configuring extcon fig.conf, you will be greeted with some warnings telling you which columns you’re missing for the realtime table. If you were to load from realtime now, you would get the following similar output on the console (which has been trimmed due to space requirements):
WARNING: Realtime table ast_sippeers@asterisk requires column
‘name’, but that column does not exist!
WARNING: Realtime table ast_sippeers@asterisk requires column
‘ipaddr’, but that column does not exist!
WARNING: Realtime table ast_sippeers@asterisk requires column
‘port’, but that column does not exist!
WARNING: Realtime table ast_sippeers@asterisk requires column
‘regseconds’, but that column does not exist!
WARNING: Realtime table ast_sippeers@asterisk requires column
‘defaultuser’, but that column does not exist!
WARNING: Realtime table ast_sippeers@asterisk requires column
‘fullcontact’, but that column does not exist!
WARNING: Realtime table ast_sippeers@asterisk requires column
‘regserver’, but that column does not exist!
WARNING: Realtime table ast_sippeers@asterisk requires column
‘useragent’, but that column does not exist!
WARNING: Realtime table ast_sippeers@asterisk requires column
‘lastms’, but that column does not exist!
As you can see, we are missing several columns from the table ast_sipfriends , which we’ve defined as connecting to the asterisk object as defined in res_odbc.conf. The next step is to create our ast_sipfriends table with all the columns listed by the warning messages, in addition to the following: the type column, which is required to define users, peers, and friends; the secret column, which is used for setting a password; and the host column, which allows us to define whether the peer is dynamically registering to us or has a static IP address. Table -2 lists all the columns that should appear in our table, and their types.
Table -2. Minimal sippeers/sipusers realtime table
The SQL code to create this table should look something like this:
create table ast_sippeers
(
type varchar(6)
name varchar(128),
secret varchar(128),
context varchar(128),
host varchar(128),
ipaddr varchar(128),
port varchar(5),
regseconds bigint,
defaultuser varchar(128),
fullcontact varchar(128),
regserver varchar(128),
useragent varchar(128),
lastms integer
);
For each peer you want to register, you need to insert data in the columns type , name , secret , context , host , and defaultuser . The rest of the columns will be populated automatically when the peer registers.
The port , regseconds , and ipaddr fields are required to let Asterisk store the registration information for the peer so it can determine where to send the calls. (Note that if the peer is static , you will have to populate the ipaddr field yourself.) The port field is optional and defaults to the standard port defined in the [general] section, and the regseconds will remain blank. Table -3 lists some sample values that we’ll use to populate our ast_sipfriends table.
Table -3. Example information used to populate the ast_sipfriends table
Prior to registering your peer, though, you need to enable realtime caching in sip.conf. Otherwise, the peer will not be loaded into memory, and the registration will not be remembered. If your peers only place calls and don’t need to register to your system, you don’t need to enable realtime caching because the peers will be checked against the database each time they place a call. However, if you load your peers into memory, the database will only need to be contacted on initial registration and after the registration expires.
Additional options in sip.conf exist for realtime peers. These are defined in the [general] section and described in Table -4.
Table -4. Realtime options in sip.conf
After enabling rtcachefriends=yes in sip.conf and reloading chan_sip.so (using module reload chan_sip.so), you can register your peer to Asterisk using realtime, and the peer should then be populated into memory. You will be able to verify this by executing the sip show peers command on the Asterisk console:
Name/username Host Dyn Port Status Realtime
0000FFFF0008/0000FFFF0008 172.16.0.160 D 5060 Unmonitored Cached RT
If you were to look at the table in the database directly, you would see something
like this:
+——–+————–+———+———–+———+————————–+
| type | name | secret | context | host | ipaddr |
+——–+————–+———+———–+———+—————————+
| friend | 0000FFFF0008 | welcome | LocalSets | dynamic | 172.16.0.160 |
+——–+————–+———+———–+———+—————————+
+——+————+————–+—————————————————————–+
| port | regseconds | defaultuser | fullcontact |
+——+————+————–+—————————————————————–+
| 5060 | 1283928895 | 0000FFFF0008 | sip:0000FFFF0008@172.16.0.160:52722 |
+——+————+————–+—————————————————————–+
+———–+—————–+——–+
| regserver | useragent | lastms |
+———–+—————–+——–+
| NULL| Zoiper rev.6739 | 0 |
+———–+—————–+——–+
There are many more options that we can define for SIP friends, such as the caller ID; adding that information is as simple as adding a callerid column to the table. See the sip.conf.sample file for more options that can be defined for SIP friends.
