Difference between revisions of "Tutorials Export Text Messages Using Perl"
(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...) |
|||
| (One intermediate revision by one other user not shown) | |||
| 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. | 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"> |
#!/usr/bin/perl | #!/usr/bin/perl | ||
| Line 19: | Line 19: | ||
} | } | ||
| − | </ | + | </source> |
<br><br><br><br> | <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. |
| − | < | + | <source lang="perl"> |
#!/usr/bin/perl | #!/usr/bin/perl | ||
| Line 30: | Line 30: | ||
($input,$me,$you) = @ARGV; | ($input,$me,$you) = @ARGV; | ||
| + | |||
| + | $time_zone = 'America/Chicago'; | ||
if ($me eq '') | if ($me eq '') | ||
| Line 60: | Line 62: | ||
$tofrom = find_code ( $x->[2] ); | $tofrom = find_code ( $x->[2] ); | ||
$dt = DateTime->from_epoch(epoch => $time); | $dt = DateTime->from_epoch(epoch => $time); | ||
| − | $dt->set_time_zone( | + | $dt->set_time_zone( $time_zone ); |
print $dt->month.'/'.$dt->day.'/'.$dt->year.' '.$dt->hour.':'.$dt->minute.':'.$dt->second.' '.$tofrom.$x->[1]; | print $dt->month.'/'.$dt->day.'/'.$dt->year.' '.$dt->hour.':'.$dt->minute.':'.$dt->second.' '.$tofrom.$x->[1]; | ||
| Line 87: | Line 89: | ||
} | } | ||
| − | </ | + | </source> |
Latest revision as of 07:07, 17 August 2010
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">
- !/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">
- !/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>