Below we explain the columns in the various EMG database tables.
Table cfg_connectors
Base connector configuration.
Only read by EMG, never updated.
| Column | Description |
|---|---|
| id | Auto-generated unique id (primary key) |
| name | Connector name |
| connector_order | Numeric sort order value that can be used to sort connectors in specific order |
| updated | Timestamp when record last updated |
| disabled | If set to 1, connector will be considered disabled |
| note | Optional textual description |
Table cfg_connectoroptions
Additional connector configuration options.
Only read by EMG, never updated.
| Column | Description |
|---|---|
| id | Auto-generated unique id (primary key) |
| connectorid | Connector id |
| keyorder | Numeric sort order value that can be used to sort options in specific order |
| updated | Timestamp when record last updated |
| disabled | If set to 1, connector will be considered disabled |
| keyname | Connector option name |
| value | Connector option value |
| note | Optional textual description |
Table cfg_general
General configuration options.
| Column | Description |
|---|---|
| id | Auto-generated unique id (primary key) |
| keyorder | Numeric sort order value that can be used to sort options in specific order |
| keyname | General option name |
| value | General option value |
| updated | Timestamp when record last updated |
Table cfg_plugins
Plugin configuration.
| Column | Description |
|---|---|
| id | Auto-generated unique id (primary key) |
| name | Plugin name |
| instances | Number of plugin instances |
| library | Path to plugin file in file system |
| config | Optional config option |
| offset | Offset |
| updated | Timestamp when record last updated |
| disabled | If set to 1, connector will be considered disabled |
| note | Optional textual description |
| dbprofile | Database profile info to send to plugin method “create_config”. Added in EMG 7.1. |
Table cfg_satpools
SAT pool configuration.
| Column | Description |
|---|---|
| id | Auto-generated unique id (primary key) |
| name | SAT pool name |
| addressrange | Address range for pool |
| threaded | Threaded conversations flag |
| quoted_reply | Quoted replies flag |
| ignore_destaddr | Ignore destaddr flag |
| random | Random flag |
| expire | Expire time for SAT pool entries, in seconds |
| updated | Timestamp when record last updated |
| disabled | If set to 1, connector will be considered disabled |
| note | Optional textual description |
Table connectorlog
Connector log.
Normally not enabled.
Table emgsystem
System information, such as schema version.
| Column | Description |
|---|---|
| keyname | Key |
| value | value |
Keys used:
| Key | Description |
|---|---|
| emgschema | EMG schema versio, for example “37” |
| dbconfig_lastok | Timestamp for last successfully generated configuration |
| dbconfig_lastok_X | Timestamp for last successfully generated configuration in a multi-node environment (X=Node id) |
| hourly_summary_last_ts | Used by “hourly_summary.pl” aggregation script to keep track of last message timestamp (used from script version 42720) |
Table emguser
EMG accounts
| 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 sessions | |
| 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 charge_balance or 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 | |
| forcesourceaddr | 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 | |
| mode | Can be set to “RX” to force user to bind as a “receiver” (only receive, not send messages). | |
| cert_fingerprint | If set any fingerprint for a certificate presented by user must match. | |
| min_vp | If user sends a value for validity period less than min_vp it will be adjusted to min_vp. | |
| max_vp | If user sends a value for validity period larger than max_vp it will be adjusted to max_vp. Added in EMG 7.1. | |
| dlr_delay | Optional delay in seconds to hold dlr before forwarding it to user. Added in EMG 7.1. | |
| charge_balance_postpaid_limit | Lower limit for charge_balance when customer is portpaid. 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.
| Column | Description |
|---|---|
| useraccessid | Auto-generated unique id (primary key) |
| userid | Reference to emguser.id |
| ipaddress | IP address |
| ipstr | IP address as text (must be used for IPv6) |
| ipwidth | Network mask, bits (0 = Any, 24 = IPv4 C-net, 32 = IPv4 host) |
| connector | Connector name, “*” for any |
Table messagebody
Message body.
| Column | Description |
|---|---|
| id | EMG message id (primary key) |
| data | Message data, hex encoded |
Table messageoption
Additional message options.
| Column | Description |
|---|---|
| useraccessid | Auto-generated unique id (primary key) |
| id | EMG message id |
| optionkey | Message option key |
| data | Message 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.
| Column | Description |
|---|---|
| id | Auto-generated unique id (primary key) |
| created | Timestamp when record created, second precision |
| msecs | Millisecond precision |
| trn | Transaction number, when available |
| rw | Set to “R” when operation read and “W” when operation written |
| connector | Connector name |
| instance | Connector instance number |
| operation | PDU operation |
| status | PDU status |
| pdu | Textual representation of pdu data |
Table routelog
Message log.
All columns are written by EMG.
| Column | Description |
|---|---|
| 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, 5 = Delivery report) |
| 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 |