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
|
/*
* 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 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;
}
}
|
