PalmDatabase.db3 File - Revision history

Ambitin at 00:59, 13 June 2010

2010-06-13T00:59:54Z

Prenode: /* Exporting data to HTML */

2010-06-05T22:24:37Z

Exporting data to HTML

Prenode at 22:19, 5 June 2010

2010-06-05T22:19:15Z

Clockworksoftware: Manually editing PalmDatabase.db3

2010-05-29T01:30:13Z

Manually editing PalmDatabase.db3

Corpx: +link to Tutorials Export Text Messages Using Perl

2010-05-26T09:50:27Z

+link to Tutorials Export Text Messages Using Perl

Drewp at 04:01, 28 April 2010

2010-04-28T04:01:20Z

Ttt ttt at 13:10, 21 January 2010

2010-01-21T13:10:42Z

Ttt ttt at 13:01, 21 January 2010

2010-01-21T13:01:57Z

Ttt ttt: /* Exploring PalmDatabase.db3 */

2010-01-21T12:57:43Z

Exploring PalmDatabase.db3

Ttt ttt: /* Known Issues */

2010-01-21T12:56:44Z

Known Issues

← Older revision		Revision as of 00:59, 13 June 2010
Line 249:		Line 249:


		+	=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.

−	==More info==	+	==Perl Script==
		+
		+	<pre><nowiki>
		+	#!/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;
		+	}
		+	</nowiki></pre>
		+
		+	=More info=
	[[Tutorials Export Text Messages Using Perl\|Export Text Messages Using Perl]]		[[Tutorials Export Text Messages Using Perl\|Export Text Messages Using Perl]]

@@ Line 202: / Line 202: @@
 =Exporting data to HTML=
-I'm 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:
+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:
 <pre><nowiki>
 select

← Older revision		Revision as of 22:19, 5 June 2010
Line 199:		Line 199:

	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		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'm 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:
		+	<pre><nowiki>
		+	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;
		+	</nowiki></pre>
		+
		+	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=		=Experience with manual update of PalmDatabase3.db3=

@@ Line 199: / Line 199: @@
 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
 ==More info==
 [[Tutorials Export Text Messages Using Perl|Export Text Messages Using Perl]]

← Older revision		Revision as of 09:50, 26 May 2010
Line 199:		Line 199:

	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		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
		+
		+
		+	==More info==
		+	[[Tutorials Export Text Messages Using Perl\|Export Text Messages Using Perl]]

← Older revision		Revision as of 04:01, 28 April 2010
Line 194:		Line 194:
	close(SQLSEL);		close(SQLSEL);
	</nowiki></pre>		</nowiki></pre>
		+
		+
		+	=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

← Older revision		Revision as of 13:10, 21 January 2010
Line 194:		Line 194:
	close(SQLSEL);		close(SQLSEL);
	</nowiki></pre>		</nowiki></pre>
−
−	I was able to restore most of my contacts (used a trial of missing sync to import initially and those are not saved in palm profile :-( ) by generating .sql with sqlite-manager and apply those to a backup of PalmDatabase.db3 and then mv copy.db3 to the original place.

← Older revision		Revision as of 13:01, 21 January 2010
Line 194:		Line 194:
	close(SQLSEL);		close(SQLSEL);
	</nowiki></pre>		</nowiki></pre>
		+
		+	I was able to restore most of my contacts (used a trial of missing sync to import initially and those are not saved in palm profile :-( ) by generating .sql with sqlite-manager and apply those to a backup of PalmDatabase.db3 and then mv copy.db3 to the original place.

@@ Line 8: / Line 8: @@
 * '''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 [[Patch webOS GPS Tracking|Tracking]] script looks for messages in this table.

@@ Line 98: / Line 98: @@
 * 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.) -[[user:Wsobel|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==