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.

1 files changed, 19 insertions, 0 deletions

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;