Changed data access queries for rooms, user logs, and favorites

8 files changed, 326 insertions, 157 deletions

diff --git a/db/0-create.psql b/db/0-create.psql
index 8676b79..3479159 100644
--- a/db/0-create.psql
+++ b/db/0-create.psql
@@ -16,6 +16,9 @@ CREATE TABLE users (
     is_bot boolean NOT NULL DEFAULT false,
     profile_bg text
 );
+
+ALTER TABLE users ADD CONSTRAINT users_nick_constraint UNIQUE (LOWER(nick));
+
 CREATE INDEX users_nick_lowercase_idx ON users (lower(nick));
 
 --    ALTER TABLE users
@@ -25,8 +28,6 @@ CREATE INDEX users_nick_lowercase_idx ON users (lower(nick));
 --    ALTER TABLE users
 --    ADD COLUMN last_ip cidr DEFAULT '0';
 
-
-
 CREATE TABLE rooms (
     room_id SERIAL PRIMARY KEY,
     key text UNIQUE NOT NULL,
@@ -46,14 +47,13 @@ CREATE TABLE messages (
     is_image bool NOT NULL,
     is_text bool NOT NULL DEFAULT false
 );
-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);
 
--- Used to load recent dumps in a room
-CREATE INDEX messages_room_id_created_on_idx ON messages (room_id, created_on desc);
+-- historical image dumps per user
+CREATE INDEX messages_user_created_on_image_only_idx ON messages (user_id, created_on)
+    WHERE is_image;
+
+-- historical image dumps in a room. needs to handle non-image messages.
+CREATE INDEX messages_room_id_created_on_idx ON messages (room_id, created_on);
 
 -- Queries to support:
 -- 1) What messages are tagged x? (ordered by time, popularity)
@@ -69,10 +69,10 @@ CREATE TABLE tags (
   tag text NOT NULL,
   PRIMARY KEY (user_id, message_id, tag)
 );
-CREATE INDEX tags_user_id_idx ON tags (user_id);
+
 CREATE INDEX tags_message_id_idx ON tags (message_id);
-CREATE INDEX tags_created_on_id_idx ON tags (created_on DESC);
 CREATE INDEX tags_tag_lowercase_idx ON tags (lower(tag));
+CREATE INDEX tags_user_id_created_on_idx ON tags(user_id, created_on);
 
 CREATE TABLE mutes (
   mute_id SERIAL PRIMARY KEY,
diff --git a/src/site.clj b/src/site.clj
index dc57748..c2d86fa 100644
--- a/src/site.clj
+++ b/src/site.clj
@@ -10,7 +10,7 @@
         clojure.contrib.command-line
         clojure.contrib.duck-streams
         clojure.contrib.json.write
-	clojure.contrib.seq-utils
+        clojure.contrib.seq-utils
         clojure.contrib.sql
         clojure.contrib.str-utils
         clojure.contrib.def
@@ -19,7 +19,7 @@
         admin
         compojure
         email
-	fame
+        fame
         utils
         cookie-login
         session-sweeper
@@ -415,12 +415,17 @@ FROM   users u
 
 ;; Profile
 
-(defn take-random-image [content]
-  (let [imgs (filter is-image? (.split content " "))]
-    (if (empty? imgs) nil (rand-elt imgs))))
+(defn take-images [content]
+  (filter is-image? (.split content " ")))
 
-(defn msg-transformer [key f]
-  (fn [msg] (assoc msg key (f (msg key)))))
+(defn pull-random-dump-images [dumps num]
+  (take num
+        (shuffle
+         (set (apply concat
+                     (map
+                      (comp take-images :content)
+                      dumps))))))
+  
 
 (defn count-dumps-posted [nick]
   (:count
@@ -438,12 +443,6 @@ FROM   users u
                  and t.message_id = m.message_id and m.is_image = true"
 		(.toLowerCase nick)]))))
 
