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

[Zeeshan Zakaria]

If I remember correctly you should do CDR(flavor)="cherry" and it should
work. I have added custom fields in my CDR table in the past and didn't need
triggers.

On 2010-03-29 3:40 AM, "Robert Price" <robert at proxims.com> wrote:

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

--
_____________________________________________________________________
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
              http://www.asterisk.org/hello

asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
  http://lists.digium.com/mailman/listinfo/asterisk-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-users/attachments/20100329/6de3347c/attachment.htm