SMS Server Tools 3
This site is hosted by Kekekasvi.com
 Menu
Basic information:
Additional information:
Support:
Get SMS Server Tools 3:
Additional Options

 Sponsored links

 Search
Custom Search

 Visitor locations
 
 SMS Server Tools 3 Community
Welcome, Guest. Please login or register. Thu Mar 28, 2024 08:38
SMSTools3 Community » Sample scripts / setups Bottom

Sample eventhandler to store messages into SQL database

  This topic is locked

Page:  1

Author Post
Member
Registered:
May 2009
Location: Philippines
Could anyone post sample scripts for the eventhandler if I intend to store the incoming/outgoing messages into an sql database?

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
Here, this one stores also message body, script in the package (scripts/mysmsd) does not.

#!/bin/bash

# This is an example script that logs all events into an SQL database

SQL_HOST=localhost
SQL_USER=root
SQL_PASSWORD=""
SQL_DATABASE=smsd
SQL_TABLE=sms_log

DATE=`date +"%Y-%m-%d %H:%M:%S"`
#Extract data from the SMS file
FROM=`formail -zx From: < $2 | sed 's/"//g'`
TO=`formail -zx To: < $2`
#Remove plus sign, spaces, minus and short number prefix
TO=`echo "$TO" | sed 's/ //g' | sed 's/+//g' | sed 's/s//g' | sed 's/-//g'`
SUBJECT=`formail -zx Subject: < $2`
SENT=`formail -zx Sent: < $2`
TEXT=`sed -e '1,/^$/ d' < $2`
TEXTSQL=$(echo "$TEXT" | sed 's/\\/\\\\/g' | sed 's/\"/\\\"/g')

#Set some SQL parameters
if [ "$SQL_PASSWORD" != "" ]; then
  SQL_ARGS="-p$SQL_PASSWORD";
else
  SQL_ARGS="";
fi
SQL_ARGS="-h $SQL_HOST -u $SQL_USER $SQL_ARGS -D $SQL_DATABASE -s -e"

#Insert a new entry into the SQL table

if [ "$1" = "FAILED" ] || [ "$1" = "SENT" ]; then
   mysql $SQL_ARGS "insert into $SQL_TABLE (type,sent,sender,receiver,msgid,text) values (\"$1\",\"$DATE\",\"$FROM\",\"$TO\",\"$3\",\"$TEXTSQL\");";
elif [ "$1" = "RECEIVED" ]; then
   mysql $SQL_ARGS "insert into $SQL_TABLE (type,sent,received,sender,receiver,text) values (\"RECEIVED\",\"$SENT\",\"$DATE\",\"$FROM\",\"$SUBJECT\",\"$TEXTSQL\");";
elif [ "$1" = "REPORT" ]; then
   #Extract more data from the status report file
   DISCHARGE=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Discharge_timestamp:`
   MSGID=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Message_id:`
   STATUS=`sed -e 1,/SMS\ STATUS/d < $2 | formail -zx Status: | cut -f1 -d,`

   if [ "$MSGID" != "" ]; then
     ID=`mysql $SQL_ARGS "select id from $SQL_TABLE where receiver=\"$FROM\" and type=\"SENT\" and msgid=\"$MSGID\" order by id desc limit 1;"`
     mysql $SQL_ARGS "update $SQL_TABLE set received=\"$DISCHARGE\",status=\"$STATUS\" where id=\"$ID\";"
   fi

fi
 
 
'bash' Syntax Highlight powered by GeSHi


Member
Registered:
May 2009
Location: Philippines
Topic owner
Thanks for the quick reply! There are lots of new info I can learn from them.

Member
Registered:
Jun 2009
Location: Semarang/JawaTengah, Indonesia
Keke,

thanks for that MySQL example script. But could you help me by providing a simple code for storing an incoming SMS into PostgreSQL?

is it possible using postgresql with smstools3?

thanks alot

-Iwan P-

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
iwanpc wrote
is it possible using postgresql with smstools3?

Yes it is possible. You can use psql command to perform any actions required.

I do not use PostgreSQL and do not have it installed on any of my servers. I used Google to find out some information.

From the eventhandler script, psql can be run like this:
sudo -u postgres psql -U username -c "SQL Command;"

The documentation of PostgreSQL (here) gives the following sample:
INSERT INTO products (product_no, name) VALUES (1, 'Cheese');

It's very similar than MySQL syntax, however is uses single quotation marks. Probably double quotation marks will work too, but I'm not sure. You could change your SQL safe text to escape single quotation marks, like this:
TEXTSQL=$(echo "$TEXT" | sed 's/\\/\\\\/g' | sed "s/'/\\\'/g")

