summaryrefslogtreecommitdiff
path: root/util/updates/update_1.22.sql
blob: e37297653ba96b61e7c758dc5e0571d51ab078fa (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

-- store relation graph image maps in the database
CREATE TABLE relgraph (
  id SERIAL NOT NULL PRIMARY KEY,
  cmap text NOT NULL DEFAULT ''
) WITHOUT OIDS;

SELECT SETVAL('relgraph_id_seq', NEXTVAL('relgraph_seq'));
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 WHERE rgraph = 0;


-- 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;