/[couchdb]/scripts/reblog2couchdb.pl
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /scripts/reblog2couchdb.pl

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 10 by dpavlin, Fri Aug 8 19:19:12 2008 UTC revision 28 by dpavlin, Sat Apr 25 00:11:51 2009 UTC
# Line 7  use Data::Dump qw/dump/; Line 7  use Data::Dump qw/dump/;
7    
8  $|++;  $|++;
9    
10    my $debug = @ARGV ? 1 : 0;
11    
12  my $database = 'reblog';  my $database = 'reblog';
13    
14  my $dbi = "DBI:mysql:database=$database";  my $dbi = "DBI:mysql:database=$database";
15  $dbi .= ";host=127.0.0.1;port=13306";   # XXX over ssh  $dbi .= ";host=127.0.0.1;port=13306";   # XXX over ssh
16    
17    my $dbh = DBI->connect($dbi,"","",{ RaiseError => 1 });
18    
19    $dbh->do(qq{
20            create temporary table published_items as
21            select
22                    item_id
23            from
24                    items_userdata
25            where
26                    label = 'published' and
27                    value_numeric = 1
28    });
29    
30  my $sql = qq{  my $sql = qq{
31          select          select
32                  items.id as _id,                  items.id as _id,
# Line 20  my $sql = qq{ Line 35  my $sql = qq{
35                  feeds.title as feed_title,                  feeds.title as feed_title,
36                  feeds.link as feed_link,                  feeds.link as feed_link,
37                  feeds.description as feed_description                  feeds.description as feed_description
 --              t.value_long as tags  
38          from items          from items
39          join items_userdata on items.id = item_id          join published_items on items.id = item_id
40          join feeds on items.feed_id = feeds.id          join feeds on items.feed_id = feeds.id
41  --      left outer join items_userdata as t on items.id = t.item_id and t.label='tags'          where items.id > ?
         where items.id > ? and items_userdata.label = 'published' and items_userdata.value_numeric = 1  
42          order by items.id asc          order by items.id asc
43          limit 1000          limit 1000
44  };  };
45    
46  my $c = CouchDB::Client->new(uri => 'http://localhost:5984/');  my $sql_tags = qq{
47    select
48            items_userdata.item_id,
49            value_long as tags,
50            timestamp
51    from items_userdata
52    join published_items p
53            on items_userdata.item_id = p.item_id and label='tags'
54    where
55            items_userdata.item_id > ?
56    order by items_userdata.item_id asc
57    };
58    
59    my $c = CouchDB::Client->new(uri => 'http://192.168.1.13:5984/');
60    
61  $c->testConnection or die "The server cannot be reached";  $c->testConnection or die "The server cannot be reached";
62  print "CouchDB version " . $c->serverInfo->{version} . "\n";  print "CouchDB version " . $c->serverInfo->{version} . "\n";
63  my $db = $c->newDB( $database );  my $db = $c->newDB( $database );
64  $db->create unless $c->dbExists( $database );  $db->create unless $c->dbExists( $database );
65    
66  my $status = $db->newDoc( '_sync' );  my $status = $db->newDoc( 'last_sync' );
67  eval { $status->retrieve };  eval { $status->retrieve };
68  $status->create if $@;  $status->create if $@;
69    
70  print "status ",dump( $status->{data} ), "\n";  print "status ",dump( $status->{data} ), "\n";
71    
72  my $last_row = $status->{data}->{last_row_id} || 0;  my $last_row = $status->{data}->{last_row_id} || 0;
73    $last_row = 0 if $debug;
74    
75  sub commit_last_row {  sub commit_last_row {
76          warn "commit_last_row $last_row\n";          warn "commit_last_row $last_row\n";
# Line 51  sub commit_last_row { Line 78  sub commit_last_row {
78          $status->update;          $status->update;
79  }  }
80    
 my $dbh = DBI->connect($dbi,"","") || die $DBI::errstr;  
   
81  print "Fetching items from $dbi id > $last_row\n";  print "Fetching items from $dbi id > $last_row\n";
82    
83  my $sth = $dbh->prepare($sql) || die $dbh->errstr();  my $sth = $dbh->prepare($sql);
84  $sth->execute( $last_row ) || die $sth->errstr();  $sth->execute( $last_row );
85    
86  warn dump( $sth->{NAME} );  warn dump( $sth->{NAME} );
87    
88  print "found ",$sth->rows," items to process...\n";  print "found ",$sth->rows," items to process...\n";
89    
90  my $pk = 'id';  my $sth_tags = $dbh->prepare($sql_tags);
91    $sth_tags->execute( $last_row );
92    print "found ",$sth_tags->rows, " tags found...\n";
93    
94  my $count = 0;  my $count = 0;
95    
96    my $row_tags = $sth_tags->fetchrow_hashref();
97    
98  while (my $row = $sth->fetchrow_hashref() ) {  while (my $row = $sth->fetchrow_hashref() ) {
99          my $_id = $row->{_id} || die "row needs _id";          my $_id = $row->{_id} || die "row needs _id";
100          my $doc = $db->newDoc( $_id );          my $doc = $db->newDoc( $_id );
101    
102            while ( $row_tags && $row_tags->{item_id} < $row->{_id} ) {
103                    $row_tags = $sth_tags->fetchrow_hashref();
104                    warn "## got tags: ",dump( $row_tags ) if $debug;
105            }
106    
107          sub row2doc {          sub row2doc {
108                  my ( $row, $doc ) = @_;                  my ( $row, $doc ) = @_;
109                  my $a = delete( $row->{xml} );                  my $a = delete( $row->{xml} );
110                  $doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;                  $doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;
111                  my $a = delete( $row->{content} );                  $a = delete( $row->{content} );
112                  $doc->addAttachment( 'content.html', 'text/html', $a ) if $a;                  $doc->addAttachment( 'content.html', 'text/html', $a ) if $a;
113                    if ( $row_tags && $row_tags->{item_id} == $row->{_id} ) {
114                            $row->{tags} = [ split(/\s+/, $row_tags->{tags} ) ];
115                            warn "++ ",$row->{item_id}, dump( $row->{tags} );
116                    }
117                  $doc->{data} = $row;                  $doc->{data} = $row;
118                    warn "## ",dump( $row ) if $debug;
119                  return $doc;                  return $doc;
120          }          }
121    
Legend:
Removed from v.10  
changed lines
  Added in v.28
  ViewVC Help
Powered by ViewVC 1.1.26