Could anyone post sample scripts for the eventhandler if I intend to store the incoming/outgoing messages into an sql database?

Here, this one stores also message body, script in the package (scripts/mysmsd) does not.

Thanks for the quick reply! There are lots of new info I can learn from them.

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-

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.

Thanks Keke for the sample

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

-Iwan P-

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-

You did run this script manually, right?:

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

What was the error message?

What would be the right structure for sms_log table?
Especialy datatypes/sizes.

I used following one:
CREATE TABLE `sms_log` ( `id` bigint(20) NOT NULL auto_increment, `type` varchar(10) NOT NULL, `sent_time` varchar(20) default NULL, `received_time` varchar(20) default NULL, `sender` varchar(30) default NULL, `receiver` varchar(30) default NULL, `msgid` varchar(20) default NULL, `text` varchar(255) character set utf8 collate utf8_unicode_ci default NULL, `status` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

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.

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
CREATE TABLE `sms_log` ( `id` bigint(20) NOT NULL auto_increment, `type` varchar(10) NOT NULL, `sent_time` varchar(20) default NULL, `received_time` varchar(20) default NULL, `sender` varchar(30) default NULL, `receiver` varchar(30) default NULL, `msgid` varchar(20) default NULL, `text` varchar(255) default NULL, `status` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM CHARACTER SET ucs2 COLLATE ucs2_unicode_ci;

Here is my event handler file:
#!/bin/sh if [ "$1" == "RECEIVED" ]; then if sed -e '/^$/ q' < "$2" | grep "^Alphabet: UCS2" > /dev/null; then TMPFILE=`mktemp /tmp/smsd_XXXXXX` sed -e '/^$/ q' < "$2" | sed -e 's/Alphabet: UCS2/Alphabet: UTF-8/g' > $TMPFILE sed -e '1,/^$/ d' < "$2" | iconv -f UNICODEBIG -t UTF-8 >> $TMPFILE mv $TMPFILE "$2" fi fi # This is an example script that logs all events into an SQL database # You need a MYSQL database as described in the documentation. # Please read the documentation before using this script. 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\",CONVERT(\"$TEXTSQL\" USING utf8));"; elif [ "$1" = "RECEIVED" ]; then mysql $SQL_ARGS "insert into $SQL_TABLE (type,sent,received,sender,receiver,text) values (\"RECEIVED\",\"$SENT\",\"$DATE\",\"$FROM\",\"$SUBJECT\",CONVERT(\"$TEXTSQL\" USING utf8));"; 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

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?

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.

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. 

Thanks for your reply!
Yes the formail was the problem.