Using sqlbox and triggers to keep track of Kannel message delivery reports

/*

This is a Mini HOW-TO in order to send sms messages via Kannel and sqlbox and keep track of status information.
Author: Rene Kluwen <rene.kluwen@chimit.nl>.

Many thanks to Alejandro to host this how-to on his Kannel blog. The text of this blog is completely enclosed in comment blocks, so you can just copy and paste the whole post in your favorite mysql editor and it will run the code (e.g. create the necessary tables, triggers and stored procedures).

We only need sqlbox and bearerbox. In this example, smsbox is not used. Also we do not use an http server for delivery reports.
Everything is done in Mysql.

To start, we assume that you have a working Kannel and Sqlbox installation. See the Kannel users manual on how to set this up.
Next, we need to collect the following information:

– <sender_id>: The number (or text) that the messages appear to be sent from.
– <smsc>: The smsc that the messages will be submitted to.
– <sqlbox_id>: The ‘smsbox-id’ of your sqlbox instance.

We use these values as defaults in our trigger. Substitute them with your own values, below.

*/

/* first, we drop the table if it exists already, in case we are updating */

DROP TABLE IF EXISTS snd;

/* The following table holds our messages. The messages that are inserted in this table will automatically be sent by Kannel (see trigger below) and the status fields of the message are automatically updated once DLR reports of the message come in (see second trigger). */

CREATE TABLE `snd` (
  `snd_id` bigint(20) NOT NULL auto_increment COMMENT 'Every message has a unique id',
  `snd_to` varchar(255) default NULL COMMENT 'The number that the message is sent to',
  `snd_txt` longtext COMMENT 'The body text of the message',
  `snd_sentat` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'The time that the message is inserted',
  `snd_success` datetime default NULL COMMENT 'The time that the message was delivered to the hand set',
  `snd_failure` datetime default NULL COMMENT 'The time that the message failed',
  `snd_submitted` datetime default NULL COMMENT 'The time that the message is submitted to the smsc',
  `snd_buffered` datetime default NULL COMMENT 'The time that the message is buffered at the smsc',
  `snd_rejected` datetime default NULL COMMENT 'The time that the message was rejected',
  `snd_intermediate` datetime default NULL COMMENT 'Time of intermediate status',
  `snd_last` int(11) NOT NULL default '0',
  PRIMARY KEY  (`snd_id`)
) DEFAULT CHARSET=latin1 COMMENT='This table holds the messages that are sent, including DLR values.';

/* first, we drop the trigger if it exists already, in case we are updating */

DROP TRIGGER IF EXISTS after_snd_insert;

/* The following trigger inserts appropriate values into the send_sms table. Kannel scans this table and sends out any message that is entered, subsequently deleting it. Our message keeps being held in the ‘snd’ table. We keep a reference to our record in `snd` by inserting `snd_id` in the dlr_url place holder. */

CREATE TRIGGER after_snd_insert
    AFTER INSERT ON snd
    FOR EACH ROW BEGIN
    /* Below we use some default values. Replace them with your own. Also replace send_sms in case you configured a different
       name for sql-insert-table in your sqlbox.conf */
    INSERT INTO send_sms (momt, sender, receiver, msgdata, smsc_id, sms_type, dlr_mask, dlr_url, boxc_id) 
    VALUES ('MT', '<sender_id>', NEW.snd_to, urlencode(NEW.snd_txt), '', 2, 31, NEW.snd_id, '<sqlbox_id>');
END;

/* first, we drop the trigger if it exists already, in case we are updating */

DROP TRIGGER IF EXISTS after_sent_sms_insert;

/* The second and final trigger updates the status fields of our ‘snd’ table.
Kannel inserts all dlr reports in the sent_sms table. We pick up the information from there and use it to update our original record in the snd table. Remember, we have the `snd_id` in the dlr_url field. */

CREATE TRIGGER after_sent_sms_insert
    /* again, replace sent_sms if you configured a different name for your sql-log-table. */
    AFTER INSERT ON sent_sms
    FOR EACH ROW BEGIN
        IF NEW.momt = 'DLR' THEN
            CASE NEW.dlr_mask
            WHEN  1 THEN UPDATE snd SET snd_success = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
            WHEN  2 THEN UPDATE snd SET snd_failure = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
            WHEN  4 THEN UPDATE snd SET snd_buffered = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
            WHEN  8 THEN UPDATE snd SET snd_submitted = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
            WHEN 16 THEN UPDATE snd SET snd_rejected = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
            WHEN 32 THEN UPDATE snd SET snd_intermediate = FROM_UNIXTIME(NEW.time) WHERE snd_id = NEW.dlr_url;
            ELSE UPDATE snd SET snd_last = NEW.dlr_mask WHERE snd_id = NEW.dlr_url;
            END CASE;
        END IF;
END

/* In the end, a listing of this helper function we need because sqlbox expects the msgdata field urlencoded. */

DROP FUNCTION IF EXISTS urlencode;
CREATE FUNCTION urlencode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
    DETERMINISTIC
    CONTAINS SQL
    BEGIN
        DECLARE c VARCHAR(4096) DEFAULT '';
        DECLARE pointer INT DEFAULT 1;
        DECLARE s2 VARCHAR(4096) DEFAULT '';

        IF ISNULL(s) THEN
            RETURN NULL;
        ELSE
            SET s2 = '';
            WHILE pointer <= length(s) DO
                SET c = MID(s,pointer,1);
                IF c = ' ' THEN
                    SET c = '+';
                ELSEIF NOT (ASCII(c) BETWEEN 48 AND 57 OR
                    ASCII(c) BETWEEN 65 AND 90 OR
                    ASCII(c) BETWEEN 97 AND 122) THEN
                    SET c = concat("%",LPAD(CONV(ASCII(c),10,16),2,0));
                END IF;
                SET s2 = CONCAT(s2,c);
                SET pointer = pointer + 1;
            END while;
        END IF;
    RETURN s2;
END;

/* end of helper function urlencode */

/* Usage example; substitute the given number (and text) with your own: */

INSERT INTO snd (snd_to, snd_txt) VALUES ('1234567890', 'My first example text to send...');

/* After inserting this message, Kannel will send it and update it’s status fields automatically. */