SMS Tools ke Postgresql

sms_event.sh

#!/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');"

sms_log_ddl.sql

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 ;