diff options
Diffstat (limited to 'src/main/java/com/juick/server/MessagesQueries.java')
| -rw-r--r-- | src/main/java/com/juick/server/MessagesQueries.java | 1065 |
1 files changed, 1065 insertions, 0 deletions
diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java new file mode 100644 index 00000000..8bef9018 --- /dev/null +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -0,0 +1,1065 @@ +/* + * Juick + * Copyright (C) 2008-2011, Ugnich Anton + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + */ +package com.juick.server; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Types; +import java.util.ArrayList; +import java.util.Collections; + +/** + * + * @author Ugnich Anton + */ +public class MessagesQueries { + + public static int createMessage(Connection sql, int uid, String txt, String attachment, ArrayList<com.juick.Tag> tags) { + int mid = 0; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("INSERT INTO messages(user_id,attach) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS); + stmt.setInt(1, uid); + if (attachment != null) { + stmt.setString(2, attachment); + } else { + stmt.setNull(2, Types.VARCHAR); + } + stmt.executeUpdate(); + rs = stmt.getGeneratedKeys(); + if (rs.first()) { + mid = rs.getInt(1); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + + if (mid > 0) { + String tagsNames = ""; + String tagsIDs = ""; + + for (int i = 0; i < tags.size(); i++) { + if (i > 0) { + tagsNames += " "; + tagsIDs += ","; + } + tagsNames += tags.get(i).Name; + tagsIDs += "(" + mid + "," + tags.get(i).TID + ")"; + } + + try { + stmt = sql.prepareStatement("INSERT INTO messages_tags(message_id,tag_id) VALUES " + tagsIDs); + stmt.executeUpdate(); + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + + try { + stmt = sql.prepareStatement("INSERT INTO messages_txt(message_id,tags,txt) VALUES (?,?,?)"); + stmt.setInt(1, mid); + stmt.setString(2, tagsNames); + stmt.setString(3, txt); + stmt.executeUpdate(); + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + } + + return mid; + } + + public static int createReply(Connection sql, int mid, int rid, int uid, String txt, String attachment) { + int ridnew = getReplyIDIncrement(sql, mid); + + PreparedStatement stmt = null; + try { + stmt = sql.prepareStatement("INSERT INTO replies(message_id,reply_id,user_id,replyto,attach,txt) VALUES (?,?,?,?,?,?)"); + stmt.setInt(1, mid); + stmt.setInt(2, ridnew); + stmt.setInt(3, uid); + stmt.setInt(4, rid); + if (attachment != null) { + stmt.setString(5, attachment); + } else { + stmt.setNull(5, Types.VARCHAR); + } + stmt.setString(6, txt); + if (stmt.executeUpdate() == 0) { + ridnew = 0; + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + + if (ridnew > 0) { + SQLHelpers.executeInt(sql, "UPDATE messages SET replies=replies+1 WHERE message_id=?", mid); + } + + return ridnew; + } + + public static int getReplyIDIncrement(Connection sql, int mid) { + int rid = 0; + + SQLHelpers.execute(sql, "LOCK TABLES messages WRITE"); + if (SQLHelpers.executeInt(sql, "UPDATE messages SET maxreplyid=maxreplyid+1 WHERE message_id=?", mid) == 1) { + rid = SQLHelpers.getInt(sql, "SELECT maxreplyid FROM messages WHERE message_id=?", mid, 0); + } + SQLHelpers.execute(sql, "UNLOCK TABLES"); + + return rid; + } + + public static boolean recommendMessage(Connection sql, int mid, int vuid) { + boolean res = SQLHelpers.execute(sql, "INSERT IGNORE INTO favorites(user_id,message_id) VALUES (" + vuid + "," + mid + ")") == 1; + if (res) { + SQLHelpers.executeInt(sql, "UPDATE messages SET likes=likes+1 WHERE message_id=?", mid); + } + return res; + } + + public static boolean canViewThread(Connection sql, int mid, int uid) { + + int privacy = 0; + int owner_uid = 0; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT user_id,privacy FROM messages WHERE messages.message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + if (rs.first()) { + owner_uid = rs.getInt(1); + privacy = rs.getInt(2); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return privacy >= 0 + || uid == owner_uid + || ((privacy == -1 || privacy == -2) && uid > 0 && UserQueries.isInWL(sql, owner_uid, uid)); + } + + public static boolean isReadOnly(Connection sql, int mid) { + boolean ret = false; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT readonly FROM messages WHERE message_id=?"); + stmt.setInt(1, mid); + 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; + } + + public static boolean isSubscribed(Connection sql, int uid, int mid) { + boolean ret = false; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT 1 FROM subscr_messages WHERE suser_id=? AND message_id=?"); + stmt.setInt(1, uid); + stmt.setInt(2, mid); + 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; + } + + public static int getMessagePrivacy(Connection sql, int mid) { + return SQLHelpers.getInt(sql, "SELECT privacy FROM messages WHERE message_id=?", mid, -4); + } + + public static com.juick.Message getMessage(Connection sql, int mid) { + com.juick.Message msg = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT messages.user_id,users.nick,TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.readonly,messages.privacy,messages.replies,messages.attach,messages.place_id,messages.lat,messages.lon,messages.likes,messages.hidden FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + if (rs.first()) { + msg = new com.juick.Message(); + msg.MID = mid; + msg.User = new com.juick.User(); + msg.User.UID = rs.getInt(1); + msg.User.UName = rs.getString(2); + msg.TimeAgo = rs.getInt(3); + msg.TimestampString = rs.getString(4); + msg.ReadOnly = rs.getBoolean(5); + msg.Privacy = rs.getInt(6); + msg.FriendsOnly = msg.Privacy < 0; + msg.Replies = rs.getInt(7); + msg.AttachmentType = rs.getString(8); + //if (rs.getInt(9) > 0) { + // msg.Place = PlacesQueries.getPlace(sql, rs.getInt(9)); + //} + if (rs.getDouble(10) != 0) { + msg.Place = new com.juick.Place(); + msg.Place.lat = rs.getDouble(10); + msg.Place.lon = rs.getDouble(11); + } + msg.Likes = rs.getInt(12); + msg.Hidden = rs.getBoolean(13); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + if (msg == null) { + return null; + } + + try { + stmt = sql.prepareStatement("SELECT tags,repliesby,txt FROM messages_txt WHERE message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + if (rs.first()) { + if (rs.getString(1) != null) { + msg.parseTags(rs.getString(1)); + } + msg.RepliesBy = rs.getString(2); + msg.Text = rs.getString(3); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return msg; + } + + public static com.juick.Message getReply(Connection sql, int mid, int rid) { + com.juick.Message msg = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT replies.user_id,users.nick,replies.replyto,DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s'),replies.attach,replies.txt FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? AND replies.reply_id=?"); + stmt.setInt(1, mid); + stmt.setInt(2, rid); + rs = stmt.executeQuery(); + if (rs.first()) { + msg = new com.juick.Message(); + msg.MID = mid; + msg.RID = rid; + msg.User = new com.juick.User(); + msg.User.UID = rs.getInt(1); + msg.User.UName = rs.getString(2); + msg.ReplyTo = rs.getInt(3); + msg.TimestampString = rs.getString(4); + msg.AttachmentType = rs.getString(5); + msg.Text = rs.getString(6); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return msg; + } + + public static com.juick.User getMessageAuthor(Connection sql, int mid) { + com.juick.User user = null; + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT messages.user_id,users.nick FROM messages INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + if (rs.first()) { + user = new com.juick.User(); + user.UID = rs.getInt(1); + user.UName = rs.getString(2); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return user; + } + + public static ArrayList<com.juick.Tag> getMessageTags(Connection sql, int mid) { + ArrayList<com.juick.Tag> tags = new ArrayList<com.juick.Tag>(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags INNER JOIN messages_tags ON (messages_tags.message_id=? AND messages_tags.tag_id=tags.tag_id)"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.Tag t = new com.juick.Tag(); + t.TID = rs.getInt(1); + t.SynonymID = rs.getInt(2); + t.Name = rs.getString(3); + t.UsageCnt = rs.getInt(4); + tags.add(t); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return tags; + } + + public static ArrayList<Integer> getMessageTagsIDs(Connection sql, int mid) { + ArrayList<Integer> tids = new ArrayList<Integer>(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT tag_id FROM messages_tags WHERE message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + tids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return tids; + } + + public static ArrayList<String> getMessageRecommendations(Connection sql, int mid) { + ArrayList<String> users = new ArrayList<String>(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT users.nick FROM favorites INNER JOIN users ON (favorites.message_id=? AND favorites.user_id=users.id)"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + users.add(rs.getString(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return users; + } + + public static ArrayList<Integer> getAll(Connection sql, int visitor_uid, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (visitor_uid > 1) { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND hidden=0 AND (privacy>0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, before); + stmt.setInt(2, visitor_uid); + stmt.setInt(3, visitor_uid); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE hidden=0 AND (privacy>0 OR user_id=?) AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, visitor_uid); + stmt.setInt(2, visitor_uid); + } + } else { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND hidden=0 AND privacy>0 ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE hidden=0 AND privacy>0 ORDER BY message_id DESC LIMIT 20"); + } + + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getTag(Connection sql, int tid, int visitor_uid, int before, int cnt) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + 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 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, 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 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, tid); + stmt.setInt(3, visitor_uid); + stmt.setInt(4, cnt); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getTags(Connection sql, String tids, int visitor_uid, int before, int cnt) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + 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 IN (" + tids + ") AND messages.message_id<? AND (messages.privacy>0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?"); + stmt.setInt(1, before); + stmt.setInt(2, visitor_uid); + stmt.setInt(3, cnt); + } else { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") AND (messages.privacy>0 OR messages.user_id=?) ORDER BY messages.message_id DESC LIMIT ?"); + stmt.setInt(1, visitor_uid); + stmt.setInt(2, cnt); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getPlace(Connection sql, int place_id, int visitor_uid, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE place_id=? AND message_id<? AND (privacy>0 OR user_id=?) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, place_id); + stmt.setInt(2, before); + stmt.setInt(3, visitor_uid); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE place_id=? AND (privacy>0 OR user_id=?) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, place_id); + stmt.setInt(2, visitor_uid); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getMyFeed(Connection sql, int uid, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(40); + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id<? AND (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + stmt.setInt(2, before); + stmt.setInt(3, uid); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + stmt.setInt(2, uid); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + Collections.sort(mids, Collections.reverseOrder()); + int remove = mids.size() - 20; + for (int i = 0; i < remove; i++) { + mids.remove(20); + } + + return mids; + } + + public static ArrayList<Integer> getPrivate(Connection sql, int uid, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + 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); + } else { + 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); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getDiscussions(Connection sql, int uid, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM subscr_messages WHERE suser_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM subscr_messages WHERE suser_id=? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return mids; + } + + public static ArrayList<Integer> getRecommended(Connection sql, int uid, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) AND message_id<? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + + return mids; + } + + public static ArrayList<Integer> getPopular(Connection sql, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND privacy>0 AND popular>0 ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND popular>0 ORDER BY message_id DESC LIMIT 20"); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getPhotos(Connection sql, int visitor_uid, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND (privacy>0 OR user_id=?) AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, before); + stmt.setInt(2, visitor_uid); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE (privacy>0 OR user_id=?) AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, visitor_uid); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getSearch(Connection sql, Connection sqlSearch, String search, int before) { + ArrayList<Integer> mids0 = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE MATCH(?) AND id<? ORDER BY id DESC LIMIT 25"); + stmt.setString(1, search); + stmt.setInt(2, before); + } else { + stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE MATCH(?) ORDER BY id DESC LIMIT 25"); + stmt.setString(1, search); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids0.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + ArrayList<Integer> mids = new ArrayList<Integer>(20); + if (mids0.size() > 0) { + try { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArrayInt2String(mids0) + ") AND privacy>0 ORDER BY message_id DESC LIMIT 20"); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + } + + return mids; + } + + public static ArrayList<Integer> getUserBlog(Connection sql, int UID, int privacy, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id<? AND privacy>=" + privacy + " ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>=" + privacy + " ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getUserTag(Connection sql, int UID, int TID, int privacy, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + 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.user_id=? AND messages_tags.tag_id=? AND messages.message_id<? AND messages.privacy>=" + privacy + " ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, TID); + stmt.setInt(3, before); + } else { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages_tags INNER JOIN messages USING(message_id) WHERE messages.user_id=? AND messages_tags.tag_id=? AND messages.privacy>=" + privacy + " ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, TID); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getUserRecommendations(Connection sql, int UID, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM favorites WHERE user_id=? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getUserPhotos(Connection sql, int UID, int privacy, int before) { + ArrayList<Integer> mids = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id<? AND privacy>=" + privacy + " AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND privacy>=" + privacy + " AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, UID); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + return mids; + } + + public static ArrayList<Integer> getUserSearch(Connection sql, Connection sqlSearch, int UID, String search, int privacy, int before) { + ArrayList<Integer> mids0 = new ArrayList<Integer>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + if (before > 0) { + stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE user_id=? AND MATCH(?) AND id<? ORDER BY id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setString(2, search); + stmt.setInt(3, before); + } else { + stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE user_id=? AND MATCH(?) ORDER BY id DESC LIMIT 20"); + stmt.setInt(1, UID); + stmt.setString(2, search); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids0.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + ArrayList<Integer> mids = new ArrayList<Integer>(20); + if (mids0.size() > 0) { + try { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArrayInt2String(mids0) + ") AND privacy>=" + privacy + " ORDER BY message_id DESC"); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + } + + return mids; + } + + public static ArrayList<com.juick.Message> getMessages(Connection sql, ArrayList<Integer> mids) { + ArrayList<com.juick.Message> msgs = new ArrayList<com.juick.Message>(20); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT STRAIGHT_JOIN messages.message_id,messages.user_id,users.nick,messages_txt.tags,messages.readonly,messages.privacy,messages_txt.txt,TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),DATE_FORMAT(messages.ts,'%Y-%m-%d %H:%i:%s'),messages.replies,messages_txt.repliesby,messages.attach,messages.lat,messages.lon,messages.likes FROM (messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id) INNER JOIN users ON messages.user_id=users.id WHERE messages.message_id IN (" + Utils.convertArrayInt2String(mids) + ") ORDER BY messages.message_id DESC"); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.Message msg = new com.juick.Message(); + msg.User = new com.juick.User(); + + msg.MID = rs.getInt(1); + msg.User.UID = rs.getInt(2); + msg.User.UName = rs.getString(3); + if (rs.getString(4) != null) { + msg.parseTags(rs.getString(4)); + } + msg.ReadOnly = rs.getInt(5) == 1; + msg.Privacy = rs.getInt(6); + msg.FriendsOnly = msg.Privacy < 0; + msg.Text = rs.getString(7); + msg.TimeAgo = rs.getInt(8); + msg.TimestampString = rs.getString(9); + msg.Replies = rs.getInt(10); + msg.RepliesBy = rs.getString(11); + msg.AttachmentType = rs.getString(12); + if (rs.getDouble(13) != 0) { + msg.Place = new com.juick.Place(); + msg.Place.lat = rs.getDouble(13); + msg.Place.lon = rs.getDouble(14); + } + msg.Likes = rs.getInt(15); + + msgs.add(msg); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return msgs; + } + + public static ArrayList<com.juick.Message> getReplies(Connection sql, int mid) { + ArrayList<com.juick.Message> replies = new ArrayList<com.juick.Message>(); + + PreparedStatement stmt = null; + ResultSet rs = null; + try { + stmt = sql.prepareStatement("SELECT replies.reply_id,replies.replyto,replies.user_id,users.nick,users.banned,replies.txt,TIMESTAMPDIFF(MINUTE,replies.ts,NOW()),DATE_FORMAT(replies.ts,'%Y-%m-%d %H:%i:%s'),replies.attach FROM replies INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? ORDER BY replies.reply_id ASC"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + com.juick.Message msg = new com.juick.Message(); + msg.MID = mid; + msg.RID = rs.getInt(1); + msg.ReplyTo = rs.getInt(2); + msg.User = new com.juick.User(); + msg.User.UID = rs.getInt(3); + msg.User.UName = rs.getString(4); + msg.User.Banned = rs.getBoolean(5); + msg.Text = rs.getString(6); + msg.TimeAgo = rs.getInt(7); + msg.TimestampString = rs.getString(8); + msg.AttachmentType = rs.getString(9); + + replies.add(msg); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + return replies; + } + + public static boolean setMessagePopular(Connection sql, int mid, int popular) { + boolean ret = false; + + PreparedStatement stmt = null; + try { + if (popular == -2) { + stmt = sql.prepareStatement("UPDATE messages SET hidden=1 WHERE message_id=?"); + stmt.setInt(1, mid); + } else if (popular == -1) { + stmt = sql.prepareStatement("UPDATE messages SET popular=0 WHERE message_id=?"); + stmt.setInt(1, mid); + } else { + stmt = sql.prepareStatement("UPDATE messages SET popular=? WHERE message_id=?"); + stmt.setInt(1, popular); + stmt.setInt(2, mid); + } + ret = stmt.executeUpdate() > 0; + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + + if (popular == -1) { + try { + stmt = sql.prepareStatement("INSERT INTO top_ignore_messages VALUES (?)"); + stmt.setInt(1, mid); + stmt.executeUpdate(); + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(null, stmt); + } + } + + 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; + } +} |