Proposal: a more powerful SQL DLR storage
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.
Re: Proposal: a more powerful SQL DLR storage
Hi, Why don't you just call your custom script through dlr-url? The CGI script (in any language you like) can update any custom database you are using in your setup. Regards, C. On 07/11/2014 09:34, Vincenzo Romano wrote: 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.
Re: Proposal: a more powerful SQL DLR storage
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 vincenzo.rom...@notorand.it: 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-330 Fax: +41-61-331 Mobile: +41-79-2457333 Address: Clarastrasse 3, 4058 Basel, Switzerland E-Mail: andr...@fink.org www.datacell.com, www.backbone.is, www.finkconsulting.com www.fink.org --- Jabber/XMPP: andr...@fink.org ICQ: 8239353 Skype: andreasfink
Re: Proposal: a more powerful SQL DLR storage
Hi. I am not allowed (in my case) to install another web server (either local or not) or change the existing configuration. Moreover i saw the information was just there, in the dlr.c file, a few lines of code away from my need. dlr-url is powerful, indeed, but adds complexity to something that's already there: the SQL DB. 2014-11-07 10:47 GMT+01:00 Cezary Siwek cza...@thebestisp.co.uk: Hi, Why don't you just call your custom script through dlr-url? The CGI script (in any language you like) can update any custom database you are using in your setup. Regards, C. On 07/11/2014 09:34, Vincenzo Romano wrote: 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.
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 af...@list.fink.org: 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 vincenzo.rom...@notorand.it: 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-330 Fax: +41-61-331 Mobile: +41-79-2457333 Address: Clarastrasse 3, 4058 Basel, Switzerland E-Mail: andr...@fink.org www.datacell.com, www.backbone.is, www.finkconsulting.com www.fink.org --- Jabber/XMPP: andr...@fink.org ICQ: 8239353 Skype: andreasfink
RE: Proposal: a more powerful SQL DLR storage
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:devel-boun...@kannel.org] 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 af...@list.fink.org: 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 vincenzo.rom...@notorand.it: 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-330 Fax: +41-61-331 Mobile: +41-79-2457333 Address: Clarastrasse 3, 4058 Basel, Switzerland E-Mail: andr...@fink.org www.datacell.com, www.backbone.is, www.finkconsulting.com www.fink.org --- Jabber/XMPP: andr...@fink.org 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.
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 eduardo.sp...@gemalto.com: 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:devel-boun...@kannel.org] 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 af...@list.fink.org: 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 vincenzo.rom...@notorand.it: 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-330 Fax: +41-61-331 Mobile: +41-79-2457333 Address: Clarastrasse 3, 4058 Basel, Switzerland E-Mail: andr...@fink.org www.datacell.com, www.backbone.is, www.finkconsulting.com www.fink.org --- Jabber/XMPP: andr...@fink.org 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
