add last_login time and last_ip to users table

4 files changed, 51 insertions, 13 deletions

diff --git a/db/0-create.psql b/db/0-create.psql
index 4725c08..5016aa2 100644
--- a/db/0-create.psql
+++ b/db/0-create.psql
@@ -6,6 +6,9 @@ CREATE TABLE users (
     hash text NOT NULL,
     email text NOT NULL,
     created_on timestamp NOT NULL DEFAULT now(),
+    last_login timestamp,
+    created_ip cidr DEFAULT '0',
+    last_ip cidr DEFAULT '0',
     is_admin boolean NOT NULL DEFAULT false,
     avatar text NOT NULL DEFAULT '',
     contact text NOT NULL DEFAULT '',
@@ -15,6 +18,15 @@ CREATE TABLE users (
 );
 CREATE INDEX users_nick_lowercase_idx ON users (lower(nick));
 
+--    ALTER TABLE users
+--    ADD COLUMN last_login timestamp;
+--    ALTER TABLE users
+--    ADD COLUMN created_ip cidr DEFAULT '0';
+--    ALTER TABLE users
+--    ADD COLUMN last_ip cidr DEFAULT '0';
+
+
+
 CREATE TABLE rooms (
     room_id SERIAL PRIMARY KEY,
     key text UNIQUE NOT NULL,
@@ -105,3 +117,21 @@ CREATE TABLE invalid_feed_images (
 );
 
 CREATE INDEX invalid_feed_images_idx ON invalid_feed_images (image_url);
+
+CREATE TABLE avatars (
+  avatar_id SERIAL PRIMARY KEY,
+  user_id NOT NULL REFERENCES users,
+  created_on timestamp NOT NULL DEFAULT now(),
+  type text,
+  url text,
+  w integer,
+  h integer,
+  thumb text,
+  tw integer,
+  th integer
+);
+
+CREATE TABLE images (
+  image_id SERIAL PRIMARY KEY,
+  
+);
\ No newline at end of file
diff --git a/src/site.clj b/src/site.clj
index d2002b1..6af0ebd 100644
--- a/src/site.clj
+++ b/src/site.clj
@@ -252,18 +252,28 @@
     (redirect-to "/chat")
     (serve-file "static" "index.html")))
 
-(defn login [session params cookies]
+(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))
+)
+
+(defn login [session params cookies request]
   (let [nick         (or (params :nick) "")
         hash         (or (params :hash) "")
         remember-me  (= (params :rememberme) "yes")
         db-user      (authorize-nick-hash nick hash)
         login-cookie (if remember-me
                        (make-login-token db-user)
-                       (clear-login-token *login-token-key*))]
+                       (clear-login-token *login-token-key*))
+        ip           (get-ip request)]
     (if db-user
-      [(session-assoc-from-db db-user)
-       login-cookie
-       (resp-success "OK")]
+      (do
+        (log-login (db-user :user_id) ip)
+        [(session-assoc-from-db db-user)
+        login-cookie
+        (resp-success "OK")]
+      )
       (resp-error "BAD_LOGIN"))))
 
 (defn logout [session]
@@ -1102,11 +1112,6 @@ order by count desc limit ? offset ?")
     (.setAttribute st "dumps" (map process-message-for-output msgs))
     (.toString st)))
 
-(defn show-ip [request]
-  (let [ip (get (:headers request) "x-real-ip")
-        ip (if ip ip (:remote-addr request))] (str ip))
-)
-
 ;; Compojure Routes
 
 (defn serve-static [dir path]
@@ -1125,7 +1130,6 @@ order by count desc limit ? offset ?")
   (GET "/:room/chat"              (validated-chat session (params :room) "chat"))
   (GET "/chat"                    (validated-chat session *default-room* "chat"))
   (GET "/chat/:t"                 (validated-chat session *default-room* (params :t)))
-  (GET "/ip"                      (show-ip request))
 
   (GET "http://:sub.dump.fm/log"         (validated-log session (params :sub) "0" params))
   (GET "http://:sub.dump.fm/log/:offset" (validated-log session (params :sub) (params :offset) params))
@@ -1148,7 +1152,7 @@ order by count desc limit ? offset ?")
   
   (GET "/p/:nick/:postid" (single-message session (params :nick) (params :postid)))
   ;; TODO: these shouldn't be GETs
-  (GET "/login" (login session params cookies))
+  (GET "/login" (login session params cookies request))
   (GET "/logout" (logout session))
   (GET "/register" (serve-static "static" "register.html"))
   (GET "/browser" (browser session))
diff --git a/src/user.clj b/src/user.clj
index 417470b..1d59944 100644
--- a/src/user.clj
+++ b/src/user.clj
@@ -34,7 +34,6 @@
     ; TODO: logging
     (println (format "Error updating hash for %s" nick))))
 
-
 (defn reset-token [nick hash ts]
   (sha1-hash nick hash ts))
 
diff --git a/src/utils.clj b/src/utils.clj
index 9008793..607343e 100755
--- a/src/utils.clj
+++ b/src/utils.clj
@@ -40,6 +40,11 @@
       (slurp* url)
       (throw (Exception. (str "Invalid url " u))))))
 
+(defn get-ip [request]
+  (let [ip (get (:headers request) "x-real-ip")  ; behind nginx
+        ip (if ip ip (:remote-addr request))] (str ip))  ; deployed locally
+)
+
 (defn append [& vecs]
   (reduce into vecs))