/[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 15 by dpavlin, Thu Aug 28 21:53:49 2008 UTC revision 36 by dpavlin, Tue Apr 28 21:34:58 2009 UTC
# Line 14  my $database = 'reblog'; Line 14  my $database = 'reblog';
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,                  i.id as item_id,
33                  items.*,  --              i.guid as _id,
34                  feeds.url as feed_url,                  i.link as _id,
35                  feeds.title as feed_title,                  i.*,
36                  feeds.link as feed_link,                  f.url as feed_url,
37                  feeds.description as feed_description                  f.title as feed_title,
38          from items                  f.link as feed_link,
39          join items_userdata on items.id = item_id                  f.description as feed_description
40          join feeds on items.feed_id = feeds.id          from items i
41          where items.id > ? and items_userdata.label = 'published' and items_userdata.value_numeric = 1          join published_items p on i.id = p.item_id
42          order by items.id asc          join feeds f on i.feed_id = f.id
43            where i.id > ?
44            order by i.id asc
45          limit 1000          limit 1000
46  };  };
47    
48  my $sql_tags = qq{  my $sql_tags = qq{
49  select  select
50          items_userdata.item_id,          items_userdata.item_id,
51          t.value_long as tags,          value_long as tags,
52          items_userdata.timestamp          timestamp
53  from items_userdata  from items_userdata
54  join items_userdata as t  join published_items p
55          on items_userdata.item_id = t.item_id and t.label='tags'          on items_userdata.item_id = p.item_id and label='tags'
56  where  where
57          items_userdata.item_id > ? and          items_userdata.item_id > ?
         items_userdata.label = 'published' and  
         items_userdata.value_numeric = 1  
58  order by items_userdata.item_id asc  order by items_userdata.item_id asc
59  };  };
60    
61  my $c = CouchDB::Client->new(uri => 'http://localhost:5984/');  my $c = CouchDB::Client->new(uri => 'http://192.168.1.13:5984/');
62    
63  $c->testConnection or die "The server cannot be reached";  $c->testConnection or die "The server cannot be reached";
64  print "CouchDB version " . $c->serverInfo->{version} . "\n";  print "CouchDB version " . $c->serverInfo->{version} . "\n";
65  my $db = $c->newDB( $database );  my $db = $c->newDB( $database );
66  $db->create unless $c->dbExists( $database );  $db->create unless $c->dbExists( $database );
67    
68  my $status = $db->newDoc( '_sync' );  my $status = $db->newDoc( 'last_sync' );
69  eval { $status->retrieve };  eval { $status->retrieve };
70  $status->create if $@;  $status->create if $@;
71    
# Line 67  sub commit_last_row { Line 80  sub commit_last_row {
80          $status->update;          $status->update;
81  }  }
82    
 my $dbh = DBI->connect($dbi,"","",{ RaiseError => 1 });  
   
83  print "Fetching items from $dbi id > $last_row\n";  print "Fetching items from $dbi id > $last_row\n";
84    
85  my $sth = $dbh->prepare($sql);  my $sth = $dbh->prepare($sql);
# Line 87  my $count = 0; Line 98  my $count = 0;
98  my $row_tags = $sth_tags->fetchrow_hashref();  my $row_tags = $sth_tags->fetchrow_hashref();
99    
100  while (my $row = $sth->fetchrow_hashref() ) {  while (my $row = $sth->fetchrow_hashref() ) {
101          my $_id = $row->{_id} || die "row needs _id";          my $_id = $row->{_id} || "c$count";
102            $_id =~ s{\W+}{_}g;
103            $_id =~ s{_+$}{};
104          my $doc = $db->newDoc( $_id );          my $doc = $db->newDoc( $_id );
105    
106          while ( $row_tags && $row_tags->{item_id} < $row->{_id} ) {          while ( $row_tags && $row_tags->{item_id} < $row->{item_id} ) {
107                  $row_tags = $sth_tags->fetchrow_hashref();                  $row_tags = $sth_tags->fetchrow_hashref();
108                  warn "## got tags: ",dump( $row_tags ) if $debug;                  warn "## got tags: ",dump( $row_tags ) if $debug;
109          }          }
# Line 101  while (my $row = $sth->fetchrow_hashref( Line 114  while (my $row = $sth->fetchrow_hashref(
114                  $doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;                  $doc->addAttachment( 'item.xml', 'application/xhtml+xml', $a ) if $a;
115                  $a = delete( $row->{content} );                  $a = delete( $row->{content} );
116                  $doc->addAttachment( 'content.html', 'text/html', $a ) if $a;                  $doc->addAttachment( 'content.html', 'text/html', $a ) if $a;
117                  if ( $row_tags && $row_tags->{item_id} == $row->{_id} ) {                  if ( $row_tags && $row_tags->{item_id} == $row->{item_id} ) {
118                          $row->{tags} = [ split(/\s+/, $row_tags->{tags} ) ];                          $row->{tags} = [ split(/\s+/, $row_tags->{tags} ) ];
119                          warn "++ ",$row->{item_id}, dump( $row->{tags} );                          warn "++ ",$row->{item_id}, dump( $row->{tags} );
120                  }                  }
Legend:
Removed from v.15  
changed lines
  Added in v.36
  ViewVC Help
Powered by ViewVC 1.1.26