Hi!
2013/7/23 Porter, Kelvin <[email protected]> > Hi,**** > > ** ** > > Responses below.**** > > ** ** > > Regards,**** > > ** ** > > Kelvin R. Porter**** > > ** ** > > *From:* spameden [mailto:[email protected]] > *Sent:* Monday, July 22, 2013 6:23 PM > > *To:* Porter, Kelvin > *Cc:* [email protected]; [email protected] > *Subject:* Re: Proposal for Real-time Routing in opensmppbox and bearerbox > **** > > ** ** > > ** ** > > ** ** > > 2013/7/23 Porter, Kelvin <[email protected]>**** > > 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:[email protected] <[email protected]>] > *Sent:* Monday, July 22, 2013 3:55 PM > *To:* Porter, Kelvin > *Cc:* [email protected]; [email protected] > *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 <[email protected]>**** > > *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).**** > > **** > > **** > > ** ** >
