From 90537b1498485f7571cedd47ed5700cd49f82afe Mon Sep 17 00:00:00 2001 From: Scott Ostler Date: Thu, 11 Nov 2010 13:34:49 -0500 Subject: Removed unused db functions --- src/site.clj | 72 ------------------------------------------------------------ 1 file changed, 72 deletions(-) (limited to 'src/site.clj') diff --git a/src/site.clj b/src/site.clj index 3029f6a..6f97c81 100644 --- a/src/site.clj +++ b/src/site.clj @@ -110,34 +110,6 @@ (new-messages room ts)) "favs" (new-favs nick ts)}) -(defn count-messages-by-nick [nick image-only] - (let [query (str "SELECT COUNT(*) - FROM messages m, users u, rooms r - WHERE m.user_id = u.user_id AND u.nick = ? - AND r.room_id = m.room_id AND r.admin_only = false " - (if image-only "AND m.is_image = true " ""))] - (do-count [query nick]))) - -(defn fetch-messages-by-nick - ([nick image-only] (fetch-messages-by-nick nick image-only 0)) - ([nick image-only offset] - (let [query (str "SELECT m.content, m.created_on, m.message_id, u.nick, u.avatar, r.key - FROM messages m, users u, rooms r - WHERE m.user_id = u.user_id AND u.nick = ? - AND r.room_id = m.room_id AND r.admin_only = false " - (if image-only "AND m.is_image = true " "") - "ORDER BY created_on DESC - LIMIT ? OFFSET ?")] - (do-select [query nick *dumps-per-page* offset])))) - -(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)])))) (defn fetch-public-message-by-id [m-id] (let [msg (tags/fetch-dump-by-id m-id)] @@ -401,22 +373,6 @@ ORDER BY cnt DESC (map (comp take-images :content) dumps)))))) - -(defn count-dumps-posted [nick] - (:count - (first - (do-select ["select count(*) from messages m, users u - where m.user_id = u.user_id and lower(u.nick) = ? - and m.is_image = true" (.toLowerCase nick)])))) - -(defn count-dumps-user-faved [nick] - (:count - (first - (do-select ["select count(distinct(m.message_id)) from users u, tags t, messages m - where lower(u.nick) = ? and u.user_id = t.user_id - and t.tag = 'favorite' - and t.message_id = m.message_id and m.is_image = true" - (.toLowerCase nick)])))) (defn profile ([session profile-nick] (profile session profile-nick "profile")) @@ -602,34 +558,6 @@ order by count desc limit ? offset ?") "score_ent" (score-to-entity score) "score" score))) -(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)])) - (def directory-cache-ttl (minutes 10)) (def memoized-lookup-recent-posts-tagless (ttl-memoize lookup-recent-posts-tagless directory-cache-ttl)) -- cgit v1.2.3-70-g09d2 From 1179b2a2aefb6a4c15db9634cc0c9c910fea8efa Mon Sep 17 00:00:00 2001 From: Scott Ostler Date: Sun, 14 Nov 2010 13:16:12 -0800 Subject: Added redis back for popular posts, fixed CTRL-M chars in src/site.clj, tweaked single_message.st --- src/datalayer.clj | 76 ++++++++++++++++++ src/site.clj | 186 +++++++++++---------------------------------- src/tags.clj | 60 +++++++-------- template/head.st | 4 +- template/popular_dump.st | 2 +- template/single_message.st | 56 ++++++++------ 6 files changed, 186 insertions(+), 198 deletions(-) create mode 100644 src/datalayer.clj (limited to 'src/site.clj') 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 diff --git a/src/site.clj b/src/site.clj index 161f0d5..1abb876 100644 --- a/src/site.clj +++ b/src/site.clj @@ -18,6 +18,7 @@ config admin compojure + datalayer email fame utils @@ -110,38 +111,10 @@ (new-messages room ts)) "favs" (new-favs nick ts)}) -(defn count-messages-by-nick [nick image-only] - (let [query (str "SELECT COUNT(*) - FROM messages m, users u, rooms r - WHERE m.user_id = u.user_id AND u.nick = ? - AND r.room_id = m.room_id AND r.admin_only = false " - (if image-only "AND m.is_image = true " ""))] - (do-count [query nick]))) - -(defn fetch-messages-by-nick - ([nick image-only] (fetch-messages-by-nick nick image-only 0)) - ([nick image-only offset] - (let [query (str "SELECT m.content, m.created_on, m.message_id, u.nick, u.avatar, r.key - FROM messages m, users u, rooms r - WHERE m.user_id = u.user_id AND u.nick = ? - AND r.room_id = m.room_id AND r.admin_only = false " - (if image-only "AND m.is_image = true " "") - "ORDER BY created_on DESC - LIMIT ? OFFSET ?")] - (do-select [query nick *dumps-per-page* offset])))) - -(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)])))) - -(defn fetch-public-message-by-id [m-id] - (let [msg (tags/fetch-dump-by-id m-id)] - (if (and msg (not (:admin_only msg))) + +(defn fetch-public-message-by-id [m-id viewer-nick] + (if-let [msg (tags/fetch-dump-by-id m-id viewer-nick)] + (if-not (:admin_only msg) msg))) ;; User-id/nick cache @@ -246,9 +219,10 @@ (defn log-login [user_id ip] ;; i'm using do-cmds here because update-values can't deal with stuff like 'last_login = now()' - (let [query (format "UPDATE users SET last_ip = '%s'::cidr, last_login = now() WHERE user_id = %s" (str ip) (str user_id))] - (do-cmds query)) -) + (try + (let [query (format "UPDATE users SET last_ip = '%s'::cidr, last_login = now() WHERE user_id = %s" (str ip) (str user_id))] + (do-cmds query)) + (catch Exception e nil))) (defn login [session params cookies request] (let [nick (or (params :nick) "") @@ -264,9 +238,8 @@ (log-login (db-user :user_id) ip) [(session-assoc-from-db db-user) login-cookie - (resp-success "OK")] - ) - (resp-error "BAD_LOGIN")))) + (resp-success "OK")]) + (resp-error "BAD_LOGIN")))) (defn logout [session] [(session-dissoc :nick :user_id :is_admin :avatar) @@ -401,22 +374,6 @@ ORDER BY cnt DESC (map (comp take-images :content) dumps)))))) - -(defn count-dumps-posted [nick] - (:count - (first - (do-select ["select count(*) from messages m, users u - where m.user_id = u.user_id and lower(u.nick) = ? - and m.is_image = true" (.toLowerCase nick)])))) - -(defn count-dumps-user-faved [nick] - (:count - (first - (do-select ["select count(distinct(m.message_id)) from users u, tags t, messages m - where lower(u.nick) = ? and u.user_id = t.user_id - and t.tag = 'favorite' - and t.message_id = m.message_id and m.is_image = true" - (.toLowerCase nick)])))) (defn profile ([session profile-nick] (profile session profile-nick "profile")) @@ -549,42 +506,15 @@ ORDER BY cnt DESC ;; Who faved me -(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, - array_agg(u2.avatar) as user_avs, - array_agg(t.created_on) as favtime, - (select exists (select 1 from tags - where tag = 'favorite' and user_id = ? and message_id = m.message_id)) as favorited -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 != u.user_id -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 ?") - -(def num-popular-dumps 40) - -(defn get-popular-dumps [nick user-id] - (for [d (do-select [popular-dumps-qry user-id nick 40 0])] - (let [fav-nicks (.getArray (:user_nicks d))] - (assoc d - :favers (sort-by :t (comp #(* -1 %) compare) - (map (fn [n a t] (if (non-empty-string? a) - {:nick n :avatar a :t t} - {:nick n :t t})) - fav-nicks - (.getArray (:user_avs d)) - (.getArray (:favtime d)))) - :user_nicks nil :user_avs nil :favtime nil)))) +(def use-popular-redis false) (defn popular [session profile-nick] (if-let [user-info (fetch-nick profile-nick)] (let [st (fetch-template "popular" session) profile-nick (:nick user-info) - raw-dumps (get-popular-dumps profile-nick (or (:user_id session) -1)) + raw-dumps (if use-popular-redis + (fetch-popular-dumps-redis profile-nick (:nick session)) + (fetch-popular-dumps profile-nick (:nick session))) dumps (map process-message-for-output raw-dumps)] (.setAttribute st "nick" profile-nick) (.setAttribute st "mini_profile" (build-mini-profile user-info)) @@ -602,33 +532,6 @@ order by count desc limit ? offset ?") "score_ent" (score-to-entity score) "score" score))) -(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)])) (def directory-cache-ttl (minutes 10)) (def memoized-lookup-recent-posts-tagless @@ -662,19 +565,15 @@ WHERE u.user_id = ANY(?)" ;; Single posts (defn single-message [session nick-from-url id-from-url] - (if-let [user-info (fetch-nick nick-from-url)] - (if-let [message (fetch-public-message-by-id id-from-url)] - ; error if nick in url doesn't match the nick who posted the message from the id in url - ; this prevents people from scraping all the content by incrementing the id in the url - (if (= (user-info :user_id) (message :user_id)) - (let [st (fetch-template "single_message" session) - message (tags/add-favorited-flag message session) - message (tags/remove-tags-for-output message)] - (.setAttribute st "dump" (process-message-for-output message)) - (.toString st)) - (resp-error "NO_MESSAGE")) + (if-let [message (fetch-public-message-by-id id-from-url (:nick session))] + ; error if nick in url doesn't match the nick who posted the message from the id in url + ; this prevents people from scraping all the content by incrementing the id in the url + (if (= nick-from-url (:nick message)) + (let [st (fetch-template "single_message" session)] + (.setAttribute st "dump" (process-message-for-output message)) + (.toString st)) (resp-error "NO_MESSAGE")) - (resp-error "NO_USER"))) + (resp-error "NO_MESSAGE"))) ;; Chat @@ -844,18 +743,15 @@ WHERE u.user_id = ANY(?)" (.toString st))) (defn validated-log [session room-key offset params] - (if-vip - (let [room-key (if (= (lower-case room-key) "www") "dumpfm" room-key)] - (if (validate-room-access room-key session) - (log session (lookup-room room-key) offset params) - (resp-error "UNKNOWN_ROOM"))) - (redirect-to "http://dump.fm"))) + (let [room-key (if (= (lower-case room-key) "www") "dumpfm" room-key)] + (if (validate-room-access room-key session) + (log session (lookup-room room-key) offset params) + (resp-error "UNKNOWN_ROOM")))) ;; Hiscore test... redis test... (defn redis-ids-test [period] - (let [reddis-server {:host "127.0.0.1" :port 6379 :db 0} - ids (redis/with-server reddis-server + (let [ids (redis/with-server redis-server (redis/zrevrange (str "hiscore:" period) 0 -1)) ids (map maybe-parse-int ids)] ids)) @@ -1070,15 +966,14 @@ WHERE u.user_id = ANY(?)" ;; cons: has to use a + + @@ -10,8 +12,6 @@ $if(!user_nick)$ - - $endif$ $if(isadmin)$ diff --git a/template/popular_dump.st b/template/popular_dump.st index 4709725..88e61c4 100644 --- a/template/popular_dump.st +++ b/template/popular_dump.st @@ -9,7 +9,7 @@ $dump.created_on$ - in $dump.key$$dump.content$
-$dump.count$ $dump.favers: { f | $f.nick$   }$ +$dump.count$ $dump.favers: { nick | $nick$   }$
$share_buttons()$ diff --git a/template/single_message.st b/template/single_message.st index e017b6f..1428f2b 100644 --- a/template/single_message.st +++ b/template/single_message.st @@ -1,37 +1,47 @@ - dump.fm $head()$ - - $banner()$ -


-
-
-$if(dump.avatar)$ -
-
- -
- $dump.nick$ -
-
-$endif$ - -
$dump.content$
-
-$share_buttons()$ +



+
+ + - + + +
$dump.content$
+
+
+ $dump.count$ $dump.favers: { nick | $nick$   }$ +
+$share_buttons()$ +
+ + +
+ $footer()$ +
+
+ -- cgit v1.2.3-70-g09d2 From 789ed39751c17fa1aa73f3119a80403e34f0d984 Mon Sep 17 00:00:00 2001 From: Scott Ostler Date: Sun, 14 Nov 2010 13:19:49 -0800 Subject: Disable scores/hall updating --- src/fame.clj | 4 ++-- src/site.clj | 3 ++- 2 files changed, 4 insertions(+), 3 deletions(-) (limited to 'src/site.clj') diff --git a/src/fame.clj b/src/fame.clj index 0732992..1baf6e0 100644 --- a/src/fame.clj +++ b/src/fame.clj @@ -2,8 +2,8 @@ (:use scheduled-agent utils)) -; should probably cache this to disk somewhere -(def hall-of-fame-update-frequency (* 5 60 60)) +; sostler: don't refresh until redis cache can be added +(def hall-of-fame-update-frequency (* 5 60 60 999999)) (def hall-of-fame-query " select m.created_on, m.message_id, m.content, u.nick, u.avatar, r.key, count(*) diff --git a/src/site.clj b/src/site.clj index 1abb876..4c3560e 100644 --- a/src/site.clj +++ b/src/site.clj @@ -328,7 +328,8 @@ ORDER BY cnt DESC {:list res :map (zipmap (map :nick res) (map :cnt res))})) -(def *scores-refresh-period-sec* (* 29 60)) +;; sostler: stop score refresh until redis cache can be added +(def *scores-refresh-period-sec* (* 29 60 9999999)) (def *user-scores* (scheduled-agent build-score-list -- cgit v1.2.3-70-g09d2