From 84de24ebd67552e8f1ee01a377a0437b00a8db0e Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Sun, 14 Oct 2012 11:34:26 +0700 Subject: Privacy --- src/com/juick/server/MessagesQueries.java | 56 ++++++++++++++++++++++++++----- src/com/juick/server/UserQueries.java | 25 ++++++++++++-- 2 files changed, 70 insertions(+), 11 deletions(-) diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java index 3ff47ca7..b406eb39 100644 --- a/src/com/juick/server/MessagesQueries.java +++ b/src/com/juick/server/MessagesQueries.java @@ -29,6 +29,30 @@ import java.util.ArrayList; */ public class MessagesQueries { + public static boolean canViewThread(Connection sql, int mid, int uid) { + + int privacy = 0; + int owner_uid = 0; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT user_id,privacy FROM messages WHERE messages.message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + if (rs.first()) { + owner_uid = rs.getInt(1); + privacy = rs.getInt(2); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return privacy >= 0 || (privacy == -1 && uid > 0 && UserQueries.isInWL(sql, owner_uid, uid)) || (privacy == -2 && uid == owner_uid); + } + public static com.juick.Message getMessage(Connection sql, int mid) { com.juick.Message msg = null; @@ -190,7 +214,21 @@ public class MessagesQueries { public static ArrayList getMyFeed(Connection sql, int uid, int before) { ArrayList mids = new ArrayList(20); - + /* + my $sql="SELECT DISTINCT t.message_id FROM ("; + $sql.="SELECT STRAIGHT_JOIN message_id FROM subscr_users INNER JOIN messages ON subscr_users.user_id=messages.user_id WHERE privacy>=0 AND subscr_users.suser_id=$uid"; + $sql.=" UNION SELECT message_id FROM (subscr_users INNER JOIN messages ON subscr_users.user_id=messages.user_id) INNER JOIN wl_users ON messages.user_id=wl_users.user_id WHERE privacy=-1 AND subscr_users.suser_id=$uid AND wl_users.wl_user_id=$uid"; + $sql.=" UNION SELECT message_id FROM messages WHERE user_id=$uid"; + $sql.=" UNION SELECT message_id FROM subscr_messages WHERE suser_id=$uid"; + $sql.=" UNION SELECT message_id FROM favorites WHERE user_id=$uid"; + $sql.=" UNION SELECT message_id FROM favorites INNER JOIN subscr_users ON (subscr_users.suser_id=$uid AND favorites.user_id=subscr_users.user_id)"; + $sql.=") AS t LEFT JOIN messages_tags USING(message_id) WHERE (tag_id NOT IN (SELECT tag_id FROM bl_tags WHERE user_id=$uid) OR tag_id IS NULL)"; + if($before_mid>0) { + $sql.=" AND message_id<$before_mid"; + } + $sql.=" ORDER BY t.message_id DESC LIMIT 20"; + + */ PreparedStatement stmt = null; ResultSet rs = null; try { @@ -242,18 +280,18 @@ public class MessagesQueries { return mids; } - public static ArrayList getIncoming(Connection sql, int uid, int before) { + public static ArrayList getDiscussions(Connection sql, int 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 user_id IN (SELECT suser_id FROM subscr_users WHERE user_id=?) AND message_id 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id0 ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id=0 ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, UID); stmt.setInt(2, before); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>0 ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>=0 ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, UID); } rs = stmt.executeQuery(); @@ -483,11 +521,11 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id=0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, UID); stmt.setInt(2, before); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>=0 AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, UID); } rs = stmt.executeQuery(); @@ -533,7 +571,7 @@ public class MessagesQueries { ArrayList 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"); rs = stmt.executeQuery(); rs.beforeFirst(); while (rs.next()) { diff --git a/src/com/juick/server/UserQueries.java b/src/com/juick/server/UserQueries.java index 906f7742..bd392ade 100644 --- a/src/com/juick/server/UserQueries.java +++ b/src/com/juick/server/UserQueries.java @@ -121,11 +121,11 @@ public class UserQueries { PreparedStatement stmt = null; ResultSet rs = null; try { - stmt = sql.prepareStatement("SELECT logins.hash FROM logins WHERE user_id=?"); + stmt = sql.prepareStatement("SELECT hash FROM logins WHERE user_id=?"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (rs.first()) { - hash = rs.getString(2); + hash = rs.getString(1); } } catch (SQLException e) { System.err.println(e); @@ -235,4 +235,25 @@ 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 { + stmt = sql.prepareStatement("SELECT 1 FROM wl_users WHERE user_id=? AND wl_user_id=?"); + stmt.setInt(1, uid); + stmt.setInt(2, check); + rs = stmt.executeQuery(); + if (rs.first()) { + ret = rs.getInt(1) == 1; + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return ret; + } } -- cgit v1.2.3