getBLUsers, getBLTags, setPopular

3 files changed, 132 insertions, 66 deletions

diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java
index 1f4c8408..d2254575 100644
--- a/src/com/juick/server/MessagesQueries.java
+++ b/src/com/juick/server/MessagesQueries.java
@@ -291,21 +291,21 @@ public class MessagesQueries {
         try {
             if (visitor_uid > 1) {
                 if (before > 0) {
-                    stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND popular>=-1 AND (privacy>0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) ORDER BY message_id DESC LIMIT 20");
+                    stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND hidden=0 AND (privacy>0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) ORDER BY message_id DESC LIMIT 20");
                     stmt.setInt(1, before);
                     stmt.setInt(2, visitor_uid);
                     stmt.setInt(3, visitor_uid);
                 } else {
-                    stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE popular>=-1 AND (privacy>0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) ORDER BY message_id DESC LIMIT 20");
+                    stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE hidden=0 AND (privacy>0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) ORDER BY message_id DESC LIMIT 20");
                     stmt.setInt(1, visitor_uid);
                     stmt.setInt(2, visitor_uid);
                 }
             } else {
                 if (before > 0) {
-                    stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND popular>=-1 AND privacy>0 ORDER BY message_id DESC LIMIT 20");
+                    stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND hidden=0 AND privacy>0 ORDER BY message_id DESC LIMIT 20");
                     stmt.setInt(1, before);
                 } else {
-                    stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE popular>=-1 AND privacy>0 ORDER BY message_id DESC LIMIT 20");
+                    stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE hidden=0 AND privacy>0 ORDER BY message_id DESC LIMIT 20");
                 }
 
             }
@@ -890,9 +890,17 @@ public class MessagesQueries {
 
         PreparedStatement stmt = null;
         try {
-            stmt = sql.prepareStatement("UPDATE messages SET popular=? WHERE message_id=?");
-            stmt.setInt(1, popular);
-            stmt.setInt(2, mid);
+            if (popular == -2) {
+                stmt = sql.prepareStatement("UPDATE messages SET hidden=1 WHERE message_id=?");
+                stmt.setInt(1, mid);
+            } else if (popular == -1) {
+                stmt = sql.prepareStatement("UPDATE messages SET popular=0 WHERE message_id=?");
+                stmt.setInt(1, mid);
+            } else {
+                stmt = sql.prepareStatement("UPDATE messages SET popular=? WHERE message_id=?");
+                stmt.setInt(1, popular);
+                stmt.setInt(2, mid);
+            }
             ret = stmt.executeUpdate() > 0;
         } catch (SQLException e) {
             System.err.println(e);
@@ -900,6 +908,18 @@ public class MessagesQueries {
             Utils.finishSQL(null, stmt);
         }
 
+        if (popular == -1) {
+            try {
+                stmt = sql.prepareStatement("INSERT INTO top_ignore_messages VALUES (?)");
+                stmt.setInt(1, mid);
+                stmt.executeUpdate();
+            } catch (SQLException e) {
+                System.err.println(e);
+            } finally {
+                Utils.finishSQL(null, stmt);
+            }
+        }
+
         return ret;
     }
 }
diff --git a/src/com/juick/server/TagQueries.java b/src/com/juick/server/TagQueries.java
index 82c0f9ae..87704c62 100644
--- a/src/com/juick/server/TagQueries.java
+++ b/src/com/juick/server/TagQueries.java
@@ -95,4 +95,25 @@ public class TagQueries {
         }
         return tags;
     }
+
+    public static ArrayList<String> getUserBLTags(Connection sql, int uid) {
+        ArrayList<String> tags = new ArrayList<String>();
+
+        PreparedStatement stmt = null;
+        ResultSet rs = null;
+        try {
+            stmt = sql.prepareStatement("SELECT tags.name FROM tags INNER JOIN bl_tags ON (bl_tags.user_id=? AND bl_tags.tag_id=tags.tag_id)");
+            stmt.setInt(1, uid);
+            rs = stmt.executeQuery();
+            rs.beforeFirst();
+            while (rs.next()) {
+                tags.add(rs.getString(1));
+            }
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(rs, stmt);
+        }
+        return tags;
+    }
 }
