From 83780af41e3981d0da3b1c1bc3f071fea4538d55 Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Sat, 22 Sep 2012 15:28:19 +0000
Subject: Fix migrations.

---
 migrations/1_add_roles.sql                       |  4 ++--
 migrations/3_fix_lazychat_message_visibility.sql |  2 +-
 migrations/4_add_user_default_target.sql         |  5 +++++
 migrations/5_add_posts.sql                       | 17 ++++++++++++++---
 migrations/6_genericize_access_control.sql       |  7 ++++++-
 5 files changed, 28 insertions(+), 7 deletions(-)

diff --git a/migrations/1_add_roles.sql b/migrations/1_add_roles.sql
index f55d6bf..802d9f8 100644
--- a/migrations/1_add_roles.sql
+++ b/migrations/1_add_roles.sql
@@ -90,7 +90,7 @@ WITH world(id) AS (
   INSERT INTO lazychat_targets
        SELECT m.id, world.id
          FROM lazychat_messages m, world
-        WHERE m.visibility = 'public';
+        WHERE m.visibility = 'public'
 )
 INSERT INTO role_tags SELECT id, 'world' FROM world;
 
@@ -131,7 +131,7 @@ $$ LANGUAGE plpgsql;
 CREATE TRIGGER new_user_put_in_universal_role
   AFTER INSERT ON users
   FOR EACH ROW
-  EXECUTE PROCEDURE put_new_user_in_universal_role();
+  EXECUTE PROCEDURE new_user_put_in_universal_role();
 
 CREATE FUNCTION new_user_put_user_in_user_role() RETURNS TRIGGER AS $$
 BEGIN
diff --git a/migrations/3_fix_lazychat_message_visibility.sql b/migrations/3_fix_lazychat_message_visibility.sql
index 4af6b30..9f200ef 100644
--- a/migrations/3_fix_lazychat_message_visibility.sql
+++ b/migrations/3_fix_lazychat_message_visibility.sql
@@ -3,5 +3,5 @@ CREATE OR REPLACE VIEW user_visible_lazychat_messages AS
      FROM effective_user_roles eur, lazychat_targets t
     WHERE t.target = eur.role
   UNION
-    SELECT m.author, m.message
+    SELECT m.author, m.id
     FROM lazychat_messages m;
diff --git a/migrations/4_add_user_default_target.sql b/migrations/4_add_user_default_target.sql
index 89d0b25..4486410 100644
--- a/migrations/4_add_user_default_target.sql
+++ b/migrations/4_add_user_default_target.sql
@@ -1,3 +1,5 @@
+BEGIN TRANSACTION;
+
 CREATE TABLE user_default_target(
   "user" INTEGER NOT NULL,
   target INTEGER NOT NULL,
@@ -10,3 +12,6 @@ INSERT INTO user_default_target
      SELECT users.id, roles.id
        FROM users
       INNER JOIN roles ON (roles."name" = 'Inner Circle');
+
+COMMIT;
+
diff --git a/migrations/5_add_posts.sql b/migrations/5_add_posts.sql
index 49905f4..e145086 100644
--- a/migrations/5_add_posts.sql
+++ b/migrations/5_add_posts.sql
@@ -1,3 +1,5 @@
+BEGIN TRANSACTION;
+
 -- Add table.
 CREATE TABLE posts(
   id          SERIAL    NOT NULL,
@@ -14,17 +16,23 @@ 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 $$
+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 offst;
-  UPDATE bookmarks SET id = id + offst cascade;
+  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;
@@ -43,3 +51,6 @@ 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;
+
diff --git a/migrations/6_genericize_access_control.sql b/migrations/6_genericize_access_control.sql
index b415d5b..c7727bf 100644
--- a/migrations/6_genericize_access_control.sql
+++ b/migrations/6_genericize_access_control.sql
@@ -1,3 +1,5 @@
+BEGIN;
+
 ALTER TABLE lazychat_targets RENAME TO post_targets;
 
 CREATE VIEW user_visible_posts AS
@@ -5,10 +7,13 @@ CREATE VIEW user_visible_posts AS
      FROM effective_user_roles eur, post_targets t
     WHERE t.target = eur.role
   UNION
-    SELECT m.owner, m.message
+    SELECT m.owner, m.id
     FROM posts m;
 
 CREATE OR REPLACE VIEW user_visible_lazychat_messages AS
   SELECT uvp.user, uvp.message
     FROM user_visible_posts uvp
     INNER JOIN lazychat_messages lm ON lm.id = uvp.message;
+
+COMMIT;
+
-- 
cgit v1.2.3