SMSServer - Database Interface

Introduction

With 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 database interface was developed and tested with:

Microsoft SQL Server & SQL Server Express.
Oracle 10g Express.
MySQL v5.10.
Derby database.

It was also reported to work with PostgreSQL.

Database structure

Inbound SMS Table

This table should be named smserver_in, unless specified otherwise in the configuration file. SMSServer will read inbound messages from your gateway(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 sets this field to 0. You can use this field for your own purposes.
originator	CHAR(16)	NOT NULL	The originator of the received message.
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 Table

This 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 gateway(s). The table structure should be as follows:

Field Name	Type	Technical Info	Description
id	INT	Autonumber/identity field	The table's primary key.
type	CHAR(1)	NOT NULL	The message type. This should be "O" for normal outbound messages, or "W" for wap si messages.
recipient	CHAR(16)	NOT NULL	The recipient's number to whom the message should be sent.
text	CHAR(xxx)	NOT NULL	The message text.
wap_url	CHAR(xxx)	NOT NULL for WAP SI messages!	The WAP SI URL address.
wap_expiry_date	DATETIME	NULL	The WAP SI expiry date. If no value is given, SMSServer will calculate a 7 day ahead expiry date.
wap_signal	CHAR(1)	NULL	The WAP SI signal. Use "N" for NONE, "L" for LOW, "M" for MEDIUM, "H" for HIGH, "D" for DELETE. If no value/invalid value is given, the NONE signal will be used.
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 Table

This 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(16)	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 File

This 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, "oracle" for Oracle.
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

Sample database creation files

You can find sample database creation scripts in the "misc\SMSServer Database Scripts" directory.