[asterisk-users] How to add custom CDR fields to MySQL

[Robert Price]

Hello Alex,

I'm struggling with the same problem and, not wanting to modify the CDR 
backend, I just put in a workaround in the form of a MySQL trigger. 
I'll describe what I did in case it helps someone, though I'm very 
inexperienced at making compound procedures in MySQL.

In my extensions.conf, I can do something like this:

   exten => s,1,Set(CDR(userfield)="flavor=cherry|color=maroon"

The result is that my CDR(userfield) is a pipe-delimited list of 
key=value pairs.  In my MySQL 5.0.45 database, I have altered my cdr 
table with a column called 'flavor' and another called 'color'.  I have 
also created a trigger thus:

DELIMITER //

CREATE TRIGGER cdr_insert BEFORE INSERT ON cdr
FOR EACH ROW
   BEGIN
     SET @numidx = LENGTH(NEW.userfield)
          - LENGTH(REPLACE(NEW.userfield, '|', '')) + 1;
     SET @idx = 0;
     WHILE @idx + 1 <= @numidx DO
       SET @idx = @idx + 1;
       SET @param = SUBSTRING_INDEX(
                   SUBSTRING_INDEX(NEW.userfield, '|', @idx), '|', -1);
       SET @pos = LOCATE('=', @param);
       IF @pos > 0 THEN
         SET @key = SUBSTRING(@param, 1, @pos - 1);
         SET @value = SUBSTRING(@param, @pos + 1);

         CASE @key
           WHEN 'flavor' THEN SET NEW.flavor = @value;
           WHEN 'color' THEN SET NEW.color = @value;
         END CASE;

       END IF;
     END WHILE;
     SET NEW.userfield = '';
   END
//

DELIMITER ;

You can omit SET NEW.userfield = '' if you want to retain the userfield 
to prevent data loss.  Expand the CASE statement as necessary to 
enumerate all the fields you want to be able to specify via userfield, 
and make sure you've created the appropriate columns beforehand.  You 
can even specify things like 'dst' and 'dcontext', which you wouldn't 
normally be able to control.  The loop silently ignores elements of the 
pipe-delimited list that it does not recognize.  In particular, if the 
value of CDR(userfield) begins with a pipe, that doesn't create a problem.

So far, it appears to work.

Cheers,
Robert

 >     Hi all,
 >
 >     I've been trying to add a custom mysql field to my CDR's, but I
 >     must be doing something wrong.
 >
 >     I am using asterisk 1.4 and asterisk 1.6, in extensions.conf I
 >     add:
 >
 >     exten => h,1,Set(CDR(q931)=${HANGUPCAUSE})
 >
 >     This extension is executed, I can see it in the asterisk console.
 >
 >     I have added a new column in my MySQL database called q931.
 >     However,
 >     the new field does not show up in my database or in the Master.csv
 >     file.
 >
 >     Any help would be greatly appreciated.
 >     Regards,
 >
 >     Alex