(ns datalayer (:require redis tags) (:use clojure.contrib.sql clojure.contrib.json.write clojure.contrib.json.read config jedis message user utils)) ;;;; Message lookup (defn recent-posts-query [user-id] (format " SELECT u.user_id, u.nick, u.avatar, m.content, m.message_id%s FROM users u LEFT JOIN messages m on m.message_id = (SELECT message_id FROM messages WHERE user_id = u.user_id AND is_image AND room_id IN (SELECT room_id from rooms where admin_only = false) ORDER BY created_on desc LIMIT 1) WHERE u.user_id = ANY(?)" (if user-id (format ", EXISTS (SELECT 1 FROM tags WHERE tag = 'favorite' AND user_id = %s AND message_id = m.message_id) AS favorited" user-id) ", false AS favorited"))) (defn recent-posts-nick-query [user-id] (format " SELECT u.user_id, u.nick, u.avatar, m.content, m.message_id%s FROM users u LEFT JOIN messages m on m.message_id = (SELECT message_id FROM messages WHERE user_id = u.user_id AND is_image AND room_id IN (SELECT room_id from rooms where admin_only = false) ORDER BY created_on desc LIMIT 1) WHERE u.nick = ANY(?)" (if user-id (format ", EXISTS (SELECT 1 FROM tags WHERE tag = 'favorite' AND user_id = %s AND message_id = m.message_id) AS favorited" user-id) ", false AS favorited"))) (defn lookup-recent-posts [user-tag-id user-ids] (do-select [(recent-posts-query user-tag-id) (sql-array "int" user-ids)])) (defn lookup-recent-posts-tagless [user-ids] (do-select [(recent-posts-query nil) (sql-array "int" user-ids)])) (defn lookup-recent-posts-by-nicks [user-tag-id nicks] (do-select [(recent-posts-nick-query user-tag-id) (sql-array "varchar" nicks)])) (defn lookup-recent-posts-tagless-by-nicks [nicks] (do-select [(recent-posts-nick-query nil) (sql-array "text" nicks)])) (defn fetch-message-by-id [m-id] (let [query "SELECT m.message_id, m.content, m.created_on, m.user_id, m.is_image, u.nick, u.avatar, r.key, r.admin_only FROM messages m, users u, rooms r WHERE m.user_id = u.user_id AND r.room_id = m.room_id AND m.message_id = ?"] (first (do-select [query (maybe-parse-int m-id -1)])))) ;;;; Popular Posts (def popular-dumps-qry " select u.nick, u.avatar, r.key, m.message_id, m.content, m.created_on, count(*) as count, array_agg(u2.nick) as user_nicks from users u, messages m, rooms r, tags t, users u2 where lower(u.nick) = lower(?) and u.user_id = m.user_id and m.message_id = t.message_id and m.room_id = r.room_id and m.is_image = true and r.admin_only = false and t.tag = 'favorite' and t.user_id = u2.user_id group by u.nick, u.avatar, r.key, m.message_id, m.content, m.created_on order by count desc limit ? offset ?") (defn fetch-popular-dumps [nick viewer-nick] (for [d (do-select [popular-dumps-qry nick 40 0])] (let [favers (.getArray (:user_nicks d))] (assoc d :favers favers :favorited (some #(= % viewer-nick) favers))))) (defn fetch-popular-dumps-redis [nick viewer-nick] (let [rkey (str "popular:" nick) msg-ids (redis/with-server redis-server (redis/zrevrange rkey 0 (dec num-popular-dumps))) msg-ids (map maybe-parse-int msg-ids)] (if-not (empty? msg-ids) (tags/fetch-dumps-by-ids msg-ids viewer-nick)))) ;;;; Redis Favscores (defn fetch-redis-directory [page num] (vec (for [t (with-jedis #(.zrevrangeWithScores % "favscores" (* page num) (dec (* (inc page) num))))] {:nick (.getElement t) :score (int (.getScore t))}))) (defn fetch-redis-favscore [nick] (if (= (lower-case nick) "scottbot") -1 (maybe-parse-int (redis/with-server redis-server (redis/zscore "favscores" (lower-case nick))) 0))) (defn incrby-redis-favscore! [nick msg-id inc is-image] (let [msg-id (str msg-id) inc (double inc)] (with-jedis #(do (.zincrby % "favscores" inc (lower-case nick)) (when is-image (.zincrby % (str "popular:" nick) inc msg-id) (.zincrby % "hall" inc msg-id)))))) ;;;; Redis Hall of Fame (defn fetch-redis-hall [viewer-nick] (let [ids (map maybe-parse-int (redis/with-server redis-server (redis/zrevrange "hall" 0 (dec num-hall-dumps))))] (if-not (empty? ids) (tags/fetch-dumps-by-ids ids viewer-nick)))) ;;;; Message insertion (def msg-insert-query "INSERT INTO messages (user_id, room_id, content, is_image, is_text) VALUES (?, ?, ?, ?, ?) RETURNING message_id, created_on") (defn insert-message-into-postgres! [author-id room-id content is-image is-text recips] (with-connection *db* (transaction (let [{msg-id :message_id ts :created_on} (first (do-select [msg-insert-query author-id room-id content is-image is-text]))] (doseq [r recips] (insert-values :direct_messages [:message_id :author_id :recip_id] [msg-id author-id (:user_id r)])) [msg-id ts])))) (defn insert-recips-into-redis! [recips author-id ts content] (let [dm-json (json-str {"author_id" author-id "recips" (map :nick recips) "content" content})] (redis/with-server redis-server (redis/atomically (doseq [r recips] (redis/zadd (str "directmessage:" (:user_id r)) (.getTime ts) dm-json)))))) (defn insert-message! [author-id author-nick room-id content] (let [msg-type (classify-msg content) is-image (boolean (#{:image :mixed} msg-type)) is-text (boolean (#{:mixed :text} msg-type)) recips (get-recips content) [msg-id ts] (insert-message-into-postgres! author-id room-id content is-image is-text recips)] (if-not (empty? recips) (insert-recips-into-redis! recips author-id ts content)) {:author author-nick :msg-id msg-id :room room-id :db-ts ts :content content :recips (map (comp lower-case :nick) recips)})) (defn fetch-private-messages [user-id] (for [dm (redis/with-server redis-server (redis/zrevrange (str "directmessage:" user-id) 0 40))] (let [dm (read-json dm) info (fetch-user-id (get dm "author_id"))] {"nick" (:nick info) "content" (get dm "content") "recips" (get dm "recips" []) "avatar" (:avatar info)})))