EMG 6.0.6 - User's Guide
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:
In order to use a database a few steps must be accomplished:
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.
DB emg <
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.
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):
DB emg <
CONNECTOR smpp-in1 <
Minimum content in database:
INSERT INTO emguser (username, password) VALUES(`user1','secret');
INSERT INTO emguseraccess (userid, ipaddress, ipwidth, connector)
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.