MySQL CEL Table Schema

Home » Initial Configuration of Asterisk » MySQL CEL Table Schema

From the Asterisk Wiki, we have this description of CEL:
CEL, or Channel Event Logging, has been written with the hopes that it will help solve some of the problems that were difficult to address in CDR records. Some difficulties in CDR generation are the fact that the CDR record stores three events: the “Start” time, the “Answer” time, and the “End” time. Billing time is usually the difference between “Answer” and “End”, and total call duration was the difference in time from “Start” to “End”. The trouble with this direct and simple approach is the fact that calls can be transferred, put on hold, conferenced, forwarded, etc. In general, those doing billing applications in Asterisk find they have to do all sorts of very creative things to overcome the shortcomings of CDR records, often supplementing the CDR records with AGI scripts and manager event filters.

If you are using MySQL or MariaDB, this is the table schema for CEL ODBC backed. Please note the following:

1. Attributes like default charset and collate are set to UTF8, which supports lots of different characters.
2. Storage engine has explicitly been defined as ‘InnoDB‘.

CREATE TABLE `cel` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `eventtype` VARCHAR(30) COLLATE utf8_unicode_ci NOT NULL,
  `eventtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   ON UPDATE CURRENT_TIMESTAMP,
  `userdeftype` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `cid_name` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `cid_num` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `cid_ani` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `cid_rdnis` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `cid_dnid` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `exten` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `context` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `channame` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `appname` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `appdata` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `amaflags` INT(11) NOT NULL,
  `accountcode` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL,
  `peeraccount` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL,
  `uniqueid` VARCHAR(150) COLLATE utf8_unicode_ci NOT NULL,
  `linkedid` VARCHAR(150) COLLATE utf8_unicode_ci NOT NULL,
  `userfield` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `peer` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

Optimize Your Tables
If you are planning to extensively use the ARA, then you should seriously consider optimizing your tables if you expect your system to be scalable.
There are lots of resources online about database optimization, take some time to read about the topic.

Note: this article was originally posted by me at the Wiki, but never made it into public, so I’m posting it here, where I plan to keep it updated.