Installing and configuring SQL-Based queue engine for Kannel (SQL box)

SQL box is a special Kannel box that sits between bearer box and SMS box and uses a database queue to store and forward messages. Messages are queued on a configurable table (defaults to send_sms) and moved to another table (defaults to sent_sms) afterwards.

After installing the gateway, we need to compile SQL box.

Steps:

Navigate to the directory where Kannel is unzipped and then type the following commands:

cd addons/sqlbox/
sudo ./bootstrap
sudo ./configure --with-kannel-dir=/usr/local/kannel-1.5.0/
sudo make bindir=/usr/local/kannel/kannel-1.5.0/

Create a file named sqlbox.conf and add the following configuration:

# SQL BOX CONFIG
group = sqlbox
id = sqlbox-db
smsbox-id = sqlbox
global-sender = ""
bearerbox-host = localhost
bearerbox-port = 13010
smsbox-port = 13020
smsbox-port-ssl = false
sql-log-table = sent_sms
sql-insert-table = send_sms
log-file = "/var/log/kannel/kannel-sqlbox.log"

# Example MYSQL Connection
group = mysql-connection
id = sqlbox-db
host = localhost
username = username
password = password
database = kannel_db

Create the send_sms table using the following SQL command:

CREATE TABLE `send_sms` (
  `sql_id` bigint(20) NOT NULL auto_increment,
  `momt` enum('MO','MT') default NULL,
  `sender` varchar(20) default NULL,
  `receiver` varchar(20) default NULL,
  `udhdata` blob,
  `msgdata` text,
  `time` bigint(20) default NULL,
  `smsc_id` varchar(255) default NULL,
  `service` varchar(255) default NULL,
  `account` varchar(255) default NULL,
  `id` bigint(20) default NULL,
  `sms_type` bigint(20) default NULL,
  `mclass` bigint(20) default NULL,
  `mwi` bigint(20) default NULL,
  `coding` bigint(20) default NULL,
  `compress` bigint(20) default NULL,
  `validity` bigint(20) default NULL,
  `deferred` bigint(20) default NULL,
  `dlr_mask` bigint(20) default NULL,
  `dlr_url` varchar(255) default NULL,
  `pid` bigint(20) default NULL,
  `alt_dcs` bigint(20) default NULL,
  `rpi` bigint(20) default NULL,
  `charset` varchar(255) default NULL,
  `boxc_id` varchar(255) default NULL,
  `meta_data` varchar(255) default NULL,
  `foreign_id` bigint(20) default NULL,
  `binfo` varchar(255) default NULL,
  PRIMARY KEY (`sql_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Create sent_sms table using the same structure.

Now start bearerbox and sqlbox using commands:

sudo /usr/local/kannel-1.5.0/sbin/bearerbox /home/shaiju/kannel/kannel.conf
sudo /usr/local/kannel-1.5.0/sbin/sqlbox /home/shaiju/kannel/sqlbox.conf

Example

As with the sendsms interface, you don't need to specify all the columns in order to successfully enqueue a message.

Here's an example query you can use to send a simple message using Sqlbox:

INSERT INTO send_sms (momt, sender, receiver, msgdata, sms_type, smsc_id)
VALUES ('MT', 'TESTID', '9995323922', 'Hello world', 2, 'testsmsc1');

The above example would send a message with text "Hello world" to number "9995323922". If possible, the sender would be set to "TESTID". You can add other parameters to specify routing, charset encoding, and any other settings your setup may require.

If you are interested in learning more about this, refer here.