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
|
BEGIN;
ALTER TABLE ratings ADD id SERIAL UNIQUE;
GRANT ALL ON ratings_id_seq TO pypi;
CREATE TABLE comments(
id SERIAL PRIMARY KEY,
rating INTEGER REFERENCES ratings(id) ON DELETE CASCADE,
user_name TEXT REFERENCES users ON DELETE CASCADE,
date TIMESTAMP,
message TEXT,
in_reply_to INTEGER REFERENCES comments ON DELETE CASCADE
);
GRANT ALL ON comments TO pypi;
GRANT ALL ON comments_id_seq TO pypi;
INSERT INTO comments(rating, user_name, date, message)
SELECT id, user_name, date, message FROM ratings WHERE message!='';
ALTER TABLE ratings DROP COLUMN message;
CREATE TABLE comments_journal(
name text,
version text,
id INTEGER,
submitted_by TEXT REFERENCES users ON DELETE CASCADE,
date TIMESTAMP,
action TEXT,
FOREIGN KEY (name, version) REFERENCES releases (name, version) ON DELETE CASCADE
);
GRANT ALL ON comments_journal TO pypi;
END;
|
