SMSTools3 Sample scripts / setups

Hi all,

Here's my first attempt at a script to store my SMS incomings and outgoings in a MySQL/MariaDB database. I use Perl and this code is likely to go through a tidy-up. It doesn't use formail.

I wanted to store as much information as possible in the same table for both SENT and RECEIVED entries. I also wanted to store the actual message text for later processing by a separate script: hence the "processed" column. New entries are set to N(o) by default, and would be updated to P(rocessing) when being processed by the other script. When processing is complete it'll be set to Y(es).

The message text itself is stored with line-ends replaced by the " | " (pipe) symbol so that I can easily parse the message as a single string: I did this before when I used a commercial email-to-SMS gateway and it works well.

Anyway, have fun, and ignore my coding "style" - it works for me but YMMV

--
-- create table "sms_log" in database "mydb"
--
use mydb;

DROP TABLE IF EXISTS `sms_log`;
CREATE TABLE `sms_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` 	varchar(10) COLLATE ucs2_unicode_ci NOT NULL,
  `from_tel` 	varchar(20) COLLATE ucs2_unicode_ci NOT NULL,
  `from_toa` 	varchar(50) COLLATE ucs2_unicode_ci NOT NULL,
  `from_smsc` 	varchar(30) COLLATE ucs2_unicode_ci NOT NULL,
  `sent` 	datetime COLLATE ucs2_unicode_ci DEFAULT NULL,
  `received` 	datetime COLLATE ucs2_unicode_ci DEFAULT NULL,
  `modem` 	varchar(30) COLLATE ucs2_unicode_ci DEFAULT NULL,
  `IMSI` 	varchar(22) COLLATE ucs2_unicode_ci DEFAULT NULL,
  `IMEI` 	varchar(22) COLLATE ucs2_unicode_ci DEFAULT NULL,
  `report` 	varchar(3) COLLATE ucs2_unicode_ci DEFAULT NULL,
  `alphabet` 	varchar(3) COLLATE ucs2_unicode_ci DEFAULT NULL,
  `sentto` 	varchar(20) COLLATE ucs2_unicode_ci NOT NULL,
  `sending_time` varchar(20) COLLATE ucs2_unicode_ci NOT NULL,
  `text` 	varchar(2048) COLLATE ucs2_unicode_ci DEFAULT NULL,
  `msgfile` 	varchar(128) COLLATE ucs2_unicode_ci DEFAULT NULL,
  `processed` 	enum('N','P','Y') COLLATE ucs2_unicode_ci DEFAULT 'N',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_unicode_ci;
ALTER TABLE foo.sms_log ADD INDEX (processed);
ALTER TABLE foo.sms_log ADD INDEX (from_tel);

[ Select ]

[ Expand ]

#!/usr/bin/perl -w

use DBI;
use DBD::mysql;

my $transactiontype = $ARGV[0] || die;
my $smsmsessagefile = $ARGV[1];

use vars qw{$SQL_HOST $SQL_USER $SQL_PASSWORD $SQL_DATABASE $SQL_TABLE};
( $SQL_HOST, $SQL_USER, $SQL_PASSWORD, $SQL_DATABASE, $SQL_TABLE ) = ( 'localhost', 'someuser', 'somepass', 'mydb', 'sms_log' );

# Examples
# -------------------------------------------------- RECEIVED
# From: 44xxxxxxxxxx
# From_TOA: 91 international, ISDN/telephone
# From_SMSC: 44yyyyyyyyyy
# Sent: 18-04-12 10:37:27
# Received: 18-04-12 09:37:42
# Subject: GSM1
# Modem: GSM1
# IMSI: 23nnnnnnnnnnnnn
# IMEI: 86mmmmmmmm
# Report: yes
# Alphabet: ISO
# Length: 19
#
# Test 2
# -------------------------------------------------- SENT
# To: xxxxxxxxxx
# Modem: GSM1
# Sent: 18-04-23 09:29:49
# Sending_time: 5
# IMSI: 23nnnnnnnnnnnnn
# IMEI: 86mmmmmmmm
#
# THIS IS ALSO A TEST
# --------------------------------------------------

my ( $FROM, $FROMTOA, $FROMSMSC, $RECEIVED, $SUBJECT, $REPORT, $ALPHABET, $LENGTH ) = ('')x8;	# RX
my ( $SENTTO, $SENDINGTIME ) = ('')x2;								# TX
my ( $SQL, $SENT, $MODEM, $IMSI, $IMEI, $actualmessage ) = ('')x6;				# Common