Arguments can be simply:
SQL_ARGS="-U $SQL_USER"

Your SQL command can then be something like this:
sudo -u postgres psql $SQL_ARGS "INSERT INTO $SQL_TABLE (type,sent,received,sender,receiver,text) VALUES ('RECEIVED','$SENT','$DATE','$FROM','$SUBJECT','$TEXTSQL');"

You should refer to the documentation of PostgreSQL to get more information. A command man psql is a good choice too.

While writing and testing an eventhandler, first run it manually to see possible error messages. If you give it to the smsd, you will not see error messages and therefore debugging is impossible.

Member
Registered:
Jun 2009
Location: Semarang/JawaTengah, Indonesia
Thanks Keke for the sample:)

I'll try this code for my PostgreSQL. So the key is using eventhandler script.

-Iwan P-

Member
Registered:
Jun 2009
Location: Semarang/JawaTengah, Indonesia
Keke,

below is your script which I've modified for PostgreSQL:

#!/bin/sh


if [ "$1" != "RECEIVED" ]; then exit; fi;

#DB parameter
SQL_HOST=localhost
SQL_USER=enterprisedb
SQL_PASSWORD=
SQL_DATABASE=MitraLunas
SQL_TABLE=smsmasuk

#Extract data dari SMS file
FROM=`formail -zx From: < $2`
TEXT=`formail -I "" <$2 | sed -e"1d"`

#sekedar dipastikan biar gak ada karakter aneh
TEXTSQL=$(echo "$TEXT" | sed 's/\\/\\\\/g' | sed "s/'/\\\'/g")

#Ini kunci SQL Query
sudo -u $SQL_USER psql -U $SQL_USER -d MitraLunas -c "INSERT INTO $SQL_TABLE (Jenis,Sent,Received,Sender,Subject,Isi) VALUES ('RECEIVED','$SENT','$DATE','$FROM','$SUBJECT','$TEXTSQL');"

----end of script----

after modify the script, i started sms3 service and execute the script. but i can't store the incoming sms into the table.

Something missing in my concept?

-Iwan P-

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
You did run this script manually, right?:

/path/to/your/smsd_eventhandler RECEIVED /path/to/sample/incoming/file

What was the error message?

Member
Registered:
Oct 2009
Location: Latvia
What would be the right structure for sms_log table?
Especialy datatypes/sizes.

I used following one:


I modified in example following field names sent_time and received_time.
I can get messages into table, but there is no values in receiver and sender fields.

Member
Registered:
Oct 2009
Location: Latvia
The next thing that I tried is to get correct accentuated message into a table.
I tried to do conversion with CONVERT(\"$TEXTSQL\" USING utf8 ).
But without success.
:(

Here is my:
Table


Here is my event handler file:


Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
I think It should work if you just store messages using the UTF-8 character set.

Your MySQL Server probably uses UTF-8 Unicode as MySQL Charset already. When you create a table, you can use a collation utf8_general_ci, or utf8_latvian_ci which is available too.

As you had no problems with SMS files, and you locale was UTF-8, you could try to read a message back from the database (with a suitable script) and compare the result.

Probably you had a problem when viewing a database. What tool have you used for viewing?

Member
Registered:
Oct 2009
Location: Latvia
Ups...
Sorry you are right.
I used phpmyadmin. While its page encoding tags shows:
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
It seems, that it do some conversion while retrieving data.
I tested with mysql command line client and it looks good.

But I still could not get receiver/sender into table.

Administrator
Registered:
May 2009
Location: Jyväskylä, Finland
You probably have this already solved, but if not, check the following:

You are using /bin/sh as a shell, but the script is using the syntax of bash shell. In many cases /bin/sh is a symbolic link to the bash, but not always. Check what ls -l `which sh`says . In some machine it gives:
lrwxrwxrwx 1 root root 4 2009-08-30 15:36 /bin/sh -> dash
and bash syntax does not work.

Also check that you have formail command available.

 While writing and testing an eventhandler, first run it manually to see possible error messages. If you give it to the smsd, you will not see error messages and therefore debugging is impossible. 

Member
Registered:
Oct 2009
Location: Latvia
Thanks for your reply!
Yes the formail was the problem.

  This topic is locked

Page:  1

SMSTools3 Community » Sample scripts / setups Top

 
Time in this board is UTC.  

Privacy Policy   SMS Server Tools 3 Copyright © Keijo Kasvi.