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.

2 files changed, 23 insertions, 4 deletions

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;