This document describes the database schema and usage in EMG 7.1 and later.
The default configuration of EMG and EMG Portal uses a MySQL database for user authentication and for logging and statistics.
Core EMG tables have no special prefix while EMG Portal tables are prefixed with “emgp_”.
Authentication
EMG uses the database when authenticating incoming connections (via SMPP for example. This is enabled by keyword “USERDB” in connector configuration.
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
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
Message are logged to the table “routelog”.
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.
Aggregated data
Since queries against the routelog table can be very time-consuming with large amounts of data EMG Portal includes aggregation scripts for aggregation of message statistics hourly summaries in tables “emgp_hourly_summary” and “emgp_hourly_summary_connector”.
Sample queries
Number of SMS sent for a specific user / account (“client1”) for a specific time period (June 2016):
SELECT SUM(IFNULL(npdus, 0))
FROM routelog
WHERE username = 'client1'
AND starttime >= '2016-06-01'
AND starttime < '2016-07-01'
AND msgtype = 1;Tables
The full list of tables and their columns are described separately.