ODBC Voicemail

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 take actions 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 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.


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 sections the section called “Installing PostgreSQL for CentOS” and the section called “Installing and Configuring ODBC” before continuing. In the latter section, be sure you have enabled ODBC_STORAGE in the menuselect system under Voicemail Options.

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. 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


You must be a superuser to execute the following code. Also, if you use the postgres user to create the table, you will need to use the ALTER TABLE SQL directive to change the owner to the asterisk user.

At the PostgreSQL console, run the following script to create the large object type:

CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal

CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal

CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal

CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal

CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend,


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 $$
      msgcount INTEGER;
      -- raise notice 'Starting lo_cleanup function for large object with oid
      -- 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;
          perform lo_unlink(old.recording);
          if found then
            raise notice 'Cleaning up the large object table';
            return new;
            raise exception 'Failed to clean up the large object table';
            return old;
          end if;
        end if;
        raise notice 'No need to clean up the large object table, 
        no recording on old row';

        return new;
      end if;
    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:

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 16.6, “ODBC voicemail storage table layout” 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 16.6. ODBC voicemail storage table layout

Column nameColumn type
uniqueidSerial, primary key
dirVarchar 80
recordingBLOB (Binary Large OBject)
contextVarchar 80
macrocontextVarchar 80
calleridVarchar 40
origtimeVarchar 40
durationVarchar 20
mailboxuserVarchar 80
mailboxcontextVarchar 80
labelVarchar 30
readBoolean, default false[a]
flagVarchar 10

[a] read is a reserved word in both MySQL and PostgreSQL (and likely other databases), which means you need to escape the column name when you create it. In MySQL this is done with backticks (`) around the word read when you create the table, and in PostgreSQL with double quotes ("). In MS SQL you would use square brackets, e.g., [read].

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:


You may want to create a separate voicemail context, or you can utilize the default voicemail context. Alternatively, you can skip creating a new user and use an existing user, such as 0000FFFF0001. We’ll define the mailbox in the default voicemail context like so:

1000 => 1000,J.P. Wiser


You can also use the voicemail definition in extconfig.conf to load your users from the database. See the section called “Dynamic Realtime” for more information about setting up certain module configuration options in the database, and the section called “Static Realtime” for details on loading the rest of the configuration file.

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

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 Voicemail

Let’s create some simple dialplan logic to leave and retrieve some voicemail from our test voicemail box. You can use the simple dialplan logic that follows (or, of course, any voicemail delivery and retrieval functionality you defined earlier in this book):

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):



One of the ways that unsavory folks get into systems is via test users that are not immediately removed from the system after testing. Whenever you’re utilizing a test extension, you should be doing it on a system that is removed from the Internet, or at the very least, place it into a context that does not have access to outbound dialing and has a strong password.

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 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 
    -- 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


At this point you can check the database to verify that your data was successfully written. See the upcoming sections for more information.

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:

    -- 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

then 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 that 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 that 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

Then verify the audio with your favorite audio application, such as play:

$ 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


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:

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 |


You can also add the recording column to the SELECT statement, but you’ll end up with a lot of gibberish on your screen.

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';


The user you’re exporting data with needs to have the FILE permission in MySQL, which means it must have been granted ALL access. If you did not grant ALL privileges to the asterisk user, you will need to utilize the root user for file export.

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 


 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