ODBC Voicemail Message Storage
ODBC Voicemail Message Storage
Asterisk enables you 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 act to protect that data, such as making regular backups and possibly clustering the database backend using replication.
Asterisk stores each voicemail message 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 the record from the database when the user deletes the voicemail. Many databases, such as MySQL, contain native support for BLOBs, but as you’ll see, with PostgreSQL a couple of extra steps are required to utilize this functionality. After com‐ pleting this section 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.
Compiling the app_voicemail Module to Support ODBC Storage
In order to support writing voice messages to an ODBC database, the capability to do so must be compiled into the voicemail module.
Navigate to the directory where you downloaded your Asterisk source code.
cd ~/src/asterisk-complete/asterisk/11
Run make with the menuselect argument:
$ sudo make menuselect
This will start the Asterisk Module and Build Selection interface. You will want to navigate to Voicemail Build Options and select ODBC_STORAGE :
Asterisk Module and Build Option Selection
Voicemail Build Options
— core —
( ) FILE_STORAGE
[*] ODBC_STORAGE
XXX IMAP_STORAGE
Then simply save, and run:
$ make install
and restart Asterisk. Your voicemail is ready to write to the database!
Creating the Large Object Type for PostgreSQL
While MySQL has a BLOB (Binary Large OBject) type, we have to tell PostgreSQL how to handle large objects. 19 This includes creating a trigger to clean up the data when we delete from the database a record 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 in the table used to store voicemail messages. 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,
don’t 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 clean up the large object table’;
return old;
end if;
end if;
else
raise notice ‘No need to clean up the large object table,
no recording on old row’;
return new;
end if;
end$$
LANGUAGE plpgsql;
— 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,
don’t 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 clean up the large object table’;
return old;
end if;
end if;
else
raise notice ‘No need to clean up 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,
flag varchar(10)
);
And now we need to associate a trigger with our newly created table in order to perform cleanup whenever we change or delete a record in the voicemessages table:
CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW
EXECUTE PROCEDURE vm_lo_cleanup();
ODBC Voicemail Storage Table Layout
We’ll be utilizing the voicemessages table for storing our voicemail information in an ODBC-connected database. Table 1 describes the table configuration for ODBC voicemail storage. If you’re using a PostgreSQL database, the table definition and large object support were configured in the preceding section.
Table 1 ODBC voicemail storage table layout
Here is an example of how to create this table under MySQL:
CREATE TABLE voicemessages
(
uniqueid serial PRIMARY KEY,
msgnum int(4),
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 blob,
label varchar(30),
`read` bool DEFAULT false,
flag varchar(10)
);
A PostgreSQL example is in the previous section.
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! Normally, you probably have multiple format types defined in the [general] section of voicemail.conf, but we need to set this to a single format because we can only save one file (format) to the database. 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 . Edit the [general] section of your voicemail.conf file so that the following values are
set:
[general]
format=wav49
odbcstorage=asterisk
odbctable=voicemessages
To create the users you can either separate voicemail context, or simply use the default
voicemail section. Alternatively, you can skip creating a new user and use an existing
user, such as 0000FFFF0001 . We’ll define the mailbox in the default section of the voice
mail.conf file like so:
[default]
1000 => 1000,J.P. Wiser
Now connect to your Asterisk console and unload, then load 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
Then 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 Voice Message Storage
Let’s create some simple dialplan logic to leave and retrieve some voicemail from our test voicemail box. You can add the simple dialplan logic that follows to your exten sions.conf file (or, of course, any voicemail delivery and retrieval functionality you de‐
fined earlier in this book):
[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
Then configure your phone or client with the username odbc_test_user and password
<supersecret>, and 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
Now that you’ve confirmed everything was stored in the database correctly, you 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
Verifying binary data stored in PostgreSQL
To make sure the recording really did make it into the database, use the psql application:
$ psql -h localhost -U asterisk asterisk
Password:
Next, run a SELECT statement to verify that you have some data in the voicemessages
table:
localhost=# SELECT uniqueid,dir,callerid,mailboxcontext,
recording FROM voicemessages;
uniqueid | dir | callerid
———+—————————————————————+————–
1 | /var/spool/asterisk/voicemail/default/1000/INBOX | +18005551212
| mailboxcontext | recording |
+—————-+———–+
| default | 47395 |
(1 row)
If the recording was placed in the database, you should get a row back. You’ll notice that the recording column contains a number (which will most certainly be different from what is listed here), which is really the object ID of the large object stored in a system table. You can 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 you’re verifying is that the object ID in the voicemessages table matches what is listed in the large object system table. You can also pull the data out of the database and store it to the hard drive:
localhost=# \lo_export 47395 /tmp/voicemail-47395.wav
lo_export
Then verify the audio with your favorite audio application, such as play:
$ play /tmp/voicemail-47395.wav
Input Filename : /temp/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.
Verifying binary data stored in MySQL
To verify that your data is being written correctly, you can use the mysql application to log into your database and export the voicemail recording to a file:
$ mysql -u asterisk -p asterisk
Enter password:
Once logged into the database, you can use a SELECT statement to dump the contents of the recording to a file. First, though, make sure you have at least a single recording in your voicemessages table: 20
mysql> SELECT uniqueid, msgnum, callerid, mailboxuser, mailboxcontext, `read`
-> FROM voicemessages;
+———-+————-+——————————+————-
| uniqueid | msgnum | callerid | mailboxuser
+———-+————-+——————————+————-
|1 | 0 | “Leif Madsen” <100> | 100
|2 | 1 | “Leif Madsen” <100> | 100
|3 | 2 | “Leif Madsen” <100> | 100
|5 | 0 | “Julie Bryant” <12565551111> | 100
+———-+——–+——————————+——————-
+—————-+——+
| mailboxcontext | read |
+—————-+——+
| shifteight.org | 0 |
| shifteight.org | 0 |
| shifteight.org | 0 |
| default | 0 |
+—————-+——+
Having verified that you have data in your voicemessages table, you can export one of the recordings and play it back from the console:
mysql> SELECT recording FROM voicemessages WHERE uniqueid = ‘5’
-> DUMPFILE ‘/tmp/voicemail_recording.wav’;
Now exit the MySQL console, and use the play application from the console (assuming you have speakers and a sound card configured on your Asterisk system, which you might if you are going to use it for overhead paging), or copy the file to another system and listen to it there:
$ play /tmp/voicemail_recording.wav
voicemail_recording.wav:
File Size: 7.28k Bit Rate: 13.1k
Encoding: GSM
Channels: 1 @ 16-bit
Samplerate: 8000Hz
Replaygain: off
Duration: 00:00:04.44
In:100% 00:00:04.44 [00:00:00.00] Out:35.5k [ | ] Hd:4.4 Clip:0
Done.
Database Integration of ACD Queues
One of the advantages of storing configuration information in a database is that it can make it easier to create interfaces to manage the data (in theory this can be done with configuration files, too; however, most web-based frameworks or interface development environments assume that configuration details will be stored in a database).
With a Call Centre (often referred to as ACD Queues), it can be very useful to be able to allow adjustment of queue parameters without having to edit and reload configuration files. Management of a call center can be a complex task, and allowing for simpler adjustment of parameters can make everyone’s life a whole lot easier.
Storing queues.conf in a Database
Storing queues.conf in a database works the same as any other config file. You have the choice of using either Static or Dynamic Realtime.
Storing queues.conf using Static Realtime
First, in extconfig.conf, you need a section that indicates queues.conf is going to be stored in a database:
vim /etc/asterisk/extconfig.conf
[settings]
queues.conf => odbc,asterisk,ast_config
Second, in the table itself, you need to store the queue parameters as they would appear in the queues.conf file.
An example of the syntax for a single-line entry would be something like this:
mysql> insert into ast_config
(cat_metric,var_metric,filename,category,var_name,var_val,commented)
VALUES
(2,1,’queues.conf’,’firstqueue’,’strategy’,’rrmemory’,0);
This would produce an entry in the table looking something like Table 2.
Table 2. An example table entry
You’ll need a few dozen entries to properly replicate your queues.conf file.
We recommend using Dynamic Realtime for storing your queue parameters.
Storing queues.conf using Dynamic Realtime
Storing queue parameters in a table makes a lot more sense if you use Dynamic Realtime. The table you create will be a bit easier on the eyes (although it can end up containing a lot of columns 22 ), and each queue will be defined in a single record:
CREATE TABLE `Queues` (
`QueueID` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(128) NOT NULL COMMENT ‘Asterisk’s name for the queue’,
`description` varchar(128) default NULL,
`maxlen` tinyint(4) default NULL,
`reportholdtime` varchar(3) default ‘no’,
`periodic_announce_frequency` varchar(4) default NULL,
`periodic_announce` varchar(128) default NULL,
`strategy` varchar(20) NOT NULL default ‘rrmemory’,
`joinempty` varchar(35) default ‘no’,
`leavewhenempty` varchar(35) default ‘no’,
`autopause` varchar(3) default ‘no’,
`announce_round_seconds` varchar(4) default NULL,
`retry` varchar(4) default NULL,
`wrapuptime` varchar(4) default NULL,
`announce_holdtime` varchar(3) default ‘no’,
`announce_frequency` varchar(4) default ‘0’,
`timeout` varchar(4) default ’60’,
`context` varchar(128) NOT NULL,
`musicclass` varchar(128) default ‘default’,
`autofill` varchar(3) default ‘yes’,
`ringinuse` varchar(45) default ‘no’,
`musiconhold` varchar(128) default ‘yes’,
`monitor_type` varchar(128) default ‘MixMonitor’,
`monitor_format` varchar(128) default ‘wav’,
`servicelevel` varchar(4) default ’60’,
`queue_thankyou` varchar(128) default ”,
`queue_youarenext` varchar(128) default ”,
`queue_thereare` varchar(128) default ”,
`queue_callswaiting` varchar(128) default ”,
`queue_holdtime` varchar(128) default ”,
`queue_minutes` varchar(128) default ”,
`queue_seconds` varchar(128) default ”,
`queue_lessthan` varchar(128) default ”,
`queue_reporthold` varchar(128) default ”,
`relative_periodic_announce` varchar(4) default ‘yes’,
PRIMARY KEY (`QueueID`),
UNIQUE KEY `name_UNIQUE` (`name`),
UNIQUE KEY `UniqueID_UNIQUE` (`QueueID`)
)
You then simply edit the extconfig.conf file to tell Asterisk you want to use this table to store your queue configurations:
vim /etc/asterisk/extconfig.conf
[settings]
queues => odbc,asterisk,queue_table
queue_members => odbc,asterisk,queue_member_table
CREATE TABLE queue_member_table (
uniqueid INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
membername varchar(40),
queue_name varchar(128),
interface varchar(128),
penalty INT(11),
paused INT(11),
UNIQUE KEY queue_interface (queue_name, interface)
);
queue_member_table does not need to have any data stored in it. It simply has to exist.
After restarting Asterisk, your queues should now be viewable via the console command queue show , and of course you’ll also be able to use them in your dialplan.
Storing Dialplan Parameters for a Queue in a Database
The dialplan application Queue() allows for several parameters to be passed to it. The CLI command core show application Queue defines the following syntax:
[Syntax]
Queue(queuename[,options[,URL[,announceoverride[,timeout[,AGI[,macro[,gosub[,
rule[,position]]]]]]]]])
Since we’re storing our queue in a database, why not also store the parameters you wish to pass to the queue in a similar manner?
CREATE TABLE `QueueDialplanParameters` (
`QueueDialplanParametersID` mediumint(8) NOT NULL auto_increment,
`Description` varchar(128) NOT NULL,
`QueueID` mediumint(8) unsigned NOT NULL COMMENT ‘Pointer to queues_table’,
`options` varchar(45) default ‘n’,
`URL` varchar(256) default NULL,
`announceoverride` bit(1) default NULL,
`timeout` varchar(8) default NULL,
`AGI` varchar(128) default NULL,
`macro` varchar(128) default NULL,
`gosub` varchar(128) default NULL,
`rule` varchar(128) default NULL,
`position` tinyint(4) default NULL,
`queue_tableName` varchar(128) NOT NULL,
PRIMARY KEY (`QueueDialplanParametersID`)
);
Using func_odbc , you can write a function that will return the dialplan parameters
relevant to that queue:
[QUEUE_DETAILS]
prefix=GET
dsn=asterisk
readsql=SELECT * FROM QueueDialplanParameters
readsql+= WHERE QueueDialplanParametersID=’${ARG1}’
Then pass those parameters to the Queue() application as calls arrive:
exten => s,1,Verbose(1,Call entering queue named ${SomeValidID)
same => n,Set(QueueParameters=${GET_QUEUE_DETAILS(SomeValidID)})
same => n,Queue(${QueueParameters})
While somewhat more complicated to develop than just writing appropriate dialplan, the advantage is that you will be able to manage a larger number of queues, with a wider variety of parameters, using dialplan that is flexible enough to handle any sort of parameters the queueing application in Asterisk accepts. For anything more than a very simple queue, we think you will find the use of a database for all this will be well worth the effort.
Writing queue_log to Database
Finally, we can store our queue_log to a database, which can make it easier for external applications to extract queue performance details from the system:
CREATE TABLE queue_log (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
time char(26) default NULL,
callid varchar(32) NOT NULL default ”,
queuename varchar(32) NOT NULL default ”,
agent varchar(32) NOT NULL default ”,
event varchar(32) NOT NULL default ”,
data1 varchar(100) NOT NULL default ”,
data2 varchar(100) NOT NULL default ”,
data3 varchar(100) NOT NULL default ”,
data4 varchar(100) NOT NULL default ”,
data5 varchar(100) NOT NULL default ”,
PRIMARY KEY (`id`)
);
Edit your extconfig.conf file to refer to the queue_log table:
[settings]
queue_log => odbc,asterisk,queue_log
A restart of Asterisk and your queue will now log information to the database. As an example, logging an agent into the sales queue should produce something like this:
mysql> select * from queue_log;
+—-+———————————–+———————-+—————-+
| id | time | callid | queuename |
+—-+———————————–+———————-+—————-+
| 1 | 2013-01-22 15:07:49.772263 | NONE | NONE |
| 2 | 2013-01-22 15:07:49.809028 | toronto-1358885269.1 | support |
+—-+————————————+———————-+—————-+
+——————+————+———+——-+———+——-+——-+
| agent | event | data1 | data2 | data3 | data4 | data5 |
+——————+————+——-+——-+——-+——-+——-+
| NONE | QUEUESTART| | | | | |
| SIP/0000FFFF0001 | ADDMEMBER | | | | |
+——————+————+——-+——-+——-+——-+——-+
If you’re developing any sort of external application that needs access to queue statistics, having the data stored in this manner will prove far superior to using the /var/log/asterisk/queue_log file.
