Re: [coldsync-hackers] generic database processing

[Christophe Beauregard <christophe dot beauregard at sympatico.ca>]

On Thursday 16 March 2006 20:17, cilantro05@comcast.net wrote:

> Is there a simple, reversible way to convert Palm databases into CSV
> format on the command line?

Generically?

No.

Part of the problem is with the word "database". You'd think that implies a 
certain commonality, but...

Conceptually, you can think of the typical Palm "database" as an array of 
data blocks. For many applications, each block is really just a serialized 
C structure. If you don't know the memory layout of that C structure, you 
can't read or write the database.

Some databases are, uh, "richer" and need a more sophisticated approach to 
handling them. For example, most of the "document" formats like DOC, 
Plucker, are basically files spread across many of blocks.

So there's no generic way for a database tool to say "tell me what your 
database schema looks like". Nothing like, say, "SELECT * FROM address;".

What ends up happening is someone has to write database handlers which can 
decode the C structures, and maybe re-encode them. Some of the structures 
are (poorly) documented. Quite a few are reverse engineered. Because the 
reverse engineering process may be imprecise, actually _writing_ the 
database isn't possible in some cases.

Eventually, the "popular" database formats have modules to handle their 
internal details. But it ends up being rather ad-hoc. For example, p5-palm 
doesn't have support for the Contact database. It does support the older 
Address database, and the Contact app stores records into that format as 
well; _most_ of the data is useable, but not all.

This is the case across the board. pilot-link has a bunch of tools for a 
specific set of databases, p5-palm has a bunch of modules for a different 
set of databases... pilot-qof, which I saw mentioned over on 
pilot-link-devel, supports just a handful of databases.

In summary, there MAY be a simple, reversible way to convert SOME Palm 
databases to and from CSV from the command-line.

But it isn't p5-palm. The closest thing it has is the dumpdb script in 
utils/, and that doesn't generate CSV. p5-palm is really a set of classes 
for manipulating databases from perl programs.

Which brings up another point... Because the records are basically raw 
in-memory C structures, they aren't necessarily going to convert nicely to 
and from tabular forms like CSV.

> The closest claim to this I've seen are the coldsync conduits, using the
> p5-palm perl modules.  But I don't speak perl very well, and haven't been
> able to figure out how to use these tools to actually do the conversion

> So I looked directly at the p5-palm perl modules, but clearly don't speak
> enough perl to get started -- I think I've got them loaded correctly, but
> don't have a clue how to process a file with them.

In a nutshell, the tools allow you to write scripts to load database records 
into perl hash variables. From those hash variables, you can do whatever 
you want. For example, a trivial script to convert a few fields of the 
Datebook database to CSV might look like:

#!/usr/bin/perl
use Palm::PDB;
use Palm::Datebook;
use Text::CSV_XS;

my $csv = Text::CSV_XS->new();

my $pdb = new Palm::PDB;
$pdb->Load("DatebookDB.pdb");
for( @{$pdb->records()} ) {
  $csv->print(\*STDOUT,
     [ $_->{day}, $_->{month}, $_->{year}, $_->{description} ]);
}
$pdb->Close();
exit 0;

c.