blob: e145086b877ba9334abaa6c946588125c409e3ae (
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
|
BEGIN TRANSACTION;
-- Add table.
CREATE TABLE posts(
id SERIAL NOT NULL,
owner INTEGER,
date TIMESTAMP WITH TIME ZONE DEFAULT now(),
PRIMARY KEY(id),
FOREIGN KEY(owner) REFERENCES users
);
-- Uniquify bookmarks.id wrt. lazychat_messages.id.
ALTER TABLE bookmark_tags
DROP CONSTRAINT bookmark_tags_bookmark_fkey;
ALTER TABLE bookmark_tags
ADD CONSTRAINT bookmark_tags_bookmark_fkey
FOREIGN KEY (bookmark) REFERENCES bookmarks(id) ON UPDATE CASCADE;
CREATE FUNCTION tmp_update_bookmarx_ids() RETURNS VOID AS $$
DECLARE
min1 INTEGER;
min2 INTEGER;
offst INTEGER;
bookmark RECORD;
BEGIN
SELECT MAX(id)+1 FROM lazychat_messages INTO min1;
SELECT MAX(id) FROM bookmarks INTO min2;
SELECT GREATEST(min1, min2) INTO offst;
UPDATE bookmarks SET id = id + offst;
END;
$$ LANGUAGE plpgsql;
--ALTER TABLE bookmarks DROP CONSTRAINT bookmarks_pkey;
SELECT tmp_update_bookmarx_ids();
DROP FUNCTION tmp_update_bookmarx_ids();
--ALTER TABLE bookmarks ADD CONSTRAINT bookmarks_pkey PRIMARY KEY (id);
-- Enable inheritance.
ALTER TABLE bookmarks INHERIT posts;
ALTER TABLE lazychat_messages RENAME COLUMN author TO owner;
ALTER TABLE lazychat_messages INHERIT posts;
-- Merge id seqs.
ALTER TABLE bookmarks ALTER COLUMN id SET DEFAULT nextval('posts_id_seq'::regclass);
ALTER TABLE lazychat_messages ALTER COLUMN id SET DEFAULT nextval('posts_id_seq'::regclass);
DROP SEQUENCE bookmarks_id_seq;
DROP SEQUENCE lazychat_messages_id_seq;
SELECT setval('posts_id_seq', MAX(id)+1) FROM posts;
-- FIXME: We ought to add some kind of constraint on bookmarks.id and
-- lazychat_messages.id such that uniqueness across both tables is
-- guaranteed.
COMMIT;
|
