Return to MemoDB2ClassicNote.pl CVS log

Up to [local] / palm / MemoDB2ClassicNote

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

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.4     ! andrew     17: #     REVISION:  $AFresh1: MemoDB2ClassicNote.pl,v 1.3 2010/03/09 21:29:46 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:
1.4     ! andrew     43: my $count = 0;
1.1       andrew     44: my $cat_sth = $dbh->prepare(
                     45:     'INSERT INTO categories ("cat_id", "category") VALUES (?,?)');
                     46: foreach my $id ( 0 .. @categories ) {
                     47:     my $name = $categories[$id]->{name};
                     48:     next if !$name;
                     49:
1.4     ! andrew     50:     $count++;
        !            51:     print "Category [$count]: $id $name\n";
1.1       andrew     52:     $cat_sth->execute( $id, $name ) or die $cat_sth->errstr;
                     53: }
                     54:
1.4     ! andrew     55: $count = 0;
1.1       andrew     56: my $rec_sth
                     57:     = $dbh->prepare( 'INSERT INTO'
                     58:         . ' memos ("cat_id", "head", "memo", "privat")'
                     59:         . ' VALUES (?,?,?,?)' );
                     60: foreach my $record ( @{ $pdb->{records} } ) {
                     61:     my $data = $record->{data};
                     62:     $data =~ s{\n}{<br>}gxms;
                     63:     $data =~ s/\P{IsASCII}//gxms;
                     64:     $data =~ s/(?:<br>)+$//gxms;
                     65:
                     66:     my ($head) = split '<br>', $data;
                     67:
                     68:     my $private = 0;
                     69:     if ( exists $record->{attributes} && $record->{attributes}->{private} ) {
                     70:         $private = 1;
                     71:     }
                     72:
1.4     ! andrew     73:     $count++;
        !            74:     print "Record [$count]: $head\n";
1.1       andrew     75:
                     76:     $rec_sth->execute( $record->{category}, $head, $data, $private )
                     77:         or die $cat_sth->errstr;
                     78: }
                     79:
                     80: $dbh->disconnect;
                     81:
                     82: sub New_DB {
                     83:     my ($dbname) = @_;
                     84:
                     85:     my $DB = <<'EOL';
                     86:     CREATE TABLE __WebKitDatabaseInfoTable__ (
                     87:         key TEXT NOT NULL ON CONFLICT FAIL UNIQUE ON CONFLICT REPLACE,
                     88:         value TEXT NOT NULL ON CONFLICT FAIL
                     89:     );
                     90:     INSERT INTO __WebKitDatabaseInfoTable__ VALUES('WebKitDatabaseVersionKey','');
                     91:
                     92:     CREATE TABLE categories (
                     93:         cat_id INTEGER PRIMARY KEY,
                     94:         category TEXT,
                     95:         templ_id INTEGER DEFAULT 0,
                     96:         color INTEGER DEFAULT 0,
                     97:         passwd INTEGER DEFAULT 0
                     98:     );
                     99:     -- INSERT INTO categories VALUES(0,'undefined',0,0,0);
                    100:
                    101:     CREATE TABLE keywords (
                    102:         kw_id INTEGER PRIMARY KEY,
                    103:         color INTEGER,
                    104:         keyword TEXT
                    105:     );
                    106:
                    107:     CREATE TABLE memos (
                    108:         memo_id INTEGER PRIMARY KEY,
                    109:         cat_id INTEGER,
                    110:         head TEXT,
                    111:         memo TEXT,
                    112:         pos INTEGER DEFAULT 0,
                    113:         level INTEGER DEFAULT 0,
                    114:         keyword INTEGER DEFAULT 0,
                    115:         privat INTEGER DEFAULT 0,
                    116:         created TIMESTAMP,
                    117:         changed TIMESTAMP
                    118:     );
                    119:
                    120:     CREATE TABLE version (dbver INTEGER PRIMARY KEY);
                    121:     INSERT INTO version VALUES(1);
                    122:     INSERT INTO version VALUES(2);
                    123: EOL
                    124:
                    125:     my @sql = grep {$_} map {
                    126:         chomp;
                    127:         s/--.*$//gxms;
                    128:         s/^\s+//xms;
                    129:         $_
                    130:     } split ';', $DB;
                    131:
                    132:     my $dbh = DBI->connect( "dbi:SQLite:dbname=$dbname", "", "" )
                    133:         or die $dbh->errstr;
                    134:
                    135:     $dbh->begin_work;
                    136:     foreach my $sql (@sql) {
                    137:         $dbh->do($sql) or die "Unable to [$sql] " . $dbh->errstr;
                    138:     }
                    139:     $dbh->commit;
                    140:
                    141:     return $dbh;
                    142: }