#!/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.3 2010/03/09 21:29:46 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 $cat_sth = $dbh->prepare(
    'INSERT INTO categories ("cat_id", "category") VALUES (?,?)');
foreach my $id ( 0 .. @categories ) {
    my $name = $categories[$id]->{name};
    next if !$name;

    print "Category: $id $name\n";
    $cat_sth->execute( $id, $name ) or die $cat_sth->errstr;
}

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;
    }

    print "Record: $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;
}