Asterisk Voicemail ODBC storage

ver: CVS HEAD >= 08-24-05 | Asterisk 1.2

Requirements

unixODBC (app and dev required) – Check your local RPM provider or http://www.unixodbc.org/

Setup

Asterisk 1.4
1) Run “make menuselect”
2) Go down to “Voicemail Build Options”
3) Select ODBC_STORAGE. Note that if there is an “XXX” next to this option, then the necessary dependencies are not installed. Be sure that you have unixodbc-dev as well as libtool installed.
4) Hit ‘x’ to save an exit.

Asterisk 1.2
You have to edit the Makefile in /apps adding:

CFLAGS+=-DEXTENDED_ODBC_STORAGE
CFLAGS+=-DUSE_ODBC_STORAGE

Or you can unrem (remove the #) the existing lines that already say those two lines. (Yes, you need both lines.)

If you’ve already compiled asterisk, you must recompile (make clean;make;make install).

Voicemail.conf

Uncomment or add these lines to voicemail.conf.

odbcstorage should match the section name in res_odbc.conf
odbctable should be the name of the table you’re storing messages in.

odbcstorage=asterisk
odbctable=voicemessages

Database format

MySQL

CREATE TABLE `bit_voicemessages` (
`id` int(11) NOT NULL auto_increment,
`msgnum` int(11) NOT NULL default ‘0’,
`dir` varchar(80) default ”,
`context` varchar(80) default ”,
`macrocontext` varchar(80) default ”,
`callerid` varchar(40) default ”,
`origtime` varchar(40) default ”,
`duration` varchar(20) default ”,
`mailboxuser` varchar(80) default ”,
`mailboxcontext` varchar(80) default ”,
`recording` longblob,
`flag` varchar(128) default ”,
PRIMARY KEY (`id`),
KEY `dir` (`dir`)
) ENGINE=InnoDB;

Asterisk 1.6.1

requires a new column “flag” added above.

DB2

CREATE TABLE voicemessages (
id bigint generated always as identity NOT NULL,
dbdate timestamp not null default current timestamp,
dir varchar(80) default ”,
msgnum bigint NOT NULL default 0,
context varchar(80) default ”,
macrocontext varchar(80) default ”,
callerid varchar(40) default ”,
origtime varchar(40) default ”,
duration varchar(20) default ”,
mailboxuser varchar(80) default ”,
mailboxcontext varchar(80) default ”,
recording blob,
PRIMARY KEY (id)
);

create index idx_voicemsgs on voicemessages(dir,msgnum);

README.odbcstorage

ODBC Voicemail Storage
======================

ODBC Storage allows you to store voicemail messages within a database
instead of using a file. This is *not* a full realtime engine and

only* supports ODBC. The table description for the “voicemessages”

table is as follows:

Mark

Other considerations

When using ODBC for storage, app_voicemail reads the busy and unavail messages from the database and deletes any it finds on the local voicemail spool path. If there is no message held in the database and there is one locally, app_voicemail will play the local version and then delete it. Further calls will just play the standard Alison “I’m sorry, but the extension……..” message.

If you’ve already started using ODBC and don’t want to run the conversion script, below, then assuming you’re using MySQL, you can manually insert the WAV files into the database with the following SQL:

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’));
INSERT INTO voicemail (msgnum,dir,mailboxuser,mailboxcontext,recording) VALUES (-1,’/var/spool/asterisk/voicemail/CONTEXT/USER/unavail’,’CONTEXT’,’USER’,LOAD_FILE(‘/var/spool/asterisk/voicemail/CONTEXT/USER/unavail.WAV’));

Where ‘CONTEXT’ and ‘USER’ are the context and username to be used and the paths shown are to be amended as appropriate. 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.

When connecting to MS SQL Server you may need to adjust the option “textsize” to a larger value than provided in the default freetds.conf file. With the default provided value my voicemail playback was being cut short, with the adjusted value of:

/etc/freetds.conf
textsize = 20971520

that fixed the issue.

Notes:

I had a problem initially with connecting, it was an unixODBC problem so make sure you’ve got that configured correctly. – Flobi
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. -X1Z

PHP script to convert to ODBC voicemail storage