login | register
Sat 06 of Sep, 2008 [16:16 UTC]

voip-info.org

Discuss [3] History

Asterisk Voicemail ODBC storage

Created by: flobi,Last modification on Mon 20 of Aug, 2007 [17:24 UTC] by pgaz

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 `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,
 PRIMARY KEY  (`id`),
 KEY `dir` (`dir`)
) ENGINE=InnoDB;


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:

+-------------------------------------------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+-------------------------------------------+-------+
| msgnum       | int(11)     | YES  |     | NULL    |       |
| dir          | varchar(80) | YES  | MUL | NULL    |       |
| context      | varchar(80) | YES  |     | NULL    |       |
| macrocontext | varchar(80) | YES  |     | NULL    |       |
| callerid     | varchar(40) | YES  |     | NULL    |       |
| origtime     | varchar(40) | YES  |     | NULL    |       |
| duration     | varchar(20) | YES  |     | NULL    |       |
| recording    | longblob    | YES  |     | NULL    |       |
+-------------------------------------------+-------+

The database name (from /etc/asterisk/res_odbc.conf) is in the 
"odbcstorage" variable in the general section.

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!


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



Comments

Comments Filter
222

333Re: Aaaaaaaaaaarrrrrrrrgggggggghh (inhale) hhhhhhhhhhh!!!!!!!!!!!!!

by flobi, Thursday 12 of January, 2006 [19:01:27 UTC]
Apparently, I downloaded some new source or something and I had to re-un-rem the Makefile lines. Maybe I can have this done tomorrow? I don't know.
222

333Aaaaaaaaaaarrrrrrrrgggggggghh (inhale) hhhhhhhhhhh!!!!!!!!!!!!!

by flobi, Thursday 12 of January, 2006 [16:08:55 UTC]
I've spent the last 4 months working on a AGI system with the basis of using the database almost exclusively with static config files for everything I don't need to change...so I'm writing the part to configure voicemail setup and I notice that my voicemails aren't being saved in the database at all. Then, I go to look for resouces and the main resource I find (this page) was written by ME saying I couldn't get it to work!

But what's wierd is that I have SOME files in my database that would indicate that I at least had it working at one point. And they want this thing finished tomorrow? I don't think so.
222

333One minor addition

by mghaynes, Monday 21 of November, 2005 [17:44:57 UTC]
I just got voicemail odbc storage working using this tutorial. The only thing I wanted to make perfectly clear since it's mentioned more as a footnote in this document. The odbcstorage variable should be set in the general section of voicemail.conf and should be given the odbc name listed in res_odbc.conf for your asterisk DSN, not the db name nor the dsn itself. Once I set this in this manner, it started working perfectly. I still cannot get the realtime voicemail configuration to work, but that's another story.

Hope this helps.