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_ContactPoint – Has one entry per contactdetail (Phonenumbers ...)
• 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
  • If working locally on the device:
    root@castle:/var/luna/data/dbdata# sqlite3 PalmDatabase.db3
    sqlite> .load /usr/lib/sqlite3_palm_extension.so
• 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);

Exporting Data to RDF

I have some code at http://bigasterisk.com/darcs/?r=palmpre2rdf;a=tree for extracting the call log and SMS logs to RDF (which can then be serialized to xml, n3, or other graph syntaxes). Find me on #webos-internals if you want to talk about running or expanding this code. -drewp

Exporting data to HTML

I (prenode) am currently working on a Java application that allows me to export messages between two people to a HTML file. It uses the PalmDatabase.db3 file. For anyone interested in the SQL query, here it is:

select
com_palm_pim_Recipient.address, 
com_palm_pim_FolderEntry.smsClass, 
com_palm_pim_Recipient.firstName, 
com_palm_pim_Recipient.lastName, 
com_palm_pim_FolderEntry.fromAddress, 
com_palm_pim_FolderEntry.timeStamp, 
com_palm_pim_FolderEntry.messageText 
from 
com_palm_pim_FolderEntry 
join 
com_palm_pim_Recipient on (com_palm_pim_FolderEntry.id = com_palm_pim_Recipient.com_palm_pim_FolderEntry_id) 
where 
messageType="SMS" AND ((smsClass=0 AND (address="ADDR1" OR address="ADDR2")) OR (address="ADDR1" OR address="ADDR2")) 
order by 
timeStamp;

ADDR1 and ADDR2 are the telephone numbers of you messaging partner. The problem seems to be the international area code. So ADDR1 is typically the telephone number without area code and ADDR2 the one with it.

Experience with manual update of PalmDatabase3.db3

I was to add another email/contacts/messengeraccount to Pre device and the first few attempts to do this through "Add Account" in Email app were unsuccessful for whatever reason. The new accounts were not created. However when I finally sorted everything out on the provider side, learned the correct email password etc and went to add the account again, I've got an error message "Account already exists". However how such account was shown by email/contacts/calender or messenger app.

I wasn't able to resolve the problem until I grepped PalmDatabase.db3 file with a script like this (btw, I did this right on Pre)

substring=mydomain.com
for i in `sqlite3 -echo PalmDatabase.db3 .tables`; do
        if sqlite3 PalmDatabase.db3 "SELECT * FROM $i" | grep -q -i $substring; then
                echo Found in table $i
                sqlite3 PalmDatabase.db3 "SELECT * FROM $i" | grep -i $substring
        fi
done

I found "mydomain.com" in a few tables of PalmDatabase.db3. The I downloaded this file to local box, run it through https://addons.mozilla.org/en-US/firefox/addon/5817/, manually eliminated the found entries, saved file, uploaded it back to Pre and then restarted the device.

After the device came up I was able to add my mail/contacts/calendar accounts successfully. Good luck

Exporting Local Calendar to iCal File

Follow the above instructions for getting sqlite and your Palm database into the same directory. You can then run this script to export all of your local calendar entries into an iCal file.

Limitations

It assumes your local calendar entries are in GMT. This may be a valid assumption but is not one I can verify.

Perl Script

#!/usr/bin/perl

# copy of /var/luna/data/dbdata/PalmDatabase.db3 
my $sqlite = './sqlite3.exe';
my $db = './PalmDatabase.db3';

# find out the local account
my $accountSql = q{"SELECT com_palm_pim_Calendar.id FROM com_palm_pim_Calendar WHERE com_palm_pim_Calendar.name = 'Palm Profile';"};
my $account = `$sqlite $db $accountSql`;
$account =~ s/\n|\s*//g;

my $sql = "SELECT com_palm_pim_CalendarEvent.id, com_palm_pim_CalendarEvent.timeZoneId, com_palm_pim_CalendarEvent.alarm, com_palm_pim_CalendarEvent.startTimestamp, com_palm_pim_CalendarEvent.endTimestamp, com_palm_pim_CalendarEvent.allDay, com_palm_pim_CalendarEvent.subject, com_palm_pim_CalendarEvent.location, com_palm_pim_CalendarEvent.rrule FROM com_palm_pim_CalendarEvent WHERE com_palm_accounts_ActiveRecordFolder_id = '" . $account . "';";

my $output = `$sqlite $db "$sql"`;
my $sqlNotesPrefix = "SELECT com_palm_pim_CalendarEvent.note FROM com_palm_pim_CalendarEvent WHERE com_palm_pim_CalendarEvent.id = \'";
my $sqlNotesSuffix = "\';";

open(ICAL, ">export.ics");

print ICAL "BEGIN:VCALENDAR\nVERSION:2.0\nPRODID:-//york/pre/ical-dropper/";

my @lines = split(/\n/, $output);
my $uidValue = 0;

foreach my $line (@lines) 
{
	my ($id, $timezoneid, $valarm, $start, $end, $allDay, $subject, $location, $rrule) = split (/\|/, $line, 9);

	if (!($subject =~ /^\s*$/))
	{		
		my $sql2 = $sqlNotesPrefix . $id . $sqlNotesSuffix;
		my $description = `$sqlite $db "$sql2"`;
		print ICAL "\nBEGIN:VEVENT\nUID:0123456789AEEEA123457778888743FFEACD1$uidValue\n";
		print ICAL "SUMMARY:$subject\n";
		$datestring=convertToIcalDateString($start, $allDay);		
		print ICAL "DTSTART".$datestring."\n";
		$datestring=convertToIcalDateString($end, $allDay);
		print ICAL "DTEND".$datestring."\n"; # append 'Z' for UTC
		if (!($description =~ /^\s*$/))
		{
			$description =~ s/\n/\n\t/g;
			$description =~ s/\n\t$/\n/g;
			print ICAL "DESCRIPTION:$description";
		}

		if (!($location =~ /^\s*$/))
		{
			print ICAL "LOCATION:$location\n";
		}

		if (!($rrule =~ /^\s*$/))
		{
			$rrule =~ s/\r|\n//g;
			print ICAL $rrule . "\n";
		}
		
		if ((!($valarm =~ /^\s*$/)) && (!($valarm =~ /none/i)))
		{
			print ICAL "BEGIN:VALARM\nACTION:DISPLAY\n";
			print ICAL "DESCRIPTION:$subject\n";
			print ICAL "TRIGGER:$valarm\n";
			print ICAL "END:VALARM\n";
		}
		
		print ICAL "END:VEVENT\n";
		$uidValue++;
	}
}

print ICAL "END:VCALENDAR\n";
close(ICAL);

print $uidValue . " calendar events exported.\n";

sub convertToIcalDateString()
{
	my ($thistime, $allDay) = @_;
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime($thistime/1000);
	my ($result);
	my ($twohour) =  sprintf("%02d", $hour);
	my ($twominute) =  sprintf("%02d", $min);
	my ($twomon) =  sprintf("%02d", ($mon+1));
	my ($twoday) =  sprintf("%02d", $mday);
	
	if ($allDay == "0")
	{
		$result = ":" . ($year+1900) . ($twomon) . ($twoday) . "T" . $twohour . $twominute . "00Z";
	}
	else
	{
		$result = ";VALUE=DATE:" . ($year+1900) . ($twomon) . ($twoday);
	}

	return $result;
}

More info

Export Text Messages Using Perl