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, |
36 |
feeds.link as feed_link, |
feeds.link as feed_link, |
37 |
feeds.description as feed_description |
feeds.description as feed_description |
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 |
where items.id > ? and items_userdata.label = 'published' and items_userdata.value_numeric = 1 |
where items.id > ? |
42 |
order by items.id asc |
order by items.id asc |
43 |
limit 1000 |
limit 1000 |
44 |
}; |
}; |
46 |
my $sql_tags = qq{ |
my $sql_tags = qq{ |
47 |
select |
select |
48 |
items_userdata.item_id, |
items_userdata.item_id, |
49 |
t.value_long as tags, |
value_long as tags, |
50 |
items_userdata.timestamp |
timestamp |
51 |
from items_userdata |
from items_userdata |
52 |
join items_userdata as t |
join published_items p |
53 |
on items_userdata.item_id = t.item_id and t.label='tags' |
on items_userdata.item_id = p.item_id and label='tags' |
54 |
where |
where |
55 |
items_userdata.item_id > ? and |
items_userdata.item_id > ? |
|
items_userdata.label = 'published' and |
|
|
items_userdata.value_numeric = 1 |
|
56 |
order by items_userdata.item_id asc |
order by items_userdata.item_id asc |
57 |
}; |
}; |
58 |
|
|
78 |
$status->update; |
$status->update; |
79 |
} |
} |
80 |
|
|
|
my $dbh = DBI->connect($dbi,"","",{ RaiseError => 1 }); |
|
|
|
|
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); |
my $sth = $dbh->prepare($sql); |