Fixed user-logs offbyone bug, replace user-info map with dynamic query

1 files changed, 31 insertions, 45 deletions

diff --git a/src/site.clj b/src/site.clj
index 8823798..5253f88 100644
--- a/src/site.clj
+++ b/src/site.clj
@@ -324,7 +324,8 @@
 ;; Fav scores
 
 (def *score-query* "
-SELECT u.nick,
+SELECT u.user_id,
+       u.nick,
        COUNT(*) AS cnt
 FROM   tags t,
        messages m,
@@ -332,49 +333,22 @@ FROM   tags t,
 WHERE  t.message_id = m.message_id
        AND m.user_id != t.user_id
        AND m.user_id = u.user_id
-GROUP  BY u.nick
+GROUP  BY u.user_id, u.nick
 ORDER BY cnt DESC
 ")
 
-(def *user-info-query* "
-SELECT u.nick,
-       u.user_id,
-       u.avatar,
-       LAST.content,
-       LAST.message_id       
-FROM   users u
-       LEFT JOIN messages LAST
-         ON u.user_id = LAST.user_id
-            AND LAST.created_on = (SELECT MAX(created_on)
-                                   FROM   messages
-                                   WHERE  user_id = u.user_id
-                                          AND room_id != 2
-                                          AND is_image)
-")
-
-
-(defn build-user-info-map []
-  (let [res (do-select [*user-info-query*])]
-    (zipmap (map :nick res) res)))
-
 (defn build-score-list []
   (let [res (vec (do-select [*score-query*]))]
     {:list res
      :map  (zipmap (map :nick res) (map :cnt res))}))
 
 (def *scores-refresh-period-sec* (* 29 60))
-(def *user-info-refresh-period-sec* (* 31 60))
 
 (def *user-scores*
      (scheduled-agent build-score-list
                       *scores-refresh-period-sec*
                       []))
 
-(def *user-info*
-     (scheduled-agent build-user-info-map
-                      *user-info-refresh-period-sec*
-                      {}))
-
 (def *piece-map*
      (zipmap
       [:pawn :knight :bishop :rook :queen :king :skull]
@@ -397,14 +371,6 @@ FROM   users u
     (let [scores (:map (poll *user-scores*))]
       (get scores nick 0))))
 
-(defn lookup-user-info [nick]
-  (if-let [i (get (poll *user-info*) nick)]
-    (if-let [score (lookup-score nick)]
-      (assoc i :score (lookup-score nick)
-               :score_ent (score-to-entity score))
-      i)
-    {}))
-
 (defn get-user-ranking [offset num]
   (if-let [ranking  (:list (poll *user-scores*))]
     (let [cnt       (count ranking)]
@@ -426,7 +392,6 @@ FROM   users u
                       (comp take-images :content)
                       dumps))))))
   
-
 (defn count-dumps-posted [nick]
   (:count
    (first 
@@ -549,7 +514,7 @@ FROM   users u
                          :date (if msg-id nil date)
                          :limit (inc *dumps-per-page*)
                          :direction :forward))
-        dumps         (map process-message-for-output (butlast raw-dumps))]
+        dumps         (map process-message-for-output (take *dumps-per-page* raw-dumps))]
     (.setAttribute st "nick" (:nick user-info))
     (.setAttribute st "is_home" (= (:nick user-info) (:nick session)))
     (.setAttribute st "mini_profile" (build-mini-profile user-info))
@@ -622,14 +587,35 @@ order by count desc limit ? offset ?")
 (def *per-directory-page* 25)
 
 (defn process-directory-entry [entry]
-  (let [info (lookup-user-info (:nick entry))]
-    (assoc (stringify-and-escape info)
-      "score_ent" (:score_ent info))))
+  (let [score (lookup-score (:nick entry))]
+    (assoc (stringify-and-escape entry)
+      "score_ent" (score-to-entity score)
+      "score"     score)))
+
+(def recent-posts-query  "
+SELECT u.user_id, u.nick, u.avatar,
+       m.content, m.message_id
+FROM   users u, messages m
+WHERE  u.user_id = ANY(?)
+  AND    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);
+")
+
+(defn add-recent-posts [users]
+  (if-not (empty? users)
+    (let [ids  (map :user_id users)
+          res  (do-select [recent-posts-query (sql-array "int" ids)])]
+      (for [u users]
+        (merge u (find-first #(= (:user_id u) (:user_id %)) res))))))
 
 (defn get-directory-info [offset]
   (map process-directory-entry
-       (get-user-ranking offset
-                         *per-directory-page*)))
+       (add-recent-posts
+        (get-user-ranking offset *per-directory-page*))))
     
 (defn directory [session offset]
   (let [st     (fetch-template "directory" session)
@@ -1366,7 +1352,7 @@ order by count desc limit ? offset ?")
 ; Delay the following to reduce start-load
 (Thread/sleep 15000)
 (start! *user-scores*)
-(start! *user-info*)
+
 (start-user-flusher!)
 (start-session-pruner!)
 (start! hall-results)