summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2022-07-31 17:07:48 +0200
committerYorhel <git@yorhel.nl>2022-07-31 17:07:49 +0200
commit691797f62bdc6df8307f6bd47b9ca14dda8920d7 (patch)
tree52a50373bf865faca97a64b22905824f3879dc24 /util
parentec20437fc576d156eb8ccec7ac49a01fecf76add (diff)
Add VN development status field
https://vndb.org/t18854
Diffstat (limited to 'util')
-rw-r--r--util/updates/2022-07-31-vn-devstatus.sql24
1 files changed, 24 insertions, 0 deletions
diff --git a/util/updates/2022-07-31-vn-devstatus.sql b/util/updates/2022-07-31-vn-devstatus.sql
new file mode 100644
index 00000000..7bc709a0
--- /dev/null
+++ b/util/updates/2022-07-31-vn-devstatus.sql
@@ -0,0 +1,24 @@
+ALTER TABLE vn ADD COLUMN devstatus smallint NOT NULL DEFAULT 0;
+ALTER TABLE vn_hist ADD COLUMN devstatus smallint NOT NULL DEFAULT 0;
+\i sql/editfunc.sql
+
+UPDATE vn SET devstatus = 0 WHERE devstatus <> 0;
+
+-- Heuristic: VN is considered cancelled if it meets all of the following criteria:
+-- * doesn't have a complete release
+-- * doesn't have any release after 2020
+-- * doesn't have multiple partial releases
+-- * doesn't have both a trial and partial release (weird heuristic, but there's many matching in-dev games)
+UPDATE vn SET devstatus = 2 WHERE
+ id NOT IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype = 'complete' OR released > 20200000)
+ AND id NOT IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype = 'partial' GROUP BY vid HAVING COUNT(r.id) > 1)
+ AND id NOT IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype IN('partial','trial') GROUP BY vid HAVING COUNT(DISTINCT rtype) = 2);
+
+-- Heuristic: VN is considerd in development if it's not cancelled and meets one of the following:
+-- * Has a future release date
+-- * Has no complete releases and only a single partial release
+UPDATE vn SET devstatus = 1 WHERE devstatus = 0 AND (c_released > 22020731 OR (
+ id NOT IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype = 'complete')
+ AND id IN(SELECT vid FROM releases_vn rv JOIN releases r ON r.id = rv.id WHERE NOT r.hidden AND rtype = 'partial' GROUP BY vid HAVING COUNT(r.id) = 1)));
+
+UPDATE vn_hist SET devstatus = v.devstatus FROM changes c JOIN vn v ON c.itemid = v.id WHERE vn_hist.chid = c.id AND v.devstatus <> vn_hist.devstatus;