#!/usr/bin/perl
#
# sms_cleaner.pl                                Author: Rich West
#                                               Date: Jan 10, 2010
#
# Originally, CSV file was created via:
# sqlite3 -csv -separator "," sms.mddata "select message.date,message.address,message.flags,message.text from message,group_member where group_member.group_id=message.group_id and group_member.group_id=2" > test.csv
# Now the SQLite file is read directly.
#
# Script which reads the sqlite database file from an iphone, identifies certain
# conversations, and displays them.
#
# The file is usually found at:
# C:\Documents and Settings\<username>\Application Data\Apple Computer
#   \MobileSync\Backup\<string of characters>
#   \3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata
#
################################################################################


use strict;
$|++;

&usage if ($#ARGV != 0);
&main ($ARGV[0]);

##
# Standard Usage Statement
##
sub usage
{
   print STDERR "Usage: $0 <SQLite filename>\n\n";
   exit;
}

##
# Read the database file and display the appropriate output
##
sub read_file
{
   my ($FILENAME) = @_;
   use DBI;

   my ($qs, $sth, @row, $name, $date);
   my ($dbh) = DBI->connect("dbi:SQLite:dbname=$FILENAME","","");

   $qs = "select message.date,message.address,message.flags,message.text from message,group_member where group_member.group_id=message.group_id and group_member.group_id=2";
   $sth = $dbh->prepare($qs)
      or warn "[read_db_file] Query had some problem: " . $dbh->errstr . "\n$qs\n";
   $sth->execute
      or warn "[read_db_file] Query had some problem: " . $sth->errstr . "\n$qs\n";
   while (@row=$sth->fetchrow_array())
   {
      $name = "Me";
      $name = "SomeoneElse" if ($row[2] == 2);
      $date = localtime($row[0]);
      print "$date: $name -> $row[3]\n";
   }
}

##
# The main guts of the script
##
sub main
{
   my ($FILENAME) = @_;

   &read_file($FILENAME);
}