From 917e44c66ee99f31b0ac655c56b598d71014652e Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Wed, 6 Mar 2013 18:25:50 +0700 Subject: MessagesQueries.getMessageTagsIDs MessagesQueries visitor_id UserQueries.createUser --- src/com/juick/server/MessagesQueries.java | 68 ++++++++++++++++++++++--------- src/com/juick/server/UserQueries.java | 24 +++++++++++ 2 files changed, 73 insertions(+), 19 deletions(-) diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java index 227b3fea..ad00b9d2 100644 --- a/src/com/juick/server/MessagesQueries.java +++ b/src/com/juick/server/MessagesQueries.java @@ -232,6 +232,28 @@ public class MessagesQueries { return user; } + public static ArrayList getMessageTagsIDs(Connection sql, int mid) { + ArrayList tids = new ArrayList(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT tag_id FROM messages_tags WHERE message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + tids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return tids; + } + public static ArrayList getMessageRecommendations(Connection sql, int mid) { ArrayList users = new ArrayList(); @@ -254,17 +276,19 @@ public class MessagesQueries { return users; } - public static ArrayList getAll(Connection sql, int before) { + public static ArrayList getAll(Connection sql, int visitor_uid, int before) { ArrayList mids = new ArrayList(20); PreparedStatement stmt = null; ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages WHERE messages.message_id0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 OR user_id=?) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, before); + stmt.setInt(2, visitor_uid); } else { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages WHERE messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE (privacy>0 OR user_id=?) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, visitor_uid); } rs = stmt.executeQuery(); rs.beforeFirst(); @@ -279,19 +303,21 @@ public class MessagesQueries { return mids; } - public static ArrayList getTag(Connection sql, int tid, int before) { + public static ArrayList getTag(Connection sql, int tid, int visitor_uid, int before) { ArrayList mids = new ArrayList(20); PreparedStatement stmt = null; ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id=? AND messages.message_id0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id=? AND messages.message_id0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT 20"); stmt.setInt(1, tid); stmt.setInt(2, before); + stmt.setInt(3, visitor_uid); } else { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id=? AND messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id=? AND (messages.privacy>0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT 20"); stmt.setInt(1, tid); + stmt.setInt(2, visitor_uid); } rs = stmt.executeQuery(); rs.beforeFirst(); @@ -306,19 +332,21 @@ public class MessagesQueries { return mids; } - public static ArrayList getPlace(Connection sql, int place_id, int before) { + public static ArrayList getPlace(Connection sql, int place_id, int visitor_uid, int before) { ArrayList mids = new ArrayList(20); PreparedStatement stmt = null; ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE place_id=? AND message_id0 ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE place_id=? AND message_id0 OR user_id=?) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, place_id); stmt.setInt(2, before); + stmt.setInt(3, visitor_uid); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE place_id=? AND privacy>0 ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE place_id=? AND (privacy>0 OR user_id=?) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, place_id); + stmt.setInt(2, visitor_uid); } rs = stmt.executeQuery(); rs.beforeFirst(); @@ -339,13 +367,13 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id=0 OR (privacy=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?)) AND message_id NOT IN (SELECT message_id FROM messages_tags WHERE tag_id IN (SELECT tag_id FROM bl_tags WHERE user_id=?)) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?)) AND message_id NOT IN (SELECT message_id FROM messages_tags WHERE tag_id IN (SELECT tag_id FROM bl_tags WHERE user_id=?)) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, uid); stmt.setInt(2, before); stmt.setInt(3, uid); stmt.setInt(4, uid); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE privacy>=0 OR (privacy=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?)) AND message_id NOT IN (SELECT message_id FROM messages_tags WHERE tag_id IN (SELECT tag_id FROM bl_tags WHERE user_id=?)) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?)) AND message_id NOT IN (SELECT message_id FROM messages_tags WHERE tag_id IN (SELECT tag_id FROM bl_tags WHERE user_id=?)) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, uid); stmt.setInt(2, uid); stmt.setInt(3, uid); @@ -475,17 +503,19 @@ public class MessagesQueries { return mids; } - public static ArrayList getPhotos(Connection sql, int before) { + public static ArrayList getPhotos(Connection sql, int visitor_uid, int before) { ArrayList mids = new ArrayList(20); PreparedStatement stmt = null; ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 OR user_id=?) AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, before); + stmt.setInt(2, visitor_uid); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE (privacy>0 OR user_id=?) AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, visitor_uid); } rs = stmt.executeQuery(); rs.beforeFirst(); @@ -507,11 +537,11 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sqlSearch.prepareStatement("SELECT @id AS message_id FROM messages WHERE MATCH(?) AND @id mids = new ArrayList(20); if (mids0.size() > 0) { try { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArray2String(mids0) + ") AND privacy>0 ORDER BY message_id DESC"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArray2String(mids0) + ") AND privacy>0 ORDER BY message_id DESC LIMIT 20"); rs = stmt.executeQuery(); rs.beforeFirst(); while (rs.next()) { @@ -578,12 +608,12 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? AND messages.message_id" + privacy + " ORDER BY messages.message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? AND messages.message_id=" + privacy + " ORDER BY messages.message_id DESC LIMIT 20"); stmt.setInt(1, UID); stmt.setInt(2, TID); stmt.setInt(3, before); } else { - stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? AND messages.privacy>" + privacy + " ORDER BY messages.message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? AND messages.privacy>=" + privacy + " ORDER BY messages.message_id DESC LIMIT 20"); stmt.setInt(1, UID); stmt.setInt(2, TID); } diff --git a/src/com/juick/server/UserQueries.java b/src/com/juick/server/UserQueries.java index ff1d0e24..17873676 100644 --- a/src/com/juick/server/UserQueries.java +++ b/src/com/juick/server/UserQueries.java @@ -21,6 +21,7 @@ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Statement; import java.util.ArrayList; import java.util.Random; @@ -32,6 +33,29 @@ public class UserQueries { static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; + public static int createUser(Connection sql, String username, String password, String lang) { + int uid = 0; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("INSERT INTO users(nick,passw,lang) VALUES (?,?,?)", Statement.RETURN_GENERATED_KEYS); + stmt.setString(1, username); + stmt.setString(2, password); + stmt.setString(3, lang); + stmt.executeUpdate(); + rs = stmt.getGeneratedKeys(); + if (rs.first()) { + uid = rs.getInt(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return uid; + } + public static com.juick.User getUserByNick(Connection sql, String username) { com.juick.User user = null; -- cgit v1.2.3