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

Hi!

It seems that using sent_sms is better.

It allows to catch a situation when messages cannot be sent to SMSC.

This is due to fact that the record in "dlr" table is created only after 
message is sent to SMSC, but the record in "sent_sms" table is created 
anyway.

Also, sent_sms contains validity period. Can detect outdated messages.

What is the good way to match records referring the same original SMS in 
sent_sms table?

We now have in "dlr_url" field something like

 
http://localhost/sms/dlr.php?smsc=%i&from=%P&to=%p&dlr-mask=%d&smsID=1412830346425084685

where 1412830346425084685 is unique id, generated at send time by 
message sending program.

Can use dlr_url as a key. Drawbacks are: it's a bit long, and SMS 
tracking system breaks if dlr_url isn't filled with unique value for 
some reason.

--
Best regards, Anton Osennikov.

07.10.2014 23:48, Rene Kluwen пишет:
> 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.
>