The func_odbc
dialplan function is arguably the coolest and most powerful dialplan function
in Asterisk. It allows you to create and use fairly simple dialplan
functions that retrieve and use information from databases directly in the
dialplan. There are all kinds of ways in which this might be used, such as
managing users or allowing sharing of dynamic information within a
clustered set of Asterisk machines.
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 relationship between the function
names you create and the SQL statements you wish them to perform. By
referring to the named function in the dialplan, you can retrieve and
update values in the database.
While using an external script to interact with a database (from which a flat file is created that Asterisk would read) has advantages (if the database went down, your system would continue to function and the script would simply not update any files until connectivity to the database was restored), a major disadvantage is that any changes you make to a user are not available until you run the update script. This is probably not a big issue on small systems, but on large systems, waiting for changes to take effect can cause issues, such as pausing a live call while a large file is loaded and parsed.
You can relieve some of this by utilizing a replicated database
system. In the version of Asterisk following 1.4 (currently trunk),
the syntax of the func_odbc.conf
file changes slightly, but gives the ability to failover to another
database system. This way you can cluster the database backend
utilizing a master-master relationship (pgcluster; Slony-II), or a
master-slave (Slony-I) replication system.
In order to get you into the right frame of mind for what follows, we want you to picture a Dagwood sandwich.[137]
Can you relay the total experience of such a thing by showing
someone a picture of a tomato, or by waving a slice of cheese about? Not
hardly. That is the conundrum we faced when trying to give a useful
example 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.
For our example, we decided to implement something that we think could have some practical uses. Let’s 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, so we have built
one for you in order to show you the power of
func_odbc
.
Lets start with the easy stuff, and create two desktop phones in the
sip.conf
file.
; sip.conf ; HOT DESK USERS [desk_1] type=friend host=dynamic secret=my_special_secret context=hotdesk qualify=yes [desk_2] type=friend host=dynamic secret=my_special_secret context=hotdesk qualify=yes ; END HOT DESK USERS
These two desk phones both enter the
dialplan at the [hotdesk]
context
in extensions.conf
. 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’ve
covered all that in Chapter 4, Initial Configuration of Asterisk.
That’s all for sip.conf
.
We’ve got two slices of bread. 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 like so:
#su - postgres
$psql -U asterisk -h localhost asterisk
Password:
Then create the table with the following bit of code:
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;
After that, we populated the database with the following information (some of the values that you see actually would change only after the dialplan work is done, but we have it in 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',
'desk_1');
Repeat the previous line and change the
VALUES for all entries you wish to have in the database. You can view the
data in the ast_hotdesk
table by running a simple
SELECT
statement from the PostgreSQL console:
asterisk=> SELECT * FROM ast_hostdesk;
which would give you something like the following output:
| id | extension | first_name | last_name | cid_name | cid_number | pin
|---+-----------+------------+----------------+-----------------+-------------+----
| 1 | 1101 | "Leif" | "Madsen" | "Leif Madsen" | "4165551101" | "555"
| 2 | 1102 | "Jim" | "Van Meggelen" | "Jim Van Meggelen" | "4165551102" | "556"
| 3 | 1103 | "Jared" | "Smith" | "Jared Smith" | "4165551103" | "557"
| 4 | 1104 | "Mark" | "Spencer" | "Mark Spencer" | "4165551104" | "558"
| 5 | 1105 | "Kevin" | "Fleming" | "Kevin Fleming" | "4165551105" | "559"
| context | status | location |$
+-----------------+---------+----------+
| "longdistance" | "TRUE" | "desk_1" |
| "longdistance" | "FALSE" | "" |
| "local" | "FALSE" | "" |
| "international" | "FALSE" | "" |
| "local" | "FALSE" | "" |
We’ve got the condiments now, so let’s get to our dialplan. This is where the magic is going to happen.
Before you start typing, take note that we have placed all of the sample text that follows in appendix H, so while we encourage you to follow us along through the examples, you can also see what it all looks like as a whole, by checking the appendix (and by copying and pasting, if you have an electronic version of this book).
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 => _110[1-5],1,NoOp() exten => _110[1-5],n,Set(E=${EXTEN}) exten => _110[1-5],n,Verbose(1|Hot Desk Extension ${E} is changing status) exten => _110[1-5],n,Verbose(1|Checking current status of extension ${E}) exten => _110[1-5],n,Set(${E}_STATUS=${HOTDESK_INFO(status,${E})}) exten => _110[1-5],n,Set(${E}_PIN=${HOTDESK_INFO(pin,${E})})
We’re not done writing this extension yet, but let’s pause for a moment and see where we’re at so far.
When a sales agent sits down at a desk, they log in by dialing their
own extension number. In this case we have allowed the 1101 through 1105
extensions to log in with our pattern match of _110[1-5]
. You could just as easily make this
less restrictive by using _11XX
(allowing 1100 through 1199). This extension uses
func_odbc
to perform a lookup with the HOTDESK_INFO()
dialplan function (which we will
be creating shortly). This custom function (which we 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 read=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. If you don’t configure the prefix
, then Asterisk adds “ODBC” to the name of
the function (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 task they are
performing. In this case we chose HOTDESK, which means that this custom
function will be named HOTDESK_INFO
.
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. In
Figure 12.1, “Relationships between func_odbc.conf, res_odbc.conf,
/etc/odbc.ini (unixODBC), and the database connection”, we show the relationship between
the various file configurations and how they reference down the chain to
connect to the database.
We then define our SQL statement with the read
attribute. Dialplan functions have two
different formats that they can be called with: one for retrieving
information, and one for setting information. The read
attribute is used when we call the HOTDESK_INFO()
function with the retrieve format
(and we could execute a separate SQL statement with the write
attribute; we’ll discuss the format for
the write
attribute a little bit later
in this chapter).
Reading values from this function would take the format in the dialplan like so:
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 read
attribute, available as ${ARG1}
and
${ARG2}
, respectively. If we had passed
a third option, this would have been available as ${ARG3}
.
Be sure that your data is unique enough that you only get a single row back. If more than one row is returned, Asterisk will see only the first row returned. With PostgreSQL, you could add a LIMIT 1 to the end of your SQL statement to limit a single row being returned, but this is not a good practice to rely on. A little further into this section we’ll see how we can use the LIMIT and OFFSET PostgreSQL functions to loop through multiple rows of data!
After the SQL statement is executed, the value returned (if any) is
assigned to the RETURNED_VALUE
channel variable.
Figure 12.1. Relationships between func_odbc.conf, res_odbc.conf, /etc/odbc.ini (unixODBC), and the database connection
So, in the first two lines of our 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}
, respectfully, the SQL statement is
executed, and the value returned is assigned to the ${E}_STATUS
channel variable.
OK, let’s finish writing the pattern-match extension now:
exten => _110[1-5],n,Set(${E}_STATUS=${HOTDESK_INFO(status,${E})}) exten => _110[1-5],n,Set(${E}_PIN=${HOTDESK_INFO(pin,${E})}) exten => _110[1-5],n,GotoIf($[${ISNULL(${${E}_STATUS})}]?invalid_user,1) ; check if ${E}_STATUS is NULL exten => _110[1-5],n,GotoIf($[${${E}_STATUS} = 1]?logout,1:login,1)
After
assigning the value of the status
column to the
${E}_STATUS
variable (if you dialed extension 1101,
then the variable name would be 1101_STATUS
), we check
if we received a value back from the database (error checking). We make
use of the ISNULL()
function to perform
this check.
The last row in the block checks the status of the phone, and if
currently logged in, will log off the agent. If not already logged in, it
will go to extension login
, priority
1
within the same context.[138]
In the version following 1.4 (currently trunk) you can use the
${ODBCROWS}
channel variable
with statements executed by a readsql
. We could have replaced the GotoIf()
with something like:
exten => _110[1-5],n,GotoIf($[${ODBCROWS} < 0]?invalid_user,1)
The login
extension runs some initial checks to verify the pin code entered by
the agent. We allow him three tries to enter the correct pin, and if
invalid, will send the call to the login_fail
extension (which we will be writing
later on).
exten => login,1,NoOp() ; set counter initial value exten => login,n,Set(PIN_TRIES=0) ; set max number of login attempts exten => login,n,Set(MAX_PIN_TRIES=3) exten => login,n(get_pin),NoOp() ; increase pin try counter exten => login,n,Set(PIN_TRIES=$[${PIN_TRIES} + 1]) exten => login,n,Read(PIN_ENTERED|enter-password|${LEN(${${E}_PIN})}) exten => login,n,GotoIf($[${PIN_ENTERED} = ${${E}_PIN}]?valid_login,1) exten => login,n,Playback(invalid-pin) exten => login,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 we’re calling from. The
CHANNEL
variable is usually populated with something
such as: SIP/desk_1-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 of desk_1
to the
LOCATION
variable.
exten => valid_login,1,NoOp() ; CUT off the channel technology and assign to the LOCATION variable exten => valid_login,n,Set(LOCATION=${CUT(CHANNEL,/,2)}) ; CUT off the unique identifier and save the remainder to the LOCATION variable exten => valid_login,n,Set(LOCATION=${CUT(LOCATION,-,1)})
We utilize yet another custom function, HOTDESK_CHECK_PHONE_LOGINS
()
, created in func_odbc.conf
to check if any other users were
previously logged in to this phone and had forgotten to log out. If the
number of previously logged in users was greater than 0 (and should only
ever be 1, but we check for more anyway and reset those, too), it runs the
logic in the logout_login
extension.
If no previous agents were logged in, we update the login status for
this user with the HOTDESK_STATUS()
function:
exten => valid_login,n,Set(ARRAY(USERS_LOGGED_IN)=${HOTDESK_CHECK_PHONE_ LOGINS(${LOCATION})}) exten => valid_login,n,GotoIf($[${USERS_LOGGED_IN} > 0]?logout_login,1) exten => valid_login,n(set_login_status),NoOp() ; Set the status for the phone to '1' and where we're logged into ; NOTE: we need to escape the comma here because the Set() application has arguments exten => valid_login,n,Set(HOTDESK_STATUS(${E})=1\,${LOCATION}) exten => valid_login,n,GotoIf($[${ODBCROWS} < 1]?error,1) exten => valid_login,n,Playback(agent-loginok) exten => valid_login,n,Hangup()
We create a write function in func_odbc.conf
like so:
[STATUS] prefix=HOTDESK dsn=asterisk write=UPDATE ast_hotdesk SET status = '${VAL1}', location = '${VAL2}' WHERE extension = '${ARG1}'
The syntax is very similar to the read
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
${VAL
x
}
and
${ARG
x
}
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})
Because the Set()
dialplan
application can also take arguments (you can set
multiple variables and values by separating them with commas
or pipes), you need to escape the comma with the backslash ( \ ) so it is not processed by the
expression parser for the Set()
application, but rather parses it for the HOTDESK_STATUS()
function.
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 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 in, we would check this with the logout_login
extension. This dialplan logic will
utilize the While()
application to loop
through the database and perform any database correction that may need to
occur. 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:
exten => logout_login,1,NoOp() ; set all logged in users on this device to logged out status exten => logout_login,n,Set(ROW_COUNTER=0) exten => logout_login,n,While($[${ROW_COUNTER} < ${USERS_LOGGED_IN}])
The
${USERS_LOGGED_IN}
variable was set
previously with the HOTDESK_CHECK_PHONE_LOGINS()
function, which
assigned a value of 1 or greater. We did this by counting the number of
rows that were affected:
; func_odbc.conf [CHECK_PHONE_LOGINS] prefix=HOTDESK dsn=asterisk read=SELECT COUNT(status) FROM ast_hotdesk WHERE status = '1' AND location = '${ARG1}'
We
then get the extension number of the user that is logged in with the
HOTDESK_LOGGED_IN_USER()
function. The
LOCATION
variable is populated with desk_1
, which tells us which device we want to
check on, and the ${ROW_COUNTER}
contains which iteration of the loop we’re on. These are both passed as
arguments to the dialplan function. The result is then assigned to the
WHO
variable:
exten => logout_login,n,Set(WHO=${HOTDESK_LOGGED_IN_USER(${LOCATION},${ROW_COUNTER})})
The
HOTDESK_LOGGED_IN_USER()
function then
pulls a specific row out of the database that corresponds with the
iteration of the loops we are trying to process:
[LOGGED_IN_USER] prefix=HOTDESK dsn=asterisk read=SELECT extension FROM ast_hotdesk WHERE status = '1' AND location = '${ARG1}' ORDER BY id LIMIT '1' OFFSET '${ARG2}'
Now
that we know what extension we want to update, we write to the HOTDESK_STATUS()
function, and assign a 0 to the
status
column where the extension number matches the
value in the ${WHO}
variable (i.e., 1101). We then end
the loop with EndWhile()
and return back to the
valid_login
extension at the
set_login_status
priority label (as discussed
previously):
exten => logout_login,n,Set(HOTDESK_STATUS(${WHO})=0) ; logout phone exten => logout_login,n,Set(ROW_COUNTER=$[${ROW_COUNTER} + 1]) exten => logout_login,n,EndWhile() exten => logout_login,n,Goto(valid_login,set_login_status) ; return to logging in
The rest of the context should be fairly straightforward (if some of
this doesn’t make sense, we suggest you go back and refresh your memory
with Chapter 5, Dialplan Basics and Chapter 6, More Dialplan Concepts). The one 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, then we assume an
error and handle appropriately:
exten => logout,1,NoOp() exten => logout,n,Set(HOTDESK_STATUS(${E})=0) exten => logout,n,GotoIf($[${ODBCROWS} < 1]?error,1) exten => logout,n,Playback(silence/1&agent-loggedoff) exten => logout,n,Hangup() exten => login_fail,1,NoOp() exten => login_fail,n,Playback(silence/1&login-fail) exten => login_fail,n,Hangup() exten => error,1,NoOp() exten => error,n,Playback(silence/1&connection-failed) exten => error,n,Hangup() exten => invalid_user,1,NoOp() exten => invalid_user,n,Verbose(1|Hot Desk extension ${E} does not exist) exten => invalid_user,n,Playback(silence/2&invalid) exten => invalid_user,n,Hangup()
We also include the
hotdesk_outbound
context which will
handle our outgoing calls after we have logged the agent in to the
system:
include => hotdesk_outbound
The
hotdesk_outbound
context utilizes many of the same
principles and usage as previously discussed, so we won’t approach it
quite so thoroughly, but essentially the
context will catch
all dialed numbers from the desk phones. We first set our
[hotdesk_outbound]
LOCATION
variable using the CHANNEL
variable, then determine which extension (agent) is logged in to the
system and assign it to the WHO
variable. If this
variable is NULL
, then we reject the outgoing call. If
not NULL
, then we get the agent information using the
HOTDESK_INFO()
function and assign it
to several CHANNEL
variables. This includes the context to handle the call
with, where we perform a Goto()
to the
context we have been assigned (which controls our outbound access).
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), then the built-in extension
i
is executed which plays back a message stating the
action cannot be performed, then hangs up the caller:
[hotdesk_outbound] exten => _X.,1,NoOp() exten => _X.,n,Set(LOCATION=${CUT(CHANNEL,/,2)}) exten => _X.,n,Set(LOCATION=${CUT(LOCATION,-,1)}) exten => _X.,n,Set(WHO=${HOTDESK_PHONE_STATUS(${LOCATION})}) exten => _X.,n,GotoIf($[${ISNULL(${WHO})}]?no_outgoing,1) exten => _X.,n,Set(${WHO}_CID_NAME=${HOTDESK_INFO(cid_name,${WHO})}) exten => _X.,n,Set(${WHO}_CID_NUMBER=${HOTDESK_INFO(cid_number,${WHO})}) exten => _X.,n,Set(${WHO}_CONTEXT=${HOTDESK_INFO(context,${WHO})}) exten => _X.,n,Goto(${${WHO}_CONTEXT},${EXTEN},1) [international] exten => _011.,1,NoOp() exten => _011.,n,Set(E=${EXTEN}) exten => _011.,n,Goto(outgoing,call,1) exten => i,1,NoOp() exten => i,n,Playback(silence/2&sorry-cant-let-you-do-that2) exten => i,n,Hangup() include => longdistance [longdistance] exten => _1NXXNXXXXXX,1,NoOp() exten => _1NXXNXXXXXX,n,Set(E=${EXTEN}) exten => _1NXXNXXXXXX,n,Goto(outgoing,call,1) exten => _NXXNXXXXXX,1,Goto(1${EXTEN},1) exten => i,1,NoOp() exten => i,n,Playback(silence/2&sorry-cant-let-you-do-that2) exten => i,n,Hangup() include => local [local] exten => _416NXXXXXX,1,NoOp() exten => _416NXXXXXX,n,Set(E=${EXTEN}) exten => _416NXXXXXX,n,Goto(outgoing,call,1) exten => i,1,NoOp() exten => i,n,Playback(silence/2&sorry-cant-let-you-do-that2) exten => i,n,Hangup()
If the call is allowed to be
executed, then the call is sent to the [outgoing]
context for call processing, where
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() exten => call,n,Set(CALLERID(name)=${${WHO}_CID_NAME}) exten => call,n,Set(CALLERID(number)=${${WHO}_CID_NUMBER}) exten => call,n,Dial(SIP/service_provider/${E}) exten => call,n,Playback(silence/2&pls-try-call-later) exten => call,n,Hangup()
Our service_provider
might look something like this
in sip.conf
:
[service_provider] type=friend host=switch1.service_provider.net username=my_username fromuser=my_username secret=welcome context=incoming canreinvite=no disallow=all allow=ulaw
And that’s it! The complete dialplan utilized for the hot-desk feature is displayed in full in Appendix G, An Example of func_odbc.
How many things have you just thought of that you could apply func_odbc to? See why we’re so excited about this feature as well?!
[137] And if you don’t know what a Dagwood is, that’s what Wikipedia is for. I am not that old.
[138] Remember that in a traditional phone system all extensions must be numbers, but in Asterisk, extensions can have names as well. A possible advantage of using an extension that’s not a number is that it will be much harder for a user to dial it from her phone and, thus, more secure. We’re going to use several named extensions in this example. If you want to be absolutely sure that a malicious user cannot access those named extensions, simply use the trick that the AEL loader uses: start with a priority other than 1.