couchdb/scripts/reblog2couchdb.pl

#!/usr/bin/perl -w

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

$|++;

my $debug = @ARGV ? 1 : 0;

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 > ? and items_userdata.label = 'published' and items_userdata.value_numeric = 1
        order by items.id asc
        limit 1000
};

my $sql_tags = qq{
select
        items_userdata.item_id,
        t.value_long as tags,
        items_userdata.timestamp
from items_userdata
join items_userdata as t
        on items_userdata.item_id = t.item_id and t.label='tags'
where
        items_userdata.item_id > ? and
        items_userdata.label = 'published' and
        items_userdata.value_numeric = 1
order by items_userdata.item_id asc
};

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;
$last_row = 0 if $debug;

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,"","",{ RaiseError => 1 });

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

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

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

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

my $sth_tags = $dbh->prepare($sql_tags);
$sth_tags->execute( $last_row );
print "found ",$sth_tags->rows, " tags found...\n";

my $count = 0;

my $row_tags = $sth_tags->fetchrow_hashref();

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

        while ( $row_tags && $row_tags->{item_id} < $row->{_id} ) {
                $row_tags = $sth_tags->fetchrow_hashref();
                warn "## got tags: ",dump( $row_tags ) if $debug;
        }

        sub row2doc {
                my ( $row, $doc ) = @_;
                my $a = delete( $row->{xml} );
                $doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;
                $a = delete( $row->{content} );
                $doc->addAttachment( 'content.html', 'text/html', $a ) if $a;
                if ( $row_tags && $row_tags->{item_id} == $row->{_id} ) {
                        $row->{tags} = [ split(/\s+/, $row_tags->{tags} ) ];
                        warn "++ ",$row->{item_id}, dump( $row->{tags} );
                }
                $doc->{data} = $row;
                warn "## ",dump( $row ) if $debug;
                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	#!/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 $debug = @ARGV ? 1 : 0;
11
12	my $database = 'reblog';
13
14	my $dbi = "DBI:mysql:database=$database";
15	$dbi .= ";host=127.0.0.1;port=13306"; # XXX over ssh
16
17	my $sql = qq{
18	select
19	items.id as _id,
20	items.*,
21	feeds.url as feed_url,
22	feeds.title as feed_title,
23	feeds.link as feed_link,
24	feeds.description as feed_description
25	from items
26	join items_userdata on items.id = item_id
27	join feeds on items.feed_id = feeds.id
28	where items.id > ? and items_userdata.label = 'published' and items_userdata.value_numeric = 1
29	order by items.id asc
30	limit 1000
31	};
32
33	my $sql_tags = qq{
34	select
35	items_userdata.item_id,
36	t.value_long as tags,
37	items_userdata.timestamp
38	from items_userdata
39	join items_userdata as t
40	on items_userdata.item_id = t.item_id and t.label='tags'
41	where
42	items_userdata.item_id > ? and
43	items_userdata.label = 'published' and
44	items_userdata.value_numeric = 1
45	order by items_userdata.item_id asc
46	};
47
48	my $c = CouchDB::Client->new(uri => 'http://localhost:5984/');
49
50	$c->testConnection or die "The server cannot be reached";
51	print "CouchDB version " . $c->serverInfo->{version} . "\n";
52	my $db = $c->newDB( $database );
53	$db->create unless $c->dbExists( $database );
54
55	my $status = $db->newDoc( '_sync' );
56	eval { $status->retrieve };
57	$status->create if $@;
58
59	print "status ",dump( $status->{data} ), "\n";
60
61	my $last_row = $status->{data}->{last_row_id} \|\| 0;
62	$last_row = 0 if $debug;
63
64	sub commit_last_row {
65	warn "commit_last_row $last_row\n";
66	$status->{data}->{last_row_id} = $last_row;
67	$status->update;
68	}
69
70	my $dbh = DBI->connect($dbi,"","",{ RaiseError => 1 });
71
72	print "Fetching items from $dbi id > $last_row\n";
73
74	my $sth = $dbh->prepare($sql);
75	$sth->execute( $last_row );
76
77	warn dump( $sth->{NAME} );
78
79	print "found ",$sth->rows," items to process...\n";
80
81	my $sth_tags = $dbh->prepare($sql_tags);
82	$sth_tags->execute( $last_row );
83	print "found ",$sth_tags->rows, " tags found...\n";
84
85	my $count = 0;
86
87	my $row_tags = $sth_tags->fetchrow_hashref();
88
89	while (my $row = $sth->fetchrow_hashref() ) {
90	my $_id = $row->{_id} \|\| die "row needs _id";
91	my $doc = $db->newDoc( $_id );
92
93	while ( $row_tags && $row_tags->{item_id} < $row->{_id} ) {
94	$row_tags = $sth_tags->fetchrow_hashref();
95	warn "## got tags: ",dump( $row_tags ) if $debug;
96	}
97
98	sub row2doc {
99	my ( $row, $doc ) = @_;
100	my $a = delete( $row->{xml} );
101	$doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;
102	$a = delete( $row->{content} );
103	$doc->addAttachment( 'content.html', 'text/html', $a ) if $a;
104	if ( $row_tags && $row_tags->{item_id} == $row->{_id} ) {
105	$row->{tags} = [ split(/\s+/, $row_tags->{tags} ) ];
106	warn "++ ",$row->{item_id}, dump( $row->{tags} );
107	}
108	$doc->{data} = $row;
109	warn "## ",dump( $row ) if $debug;
110	return $doc;
111	}
112
113	row2doc( $row, $doc );
114
115	eval { $doc->create };
116	if ( $@ ) {
117	$doc->retrieve;
118	row2doc( $row, $doc )->update;
119	# eval { $doc->update };
120	warn $@ ? "$count ERROR $_id $@\n" : "$count updated $_id\n";
121	} else {
122	warn "$count created $_id\n";
123	}
124
125	$last_row = $row->{id};
126	$count++;
127
128	commit_last_row if $count % 100 == 0 # checkpoint every 100 records
129	}
130
131	commit_last_row;
132
133	__END__
134
135	$sql = qq{
136	update items_userdata
137	set value_numeric = 1
138	where label = 'read' and item_id in ($ids)
139	};
140
141	$dbh->do( $sql );
142