www.nordicmessaging.se

EMG 6.0.6 - User's Guide

Table of ContentsPreviousNext

13. Database support

A number of EMG components support DB connectivity. Currently the only supported database is MySQL. However, EMG does not require a database in order to work. Some functionality in future releases may depend on the database support but the core functionality does not depend on it.

Specifically database support may affect:

Account information
Routing log
Connector log
MGP phonebooks
EMG Roamer module

In order to use a database a few steps must be accomplished:

A database server must be installed
The database must be initialized for EMG
A database profile must be created in the configuration file, server.cfg
The database profile must explicitly be used in server.cfg using relevant keywords (DBPROFILE, USERDB, ROUTELOGDB etc).

13.1 Getting started with a DB

13.1.1 Installing the database server

Please visit the website of the database supplier for more information on generic installation, configuration and licensing of the database used.

13.1.2 Initializing the database

Scripts for database initilization is provided in the EMG distribution, for example emg-schema-mysql.sql. This script does not create the database. Also it does not drop the tables if they exist. For MySQL there is a script performing these tasks included in the distribution. It is named createemgdb-mysql.sh and is provided as reference.

13.1.3 Creating a database profile

In order to use the database a database profile must be defined in server.cfg.

Sample entry:

DB emg <
TYPE=MYSQL
HOST=localhost
PORT=3306
DBNAME=emg
USERNAME=emg
PASSWORD=secret
INSTANCES=1
>

13.1.4 Referencing a database profile

Use the DBPROFILE keyword in the configuration file, server.cfg to reference the previously created database profile (in this case called "emg").

For EMG Roamer, the DBPROFILE keyword would be used in the roamer.cfg file.

Example:

DBPROFILE=emg

13.2 Using the database

13.2.1 Putting the message route log in a database

By adding the keyword ROUTELOGDB all necessary information about a message will be stored in the "routelog" table in the database. Together with the actual message contents which is stored in the "messagebody" table all information for tracking as well as billing messages should be readily available.

Any message options present on the message for which there is no column in "routelog" will "spill over in to the "messageption" table. This can be suppressed using the DISABLE_MESSAGEOPTION keyword.

13.2.2 Putting the connector log in a database (deprecated)

By adding the keyword CONNECTORLOGDB all connector events that are written into the connector log files are also stored in the database. For each message received by EMG and sent out there will be two corresponding entries in the connectorlog, one for each event (receive and send).

Putting the connector log in the database is not efficient and should be avoided.

13.2.3 User authentication from database

By using the keyword USERDB instead of USERS on a connector the user information will be read from the specified database profile. User information are read from two tables "emguser" and "emguseraccess".

Sample configuration (parts of configuration omitted):

DBPROFILE=emg

DB emg <
...
>

CONNECTOR smpp-in1 <
...
USERDB=emg
>

Minimum content in database:

INSERT INTO emguser (username, password) VALUES(`user1','secret');

INSERT INTO emguseraccess (userid, ipaddress, ipwidth, connector)
  VALUES(1,'0.0.0.0',0,'*');

This would allow user "user1" to connect to any connector from any IP address. We assume that the userid assigned by the first insert is "1".

EMG will read user information from database for every login, if the user is not already logged in. Therefore it may not be necessary to refresh or reload the server after adding or modifying account information.

13.3 Schema version handling

From time to time the EMG database schema is updated. This is automatically detected by the server when the server is started and if the database indicates an older schema version than is used by the server the server will refuse to start until the schema has been upgraded.

A schema upgrade is performed by executing "emgd -upgradedb". Please note that with large tables this can take a considerable amount of time, even hours with really large databases. The EMG server must NOT be running when the database schema is upgraded or the database will potentially be corrupted.

The SQL that will be executed in order to perform the upgrade can be displayed with "emgd -upgradesql".

EMG reads the current schema version from the "emgsystem" table which should holds one row with "keyname = emgschema" and "value" set to the current schema version. If the emgsystem table or the row is missing a schema version of "0" is implicated.

Table of ContentsPreviousNext