[asterisk-users] Preparing to store vm in database

[Leif Madsen]

On 09/12/11 01:10 AM, Mike Diehl wrote:
> Hi all,
>
> I'm getting ready to start storing all of my voicemail in a mysql database.
> I've already got RT sip and RT voicemailboxes working.
>
> I understand that vm storage only works via odbc.  I've read all of the
> documentation I could find, including:
>
> http://www.voip-info.org/wiki/view/Asterisk+Voicemail+ODBC+storage
>
>
> ===========================================================
> But there was this comment, at the bottom of the page:
> "I tried adding ODBC message storage to a 1.2.5 system already using MySQL for
> RealTime... Not a good idea, but using ODBC for both Realtime and msg storage
> seems good so far."
>
> Is this still true with Asterisk 1.6.2.9?  Or do I need to migrate all of my
> RT configuration to ODBC?

Interfacing with a database through ODBC in Asterisk is inherently more 
stable. The res_odbc interface has received significant amounts of 
testing and development over the years, and is definitely the most 
stable. One tip I'd offer is to use the latest ODBC drivers from 
unixodbc.org and not via the packages on your system, which are usually 
quite old (the ones shipped with CentOS 5 are from 2006 if I remember 
correctly).

The MySQL and PostgreSQL drivers receive quite a bit less attention. 
I've had good luck with res_odbc over the years as I've been deploying 
it since Asterisk 1.4. The res_odbc drivers have received lots of 
attention from its author, Tilghman Lesher.


> ===========================================================
> I also read this comment:
> "Make sure you load the .WAV file and not the .wav or .gsm or it won't work!
> <-- is that true? I couldnt get .WAV to work. But when I used a 16bit 8000Hz
> file with a .wav extension, it worked fine."
>
> Will I be ok if I just load the .wav file?

If it works for you in development and testing, then it should be fine. 
I haven't deployed voicemail audio into the database for a few years, 
but I think I used 'wav' as well.

> ===========================================================
> Also, I see that I'll have to pre-load all of the voicemail messages and
> greeting files into the database, like so:
>
> INSERT INTO voicemail (msgnum,dir,mailboxuser,mailboxcontext,recording) VALUES
> (-1,'/var/spool/asterisk/voicemail/CONTEXT/USER/busy','CONTEXT','USER',LOAD_FILE('/var/spool/asterisk/voicemail/CONTEXT/USER/busy.WAV'));
>
> I assume this has to be done for every CONTEXT and every USER, and those
> values need to be substituted into both the fields, and the directory path
> value.

That makes sense. If you're going to start using voicemail in the 
database, then you'll need some data there ;)

> ===========================================================
> Finally, is there an AGI command that will play a .wav file from the database?

There is not. Some work was done a few years ago to allow playing audio 
from the database like you're thinking, but it was never complete, and 
has never become a priority issue for any community developer to 
complete. You could of course create an AGI() script that pulled the 
audio out of the database, caching the audio for a period of time, then 
just played it like you would any other file, and cleaning up the file 
after.

-- 
Leif Madsen
http://www.oreilly.com/catalog/asterisk