-(defn recently-faved [nick]
-  (do-select ["select distinct(u2.avatar, u2.nick) from users u, tags t, messages m, users u2
-               where lower(u.nick) = ? and u.user_id = t.user_id and t.message_id = m.message_id
-               and m.user_id = u2.user_id order by t.created_on, u2.avatar, u2.nick limit 4"
-	      (.toLowerCase nick)]))
-
 (defn profile
   ([session profile-nick] (profile session profile-nick "profile"))
   ([session profile-nick template]
@@ -453,18 +452,12 @@ FROM   users u
              nick          (session :nick)
              logger        (make-time-logger)
              is-home       (and nick (= nick profile-nick))
-             has-avatar    (non-empty-string? (user-info :avatar))
              score         (lookup-score profile-nick)
-	     raw-dumps     (logger tags/fetch-dumps-by-nick
+	     dumps         (logger tags/fetch-image-dumps
+                                   :user-tag-id (:user_id session)
 				   :nick profile-nick
-				   :image-only true
-				   :amount 5)
-	     dumps         (logger doall
-				   (map (comp process-message-for-output
-					      (msg-transformer :content take-random-image)
-					      tags/remove-tags-for-output
-					      tags/add-favorited-flag)
-					(take 5 raw-dumps) (repeat session)))]
+				   :limit 10)
+             imgs          (pull-random-dump-images dumps 5)]
          (do
            (.setAttribute st "is_home" is-home)
            (doseq [a [:nick :avatar :contact :bio]]
@@ -473,8 +466,8 @@ FROM   users u
 			      (if (non-empty-string? v) (escape-html v)))))
 	   (.setAttribute st "score" (comma-format score))
 	   (.setAttribute st "score_ent" (score-to-entity score))
-	   (if (not (empty? dumps))
-	     (.setAttribute st "dumps" dumps))
+           (if (not (empty? imgs))
+	     (.setAttribute st "imgs" imgs))
 	   (.setAttribute st "debug_log_items" (logger))
 	   (.toString st)))
        (resp-error "NO_USER"))))
@@ -500,6 +493,23 @@ FROM   users u
           :else (do (update-user-db user-id attr val)
                     (resp-success "OK")))))
 
+;; Generic Handler
+
+(defn generic-profile-handler [session nick date msg-id
+                               func redirecter unknown]
+  (if-let [user-info (fetch-nick nick)]
+    ;; If a valid msg-id is provided, the date is ignored. 
+    ;; This makes urls such as /user/bogus/5 valid.
+    (cond msg-id (if-let [msg-id (maybe-parse-int msg-id)]
+                   (func session user-info nil msg-id)
+                   (redirecter user-info))
+          ;; If an invalid date is provided, we redirect to the user's first favs page.
+          date   (if-let [date (parse-yyyy-mm-dd-date date)]
+                   (func session user-info date nil)
+                   (redirecter user-info))
+          :else (func session user-info nil nil))
+    (unknown)))
+
 ;; User log
 
 (defn build-mini-profile [user-info]
@@ -514,35 +524,54 @@ FROM   users u
       (.setAttribute "score" (comma-format score))
       (.setAttribute "score_ent" (score-to-entity score))
       (.toString))))
