I have installed Asterisk 1.8.5, and I have enabled the mysql cdr database feature. I notice that there is no primary key created and I am also wondering if indexing should be enabled on a certain field in the table.
I used the following code to create it (Taken from wiki.asterisk.org/wiki/display/ … DR+Backend)
CREATE TABLE cdr (
calldate datetime NOT NULL default '0000-00-00 00:00:00',
clid varchar(80) NOT NULL default '',
src varchar(80) NOT NULL default '',
dst varchar(80) NOT NULL default '',
dcontext varchar(80) NOT NULL default '',
channel varchar(80) NOT NULL default '',
dstchannel varchar(80) NOT NULL default '',
lastapp varchar(80) NOT NULL default '',
lastdata varchar(80) NOT NULL default '',
duration int(11) NOT NULL default '0',
billsec int(11) NOT NULL default '0',
disposition varchar(45) NOT NULL default '',
amaflags int(11) NOT NULL default '0',
accountcode varchar(20) NOT NULL default '',
uniqueid varchar(32) NOT NULL default '',
userfield varchar(255) NOT NULL default ''
);
There’s indeed no “natural” primary key within the basic cdr-structure itself, as the column “uniquekey” may lead to false interpretations as it is non-unique in certain scenarios (e.g. transfers).
For working with a primary key You should add a column (e.g. simply ID) as an autoincrement column.
Indexing of the table for specific columns or combinations thereof depends on the statements You’d like to run when selecting data from the table. As asterisk only make INSERT’s to the table and never selects, updates or deletes something from it, there are no special requirements for asterisk itself to guarantee performance.
Thanks that is very helpful. I should maybe explain what I would like to do. I want to have calls come into the asterisk server, and have the call recorded to a wav file and a database entry created. I would then like to build a web application that can pull up the calls, and list the appropriate wav file to be downloaded, and provide general details about the call.
Would you be able to recommend the best way to relate a CDR database entry to a wav file? I am thinking there must be some way to do this by using some sort of unique identifier. Would this be the purpose of the “uniquekey”?
I would do it in another way as well as Your idea could work.
I would create the wav-files using e.g. UNIXTIME and probably any userdefined identifier (e.g. the channel name creating the file). This infomation I would store (probably as full path to the file) in CDR(userfield).
Thus direct access from a specific CDR-record to the wav-file (represented by it’s full path) should be easy to implement in any WEB-application.