Understanding EMG database usage (EMG 5.3 and later) Print
Monday, 17 October 2011

This document applies to EMG 5.3 and later. For earlier EMG 5.2 and earlier, click here.

EMG can be used together with a MySQL database for improved flexibility with user authentication and for logging and statistics.

In order to use a database it must be set up and a database profile must be added to the "server.cfg" configuration file. This procedure is described in the EMG User's Guide.

What's new

In EMG 5.3 two major changes was implemented:

  • Message ids are now 64-bit
  • New fields were added for the possibility of handling message charges in decimal numbers rather than integers

When migrating from an earlier version of EMG a "emgd -upgradedb" is necessary. Please note that this may take a long time for large databases since it will perform an "alter table" to change connectorlog and routelog tables. We recommend testing the migration in a test environment before running it on a production database. In order to see what commands will be executed (without actually executing them), run "emgd -upgradesql".

Authentication

EMG can use the database when authenticating incoming connections. This is enabled by replacing the "USERS" keyword in the connector configuration with the "USERDB" keyword. The tables involved are "emguser" and "emguseraccess", where "emguser" contains user credentials and "emguseraccess" contains IP addresses from which the user is allowed to connect.

Please note that there must exist entries for the specific user in both tables, or the authentication will fail.

EMG validates the provided username and password combination towards the fields "username" and "password" (or "md5password", if set) in "emguser" and at the same time joins in entries from "emguseraccess". If username and password matches, the entries from "emguseraccess" will be traversed to look for an entry matching the source IP address of the user. Entries in "emguseraccess" contains the "userid" (id from "emguser" table), "ipaddress", "ipmask" and "connector". Fields "ipaddress" and "mask" contains the IP address (as an integer) and the network mask (a value from 0 to 32), so that 3232235776 ("192.168.1.0" as an integer) and "24" matches the class C network "192.168.1.0/255.255.255.0". The value for "connector" can either be a specific connector name or the special value "*" which means any connector.

