Add table posts.

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)