summaryrefslogtreecommitdiff
path: root/util/updates/update_2.14.sql
blob: e8930843b02f38cd8cddd8fd5db7dd4fa3588f3c (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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

-- add vn.c_olang
ALTER TABLE vn ADD COLUMN c_olang language[] NOT NULL DEFAULT '{}';


-- reload functions
\i util/sql/func.sql


-- redefine the triggers to use the new conditional triggers in PostgreSQL 9.0

DROP TRIGGER hidlock_update ON vn;
DROP TRIGGER hidlock_update ON producers;
DROP TRIGGER hidlock_update ON releases;
CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON vn            FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON producers     FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
CREATE TRIGGER hidlock_update             BEFORE UPDATE           ON releases      FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();


DROP TRIGGER vn_stats_update            ON vn;
DROP TRIGGER producers_stats_update     ON producers;
DROP TRIGGER releases_stats_update      ON releases;
DROP TRIGGER threads_stats_update       ON threads;
DROP TRIGGER threads_posts_stats_update ON threads_posts;
DROP TRIGGER users_stats_update         ON users;
CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON vn            FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON producers     FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON releases      FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON threads       FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON threads       FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache_edit           AFTER  UPDATE           ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
CREATE TRIGGER stats_cache                AFTER  INSERT OR DELETE ON users         FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();

DROP TRIGGER vn_anime_aid ON vn_anime;
CREATE TRIGGER vn_anime_aid_new           BEFORE INSERT           ON vn_anime      FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
CREATE TRIGGER vn_anime_aid_edit          BEFORE UPDATE           ON vn_anime      FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid();

DROP TRIGGER anime_fetch_notify ON anime;
CREATE TRIGGER anime_fetch_notify         AFTER  INSERT OR UPDATE ON anime         FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify();

DROP TRIGGER vn_rev_image_notify ON vn_rev;
CREATE TRIGGER vn_rev_image_notify        AFTER  INSERT OR UPDATE ON vn_rev        FOR EACH ROW WHEN (NEW.image < 0) EXECUTE PROCEDURE vn_rev_image_notify();

DROP TRIGGER screenshot_process_notify ON screenshots;
CREATE TRIGGER screenshot_process_notify  AFTER  INSERT OR UPDATE ON screenshots   FOR EACH ROW WHEN (NEW.processed = FALSE) EXECUTE PROCEDURE screenshot_process_notify();

DROP TRIGGER vn_relgraph_notify ON vn;
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();

DROP TRIGGER producer_relgraph_notify ON producers;
CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW
  WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph
     OR OLD.latest IS DISTINCT FROM NEW.latest
  ) EXECUTE PROCEDURE producer_relgraph_notify();

DROP TRIGGER release_vncache_update ON releases;
CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW
  WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden)
  EXECUTE PROCEDURE release_vncache_update();

DROP TRIGGER notify_dbdel ON vn;
DROP TRIGGER notify_dbdel ON producers;
DROP TRIGGER notify_dbdel ON releases;
CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
CREATE TRIGGER notify_dbdel               AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();

DROP TRIGGER notify_listdel ON vn;
DROP TRIGGER notify_listdel ON releases;
CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
CREATE TRIGGER notify_listdel             AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();

DROP TRIGGER notify_dbedit ON vn;
DROP TRIGGER notify_dbedit ON producers;
DROP TRIGGER notify_dbedit ON releases;
CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON vn            FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON producers     FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON releases      FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();

DROP TRIGGER notify_announce ON threads_posts;
CREATE TRIGGER notify_announce            AFTER  INSERT           ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();

DROP TRIGGER vn_vnsearch_notify ON vn;
CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW
  WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL AND NOT NEW.hidden
     OR NEW.hidden IS DISTINCT FROM OLD.hidden
     OR NEW.latest IS DISTINCT FROM OLD.latest
  ) EXECUTE PROCEDURE vn_vnsearch_notify();

DROP TRIGGER vn_vnsearch_notify ON releases;
CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW
  WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest)
  EXECUTE PROCEDURE vn_vnsearch_notify();



-- add ON DELETE clause to all foreign keys referencing users (id)
-- and change some defaults/constraints to make sure it'll actually work

ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey;
ALTER TABLE changes             ADD FOREIGN KEY (requester) REFERENCES users         (id) ON DELETE SET DEFAULT;

UPDATE notifications SET c_byuser = 0 WHERE c_byuser IS NULL;
ALTER TABLE notifications ALTER COLUMN c_byuser SET DEFAULT 0;
ALTER TABLE notifications ALTER COLUMN c_byuser SET NOT NULL;
ALTER TABLE notifications DROP CONSTRAINT notifications_uid_fkey;
ALTER TABLE notifications DROP CONSTRAINT notifications_c_byuser_fkey;
ALTER TABLE notifications       ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE notifications       ADD FOREIGN KEY (c_byuser)  REFERENCES users         (id) ON DELETE SET DEFAULT;

ALTER TABLE rlists DROP CONSTRAINT rlists_uid_fkey;
ALTER TABLE rlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;

ALTER TABLE sessions DROP CONSTRAINT sessions_uid_fkey;
ALTER TABLE sessions            ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;

ALTER TABLE tags ALTER COLUMN addedby SET DEFAULT 0;
ALTER TABLE tags DROP CONSTRAINT tags_addedby_fkey;
ALTER TABLE tags                ADD FOREIGN KEY (addedby)   REFERENCES users         (id) ON DELETE SET DEFAULT;

ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey;
ALTER TABLE tags_vn             ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;

ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_uid_fkey;
ALTER TABLE threads_posts       ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;

ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey;
ALTER TABLE votes               ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;

ALTER TABLE wlists DROP CONSTRAINT wlists_uid_fkey;
ALTER TABLE wlists              ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;


-- regenerate vn.c_* columns
SELECT COUNT(*) FROM (SELECT update_vncache(id) FROM vn WHERE NOT hidden) s;