Sqlbox is a special Kannel box that sits between bearerbox and smsbox and
uses a database queue to store and forward messages.
Sqlbox behaves similar to other Kannel boxes and share a compatible configuration
file format and command line options.It works between bearerbox and smsbox, intercept all messages and use a couple of database
tables to process messages.Messages are queued on a configurable table (defaults to
send_sms) and moved to another table (defaults to sent_sms)
afterwards.You can also manually insert messages into the send_sms table
and they will be sent and moved to the sent_sms table as well. This
allows for fast and easy injection of large amounts of messages into
kannel. Modular architecture: Easily integrates into Kannel infrastructure.Compatible configuration file format and command line arguments.Supports most Kannel features.
sqlbox is being developed on Linux and OSX systems, and
should be fairly easy to export to other Unix-like systems. However,
we don't yet support other platforms, due to lack of time, although
it should be working without major problems on Windows (through
Cygwin), Mac OSX, Solaris and FreeBSD. sqlbox requires the following software environment:
Kannel libraries (gwlib) installed.C compiler and libraries for ANSI C, with normal Unix
extensions such as BSD sockets and related tools. (GNU's GCC
tool-chain is recommended)GNU Make. An implementation of POSIX threads (
pthread.h
).
DocBook processing tools: DocBook style-sheets, jade, jadetex,
etc; see
README
, section `Documentation', for more information (pre-formatted
versions of the documentation are available, and you can compile
Sqlbox itself even without the documentation tools).
GNU autoconf
This chapter explains how to build and install sqlbox from
source or from a binary package. The goal of this chapter is to get
the module compiled and all the files in the correct places; the next
chapter will explain how to configure it. If you are upgrading from a previous version, please look at
for any important information.
The source code to Sqlbox is available for download at
.
It is available in various formats and you can choose to download
either the latest release version or the daily snapshot of the
development source tree for the next release version, depending on
whether you want to use Sqlbox for production use or to participate
in the development.
If you're serious about development, you probably want to use
CVS, the version control system used by the Kannel project. This
allows you to participate in Sqlbox development much more easily
than by downloading the latest release and integrating any
changes you've made every day. CVS does that for you. (See the
Kannel web site for more information on how to use CVS.) The documentation for Sqlbox consists of two parts:
User's Guide
, i.e., the one you're reading at the moment.
The
README
and various other text files in the source tree.
You can also find general information on Kannel's
and information about existing problems at
.
We intend to cover everything you need to install and use Sqlbox is
in
User's Guide
, but the guide is still incomplete in this respect. The
README
is not supposed to be very important, nor contain much information.
Instead, it will just point at the other documentation.
If you are using Sqlbox on a supported platform, or one that is
similar enough to one, compiling Sqlbox should be trivial. After you
have unpacked the source package of your choose, or after you have
checked out the source code from CVS, enter the following commands:
./bootstrap ./configure make
The
bootstrap
script uses autoconf to generate the files needed to build the
module. The
configure
script investigates various things on your computer for the Sqlbox
compilation needs, and writes out the
Makefile
used to compile the module.
make
then runs the commands to actually compile it.
If either command writes out an error message and stops before it
finishes its job, you have a problem, and you either need to fix it
yourself, if you can, or report the problem to the Kannel project.
See
for details.
For detailed instruction on using the configuration script, see file
INSTALL
. That file is a generic documentation for
configure
. Sqlbox defines a few additional options:
--with-kannel-dir=
DIR
Where to look for Kannel Gateway libs and header files
DIR
points to the Kannel installation directory. Defaults to
/usr/local
--disable-docs (default is --enable-docs)
Use this option if you don't have DocBook installed and/or you
want to save some time and CPU cycles. Pre-generated
documentation is available on Kannel's site. Default behavior is
to build documentation, b.e., converting the User Guide from the
DocBook markup language to PostScript and HTML if DocBook is
available.
--enable-drafts (default is
--disable-drafts)
When building documentation, include the sections marked as
draft
.
--with-ctlib=DIR
Include Ct-Lib support.
DIR
is the Ct-Lib install directory, defaults to
/opt/sybase.
--with-freetds=DIR
Include FreeTDS Ct-Lib support.
DIR
is the FreeTDS install directory, defaults to
/usr/local.
You may need to add compilations flags to configure:
CFLAGS='-pthread' ./configure
The above, for instance, seems to be required on FreeBSD. If you
want to develop Sqlbox, you probably want to add CFLAGS that make
your compiler use warning messages. For example, for GCC:
CFLAGS='-Wall -O2 -g' ./configure
(You may, at your preference, use even stricter checking options.)
After you have compiled Kannel, you need to install the sqlbox binary
in a suitable place. This is most easily done by using
make again:
make bindir=/path/to/directory install
Replace
/path/to/directory
with the pathname of the actual directory where the programs should
be installed. This install the sqlbox binary:
gw/sqlbox
To be doneTo be doneTo be doneThis chapter explains how to configure and run Sqlbox and also
how to tell if it's running from Kannel's HTTP interface.There is only one configuration file for Sqlbox, and that file
commands all aspects of its execution.The configuration file can be divided into two parts:
sqlbox configuration and database connection.Details of each part are in appropriate sections later on
this documentation.The syntax used for the configuration file is the same used
in Kannel. Skip this section if you are already familiar with it.
Otherwise, keep on reading: A configuration file consists of groups of configuration variables.
Groups are separated by empty lines, and each variable is defined
on its own line. Each group in Sqlbox configuration is
distinguished with a group variable. Comments are lines that begin
with a number sign (
#
) and are ignored (they don't, for example, separate groups of
variables).
A variable definition line has the name of the variable, and equals
sign (
=
) and the value of the variable. The name of the variable can
contain any characters except whitespace and equals. The value of
the variable is a string, with or without quotation marks (
) around it. Quotation marks are needed if the variable needs to
begin or end with whitespace or contain special characters. Normal
C escape character syntax works inside quotation marks.
Perhaps an example will make things easier to comprehend:
01 # Sqlbox configuration
02 group = sqlbox
03 id = "my-sqlbox"
04 smsbox-id = "sqlbox"
...
11 log-level = 0
12 log-file = "/var/log/kannel/kannel-sqlbox.log"
13
14 #MySQL Connection
15 group = mysql-connection
16 id = "my-sqlbox"
17 host = localhost
...
The above snippet defines an sqlbox instance with id my-sqlbox
that identifies with bearerbox as sqlbox and also
sets the log-level and file location. It also defines a MySQL connection to localhost.
Lines 1 and 14 are comment lines. Line 13 separates the two
groups. The remaining lines define variables. The group type is
defined by the group variable value.The various variables that are understood in each type of
configuration group are explained below. Some variable values are marked as 'bool'.
The value for variable can be like true, false, yes, no, on, off,
0 or 1. Other values are treated as 'true' while if the variable is
not present at all, it is treated as being 'false'.
A configuration file may contain a special directive called
include
to include other file or a directory with files to the
configuration processing.
This allows to segment the specific configuration groups
required for several services and boxes to different files and
hence to have more control in larger setups. Here is an example that illustrates the
include
statement :
group = sqlbox
id = my-sqlbox
smsbox-id = sqlbox
...
log-file = "/var/log/kannel/kannel-sqlbox.log"
log-level = 0
include = "dbconn.conf"
Above is the main
sqlbox.conf
configuration file that includes the following
dbconn.conf
file with all required directives for the database connection.
group = mysql-connection
id = my-sqlbox
host = localhost
username = myuser
password = mypass
database = kannel
The above
include
statement may be defined at any point in the configuration file and
at any inclusion depth. Hence you can cascade numerous inclusions
if necessary.
At process start time inclusion of configuration files
breaks if either the included file can not be opened and processed
or the included file has been processed already in the stack and a
recursive cycling has been detected. The configuration file
MUST
always include an 'sqlbox' group for general configuration. This
group should be the first group in the configuration file.
As its simplest form, 'sqlbox' group looks like this:
group = sqlbox
id = sqlbox
bearerbox-port = 13001
Naturally this is usually not sufficient for any real use. Thus, one or
more of the optional configuration variables are used. In following
list (as in any other similar lists), all mandatory variables are
marked with (m), while conditionally mandatory
(variables which must be set in certain cases) are marked with
(c)
.
A sample more complex 'sqlbox' group could be something like
this:
group = sqlbox
id = sqlbox-db
smsbox-id = sqlbox
#global-sender = ""
bearerbox-host = localhost
bearerbox-port = 13001
smsbox-port = 13005
smsbox-port-ssl = false
sql-log-table = sent_sms
sql-insert-table = send_sms
log-file = "/var/log/kannel/kannel-sqlbox.log"
log-level = 0
sqlbox needs a connection to a supported DB engine to operate.
This connection is established at startup time and kept open until the box stops.At the moment, sqlbox supports MySQL, Oracle, PostgreSQL, LibSDB, MS-SQL, Sybase,
Sqlite2 and Sqlite3, though only MySQL and PostgreSQL are tested enough to be considered stable.The process of configuring a DB connection is simple: You need to create a
[engine]-connection section (where [engine] is the DB engine name, either
mysql, oracle, pgsql, sdb, sqlite or sqlite3) and indicate a few parameters needed to
establish the DB connection.Uses a MySQL database to store the data. You need to specify the mysql-connection
group.Example configuration:group = mysql-connection
id = my-sqlbox
host = localhost
username = foo
password = bar
database = kannel
max-connections = 1Uses an MS-SQL or Sybase database to store the data. You need to specify the mssql-connection
group.Example configuration:group = oracle-connection
server = myserver
username = foo
password = bar
max-connections = 1Uses an Oracle database to store the data. You need to specify the oracle-connection
group.Example configuration:group = oracle-connection
tnsname = //localhost:1521/XE
username = foo
password = bar
max-connections = 1Uses a PostgreSQL database to store the data. You need to specify the pgsql-connection
group.Example configuration:group = pgsql-connection
id = pg-sqlbox
host = localhost
username = foo
password = bar
database = kannel
max-connections = 1Uses the LibSDB database abstraction layer to transparently connect to a database that stores the data.
You need to specify the sdb-connection group.Example configuration:group = sdb-connection
id = sd-sqlbox
#url = mysql:host=localhost:db=kannel:uid=myuser:pwd=mypass
#url = sqlite:db=/path/to/kannel.db
url = sqlite3:db=/path/to/kannel3.db
max-connections = 1Uses a Sqlite 2.x database to store the data. You need to specify the sqlite-connection
group.Example configuration:group = sqlite-connection
id = s2-sqlbox
host = localhost
database = /path/to/kannel.db
lock-timeout = 5
max-connections = 1Uses a Sqlite 3.x database to store the data. You need to specify the sqlite3-connection
group.Example configuration:group = sqlite3-connection
id = s3-sqlbox
host = localhost
database = /path/to/kannel3.db
lock-timeout = 5
max-connections = 1You need to start sqlbox after starting
the bearerbox, otherwise it won't have a port
open to connect to. The preferred way to do this is to include
sqlbox into your Kannel's startup script.
If you want to start it from command line (for testing, for
example), give the following command:
/path/to/sqlbox -v 1 [config-file]
The -v 1 sets the logging level to
INFO. This way, you won't see a large amount of
debugging output (the default is DEBUG). Full
explanation of Sqlbox command line arguments is below.[config-file] is the name of the
configuration file you are using with Sqlbox. The basic distribution
packet comes with a sample configuration file you can use with some
minor tweakings (check on the /examples folder.
Feel free to edit the file to suit your needs.Of course you need to have the bearerbox running
before starting the box. Without the bearer box, sqlbox won't even start.Sqlbox accept certain command line options
and arguments when they are launched. These arguments are:Sqlbox creates it's DB tables on the fly if the tables are not present at that moment. If you're
upgrading from a previous version, or happen to have tables with the same names as the ones Sqlbox
uses, but having a different structure, this will probably cause problems and there's a good chance the
process will panic and stop. In that case, rename/drop the offending tables or change the names Sqlbox
uses by using the sql-log-table and sql-insert-table variables.One of the nice features Sqlbox provides is the ability to insert MT messages into Kannel's queue by
inserting rows into the send_sms table. Keep in mind that both tables have the same schema,
but you only need to care about send_sms. Sqlbox will move messages to the
sent_sms table autmatically after processing it.The tables structure is as follows:As when you're using the sendsms interface, you don't need to specify
all the columns in order to succesfully 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
) VALUES (
'MT', '1234', '1234567890', 'Hello world', 2
);The former example would send a message with text "Hello world" to number "1234567890".
If possible, the sender would be set to "1234".You can add other parameters to specify routing, charset encoding and any other settings
your setup may require. Just remember, try to keep it simple whenever possibleThis chapter explains where to find help with problems
related to the gateway, and the preferred procedure for reporting
bugs and sending corrections to them.The Kannel development mailing list is devel@kannel.org. To subscribe, send mail to .
This is currently the best location for asking help and reporting
bugs. Please include configuration file and version number.This appendix includes pertinent information about required
changes on upgrades.
As a general rule, always check the ChangeLog file
before upgrading, because it may contain important information worth knowing
before making any changes.Sqlbox is a simple module that usually upgrades easily and without requiring any other changes.In some cases, a change on the DB structure takes place and this requires changes on the DB schemas as well.
Since sqlbox automatically generates its tables, the best approach for this kind of upgrades is
to make sure that there's no messages pending, backup the tables contents (if there's no messages pending only the
sent_sms table will have records), drop the tables and let sqlbox create the
tables again. Alternatively you can check what changes are necessary and ALTER the tables yourself.