summaryrefslogtreecommitdiff
path: root/migrations
diff options
context:
space:
mode:
authorMatthias Andreas Benkard <code@mail.matthias.benkard.de>2012-08-18 17:39:46 +0200
committerMatthias Andreas Benkard <code@mail.matthias.benkard.de>2012-08-18 17:39:46 +0200
commit573113dfb99e4d6eb6086b53dee1afcb845c18a9 (patch)
tree2aac5cde2d91273db4f93af61905da34e28d0fa3 /migrations
parent202ef602aa8672c74619cd2db47dc3e1bab747a6 (diff)
Add table posts.
Diffstat (limited to 'migrations')
-rw-r--r--migrations/5_add_posts.sql45
1 files changed, 45 insertions, 0 deletions
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.