diff --git a/src/com/juick/server/UserQueries.java b/src/com/juick/server/UserQueries.java
index a47ef815..1933f26a 100644
--- a/src/com/juick/server/UserQueries.java
+++ b/src/com/juick/server/UserQueries.java
@@ -31,12 +31,12 @@ import java.util.UUID;
  * @author Ugnich Anton
  */
 public class UserQueries {
-
+    
     static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
-
+    
     public static String getSignUpHashByJID(Connection sql, String jid) {
         String hash = SQLHelpers.getString(sql, "SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL", jid);
-
+        
         if (hash == null) {
             hash = UUID.randomUUID().toString();
             PreparedStatement stmt = null;
@@ -51,13 +51,13 @@ public class UserQueries {
                 Utils.finishSQL(null, stmt);
             }
         }
-
+        
         return hash;
     }
-
+    
     public static int createUser(Connection sql, String username, String password) {
         int uid = 0;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -74,16 +74,16 @@ public class UserQueries {
         } finally {
             Utils.finishSQL(rs, stmt);
         }
-
+        
         SQLHelpers.executeInt(sql, "INSERT INTO useroptions(user_id) VALUES (?)", uid);
         SQLHelpers.executeInt(sql, "INSERT INTO subscr_users(user_id,suser_id) VALUES (2,?)", uid);
-
+        
         return uid;
     }
-
+    
     public static com.juick.User getUserByUID(Connection sql, int uid) {
         com.juick.User user = null;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -102,10 +102,10 @@ public class UserQueries {
         }
         return user;
     }
-
+    
     public static com.juick.User getUserByName(Connection sql, String username) {
         com.juick.User user = null;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -124,10 +124,10 @@ public class UserQueries {
         }
         return user;
     }
-
+    
     public static com.juick.User getUserByJID(Connection sql, String jid) {
         com.juick.User user = null;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -147,10 +147,10 @@ public class UserQueries {
         }
         return user;
     }
-
+    
     public static ArrayList<com.juick.User> getUsersByName(Connection sql, ArrayList<String> unames) {
         ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -170,10 +170,10 @@ public class UserQueries {
         }
         return users;
     }
-
+    
     public static ArrayList<com.juick.User> getUsersByID(Connection sql, ArrayList<Integer> uids) {
         ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -191,13 +191,13 @@ public class UserQueries {
         } finally {
             Utils.finishSQL(rs, stmt);
         }
-
+        
         return users;
     }
-
+    
     public static boolean fillUsersByID(Connection sql, ArrayList<com.juick.User> users) {
         boolean ret = false;
-
+        
         String uids = "";
         final int usersSize = users.size();
         for (int i = 0; i < usersSize; i++) {
@@ -206,7 +206,7 @@ public class UserQueries {
             }
             uids += users.get(i).UID;
         }
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -227,13 +227,13 @@ public class UserQueries {
         } finally {
             Utils.finishSQL(rs, stmt);
         }
-
+        
         return ret;
     }
-
+    
     public static ArrayList<com.juick.User> getUsersByJID(Connection sql, ArrayList<String> jids) {
         ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -254,26 +254,26 @@ public class UserQueries {
         }
         return users;
     }
-
+    
     public static String getJIDbyUID(Connection sql, int uid) {
         return SQLHelpers.getString(sql, "SELECT jid FROM jids WHERE user_id=? AND active=1", uid);
     }
-
+    
     public static int getUIDbyJID(Connection sql, String jid) {
         return SQLHelpers.getInt(sql, "SELECT user_id FROM jids WHERE jid=?", jid, 0);
     }
-
+    
     public static int getUIDbyName(Connection sql, String uname) {
         return SQLHelpers.getInt(sql, "SELECT id FROM users WHERE nick=?", uname, 0);
     }
-
+    
     public static int getUIDbyHash(Connection sql, String hash) {
         return SQLHelpers.getInt(sql, "SELECT user_id FROM logins WHERE hash=?", hash, 0);
     }
-
+    
     public static com.juick.User getUserByHash(Connection sql, String hash) {
         com.juick.User user = null;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -293,10 +293,10 @@ public class UserQueries {
         }
         return user;
     }
-
+    
     public static String getHashByUID(Connection sql, int uid) {
         String hash = SQLHelpers.getString(sql, "SELECT hash FROM logins WHERE user_id=?", uid);
-
+        
         if (hash == null) {
             hash = generateHash(16);
             PreparedStatement stmt = null;
@@ -311,10 +311,10 @@ public class UserQueries {
                 Utils.finishSQL(null, stmt);
             }
         }
-
+        
         return hash;
     }
-
+    
     public static String generateHash(int len) {
         Random rnd = new Random();
         StringBuilder sb = new StringBuilder(len);
@@ -323,11 +323,11 @@ public class UserQueries {
         }
         return sb.toString();
     }
-
+    
     public static boolean checkUserNameValid(String uname) {
         return uname != null && uname.length() >= 2 && uname.length() <= 16 && uname.matches("[a-zA-Z0-9\\-]+");
     }
-
+    
     public static int checkPassword(Connection sql, String username, String password) {
         int uid = 0;
         PreparedStatement stmt = null;
@@ -350,10 +350,10 @@ public class UserQueries {
         }
         return uid;
     }
-
+    
     public static int getUserOptionInt(Connection sql, int uid, String option, int defaultValue) {
         int ret = defaultValue;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -370,7 +370,7 @@ public class UserQueries {
         }
         return ret;
     }
-
+    
     public static void setUserOptionInt(Connection sql, int uid, String option, int value) {
         PreparedStatement stmt = null;
         try {
@@ -384,10 +384,10 @@ public class UserQueries {
             Utils.finishSQL(null, stmt);
         }
     }
-
+    
     public static boolean getCanMedia(Connection sql, int uid) {
         boolean ret = false;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -404,10 +404,10 @@ public class UserQueries {
         }
         return ret;
     }
-
+    
     public static boolean isInWL(Connection sql, int uid, int check) {
         boolean ret = false;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -425,10 +425,10 @@ public class UserQueries {
         }
         return ret;
     }
-
+    
     public static boolean isInBL(Connection sql, int uid, int check) {
         boolean ret = false;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -446,10 +446,10 @@ public class UserQueries {
         }
         return ret;
     }
-
+    
     public static boolean isInBLAny(Connection sql, int uid, int uid2) {
         boolean ret = false;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -469,10 +469,10 @@ public class UserQueries {
         }
         return ret;
     }
-
+    
     public static ArrayList<Integer> checkBL(Connection sql, int visitor, ArrayList<Integer> uids) {
         ArrayList<Integer> ret = new ArrayList<Integer>();
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -488,13 +488,13 @@ public class UserQueries {
         } finally {
             Utils.finishSQL(rs, stmt);
         }
-
+        
         return ret;
     }
-
+    
     public static boolean isSubscribed(Connection sql, int uid, int check) {
         boolean ret = false;
-
+        
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
@@ -512,27 +512,52 @@ public class UserQueries {
         }
         return ret;
     }
-
+    
     public static ArrayList<Integer> getUserRead(Connection sql, int uid) {
         return SQLHelpers.getArrayInteger(sql, "SELECT user_id FROM subscr_users WHERE suser_id=?", uid);
     }
-
+    
     public static ArrayList<Integer> getUserReaders(Connection sql, int uid) {
         return SQLHelpers.getArrayInteger(sql, "SELECT suser_id FROM subscr_users WHERE user_id=?", uid);
     }
-
+    
+    public static ArrayList<com.juick.User> getUserBLUsers(Connection sql, int uid) {
+        ArrayList<com.juick.User> users = new ArrayList<com.juick.User>();
+        
+        PreparedStatement stmt = null;
+        ResultSet rs = null;
+        try {
+            stmt = sql.prepareStatement("SELECT users.id,users.nick FROM users INNER JOIN bl_users ON(bl_users.bl_user_id=users.id) WHERE bl_users.user_id=?");
+            stmt.setInt(1, uid);
+            rs = stmt.executeQuery();
+            rs.beforeFirst();
+            while (rs.next()) {
+                com.juick.User u = new com.juick.User();
+                u.UID = rs.getInt(1);
+                u.UName = rs.getString(2);
+                users.add(u);
+            }
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(rs, stmt);
+        }
+        
+        return users;
+    }
+    
     public static int getStatsIRead(Connection sql, int uid) {
         return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", uid, 0);
     }
-
+    
     public static int getStatsMyReaders(Connection sql, int uid) {
         return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE user_id=?", uid, 0);
     }
-
+    
     public static int getStatsMessages(Connection sql, int uid) {
         return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM messages WHERE user_id=?", uid, 0);
     }
-
+    
     public static int getStatsReplies(Connection sql, int uid) {
         return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM replies WHERE user_id=?", uid, 0);
     }