1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
-- Two extra indices for performance
CREATE INDEX releases_producers_rid ON releases_producers (rid);
CREATE INDEX tags_vn_vid ON tags_vn (vid);
-- Extra language for ukrainian
ALTER TYPE language RENAME TO language_old;
CREATE TYPE language AS ENUM ('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'hu', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ru', 'sk', 'sv', 'tr', 'uk', 'vi', 'zh');
ALTER TABLE producers_rev ALTER COLUMN lang DROP DEFAULT;
ALTER TABLE producers_rev ALTER COLUMN lang TYPE language USING lang::text::language;
ALTER TABLE producers_rev ALTER COLUMN lang SET DEFAULT 'ja';
ALTER TABLE releases_lang ALTER COLUMN lang TYPE language USING lang::text::language;
ALTER TABLE vn ALTER COLUMN c_languages DROP DEFAULT;
DROP TRIGGER vn_relgraph_notify ON vn;
ALTER TABLE vn ALTER COLUMN c_languages TYPE language[] USING c_languages::text[]::language[];
CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph
OR OLD.latest IS DISTINCT FROM NEW.latest
OR OLD.c_released IS DISTINCT FROM NEW.c_released
OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
) EXECUTE PROCEDURE vn_relgraph_notify();
ALTER TABLE vn ALTER COLUMN c_languages SET DEFAULT '{}';
ALTER TABLE vn ALTER COLUMN c_olang DROP DEFAULT;
ALTER TABLE vn ALTER COLUMN c_olang TYPE language[] USING c_olang::text[]::language[];
ALTER TABLE vn ALTER COLUMN c_olang SET DEFAULT '{}';
DROP TYPE language_old;
-- VN votes * 10
-- (The WHERE prevents another *10 if this query has already been executed)
UPDATE votes SET vote = vote * 10 WHERE NOT EXISTS(SELECT 1 FROM votes WHERE vote > 10);
-- recalculate c_rating
UPDATE vn SET c_rating = (SELECT (
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)
*(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) /
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
);
|