diff options
Diffstat (limited to 'src/com/juick/server/MessagesQueries.java')
| -rw-r--r-- | src/com/juick/server/MessagesQueries.java | 551 |
1 files changed, 551 insertions, 0 deletions
diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java new file mode 100644 index 00000000..3ff47ca7 --- /dev/null +++ b/src/com/juick/server/MessagesQueries.java @@ -0,0 +1,551 @@ +/* + * 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.util.ArrayList; + +/** + * + * @author Ugnich Anton + */ +public class MessagesQueries { + + 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,messages.ts,messages.readonly,messages.attach,messages.place_id,messages.lat,messages.lon 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.TimestampString = rs.getString(3); + msg.ReadOnly = rs.getBoolean(4); + msg.AttachmentType = rs.getString(5); + if (rs.getInt(6) > 0) { + msg.place = PlacesQueries.getPlace(sql, rs.getInt(6)); + } + if (rs.getDouble(7) != 0) { + msg.place = new com.juick.Place(); + msg.place.lat = rs.getDouble(7); + msg.place.lon = rs.getDouble(8); + } + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + if (msg == null) { + return null; + } + + try { + stmt = sql.prepareStatement("SELECT messages_txt.tags,messages_txt.txt FROM messages_txt WHERE message_id=?"); + stmt.setInt(1, mid); + rs = stmt.executeQuery(); + if (rs.first()) { + msg.parseTags(rs.getString(1)); + msg.Text = rs.getString(2); + } + } 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,replies.ts,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<Integer> getAll(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 messages.message_id FROM messages WHERE messages.message_id<? AND messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, before); + } else { + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages WHERE messages.privacy>0 ORDER BY messages.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 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_tags.tag_id=? AND messages.message_id<? AND messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, tid); + stmt.setInt(2, before); + } 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 ORDER BY messages.message_id DESC LIMIT 20"); + stmt.setInt(1, 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> getMyFeed(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 feed 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 feed 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> 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_access 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_access 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> getIncoming(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 IN (SELECT suser_id FROM subscr_users 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 IN (SELECT suser_id FROM subscr_users 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> 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 messages.message_id IN (SELECT message_id FROM favorites WHERE user_id=11574) ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND messages.message_id IN (SELECT message_id FROM favorites WHERE user_id=11574) 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 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 attach IS NOT NULL ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND attach IS NOT NULL 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> 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 20"); + 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 20"); + 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.convertArray2String(mids0) + ") AND privacy>0 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<Integer> getUserBlog(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 message_id<? AND privacy>0 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> getUserTag(Connection sql, int UID, int TID, 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>0 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>0 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 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>0 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>0 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 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.convertArray2String(mids0) + ") AND privacy>0 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; + } +} |