Introduction: SEND SMS TCPIP GATEWAY - PHP, PYTHON and MySQL With RPI and A-gsm Shield

By THE ALPHABET PROJECT
About: electronics, software, ....

Some time ago, one of my neighbors, who owns a online ticket business, told me about a encountered problem. They needs to check, at the users registration time, their phone numbers. He intended to implement a process that send via SMS a registration PIN to the new users and, those one must fill the received PIN code into registration interface.

Our dialogue has happen in the break between rounds of a football match played in UEFA Champions League. I did not remember the second team, but one was for sure Bayern Munich....and Bayern won.

The very next morning, I showed to my neighbor my SEND SMS TCP/IP(HTTP) GATEWAY... 100% success!

Later, I used the same project, in order to build and integrate WEB application with various SMS driven appliances as the one that is published here.

Step 1: Gathering the Parts Together

  1. Raspberry PI model B+ or Raspberry PI2 model B * 1 pcs http://raspberrypi.org
  2. power supply for Raspberry PI (5V, 2A) 1 pcs
  3. keyboard + HDMI display (after initial setup, those are not needed)
  4. 100BT (wired) lan / internet connection (connect RPI to your lan)
  5. uSD card 4 RPi (8Gb or more) 1 pcs.
  6. a-gsm shield v2.064 1 pcs. http://itbrainpower.net/a-gsm/
  7. GSM (2G) SIM card 1 pcs
  8. power supply for the a-gsm shield (5V, ~1A... any decent Android phone/iPhone power adapter with uUSB type B connector ...I used the power adapter from the KINDLE belonging to my wife) 1 pcs
  9. connection wires
  10. ...some time to bake all ingredients (around 2-4 hours, if you start from scratch) :))

* Raspberry PI A+ can be used as well, but you must hack the wiring. For the Raspberry PI Zero users, see here how to add the network interface.

Step 2: How It Works (the Magic Revealed)

Before start to put all stuffs together, spend couple minutes to understand the logic behind the software.

The system implements 2 parallel processes:

one I/O system that injects SMS data into the MySQL database (WEB -PHP based or direct MySQL socket connection - see initiation methods bellow)

and,

one Python based "send SMS" process. This PYTHON process will make iterative query to the local MySQL database (SMS table), in order to extract the new messages/destinations pairs. When new pairs are available, the SMS(s) will be send by same process and the SMS table is updated. Bellow, detailed:

....
while(1):
    db = MySQLdb.connect(dbserver, dbuser, dbpass, dbname)
    curs=db.cursor()
    curs.execute("""SELECT `destinationNumber`, `smsContent`,`id` FROM `SMS` WHERE `sendTimestamp` < 1;""")
    for readline in curs.fetchall():#process each returned SMS task
        message = checkMessage(readline[1])
        res = sendSMS(readline[0], "129", message)#"don't care what" format numbers
        if res==0:
            print("SMS has been sent with succes")
            curs.execute("UPDATE `SMS` SET `sendTimestamp` = %s WHERE `SMS`.`id` =%s LIMIT 1 ;", (str(long(time())),str(readline[2])));
            db.commit()
        else:#to do how to handle error 4 not blocking process in wrong number case
            print("SMS error")
        sleep(0.5)
    db.close()
    sleep(5)#wait a while bw. cycles. let the RPI breathe...

Two SMS initiation (injection) methods can be used:

a. remotely writing the destination number and the message directly in the RPI's MySQL database (socket based connection). Example: 

"INSERT INTO `SMS` (`destinationNumber`,`message`) VALUES ('+40123456789','My first test message');"

b. accessing GET/POST WEB service that runs on RPI. GET example:

"http://myrpi_ip/injectSMS?number=%2B40123456789&message=My%20first%20test%20message"

The result of SMS sending process and send timestamp updates the message field in the SMS table.

Step 3: Putting the Hardware Together

Cable the boards together using the connecting wires as shown in first picture.

In second line, left picture, you can see a brief recap of logical wiring and of the role of each connection.

In third picture (second line, right picture), observe also the powering cables.

Additional hardware references and some interfacing alternatives >> Raspberry PI and a-gsm shield interfacing.


One question from my side: do you the enjoy bubble plastic sheet?.. I love the RED one! :))

Step 4: Preparing the Raspberry PI OS

Raspberry PI... Some considerations, first:
All commands must be run as root or using roots right. "sudo your_command" can be a good solution

