PalmDatabase.db3 File
The file /var/luna/data/dbdata/PalmDatabase.db3 is an sqlite database file that appears to contain much of the personal data stored on the Pre. The information in this database, which has about 100 tables, includes contacts, events, account credentials, and call logs.
Exploring PalmDatabase.db3
A safe way to explore PalmDatabase.db3 is to copy the file from the Pre to a computer where it can be examined with the sqlite3 command or an sqlite browser. A nice browser of this type of file is the Firefox Addon sqlite-manager. With this browser, the structure of the database tables and their contents can be easily examined.
Some of the tables in PalmDatabase.db3 are:
- com_palm_pim_Contact – Has one entry per contact per profile. Includes pointers to the remote profiles.
- com_palm_pim_Person – Has one entry per contact Contains a field for the sort order of contacts displayed in the Contact application.
- com_palm_pim_FolderEntry – Contains messages, both SMS and Email. The GPS [[[Tracking]]] script looks for messages in this table.
Modifying PalmDatabase.db3
On my previous phone, I entered all contacts in the form "Lastname, Firstname" so that the phone would sort contacts by last name. The Pre internally saves names broken up by prefix, first name, middle name, last name and suffix and has the option to sort by first or last name. When the Sprint store transferred my contacts, the last name followed by a "," went into the first name field and the first name went into the last name field. To fix this, it should have been a simple matter of exporting the contacts from the phone, using an editor or script to swap the first and last name fields (and remove the ",") and then sending the contacts back to the phone. However, there seems to be no way at present to export or import contacts in the Palm Profile. (Except at a Sprint store).
Below is a python script, precontacts.py that implements this first/last name swap. This script uses the [*http://code.google.com/p/apsw/ APSW] sqlite3 wrapper. Similar scripts could be used with different sqlite wrappers such as [*http://pysqlite.org/ pysqlite] or with different scripting languages such as Perl. This script does not modify PalmDatabase.db3 directly, rather it writes a list of SQL commands that can be applied to the database. I suggest copying PalmDatabase.db3 to a host machine, generating the SQL commands, testing that those commands work, copying the list of SQL commands to the Pre, and then executing those commands against PalmDatabase.db3 (after making a backup of that file).
#!/usr/bin/env python
# Correct names imported from a non smart phone to the Pre where names on
# the old phone were entered in the form "Lastname, Firstname". Entries of
# this type had the first name put in the Pre's Lastname field and the
# last name and comma put in the first name field.
#
# Swap the first and last names in the Palm Pre contact database.
# Only do the swap when the name in the firstname field ends with a ",".
#
# The output of this script should be directed to a file. (say swap.sql) The
# database changes can then be done with "sqlite3 PalmDatabase.db3 < swap.sql".
import os, sys, time
import apsw
DBFILE="PalmDatabase.db3"
if os.path.exists(DBFILE):
connection=apsw.Connection(DBFILE)
cursor=connection.cursor()
else:
print "No database"
# Do name swap in com_palm_pim_Contact table
for first, middle, last, id in cursor.execute("select firstName, middleName, lastName, com_palm_pim_Person_id from com_palm_pim_Contact"):
if first:
if first[-1] == ",":
newlast = first[0:-1]
first = last
last = newlast
id=`id`[0:-1]
sql = "update com_palm_pim_Contact set lastName=\""+last+"\", firstName=\""+first+"\" where com_palm_pim_Person_id="+id+";"
print sql
# Do name swap in com_palm_pim_Person table
for first, middle, last, id, sortKey in cursor.execute("select firstName, middleName, lastName, id, sortKey from com_palm_pim_Person"):
if first:
if first[-1] == ",":
newlast = first[0:-1]
first = last
last = newlast
id=`id`[0:-1]
newsortKey = last+"\t"+first+"\t"
if middle:
newsortKey+=middle
newsortKey = newsortKey.upper()
sql = "update com_palm_pim_Person set lastName=\""+last+"\", firstName=\""+first+"\", sortKey=\""+newsortKey+"\" where id="+id+";"
print sql
Exporting Contacts to Google CSV file
<nowiki>
#!/usr/bin/perl
use strict;
use Data::Dumper;
# copy from /var/luna/data/dbdata/PalmDatabase.db3
my $sqlite = './sqlite3-3.6.16.bin';
my $db = './PalmDatabase.db3';
my $contacts = {};
my @label = ('Home', 'Work', 'Other', 'Mobile');
# 3 = mobile
# 1 = work
# 0 = home
my $header = q(Name,Given Name,Additional Name,Family Name,Yomi Name,Given Name Yomi,Additional Name Yomi,Family Name Yomi,Name Prefix,Name Suffix,Initials,Nickname,Short Name,Maiden Name,Birthday,Gender,Location,Billing Information,Directory Server,Mileage,Occupation,Hobby,Sensitivity,Priority,Subject,Notes,Group Membership,E-mail 1 - Type,E-mail 1 - Value,E-mail 2 - Type,E-mail 2 - Value,E-mail 3 - Type,E-mail 3 - Value,Phone 1 - Type,Phone 1 - Value,Phone 2 - Type,Phone 2 - Value,Phone 3 - Type,Phone 3 - Value);
my $sql = q{"SELECT com_palm_pim_Contact.firstName, com_palm_pim_Contact.lastName, com_palm_pim_ContactPoint.type, com_palm_pim_ContactPoint.value, com_palm_pim_ContactPoint.label, com_palm_pim_Contact.com_palm_pim_Person_id FROM com_palm_pim_Contact INNER JOIN com_palm_pim_ContactPoint ON com_palm_pim_Contact.id = com_palm_pim_ContactPoint.com_palm_pim_Contact_contactPts_id WHERE com_palm_pim_ContactPoint.type = 'EMAIL' OR com_palm_pim_ContactPoint.type = 'PHONE' ORDER BY com_palm_pim_Contact.firstName, com_palm_pim_Contact.lastName, com_palm_pim_ContactPoint.type ASC;"};
my $output = `$sqlite $db $sql`;
open(CSV, ">palm_pre_contacts.csv");
open(SQLDEL, ">delete_contacts.sql");
open(SQLSEL, ">select_contacts.sql");
print CSV "$header\n";
my @lines = split(/\n/, $output);
foreach my $line (@lines) {
#my @parts = split(/\|/, $line);
my ($first, $last, $type, $value, $label, $pid) = split(/\|/, $line);
if ($first && $first ne '') {
if (!($contacts->{$pid})) {
$contacts->{$pid} = {
'first' => $first,
'last' => $last,
'email' => [],
'phone' => []
}
}
if ($type eq 'EMAIL') {
push(@{$contacts->{$pid}->{email}}, {
label => $label[$label],
email => $value
});
}
elsif ($type eq 'PHONE') {
$value =~ s/[^\d]//g;
push(@{$contacts->{$pid}->{phone}}, {
label => $label[$label],
phone => $value
});
}
print SQLDEL "DELETE FROM com_palm_pim_Contact WHERE com_palm_pim_Person_id=$pid;\n";
print SQLSEL "SELECT firstName, lastName from com_palm_pim_Contact WHERE com_palm_pim_Person_id=$pid;\n";
}
}
while ( my ($pid, $value) = each(%$contacts) ) {
my @line = ();
$line[0] = $value->{'first'} . ' ' . $value->{'last'};
$line[1] = $value->{'first'};
$line[3] = $value->{'last'};
$line[26] = '* My Contacts';
$line[27] = $value->{'email'}->[0]->{label};
$line[28] = $value->{'email'}->[0]->{email};
$line[29] = $value->{'email'}->[1]->{label};
$line[30] = $value->{'email'}->[1]->{email};
$line[31] = $value->{'email'}->[2]->{label};
$line[32] = $value->{'email'}->[2]->{email};
$line[33] = $value->{'phone'}->[0]->{label};
$line[34] = $value->{'phone'}->[0]->{phone};
$line[35] = $value->{'phone'}->[1]->{label};
$line[36] = $value->{'phone'}->[1]->{phone};
$line[37] = $value->{'phone'}->[2]->{label};
$line[38] = $value->{'phone'}->[2]->{phone};
print CSV join(',', @line) . "\n";
}
close(CSV);
close(SQLDEL);
close(SQLSEL);
<nowiki>