From 573113dfb99e4d6eb6086b53dee1afcb845c18a9 Mon Sep 17 00:00:00 2001 From: Matthias Andreas Benkard Date: Sat, 18 Aug 2012 17:39:46 +0200 Subject: Add table posts. --- migrations/5_add_posts.sql | 45 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 45 insertions(+) create mode 100644 migrations/5_add_posts.sql (limited to 'migrations') diff --git a/migrations/5_add_posts.sql b/migrations/5_add_posts.sql new file mode 100644 index 0000000..49905f4 --- /dev/null +++ b/migrations/5_add_posts.sql @@ -0,0 +1,45 @@ +-- 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 or replace FUNCTION tmp_update_bookmarx_ids() RETURNS VOID AS $$ +DECLARE + offst INTEGER; + bookmark RECORD; +BEGIN + SELECT MAX(id)+1 FROM lazychat_messages INTO offst; + UPDATE bookmarks SET id = id + offst cascade; +END; +$$ LANGUAGE plpgsql; +SELECT tmp_update_bookmarx_ids(); +DROP FUNCTION tmp_update_bookmarx_ids(); + +-- 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. -- cgit v1.2.3