Chapter 12. Relational Database Integration
Few things are harder to put up with than the annoyance of a good example.
Introduction
In this chapter we are going to explore integrating some Asterisk features and functions into a database. There are several databases available for Linux, but we have chosen to limit our discussion to PostgreSQL. While we acknowledge MySQL is also an extremely popular database, we had to choose one, and our experience with PostgreSQL tipped the scale in its favor. All that having been said, what we are actually going to be doing is discussing the ODBC connector, so as long has you have familiarity with getting your favorite database ODBC-ready, the substance of this chapter will apply to you.
Integrating Asterisk with databases is one of the fundamental
elements allowing clustering of Asterisk into a large, distributed
system. By utilizing the power of the database, dynamically changing
data can convey information across an array of Asterisk systems. Our
newest favorite Asterisk function is func_odbc
, which we will cover later in this
chapter.
While not all Asterisk deployments will require a relational database, understanding how to harness them opens the lid of a treasure chest full of new ways to design your telecom solution.
Installing the Database
The first thing to do is to install the PostgreSQL database server:[134]
# yum install -y postgresql-server
Then start the database, which will take a few seconds to initialize for the first time:
# service postgresql start
Next, create a user called asterisk, which we will use to connect to and manage the database. Run the following commands:
#su - postgres
$createuser -P
Enter name of user to add:
asterisk
Enter password for new user:
Enter it again:
Shall the new role be a superuser? (y/n)n
Shall the new user be allowed to create databases? (y/n)
y
Shall the new user be allowed to create more new users? (y/n)
n
CREATE USER
By
default, PostgreSQL does not listen on the TCP/IP connection, which
Asterisk will be using. We need to modify the /var/lib/pgsql/data/postgresql.conf file in
order to allow Asterisk to make IP connections to the database. To do
this, simply remove the comment from the beginning of the tcpip_socket
and port
parameters. Be sure to change the
tcpip_socket
option from false
to true
.
tcpip_socket = true max_connections = 100 # note: increasing max_connections costs about 500 bytes of shared # memory per connection slot, in addition to costs from shared_buffers # and max_locks_per_transaction. #superuser_reserved_connections = 2 port = 5432
Now, edit the
/var/lib/pgsql/data/pg_hba.conf
file in order to allow the asterisk user we just
created to connect to the PostgreSQL server over the TCP/IP socket. At
the end of the file, replace everything below #
Put your actual configuration here
with the following:
host all asterisk 127.0.0.1 255.255.255.255 md5 local all asterisk trust
Now we can create the database that we will use throughout this
chapter. We’re going to create a database called asterisk
and set the owner to our
asterisk user.
$createdb --owner=asterisk asterisk
CREATE DATABASE
Restart the PostgreSQL server after exiting from the postgres user back to root:
$exit
#service postgresql restart
We can verify our connection to the PostgreSQL server via TCP/IP like so:
#psql -h 127.0.0.1 -U asterisk
Password:
Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
asterisk=>
Double-check your configuration as discussed earlier if you get the following error, which means connections via the TCP/IP socket are not allowed:
psql: could not connect to server: Connection refused Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?
Installing and Configuring ODBC
The ODBC connector is a database abstraction layer that makes it possible for Asterisk to communicate with a wide range of databases without requiring the developers to create a separate database connector for every database Asterisk wants to support. This saves a lot of development effort and code maintenance. There is a slight performance cost to this because we are adding another application layer between Asterisk and the database. However, this can be mitigated with proper design and is well worth it when you need powerful, flexible database capabilities in your Asterisk system.
Before we install the connector in Asterisk, we have to install ODBC into Linux itself. To install the ODBC drivers, simply run the command:
# yum install -y unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel
Tip
See Chapter 3 for the matrix of packages you should have installed.
We need to install the unixODBC-devel package because it is used by Asterisk to create the ODBC modules we will be using throughout this chapter.
Verify that you have the PostgreSQL ODBC driver configured in the /etc/odbcinst.ini file. It should look something like this:
[PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/libodbcpsql.so Setup = /usr/lib/libodbcpsqlS.so FileUsage = 1
Verify the system is able to see the driver by running the
following command. It should return the label name PostgreSQL
if all is well.
#odbcinst -q -d
[PostgreSQL]
Next, configure the /etc/odbc.ini file, which is used to create an identifier that Asterisk will use to reference this configuration. If at any point in the future you need to change the database to something else, you simply need to reconfigure this file, allowing Asterisk to continue to point to the same place.[135]
[asterisk-connector] Description = PostgreSQL connection to 'asterisk' database Driver = PostgreSQL Database = asterisk Servername = localhost UserName = asterisk Password = welcome Port = 5432 Protocol = 7.4 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings =
Let’s verify that we can connect to our database using the
isql
application. The isql
application will not perform the connect
as the root user, and must be run as the same owner
as the database. Since the owner of the asterisk
database under PostgreSQL is the
asterisk user, we must create a Linux account with
the same name. In Chapter 14, we will take
advantage of this user to run Asterisk as
non-root.
#su - asterisk
$echo "select 1" | isql -v asterisk-connector
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> +------------+
| ?column? |
+------------+
| 1 |
+------------+
SQLRowCount returns 1
1 rows fetched
$exit
With unixODBC installed, configured, and verified to work, we need to recompile Asterisk so that the ODBC modules are created and installed. Change back to your Asterisk sources directory and run the ./configure script so it knows you have installed unixODBC.
#cd /usr/src/asterisk-1.4
#make distclean
#./configure
#make menuselect
#make install
Note
Almost everything in this chapter is turned on by default. You
will want to run make menuselect
to verify that the ODBC related modules are enabled. These include
cdr_odbc, func_odbc,
func_realtime, pbx_realtime,
res_config_odbc, res_odbc.
For voicemail stored in an ODBC database, be sure to select ODBC_STORAGE
from the Voicemail Build
Options menu. You can verify the modules exist in the /usr/lib/asterisk/modules/
directory.
Configuring res_odbc for Access to Our Database
ODBC connections are configured in the res_odbc.conf file located in /etc/asterisk. The res_odbc.conf file sets the parameters that the various Asterisk modules will use to connect to the database.[136]
Modify the res_odbc.conf file:
[asterisk] enabled => yes dsn => asterisk-connector username => asterisk password => welcome pooling => no limit => 0 pre-connect => yes
The dsn
option points at the database connection
we configured in /etc/odbc.ini,
and the pre-connect
option
tells Asterisk to open up and maintain a connection to
the database when loading the res_odbc.so module.
This lowers some of the overhead that would come from repeatedly
setting up and tearing down the connection to the database.
Once you’ve configured res_odbc.conf, start
Asterisk and verify the database connection with the odbc show
CLI command:
*CLI> odbc show
Name: asterisk
DSN: asterisk-connector
Pooled: no
Connected: yes
Using Realtime
The Asterisk Realtime Architecture (ARA) is a method of storing the configuration files (that would normally be found in /etc/asterisk) and their configuration options in a database table. There are two types of realtime; static and dynamic. The static version is similar to the traditional method of reading a configuration file, except that the data is read from the database instead. The dynamic realtime method is used for things such as user and peer objects (SIP, IAX2), and voicemail which loads and updates the information as it is required. Changes to static information requires a reload just as if you had changed the text file on the system, but dynamic information is polled by Asterisk as needed and requires no reload. Realtime is configured in the extconfig.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 used when you want to store the configuration
that you would normally place in the configuration files in /etc/asterisk but want to load from a
database. The same rules that apply to flat files on your system still
apply when using static realtime, such as requiring you to either run
the reload command from the Asterisk CLI, or to reload the module
associated with the configuration file (i.e., 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 filename.conf => driver,database[,table]
Note
If the table name is not specified, then Asterisk will use the name of the file instead.
The static realtime module uses a specifically formatted table to read the configuration of static files in from the database. You can define the table for static realtime in PostgreSQL as follows:
CREATE TABLE ast_config ( id serial NOT NULL, cat_metric int4 NOT NULL DEFAULT 0, var_metric int4 NOT NULL DEFAULT 0, filename varchar(128) NOT NULL DEFAULT ''::character varying, category varchar(128) NOT NULL DEFAULT 'default'::character varying, var_name varchar(128) NOT NULL DEFAULT ''::character varying, var_val varchar(128) NOT NULL DEFAULT ''::character varying, commented int2 NOT NULL DEFAULT 0, CONSTRAINT ast_config_id_pk PRIMARY KEY (id) ) WITHOUT OIDS;
A brief explanation about the columns is required in order to understand how Asterisk takes the rows from the database and applies them to the configuration for the various modules you may load:
cat_metric
The weight of the category within the file. A lower metric means it appears higher in the file (see the sidebar A Word About Metrics”).
var_metric
The weight of an item within a category. A lower metric means it appears higher in the list. This is useful for things like codec order in sip.conf or iax.conf where you want
disallow=all
to appear first (metric of 0), followed byallow=ulaw
(metric of 1), then byallow=gsm
(metric of 2) (see the sidebar A Word About Metrics”).filename
The filename the module would normally read from the hard drive of your system (i.e., musiconhold.conf, sip.conf, iax.conf, etc.).
category
The section name within the file, such as
[general]
, but don’t save to the database using the square brackets.var_name
The option on the left side of the equals sign (i.e., disallow is the
var_name
indisallow=all
).var_val
The value to an option on the right side of the equals sign (i.e., all is the
var_val
indisallow=all
).commented
Any value other than 0 will evaluate as if it were prefixed with a semicolon in the flat file (commented out).
A simple file we can load from static realtime is the musiconhold.conf 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 our classes are blank by running moh show classes
:
*CLI>restart now
*CLI>moh show classes
*CLI>
So let’s put the [default
] class back into Asterisk, but now
we’ll load it from the database. Connect to PostgreSQL and execute the
following INSERT
statements:
INSERT INTO ast_config (filename,category,var_name,var_val) VALUES ('musiconhold.conf','general','mode','files'); INSERT INTO ast_config (filename,category,var_name,var_val) VALUES ('musiconhold.conf','general','directory','/var/lib/asterisk/moh');
You
can verify 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 | general | mode | files musiconhold.conf | general | directory | /var/lib/asterisk/moh (2 rows)
And now, there’s just one last thing to modify in the extconfig.conf file in /etc/asterisk directory to tell Asterisk to get the data for musiconhold.conf from the database. Add the following line to the end of the extconfig.conf file, then save it:
musiconhold.conf => odbc,asterisk,ast_config
Then connect to the Asterisk console and perform a reload:
*CLI> module reload
You
can now verify that we have our music-on-hold classes 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. You can perform the same steps in order to load other flat files from the database!
Dynamic Realtime
The dynamic realtime system is used to load objects that may change often: SIP/IAX2 users and peers, queues and their members, and voicemail. Since this information in the system may either be changing or new records are being 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, but
dynamic realtime has well-defined configuration names such as sippeers
. Defining something like SIP peers
is done with the following format:
; extconfig.conf sippeers => driver,database[,table]
The table name is
optional, in which case Asterisk will use the predefined name (i.e.,
sippeers
) as the table to look up
the data. In our example, we’ll be using the ast_sippeers
table to store our SIP peer
information.
Tip
Remember that we have both SIP peers and SIP users; peers are end-points we send calls to, and a user is something we receive calls from. A friend is shorthand that defines both.
So to configure Asterisk to load all SIP peers from a database using realtime, we would define something like:
; extconfig.conf sippeers => odbc,asterisk,ast_sipfriends
To also load our SIP users from the database, define it like so:
sipusers => odbc,asterisk,ast_sipfriends
You
may have noticed we used the same table for both the sippeers
and sipusers
. This is because there will be a
type field (just as if you had defined the type in the
sip.conf file) that will let us define a type of
user
, peer
, or friend
. When defining the table for SIP
users and peers, we need at least the following:
+------+--------+-------+--------+-----+------------+----------+ |name |host |secret | ipaddr | port| regseconds | username | +------+--------+-------+--------+-----+------------+----------+ |100 |dynamic |welcome| | |1096954152 | 1000 | +------+--------+-------+--------+-----+------------+----------+
The
port
, regseconds
, and ipaddr
fields are required to let Asterisk
store the registration information for the peer in order to know where
to send the call. This is assuming the host is dynamic
; however, if the peer is static
, we would have to populate the
ipaddr
field ourselves. The
port
field is optional and would
use the default standard port defined in the [general]
section, and the regseconds
would remain blank.) There are
many more options for a SIP friend that we can define, such as the
caller ID, and adding that information is as simple as adding the
callerid
column to the table. See
the sip.conf.sample file for more options
that can be defined for SIP friends.
Storing Call Detail Records
Call Detail Records (CDR) contain information about calls that have passed through your Asterisk system. They are discussed further in Chapter 13. This is a popular use of databases in Asterisk because CDR can be easier to manage if you store the records in a database (for example, you could keep track of many Asterisk systems in a single table).
Let’s create a table in our database to store CDR. Log in to the PostgreSQL server with the psql application:
# psql -U asterisk -h localhost asterisk
Password:
And create the asterisk_cdr
table:
asterisk=> CREATE TABLE asterisk_cdr ( id bigserial NOT NULL, calldate timestamptz, clid varchar(80), src varchar(80), dst varchar(80), dcontext varchar(80), channel varchar(80), dstchannel varchar(80), lastapp varchar(80), lastdata varchar(80), duration int8, billsec int8, disposition varchar(45), amaflags int8, accountcode varchar(20), uniqueid varchar(40), userfield varchar(255), CONSTRAINT asterisk_cdr_id_pk PRIMARY KEY (id) ) WITHOUT OIDS;
You can verify the table was created by
using the \dt
command
(describe tables):
asterisk=> \dt asterisk_cdr
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | asterisk_cdr | table | asterisk
(1 row)
Next, configure Asterisk to store its CDR into the database. This is done in the /etc/asterisk/cdr_odbc.conf file with the following configuration:
[global] dsn=asterisk-connector username=asterisk password=welcome loguniqueid=yes table=asterisk_cdr
If Asterisk is already running, from the Asterisk CLI execute
module reload cdr_odbc.so
. You can
also just type
, to reload
everything.reload
*CLI> reload
Verify
the status of CDR by entering the following command and looking for
CDR registered backend:
ODBC
:
*CLI> cdr status
CDR logging: enabled
CDR mode: simple
CDR registered backend: cdr-custom
CDR registered backend: cdr_managerCDR registered backend: ODBC
Now,
perform a call through your Asterisk box and verify you have data in the
asterisk_cdr table. The easiest way to test a call is with the Asterisk
CLI command console dial
(assuming
that you have a sound card and chan_oss installed).
However, you can utilize any method at your disposal to place a test
call:
*CLI>console dial 100@default
-- Executing [100@default:1] Playback("OSS/dsp", "tt-weasels") in new stack
-- <OSS/dsp> Playing 'tt-weasels' (language 'en')
Then
connect to the database and perform a SELECT
statement to verify you have data in the asterisk_cdr
table. You could also do SELECT * FROM asterisk_cdr;
, but that will
return a lot more data:
#psql -U asterisk -h localhost asterisk
Password:
asterisk=>
SELECT id,dst,channel,uniqueid,calldate FROM asterisk_cdr;
id | dst | channel | uniqueid | calldate
----+-----+---------+----------------------+------------------------
1 | 100 | OSS/dsp | toronto-1171611019.0 | 2007-02-16 02:30:19-05
(1 rows)
Getting Funky with func_odbc: Hot-Desking
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.
Tip
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.
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.
Note
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, 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}
.
Note
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.
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]
Note
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})
Tip
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 and Chapter 6). 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.
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?!
ODBC Voicemail
Asterisk contains the ability to store voicemail inside the database using the ODBC connector. This is useful in a clustered environment where you want to abstract the voicemail data from the local system so that multiple Asterisk boxes have access to the same data. Of course, you have to take into consideration that you are centralizing a part of Asterisk, and you need to take actions to protect that data, such as regular backups, and possibly clustering the database backend using replication. If you are using PostgreSQL, there are some good projects for doing this: PGcluster (http://pgfoundry.org/projects/pgcluster/) and Slony-I (http://gborg.postgresql.org/project/slony1/projdisplay.php).
Asterisk stores the voicemail inside a Binary Large Object (BLOB). When retrieving the data, it pulls the information out of the BLOB and temporarily stores it on the hard drive while it is being played back to the user. Asterisk then removes the BLOB and records from the database when the user deletes the voicemail. Many databases, such as MySQL, contain native support for BLOBs, but PostgreSQL has a couple of extra steps required to utilize this functionality that we’ll explore in this section. When you’re done, you’ll be able to record, play back, and delete voicemail data from the database just as if it were stored on the local hard drive.
Note
This section builds upon previous configuration sections in
this chapter. If you have not already done so, be sure to follow the
steps in the Installing the Database” and Installing and Configuring ODBC” sections before
continuing. In the Installing and Configuring ODBC”
section, be sure you have enabled ODBC_STORAGE
in the menuselect system
under Voicemail Options.
Creating the Large Object Type
We have to tell PostgreSQL how to handle the large objects. This includes creating a trigger to clean up the data when we delete a record from the database that references a large object.
Connect to the database as the asterisk user from the console:
# psql -h localhost -U asterisk asterisk
Password:
At the PostgreSQL console, run the following script to create the large object type:
CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT; CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT; CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT; CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT; CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE ); CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT; CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;
We’ll be making use of the PostgreSQL procedural language called pgSQL/PL to create a function. This function will be called from a trigger that gets executed whenever we modify or delete a record from the table used to store voicemail. This is so the data is cleaned up and not left as an orphan in the database:
CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger" AS $$ declare msgcount INTEGER; begin -- raise notice 'Starting lo_cleanup function for large object with oid %',old.recording; -- If it is an update action but the BLOB (lo) field was not changed, dont do anything if (TG_OP = 'UPDATE') then if ((old.recording = new.recording) or (old.recording is NULL)) then raise notice 'Not cleaning up the large object table, as recording has not changed'; return new; end if; end if; if (old.recording IS NOT NULL) then SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording = old.recording; if (msgcount > 0) then raise notice 'Not deleting record from the large object table, as object is still referenced'; return new; else perform lo_unlink(old.recording); if found then raise notice 'Cleaning up the large object table'; return new; else raise exception 'Failed to cleanup the large object table'; return old; end if; end if; else raise notice 'No need to cleanup the large object table, no recording on old row'; return new; end if; end$$ LANGUAGE plpgsql;
We’re going to create a table called
voicemessages
where the voicemail
information will be stored:
CREATE TABLE voicemessages ( uniqueid serial PRIMARY KEY, msgnum int4, dir varchar(80), context varchar(80), macrocontext varchar(80), callerid varchar(40), origtime varchar(40), duration varchar(20), mailboxuser varchar(80), mailboxcontext varchar(80), recording lo, label varchar(30), "read" bool DEFAULT false );
And now we need to associate a trigger with our newly
created table in order to perform cleanup whenever we make a change or
deletion from the voicemessages
table:
CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE PROCEDURE vm_lo_cleanup();
Configuring voicemail.conf for ODBC Storage
There isn’t much to add to the voicemail.conf file to enable the
ODBC voicemail storage. In fact, it’s only three lines! Generally, you
probably have multiple format types defined in the [general]
section of
voicemail.conf, however we need to set this to a
single format. The wav49 format is a compressed WAV file format that
should be playable on both Linux and Microsoft Windows
desktops.
The odbcstorage
option points
at the name you defined in the res_odbc.conf file (if you’ve been
following along in this chapter, then we called it
asterisk). The odbctable
option refers to the table where
voicemail information should be stored. In the examples in this
chapter we use the table named voicemessages
:
[general] format=wav49 odbcstorage=asterisk odbctable=voicemessages
You may want to create a separate voicemail context, or you can utilize the default voicemail context:
[default] 1000 => 1000,J.P. Wiser
Now connect to your Asterisk console and unload then reload the app_voicemail.so module:
*CLI> module unload app_voicemail.so == Unregistered application 'VoiceMail' == Unregistered application 'VoiceMailMain' == Unregistered application 'MailboxExists' == Unregistered application 'VMAuthenticate' *CLI> module load app_voicemail.so Loaded /usr/lib/asterisk/modules/app_voicemail.so => (Comedian Mail (Voicemail System)) == Registered application 'VoiceMail' == Registered application 'VoiceMailMain' == Registered application 'MailboxExists' == Registered application 'VMAuthenticate' == Parsing '/etc/asterisk/voicemail.conf': Found
And verify that your new mailbox loaded successfully:
*CLI> voicemail show users for default
Context Mbox User Zone NewMsg
default 1000 J.P. Wiser 0
Testing ODBC Voicemail
Let’s create some simple dialplan logic to leave and retrieve some voicemail from our test voicemail box. We can use the simple dialplan logic as follows:
[odbc_vm_test] exten => 100,1,Voicemail(1000@default) ; leave a voicemail exten => 200,1,VoicemailMain(1000@default) ; retrieve a voicemail
Once you’ve updated your extensions.conf file, be sure to reload the dialplan:
*CLI> dialplan reload
You
can either include
the odbc_vm_test
context into a context
accessible by an existing user, or create a separate user to test
with. If you wish to do the latter, you could define a new SIP user in
sip.conf like so (this will work
assuming the phone is on the local LAN):
[odbc_test_user] type=friend secret=supersecret context=odbc_vm_test host=dynamic qualify=yes disallow=all allow=ulaw allow=gsm
Don’t forget to reload the SIP module:
*CLI> module reload chan_sip.so
And verify that the SIP user exists:
*CLI> sip show users like odbc_test_user
Username Secret Accountcode Def.Context ACL NAT
odbc_test_user supersecret odbc_vm_test No RFC3581
Then configure your phone or client with the username odbc_test_user and password supersecret, and then place a call to extension 100 to leave a voicemail. If successful, you should see something like:
-- Executing VoiceMail("SIP/odbc_test_user-10228cac", "1000@default") in new stack -- Playing 'vm-intro' (language 'en') -- Playing 'beep' (language 'en') -- Recording the message -- x=0, open writing: /var/spool/asterisk/voicemail/default/1000/tmp/dlZunm format: wav49, 0x101f6534 -- User ended message by pressing # -- Playing 'auth-thankyou' (language 'en') == Parsing '/var/spool/asterisk/voicemail/default/1000/INBOX/msg0000.txt': Found
We
can now make use of the psql
application again to make sure the recording really did make it into
the database:
# psql -h localhost -U asterisk asterisk
Password:
Then run a SELECT
statement to verify that you have some data in the voicemessages
table:
localhost=# SELECT id,dir,callerid,mailboxcontext,recording FROM voicemessages; id | dir | callerid | mailboxcontext | recording ---+------------------------------------------+--------------+---------------+------- 1 | /var/spool/asterisk/voicemail/default/1000/INBOX | +18005551212 | default | 47395 (1 row)
If the recording was placed in the database, we should
get a row back. You’ll notice that the recording column contains a
number (which will most certainly be different from that listed here),
which is really the object ID of the large object stored in a system
table. Let’s verify that the large object exists in this system table
with the lo_list
command:
localhost=# \lo_list
Large objects
ID | Description
-------+-------------
47395 |
(1 row)
What we’re verifying is that the object ID in the
voicemessages
table matches that
listed in the large object system table. We can also pull the data out
of the database and store it to the hard drive so we can play the file
back to make sure our message was saved correctly:
localhost=# \lo_export 47395 /tmp/voicemail-47395.wav
lo_export
Then verify the audio with your favorite audio application, such
as the play
application:
# play /tmp/voicemail-47395.wav
Input Filename : /tmp/voicemail-47395.wav
Sample Size : 8-bits
Sample Encoding: wav
Channels : 1
Sample Rate : 8000
Time: 00:06.22 [00:00.00] of 00:00.00 ( 0.0%) Output Buffer: 298.36K
Done.
And now that we’ve confirmed everything was stored in
the database correctly, we can try listening to it via the VoicemailMain()
application by dialing
extension 200:
*CLI> -- Executing VoiceMailMain("SIP/odbc_test_user-10228cac", "1000@default") in new stack -- Playing 'vm-password' (language 'en') -- Playing 'vm-youhave' (language 'en') -- Playing 'digits/1' (language 'en') -- Playing 'vm-INBOX' (language 'en') -- Playing 'vm-message' (language 'en') -- Playing 'vm-onefor' (language 'en') -- Playing 'vm-INBOX' (language 'en') -- Playing 'vm-messages' (language 'en') -- Playing 'vm-opts' (language 'en') -- Playing 'vm-first' (language 'en') -- Playing 'vm-message' (language 'en') == Parsing '/var/spool/asterisk/voicemail/default/1000/INBOX/msg0000.txt': Found
Conclusion
In this chapter, we learned about several areas where Asterisk can integrate with a relational database. This is useful for systems where you need to start scaling by clustering multiple Asterisk boxes working with the same centralized information, or when you want to start building external applications to modify information without requiring a reload of the system (i.e., not requiring the modification of flatfiles).
[134] On a large, busy system you will want to install this on a completely separate box from your Asterisk system.
[135] Yes, this is excessively verbose. The only entries you really
need are Driver
, Database
, and Servername
. Even the Username
and Password
are specified elsewhere, as seen
later.
[136] The pooling
and limit
options are quite useful for MS SQL Server and Sybase
databases. These permit you to establish multiple connections (up
to limit
connections) to a
database while ensuring that each connection has only one
statement executing at once (this is due to a limitation in the
protocol used by these database servers).
[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.
Get Asterisk: The Future of Telephony, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.