Return to MemoDB2ClassicNote.pl CVS log

Up to [local] / palm / MemoDB2ClassicNote

Annotation of palm/MemoDB2ClassicNote/MemoDB2ClassicNote.pl, Revision 1.3

1.1       andrew      1: #!/usr/bin/perl
                      2: #===============================================================================
                      3: #
                      4: #         FILE:  MemoDB2ClassicNote.pl
                      5: #
                      6: #        USAGE:  ./MemoDB2ClassicNote.pl <source> <dest>
1.2       andrew      7: #                dest is the db file in
                      8: #                x:\.app-storage\file_.media.cryptofs.apps.usr.palm.applications.com.littlestick.classicnote_0\
1.1       andrew      9: #
                     10: #  DESCRIPTION:  converts a Palm MemoDB.pdb to ClassicNote format.
1.3     ! andrew     11: #                http://www.precentral.net/homebrew-apps/classicnote
1.1       andrew     12: #
                     13: #       AUTHOR:  Andrew Fresh (AAF), andrew@afresh1.com
                     14: #      COMPANY:  Red River Communications
                     15: #      VERSION:  1.0
                     16: #      CREATED:  03/09/10 11:22:52
1.3     ! andrew     17: #     REVISION:  $AFresh1: MemoDB2ClassicNote.pl,v 1.2 2010/03/09 20:55:05 andrew Exp $
1.1       andrew     18: #===============================================================================
                     19:
                     20: use strict;
                     21: use warnings;
                     22:
                     23: use DBD::SQLite;
                     24:
                     25: use Palm::PDB;
                     26: use Palm::Memo;
                     27:
                     28: my ( $src, $dst ) = @ARGV;
                     29:
                     30: die "Usage: $0 <source> <dest>\n" if !$dst;
                     31: die "$dst exists!\n" if -e $dst;
                     32:
                     33: my $pdb = Palm::PDB->new();
                     34: $pdb->Load($src) or die "Couldn't load [$src]: $!\n";
                     35:
                     36: my $dbh = New_DB($dst);
                     37:
                     38: my @categories = @{ $pdb->{appinfo}->{categories} };
                     39: if ( !@categories ) {
                     40:     @categories = ('undefined');
                     41: }
                     42:
                     43: my $cat_sth = $dbh->prepare(
                     44:     'INSERT INTO categories ("cat_id", "category") VALUES (?,?)');
                     45: foreach my $id ( 0 .. @categories ) {
                     46:     my $name = $categories[$id]->{name};
                     47:     next if !$name;
                     48:
                     49:     print "Category: $id $name\n";
                     50:     $cat_sth->execute( $id, $name ) or die $cat_sth->errstr;
                     51: }
                     52:
                     53: my $rec_sth
                     54:     = $dbh->prepare( 'INSERT INTO'
                     55:         . ' memos ("cat_id", "head", "memo", "privat")'
                     56:         . ' VALUES (?,?,?,?)' );
                     57: foreach my $record ( @{ $pdb->{records} } ) {
                     58:     my $data = $record->{data};
                     59:     $data =~ s{\n}{<br>}gxms;
                     60:     $data =~ s/\P{IsASCII}//gxms;
                     61:     $data =~ s/(?:<br>)+$//gxms;
                     62:
                     63:     my ($head) = split '<br>', $data;
                     64:
                     65:     my $private = 0;
                     66:     if ( exists $record->{attributes} && $record->{attributes}->{private} ) {
                     67:         $private = 1;
                     68:     }
                     69:
                     70:     print "Record: $head\n";
                     71:
                     72:     $rec_sth->execute( $record->{category}, $head, $data, $private )
                     73:         or die $cat_sth->errstr;
                     74: }
                     75:
                     76: $dbh->disconnect;
                     77:
                     78: sub New_DB {
                     79:     my ($dbname) = @_;
                     80:
                     81:     my $DB = <<'EOL';
                     82:     CREATE TABLE __WebKitDatabaseInfoTable__ (
                     83:         key TEXT NOT NULL ON CONFLICT FAIL UNIQUE ON CONFLICT REPLACE,
                     84:         value TEXT NOT NULL ON CONFLICT FAIL
                     85:     );
                     86:     INSERT INTO __WebKitDatabaseInfoTable__ VALUES('WebKitDatabaseVersionKey','');
                     87:
                     88:     CREATE TABLE categories (
                     89:         cat_id INTEGER PRIMARY KEY,
                     90:         category TEXT,
                     91:         templ_id INTEGER DEFAULT 0,
                     92:         color INTEGER DEFAULT 0,
                     93:         passwd INTEGER DEFAULT 0
                     94:     );
                     95:     -- INSERT INTO categories VALUES(0,'undefined',0,0,0);
                     96:
                     97:     CREATE TABLE keywords (
                     98:         kw_id INTEGER PRIMARY KEY,
                     99:         color INTEGER,
                    100:         keyword TEXT
                    101:     );
                    102:
                    103:     CREATE TABLE memos (
                    104:         memo_id INTEGER PRIMARY KEY,
                    105:         cat_id INTEGER,
                    106:         head TEXT,
                    107:         memo TEXT,
                    108:         pos INTEGER DEFAULT 0,
                    109:         level INTEGER DEFAULT 0,
                    110:         keyword INTEGER DEFAULT 0,
                    111:         privat INTEGER DEFAULT 0,
                    112:         created TIMESTAMP,
                    113:         changed TIMESTAMP
                    114:     );
                    115:
                    116:     CREATE TABLE version (dbver INTEGER PRIMARY KEY);
                    117:     INSERT INTO version VALUES(1);
                    118:     INSERT INTO version VALUES(2);
                    119: EOL
                    120:
                    121:     my @sql = grep {$_} map {
                    122:         chomp;
                    123:         s/--.*$//gxms;
                    124:         s/^\s+//xms;
                    125:         $_
                    126:     } split ';', $DB;
                    127:
                    128:     my $dbh = DBI->connect( "dbi:SQLite:dbname=$dbname", "", "" )
                    129:         or die $dbh->errstr;
                    130:
                    131:     $dbh->begin_work;
                    132:     foreach my $sql (@sql) {
                    133:         $dbh->do($sql) or die "Unable to [$sql] " . $dbh->errstr;
                    134:     }
                    135:     $dbh->commit;
                    136:
                    137:     return $dbh;
                    138: }