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

[Matt Jordan (JIRA)]

     [ https://issues.asterisk.org/jira/browse/ASTERISK-20400?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Matt Jordan closed ASTERISK-20400.
----------------------------------

    Resolution: Suspended

> 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