-  
-(defn user-log [session profile-nick offset]
-  (if-let [user-info (fetch-nick profile-nick)]
-    (let [st            (fetch-template "userlog" session)
-	  profile-nick  (:nick user-info) ; Update to get right casing
-	  nick          (session :nick)
-	  logger        (make-time-logger)
-	  has-avatar    (non-empty-string? (:avatar user-info))
-	  offset        (maybe-parse-int offset 0)
-	  dump-offset   (* offset *dumps-per-page*)
-	  raw-dumps     (logger tags/fetch-dumps-by-nick
-				:nick profile-nick
-				:amount (+ 1 *dumps-per-page*)
-				:offset dump-offset)
-	  dumps         (map tags/add-favorited-flag (take *dumps-per-page* raw-dumps) (repeat session))
-	  dumps         (map tags/remove-tags-for-output dumps)
-	  dumps         (logger doall (map process-message-for-output dumps))]
-      (.setAttribute st "nick" profile-nick)
-      (.setAttribute st "is_home" (= nick profile-nick))
-      (.setAttribute st "mini_profile" (build-mini-profile user-info))
-      (if (> (count dumps) 0)
-	  (.setAttribute st "dumps" dumps))
-        (if (> (count raw-dumps) *dumps-per-page*)
-          (.setAttribute st "next" (inc offset)))
-        (if (not= offset 0)
-          (.setAttribute st "prev" (max (dec offset) 0)))
-        (.setAttribute st "debug_log_items" (logger))
-        (.toString st))
-    (resp-error "NO_USER")))
+
+;; The next-page link is generated by retrieving one additional dump,
+;; and creating a link from its date and message id.
+(defn log-next-page-link [last-msg]
+  (format "/%s/%s/%s"
+          (:nick last-msg)
+          (format-yyyy-mm-dd (:created_on last-msg))
+          (:message_id last-msg)))
+
+(defn user-log [session user-info date msg-id]
+  (let [st            (fetch-template "userlog" session)
+        logger        (make-time-logger)
+        raw-dumps     (tags/fetch-image-dumps
+                       :nick (:nick user-info)
+                       :user-tag-id (:user_id session)
+                       :msg-id msg-id
+                       :date (if msg-id nil date)
+                       :limit (inc *dumps-per-page*))
+        back-dumps    (if (or date msg-id)
+                        (tags/fetch-image-dumps
+                         :nick (:nick user-info)
+                         :msg-id msg-id
+                         :date (if msg-id nil date)
+                         :limit (inc *dumps-per-page*)
+                         :direction :forward))
+        dumps         (map process-message-for-output (butlast 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))
+    (println back-dumps)
+    (if (> (count dumps) 0)
+      (.setAttribute st "dumps" dumps))
+    (.setAttribute st "prev"
+                   (if back-dumps
+                     (cond
+                      (> (count back-dumps) *dumps-per-page*) (log-next-page-link (last back-dumps))
+                      (> (count back-dumps) 1) (format "/%s/log" (:nick user-info))
+                      :else nil)))
+    (if (> (count raw-dumps) *dumps-per-page*)
+      (.setAttribute st "next" (log-next-page-link (last raw-dumps))))
+    (.setAttribute st "debug_log_items" (logger))
+    (.toString st)))
+
+(defn user-log-handler [session nick date msg-id]
+  (generic-profile-handler session nick date msg-id
+                           user-log
+                           (fn [u] (redirect-to (str "/" (:nick u))))
+                           #(resp-error "NO_USER")))
 
 ;; Who faved me
 
@@ -663,11 +692,12 @@ order by count desc limit ? offset ?")
     (let [now              (System/currentTimeMillis)
 	  nick             (session :nick)
 	  limit            (if (:admin_only room) *vip-dumps-per-page* *dumps-per-page*)
-	  message-list     (reverse (tags/fetch-dumps-by-room :room-id (room :room_id)
-							      :image-only false
-							      :amount limit))
-	  message-list     (map tags/add-favorited-flag message-list (repeat session))
-	  message-list     (to-array (map process-message-for-output message-list))]
+	  raw-msgs         (reverse (tags/fetch-image-dumps :room (:key room)
+                                                            :image-only false
+                                                            :user-tag-id (:user_id session)
+                                                            :hide-vip false
+                                                            :limit limit))
+	  message-list     (to-array (map process-message-for-output raw-msgs))]
       (if nick
 	(dosync
 	 (login-user (user-struct-from-session session) room)))
@@ -874,10 +904,10 @@ order by count desc limit ? offset ?")
 ;; message-user-id: get messages posted by this user
 ;; tag-user-id: get messages tagged by this user
 (defnk tagged-dumps-template [session params tags url page-title info-bar
-			                                       :message-user-id false
-                                                               :tag-user-id false
-                                                               :logger (make-time-logger)
-                                                               :include-vip false]
+                              :message-user-id false
+                              :tag-user-id false
+                              :logger (make-time-logger)
+                              :include-vip false]
   (let [st (fetch-template "tagged_dumps" session)
 	      offset (maybe-parse-int (params :offset) 0)
         dump-offset (* offset *dumps-per-page*)
@@ -891,16 +921,16 @@ order by count desc limit ? offset ?")
         dumps (map tags/add-favorited-flag (take *dumps-per-page* raw-dumps) (repeat session))
         dumps (map tags/remove-tags-for-output dumps)
         dumps (logger doall (map process-message-for-output dumps))]
-          (if (> (count raw-dumps) *dumps-per-page*)
-            (.setAttribute st "next" (inc offset)))
-          (if (not= offset 0)
-            (.setAttribute st "prev" (max (dec offset) 0)))
-          (.setAttribute st "dumps" dumps)
-	  (.setAttribute st "infobar" info-bar)
-          (.setAttribute st "page_title" page-title)
-          (.setAttribute st "page_url" url)
-          (.setAttribute st "debug_log_items" (logger))
-          (.toString st)))
+    (.setAttribute st "dumps" dumps)
+    (.setAttribute st "infobar" info-bar)
+    (.setAttribute st "page_title" page-title)
+    (.setAttribute st "page_url" url)
+    (if (not= offset 0)
+      (.setAttribute st "prev" (format "/%s/%s" url (max 0 (dec offset)))))
+    (if (> (count raw-dumps) *dumps-per-page*)
+      (.setAttribute st "next" (format "/%s/%s" url (inc offset))))
+    (.setAttribute st "debug_log_items" (logger))
+    (.toString st)))
 
 ;; gotta parse tag intersections myself because +'s get decoded into spaces
 ;; there's probably a less hacky way to do this
