Use SMS Fox Box with Mysql Backend

The SMS Fox Box internal database is SQLite. This engine offers really good performance and supports databases up to 2 TB in size. You would say: "really good, but my system is based on MySQL!", this is not a matter with MySMS module!

The main purpose of this module is grant you the capability to automatically divert all your message to your favourite database.
As all other processes also this one is controlled by the main event handler.
The code below is the call to the module from event handler and it is a portion of it

To activate/deactivate the module you always have to remember to remove/add the # sharp character as described in the Event Handler how to.

The configuration is fully web based, and you can access it by clicking on "MySQL Module" entry. Below is listed all what you need to complete setup:
- A MySQL socket listening on default port on your server with permission setted up to accept incoming connection from the IP of     SMS FoxBox [more info...]
- The IP/hostname of your MySQL Server
- The Username to access the table with enough permission to perform (SELECT, INSERT).
- The password you setted up for the user, remember to verify password style. [more info...]
- The name of database where we have to perform the INSERT query.
- You can choose to store messages only on MySQL or on both database (internal SQLite and Mysql).

After you finished filling up required fields choose "Save Configuration" to apply changes.

Your MySQL Database should contain a table named "messages" with the following fields [times,froms,tos,subject,sent,texts].
The insert will populate this fields with Time when the message was received by SMS FoxBox, Sender phone number, Receivers phone number, The logical name of the modem that received the message, Time when message was sent and the TEXT of the message.

The INSERT is called by a script that "parse" the SMS File for extract values and pass it as argument at a binary file that perform the real insert.
The parser is located in /etc/sms/scripts/mysmsd and the code is reported below:


DATE=`date +"%Y-%m-%d %H:%M:%S"`
#Extract data from the SMS file
FROM=`/etc/procmail/formail -zx From: < $2 | sed 's/"//g'`
TO=`/etc/procmail/formail -zx To: < $2`
SUBJECT=`/etc/procmail/formail -zx Subject: < $2`
SENT=`/etc/procmail/formail -zx Sent: < $2`
#Text is not used but could be used
TEXT=`/etc/procmail/formail -I "" <$2`
#CALL the binary
/etc/sms/mysms "$DATE" "$FROM" "$TO" "$SUBJECT" "$SENT" "$TEXT"

For the SMS processing we used a PROCMAIL utility called FORMAIL more info are available on their website.