diff options
| -rw-r--r-- | migrations/5_add_posts.sql | 45 | ||||
| -rw-r--r-- | schema.sql | 13 | ||||
| -rw-r--r-- | src/mulk/benki/lazychat.clj | 16 |
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. @@ -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) |