summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--migrations/5_add_posts.sql45
-rw-r--r--schema.sql13
-rw-r--r--src/mulk/benki/lazychat.clj16
3 files changed, 60 insertions, 14 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.
diff --git a/schema.sql b/schema.sql
index ae843f8..c555166 100644
--- a/schema.sql
+++ b/schema.sql
@@ -117,10 +117,15 @@ CREATE TABLE wiki_page_revisions(
);
-CREATE TABLE bookmarks(
+CREATE TABLE posts(
id SERIAL NOT NULL,
owner INTEGER,
date TIMESTAMP WITH TIME ZONE DEFAULT now(),
+ PRIMARY KEY(id),
+ FOREIGN KEY(owner) REFERENCES users
+);
+
+CREATE TABLE bookmarks INHERITS posts (
uri VARCHAR NOT NULL,
title VARCHAR,
description VARCHAR,
@@ -137,11 +142,7 @@ CREATE TABLE bookmark_tags(
FOREIGN KEY(bookmark) REFERENCES bookmarks
);
-
-CREATE TABLE lazychat_messages(
- id SERIAL NOT NULL,
- author INTEGER,
- date TIMESTAMP WITH TIME ZONE DEFAULT now(),
+CREATE TABLE lazychat_messages INHERITS posts (
content VARCHAR,
format VARCHAR NOT NULL,
PRIMARY KEY(id),
diff --git a/src/mulk/benki/lazychat.clj b/src/mulk/benki/lazychat.clj
index 2b0db68..75b0b16 100644
--- a/src/mulk/benki/lazychat.clj
+++ b/src/mulk/benki/lazychat.clj
@@ -48,11 +48,11 @@
(with-dbt
(when id
;; FIXME: Is this assertion sufficient? Is it too strict?
- (assert (query1 "SELECT 't' WHERE currval('lazychat_messages_id_seq') >= ?" id)))
+ (assert (query1 "SELECT 't' WHERE currval('posts_id_seq') >= ?" id)))
(let [id (or id
- (:id (query1 "SELECT nextval('lazychat_messages_id_seq')::INTEGER AS id")))]
+ (:id (query1 "SELECT nextval('posts_id_seq')::INTEGER AS id")))]
(sql/with-query-results ids
- ["INSERT INTO lazychat_messages(id, author, content, format)
+ ["INSERT INTO lazychat_messages(id, owner, content, format)
VALUES (?, ?, ?, ?)
RETURNING id"
id user content format]
@@ -84,7 +84,7 @@
{:content content, :visibility visibility,
:format format, :targets targets,
:referees referees, :id id,
- :author user, :date (java.util.Date.)})
+ :owner user, :date (java.util.Date.)})
{:type ::lafargue-message}))))))
(defn create-lazychat-message! [msg]
@@ -108,7 +108,7 @@
:referees []})))
(defn select-message [id]
- (let [message (query1 "SELECT author, content, format, visibility, date
+ (let [message (query1 "SELECT owner, content, format, visibility, date
FROM lazychat_messages
WHERE id = ?"
id)
@@ -145,9 +145,9 @@
(defmacro with-messages-visible-by-user [[messages user] & body]
`(sql/with-query-results ~messages
- ["SELECT m.id, m.author, m.date, m.content, m.format, u.first_name, u.last_name
+ ["SELECT m.id, m.owner, m.date, m.content, m.format, u.first_name, u.last_name
FROM lazychat_messages m
- JOIN users u ON (author = u.id)
+ JOIN users u ON (owner = u.id)
JOIN user_visible_lazychat_messages uvlm ON (uvlm.message = m.id)
WHERE uvlm.user IS NOT DISTINCT FROM ?
ORDER BY m.date DESC"
@@ -262,7 +262,7 @@
(defpage [:post "/lafargue/messages/genid"] {id :id}
(with-auth
(response/json
- (with-dbt (query1 "SELECT NEXTVAL('lazychat_messages_id_seq')")))))
+ (with-dbt (query1 "SELECT NEXTVAL('posts_id_seq')")))))
(defn init-lazychat! []
(receive-all lafargue-events push-message-to-xmpp)