IntroductionWith this interface, SMSServer uses three database tables to do its job: - One for storing inbound messages received
- One for sending the outbound messages
- A third one for logging the voice calls received.
SMSServer was developed with Microsoft SQL Server in mind. However, you could use whatever database you want, as long as a compliant JDBC driver is available. Furthermore, you should review all the SQL statements inside SMSServer to see if they require modifications for your database. Inbound SMS TableThis table should be named smsserver_in , unless specified otherwise in the configuration file. SMSServer will read inbound messages from your modem(s) and will insert them in this table. The table structure should be as follows: | Field Name | Type | Technical Info | Description | | id | INT | Autonumber/identity field | The table's primary key. | | process | INT | NOT NULL | When new rows (i.e. messages) are created, SMSServer set this field to 0. You can use this field for your own purposes. | | originator | CHAR(16) | NOT NULL | The originator of the received message. International format, no leading "+" or zeroes. | | type | CHAR(1) | NOT NULL | "I" for inbound message, "S" for status report message. | | encoding | CHAR(1) | NOT NULL | "7" for 7bit, "8" for 8bit and "U" for Unicode/UCS2. | | message_date | DATETIME | NOT NULL | The message date (retrieved by the message headers). | | receive_date | DATETIME | NOT NULL | The datetime when message was received. | | text | CHAR(xxx) | NOT NULL | The body of the message. | | original_ref_no | CHAR(64) | NULL | Available only for status report messages: refers to the RefNo of the original outbound message. | | original_receive_date | DATETIME | NULL | Available only for status report messages: refers to the receive date of the original outbound message. | | gateway_id | CHAR(64) | NOT NULL | The ID of the gateway from which the message was received. |
- For each message received, SMSServer will create a row in this table. SMSServer will never delete rows from this table, so if you want to clean-up or anything this is your responsibility.
Outbound SMS TableThis table should be named smsserver_out , unless specified otherwise in the configuration file. SMSServer will read rows from this table and will dispatch them from your modems. The table structure should be as follows: | Field Name | Type | Technical Info | Description | | id | INT | Autonumber/identity field | The table's primary key. | | recipient | CHAR(16) | NOT NULL | The recipient's number to whom the message should be sent. International format, no leading "+" or zeroes. | | text | CHAR(xxx) | NOT NULL | The message text. | | create_date | DATETIME | NOT NULL, Default value: current date/time | The datetime when this record was created. | | originator | CHAR(16) | NOT NULL, Default value: '' | The originator. Normally you should leave this blank. | | encoding | CHAR(1) | NOT NULL, Default value: '7' | "7" for 7bit, "8" for 8bit and "U" for Unicode/UCS2. | | status_report | INT | NOT NULL, Default value: 0 | Set to 1 if you require a status report message to be generated. | | flash_sms | INT | NOT NULL, Default value: 0 | Set to 1 if you require your message to be sent as a flash message. | | src_port | INT | NOT NULL, Default value: -1 | Set to source port (for midlets) | | dst_port | INT | NOT NULL, Default value: -1 | Set to destination port (for midlets) | | sent_date | DATETIME | NULL | The sent date. This field is updated by SMSServer when it sends your message. | | ref_no | CHAR(64 | NULL | The Reference ID of your message. This field is updated by SMSServer when it sends your message. | | priority | INT | NOT NULL, Default Value 0 | Lower (or negative) values mean lower priority than higher (or positive) values. By convention, a priority of a value 0 (zero) is considered the normal priority. High priority messages get sent first than others. | | status | CHAR(1) | NOT NULL, Default value "U" | "U" : unsent, "Q" : queued, "S" : sent, "F" : failed. This field is updated by SMSServer when it sends your message. If set in the configuration file, this field takes extra values depending on the received status report message: "D" : delivered, "P" : pending, "A" : aborted. | | errors | INT | NOT NULL, Default value: 0 | The number of retries SMSServer did to send your message. This field is updated by SMSServer. | | gateway_id | CHAR(64) | NOT NULL, Default value is the star character | Set it to the star character if you want to leave to SMSServer the decision as to which gateway to use to send your message. Set it to a specific Gateway ID to force SMSServer to send your message via this gateway. |
- If you want to instruct SMSServer to send out some messages, create the necessary records in this table. SMSServer will check this table and send out all messages queued in it.
- SMSServer will never delete rows from this table - it will just update the ref_no / sent_date / status / errors / gateway_id fields. If you want to clean-up or anything this is your responsibility.
- Binary messages should be saved in ASCII/hex format, for example a binary message "1234" should be saved as "30313233".
Voice Calls TableThis table should be named smsserver_calls , unless specified otherwise in the configuration file. SMSServer will use this table for logging all voice calls it receives (and eventually drops). SMSServer always creates and never deletes records in this table. The table structure should be as follows: | Field Name | Type | Technical Info | Description | | id | INT | Autonumber/identity field | The table's primary key. | | call_date | DATETIME | NOT NULL | The date/time of the received call. | | gateway_id | CHAR(64) | NOT NULL | The id of the gateway which received the call. | | caller_id | CHAR(64) | NOT NULL | The caller id. |
- SMSServer will never delete rows from this table - it will just keep inserting new records. If you want to clean-up or anything this is your responsibility.
Configuration FileThis interface requires the following configuration settings: | Option | Description | | url | The URL of the database which hosts SMSServer tables. | | driver | The database driver class. | | username | The database username. | | password | The database password. | | type | Allowed values: "mssql" for MS SQL Server, "mysql" for MySQL. | | tables.sms_in | This is the name of the "smsserver_in" table, should you choose to name it differently. | | tables.sms_out | This is the name of the "smsserver_out" table, should you choose to name it differently. | | tables.calls | This is the name of the "smsserver_calls" table, should you choose to name it differently. | | batch_size | Maximum number of messages to send from the database at each processing cycle | | retries | This setting defines the number of retries SMSServer should give a failing-to-be-sent message before it decides to really mark it as a "failed" one and leave it aside. | | update_outbound_on_statusreport | Default is "no". If set to "yes", SMSServer will automatically update the status field of the outbound table (smsserver_out) according to the status of the relevant status report messages it receives. So in order to activate this feature set the above setting to "yes" and make sure you request status reports for all of your outbound messages. |
Example: interface.0=db1, Database
db1.url=jdbc:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=smslib
db1.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db1.username=smslib
db1.password=smslib
db1.type=mssql
db1.tables.sms_in=smssvr_in
db1.tables.sms_out=smssvr_out
db1.tables.calls=smssvr_calls
db1.batch_size=50
db1.retries=2
db1.update_outbound_on_statusreport=yes
|
Sign in to add a comment
