diff options
| -rw-r--r-- | src/com/juick/server/MessagesQueries.java | 34 | ||||
| -rw-r--r-- | src/com/juick/server/TagQueries.java | 21 | ||||
| -rw-r--r-- | src/com/juick/server/UserQueries.java | 143 |
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); } |