summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-09-14 11:58:47 +0200
committerYorhel <git@yorhel.nl>2019-09-14 12:00:11 +0200
commit2b6a309daa3c3131bee7075246ff1e38a54cc53d (patch)
treef4a9d317f1af22b2469e0f09da00835d1da95765 /lib/VNDB/DB
parent5de48b359f21c8208d2c9c5b6fcfc69c41192c30 (diff)
SQL: Replace "shop_*.found" with "deadsince" timestamp
Has the inverted meaning. Keeping track of how long we've been unable to find a certain shop link is useful in weeding out temporary unavailability when querying for dead links.
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r--lib/VNDB/DB/Releases.pm12
1 files changed, 6 insertions, 6 deletions
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index 421b3217..940ff087 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -214,11 +214,11 @@ sub _enrich {
, r.l_dlsite, sdlsite.price AS l_dlsite_price, sdlsite.shop AS l_dlsite_shop
, r.l_dlsiteen, sdlsiteen.price AS l_dlsiteen_price, sdlsiteen.shop AS l_dlsiteen_shop
FROM releases$hist r
- LEFT JOIN shop_denpa sdenpa ON sdenpa.id = r.l_denpa AND sdenpa.found
- LEFT JOIN shop_dlsite sdlsite ON sdlsite.id = r.l_dlsite AND sdlsite.found
- LEFT JOIN shop_dlsite sdlsiteen ON sdlsiteen.id = r.l_dlsiteen AND sdlsiteen.found
- LEFT JOIN shop_jlist sjlist ON sjlist.id = r.l_jlist AND sjlist.found
- LEFT JOIN shop_mg smg ON smg.id = r.l_mg AND smg.found
+ LEFT JOIN shop_denpa sdenpa ON sdenpa.id = r.l_denpa AND sdenpa.lastfetch IS NOT NULL AND sdenpa.deadsince IS NULL
+ LEFT JOIN shop_dlsite sdlsite ON sdlsite.id = r.l_dlsite AND sdlsite.lastfetch IS NOT NULL AND sdlsite.deadsince IS NULL
+ LEFT JOIN shop_dlsite sdlsiteen ON sdlsiteen.id = r.l_dlsiteen AND sdlsiteen.lastfetch IS NOT NULL AND sdlsiteen.deadsince IS NULL
+ LEFT JOIN shop_jlist sjlist ON sjlist.id = r.l_jlist AND sjlist.lastfetch IS NOT NULL AND sjlist.deadsince IS NULL
+ LEFT JOIN shop_mg smg ON smg.id = r.l_mg AND smg.lastfetch IS NOT NULL AND smg.deadsince IS NULL
WHERE r.$colname IN(!l)",
[ keys %r ]
)});
@@ -229,7 +229,7 @@ sub _enrich {
} 0..$#$r;
if(keys %p) {
push(@{$r->[$p{$_->{gtin}}]{l_playasia}}, $_) for (@{$self->dbAll("
- SELECT gtin, price, url FROM shop_playasia WHERE gtin IN(!l)",
+ SELECT gtin, price, url FROM shop_playasia WHERE gtin IN(!l) AND price <> ''",
[ keys %p ]
)});
}