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
--              t.value_long as tags
        from items
        join items_userdata on items.id = item_id
        join feeds on items.feed_id = feeds.id
--      left outer join items_userdata as t on items.id = t.item_id and t.label='tags'
        where items.id > ? and items_userdata.label = 'published' and items_userdata.value_numeric = 1
        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;

sub commit_last_row {
        warn "commit_last_row $last_row\n";
        $status->{data}->{last_row_id} = $last_row;
        $status->update;
}

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...\n";

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 $@ ? "$count ERROR $_id $@\n" : "$count updated $_id\n";
        } else {
                warn "$count created $_id\n";
        }

        $last_row = $row->{id};
        $count++;

        commit_last_row if $count % 100 == 0    # checkpoint every 100 records
}

commit_last_row;

__END__

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

$dbh->do( $sql );

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