@@ -920,29 +950,55 @@ order by count desc limit ? offset ?")
         page-title (str "dumps tagged as '" (escape-html (str-join "' and '" tags)) "'")]
     (tagged-dumps-template session params tags url page-title "")))
 
-(defn favorites [session params]
-  (if-let [user-info (fetch-nick (params :nick))]
-    (let [nick (params :nick)
-	  user-info (fetch-nick nick)
-	  user-id (:user_id user-info)
-	  avatar (:avatar user-info)
-	  url (str nick "/favorites")
-	  page-title (str nick "'s favorites")
-	  infobar (build-mini-profile user-info)]
-      (tagged-dumps-template session params "favorite" url page-title infobar
-			     :tag-user-id user-id
-			     :include-vip (is-vip? session)))
-    "NO_USER"))
+(defn favorites-next-page-link [nick last-msg]
+  (format "/%s/favorites/%s/%s"
+          nick
+          (format-yyyy-mm-dd (:tagged_on last-msg))
+          (:message_id last-msg)))
+
+(defn favorites [session user-info date msg-id]
+  (let [st            (fetch-template "tagged_dumps" session)
+        logger        (make-time-logger)
+        raw-dumps     (tags/fetch-tagged-dumps
+                       :nick (:nick user-info)
+                       :user-tag-id (:user_id session)
+                       :msg-id msg-id
+                       :date (if msg-id nil date)
+                       :limit (inc *dumps-per-page*))
+        back-dumps    (if (or date msg-id)
+                        (tags/fetch-tagged-dumps
+                         :nick (:nick user-info)
+                         :msg-id msg-id
+                         :date (if msg-id nil date)
+                         :limit (inc *dumps-per-page*)
+                         :direction :forward))
+        dumps         (map process-message-for-output (butlast raw-dumps))]
+    (.setAttribute st "prev"
+                   (if back-dumps
+                     (cond
+                      (> (count back-dumps) *dumps-per-page*) (favorites-next-page-link (:nick user-info)
+                                                                                        (last back-dumps))
+                      (> (count back-dumps) 1) (format "/%s/favorites" (:nick user-info))
+                      :else nil)))
+    (if (> (count raw-dumps) *dumps-per-page*)
+      (.setAttribute st "next" (favorites-next-page-link (:nick user-info) (last raw-dumps))))
+    (.setAttribute st "dumps" dumps)
+    (.setAttribute st "infobar" (build-mini-profile user-info))
+    (.setAttribute st "page_title" (format "%s'S FAVS" (:nick user-info)))
+    (.setAttribute st "debug_log_items" (logger))
+    (.toString st)))
+
+(defn favorites-handler [session nick date msg-id]
+  (generic-profile-handler session nick date msg-id
+                           favorites
+                           (fn [u] (redirect-to (format "/%s/favorites" (:nick u))))
+                           #(resp-error "NO_USER")))                       
 
 (defn json-favorites [session params]
   (let [nick (params :nick)
         user-id (user-id-from-nick nick)
-        raw-favs (tags/fetch-dumps-by-tag :tags "favorite"
-                                          :image-only false
-                                          :amount 50
-                                          :offset 0
-                                          :tag-user-id user-id
-                                          :with-tags false)
+        raw-favs (tags/fetch-tagged-dumps :nick nick
+                                          :limit 50)
         favs (reduce (fn [m fav] (assoc m (str (fav :message_id)) (fav :content))) {} raw-favs)]
     (str "RawFavs=" (json-str favs))))
 
@@ -1154,16 +1210,16 @@ order by count desc limit ? offset ?")
   (GET "/r/:room/log/:offset"              (validated-log session (params :room) (params :offset) params))
 
   (GET "/favicon.ico" (serve-static "static" "favicon.ico"))
-  (GET "/u/:nick" (profile session (params :nick)))
-  (GET "/u/:nick/" (profile session (params :nick)))
+  (GET "/u/:nick"  (redirect-to (str "/" (params :nick))))
+  (GET "/u/:nick/" (redirect-to (str "/" (params :nick))))
   (GET "/u/:nick/tag/:tag" (tagged-dumps-by-nick session params (request-url request)))
   (GET "/u/:nick/tag/:tag/:offset" (tagged-dumps-by-nick session params (request-url request)))
-  (GET "/u/:nick/favorites" (favorites session params))
-  (GET "/u/:nick/favorites/:offset" (favorites session params))
+  (GET "/u/:nick/favorites" (redirect-to (format "/%s/favorites" (params :nick))))
+  (GET "/u/:nick/favorites/:offset" (redirect-to (format "/%s/favorites" (params :nick))))
   (GET "/json/:nick/favorites" (json-favorites session params))
   
   ; have to put this route after favs
-  (GET "/u/:nick/:offset" (user-log session (params :nick) (params :offset)))
+  (GET "/u/:nick/:offset" (redirect-to (str "/" (params :nick))))
   
   (GET "/p/:nick/:postid" (single-message session (params :nick) (params :postid)))
   ;; TODO: these shouldn't be GETs
@@ -1223,14 +1279,21 @@ order by count desc limit ? offset ?")
   (GET "/:nick/"          (profile session (params :nick)))
   (GET "/:nick/tag/:tag" (tagged-dumps-by-nick session params (request-url request)))
   (GET "/:nick/tag/:tag/:offset" (tagged-dumps-by-nick session params (request-url request)))
-  (GET "/:nick/favorites" (favorites session params))
-  (GET "/:nick/favorites/:offset" (favorites session params))
+  (GET "/:nick/favorites" (favorites-handler session (params :nick) nil nil))
+  (GET "/:nick/favorites/" (favorites-handler session (params :nick) nil nil))
+  (GET "/:nick/favorites/:date" (favorites-handler session (params :nick) (params :date) nil))
+  (GET "/:nick/favorites/:date/" (favorites-handler session (params :nick) (params :date) nil))
+  (GET "/:nick/favorites/:date/:msg" (favorites-handler session (params :nick) (params :date) (params :msg)))
+  (GET "/:nick/favs" (favorites-handler session (params :nick) nil nil))
+  (GET "/:nick/favs/:date" (favorites-handler session (params :nick) (params :date) nil))
+  (GET "/:nick/favs/:date/:msg" (favorites-handler session (params :nick) (params :date) (params :msg)))
   (GET "/:nick/popular" (popular session (params :nick)))
-  (GET "/:nick/favs" (favorites session params))
-  (GET "/:nick/favs/:offset" (favorites session params))
-  (GET "/:nick/log" (user-log session (params :nick) "0"))
-  (GET "/:nick/log/:offset" (user-log session (params :nick) (params :offset)))
-  (GET "/:nick/:offset"   (user-log session (params :nick) (params :offset)))
+  (GET "/:nick/log"        (user-log-handler session (params :nick) nil nil))
+  (GET "/:nick/log/"        (user-log-handler session (params :nick) nil nil))
+  (GET "/:nick/:date"      (user-log-handler session (params :nick) (params :date) nil))
+  (GET "/:nick/:date/"      (user-log-handler session (params :nick) (params :date) nil))
+  (GET "/:nick/:date/:msg" (user-log-handler session (params :nick) (params :date) (params :msg)))
+
   (GET "/" (landing session))
   (ANY "*" (unknown-page)))
 
diff --git a/src/tags.clj b/src/tags.clj
index b95f824..3077b2a 100644
--- a/src/tags.clj
+++ b/src/tags.clj
@@ -3,6 +3,7 @@
            java.util.Date)
   (:use clojure.contrib.sql
         clojure.contrib.def
+        clojure.contrib.fcase
         clojure.contrib.json.write
         clojure.contrib.str-utils
         compojure
@@ -152,6 +153,75 @@ WHERE EXISTS
 " ORDER BY created_on DESC
   LIMIT ? OFFSET ?"))
 
+(defn lookup-direction [d]
+  (case d
+        :backward ["DESC" "<="]
+        :forward  ["ASC" ">="]
+        (except! "Unknown direction: " d)))
+
+(defnk fetch-image-dumps [:nick nil :room nil
+                          :date nil :msg-id nil :direction :backward
+                          :image-only true
+                          :user-tag-id nil :hide-vip true :limit 21]
+  (cond
+   (and nick room)             (except! "Cannot provide both nick and room for fetch-image-dumps")
+   (not (or nick room))        (except! "Must provide nick or room for fetch-image-dumps")
+   (and date msg-id)           (except! "Cannot provide both date and msg-id for fetch-image-dumps")
+   (and nick (not image-only)) (except! "messages_user_created_on_image_only_idx only covers image messages")
+   :else (let [[direction comp-str] (lookup-direction direction)]
+           (do-select
+            (build-query
+             :select ["m.content, m.message_id, m.created_on"
+                      "u.nick, u.avatar, r.key"
+                      (if user-tag-id
+                        [(str "EXISTS(SELECT 1 FROM tags WHERE tag = 'favorite' "
+                              "AND message_id = m.message_id AND user_id = ?) AS favorited")
+                         user-tag-id])]
+             :from   ["messages m, rooms r, users u"]
+             :where  [(cond
+                       nick ["m.user_id = (SELECT user_id FROM users WHERE LOWER(nick) = lower(?))" nick]
+                       room ["m.room_id = (SELECT room_id FROM rooms where LOWER(key) = lower(?))" room])
+                      "m.user_id = u.user_id"
+                      "m.room_id = r.room_id"
+                      (if image-only "m.is_image = true")
+                      (if hide-vip "r.admin_only = false")
+                      (cond date   [(format "m.created_on::date %s ?" comp-str) (to-sql-date date)]
+                            msg-id [(format "m.created_on %s
+                                             (SELECT created_on FROM messages WHERE message_id = ?)" comp-str)
+                                    msg-id])]
+             :order (format "m.created_on %s" direction)
+             :limit limit)))))
+
+(defnk fetch-tagged-dumps [:nick nil :date nil :msg-id nil :tag "favorite" :direction :backward
+                           :user-tag-id nil :hide-vip true :limit 21]
+  (cond
+   (not nick) (except! "Must provide nick for fetch-tagged-dumps")
+   :else (let [select-user-id "(SELECT user_id FROM users WHERE LOWER(nick) = lower(?))"
+               [dir comp-str] (lookup-direction direction)]
+           (do-select
+            (build-query
+             :select ["m.content, m.message_id, m.created_on"
+                      "u.nick, u.avatar, r.key"
+                      "t.created_on as tagged_on"
+                      (if user-tag-id
+                        [(str "EXISTS(SELECT 1 FROM tags WHERE tag = 'favorite' "
+                              "AND message_id = m.message_id AND user_id = ?) AS favorited")
+                         user-tag-id])]
+             :from   ["tags t, messages m, rooms r, users u"]
+             :where [[(str "t.user_id = " select-user-id) nick]
+                     "t.message_id = m.message_id"
+                     "m.user_id = u.user_id"
+                     "m.room_id = r.room_id"
+                     (if hide-vip "r.admin_only = false")
+                     (cond date   [(format "t.created_on::date %s ?" comp-str)
+                                   (to-sql-date date)]
+                           msg-id [(format (str "t.created_on %s (SELECT created_on FROM tags "
+                                                "WHERE message_id = ? " "AND tag = ? AND user_id = %s)")
+                                           comp-str select-user-id)
+                                   msg-id tag nick])]
+             :order (format "t.created_on %s" dir)
+             :limit limit)))))
+
 ;; SPEED HACK
 ;; explicit use of room ids, but fast
 ;; (not used yet)
diff --git a/src/utils.clj b/src/utils.clj
index 44131df..6d0a4ed 100755
--- a/src/utils.clj
+++ b/src/utils.clj
@@ -11,6 +11,7 @@
            org.antlr.stringtemplate.StringTemplateGroup)
   (:use clojure.contrib.json.write
         clojure.contrib.sql
+        clojure.contrib.def
         clojure.contrib.duck-streams
         clojure.contrib.str-utils
         compojure
@@ -34,6 +35,9 @@
 
 ;; Misc
 
+(defn except! [& more]
+  (throw (Exception. (apply str more))))
+
 (defn download-http-url [u]
   (let [url (URL. u)]
     (if (= (.getProtocol url) "http")
@@ -45,8 +49,11 @@
         ip (if ip ip (:remote-addr request))] (str ip))  ; deployed locally
 )
 
-(defn append [& vecs]
-  (reduce into vecs))
+(defn append [& seqs]
+  (reduce into (map vector seqs)))
+
+(defn transpose [lsts]
+  (apply (partial map vector) lsts))
 
 (declare stringify-and-escape)
 (defn escape-html-deep [o]
@@ -114,24 +121,21 @@
 
 ;; Formatters
 
-(defn- comma-helper [s]
-  (let [f (take 3 s)
-	r (drop 3 s)]
-    (if (= (count r) 0)
-      f
-      (append (comma-helper r) [\,] (reverse f)))))
 
 (defn comma-format [i]
-  (apply str
-	 (reverse (comma-helper (reverse (str i))))))
-
+  (.format (java.text.DecimalFormat. "#,###") i))
+  
+(def yyyy-formatter (new SimpleDateFormat "yyyy"))
+(def yyyy-mm-formatter (new SimpleDateFormat "yyyy-MM"))
 (def yyyy-mm-dd-formatter (new SimpleDateFormat "yyyy-MM-dd"))
+(def yymmdd-formatter (new SimpleDateFormat "yyyyMMdd"))
+
+(doseq [f [yyyy-formatter yyyy-mm-formatter yyyy-mm-dd-formatter yymmdd-formatter]]
+  (.setLenient f false))
 
 (defn format-yyyy-mm-dd [d]
   (.format yyyy-mm-dd-formatter d))
 
-(def yymmdd-formatter (new SimpleDateFormat "yyyyMMdd"))
-
 (defn format-yyyymmdd [d]
   (.format yymmdd-formatter d))
 
@@ -159,7 +163,7 @@
         labels  ["year" "month" "day" "hour" "minute"]
         arr     (into [] (for [[l v] (map vector labels vals) :when (> v 0)]
                            (str v " " (pluralize l v))))]
-    (join arr ", ")))
+    (str-join ", " arr)))
 
 (defn apply-formats [formats d]
   (into {} (for [[k v] d]
@@ -188,6 +192,44 @@
 
 ;; Database
 
+(defn to-sql-date [dt]
+  (java.sql.Date. (.getTime dt)))
+
+(defn join-clauses [clauses]
+  (let [clause-func (fn [c]
+                      (cond (string? c) [c []]
+                            (vector? c) [(first c) (rest c)]
+                            :else (except! "Invalid query-clause: " c)))
+        pairs          (for [c clauses :when c]
+                         (clause-func c))
+        [clauses vars] (transpose pairs)]
+    [clauses
+     (apply concat vars)]))
+
+(defnk build-query [:select nil :from nil :where nil
+                    :ljoin nil
+                    :order nil :limit nil :indent "    "]
+  (cond
+   (not select) (except! "Invalid query missing SELECT")
+   (not from)   (except! "Invalid query missing FROM")
+   (not where)  (except! "Invalid query missing WHERE")
+   :else (let [[sel-cls sel-var]     (join-clauses select)
+               [from-cls from-var]   (join-clauses from)
+               [where-cls where-var] (join-clauses where)]
+           (vec
+            (concat
+             [(str "SELECT\n" indent
+                   (str-join (str ",\n" indent) sel-cls)
+                   "\nFROM\n" indent
+                   (str-join (str ",\n" indent) from-cls)
+                   (if ljoin
+                     (str "\nLEFT JOIN " ljoin) "")
+                   "\nWHERE\n" indent
+                   (str-join (str " AND\n" indent) where-cls)
+                   (if order (str "\nORDER BY " order) "")
+                   (if limit (str "\nLIMIT " limit) ""))]
+             sel-var from-var where-var)))))
+  
 (defn do-cmds [query]
   (with-connection *db*
     (do-commands query)))
@@ -232,7 +274,7 @@
     (let [stmt (.prepareStatement (connection) query)]
       (doseq [[i o] (map vector (iterate inc 1) objects)]
         (.setObject stmt i o))
-      (println "update: " (.executeQuery stmt)))))
+      (.executeQuery stmt))))
               
 
 ;; Parsing
