Messages privacy, getTag, tags synonyms

2 files changed, 59 insertions, 40 deletions

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;
+    }
 }
diff --git a/src/com/juick/server/TagQueries.java b/src/com/juick/server/TagQueries.java
index 54ec862d..493e8675 100644
--- a/src/com/juick/server/TagQueries.java
+++ b/src/com/juick/server/TagQueries.java
@@ -30,20 +30,44 @@ import java.util.ArrayList;
  */
 public class TagQueries {
 
+    public static com.juick.Tag getTag(Connection sql, int tid) {
+        com.juick.Tag ret = null;
+
+        PreparedStatement stmt = null;
+        ResultSet rs = null;
+        try {
+            stmt = sql.prepareStatement("SELECT synonym_id,name FROM tags WHERE tag_id=?");
+            stmt.setInt(1, tid);
+            rs = stmt.executeQuery();
+            if (rs.first()) {
+                ret = new com.juick.Tag();
+                ret.TID = tid;
+                ret.SynonymID = rs.getInt(1);
+                ret.Name = rs.getString(2);
+            }
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(rs, stmt);
+        }
+
+        return ret;
+    }
+
     public static com.juick.Tag getTag(Connection sql, String tag, boolean autoCreate) {
         com.juick.Tag ret = null;
 
         PreparedStatement stmt = null;
         ResultSet rs = null;
         try {
-            stmt = sql.prepareStatement("SELECT tag_id,name FROM tags WHERE name=?");
+            stmt = sql.prepareStatement("SELECT tag_id,synonym_id,name FROM tags WHERE name=?");
             stmt.setString(1, tag);
             rs = stmt.executeQuery();
-            rs.beforeFirst();
-            while (rs.next()) {
+            if (rs.first()) {
                 ret = new com.juick.Tag();
                 ret.TID = rs.getInt(1);
-                ret.Name = rs.getString(2);
+                ret.SynonymID = rs.getInt(2);
+                ret.Name = rs.getString(3);
             }
         } catch (SQLException e) {
             System.err.println(e);