Re: Proposal: a more powerful SQL DLR storage

Hi,
it looks like I've done it the wrong way to solve my problem.

If I'll find enough time I'll investigate Andreas' suggestion and will
submit the changes to him...
But I need to first finish my project. :-)

2014-11-07 17:28 GMT+01:00 Spina Eduardo <[email protected]>:
> Hi All,
>
> This implementation would be important for me too. I tried sometime ago to setup the web server for similar purposes, but, running 1500+ SMS/s, the performance dropped significantly with the opening/closing of sockets (at least as it was configured, kannel was using HTTP 1.0 and creating a lot of connections with the web server, maybe there is a way to setup HTTP 1.1 here?).
>
> Best Regards,
> ---
> Eduardo Martins Spina
>
> -----Original Message-----
> From: devel [mailto:[email protected]] On Behalf Of Vincenzo Romano
> Sent: Friday, November 07, 2014 8:49 AM
> To: Andreas Fink
> Cc: devel Devel
> Subject: Re: Proposal: a more powerful SQL DLR storage
>
> Hi.
> It's that "temporaryness" that's not a mandatory feature for kannel to work.
> As long as your queries are fast and effective, the application can provide a(nother) powerful integration tool.
>
> The SMS status information is already there, within system data structures.
> My proposal is to keep it structured and stored into the DB.
> That'd be "more usefulness from the DB store".
> What you suggest is to undo later the work done to flatten all the details into one or more log lines.
> That'd be "more usefulness from the log store".
>
> My proposal leads to a more effective use of the DB, as there wouldn't be any UPDATE and DELETE (thus fewer locks, no tablespace fragmentation, more compact indices). And, reasonably, higher application throughput.
>
>
> 2014-11-07 10:49 GMT+01:00 Andreas Fink <[email protected]>:
>> the purpose of the persistent storage is for temporary usage as DLR's
>> can refer to SMS which have been sent a couple of days ago.
>> the DLR storage you want is what delivery reports are meant for and
>> those are transmitted to the user requesting it In addition to that
>> the access logs already contains this data.
>> It would probably make more sense to offer a access log into SQL
>> driver than to abuse the internal state cache mechanism.
>>
>> Am 07.11.2014 um 10:34 schrieb Vincenzo Romano
>> <[email protected]>:
>>
>> Introduction
>> Kannel has the ability to store the SMS DLRs also in SQL tables.
>> The main aim for this is to provide for storage persistence against
>> system crashes.
>> For this reason a DB table is configured where kannel will insert
>> details for submitted SMSs.
>> Later, upon reaching the final status, those data will be searched and
>> deleted.
>>
>> Motivation
>> This feature can be made more powerful with little changes to the system.
>> Basically it's all about replacing the deletion with insertion at
>> every single status change.
>> This would create a "SMS status history" from which the user can
>> extract an amount of useful information that's otherwise lost.
>>
>> Implementation
>> On the software side of the feature, the code in gw/dlr.c needs to be
>> changed in order to replace the calls to method dlr_remove with
>> dlr_insert (or even dlr_update ).
>> The underlying table needs to be supplemented at least with a "real"
>> timestamp column (automatically calculated at every insertion) in
>> order to retrieve either the latest status update or the initial
>> details thanks to the ORDER BY predicate in either ASCending or
>> DESCending direction.
>> The actual queries used in dlr_*.c should also be reviewed in order to
>> reflect thes changes, even if that can be avoided with intervention in
>> the SQL with VIEWs and TRIGGERs.
>>
>> Note
>> I recently started a thread on this topic in the users mailing list.
>> It can be viewed here:
>> http://www.kannel.org/pipermail/users/2014-November/021632.html
>> And I have no idea on how the development organisation is done in Kannel.
>>
>>
>> Thanks for the patience. Any feedback will be welcomed.
>>
>>
>>
>>
>>
>> Andreas Fink
>>
>> CEO DataCell ehf
>> CEO Backbone ehf
>>
>> ---------------------------------------------------------------
>> Tel: +41-61-6666330 Fax: +41-61-6666331  Mobile: +41-79-2457333
>> Address: Clarastrasse 3, 4058 Basel, Switzerland
>> E-Mail:  [email protected]
>> www.datacell.com, www.backbone.is, www.finkconsulting.com www.fink.org
>> ---------------------------------------------------------------
>> Jabber/XMPP: [email protected]
>> ICQ: 8239353 Skype: andreasfink
>>
>>
>>
>>
>
> ________________________________
>  This message and any attachments are intended solely for the addressees and may contain confidential information. Any unauthorized use or disclosure, either whole or partial, is prohibited.
> E-mails are susceptible to alteration. Our company shall not be liable for the message if altered, changed or falsified. If you are not the intended recipient of this message, please delete it and notify the sender.
> Although all reasonable efforts have been made to keep this transmission free from viruses, the sender will not be liable for damages caused by a transmitted virus.