RE: kannel dlr.c.patch to track dlr status via database triggers

Also for that, you can use sqlbox, instead of the dlr table.

-----Original Message-----
From: devel [mailto:[email protected]] On Behalf Of Anton Osennikov
Sent: zondag 5 oktober 2014 8:15
To: [email protected]
Subject: kannel dlr.c.patch to track dlr status via database triggers

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.