[asterisk-bugs] [JIRA] Created: (ASTERISK-20400) Let ODBC store multiple formats

[David Laban (JIRA)]

Let ODBC store multiple formats
-------------------------------

                 Key: ASTERISK-20400
                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-20400
             Project: Asterisk
          Issue Type: Improvement
      Security Level: None
          Components: Applications/app_voicemail/ODBC
            Reporter: David Laban


http://ofps.oreilly.com/titles/9780596517342/asterisk-DB.html notes that ODBC only supports one voicemail format, but it is not noted in the example configs or anywhere else, including the code (!).

The problem that I am *actually* trying to solve is that the default wav49[*] format is not supported by android, and I am not prepared to send raw PCM over a GSM connection. The only other common codec between asterisk's app_voicemail and android (ogg vorbis) is not supported by iPhone. The ovbious solution therefore is to store multiple formats in the database. 

[*] I assume that wav49 is the default for a good reason (winxp compatibility?)

I'm in the process of speccing up my changes now, and estimating how much work it will be. I will use this bug to track my progress. If anyone has any suggestions on the storage schema, please pipe up now. The voicemail_messages table has the following schema:

Table: voicemail_messages
Create Table: CREATE TABLE `voicemail_messages` (
  `dir` char(255) NOT NULL default '',
  `msgnum` int(4) NOT NULL default '0',
  `context` char(80) default NULL,
  `macrocontext` char(80) default NULL,
  `callerid` char(80) default NULL,
  `origtime` int(11) default NULL,
  `duration` int(11) default NULL,
  `recording` blob,
  `flag` char(30) default NULL,
  `category` char(30) default NULL,
  `mailboxuser` char(30) default NULL,
  `mailboxcontext` char(30) default NULL,
  PRIMARY KEY  (`dir`,`msgnum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I draw your attention to: PRIMARY KEY ('dir', 'msgnum'). 'dir' is typically something like:
/var/spool/asterisk/voicemail/default/1234/INBOX
and 'msgnum' is an integer.

This gives me the choice of:
a) Insert the parallel formats as something like:

'msgnum': ogg/var/spool/asterisk/voicemail/default/1234/INBOX

b) Create a parallel table with PRIMARY KEY (`dir`, `msgnum`, `format`), but keep the existing voicemail_messages as the primary table.

c) Break backwards compatibility by removing the `recording` blob from the table and inserting a reference field that you can look up in a table that looks something like:
(
Table: voicemail_messages_data
Create Table: CREATE TABLE `voicemail_messages` (
  `format` char(10) NOT NULL default '',
  `recording_id` int(4) NOT NULL default '0',
  `data` blob,
  `refcount` int(4) default '1',
PRIMARY KEY (`recording_id`, `format`)
)

d) Give up, and try a different tactic.

Note that any changes I make will involve keeping the various formats in sync using:
#ifdef ODBC_STORAGE
static char odbc_database[80];
static char odbc_table[80];
#define RETRIEVE(a,b,c,d) retrieve_file(a,b)
#define DISPOSE(a,b) remove_file(a,b)
#define STORE(a,b,c,d,e,f,g,h,i,j) store_file(a,b,c,d)
#define EXISTS(a,b,c,d) (message_exists(a,b))
#define RENAME(a,b,c,d,e,f,g,h) (rename_file(a,b,c,d,e,f))
#define COPY(a,b,c,d,e,f,g,h) (copy_file(a,b,c,d,e,f))
#define DELETE(a,b,c,d) (delete_file(a,b))

1. Which approach is best? c) would probably be less work, because you only need to update recording_id.
1.1. How much do we care about backwards compatibility of the DB schema?

2. How much can I optimise if I make RETRIEVE etc. parse the fmt argument? 
(A naive implementation of RETREIVE would cause a pull of all formats from the server to local storage, even if app_voicemail already knows which format is best for the channel.)

Comments from someone who has experience hacking app_voicemail would be greatly appreciated.

David.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira