diff options
| -rw-r--r-- | migrations/1_add_roles.sql | 4 | ||||
| -rw-r--r-- | migrations/3_fix_lazychat_message_visibility.sql | 2 | ||||
| -rw-r--r-- | migrations/4_add_user_default_target.sql | 5 | ||||
| -rw-r--r-- | migrations/5_add_posts.sql | 17 | ||||
| -rw-r--r-- | 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; + |