1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.juick.server;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
*
* @author ugnich
*/
public class SubscriptionsQueries {
public static ArrayList<String> getJIDSubscribedToUser(Connection sql, int uid, boolean friendsonly) {
ArrayList<String> jids = new ArrayList<String>();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
if (friendsonly == false) {
stmt = sql.prepareStatement("SELECT jids.jid FROM subscr_users INNER JOIN jids ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1");
stmt.setInt(1, uid);
} else {
stmt = sql.prepareStatement("SELECT jids.jid FROM subscr_users INNER JOIN jids ON (subscr_users.user_id=? AND subscr_users.suser_id=jids.user_id) WHERE jids.active=1 AND jids.user_id IN (SELECT wl_user_id FROM wl_users WHERE user_id=?)");
stmt.setInt(1, uid);
stmt.setInt(2, uid);
}
rs = stmt.executeQuery();
rs.beforeFirst();
while (rs.next()) {
jids.add(rs.getString(1));
}
} catch (SQLException e) {
System.err.println(e);
} finally {
Utils.finishSQL(rs, stmt);
}
return jids;
}
public static ArrayList<String> getJIDSubscribedToUserAndTags(Connection sql, int uid, int mid) {
ArrayList<String> jids = new ArrayList<String>();
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 {
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()) {
jids.add(rs.getString(1));
}
} catch (SQLException e) {
System.err.println(e);
} 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;
}
public static ArrayList<String> getJIDSubscribedToComments(Connection sql, int mid, int ignore_uid) {
ArrayList<String> jids = new ArrayList<String>();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = sql.prepareStatement("SELECT jids.jid FROM subscr_messages INNER JOIN jids ON (subscr_messages.message_id=? AND subscr_messages.suser_id=jids.user_id) WHERE jids.user_id!=? AND jids.active=1");
stmt.setInt(1, mid);
stmt.setInt(2, ignore_uid);
rs = stmt.executeQuery();
rs.beforeFirst();
while (rs.next()) {
jids.add(rs.getString(1));
}
} catch (SQLException e) {
System.err.println(e);
} finally {
Utils.finishSQL(rs, stmt);
}
return jids;
}
public static ArrayList<String> getJIDSubscribedToUserRecommendations(Connection sql, int uid, int mid, int muid) {
ArrayList<String> jids = new ArrayList<String>();
ArrayList<Integer> tags = MessagesQueries.getMessageTagsIDs(sql, mid);
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String query = "SELECT jid FROM jids WHERE active=1 AND user_id!=" + uid;
query += " AND user_id IN (SELECT suser_id FROM subscr_users WHERE user_id=" + uid + ")";
query += " AND user_id NOT IN (SELECT user_id FROM bl_users WHERE bl_user_id=" + muid + ")";
query += " AND user_id NOT IN (SELECT suser_id FROM subscr_users WHERE user_id=" + muid + ")";
query += " AND user_id NOT IN (SELECT suser_id FROM subscr_messages WHERE message_id=" + mid + ")";
query += " AND user_id NOT IN (SELECT user_id FROM favorites WHERE message_id=" + mid + ")";
query += " AND user_id NOT IN (SELECT subscr_users.suser_id FROM subscr_users INNER JOIN favorites ON (favorites.message_id=" + mid + " AND subscr_users.user_id=favorites.user_id AND favorites.user_id!=" + uid + "))";
if (!tags.isEmpty()) {
String tagsStr = Utils.convertArrayInt2String(tags);
query += " AND user_id NOT IN (SELECT suser_id FROM subscr_tags WHERE tag_id IN (" + tagsStr + "))";
query += " AND user_id NOT IN (SELECT user_id FROM bl_tags WHERE tag_id IN (" + tagsStr + "))";
}
stmt = sql.prepareStatement(query);
rs = stmt.executeQuery();
rs.beforeFirst();
while (rs.next()) {
jids.add(rs.getString(1));
}
} catch (SQLException e) {
System.err.println(e);
} finally {
Utils.finishSQL(rs, stmt);
}
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;
}
}
|
