Tutorials Export Text Messages Using Perl - Revision history

Newacct at 07:07, 17 August 2010

2010-08-17T07:07:30Z

Corpx at 09:42, 26 May 2010

2010-05-26T09:42:10Z

Corpx: New page: 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/lu...

2010-05-26T09:38:14Z

New page: 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/lu...

New page

This is a rather simple script to export text messages in a readable format from [[PalmDatabase.db3 File|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.
<pre><nowiki>
#!/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";

}
</nowiki></pre>

 
Run the following as "perl script2.pl id yourname otherpersonsname" format. The last 2 variables are optional.
<pre><nowiki>
#!/usr/bin/perl

use DBI;
use DateTime;

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

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( 'America/Chicago' );

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";

}
</nowiki></pre>

@@ Line 2: / Line 2: @@
 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.
-<pre><nowiki>
+<source lang="perl">
 #!/usr/bin/perl
@@ Line 19: / Line 19: @@
+}
-</nowiki></pre>
+</source>
 <br><br><br><br>
 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 [http://search.cpan.org/~drolsky/DateTime-0.55/lib/DateTime.pm#%22Set%22_Methods CPAN#DateTime] for more info.
-<pre><nowiki>
+<source lang="perl">
 #!/usr/bin/perl
@@ Line 89: / Line 89: @@
+}
-</nowiki></pre>
+</source>

@@ Line 22: / Line 22: @@
 <br><br><br><br>
-Run the following as "perl script2.pl id yourname otherpersonsname" format.  The last 2 variables are optional.
+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 [http://search.cpan.org/~drolsky/DateTime-0.55/lib/DateTime.pm#%22Set%22_Methods CPAN#DateTime] for more info.
 <pre><nowiki>
 #!/usr/bin/perl
@@ Line 30: / Line 30: @@
 ($input,$me,$you) = @ARGV;
 if ($me eq '')
@@ Line 60: / Line 62: @@
          $tofrom = find_code ( $x->[2] );
          $dt = DateTime->from_epoch(epoch => $time);
-         $dt->set_time_zone( 'America/Chicago' );
+         $dt->set_time_zone( $time_zone );
          print $dt->month.'/'.$dt->day.'/'.$dt->year.' '.$dt->hour.':'.$dt->minute.':'.$dt->second.' '.$tofrom.$x->[1];