From bddf6ce9dd9b9a1ed4b57df78ca1dcb3ed5a4bdd Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Wed, 9 Jul 2014 14:55:40 +0700 Subject: Messages privacy, getTag, tags synonyms --- src/com/juick/server/MessagesQueries.java | 67 ++++++++++++++----------------- src/com/juick/server/TagQueries.java | 32 +++++++++++++-- 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_id0 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_id0 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_id0 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 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); -- cgit v1.2.3