summaryrefslogtreecommitdiff
path: root/util/updates/2020-11-19-releases-official.sql
blob: badac9cfdecb7895e87a23a64bb36e5333ceafd7 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ALTER TABLE releases      ADD COLUMN official   boolean NOT NULL DEFAULT TRUE;
ALTER TABLE releases_hist ADD COLUMN official   boolean NOT NULL DEFAULT TRUE;

\i sql/editfunc.sql

-- A release is considered unofficial if it was published by an individual or
-- amateur group while the original developer is a company.
-- This should not have many false positives, but only covers a small part of the DB.
UPDATE releases r SET official = FALSE
 WHERE EXISTS(SELECT 1
        FROM releases_vn rv
        JOIN releases_vn rv2 ON rv.vid = rv2.vid
        JOIN releases r2 ON r2.id = rv2.id
        JOIN releases_producers rp2 ON rp2.id = rv2.id
        JOIN producers p ON p.id = rp2.pid
       WHERE NOT p.hidden AND NOT r2.hidden AND rp2.developer AND rv.id = r.id AND p.type = 'co')
  AND NOT EXISTS(SELECT 1 FROM releases_producers rp JOIN producers p ON p.id = rp.pid WHERE rp.id = r.id AND (rp.developer OR p.type = 'co'));

UPDATE releases_hist rh SET official = FALSE
 WHERE EXISTS(SELECT 1 FROM changes c JOIN releases r ON r.id = c.itemid WHERE c.id = rh.chid AND NOT r.official);