Return to MemoDB2ClassicNote.pl CVS log

Up to [local] / palm / MemoDB2ClassicNote

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

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