summaryrefslogtreecommitdiff
path: root/migrations
diff options
context:
space:
mode:
Diffstat (limited to 'migrations')
-rw-r--r--migrations/1_add_roles.sql4
-rw-r--r--migrations/3_fix_lazychat_message_visibility.sql2
-rw-r--r--migrations/4_add_user_default_target.sql5
-rw-r--r--migrations/5_add_posts.sql17
-rw-r--r--migrations/6_genericize_access_control.sql7
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;
+