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.

7 files changed, 38 insertions, 19 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 ]
         )});
       }
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 5d4a801e..57f33efc 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -462,7 +462,7 @@ CREATE TABLE sessions (
 CREATE TABLE shop_denpa (
   id        text NOT NULL PRIMARY KEY,
   lastfetch timestamptz,
-  found     boolean NOT NULL DEFAULT false,
+  deadsince timestamptz,
   sku       text NOT NULL DEFAULT '',
   price     text NOT NULL DEFAULT ''
 );
@@ -471,7 +471,7 @@ CREATE TABLE shop_denpa (
 CREATE TABLE shop_dlsite (
   id        text NOT NULL PRIMARY KEY,
   lastfetch timestamptz,
-  found     boolean NOT NULL DEFAULT false,
+  deadsince timestamptz,
   shop      text NOT NULL DEFAULT '',
   price     text NOT NULL DEFAULT ''
 );
@@ -480,7 +480,7 @@ CREATE TABLE shop_dlsite (
 CREATE TABLE shop_jlist (
   id        text NOT NULL PRIMARY KEY,
   lastfetch timestamptz,
-  found     boolean NOT NULL DEFAULT false,
+  deadsince timestamptz,
   jbox      boolean NOT NULL DEFAULT false,
   price     text NOT NULL DEFAULT '' -- empty when unknown or not in stock
 );
@@ -489,7 +489,7 @@ CREATE TABLE shop_jlist (
 CREATE TABLE shop_mg (
   id        integer NOT NULL PRIMARY KEY,
   lastfetch timestamptz,
-  found     boolean NOT NULL DEFAULT false,
+  deadsince timestamptz,
   r18       boolean NOT NULL DEFAULT true,
   price     text NOT NULL DEFAULT ''
 );
diff --git a/util/updates/update_20190914.sql b/util/updates/update_20190914.sql
new file mode 100644
index 00000000..6c3cf21e
--- /dev/null
+++ b/util/updates/update_20190914.sql
@@ -0,0 +1,19 @@
+ALTER TABLE shop_denpa ALTER COLUMN found DROP NOT NULL;
+ALTER TABLE shop_denpa ALTER COLUMN found DROP DEFAULT;
+ALTER TABLE shop_denpa ALTER COLUMN found TYPE timestamptz USING CASE WHEN found THEN NULL ELSE lastfetch END;
+ALTER TABLE shop_denpa RENAME COLUMN found TO deadsince;
+
+ALTER TABLE shop_dlsite ALTER COLUMN found DROP NOT NULL;
+ALTER TABLE shop_dlsite ALTER COLUMN found DROP DEFAULT;
+ALTER TABLE shop_dlsite ALTER COLUMN found TYPE timestamptz USING CASE WHEN found THEN NULL ELSE lastfetch END;
+ALTER TABLE shop_dlsite RENAME COLUMN found TO deadsince;
+
+ALTER TABLE shop_jlist ALTER COLUMN found DROP NOT NULL;
+ALTER TABLE shop_jlist ALTER COLUMN found DROP DEFAULT;
+ALTER TABLE shop_jlist ALTER COLUMN found TYPE timestamptz USING CASE WHEN found THEN NULL ELSE lastfetch END;
+ALTER TABLE shop_jlist RENAME COLUMN found TO deadsince;
+
+ALTER TABLE shop_mg ALTER COLUMN found DROP NOT NULL;
+ALTER TABLE shop_mg ALTER COLUMN found DROP DEFAULT;
+ALTER TABLE shop_mg ALTER COLUMN found TYPE timestamptz USING CASE WHEN found THEN NULL ELSE lastfetch END;
+ALTER TABLE shop_mg RENAME COLUMN found TO deadsince;