@@ -246,6 +288,19 @@
 (defn maybe-parse-long [s f]
   (if s (Long/parseLong s) f))
 
+(defn parse-yyyy-mm-dd-date [s]
+  (try (.parse yyyy-mm-dd-formatter s)
+       (catch java.text.ParseException _ nil)))
+  
+(defn parse-flexi-date
+  "Accepts date strings as YYYY, YYYY-MM, or YYYY-MM-DD."
+  [s]
+  (let [parse-f (fn [f l] (try [(.parse f s) l]
+                               (catch java.text.ParseException _ nil)))]
+    (or (parse-f yyyy-mm-dd-formatter :day)
+        (parse-f yyyy-mm-formatter :month)
+        (parse-f yyyy-formatter :year))))
+
 (defn url-decode [text]
   (URLDecoder/decode text "UTF-8"))
 
diff --git a/template/dash_dump.st b/template/dash_dump.st
deleted file mode 100644
index 5722eef..0000000
--- a/template/dash_dump.st
+++ /dev/null
@@ -1,9 +0,0 @@
-$if(dump.ztags)$
-  <div class="dash-dump dump" id="message-$dump.message_id$" nick="$dump.nick$" tags="$dump.tags: { tag | $tag.nick$:$tag.tag$ }$">
-$else$
-  <div class="dash-dump dump $if(dump.favorited)$favorite$endif$" id="message-$dump.message_id$" nick="$dump.nick$">
-$endif$
-
-<div class="content">$dump.content$</div>
-
-</div>
\ No newline at end of file
diff --git a/template/profile.st b/template/profile.st
index 999f93c..4aa1f63 100644
--- a/template/profile.st
+++ b/template/profile.st
@@ -80,7 +80,7 @@
     <div id="dashpix">
 	<a href="http://dump.fm/$nick$/log"><div id="favstxt">$if(is_home)$ your $else$ $nick$'s$endif$ most recent dumps</div></a>
 	<div id="mostrecentdumps">