if ( $transactiontype =~  /FAILED|RECEIVED|SENT/ )
	{
	open (SMS, "<$smsmsessagefile") || die "Can't open $smsmsessagefile\n";	
	binmode SMS;
	local $/;
	my $SMSDATA = <SMS>;
	close SMS;

	my $EndOfHeaders = 0;
	$SMSDATA =~ s/(\x0a)/\|/gi;

	my @splitSMSDATA = split('\|', $SMSDATA);

	foreach my $line( @splitSMSDATA )
		{
		if    ( $line =~ /^From:\s(.*)$/ )	{ $FROM = $1; }
		elsif ( $line =~ /^From_TOA:\s(.*)$/ )	{ $FROMTOA = $1; }
		elsif ( $line =~ /^From_SMSC:\s(.*)$/ )	{ $FROMSMSC = $1; }
		elsif ( $line =~ /^Sent:\s(.*)$/ )	{ $SENT = $1; }
		elsif ( $line =~ /^Received:\s(.*)$/ )	{ $RECEIVED = $1; }
		elsif ( $line =~ /^Subject:\s(.*)$/ )	{ $SUBJECT = $1; }
		elsif ( $line =~ /^Modem:\s(.*)$/ )	{ $MODEM = $1; }
		elsif ( $line =~ /^IMSI:\s(.*)$/ )	{ $IMSI = $1; }
		elsif ( $line =~ /^IMEI:\s(.*)$/ )	{ $IMEI = $1; }
		elsif ( $line =~ /^Report:\s(.*)$/ )	{ $REPORT = $1; }
		elsif ( $line =~ /^Alphabet:\s(.*)$/ )	{ $ALPHABET = $1; }
		elsif ( $line =~ /^Length:\s(.*)$/ )	{ $LENGTH = $1; }
		elsif ( $line =~ /^To:\s(.*)$/ )	{ $SENTTO = $1; }
		elsif ( $line =~ /^Sending_time:\s(.*)$/ ){ $SENDINGTIME = $1; }
		elsif ( $line =~ /^$/ && !EndOfHeaders )  { $EndOfHeaders = 1; }
		}

		my $startcounter = 0;

		if ( $transactiontype =~  /RECEIVED/ )		{ $startcounter = 13; }
		elsif ( $transactiontype =~  /FAILED|SENT/ )	{ $startcounter = 7; }

		for ( my $ctr = $startcounter; $ctr < scalar @splitSMSDATA; ++$ctr )
			{	
			my $messagechunk	= $splitSMSDATA[$ctr];
			$messagechunk	=~ s/\t/ /g;
			$messagechunk	=~ s/\s{1,}$//g;
			$messagechunk	=~ s/^\s{1,}//g;
			$actualmessage .= $messagechunk . '|'; 	
			}
		}
	else
		{ die "TYPE IS NOT RECEIVED/SENT/FAILED\n"; }
	
	my $DSN	= "DBI:mysql:database=$SQL_DATABASE;host=$SQL_HOST";
	my $user= "$SQL_USER";
	my $pw	= "$SQL_PASSWORD";
	$dbh 	= DBI->connect($DSN,$user,$pw) || die "Cannot connect: $DBI::errstr\n" unless $dbh;

	$SQL = "insert into $SQL_TABLE (type,sentto,sending_time,from_tel,from_toa,from_smsc,sent,received,modem,
			IMSI,IMEI,report,alphabet,text,msgfile,processed)
			VALUES (\"$transactiontype\",\"$SENTTO\",\"$SENDINGTIME\",\"$FROM\",\"$FROMTOA\",\"$FROMSMSC\",
			\"$SENT\",\"$RECEIVED\",\"$MODEM\",\"$IMSI\",\"$IMEI\",\"$REPORT\",\"$ALPHABET\",?,\"$smsmsessagefile\",\"N\"); ";

	$sth = $dbh->prepare($SQL);
	$sth->execute($actualmessage);	# to escape sql-unfriendly characters...
	$sth->finish;
	$dbh->disconnect;

exit 0;
# ---

[ Select ]

[ Expand ]

..and here's an extract from the table view:

          id: 50
        type: SENT
    from_tel:
    from_toa:
   from_smsc:
        sent: 2018-04-24 17:07:44
    received: 0000-00-00 00:00:00
       modem: GSM1
        IMSI: 23nnnnnnnnnnnnn
        IMEI: 86mmmmmmmm
      report:
    alphabet:
      sentto: 07xxxxxxxxxxxx
sending_time: 5
        text: 2018-04-24 17:07:37 system checking in|
     msgfile: /var/spool/sms/checked/xmpp_send.Q4UxaT
   processed: N
*************************** 51. row ***************************
          id: 51
        type: RECEIVED
    from_tel: 447376231809
    from_toa: 91 international, ISDN/telephone
   from_smsc: 44xxxxxxxxxxxx
        sent: 2018-04-24 17:12:55
    received: 2018-04-24 17:13:01
       modem: GSM1
        IMSI: 23nnnnnnnnnnnnn
        IMEI: 86mmmmmmmm
      report: no
    alphabet: ISO
      sentto:
sending_time:
        text: YOUR PHONE IS FULLY CHARGED NOW|
     msgfile: /var/spool/sms/incoming/GSM1.EcCnTD
   processed: N

[ Select ]

[ Expand ]

eventhandler using MySQL/MariaDB, written in Perl