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.

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

}

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.

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