-	$dumps: { d | $dash_dump(dump=d)$ }$
+	$imgs: { img | <img src="$img$"></img> }$
 	</div>
               <div id="likebutton">
               <iframe src="http://www.facebook.com/widgets/like.php?href=http://dump.fm/$nick$"
diff --git a/template/tagged_dumps.st b/template/tagged_dumps.st
index fb86536..44c4b5e 100644
--- a/template/tagged_dumps.st
+++ b/template/tagged_dumps.st
@@ -16,13 +16,8 @@
 	<div id="messageList">
 	  
 	  $if(dumps)$ <span class="content">
-	    $dumps: { d |  $log_dump(dump=d)$ }$
+	    $dumps: { d | $log_dump(dump=d)$ }$
           </span>
-	  $if(json_tags)$
-	  <script>
-	    $json_tags: { j | $j$; }$
-	  </script>
-	  $endif$
 	  $else$
           <span>favless</span>
 	  $endif$
@@ -32,15 +27,15 @@
 	  <div id="msginputrapper"> 
             
             $if(prev)$
-            <a href="/$page_url$/$prev$"><input id="prevbutton" value="<- Prev"></a>
+            <a href="$prev$"><input id="prevbutton" value="<- Prev" readonly="true"></a>
 	    $else$
-	    <input id="prevbutton">
+	    <input id="prevbutton" readonly="true">
 	    $endif$
 
             $if(next)$