Raspberry PI. Installing operating system. Some setup steps.

  1. download and install Raspbian image. SDFORMATER and WIN32DISKIMAGER utilities can help on this...google for them.
  2. running "sudo raspi-config" you may like enable your root access to RPi
  3. expand the filesystem (some SD space it is not allocated when you write the OS image)
  4. chose runlevel 3 (console text) for your RPi
  5. proper set your eth0 configuration. Setting a static IP address may be useful. (again google.com can help)
  6. do not forget: to change/set passwords for root and pi user
  7. enable sshd

BTW...if you would like to install Midnight Commander (for the sake of old and glorious days ), just run:

sudo apt-get -y install mc

Make available the RPi serial for connection with a-gsm. You can use vi or mcedit or any other editor at your convenience. Comment last "/etc/inittab" line: 

#T0:23:respawn:/sbin/getty -L ttyAMA0 115200 vt100

reboot your RPi:

"reboot" or "restart now"

Step 5: Install the Requiered Linux Distribution Packages (PHP, MySQL, Python + Dependencies)

What to do:

  1. update install repositories.
  2. install Apache2.
  3. install MySQL.
  4. install Python to mysqldb connector.
  5. install PHP. Run:
  6. and finally install PHP to mysqldb connector.

All together next:

sudo apt-get update
sudo apt-get install -y apache2
sudo apt-get install -y mysql-server
sudo apt-get install -y python-mysqldb
sudo apt-get install -y php5
sudo apt-get install -y php5-mysql

Check installed python modules (rpi.gpio, serial, python-mysqldb...). 

dpkg -l | grep python

can help you. Update/install if needed.

Now you have all required packages installed into your RPI.

Step 6: Install the MySQL Tables

Anyway, I suppose you know how to use the "mysql" command (interact with your MySQL server). Else, you can google on this subject. Also, one good idea can be to install phpmyadmin, first...again query on google.

Preview of the SQL script that needs to be run in order to create tables (also included in project download files):

