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.

5 files changed, 15 insertions, 15 deletions

diff --git a/lib/Multi/DLsite.pm b/lib/Multi/DLsite.pm
index a329d2b7..46a0263c 100644
--- a/lib/Multi/DLsite.pm
+++ b/lib/Multi/DLsite.pm
@@ -67,12 +67,12 @@ sub data {
 
   # We have a price? Update database.
   if($price && $shop) {
-    pg_cmd q{UPDATE shop_dlsite SET found = TRUE, shop = $2, price = $3, lastfetch = NOW() WHERE id = $1}, [ $id, $shop, $price ];
+    pg_cmd q{UPDATE shop_dlsite SET deadsince = NULL, shop = $2, price = $3, lastfetch = NOW() WHERE id = $1}, [ $id, $shop, $price ];
     AE::log debug => "$prefix for $price at /$shop/";
 
   # Nothing? Update DB
   } else {
-    pg_cmd q{UPDATE shop_dlsite SET found = FALSE, lastfetch = NOW() WHERE id = $1}, [ $id ];
+    pg_cmd q{UPDATE shop_dlsite SET deadsince = COALESCE(deadsince, NOW()), lastfetch = NOW() WHERE id = $1}, [ $id ];
     AE::log info => "$prefix not found.";
   }
 }
diff --git a/lib/Multi/Denpa.pm b/lib/Multi/Denpa.pm
index 69fe9292..ac729dce 100644
--- a/lib/Multi/Denpa.pm
+++ b/lib/Multi/Denpa.pm
@@ -75,13 +75,13 @@ sub data {
     }
     my $price = 'US$ '.$prod->{variants}[0]{price};
     $price = 'free' if $price eq 'US$ 0.00';
-    pg_cmd 'UPDATE shop_denpa SET found = TRUE, lastfetch = NOW(), sku = $2, price = $3 WHERE id = $1',
+    pg_cmd 'UPDATE shop_denpa SET deadsince = NULL, lastfetch = NOW(), sku = $2, price = $3 WHERE id = $1',
       [ $prod->{handle}, $prod->{variants}[0]{sku}, $price ];
 
     delete $handles->{$prod->{handle}};
   }
 
-  pg_cmd 'UPDATE shop_denpa SET found = FALSE, lastfetch = NOW() WHERE id = $1', [$_]
+  pg_cmd 'UPDATE shop_denpa SET deadsince = COALESCE(deadsince, NOW()), lastfetch = NOW() WHERE id = $1', [$_]
     for (keys %$handles);
 
   AE::log info => "%d in shop, %d online, %d offline", scalar @{$data->{products}}, $db_count-scalar keys %$handles, scalar keys %$handles;
diff --git a/lib/Multi/JList.pm b/lib/Multi/JList.pm
index c449532a..515a34b5 100644
--- a/lib/Multi/JList.pm
+++ b/lib/Multi/JList.pm
@@ -58,12 +58,12 @@ sub data {
 
   # Out of stock? Update database.
   if($outofstock) {
-    pg_cmd q{UPDATE shop_jlist SET found = TRUE, jbox = $2, price = '', lastfetch = NOW() WHERE id = $1}, [ $id, $jbox ];
+    pg_cmd q{UPDATE shop_jlist SET deadsince = NULL, jbox = $2, price = '', lastfetch = NOW() WHERE id = $1}, [ $id, $jbox ];
     AE::log debug => "$prefix is out of stock on jbox=$jbox";
 
   # We have a price? Update database.
   } elsif($price) {
-    pg_cmd q{UPDATE shop_jlist SET found = TRUE, jbox = $2, price = $3, lastfetch = NOW() WHERE id = $1}, [ $id, $jbox, $price ];
+    pg_cmd q{UPDATE shop_jlist SET deadsince = NULL, jbox = $2, price = $3, lastfetch = NOW() WHERE id = $1}, [ $id, $jbox, $price ];
     AE::log debug => "$prefix for $price on jbox=$jbox";
 
   # No price or stock info? Try J-List
@@ -72,7 +72,7 @@ sub data {
 
   # Nothing at all? Update database.
   } else {
-    pg_cmd q{UPDATE shop_jlist SET found = FALSE, lastfetch = NOW() WHERE id = $1}, [ $id ];
+    pg_cmd q{UPDATE shop_jlist SET deadsince = coalesce(deadsince, NOW()), lastfetch = NOW() WHERE id = $1}, [ $id ];
     AE::log info => "$prefix not found on either JBOX or J-List.";
   }
 }
diff --git a/lib/Multi/MG.pm b/lib/Multi/MG.pm
index 81659153..3ffdee8c 100644
--- a/lib/Multi/MG.pm
+++ b/lib/Multi/MG.pm
@@ -56,7 +56,7 @@ sub data {
 
   # We have a price? Update database.
   if($price) {
-    pg_cmd q{UPDATE shop_mg SET found = TRUE, r18 = $2, price = $3, lastfetch = NOW() WHERE id = $1}, [ $id, $r18, $price ];
+    pg_cmd q{UPDATE shop_mg SET deadsince = NULL, r18 = $2, price = $3, lastfetch = NOW() WHERE id = $1}, [ $id, $r18, $price ];
     AE::log debug => "$prefix for $price on r18=$r18";
 
   # Try /r18/
@@ -65,7 +65,7 @@ sub data {
 
   # Nothing? Update DB
   } else {
-    pg_cmd q{UPDATE shop_mg SET found = FALSE, lastfetch = NOW() WHERE id = $1}, [ $id ];
+    pg_cmd q{UPDATE shop_mg SET deadsince = COALESCE(deadsince, NOW()), lastfetch = NOW() WHERE id = $1}, [ $id ];
     AE::log info => "$prefix not found.";
   }
 }
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 ]
         )});
       }