Unsubscribe
________________________________ From: spameden <spame...@gmail.com> To: "Porter, Kelvin" <kelvin.por...@h3net.com> Cc: "de...@kannel.org" <de...@kannel.org>; "users@kannel.org" <users@kannel.org> Sent: Tuesday, July 23, 2013 12:31 PM Subject: Re: Proposal for Real-time Routing in opensmppbox and bearerbox Hi! 2013/7/23 Porter, Kelvin <kelvin.por...@h3net.com> Hi, > >Responses below. > >Regards, > >Kelvin R. Porter > >From:spameden [mailto:spame...@gmail.com] >Sent: Monday, July 22, 2013 6:23 PM > >To: Porter, Kelvin >Cc: de...@kannel.org; users@kannel.org >Subject: 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. >[KRP] That would be great. Is it for both opensmppbox and bearerbox? How >would you trigger the reload on opensmppbox? Bearerbox only. Here is a patch - http://pastebin.com/v4rSRHrJ (I hope Stipe wouldnt mind posting it). 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. >[KRP] 100 – 200 T/sec does not seem that like that many transactions for one >of our servers. Well, in this case you get 100-200 additional queries, it was just an example and rly depends on your database workload. [KRP] I can definitely look into a caching component as a subsequent enhancement. 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). >[KRP] Unfortunately, that will not address my needs. I need to be able to add >and remove numbers dynamically and affect their routing accordingly. >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. >[KRP] Well, you could not implement the entire configuration in a database, >because the process needs configuration information to find the database. True, ofc, you need to provide connection settings as in everywhere. Also, currently, the configuration is read in once at start up time. The code would need to be changed to consult the database or keep in sync with it dynamically. I am proposing an enhancement that would dynamically consult the database (when configured to do so). Migrating the code to dynamic configuration would be useful, I believe; however, moving some or all of the code in this direction would be a significant undertaking. I'm not against your proposal, just thinking about potential problems you might get .. The best plan is to make different engines, i.e. MySQL / PostgreSQL / Oracle / etc. Only dynamic variables like smsbox-route and various smsc settings should be kept into the database to operate them on the fly I think.. Other data like DLR / bearerbox core settings can be static in the configuration I think. Anyways, I'm waiting for your version of the patched opensmppbox / bearerbox to test out! :) 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.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 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). >> >> >
