From 0c451fd02654ffbd56b6422affe8da38ed810644 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 30 Nov 2015 20:31:53 +0300 Subject: fix previous query --- src/main/java/com/juick/server/MessagesQueries.java | 6 ++---- 1 file changed, 2 insertions(+), 4 deletions(-) diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index 7501e869..82fef672 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -719,14 +719,12 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 OR user_id=?) AND attach IS NOT NULL AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 OR user_id=?) AND attach IS NOT NULL AND user_id NOT IN (SELECT id from users WHERE banned=1) 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 (privacy>0 OR user_id=?) AND attach IS NOT NULL AND user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE (privacy>0 OR user_id=?) AND attach IS NOT NULL AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, visitor_uid); - stmt.setInt(2, visitor_uid); } rs = stmt.executeQuery(); rs.beforeFirst(); -- cgit v1.2.3 From 0a00a5e5625ea0241c0effb3ed6f715fd737a04d Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Tue, 1 Dec 2015 12:34:32 +0300 Subject: Revert "do not show banned users in search" This reverts commit 23666546ca2c0f1ee492b1bea8c0ea83c8c04a2b. --- src/main/java/com/juick/server/MessagesQueries.java | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/src/main/java/com/juick/server/MessagesQueries.java b/src/main/java/com/juick/server/MessagesQueries.java index c13aec2b..82fef672 100644 --- a/src/main/java/com/juick/server/MessagesQueries.java +++ b/src/main/java/com/juick/server/MessagesQueries.java @@ -747,11 +747,11 @@ public class MessagesQueries { try { if (before > 0) { - stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE MATCH(?) AND id mids = new ArrayList(20); if (mids0.size() > 0) { try { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id IN (" + Utils.convertArrayInt2String(mids0) + ") AND privacy>0 AND user_id NOT IN (SELECT id from users WHERE banned=1) ORDER BY message_id DESC LIMIT 20"); + 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()) { -- cgit v1.2.3 From 79e48598873f6a2b4ec1aa850e310a689d862afa Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Tue, 1 Dec 2015 16:27:32 +0300 Subject: link twitter account query --- src/main/java/com/juick/server/UserQueries.java | 29 +++++++++++++++++++++++++ 1 file changed, 29 insertions(+) diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index d1313715..470f3c8e 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -17,6 +17,8 @@ */ package com.juick.server; +import com.juick.User; + import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; @@ -574,6 +576,33 @@ public class UserQueries { return users; } + public static boolean linkTwitterAccount(Connection sql, User user, String accessToken, + String accessTokenSecret, String screenName) { + try { + PreparedStatement stmt = + sql.prepareStatement("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + + "VALUES (?,?,?,?)" + + " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?;"); + stmt.setInt(1, user.UID); + stmt.setString(2, accessToken); + stmt.setString(3, accessTokenSecret); + stmt.setString(4, screenName); + stmt.setString(5, accessToken); + stmt.setString(6, accessTokenSecret); + stmt.setString(7, screenName); + if (stmt.execute()) { + PreparedStatement stmt2 = + sql.prepareStatement("INSERT INTO subscr_users(user_id,suser_id,jid) " + + "VALUES (?,1741,'juick\\@twitter.juick.com')"); + stmt2.setInt(1, user.UID); + return stmt2.execute(); + } + } catch (SQLException e) { + + } + return false; + } + public static int getStatsIRead(Connection sql, int uid) { return SQLHelpers.getInt(sql, "SELECT COUNT(*) FROM subscr_users WHERE suser_id=?", uid, 0); } -- cgit v1.2.3 From 512d0b353f28e241937be3fa146617ba8ff97c9c Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 16 Dec 2015 13:24:29 +0300 Subject: getUsersByHash: fetch users.banned field --- src/main/java/com/juick/server/UserQueries.java | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 470f3c8e..95944ce0 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -281,13 +281,14 @@ public class UserQueries { PreparedStatement stmt = null; ResultSet rs = null; try { - stmt = sql.prepareStatement("SELECT logins.user_id,users.nick FROM logins INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?"); + stmt = sql.prepareStatement("SELECT logins.user_id,users.nick, users.banned FROM logins INNER JOIN users ON logins.user_id=users.id WHERE logins.hash=?"); stmt.setString(1, hash); rs = stmt.executeQuery(); if (rs.first()) { user = new com.juick.User(); user.UID = rs.getInt(1); user.UName = rs.getString(2); + user.Banned = rs.getBoolean(3); user.AuthHash = hash; } } catch (SQLException e) { -- cgit v1.2.3 From 03316e2af6989e026318e6860f27d26e6e805d4d Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Tue, 22 Dec 2015 16:40:00 +0300 Subject: initial logging --- src/main/java/com/juick/server/UserQueries.java | 7 +++++-- 1 file changed, 5 insertions(+), 2 deletions(-) diff --git a/src/main/java/com/juick/server/UserQueries.java b/src/main/java/com/juick/server/UserQueries.java index 95944ce0..7e3a212b 100644 --- a/src/main/java/com/juick/server/UserQueries.java +++ b/src/main/java/com/juick/server/UserQueries.java @@ -27,6 +27,7 @@ import java.sql.Statement; import java.util.ArrayList; import java.util.Random; import java.util.UUID; +import java.util.logging.Logger; /** * @@ -36,6 +37,8 @@ public class UserQueries { static final String ABCDEF = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; + private static Logger logger = Logger.getLogger(UserQueries.class.getName()); + public static String getSignUpHashByJID(Connection sql, String jid) { String hash = SQLHelpers.getString(sql, "SELECT loginhash FROM jids WHERE jid=? AND user_id IS NULL", jid); @@ -583,7 +586,7 @@ public class UserQueries { PreparedStatement stmt = sql.prepareStatement("INSERT INTO twitter(user_id,access_token,access_token_secret,uname) " + "VALUES (?,?,?,?)" + - " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?;"); + " ON DUPLICATE KEY UPDATE access_token=?,access_token_secret=?,uname=?"); stmt.setInt(1, user.UID); stmt.setString(2, accessToken); stmt.setString(3, accessTokenSecret); @@ -599,7 +602,7 @@ public class UserQueries { return stmt2.execute(); } } catch (SQLException e) { - + logger.throwing(UserQueries.class.getName(), "linkTwitterAccount", e); } return false; } -- cgit v1.2.3