In order to allow access on any connector from any IP address for a specific user the values would be 0 (ipaddress "0.0.0.0" as an integer, "0" (ipmask) and "*" (connector) respectively. However, this is the least secure option and would not be recommended if it can be avoided.

User information is never cached within EMG and therefore any updates of the information in the user tables will take effect immediately (on next user login attempt). However, session already active are not affected.

To convert an IP address to integer the following formula is used:

IP address: x.y.z.n
IP address as integer: x * 256^3 + y * 256^2 + z * 256 + n

So, for 192.168.1.0 we calculate 192 * 256^3 + 168 * 256^2 + 1 * 256 + n =3232235776

Hashed passwords

If you want to use hashed passwords, the field "md5password" should be set to the MD5 hash of the password. The field password should be set to NULL.

For example MD5 hash for string "secret" is "5ebe2294ecd0e0f08eab7690d2a6ee69".

Message credits and prepaid vs postpaid

When users are authenticated from database the credits handling will be used when sending messages. For each message the authenticated user sends the "creditssend" (or "charges_balance" if message charges are used) column will be decreased. If balance goes below 0 and allowpostpaid is not set, 0 or NULL, further sending will be rejected until balance is updated to a value > 0.

Message charges in EMG 5.3+

The credits-based system in earlier versions can now be overriden by using message charges instead. If emguser.charge_balance is set to a non-NULL value the message charges mechanism will be used instead of message credits.

In order to set charges for a message the EMG server needs to be properly licensed for use of "billing plugin" and a plugin (written in Perl or C) must be used to set the actual message charge (and optionally message cost). MGP options MGP_OPTION_CHARGE and MGP_OPTION_CHARGE_COST.

When a client sends a message EMG will first deduct the amount given by "DEFAULT_CHARGE" keyword, if defined. Then message will be routed and the proper billing plugin executed. When message is sent out EMG will check the actual charge set by the plugin and adjust the user's account balance in accordance with that. If the user is non-postpaid and the user account balance is less than needed to cover for the default charge the message will be rejected.

For example, if default charge is set to 0.02 and the actual message charge ends up being 0.05, then 0.02 will be deducted when message is received by EMG and another 0.03 when message leaves EMG.

Logging

The message log was initially written to the table "connectorlog" in the same way as the connector log files are created in the file system. However, it is no longer recommended to use "connectorlog" but instead to use the "routelog" table which is more powerful and efficient.

Use of the "routelog" table is enabled by adding "ROUTELOGDB" to the general part of server.cfg. In addition to "routelog" there are two more tables used for message logging, "messageoption" and "messagebody". In "messageoption" all message options that do not have their own field in "routelog" are added "key-value" style while the actual message body is written to "messagebody".

When a message is first received over a connector it is assigned a message id and an entry is created in "routelog" including "msgid", "starttime" (second resolution), "startmsecs" (msec resolution), "inconnector" (where message is received) and some message options (source and destination address etc). Also the field "username" specified the authenticated user that submitted the message and the field "messagetype" contains a "1" to indicate a normal message, while "5" indicates a delivery report. After the message has been routed and sent out the "outconnector", "endsecs" and "endmsecs" fields in "routelog" table are updated.

Usually a delivery report (DLR) is requested and when such a delivery report is received a new entry is created for the DLR in "routelog" with message type "5" and the original message is also updated with the "status" as indicated in the DLR plus the timestamps "lastdlrsecs" and "lastdlrmsecs". In the DLR entry the "origid" field is set to the message id of the original message to facilitate matching of messages and DLRs in the logs.

Sometimes a message received is split within EMG and sent as out multiple message parts (for example when EMG acts as a e-mail to SMS gateway). When that happens the "npdus" field is set to the number of message parts sent out so that billing can be performed correctly.

Some SMSCs can be configured to send back delivery reports even if the message has not reached its final status, for example when handset is switched off and first delivery attempt fails. EMG can handle these intermediate delivery reports and update the "bufferedstatus" field in "routelog". The actual values of that status is protocol and SMSC specific.

In order to determine delivery time it is important to understand that the difference between "endsecs" and "starttime" usually indicates the time the message spent within EMG while the difference between "lastdlrsecs" and "starttime" indicates the approximate delivery time when the message has reached it final state ("delivered"). This applies to SMSC protocols (SMPP, UCP, CIMD2 etc) when delivery reports are used while for HTTP and SMTP there are no delivery reports involved and therefore only "endsecs" will be available and "lastdlrsecs" will never be updated.

The "routelog" table should provide enough information to provide powerful tools for message tracking and statistics. When joined with "messageoption" and "messagebody" even more detailed message information is available. It is generally wise to "trim" the "messageoption" and/or "messagebody" regularly if it is not needed any more. It may also be a good idea to replicate the information onto a second database instance if complex queries are done frequently in order to ensure that the production environment is not overloaded.

Monthly summary

There is a table in the database for aggregating monthly totals of messages sent per user. A cron script or similar need be set up to populate this table. Typically it would be run first day of month to calculate totals for previous month.

Sample script (which you will need to edit according to your setup):

#!/bin/sh

# Specify month on command-line.
# For example: "2011-10" for October 2010
month=$1

# Replace with connectors where outbound messages are sent
outconnectors="'smsc1','smsc2'"
# Replace with actual DB info
dbuser=emg
dbpassword=secret
dbname=emg
dbhost=127.0.0.1

mysql -u$dbuser -p$dbpassword $dbname -h$dbhost <<EOF
START TRANSACTION;
DELETE FROM monthlysummary WHERE month = '$month';
INSERT INTO monthlysummary (month, userid, count, charge, charge_cost)
    SELECT xmonth, u.userid, xsum, xcharge, xcharge_cost FROM (
        SELECT LEFT(starttime, 7) AS xmonth, username AS xusername, SUM(npdus) AS xsum,
SUM(charge) AS xcharge, SUM(charge_cost) AS xcharge_cost
            FROM routelog
            WHERE inconnector NOT IN ($outconnectors) AND
starttime >= '$month-01' AND starttime <= '$month-31'
            GROUP BY 1, 2) r, emguser u WHERE r.xusername = u.username;
COMMIT;
EOF

 

Database columns

Table emguser

Column Description Updated by EMG?
userid Auto-generated unique id (primary key)
created Timestamp when user created
username Username
password Password (clear text)
md5password Password (MD5 hash)
creditssend Credits for sending, integer part
Yes
creditssenddec Credits for sending, decimal part in 1 / 1 000 000
creditsreceive NOT USED
charge_balance New in EMG 5.3: Account balance for sending messages  
maxsessions Max number of simultaneous session
throughput Max throughput
lastlogin Last successful login Yes
lastfailedlogin Last failed login Yes
lastip IP address last seen Yes
lockeduntil Account locked until time specified
allowpostpaid Is creditssend < 0 allowed? (1 = Yes)
idletimeout

failedlogins NOT USED
usergroup User is administrator if set to 'ADMIN'
fullname Name of customer contact person
company Company name
route User-specific route (connector name)
routedlr User specific route (connector name) for delivery reports
routing File name for user-specific routing file to use
routesat User-specific route for SAT replies
phone Customer phone number
foresourceaddr Forced source address for messages received from customer
satpoolcreate User-specific SAT pool
charges File name for charges (billing info)
extra1 Extra field for information of choice
extra2 Extra field for information of choice
extra3 Extra field for information of choice
extra4 Extra field for information of choice
extra5 Extra field for information of choice
extra6 Extra field for information of choice
extra7 Extra field for information of choice
extra8 Extra field for information of choice
extra9 Extra field for information of choice

Table routelog

All columns are written by EMG.

Column Description
msgid EMGs unique message id
starttime Timestamp when entry created (message received)
startmsecs Millisecs for starttime
endtime Timestamp when message sent out
endmsecs Miilisecs for endtime
inconnector Connector name where message received
outconnector Connector name where message sent out
sourceaddr Sender address
destaddr Recipient address
status Message status (MGP status code)
reason Protocol error code on errir
startsecs Timestamp when message received (secs since Jan 1, 1970)
endsecs Timestamp when message sent out (secs since Jan 1, 1970)
note Message note (option MGP_OPTION_NOTE)
username Authenticated user when message received by EMG
smscid Message id received when sent out
reasontext Reason text from delivery report, if any
lastdlrsecs Timestamp when last delivery report received for message (secs since Jan 1, 1970)
lastdlrmsecs Millisecs for lastdlrsecs
npdus Actual pdus when message sent out (message parts)
bufferedstatus Status reported by intermediate delivery report
msgtype Message type (1 = SMS)
origid Id of original message in SAT conversations
charge Message fee
charge_cost Message cost
charge_price_id Id for row in price table that was used for "charge", optional, application-level

charge_cost_price_id

Id for row in price table that was used for "charge_cost", optional, application-level