From e6aecf508d4c3b115bd6811678604b8a351518d4 Mon Sep 17 00:00:00 2001
From: Ugnich Anton
Date: Sat, 12 Apr 2014 16:42:14 +0700
Subject: SubscriptionsQueries.subscribeMessage; fixed BL in subscriptions

---
 src/com/juick/server/SubscriptionsQueries.java | 50 +++++++++++++++++++++++---
 1 file changed, 46 insertions(+), 4 deletions(-)

(limited to 'src/com')

diff --git a/src/com/juick/server/SubscriptionsQueries.java b/src/com/juick/server/SubscriptionsQueries.java
index 20540b93..ac465948 100644
--- a/src/com/juick/server/SubscriptionsQueries.java
+++ b/src/com/juick/server/SubscriptionsQueries.java
@@ -48,11 +48,39 @@ public class SubscriptionsQueries {
 
         PreparedStatement stmt = null;
         ResultSet rs = null;
+
+        String tbl = "subscr_jids_" + mid;
+        ArrayList<Integer> tags = MessagesQueries.getMessageTagsIDs(sql, mid);
+
+        try {
+            stmt = sql.prepareStatement("CREATE TEMPORARY TABLE " + tbl + "(user_id INT UNSIGNED NOT NULL) ENGINE=MEMORY");
+            stmt.executeUpdate();
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(null, stmt);
+        }
+
+        try {
+            String query = "INSERT INTO " + tbl + " SELECT suser_id FROM subscr_users WHERE user_id=" + uid;
+            if (!tags.isEmpty()) {
+                query += " UNION DISTINCT SELECT suser_id FROM subscr_tags WHERE tag_id IN (" + Utils.convertArrayInt2String(tags) + ") AND suser_id!=" + uid;
+            }
+            stmt = sql.prepareStatement(query);
+            stmt.executeUpdate();
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(null, stmt);
+        }
+
+
         try {
-            stmt = sql.prepareStatement("SELECT DISTINCT jid FROM jids WHERE active=1 AND user_id!=? AND user_id IN (SELECT suser_id FROM subscr_users WHERE user_id=? UNION SELECT suser_id FROM subscr_tags INNER JOIN messages_tags ON (messages_tags.message_id=? AND subscr_tags.tag_id=messages_tags.tag_id))");
-            stmt.setInt(1, uid);
-            stmt.setInt(2, uid);
-            stmt.setInt(3, mid);
+            String query = "SELECT jids.jid FROM " + tbl + " INNER JOIN jids ON (" + tbl + ".user_id=jids.user_id) WHERE jids.active=1 AND " + tbl + ".user_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=" + uid + ")";
+            if (!tags.isEmpty()) {
+                query += " AND " + tbl + ".user_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + Utils.convertArrayInt2String(tags) + "))";
+            }
+            stmt = sql.prepareStatement(query);
             rs = stmt.executeQuery();
             rs.beforeFirst();
             while (rs.next()) {
@@ -63,6 +91,16 @@ public class SubscriptionsQueries {
         } finally {
             Utils.finishSQL(rs, stmt);
         }
+
+        try {
+            stmt = sql.prepareStatement("DROP TABLE " + tbl);
+            stmt.executeUpdate();
+        } catch (SQLException e) {
+            System.err.println(e);
+        } finally {
+            Utils.finishSQL(null, stmt);
+        }
+
         return jids;
     }
 
@@ -87,4 +125,8 @@ public class SubscriptionsQueries {
         }
         return jids;
     }
+
+    public static boolean subscribeMessage(Connection sql, int mid, int vuid) {
+        return SQLHelpers.execute(sql, "INSERT IGNORE INTO subscr_messages(suser_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1;
+    }
 }
-- 
cgit v1.2.3