couchdb/scripts/reblog2couchdb.pl

#!/usr/bin/perl -w

use strict;
use DBI;
use CouchDB::Client;
use Data::Dump qw/dump/;

$|++;

my $database = 'reblog';

my $dbi = "DBI:mysql:database=$database";
$dbi .= ";host=127.0.0.1;port=13306";   # XXX over ssh

my $sql = qq{
        select
                items.id as _id,
                items.*,
                feeds.url as feed_url,
                feeds.title as feed_title,
                feeds.link as feed_link,
                feeds.description as feed_description
        from items
        join items_userdata on items.id = item_id
        join feeds on items.feed_id = feeds.id
        where items.id > ?
        order by items.id asc
        limit 1000
};


my $c = CouchDB::Client->new(uri => 'http://localhost:5984/');

$c->testConnection or die "The server cannot be reached";
print "CouchDB version " . $c->serverInfo->{version} . "\n";
my $db = $c->newDB( $database );
$db->create unless $c->dbExists( $database );

my $status = $db->newDoc( '_sync' );
eval { $status->retrieve };
$status->create if $@;

print "status ",dump( $status->{data} ), "\n";

my $last_row = $status->{data}->{last_row_id} || 0;

my $dbh = DBI->connect($dbi,"","") || die $DBI::errstr;

print "Fetching items from $dbi id > $last_row\n";

my $sth = $dbh->prepare($sql) || die $dbh->errstr();
$sth->execute( $last_row ) || die $sth->errstr();

warn dump( $sth->{NAME} );

print "found ",$sth->rows," items to process...";

my $pk = 'id';

my $count = 0;

while (my $row = $sth->fetchrow_hashref() ) {
        my $_id = $row->{_id} || die "row needs _id";
        my $doc = $db->newDoc( $_id );

        sub row2doc {
                my ( $row, $doc ) = @_;
                my $a = delete( $row->{xml} );
                $doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;
                my $a = delete( $row->{content} );
                $doc->addAttachment( 'content.html', 'text/html', $a ) if $a;
                $doc->{data} = $row;
                return $doc;
        }

        row2doc( $row, $doc );

        eval { $doc->create };
        if ( $@ ) {
                $doc->retrieve;
                row2doc( $row, $doc )->update;
#               eval { $doc->update };
                warn ( $@ ? "ERROR $_id $@" : "updated $_id" ), $/;
        } else {
                warn "created ",dump( $row ),$/;
        }

        $count++;
        if ( $count++ % 100 == 0 ) {    # checkpoint every 100 records
                $status->{data}->{last_row_id} = $row->{id};
                $status->update;
        }
}

# end checkpoint
$status->{data}->{last_row_id} = $last_row;
$status->update;

__END__

$sql = qq{
        update items_userdata
        set value_numeric = 1
        where label = 'read' and item_id in ($ids)
};

$dbh->do( $sql );

1	#!/usr/bin/perl -w
2
3	use strict;
4	use DBI;
5	use CouchDB::Client;
6	use Data::Dump qw/dump/;
7
8	$\|++;
9
10	my $database = 'reblog';
11
12	my $dbi = "DBI:mysql:database=$database";
13	$dbi .= ";host=127.0.0.1;port=13306"; # XXX over ssh
14
15	my $sql = qq{
16	select
17	items.id as _id,
18	items.*,
19	feeds.url as feed_url,
20	feeds.title as feed_title,
21	feeds.link as feed_link,
22	feeds.description as feed_description
23	from items
24	join items_userdata on items.id = item_id
25	join feeds on items.feed_id = feeds.id
26	where items.id > ?
27	order by items.id asc
28	limit 1000
29	};
30
31
32	my $c = CouchDB::Client->new(uri => 'http://localhost:5984/');
33
34	$c->testConnection or die "The server cannot be reached";
35	print "CouchDB version " . $c->serverInfo->{version} . "\n";
36	my $db = $c->newDB( $database );
37	$db->create unless $c->dbExists( $database );
38
39	my $status = $db->newDoc( '_sync' );
40	eval { $status->retrieve };
41	$status->create if $@;
42
43	print "status ",dump( $status->{data} ), "\n";
44
45	my $last_row = $status->{data}->{last_row_id} \|\| 0;
46
47	my $dbh = DBI->connect($dbi,"","") \|\| die $DBI::errstr;
48
49	print "Fetching items from $dbi id > $last_row\n";
50
51	my $sth = $dbh->prepare($sql) \|\| die $dbh->errstr();
52	$sth->execute( $last_row ) \|\| die $sth->errstr();
53
54	warn dump( $sth->{NAME} );
55
56	print "found ",$sth->rows," items to process...";
57
58	my $pk = 'id';
59
60	my $count = 0;
61
62	while (my $row = $sth->fetchrow_hashref() ) {
63	my $_id = $row->{_id} \|\| die "row needs _id";
64	my $doc = $db->newDoc( $_id );
65
66	sub row2doc {
67	my ( $row, $doc ) = @_;
68	my $a = delete( $row->{xml} );
69	$doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;
70	my $a = delete( $row->{content} );
71	$doc->addAttachment( 'content.html', 'text/html', $a ) if $a;
72	$doc->{data} = $row;
73	return $doc;
74	}
75
76	row2doc( $row, $doc );
77
78	eval { $doc->create };
79	if ( $@ ) {
80	$doc->retrieve;
81	row2doc( $row, $doc )->update;
82	# eval { $doc->update };
83	warn ( $@ ? "ERROR $_id $@" : "updated $_id" ), $/;
84	} else {
85	warn "created ",dump( $row ),$/;
86	}
87
88	$count++;
89	if ( $count++ % 100 == 0 ) { # checkpoint every 100 records
90	$status->{data}->{last_row_id} = $row->{id};
91	$status->update;
92	}
93	}
94
95	# end checkpoint
96	$status->{data}->{last_row_id} = $last_row;
97	$status->update;
98
99	__END__
100
101	$sql = qq{
102	update items_userdata
103	set value_numeric = 1
104	where label = 'read' and item_id in ($ids)
105	};
106
107	$dbh->do( $sql );
108