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

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();

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.

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:

[general]
format=wav49
odbcstorage=asterisk
odbctable=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:

[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

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

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

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

$ psql -h localhost -U asterisk asterisk
Password:

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)

localhost=# \lo_list
    Large objects
  ID   | Description
-------+-------------
 47395 |
(1 row)

localhost=# \lo_export 47395 /tmp/voicemail-47395.wav
lo_export

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

$ mysql -u asterisk -p asterisk
Enter password:

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

mysql> SELECT recording FROM voicemessages WHERE uniqueid = '5'
    -> DUMPFILE '/tmp/voicemail_recording.wav';

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