Tutorials Export Text Messages Using Perl

This is a rather simple script to export text messages in a readable format from PalmDatabase.db3. You must first import PalmDatabase.db3 to your pc from /var/luna/data/dbdata /PalmDatabase.db3 on the Pre.

Run this code (assuming PalmDatabase.db3 is in the same folder and the name is unchanged) and it will spit out information in an "ID - NAME" format. Copy the ID for the second script. <source lang="perl">

1. !/usr/bin/perl

use DBI;

$dbh = DBI->connect( "dbi:SQLite:PalmDatabase.db3" ) || die "Cannot connect: $DBI::errstr";

$res = $dbh->selectall_arrayref( q( SELECT firstName, lastName,id FROM com_palm_messaging_data_ChatThread WHERE _class_id = 11) ); foreach( @$res ) {

   $first = $_->[0];
   $last = $_->[1];
   $id = $_->[2];
   print "$id - $first $last \n";

} </source>

Run the following as "perl script2.pl id yourname otherpersonsname" format. The last 2 variables are optional. Set the $time_zone variable as needed. In the code below, it is set for Central Standard Time. See CPAN#DateTime for more info. <source lang="perl">

1. !/usr/bin/perl

use DBI; use DateTime;

($input,$me,$you) = @ARGV;

$time_zone = 'America/Chicago';

if ($me eq ) {

   $me = 'Me';

}

if ($you eq ) {

   $you = 'You';

}

$me.= " : "; $you.= " : ";

$dbh = DBI->connect( "dbi:SQLite:PalmDatabase.db3" ) || die "Cannot connect: $DBI::errstr";

$res = $dbh->selectall_arrayref( qq( SELECT belongs_id FROM com_palm_messaging_data_ChatThread_com_palm_pim_FolderEntry_Chat_Messages WHERE has_id = $input) );

foreach( @$res ) {

   $id = $_->[0];
   $temp = $dbh->selectall_arrayref( qq( SELECT timeStamp, messageText,flags FROM com_palm_pim_FolderEntry  WHERE messageType = 'SMS' and id=$id) );
   foreach $x (@$temp)
   {
       $time = $x->[0];
       $time = $time/1000;
       $tofrom = find_code ( $x->[2] );
       $dt = DateTime->from_epoch(epoch => $time);
       $dt->set_time_zone( $time_zone );
        
       print $dt->month.'/'.$dt->day.'/'.$dt->year.' '.$dt->hour.':'.$dt->minute.':'.$dt->second.' '.$tofrom.$x->[1];
   }
   

print "\n";

}

sub find_code {

   ($code) = @_;
   if ($code == 133)
   {
       return $me;
   }
   
   if ($code == 5)
   {
       return $you;
   }
   
   return "Unknown";
   

} </source>