-            <a href="/$page_url$/$next$">  <input id="nextbutton" value="Next ->"></a>
+            <a href="$next$"><input id="nextbutton" value="Next ->" readonly="true"></a>
 	    $else$
-	    <input id="nextbutton" value="nomodumps">
+	    <input id="nextbutton" value="nomodumps" readonly="true">
 	    $endif$
           </div>
 	  <div id="footerc">
diff --git a/template/userlog.st b/template/userlog.st
index 0019499..c073ce3 100644
--- a/template/userlog.st
+++ b/template/userlog.st
@@ -2,18 +2,13 @@
   <head>
     <title>$nick$'s dump.fm</title>
     $head()$
-    
     <script>
       jQuery(document).ready(initLog);
     </script> 
-
-
   </head>
   <body>
     $banner()$
     <div id="dcontent">
-
-      
       <div id="messagePanep">
 	$mini_profile$
         <div id="messageList">
@@ -28,13 +23,13 @@
         <div id="msgInputDiv">
 	  <div id="msginputrapper"> 
             $if(prev)$
-            <a href="/$nick$/$prev$"><input id="prevbutton" value="<- Prev" readonly="true"></a>
+            <a href="$prev$"><input id="prevbutton" value="<- Prev" readonly="true"></a>
 	    $else$
 	    <input id="prevbutton" readonly="true">
 	    $endif$
 
             $if(next)$
-            <a href="/$nick$/$next$">  <input id="nextbutton" value="Next ->" readonly="true"></a>
+            <a href="$next$">  <input id="nextbutton" value="Next ->" readonly="true"></a>
 	    $else$
 	    <input id="nextbutton" value="nomodumps" readonly="true">
 	    $endif$