<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>ODBC Voicemail</title><link rel="stylesheet" href="styles.css" type="text/css" /><meta name="generator" content="DocBook XSL Stylesheets V1.69.1" /><link rel="start" href="index.html" title="Asterisk™: The Future of Telephony" /><link rel="up" href="asterisk-CHP-12.html" title="Chapter 12. Relational Database Integration" /><link rel="prev" href="getting_funky.html" title="Getting Funky with func_odbc: Hot-Desking" /><link rel="next" href="I_section12_tt1610.html" title="Conclusion" /></head><body><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">ODBC Voicemail</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="getting_funky.html">Prev</a> </td><th width="60%" align="center">Chapter 12. Relational Database Integration</th><td width="20%" align="right"> <a accesskey="n" href="I_section12_tt1610.html">Next</a></td></tr></table><hr /></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="I_section12_tt1578"></a>ODBC Voicemail</h2></div></div></div><p>Asterisk contains<a id="ch12_odbcvoicemailo" class="indexterm"></a><a id="ch12_voicemail" class="indexterm"></a> 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 <a id="I_indexterm12_tt1579" class="indexterm"></a>PostgreSQL, there are some good projects for doing
this:<a id="I_indexterm12_tt1580" class="indexterm"></a> PGcluster (<a href="http://pgfoundry.org/projects/pgcluster/" target="_top">http://pgfoundry.org/projects/pgcluster/</a>)
and Slony-I (<a href="http://gborg.postgresql.org/project/slony1/projdisplay.php" target="_top">http://gborg.postgresql.org/project/slony1/projdisplay.php</a>).</p><p>Asterisk stores the voicemail inside a<a id="I_indexterm12_tt1581" class="indexterm"></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.</p><p>
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>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 <a href="installing_the_database.html" title="Installing the Database">the section called “Installing the Database”</a>” and <a href="installing_configuring_odbc.html" title="Installing and Configuring ODBC">the section called “Installing and Configuring ODBC”</a>” sections before continuing.
In the <a href="installing_configuring_odbc.html" title="Installing and Configuring ODBC">the section called “Installing and Configuring ODBC”</a>” section, be
sure you have enabled <code class="literal">ODBC_STORAGE</code> in the
menuselect system under Voicemail Options.</p></div><p>
</p><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id4156624"></a>Creating the Large Object Type</h3></div></div></div><p>We have to tell PostgreSQL how to handle the large objects.
<a id="I_indexterm12_tt1582" class="indexterm"></a>This includes creating a trigger to clean up the data when
we delete a record from the database that references a large
object.</p><p>Connect to the database as the <span class="emphasis"><em>asterisk</em></span> user
from the console:<a id="I_programlisting12_tt1583"></a></p><pre class="programlisting"># <strong class="userinput"><code>psql -h localhost -U asterisk asterisk</code></strong>
Password:</pre><p>At the PostgreSQL console, run the following script to create the
large object type:</p><p><a id="I_programlisting12_tt1584"></a></p><pre class="programlisting">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;</pre><p>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:</p><p><a id="I_programlisting12_tt1585"></a></p><pre class="programlisting">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;</pre><p>We’re going to create a table called
<code class="literal">voicemessages</code> where the voicemail information will be
stored:</p><p><a id="I_programlisting12_tt1586"></a></p><pre class="programlisting">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
);</pre><p>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 <code class="literal">voicemessages</code> table:</p><p>
<a id="I_programlisting12_tt1587"></a></p><pre class="programlisting">CREATE TRIGGER vm_cleanup AFTER DELETE OR UPDATE ON voicemessages FOR EACH ROW EXECUTE
PROCEDURE vm_lo_cleanup();</pre><p>
</p></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id4156763"></a>Configuring voicemail.conf for ODBC Storage</h3></div></div></div><p>There isn’t much to add to the<a id="I_indexterm12_tt1588" class="indexterm"></a> <span class="emphasis"><em>voicemail.conf</em></span> file to enable the
ODBC voicemail storage. In fact, it’s only three lines! Generally, you
probably have multiple format types defined in the
<code class="literal">[general]</code> section of
<span class="emphasis"><em>voicemail.conf</em></span>, 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.</p><p>The <code class="literal">odbcstorage</code> option points at the name you
defined in the<a id="I_indexterm12_tt1589" class="indexterm"></a> <span class="emphasis"><em>res_odbc.conf</em></span> file (if you’ve been
following along in this chapter, then we called it
<span class="emphasis"><em>asterisk</em></span>). The <code class="literal">odbctable</code> option
refers to the table where voicemail information should be stored. In the
examples in this chapter we use the table named
<code class="literal">voicemessages</code>:</p><p><a id="I_programlisting12_tt1590"></a></p><pre class="programlisting">[general]
format=wav49
odbcstorage=asterisk
odbctable=voicemessages</pre><p>You may want to create a separate
voicemail context, or you can utilize the default voicemail
context:</p><p><a id="I_programlisting12_tt1591"></a></p><pre class="programlisting">[default]
1000 => 1000,J.P. Wiser</pre><p>Now connect to your Asterisk
console and unload then reload the <span class="emphasis"><em>app_voicemail.so</em></span>
module:</p><p>
<a id="I_programlisting12_tt1592"></a></p><pre class="programlisting">*CLI> <span class="bold"><strong>module unload app_voicemail.so</strong></span>
== Unregistered application 'VoiceMail'
== Unregistered application 'VoiceMailMain'
== Unregistered application 'MailboxExists'
== Unregistered application 'VMAuthenticate'
*CLI> <span class="bold"><strong>module load app_voicemail.so</strong></span>
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</pre><p>
</p><p>And verify that your new mailbox loaded successfully:</p><p>
<a id="I_programlisting12_tt1593"></a></p><pre class="programlisting">*CLI> <span class="bold"><strong>voicemail show users for default</strong></span>
Context Mbox User Zone NewMsg
default 1000 J.P. Wiser 0</pre><p>
</p></div><div class="section" lang="en" xml:lang="en"><div class="titlepage"><div><div><h3 class="title"><a id="id4156931"></a>Testing ODBC Voicemail</h3></div></div></div><p>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:</p><p>
<a id="I_programlisting12_tt1594"></a></p><pre class="programlisting">[odbc_vm_test]
exten => 100,1,Voicemail(1000@default) ; leave a voicemail
exten => 200,1,VoicemailMain(1000@default) ; retrieve a voicemail</pre><p>
</p><p>Once you’ve updated your<a id="I_indexterm12_tt1595" class="indexterm"></a> <span class="emphasis"><em>extensions.conf</em></span> file, be sure to
reload the dialplan:</p><p><a id="I_programlisting12_tt1596"></a></p><pre class="programlisting">*CLI> <strong class="userinput"><code>dialplan reload</code></strong></pre><p>You
can either <code class="literal">include</code> the <code class="literal">odbc_vm_test</code> 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 <code class="filename">sip.conf</code> like so (this will work assuming the
phone is on the local LAN):</p><p><a id="I_programlisting12_tt1597"></a></p><pre class="programlisting">[odbc_test_user]
type=friend
secret=supersecret
context=odbc_vm_test
host=dynamic
qualify=yes
disallow=all
allow=ulaw
allow=gsm</pre><p>Don’t forget to reload the SIP module:</p><p><a id="I_programlisting12_tt1598"></a></p><pre class="programlisting">*CLI> <strong class="userinput"><code>module reload chan_sip.so</code></strong></pre><p>And
verify that the SIP user exists:</p><p>
<a id="I_programlisting12_tt1599"></a></p><pre class="programlisting">*CLI> <strong class="userinput"><code>sip show users like odbc_test_user</code></strong>
Username Secret Accountcode Def.Context ACL NAT
odbc_test_user supersecret odbc_vm_test No RFC3581</pre><p>
</p><p>Then configure your phone or client with the username
<span class="emphasis"><em>odbc_test_user</em></span> and password
<span class="emphasis"><em>supersecret</em></span>, and then place a call to extension 100
to leave a voicemail. If successful, you should see something
like:</p><p><a id="I_programlisting12_tt1600"></a></p><pre class="programlisting"> -- 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</pre><p>We
can now make use of the <code class="literal">psql</code>
application again to make sure the recording really did make it into the
database:</p><p><a id="I_programlisting12_tt1601"></a></p><pre class="programlisting"># <strong class="userinput"><code>psql -h localhost -U asterisk asterisk</code></strong>
Password:</pre><p>Then run a <code class="literal">SELECT</code> statement to
verify that you have some data in the <code class="literal">voicemessages</code>
table:</p><p><a id="I_programlisting12_tt1602"></a></p><pre class="programlisting">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)</pre><p>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<a id="I_indexterm12_tt1603" class="indexterm"></a> <code class="literal">lo_list</code> <span class="keep-together">command</span>:</p><p><a id="I_programlisting12_tt1604"></a></p><pre class="programlisting">localhost=#<strong class="userinput"><code> \lo_list</code></strong>
Large objects
ID | Description
-------+-------------
47395 |
(1 row)</pre><p>What we’re verifying is that the object ID in the
<code class="literal">voicemessages</code> 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:</p><p>
<a id="I_programlisting12_tt1605"></a></p><pre class="programlisting">localhost=# <strong class="userinput"><code>\lo_export 47395 /tmp/voicemail-47395.wav</code></strong>
lo_export</pre><p>
</p><p>Then verify the audio with your favorite audio application, such
as the <code class="literal">play</code> application:</p><p><a id="I_programlisting12_tt1606"></a></p><pre class="programlisting"># <strong class="userinput"><code>play /tmp/voicemail-47395.wav</code></strong>
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.</pre><p>And now that we’ve confirmed everything was stored in
the database correctly, we can try listening to it via the
<code class="literal">VoicemailMain()</code> application by dialing extension
200:<a id="I_indexterm12_tt1607" class="indexterm"></a><a id="I_indexterm12_tt1608" class="indexterm"></a></p><p>
<a id="I_programlisting12_tt1609"></a></p><pre class="programlisting">*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</pre><p>
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="getting_funky.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="asterisk-CHP-12.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="I_section12_tt1610.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Getting Funky with func_odbc: Hot-Desking </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Conclusion</td></tr></table></div><div xmlns="" id="svn-footer"><hr /><p>You are reading <em>Asterisk: The Future of Telephony</em> (2nd Edition for Asterisk 1.4), by Jim van Meggelen, Jared Smith, and Leif Madsen.<br />
This work is licensed under the <a href="http://creativecommons.org/licenses/by-nc-nd/3.0/">Creative Commons Attribution-Noncommercial-No Derivative Works License v3.0</a>.<br />
To submit comments, corrections, or other contributions to the text, please visit <a href="http://oreilly.com/catalog/9780596510480/">http://www.oreilly.com/</a>.</p></div></body></html>
distrib
>
Mandriva
>
2008.1
>
i586
>
media
>
contrib-updates
>
by-pkgid
>
c7095aefea7b97fbd2a596dcbfb9d481
>
files
>
35
