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).**** > > ** ** >
