couchdb/scripts/reblog2couchdb.pl

#!/usr/bin/perl -w

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

$|++;

my $database = 'reblog';

my $connect = "DBI:mysql:database=$database";
#$connect = "DBI:mysql:database=$database;host=localhost;port=13306";

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

# select all posts which have been read or unread
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
--      where label = 'read' and value_numeric = ?
        join feeds on items.feed_id = feeds.id
        order by items.id asc
--      limit 42
};

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

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

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

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

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

my @docs = $db->listDocs;
my $row_id = shift @docs || 0;

my $pk = 'id';

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 ),$/;
        }
}

__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			my $connect = "DBI:mysql:database=$database";
13			#$connect = "DBI:mysql:database=$database;host=localhost;port=13306";
14
15			my $dbh = DBI->connect($connect,"","") \|\| die $DBI::errstr;
16
17			# select all posts which have been read or unread
18			my $sql = qq{
19			select
20	dpavlin	4	items.id as _id,
21			items.*,
22			feeds.url as feed_url,
23			feeds.title as feed_title,
24			feeds.link as feed_link,
25			feeds.description as feed_description
26	dpavlin	2	from items
27	dpavlin	4	join items_userdata on items.id = item_id
28	dpavlin	2	-- where label = 'read' and value_numeric = ?
29	dpavlin	4	join feeds on items.feed_id = feeds.id
30			order by items.id asc
31			-- limit 42
32	dpavlin	2	};
33
34			my $sth = $dbh->prepare($sql) \|\| die $dbh->errstr();
35			$sth->execute( 1 ) \|\| die $sth->errstr();
36
37	dpavlin	4	warn dump( $sth->{NAME} );
38
39	dpavlin	2	print "found ",$sth->rows," items to process...";
40
41			my $c = CouchDB::Client->new(uri => 'http://localhost:5984/');
42
43			$c->testConnection or die "The server cannot be reached";
44			print "Running version " . $c->serverInfo->{version} . "\n";
45			my $db = $c->newDB( $database );
46			$db->create unless $c->dbExists( $database );
47
48			my @docs = $db->listDocs;
49			my $row_id = shift @docs \|\| 0;
50
51			my $pk = 'id';
52
53			while (my $row = $sth->fetchrow_hashref() ) {
54	dpavlin	4	my $_id = $row->{_id} \|\| die "row needs _id";
55	dpavlin	2	my $doc = $db->newDoc( $_id );
56	dpavlin	4
57			sub row2doc {
58			my ( $row, $doc ) = @_;
59			my $a = delete( $row->{xml} );
60			$doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;
61			my $a = delete( $row->{content} );
62			$doc->addAttachment( 'content.html', 'text/html', $a ) if $a;
63			$doc->{data} = $row;
64			return $doc;
65			}
66
67			row2doc( $row, $doc );
68
69	dpavlin	2	eval { $doc->create };
70			if ( $@ ) {
71	dpavlin	4	$doc->retrieve;
72			row2doc( $row, $doc )->update;
73			# eval { $doc->update };
74			warn ( $@ ? "ERROR $_id $@" : "updated $_id" ), $/;
75	dpavlin	2	} else {
76	dpavlin	4	warn "created ",dump( $row ),$/;
77	dpavlin	2	}
78			}
79
80			__END__
81
82			$sql = qq{
83			update items_userdata
84			set value_numeric = 1
85			where label = 'read' and item_id in ($ids)
86			};
87
88			$dbh->do( $sql );
89