Return to MemoDB2ClassicNote.pl CVS log

Up to [local] / palm / MemoDB2ClassicNote

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

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