aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/com/juick/server/MessagesQueries.java67
-rw-r--r--src/com/juick/server/TagQueries.java32
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);