Page tree
Skip to end of metadata
Go to start of metadata

What's new

In EMG 7.0 the main schema change was the new cfg_* tables to support database-driven configurations.

When migrating from an earlier version of EMG you first need to create new tables by applying "emg-schema-mysql.sql" and then run a "emgd --upgradedb".

Please note that "emgd --upgradedb" 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" is set to the MD5 hash of the password. The field "password" must 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

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.

Database schema

Below we explain the columns in the various EMG database tables.

Table cfg_connectors

Base connector configuration.

Only read by EMG, never updated.

ColumnDescription
idAuto-generated unique id (primary key)
nameConnector name
connector_orderNumeric sort order value that can be used to sort connectors in specific order
updatedTimestamp when record last updated
disabledIf set to 1, connector will be considered disabled
noteOptional textual description

Table cfg_connectoroptions

Additional connector configuration options.

Only read by EMG, never updated.

ColumnDescription
idAuto-generated unique id (primary key)
connectoridConnector id
keyorderNumeric sort order value that can be used to sort options in specific order
updatedTimestamp when record last updated
disabledIf set to 1, connector will be considered disabled
keynameConnector option name
valueConnector option value
noteOptional textual description

Table cfg_general

General configuration options.

ColumnDescription
idAuto-generated unique id (primary key)
keyorderNumeric sort order value that can be used to sort options in specific order
keynameGeneral option name
valueGeneral option value
updatedTimestamp when record last updated

Table cfg_plugins

Plugin configuration.

ColumnDescription
idAuto-generated unique id (primary key)
namePlugin name
instancesNumber of plugin instances
libraryPath to plugin file in file system
configOptional config option
offsetOffset
updatedTimestamp when record last updated
disabledIf set to 1, connector will be considered disabled
noteOptional textual description
dbprofileDatabase profile info to send to plugin method "create_config". Added in EMG 7.1.

Table cfg_satpools

SAT pool configuration.

ColumnDescription
idAuto-generated unique id (primary key)
nameSAT pool name
addressrangeAddress range for pool
threadedThreaded conversations flag
quoted_replyQuoted replies flag
ignore_destaddrIgnore destaddr flag
randomRandom flag
expireExpire time for SAT pool entries, in seconds
updatedTimestamp when record last updated
disabledIf set to 1, connector will be considered disabled
noteOptional textual description

Table connectorlog

Connector log.

Normally not enabled.

Table emgsystem

System information, such as schema version.

ColumnDescription
keynameKey
valuevalue

Keys used:

KeyDescription
emgschemaEMG schema versio, for example "37"
dbconfig_lastokTimestamp for last successfully generated configuration
dbconfig_lastok_XTimestamp for last successfully generated configuration in a multi-node environment (X=Node id)
hourly_summary_last_tsUsed by "hourly_summary.pl" aggregation script to keep track of last message timestamp (used from script version 42720)

Table emguser

EMG accounts

ColumnDescriptionUpdated by EMG?
useridAuto-generated unique id (primary key) 
createdTimestamp when user created 
usernameUsername 
passwordPassword (clear text) 
md5passwordPassword (MD5 hash) 
creditssendCredits for sending, integer partYes
creditssenddecCredits for sending, decimal part in 1 / 1 000 000 
creditsreceiveNOT USED 
charge_balanceNew in EMG 5.3: Account balance for sending messages 
maxsessionsMax number of simultaneous sessions 
throughputMax throughput 
lastloginLast successful loginYes
lastfailedloginLast failed loginYes
lastipIP address last seenYes
lockeduntilAccount locked until time specified 
allowpostpaidIs charge_balance or creditssend < 0 allowed? (1 = Yes) 
idletimeout  
failedloginsNOT USED 
usergroupUser is administrator if set to 'ADMIN' 
fullnameName of customer contact person 
companyCompany name 
routeUser-specific route (connector name) 
routedlrUser specific route (connector name) for delivery reports 
routingFile name for user-specific routing file to use 
routesatUser-specific route for SAT replies 
phoneCustomer phone number 
forcesourceaddrForced source address for messages received from customer 
satpoolcreateUser-specific SAT pool 
chargesFile name for charges (billing info) 
extra1Extra field for information of choice 
extra2Extra field for information of choice 
extra3Extra field for information of choice 
extra4Extra field for information of choice 
extra5Extra field for information of choice 
extra6Extra field for information of choice 
extra7Extra field for information of choice 
extra8Extra field for information of choice 
extra9Extra field for information of choice 
modeCan be set to "RX" to force user to bind as a "receiver" (only receive, not send messages). 
cert_fingerprintIf set any fingerprint for a certificate presented by user must match. 
min_vpIf user sends a value for validity period less than min_vp it will be adjusted to min_vp. 
max_vpIf user sends a value for validity period larger than max_vp it will be adjusted to max_vp. Added in EMG 7.1. 
dlr_delayOptional delay in seconds to hold dlr before forwarding it to user. Added in EMG 7.1. 
charge_balance_postpaid_limitLower limit for charge_balance when customer uses postpaid. As the value is compared to charge_balance, it should be a negative value. If the value is null, there is no limit. Added in EMG 7.1. 

Table emguseraccess

EMG account access entries.

Columns "ipadress" and "ipstr" are mutually exclusive meaning only one of them can be used and the other must be set to null.

ColumnDescription
useraccessidAuto-generated unique id (primary key)
useridReference to emguser.id
ipaddressIP address
ipstrIP address as text (must be used for IPv6)
ipwidthNetwork mask, bits (0 = Any, 24 = IPv4 C-net, 32 = IPv4 host)
connectorConnector name, "*" for any

Table messagebody

Message body.

ColumnDescription
idEMG message id (primary key)
dataMessage data, hex encoded

Table messageoption

Additional message options.

ColumnDescription
useraccessidAuto-generated unique id (primary key)
idEMG message id
optionkeyMessage option key
dataMessage option value

Table monthlysummary

Not used. Replaced by EMG Portal table "emgp_hourly_summary".

Table pdulog

PDU log, if enabled.

Same information as written to PDU log files can be written to database.

ColumnDescription
idAuto-generated unique id (primary key)
createdTimestamp when record created, second precision
msecsMillisecond precision
trnTransaction number, when available
rwSet to "R" when operation read and "W" when operation written
connectorConnector name
instanceConnector instance number
operationPDU operation
statusPDU status
pduTextual representation of pdu data

Table routelog

Message log.

All columns are written by EMG.

ColumnDescription
msgidEMGs unique message id
starttimeTimestamp when entry created (message received)
startmsecsMillisecs for starttime
endtimeTimestamp when message sent out
endmsecsMiilisecs for endtime
inconnectorConnector name where message received
outconnectorConnector name where message sent out
sourceaddrSender address
destaddrRecipient address
statusMessage status (MGP status code)
reasonProtocol error code on errir
startsecsTimestamp when message received (secs since Jan 1, 1970)
endsecsTimestamp when message sent out (secs since Jan 1, 1970)
noteMessage note (option MGP_OPTION_NOTE)
usernameAuthenticated user when message received by EMG
smscidMessage id received when sent out
reasontextReason text from delivery report, if any
lastdlrsecsTimestamp when last delivery report received for message (secs since Jan 1, 1970)
lastdlrmsecsMillisecs for lastdlrsecs
npdusActual pdus when message sent out (message parts)
bufferedstatusStatus reported by intermediate delivery report
msgtypeMessage type (1 = SMS, 5 = Delivery report)
origidId of original message in SAT conversations
chargeMessage fee
charge_costMessage cost
charge_price_idId 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

  • No labels