#!/usr/bin/perl
#===============================================================================
#
# FILE: MemoDB2ClassicNote.pl
#
# USAGE: ./MemoDB2ClassicNote.pl <source> <dest>
# dest is the db file in
# x:\.app-storage\file_.media.cryptofs.apps.usr.palm.applications.com.littlestick.classicnote_0\
#
# DESCRIPTION: converts a Palm MemoDB.pdb to ClassicNote format.
# http://www.precentral.net/homebrew-apps/classicnote
#
# AUTHOR: Andrew Fresh (AAF), andrew@afresh1.com
# COMPANY: Red River Communications
# VERSION: 1.0
# CREATED: 03/09/10 11:22:52
# REVISION: $AFresh1: MemoDB2ClassicNote.pl,v 1.4 2010/03/10 18:50:31 andrew Exp $
#===============================================================================
use strict;
use warnings;
use DBD::SQLite;
use Palm::PDB;
use Palm::Memo;
my ( $src, $dst ) = @ARGV;
die "Usage: $0 <source> <dest>\n" if !$dst;
die "$dst exists!\n" if -e $dst;
my $pdb = Palm::PDB->new();
$pdb->Load($src) or die "Couldn't load [$src]: $!\n";
my $dbh = New_DB($dst);
my @categories = @{ $pdb->{appinfo}->{categories} };
if ( !@categories ) {
@categories = ('undefined');
}
my $count = 0;
my $cat_sth = $dbh->prepare(
'INSERT INTO categories ("cat_id", "category") VALUES (?,?)');
foreach my $id ( 0 .. @categories ) {
my $name = $categories[$id]->{name};
next if !$name;
$count++;
print "Category [$count]: $id $name\n";
$cat_sth->execute( $id, $name ) or die $cat_sth->errstr;
}
$count = 0;
my $rec_sth
= $dbh->prepare( 'INSERT INTO'
. ' memos ("cat_id", "head", "memo", "privat")'
. ' VALUES (?,?,?,?)' );
foreach my $record ( @{ $pdb->{records} } ) {
my $data = $record->{data};
$data =~ s{\n}{<br>}gxms;
$data =~ s/\P{IsASCII}//gxms;
$data =~ s/(?:<br>)+$//gxms;
my ($head) = split '<br>', $data;
my $private = 0;
if ( exists $record->{attributes} && $record->{attributes}->{private} ) {
$private = 1;
}
$count++;
print "Record [$count]: $head\n";
$rec_sth->execute( $record->{category}, $head, $data, $private )
or die $cat_sth->errstr;
}
$dbh->disconnect;
sub New_DB {
my ($dbname) = @_;
my $DB = <<'EOL';
CREATE TABLE __WebKitDatabaseInfoTable__ (
key TEXT NOT NULL ON CONFLICT FAIL UNIQUE ON CONFLICT REPLACE,
value TEXT NOT NULL ON CONFLICT FAIL
);
INSERT INTO __WebKitDatabaseInfoTable__ VALUES('WebKitDatabaseVersionKey','');
CREATE TABLE categories (
cat_id INTEGER PRIMARY KEY,
category TEXT,
templ_id INTEGER DEFAULT 0,
color INTEGER DEFAULT 0,
passwd INTEGER DEFAULT 0
);
-- INSERT INTO categories VALUES(0,'undefined',0,0,0);
CREATE TABLE keywords (
kw_id INTEGER PRIMARY KEY,
color INTEGER,
keyword TEXT
);
CREATE TABLE memos (
memo_id INTEGER PRIMARY KEY,
cat_id INTEGER,
head TEXT,
memo TEXT,
pos INTEGER DEFAULT 0,
level INTEGER DEFAULT 0,
keyword INTEGER DEFAULT 0,
privat INTEGER DEFAULT 0,
created TIMESTAMP,
changed TIMESTAMP
);
CREATE TABLE version (dbver INTEGER PRIMARY KEY);
INSERT INTO version VALUES(1);
INSERT INTO version VALUES(2);
EOL
my @sql = grep {$_} map {
chomp;
s/--.*$//gxms;
s/^\s+//xms;
$_
} split ';', $DB;
my $dbh = DBI->connect( "dbi:SQLite:dbname=$dbname", "", "" )
or die $dbh->errstr;
$dbh->begin_work;
foreach my $sql (@sql) {
$dbh->do($sql) or die "Unable to [$sql] " . $dbh->errstr;
}
$dbh->commit;
return $dbh;
}