CREATE DATABASE `agsm-sms` ;
CREATE TABLE IF NOT EXISTS `agsm-sms`.`SMS` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`destinationNumber` varchar(20) NOT NULL,
`smsContent` varchar(160) NOT NULL,
`createTimestamp` int(11) NOT NULL DEFAULT '0',
`sendTimestamp` int(11) NOT NULL DEFAULT '0',
`errorCode` int(2) NOT NULL DEFAULT '-1',
PRIMARY KEY (`id`),
KEY `destinationNumber` (`destinationNumber`),
KEY `createTimestamp` (`createTimestamp`),
KEY `sendTimestamp` (`sendTimestamp`),
KEY `errorCode` (`errorCode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='SMS pooling table' AUTO_INCREMENT=1 ;

CREATE USER 'agsmSMSprocess'@'localhost' IDENTIFIED BY 'RPi_SMSDB_password_local';
GRANT USAGE ON * . * TO 'agsmSMSprocess'@'localhost' IDENTIFIED BY 'RPi_SMSDB_password_local' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
REVOKE ALL PRIVILEGES ON `agsm-sms` . * FROM 'agsmSMSprocess'@'localhost';
GRANT SELECT , INSERT , UPDATE ON `agsm-sms` . * TO 'agsmSMSprocess'@'localhost';
FLUSH PRIVILEGES ;

############################################################################
#Remove comment on next lines ONLY if you like to use MySQL remote inject!!#
#Be sure YOU ARE KNOWING what you are doing! Check also the my.ini file... # 
############################################################################
#CREATE USER 'remoteInsertSMS'@'192.168.xxx.yyy' IDENTIFIED BY 'RPi_SMSDB_password_remote';
#GRANT USAGE ON * . * TO 'remoteInsertSMS'@'192.168.xxx.yyy' IDENTIFIED BY 'RPi_SMSDB_password_remote' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
#REVOKE ALL PRIVILEGES ON `agsm-sms` . * FROM 'remoteInsertSMS'@'192.168.xxx.yyy';
#GRANT SELECT , INSERT , UPDATE ON `agsm-sms` . * TO 'remoteInsertSMS'@'192.168.xxx.yyy';
#FLUSH PRIVILEGES ;

- edit SMSdb.sql, line 16 and 17....change the agsmSMSprocess password, just after IDENTIFIED BY. Remember this password in order to modify it in python and php config files (see in next steps).

- edit SMSdb.sql, line 22-26, in order to enable remote MySQL connection (inject SMSs socket queries). DO THIS ONLY IF YOU KNOW WHAT YOU ARE DOING! YOU HAVE BEEN WARNED!

- load/run SMSdb.sql into the MySQL

Step 7: Inject SMS WEB Service Interface Installation

Just copy the php files into the root of the Apache server (default /var/www/html)

Edit param.php file, updating the "agsmSMSprocess" password ($pass) , if needed

$clientdb='agsm-sms';
$sqlserver='localhost';
$sqluser='agsmSMSprocess';
$sqlpass='PASSWORD_HERE';

Check the installation, accessing the WEB interface from one browser (use your RPi IP, number and message):

"http://myRPi_IP/injectSMS.php?number=%2B44123456789&message=My%20first%20test%20message"
or, 
"http://myRPi_IP/injectSMS.php?number=0044123456789&message=My%20first%20test%20message"
or, 
"http://myRPi_IP/injectSMS.php?number=44123456789&message=My%20first%20test%20message"
or, 
"http://myRPi_IP/injectSMS.php?number=0123456789&message=My%20first%20test%20message"

depending to the SMS number format accepted by your NMO! Test this behavior manually (the SMS format accepted), before use it! Else, the SMS gateway will cycle trying to send the very same SMS reporting error. If you have an SMS inserted with number in unsupported format, you must manually delete that record from the SMS table (or install and use phpmyadmin tool).

IMPORTANT:

If international number format will be used, for "number" parameter, instead leading "+", always use "%2B" (as in first example), else unsupported number format error (described before) will rise.

Step 8: PYTHON Based SMS Processing Module Installation

Folks, we are really close....

Run on shell:

mkdir /home/pi/agsmSendSMSgw

Copy the python files from downloaded archive in previously created folder

Edit the parMySQLconnect.py and update the "agsmSMSprocess" password, if needed:

dbserver = 'localhost'
dbname = 'agsm-sms'
dbuser = 'agsmSMSprocess'
dbpass = 'ESVnA2nqmnscMtHU'

Step 9: Let's Test This Beauty

Check the SMS table to see the the SMS(s) previously inserted, as in first picture.

Observe the values stored in sendTimestamp and the errorCode fields.


Start the Python SMS SEND module:

cd /home/pi/agsmSendSMSgw
sudo python sendSMSgw.py

The application will start.

If application raises MySQL related errors you must check MySQL / database / user+password, else you will notice "SMS has been sent with success" after sending the SMS(s) tasks already stored in the database. See the 4'th picture(left on the last pictures row).

In the 5'th picture (middle in the last pictures row), you can notice how the table will look like after SMS was sent.

Observe the updated values in sendTimestamp and the errorCode fields.

Step 10: Make the SMS SEND Service to Start at Boot

Edit /etc/rc.local.

sudo vi /etc/rc.d

or

sudo mcedit /etc/rc.d

Add the following line at the bottom of the file

sudo python /home/pi/agsmSendSMSgw/sendSMSgw.py

Save. Reboot your RPI.

Step 11: Download SEND SMS LAN GATEWAY PHP, Python and MySQL Code. Credits. Bonus.

The PHP, Python and MySQL code can be downloaded from here:

RASPBERRY PI SEND SMS LAN GATEWAY full package

Your email address and the IMEI of your a-gsm modem it is required for this operation.

Project originally published by me on projects and how to section of my site.

A little bonus/add-on for the my SMS DRIVEN RELAYS fans.

Here it's sample web code that allows you to remote control 2 relays:

"<body>
.......
<!--code start here-->
<form method="GET" action="http://myRPi_IP/injectSMS" id="myForm">
<select id="destination" name="destination">
	<option value="" default>..choose SMS DRIVEN RELAY destination number</option>
	<option value="+40745xyzabc">Relay friendly name ONE</option>
	<option value="0745xyzabc">Relay friendly name ONE HUNDRED</option>
</select>
<select id="RELAY0">
	<option value="" default>..choose RELAY 0 status</option>
	<option value="1">ACTIVATED</option>
	<option value="0">RELEASED</option>
</select>
<select id="RELAY1" >
	<option value="" default>..RELAY 1 status</option>
	<option value="1">ACTIVATED</option>
	<option value="0">RELEASED</option>
</select>
<input type="hidden" value="" id="message" name="message" ></input>
<script>
function sendCommand(){
	if(document.getElementById("destination").value==""){alert("empty detination"); return;}
	if(document.getElementById("RELAY0").value==""){alert("RELAY0 status not set"); return;}
	if(document.getElementById("RELAY1").value==""){alert("RELAY1 status not set"); return;}
	document.getElementById("message").value = document.getElementById("RELAY0").value+","+document.getElementById("RELAY1").value;
	document.getElementById("myForm").submit();
}
</script>
<input type="button" value="SEND COMMAND" onclick="sendCommand();"></input>
</form>
<!--code start here-->
....
</body>"

Anyway, you have to edit your RPI IP address, SMS DRIVEN RELAY IP address......

I don't want to keep you apart from all the fun. Play, patch and have fun!