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

Since we now have access to the database, it would be nice to export your contacts to Google Contacts so that they can be shared by other applications (phones that support Google Sync, Google Voice, Gmail, etc). Here's what you'll need:

1. A copy of your PalmDatabase.db3
2. sqlite3 executable binary
3. Perl

Once you have that, modify the following two lines from the perl script below:

my $db = './PalmDatabase.db3';
my $sqlite = './sqlite3-3.6.16.bin';

They should point to the path of your sqlite3 binary and database. Once you've done that, just run your script and it should create 3 files:

1. palm_pre_contacts.csv - CVS file that you can import into your Google Contacts
2. select_contacts.sql - SQL script to show which contacts can be removed from your Palm Pre
3. delete_contacts.sql - SQL script to delete the contacts from your Palm Pre

It would probably be a good idea to view the CSV file in a spreadsheet program to make sure the contacts look ok.

Known Issues

• delete_contacts.sql does not work. It throws a "no such collation sequence: LOCALIZED_SECONDARY". I haven't figured out to define this coallation sequence. For now, you will have to delete each entry manually. To avoid duplicates, you should do this before syncing with your Google account.
  • (I have added a line to the script to load the palm extensions for sqlite3. If you copy the delete file over to the pre and run it on the device, it will work.) -Wsobel
• Only allows for 3 email entries and 3 phone entries maximum for each contact.
• [FIXED: should only export Palm Profile contacts to the csv] Does not differentiate between different accounts. All contacts are grabbed.

Perl Script

#!/usr/bin/perl
use strict;

# copy of /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');

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 syncSource = 'local' AND (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 SQLDEL ".load /usr/lib/sqlite3_palm_extension.so\n";

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_Person WHERE id=$pid;\n";
		print SQLSEL "SELECT id, firstName, lastName from com_palm_pim_Person WHERE 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);