diff options
| -rw-r--r-- | db/0-create.psql | 1 | ||||
| -rw-r--r-- | src/site.clj | 165 | ||||
| -rwxr-xr-x | src/utils.clj | 2 | ||||
| -rw-r--r-- | template/directory.st | 8 |
4 files changed, 81 insertions, 95 deletions
diff --git a/db/0-create.psql b/db/0-create.psql index 3a8f1c4..d034bbe 100644 --- a/db/0-create.psql +++ b/db/0-create.psql @@ -37,6 +37,7 @@ CREATE INDEX messages_user_id_idx ON messages (user_id); CREATE INDEX messages_room_id_idx ON messages (room_id); CREATE INDEX messages_created_on_idx ON messages (created_on); CREATE INDEX messages_is_image_idx ON messages (is_image); +CREATE INDEX messages_user_created_on_idx ON messages (user_id, created_on desc); -- Queries to support: -- 1) What messages are tagged x? (ordered by time, popularity) diff --git a/src/site.clj b/src/site.clj index 4454da3..abf1446 100644 --- a/src/site.clj +++ b/src/site.clj @@ -146,7 +146,8 @@ ;; User-id/nick cache ;; I keep needing to grab user-id from a nick so I thought I'd cache them - +;; @timb: I just duplicated this in the user-info map :( +;; we should reconcile our user caches (def user-id-cache (ref {})) (def *user-id-cache-size* 500) @@ -312,19 +313,46 @@ 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 -ORDER BY cnt DESC; +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) ") -(def *scores-refresh-period-sec* (* 30 60)) -(defn compute-scores [] - (let [res (do-select [*score-query*])] - (zipmap (map :nick res) - (map :cnt res)))) +(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* (* 30 60)) +(def *user-info-refresh-period-sec* 300) (def *user-scores* - (scheduled-agent (no-args-adaptor compute-scores) + (scheduled-agent (no-args-adaptor build-score-list) *scores-refresh-period-sec* + [])) + +(def *user-info* + (scheduled-agent (no-args-adaptor build-user-info-map) + *user-info-refresh-period-sec* {})) (def *piece-map* @@ -343,39 +371,29 @@ ORDER BY cnt DESC; (def score-to-entity (comp *piece-map* score-to-piece)) -;; Profile +(defn lookup-score [nick] + (if (= (lower-case nick) "scottbot") + -1 + (let [scores (:map (poll *user-scores*))] + (get scores nick 0)))) -(def *zoeee-query* " -SELECT m.content, m.message_id, m.created_on, - u.nick, u.avatar, r.key, - array_to_string(ARRAY(SELECT nick || ' ' || tag - FROM tags, users - WHERE message_id = m.message_id AND tags.user_id = users.user_id), ' ') as tags -FROM messages m, users u,rooms r -WHERE u.nick = 'zoeee' -AND m.user_id = u.user_id -AND m.message_id <= 519399 -AND m.message_id >= 517713 -AND m.is_image = true -AND m.room_id = r.room_id -AND r.room_id = 1 -ORDER BY created_on DESC; -") +(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 zoeee-nudes [session] - (let [raw-dumps (map tags/parse-tags-from-row-as-tag-map - (do-select [*zoeee-query*])) - dumps (map tags/add-favorited-flag raw-dumps (repeat session)) - dumps (map tags/remove-tags-for-output dumps) - dumps (map process-message-for-output dumps) - st (fetch-template "profile" session) - user-info (fetch-nick "zoeee")] - (doseq [a [:nick :avatar :contact :bio]] - (let [v (user-info a)] - (.setAttribute st (name a) - (if (non-empty-string? v) (escape-html v))))) - (.setAttribute st "dumps" dumps) - (.toString st))) +(defn get-user-ranking [offset num] + (let [ranking (:list (poll *user-scores*)) + count (count ranking)] + (subvec ranking + (min count (* offset num)) + (min count (* (inc offset) num))))) + + +;; Profile (defn profile ([session profile-nick offset] (profile session profile-nick offset "profile")) @@ -388,9 +406,7 @@ ORDER BY created_on DESC; is-home (and nick (= nick profile-nick)) has-avatar (non-empty-string? (user-info :avatar)) offset (maybe-parse-int offset 0) - score (if (= (lower-case profile-nick) "scottbot") - -1 - (or ((poll *user-scores*) profile-nick) 0)) + score (lookup-score profile-nick) dump-offset (* offset *dumps-per-page*) raw-dumps (logger tags/fetch-dumps-by-nick :nick profile-nick @@ -441,51 +457,20 @@ ORDER BY created_on DESC; ;; Directory (def *per-directory-page* 25) -(def *directory-refresh-period-sec* (* 60 60)) - -(def *directory-update-query* " -SELECT m.user_id, - u.nick, - u.avatar, - message_id, - m.created_on, - msg_count, - content -FROM (SELECT user_id, - created_on, - message_id, - content, - MAX(created_on) OVER (PARTITION BY user_id) max_created_on, - COUNT(*) OVER (PARTITION BY user_id) msg_count - FROM messages - WHERE room_id = 1 - AND is_image = true) AS m, - users u -WHERE m.created_on = max_created_on - AND m.user_id = u.user_id -ORDER BY msg_count DESC") - -(defn fetch-directory [] - (vec (do-select [*directory-update-query*]))) - -(def *directory-list* - (scheduled-agent fetch-directory - *directory-refresh-period-sec* - [])) -(defn directory-search [offset] - (let [directory (poll *directory-list*) - users (subvec directory - (min (count directory) - (* offset *per-directory-page*)) - (min (count directory) - (* (inc offset) *per-directory-page*)))] - (when (> (count users) 0) - (map process-directory-listing users)))) +(defn process-directory-entry [entry] + (let [info (lookup-user-info (:nick entry))] + (assoc (stringify-and-escape info) + "score_ent" (:score_ent info)))) +(defn get-directory-info [offset] + (map process-directory-entry + (get-user-ranking offset + *per-directory-page*))) + (defn directory [session offset] - (let [st (fetch-template "directory" session) - users (directory-search offset)] + (let [st (fetch-template "directory" session) + users (get-directory-info offset)] (.setAttribute st "users" users) (cond (= offset 0) (.setAttribute st "prev" false) (= offset 1) (.setAttribute st "prev" "") @@ -493,8 +478,6 @@ ORDER BY msg_count DESC") (if (> offset 0) (.setAttribute st "cur" offset)) (.setAttribute st "next" (str "/" (inc offset))) - (if (zero? (count (poll *directory-list*))) - (.setAttribute st "notloaded" true)) (.toString st))) ;; Single posts @@ -981,12 +964,9 @@ ORDER BY msg_count DESC") ;; Compojure Routes (defn serve-static [dir path] - ; TODO: cache other static files (js, css, etc.) - (let [cache-header (if (re-find pic-regex path) - {:headers {"Cache-Control" "max-age=604800,public"}} - {})] - [cache-header - (serve-file dir path)])) + (if (= path "") + (redirect-to "http://dump.fm") + (serve-file dir path))) (defroutes static (GET "/static/*" (serve-static "static" (params :*))) @@ -1064,7 +1044,6 @@ ORDER BY msg_count DESC") (GET "/error/ie" (serve-template "error_ie" session)) ;; Put username routes below all others in priority - (GET "/zoeee/nudes" (zoeee-nudes session)) (GET "/:nick" (profile session (params :nick) "0")) (GET "/:nick/" (profile session (params :nick) "0")) (GET "/:nick/:offset" (profile session (params :nick) (params :offset))) diff --git a/src/utils.clj b/src/utils.clj index d278e56..52f8399 100755 --- a/src/utils.clj +++ b/src/utils.clj @@ -186,7 +186,7 @@ (defn do-count [query] ((first (with-connection *db* (with-query-results rs query - (doall rs)))) + (vec rs)))) :count)) (defn do-delete [table query] diff --git a/template/directory.st b/template/directory.st index d49dfea..d9129ff 100644 --- a/template/directory.st +++ b/template/directory.st @@ -19,7 +19,7 @@ <div id="log"> <br> <div id="dprofile"> - <h2> DUMP STARS </h2><br><h3>Ordered by post count</h3> + <h2> DUMP STARS </h2><br><h3>Ordered by fav count</h3> </div> @@ -30,6 +30,12 @@ $users:{ dump | <div class="dlogged-dump" id="message-$dump.message_id$"> <a href="/$dump.nick$"> + $if(dump.score_ent)$ + <h8> + fav score: $dump.score$ + <h9>$dump.score_ent$</h9> + </h8> + $endif$ <div id="usernicks"> <b>$dump.nick$</b></div> $if(dump.avatar)$ <div id="dlogavatar"> |