diff options
Diffstat (limited to 'src/datalayer.clj')
| -rw-r--r-- | src/datalayer.clj | 211 |
1 files changed, 211 insertions, 0 deletions
diff --git a/src/datalayer.clj b/src/datalayer.clj new file mode 100644 index 0000000..6b2a466 --- /dev/null +++ b/src/datalayer.clj @@ -0,0 +1,211 @@ +(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)}))) |