RE: Follow-up : Proposal for Real-time Routing in opensmppbox and bearerbox
Hi, I have been dealing with shifting business priorities. Ironically, I had just returned to work on the code change outlined in the proposal some more the past day or two. And now my priorities have been shifted yet again. The reason that I wanted to reuse the infrastructure set forth by the DLR code is that it already establishes all the connection code necessary for connecting with the database and rather than duplicate that entire structure, it is easier (and generally a better idea) to reuse it. We know that the current code can successfully connect and query with all the different flavors of database. And reusing the code keeps the database-related code to a single area of the bearerbox/smsbox/opensmppbox codebase. The idea was to add new functionality to the code with as little disruption to the code and currently underlying design as possible. I do not know whether other developers would approach it differently... I will continue to work on the code as I am able, but I cannot promise a particular delivery date. Regards, Kelvin R. Porter -Original Message- From: devel [mailto:devel-boun...@kannel.org] On Behalf Of Mick Burns Sent: Tuesday, September 24, 2013 7:25 PM To: de...@kannel.org Subject: Follow-up : Proposal for Real-time Routing in opensmppbox and bearerbox Dear Kannel friends, I would like to revive the nice thread (*) started by Kelvin Porter back in July in respect to adding new routing capabilities to bearerbox and opensmppbox. This functionality is of great importance for me and I would like to actively help in testing it out. I am x-posting this on the users and dev lists like the original thread. Now, I don't quite understand why it should hook with dlr code, please note that I am not a proficient coder at all. Perhaps Kelvin is just pointing out existing routines that can be used as an example or starting point to implement the concept. In short, I would like to avoid having to use (for fined-grain control) smsbox-route statements for each of the customers provisioned DNs. Instead, for each message received by an upstream SMPP, bearerbox does a lookup into a RDBMS (mysql/pgsql) for each message and the reply to the query contains the smsbox-id to route the message to. Otherwise, currently all messages goes to a single smsbox which delivers the message to an HTTP-based service via the sms-service stanzas. Example of the smsbox-route statements to avoid with this new feature: group = smsbox-route smsbox-id = osmppbox1 shortcode = +18885551212;+18665551212; I quickly put together a block diagram of what I am thinking: http://pastebin.com/raw.php?i=BSYC88UZ I really wonder how other carriers/aggregators implements carrier-grade routing between bearerbox's upstream smpp connections and downstream towards either smppbox(es) or smsboxes depending on where the message should normally route to. It also adds agility to the routing on a global basis and in a dynamic manner (no need to stop/start services). Thank you Mick B. (*) Link to original thread from Mr. Porter: http://www.kannel.org/pipermail/users/2013-July/019991.html
Follow-up : Proposal for Real-time Routing in opensmppbox and bearerbox
Dear Kannel friends, I would like to revive the nice thread (*) started by Kelvin Porter back in July in respect to adding new routing capabilities to bearerbox and opensmppbox. This functionality is of great importance for me and I would like to actively help in testing it out. I am x-posting this on the users and dev lists like the original thread. Now, I don't quite understand why it should hook with dlr code, please note that I am not a proficient coder at all. Perhaps Kelvin is just pointing out existing routines that can be used as an example or starting point to implement the concept. In short, I would like to avoid having to use (for fined-grain control) smsbox-route statements for each of the customers provisioned DNs. Instead, for each message received by an upstream SMPP, bearerbox does a lookup into a RDBMS (mysql/pgsql) for each message and the reply to the query contains the smsbox-id to route the message to. Otherwise, currently all messages goes to a single smsbox which delivers the message to an HTTP-based service via the sms-service stanzas. Example of the smsbox-route statements to avoid with this new feature: group = smsbox-route smsbox-id = osmppbox1 shortcode = +18885551212;+18665551212; I quickly put together a block diagram of what I am thinking: http://pastebin.com/raw.php?i=BSYC88UZ I really wonder how other carriers/aggregators implements carrier-grade routing between bearerbox's upstream smpp connections and downstream towards either smppbox(es) or smsboxes depending on where the message should normally route to. It also adds agility to the routing on a global basis and in a dynamic manner (no need to stop/start services). Thank you Mick B. (*) Link to original thread from Mr. Porter: http://www.kannel.org/pipermail/users/2013-July/019991.html
RE: Proposal for Real-time Routing in opensmppbox and bearerbox
Hi, Using HTTP RPCs to access the functionality would actually be more work in implementation on both the client (kannel) side and the server (application) side. For our application, the routing criteria (for say a number) will change once and then remain constant for quite some time. This is perfect for a database (possibly including caching). I think that the billing side is addressed brilliantly with the sqlbox functionality. At least, it works perfectly for our applications. Regards, Kelvin R. Porter From: Rinor Hoxha [mailto:rinorho...@gmail.com] Sent: Tuesday, July 23, 2013 5:33 PM To: Porter, Kelvin Cc: de...@kannel.org; users@kannel.org Subject: Re: Proposal for Real-time Routing in opensmppbox and bearerbox How about using (implementing or adapting) HTTP-based callbacks. So you could implement routing, billing or whatever you may need based on callback responses. Br, Rinor On 07/22/2013 10:45 PM, Porter, Kelvin wrote: Hi, I have included a proposed enhancement for routing message based on database contents below. I am looking for feedback. Please share your thoughts as to whether this would be of interest. Thank you. Regards, Kelvin R. Porter I would like to propose an enhancement to the source code to kannel bearerbox and opensmppbox. The enhancement would supercede the existing configuration groups: smsbox-route in the bearerbox, and smsc-route in the oppensmppbox. The enhancement dynamically consults a database table/view to determine the mapping based on the sender, receiver and original connection (smsc or box). The purpose of this enhancement is to allow dynamically changing the message routing without requiring a change to the configuration file(s) and then the subsequent restart of the opensmppbox and/or bearerbox. The enhancement works by taking advantage of the database access functionality currently used for storing DLRs in a database. The DLR code currently supports the following databases: mysql, oracle, pgsql, mssql, and sdb (where SDB is the simplified DB interface). The internal option refers to storing DLRs in memory and would not apply to this enhancement. I can write the code for these databases, but may require some assistance from the kannel community in testing them. The enhancement adds two new table parameters to the group dlr-db: table-smsc specifies the name of the table/view mapping a message to a smsc-id, and table-box specifies the name of the table/view mapping a message to a (sms)box. The enhancement is selectively enabled by configuring the parameters above. The following existing fields parameters of the group dlr-db are re-used: field-source specifies the name of the field that matches the sender of a message, and field-destination specifies the name of the field that matches the receiver of a message, and field-smsc specifies the name of the field that matches the smsc, and field-boxc-id specifies the name of the field that matches the name of the box. The following are two routines added to the dlr.h interface: /** Given message, then map to smsc id. */ Octstr * map_to_smsc(Msg *msg); /** Given message, then map to boxc-id. */ Ocstr *map_to_box(Msg *msg); If these methods return a (non-NULL) result, then they supercede the configuration-based routing. If the results are NULL, then the default configured routing can be applied. The queries for this the match look something like the following: 1. To determine the smsc... SELECT field-smsc FROM table-smsc WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-boxc-id = NULL) OR (field-boxc-id = ?)) 2. To determine the (sms)box... SELECT field-boxc-id FROM table-box WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-smsc = NULL) OR (field-smsc = ?)) The queries are written in this fashion where a column with a NULL value will always match (in essence a row with a NULL in a column will always match). That way a subset of the column values (e.g., receiver only or sender and boxc-id ) can be used to match as a criteria. New debug level logs would be added to indicate the input to the queries and the result. In addition, certain configuration panics might be added like attempting to configure table-smsc or table-box in conjunction with the internal database option. This enhancement would enable the directing of messages on the fly. I think that the approach would serve as a good foundation in case additional routing criteria were desired (i.e., language/encoding based routing).
Re: Proposal for Real-time Routing in opensmppbox and bearerbox
Hi. 2013/7/24 Porter, Kelvin kelvin.por...@h3net.com Hi, ** ** Using HTTP RPCs to access the functionality would actually be more work in implementation on both the client (kannel) side and the server (application) side. For our application, the routing criteria (for say a number) will change once and then remain constant for quite some time. This is perfect for a database (possibly including caching). About caching: you can implement simple one-time load from the database on the start and reload configuration on some trigger, maybe add URL to do so, let's say http://localhost:13000/reload-routes. so if you change rules in the database you hit the url and you're done! ** ** I think that the billing side is addressed brilliantly with the sqlbox functionality. At least, it works perfectly for our applications. Indeed. We're using patched sqlbox with bearerbox only too, deleting DLRs and updating MT records with all needed info. ** ** Regards, ** ** Kelvin R. Porter ** ** *From:* Rinor Hoxha [mailto:rinorho...@gmail.com] *Sent:* Tuesday, July 23, 2013 5:33 PM *To:* Porter, Kelvin *Cc:* de...@kannel.org; users@kannel.org *Subject:* Re: Proposal for Real-time Routing in opensmppbox and bearerbox ** ** How about using (implementing or adapting) HTTP-based callbacks. So you could implement routing, billing or whatever you may need based on callback responses. Br, Rinor I think it would be additional overhead in this case, I'm more into database / memcached idea. Same thing with calling smsbox HTTP url each time sms comes in instead of just updating info in the db directly. Very often there is separate server for sending purposes, so its not really convinient to have multiple HTTP-callbacks. Also if you're doing HTTP callback on WAN network I consider it being insecure, it should be HTTPS at least, no problem tho if its done over VPN / trusted LAN network. On 07/22/2013 10:45 PM, Porter, Kelvin wrote: *Hi,* * * *I have included a proposed enhancement for routing message based on database contents below.* * * *I am looking for feedback.* * * *Please share your thoughts as to whether this would be of interest.* * * *Thank you.* * * *Regards,* * * *Kelvin R. Porter* * * I would like to propose an enhancement to the source code to kannel bearerbox and opensmppbox. The enhancement would supercede the existing configuration groups: “smsbox-route” in the bearerbox, and “smsc-route” in the oppensmppbox. The enhancement dynamically consults a database table/view to determine the mapping based on the sender, receiver and original connection (smsc or box). The purpose of this enhancement is to allow dynamically changing the message routing without requiring a change to the configuration file(s) and then the subsequent restart of the opensmppbox and/or bearerbox. The enhancement works by taking advantage of the database access functionality currently used for storing DLRs in a database. The DLR code currently supports the following databases: “mysql”, “oracle”, “pgsql”, “mssql”, and “sdb” (where SDB is the simplified DB interface). The “internal” option refers to storing DLRs in memory and would not apply to this enhancement. I can write the code for these databases, but may require some assistance from the kannel community in testing them. The enhancement adds two new table parameters to the group “dlr-db”: “table-smsc” specifies the name of the table/view mapping a message to a smsc-id, and “table-box” specifies the name of the table/view mapping a message to a (sms)box. The enhancement is selectively enabled by configuring the parameters above. The following existing fields parameters of the group “dlr-db” are re-used: “field-source” specifies the name of the field that matches the sender of a message, and “field-destination” specifies the name of the field that matches the receiver of a message, and “field-smsc” specifies the name of the field that matches the smsc, and*** * “field-boxc-id” specifies the name of the field that matches the name of the box. The following are two routines added to the dlr.h interface: /** Given message, then map to smsc id. */ Octstr * map_to_smsc(Msg *msg); /** Given message, then map to boxc-id. */ Ocstr *map_to_box(Msg *msg); If these methods return a (non-NULL) result, then they supercede the configuration-based routing. If the results are NULL, then the default configured routing can be applied. The queries for this the match look something like the following: **1. **To determine the smsc… “SELECT field-smsc FROM
Re: Proposal for Real-time Routing in opensmppbox and bearerbox
How about using (implementing or adapting) HTTP-based callbacks. So you could implement routing, billing or whatever you may need based on callback responses. Br, Rinor On 07/22/2013 10:45 PM, Porter, Kelvin wrote: *Hi,* * * *I have included a proposed enhancement for routing message based on database contents below.* * * *I am looking for feedback.* * * *Please share your thoughts as to whether this would be of interest.* * * *Thank you.* * * *Regards,* * * *Kelvin R. Porter* * * I would like to propose an enhancement to the source code to kannel bearerbox and opensmppbox. The enhancement would supercede the existing configuration groups: smsbox-route in the bearerbox, and smsc-route in the oppensmppbox. The enhancement dynamically consults a database table/view to determine the mapping based on the sender, receiver and original connection (smsc or box). The purpose of this enhancement is to allow dynamically changing the message routing without requiring a change to the configuration file(s) and then the subsequent restart of the opensmppbox and/or bearerbox. The enhancement works by taking advantage of the database access functionality currently used for storing DLRs in a database. The DLR code currently supports the following databases: mysql, oracle, pgsql, mssql, and sdb (where SDB is the simplified DB interface). The internal option refers to storing DLRs in memory and would not apply to this enhancement. I can write the code for these databases, but may require some assistance from the kannel community in testing them. The enhancement adds two new table parameters to the group dlr-db: table-smsc specifies the name of the table/view mapping a message to a smsc-id, and table-box specifies the name of the table/view mapping a message to a (sms)box. The enhancement is selectively enabled by configuring the parameters above. The following existing fields parameters of the group dlr-db are re-used: field-source specifies the name of the field that matches the sender of a message, and field-destination specifies the name of the field that matches the receiver of a message, and field-smsc specifies the name of the field that matches the smsc, and field-boxc-id specifies the name of the field that matches the name of the box. The following are two routines added to the dlr.h interface: /** Given message, then map to smsc id. */ Octstr * map_to_smsc(Msg *msg); /** Given message, then map to boxc-id. */ Ocstr *map_to_box(Msg *msg); If these methods return a (non-NULL) result, then they supercede the configuration-based routing. If the results are NULL, then the default configured routing can be applied. The queries for this the match look something like the following: 1. To determine the smsc... SELECT field-smsc FROM table-smsc WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-boxc-id = NULL) OR (field-boxc-id = ?)) 2. To determine the (sms)box... SELECT field-boxc-id FROM table-box WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-smsc = NULL) OR (field-smsc = ?)) The queries are written in this fashion where a column with a NULL value will always match (in essence a row with a NULL in a column will always match). That way a subset of the column values (e.g., receiver only or sender and boxc-id ) can be used to match as a criteria. New debug level logs would be added to indicate the input to the queries and the result. In addition, certain configuration panics might be added like attempting to configure table-smsc or table-box in conjunction with the internal database option. This enhancement would enable the directing of messages on the fly. I think that the approach would serve as a good foundation in case additional routing criteria were desired (i.e., language/encoding based routing).
Proposal for Real-time Routing in opensmppbox and bearerbox
Hi, I have included a proposed enhancement for routing message based on database contents below. I am looking for feedback. Please share your thoughts as to whether this would be of interest. Thank you. Regards, Kelvin R. Porter I would like to propose an enhancement to the source code to kannel bearerbox and opensmppbox. The enhancement would supercede the existing configuration groups: smsbox-route in the bearerbox, and smsc-route in the oppensmppbox. The enhancement dynamically consults a database table/view to determine the mapping based on the sender, receiver and original connection (smsc or box). The purpose of this enhancement is to allow dynamically changing the message routing without requiring a change to the configuration file(s) and then the subsequent restart of the opensmppbox and/or bearerbox. The enhancement works by taking advantage of the database access functionality currently used for storing DLRs in a database. The DLR code currently supports the following databases: mysql, oracle, pgsql, mssql, and sdb (where SDB is the simplified DB interface). The internal option refers to storing DLRs in memory and would not apply to this enhancement. I can write the code for these databases, but may require some assistance from the kannel community in testing them. The enhancement adds two new table parameters to the group dlr-db: table-smsc specifies the name of the table/view mapping a message to a smsc-id, and table-box specifies the name of the table/view mapping a message to a (sms)box. The enhancement is selectively enabled by configuring the parameters above. The following existing fields parameters of the group dlr-db are re-used: field-source specifies the name of the field that matches the sender of a message, and field-destination specifies the name of the field that matches the receiver of a message, and field-smsc specifies the name of the field that matches the smsc, and field-boxc-id specifies the name of the field that matches the name of the box. The following are two routines added to the dlr.h interface: /** Given message, then map to smsc id. */ Octstr * map_to_smsc(Msg *msg); /** Given message, then map to boxc-id. */ Ocstr *map_to_box(Msg *msg); If these methods return a (non-NULL) result, then they supercede the configuration-based routing. If the results are NULL, then the default configured routing can be applied. The queries for this the match look something like the following: 1. To determine the smsc... SELECT field-smsc FROM table-smsc WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-boxc-id = NULL) OR (field-boxc-id = ?)) 2. To determine the (sms)box... SELECT field-boxc-id FROM table-box WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-smsc = NULL) OR (field-smsc = ?)) The queries are written in this fashion where a column with a NULL value will always match (in essence a row with a NULL in a column will always match). That way a subset of the column values (e.g., receiver only or sender and boxc-id ) can be used to match as a criteria. New debug level logs would be added to indicate the input to the queries and the result. In addition, certain configuration panics might be added like attempting to configure table-smsc or table-box in conjunction with the internal database option. This enhancement would enable the directing of messages on the fly. I think that the approach would serve as a good foundation in case additional routing criteria were desired (i.e., language/encoding based routing).
Re: Proposal for Real-time Routing in opensmppbox and bearerbox
Interesting idea, but what about load on the database? Will it be cached or it's gonna get results from the database everytime SMS arrives? 2013/7/23 Porter, Kelvin kelvin.por...@h3net.com *Hi,* * * *I have included a proposed enhancement for routing message based on database contents below.* * * *I am looking for feedback.* * * *Please share your thoughts as to whether this would be of interest.* * * *Thank you.* * * *Regards,* * * *Kelvin R. Porter* * * ** ** I would like to propose an enhancement to the source code to kannel bearerbox and opensmppbox. The enhancement would supercede the existing configuration groups: “smsbox-route” in the bearerbox, and “smsc-route” in the oppensmppbox. The enhancement dynamically consults a database table/view to determine the mapping based on the sender, receiver and original connection (smsc or box). The purpose of this enhancement is to allow dynamically changing the message routing without requiring a change to the configuration file(s) and then the subsequent restart of the opensmppbox and/or bearerbox. The enhancement works by taking advantage of the database access functionality currently used for storing DLRs in a database. The DLR code currently supports the following databases: “mysql”, “oracle”, “pgsql”, “mssql”, and “sdb” (where SDB is the simplified DB interface). The “internal” option refers to storing DLRs in memory and would not apply to this enhancement. I can write the code for these databases, but may require some assistance from the kannel community in testing them. The enhancement adds two new table parameters to the group “dlr-db”: “table-smsc” specifies the name of the table/view mapping a message to a smsc-id, and “table-box” specifies the name of the table/view mapping a message to a (sms)box. The enhancement is selectively enabled by configuring the parameters above. The following existing fields parameters of the group “dlr-db” are re-used: “field-source” specifies the name of the field that matches the sender of a message, and “field-destination” specifies the name of the field that matches the receiver of a message, and “field-smsc” specifies the name of the field that matches the smsc, and*** * “field-boxc-id” specifies the name of the field that matches the name of the box. The following are two routines added to the dlr.h interface: /** Given message, then map to smsc id. */ Octstr * map_to_smsc(Msg *msg); /** Given message, then map to boxc-id. */ Ocstr *map_to_box(Msg *msg); If these methods return a (non-NULL) result, then they supercede the configuration-based routing. If the results are NULL, then the default configured routing can be applied. The queries for this the match look something like the following: **1. **To determine the smsc… “SELECT field-smsc FROM table-smsc WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-boxc-id = NULL) OR (field-boxc-id = ?))” **2. **To determine the (sms)box… “SELECT field-boxc-id FROM table-box WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-smsc = NULL) OR (field-smsc = ?))” The queries are written in this fashion where a column with a NULL value will always match (in essence a row with a NULL in a column will always match). That way a subset of the column values (e.g., receiver only or sender and boxc-id ) can be used to match as a criteria. New debug level logs would be added to indicate the input to the queries and the result. In addition, certain configuration panics might be added like attempting to configure “table-smsc” or “table-box” in conjunction with the “internal” database option. This enhancement would enable the directing of messages on the fly. I think that the approach would serve as a good foundation in case additional routing criteria were desired (i.e., language/encoding based routing). ** **
RE: Proposal for Real-time Routing in opensmppbox and bearerbox
Hi, In the straightforward implementation, the load on the database would be 1 query per message sent (MT) and 1 query per message received (MO) (excluding situations where loopback occurred). A caching component could be added separately afterwards. The trade-offs would involve added code complexity, and the increasing latency in having database changes affect message routing. Different applications (e.g., A2P vs. P2P) might or might not see much benefit depending on how long the caching interval is, the rate of back-and-forth conversation, and the size of the cache. I am not familiar with all aspects of the code base, is there a caching mechanism that can be reused? I think that if caching is added that it should be configurable as to size and duration. Regards, Kelvin R. Porter From: spameden [mailto:spame...@gmail.com] Sent: Monday, July 22, 2013 3:55 PM To: Porter, Kelvin Cc: de...@kannel.orgmailto:de...@kannel.org; users@kannel.orgmailto:users@kannel.org Subject: Re: Proposal for Real-time Routing in opensmppbox and bearerbox Interesting idea, but what about load on the database? Will it be cached or it's gonna get results from the database everytime SMS arrives? 2013/7/23 Porter, Kelvin kelvin.por...@h3net.commailto:kelvin.por...@h3net.com Hi, I have included a proposed enhancement for routing message based on database contents below. I am looking for feedback. Please share your thoughts as to whether this would be of interest. Thank you. Regards, Kelvin R. Porter I would like to propose an enhancement to the source code to kannel bearerbox and opensmppbox. The enhancement would supercede the existing configuration groups: smsbox-route in the bearerbox, and smsc-route in the oppensmppbox. The enhancement dynamically consults a database table/view to determine the mapping based on the sender, receiver and original connection (smsc or box). The purpose of this enhancement is to allow dynamically changing the message routing without requiring a change to the configuration file(s) and then the subsequent restart of the opensmppbox and/or bearerbox. The enhancement works by taking advantage of the database access functionality currently used for storing DLRs in a database. The DLR code currently supports the following databases: mysql, oracle, pgsql, mssql, and sdb (where SDB is the simplified DB interface). The internal option refers to storing DLRs in memory and would not apply to this enhancement. I can write the code for these databases, but may require some assistance from the kannel community in testing them. The enhancement adds two new table parameters to the group dlr-db: table-smsc specifies the name of the table/view mapping a message to a smsc-id, and table-box specifies the name of the table/view mapping a message to a (sms)box. The enhancement is selectively enabled by configuring the parameters above. The following existing fields parameters of the group dlr-db are re-used: field-source specifies the name of the field that matches the sender of a message, and field-destination specifies the name of the field that matches the receiver of a message, and field-smsc specifies the name of the field that matches the smsc, and field-boxc-id specifies the name of the field that matches the name of the box. The following are two routines added to the dlr.h interface: /** Given message, then map to smsc id. */ Octstr * map_to_smsc(Msg *msg); /** Given message, then map to boxc-id. */ Ocstr *map_to_box(Msg *msg); If these methods return a (non-NULL) result, then they supercede the configuration-based routing. If the results are NULL, then the default configured routing can be applied. The queries for this the match look something like the following: 1. To determine the smsc... SELECT field-smsc FROM table-smsc WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-boxc-id = NULL) OR (field-boxc-id = ?)) 2. To determine the (sms)box... SELECT field-boxc-id FROM table-box WHERE ((field-sender = NULL) OR (field-sender = ?)) AND ((field-destination = NULL) OR (field-destination = ?)) AND ((field-smsc = NULL) OR (field-smsc = ?)) The queries are written in this fashion where a column with a NULL value will always match (in essence a row with a NULL in a column will always match). That way a subset of the column values (e.g., receiver only or sender and boxc-id ) can be used to match as a criteria. New debug level logs would be added to indicate the input to the queries and the result. In addition, certain configuration panics might be added like attempting to configure table-smsc or table-box in conjunction with the internal database option. This enhancement would enable the directing of messages on the fly. I think that the approach would serve as a good foundation in case additional routing criteria were desired (i.e.,
Re: Proposal for Real-time Routing in opensmppbox and bearerbox
2013/7/23 Porter, Kelvin kelvin.por...@h3net.com Hi, ** ** In the straightforward implementation, the load on the database would be 1 query per message sent (MT) and 1 query per message received (MO) (excluding situations where loopback occurred). ** ** A caching component could be added separately afterwards. The trade-offs would involve added code complexity, and the increasing latency in having database changes affect message routing. ** ** Different applications (e.g., A2P vs. P2P) might or might not see much benefit depending on how long the caching interval is, the rate of back-and-forth conversation, and the size of the cache. ** ** I am not familiar with all aspects of the code base, is there a caching mechanism that can be reused? I think there is no caching mechanism at all and grace reload is not implemented yet. Stipe was posting a patch earlier giving kannel the ability to gracefully reload it's configuration, but I think it's still bugy and needs to be cleaned up before accepted in the codebase. The problem getting configuration from SQL is 1 more query per MT, if you have busy site with huge traffic (100-200 MT /sec) it could affect on your database, so caching definitely needed somehow. Alternatively you can look into storing configuration into memcached. The route mechanism you're wanting can be implemented without database as well using add-smsc / remove-smsc commands for example. I'm using myself this 2 hooks for failsafe configuration (when 1 of the smsc goes down, I change with sed 'allowed-smsc-id' directive and re-add via remove-smsc, add-smsc commands specific smsc - everything done automatically via monit with additional scripts). I even heard someone implemented whole kannel configuration in the database, if you could make it - it would be awesome, because it would be MUCH easier to code web interface for kannel to configure it more easily. ** ** I think that if caching is added that it should be configurable as to size and duration. ** ** Regards, ** ** Kelvin R. Porter ** ** *From:* spameden [mailto:spame...@gmail.com spame...@gmail.com] *Sent:* Monday, July 22, 2013 3:55 PM *To:* Porter, Kelvin *Cc:* de...@kannel.org; users@kannel.org *Subject:* Re: Proposal for Real-time Routing in opensmppbox and bearerbox ** ** Interesting idea, but what about load on the database? Will it be cached or it's gonna get results from the database everytime SMS arrives? ** ** 2013/7/23 Porter, Kelvin kelvin.por...@h3net.com *Hi,* * * *I have included a proposed enhancement for routing message based on database contents below.* * * *I am looking for feedback.* * * *Please share your thoughts as to whether this would be of interest.* * * *Thank you.* * * *Regards,* * * *Kelvin R. Porter* * * I would like to propose an enhancement to the source code to kannel bearerbox and opensmppbox. The enhancement would supercede the existing configuration groups: “smsbox-route” in the bearerbox, and “smsc-route” in the oppensmppbox. The enhancement dynamically consults a database table/view to determine the mapping based on the sender, receiver and original connection (smsc or box). The purpose of this enhancement is to allow dynamically changing the message routing without requiring a change to the configuration file(s) and then the subsequent restart of the opensmppbox and/or bearerbox. The enhancement works by taking advantage of the database access functionality currently used for storing DLRs in a database. The DLR code currently supports the following databases: “mysql”, “oracle”, “pgsql”, “mssql”, and “sdb” (where SDB is the simplified DB interface). The “internal” option refers to storing DLRs in memory and would not apply to this enhancement. I can write the code for these databases, but may require some assistance from the kannel community in testing them. The enhancement adds two new table parameters to the group “dlr-db”: “table-smsc” specifies the name of the table/view mapping a message to a smsc-id, and “table-box” specifies the name of the table/view mapping a message to a (sms)box. The enhancement is selectively enabled by configuring the parameters above. The following existing fields parameters of the group “dlr-db” are re-used: “field-source” specifies the name of the field that matches the sender of a message, and “field-destination” specifies the name of the field that matches the receiver of a message, and “field-smsc” specifies the name of the field that matches the smsc, and*** * “field-boxc-id” specifies the name of the field that matches the name of the box. The following are two routines added
