6 use Data::Dump qw/dump/;
10 my $debug = @ARGV ? 1 : 0;
12 my $database = 'reblog';
14 my $dbi = "DBI:mysql:database=$database";
15 $dbi .= ";host=127.0.0.1;port=13306"; # XXX over ssh
17 my $dbh = DBI->connect($dbi,"","",{ RaiseError => 1 });
20 create temporary table published_items as
26 label = 'published' and
37 f.title as feed_title,
39 f.description as feed_description
41 join published_items p on i.id = p.item_id
42 join feeds f on i.feed_id = f.id
50 items_userdata.item_id,
54 join published_items p
55 on items_userdata.item_id = p.item_id and label='tags'
57 items_userdata.item_id > ?
58 order by items_userdata.item_id asc
61 my $conn = MongoDB::Connection->new;
62 my $db = $conn->get_database( $database );
63 my $items = $db->get_collection( 'items' );
66 $last_row = 0 if $debug;
68 print "Fetching items from $dbi id > $last_row\n";
70 my $sth = $dbh->prepare($sql);
71 $sth->execute( $last_row );
73 warn dump( $sth->{NAME} );
75 print "found ",$sth->rows," items to process...\n";
77 my $sth_tags = $dbh->prepare($sql_tags);
78 $sth_tags->execute( $last_row );
79 print "found ",$sth_tags->rows, " tags found...\n";
83 my $row_tags = $sth_tags->fetchrow_hashref();
85 while (my $row = $sth->fetchrow_hashref() ) {
86 my $_id = $row->{_id} || "c$count";
93 while ( $row_tags && $row_tags->{item_id} < $row->{item_id} ) {
94 $row_tags = $sth_tags->fetchrow_hashref();
95 warn "## got tags: ",dump( $row_tags ) if $debug;
98 if ( $row_tags && $row_tags->{item_id} == $row->{item_id} ) {
99 $doc->{tags} = [ split(/\s+/, $row_tags->{tags} ) ];
100 warn "++ ",$row->{item_id}, dump( $row->{tags} ),$/;
103 $items->insert( $doc );
105 $last_row = $row->{id};