Merge branch 'master' of /pichat/repo

1 files changed, 76 insertions, 0 deletions

diff --git a/src/datalayer.clj b/src/datalayer.clj
new file mode 100644
index 0000000..7086b12
--- /dev/null
+++ b/src/datalayer.clj
@@ -0,0 +1,76 @@
+(ns datalayer
+  (:require redis
+            tags)
+  (:use config
+        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 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-tag-id user-ids]
+  (do-select [(recent-posts-query nil)
+              (sql-array "int" user-ids)]))
+
+(defn fetch-message-by-id [m-id]
+  (let [query "SELECT m.message_id, m.content, m.created_on, m.user_id,
+                      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 (vec (.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)]
+    (tags/fetch-dumps-by-ids msg-ids viewer-nick)))
\ No newline at end of file