summaryrefslogtreecommitdiff
path: root/util/updates/update_2.10.sql
blob: e54e028cb2e21a1ce4b0379450920de61cbbe3ae (plain)
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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

-- no more bayesian rating for VN list on tag pages, just plain averages
DROP TABLE tags_vn_bayesian;
CREATE TABLE tags_vn_inherit (
  tag integer NOT NULL,
  vid integer NOT NULL,
  users integer NOT NULL,
  rating real NOT NULL,
  spoiler smallint NOT NULL
);


-- remove unused functions
DROP FUNCTION tag_vn_childs() CASCADE;
DROP FUNCTION tag_tree(integer, integer, boolean);
DROP TYPE tag_tree_item;


-- remove changes.causedby and give the affected changes to Multi
UPDATE changes SET requester = 1 WHERE causedby IS NOT NULL;
ALTER TABLE changes DROP COLUMN causedby;
UPDATE users SET
  c_changes = COALESCE((
    SELECT COUNT(id)
    FROM changes
    WHERE requester = users.id
    GROUP BY requester
  ), 0);


-- set default on releases_rev.released, required for the revision insertion abstraction
ALTER TABLE releases_rev ALTER COLUMN released SET DEFAULT 0;


-- type used for the revision inserting functions
CREATE TYPE edit_rettype      AS (iid integer, cid integer, rev integer);


-- import the new and updated functions
\i util/sql/func.sql


-- call update_vncache() when a release is added, edited, hidden or unhidden
CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update();


-- improved relgraph notify triggers
DROP TRIGGER vn_relgraph_notify ON vn;
CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
DROP TRIGGER vn_relgraph_notify ON producers;
CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();


-- more efficient version of tag_vn_calc()
SELECT tag_vn_calc();


-- regenerate the relation graphs so that they contain IDs for highlighting
UPDATE vn SET rgraph = NULL;
UPDATE producers SET rgraph = NULL;
DELETE FROM relgraphs;