blob: 897b0a2b9409d8b0194c0c3c43cbaf2add03ec56 (
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
|
-- store relation graph image maps in the database
CREATE TABLE relgraph (
id SERIAL NOT NULL PRIMARY KEY,
cmap text NOT NULL DEFAULT ''
) WITHOUT OIDS;
DROP SEQUENCE relgraph_seq;
ALTER TABLE vn ALTER COLUMN rgraph DROP NOT NULL;
ALTER TABLE vn ALTER COLUMN rgraph SET DEFAULT NULL;
UPDATE vn SET rgraph = NULL;
ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id) DEFERRABLE INITIALLY DEFERRED;
-- add foreign table constraint to changes.causedby
ALTER TABLE changes ALTER COLUMN causedby DROP NOT NULL;
ALTER TABLE changes ALTER COLUMN causedby SET DEFAULT NULL;
UPDATE changes c SET causedby = NULL
WHERE causedby = 0
-- yup, there are some problems caused by deleted revisions in older versions of the site
OR NOT EXISTS(SELECT 1 FROM changes WHERE c.causedby = id);
ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
-- another foreign key constraint: (threads.id, threads.count) -> (threads_posts.tid, threads_posts.num)
-- threads_posts converted to smallint as well
ALTER TABLE threads_posts ALTER COLUMN num TYPE smallint;
ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
-- screenshots now have a relation with releases
ALTER TABLE vn_screenshots ADD COLUMN rid integer DEFAULT NULL REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED;
|