kannel dlr.c.patch to track dlr status via database triggers
Anton Osennikov <[email protected]>
| Newsgroups | gmane.comp.mobile.kannel.devel |
|---|---|
| Message-ID | <[email protected]> |
Hi! Thank you for great work on Kannel project. We wanted to track SMS DLR delivery via database table, for example to know when some SMS was actually delivered. This is almost possible, clear and simple via standard kannel "dlr" table PLUS database trigger to track "dlr" table changes. One thing wasn't possible. When kannel receives DLR, it immediately deletes a record from dlr table, giving no chance for database to track DLR reception. I propose a simple patch that forces kannel to first change dlr record status to "success" or "fail", before it's deletion. Also attached is MySQL tables and triggers definition to track dlr status. First table, dlr_wait, is almost the same as standard kannel "dlr" table, but have more checks, and have uniqie key. Second table, "dlr_track" is similar to "dlr_wait", but due to triggers, keeps last dlr status for every sent SMS along with times of dlr creation and update. Of course, this creates a need to remove old data from "dlr_track" time to time. It's up to sysadmin. -- Best regards, Anton Osennikov.
dlr.c.patch
(text/plain, 1.2 KB)
--- gw/dlr.c 2014-10-03 18:34:47.000000000 +0600
+++ /var/tmp/kannel-patch//dlr.c 2014-09-24 16:44:28.000000000 +0600
@@ -449,14 +449,19 @@
}
#undef O_SET
+
+ /* update dlr entry status if function defined, even if end status, to allow status tracking via database triggers */
+ if (handles != NULL && handles->dlr_update != NULL){
+ handles->dlr_update(smsc, ts, dst_min, typ);
+ }
/* check for end status and if so remove from storage */
if ((typ & DLR_BUFFERED) && ((dlr->mask & DLR_SUCCESS) || (dlr->mask & DLR_FAIL))) {
debug("dlr.dlr", 0, "DLR[%s]: DLR not destroyed, still waiting for other delivery report", dlr_type());
- /* update dlr entry status if function defined */
- if (handles != NULL && handles->dlr_update != NULL){
- handles->dlr_update(smsc, ts, dst_min, typ);
- }
+ ///* update dlr entry status if function defined */
+ //if (handles != NULL && handles->dlr_update != NULL){
+ // handles->dlr_update(smsc, ts, dst_min, typ);
+ //}
} else {
if (handles != NULL && handles->dlr_remove != NULL){
/* it's not good for internal storage, but better for all others */
dlr-schema.sql
(text/plain, 1.8 KB)
/* MySQL tables and triggers to track SMS DLR status changes [email protected] */ drop table if exists dlr_wait; /* DLR wait */ create table dlr_wait ( smsc varchar(40) not null, ts varchar(40) not null, status int(10) not null, source varchar(40), destination varchar(40) not null, service varchar(40), url varchar(255), mask int(10) not null, boxc varchar(40), unique (smsc,ts) ); drop table if exists dlr_track; /* DLR state track */ create table dlr_track ( id int(10) not null auto_increment, created datetime not null, updated datetime not null, smsc varchar(40) not null, ts varchar(40) not null, ts_num int(10) default 1 not null, -- "1" for the *last* SMS with this "ts" value, "id" for others status int(10) not null, source varchar(40), destination varchar(40) not null, service varchar(40), url varchar(255), mask int(10) not null, boxc varchar(40), primary key(id), unique (smsc,ts,ts_num) ) auto_increment = 1000000; drop trigger dlr_wait_track_ins; delimiter // /* */ create trigger dlr_wait_track_ins after insert on dlr_wait for each row begin insert into dlr_track (created,updated,smsc,ts,ts_num,status,source,destination,service,url,mask,boxc) values (now(),now(),new.smsc,new.ts,1,new.status,new.source,new.destination,new.service,new.url,new.mask,new.boxc); end; // delimiter ; /* */ drop trigger dlr_wait_track_upd; delimiter // create trigger dlr_wait_track_upd after update on dlr_wait for each row begin update dlr_track set status = new.status,updated = now() where smsc = new.smsc and ts = new.ts and ts_num = 1; end; // delimiter ; /* */ drop trigger dlr_wait_track_del; delimiter // create trigger dlr_wait_track_del after delete on dlr_wait for each row begin update dlr_track set ts_num = id where smsc = old.smsc and ts = old.ts and ts_num = 1; end; // delimiter ;