From bddf6ce9dd9b9a1ed4b57df78ca1dcb3ed5a4bdd Mon Sep 17 00:00:00 2001
From: Ugnich Anton
Date: Wed, 9 Jul 2014 14:55:40 +0700
Subject: Messages privacy, getTag, tags synonyms

---
 src/com/juick/server/MessagesQueries.java | 67 ++++++++++++++-----------------
 1 file changed, 31 insertions(+), 36 deletions(-)

(limited to 'src/com/juick/server/MessagesQueries.java')

diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java
index d02985ee..124f3898 100644
--- a/src/com/juick/server/MessagesQueries.java
+++ b/src/com/juick/server/MessagesQueries.java
@@ -169,30 +169,7 @@ public class MessagesQueries {
 
         return privacy >= 0
                 || uid == owner_uid
-                || ((privacy == -1 || privacy == -2) && uid > 0 && UserQueries.isInWL(sql, owner_uid, uid))
-                || (privacy == -3 && uid > 0 && hasAccessToMessage(sql, mid, uid));
-    }
-
-    public static boolean hasAccessToMessage(Connection sql, int mid, int uid) {
-        boolean ret = false;
-
-        PreparedStatement stmt = null;
-        ResultSet rs = null;
-        try {
-            stmt = sql.prepareStatement("SELECT 1 FROM messages_access WHERE message_id=? AND user_id=?");
-            stmt.setInt(1, mid);
-            stmt.setInt(2, uid);
-            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;
+                || ((privacy == -1 || privacy == -2) && uid > 0 && UserQueries.isInWL(sql, owner_uid, uid));
     }
 
     public static boolean isReadOnly(Connection sql, int mid) {
@@ -445,16 +422,20 @@ 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_tags.tag_id=? AND messages.message_id<? AND (messages.privacy>0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?");
+//                stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (SELECT tag_id FROM tags WHERE tag_id=? OR synonym_id=?) AND messages.message_id<? AND (messages.privacy>0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?");
+                stmt = sql.prepareStatement("SELECT message_id FROM (tags INNER JOIN messages_tags ON ((tags.synonym_id=? OR tags.tag_id=?) AND tags.tag_id=messages_tags.tag_id)) INNER JOIN messages USING(message_id) WHERE messages.message_id<? AND (messages.privacy>0 OR messages.user_id=?) ORDER BY message_id DESC LIMIT ?");
                 stmt.setInt(1, tid);
-                stmt.setInt(2, before);
-                stmt.setInt(3, visitor_uid);
-                stmt.setInt(4, cnt);
+                stmt.setInt(2, tid);
+                stmt.setInt(3, before);
+                stmt.setInt(4, visitor_uid);
+                stmt.setInt(5, cnt);
             } 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 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?");
+                //stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (SELECT tag_id FROM tags WHERE tag_id=? OR synonym_id=?) AND (messages.privacy>0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?");
+                stmt = sql.prepareStatement("SELECT message_id FROM (tags INNER JOIN messages_tags ON ((tags.synonym_id=? OR tags.tag_id=?) AND tags.tag_id=messages_tags.tag_id)) INNER JOIN messages USING(message_id) WHERE messages.privacy>0 OR messages.user_id=? ORDER BY message_id DESC LIMIT ?");
                 stmt.setInt(1, tid);
-                stmt.setInt(2, visitor_uid);
-                stmt.setInt(3, cnt);
+                stmt.setInt(2, tid);
+                stmt.setInt(3, visitor_uid);
+                stmt.setInt(4, cnt);
             }
             rs = stmt.executeQuery();
             rs.beforeFirst();
@@ -589,15 +570,12 @@ public class MessagesQueries {
         ResultSet rs = null;
         try {
             if (before > 0) {
-                stmt = sql.prepareStatement("SELECT message_id FROM (SELECT message_id FROM messages_access WHERE user_id=? AND message_id<? UNION SELECT message_id FROM messages WHERE user_id=? AND privacy<-1 AND message_id<?) AS t ORDER BY message_id DESC LIMIT 20");
+                stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy<0 AND message_id<? ORDER BY message_id DESC LIMIT 20");
                 stmt.setInt(1, uid);
                 stmt.setInt(2, before);
-                stmt.setInt(3, uid);
-                stmt.setInt(4, before);
             } else {
-                stmt = sql.prepareStatement("SELECT message_id FROM (SELECT message_id FROM messages_access WHERE user_id=? UNION SELECT message_id FROM messages WHERE user_id=? AND privacy<-1) AS t 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);
-                stmt.setInt(2, uid);
             }
             rs = stmt.executeQuery();
             rs.beforeFirst();
@@ -1038,4 +1016,21 @@ public class MessagesQueries {
 
         return ret;
     }
+
+    public static boolean setMessagePrivacy(Connection sql, int mid) {
+        boolean ret = false;
+
+        PreparedStatement stmt = null;
+        try {
+            stmt = sql.prepareStatement("UPDATE messages SET privacy=1 WHERE message_id=?");
+            stmt.setInt(1, mid);
+            ret = stmt.executeUpdate() > 0;
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(null, stmt);
+        }
+
+        return ret;
+    }
 }
-- 
cgit v1.2.3