From 118464cfdabdad2b943e959e14ceb3012642a301 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 18 Jan 2023 11:39:05 +0300 Subject: Drop unused `useroptions` table --- .../java/com/juick/service/UserServiceImpl.java | 140 ++++++--------------- 1 file changed, 38 insertions(+), 102 deletions(-) (limited to 'src/main/java/com/juick/service/UserServiceImpl.java') diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index 6114bf78..99e56229 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -1,5 +1,5 @@ /* - * Copyright (C) 2008-2020, Juick + * Copyright (C) 2008-2023, Juick * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as @@ -19,6 +19,7 @@ package com.juick.service; import com.juick.model.*; import com.juick.util.UsernameTakenException; +import jakarta.annotation.Nonnull; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.RandomStringUtils; import org.apache.commons.lang3.StringUtils; @@ -30,12 +31,10 @@ import org.springframework.dao.DataIntegrityViolationException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; -import org.springframework.jdbc.support.GeneratedKeyHolder; -import org.springframework.jdbc.support.KeyHolder; +import org.springframework.jdbc.core.simple.SimpleJdbcInsert; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; -import javax.annotation.Nonnull; import java.net.URI; import java.sql.*; import java.time.Instant; @@ -53,7 +52,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Value("${juick.admin_users:}") List adminUsers; - private class UserMapper implements RowMapper { + private static class UserMapper implements RowMapper { @Override public User mapRow(@Nonnull ResultSet rs, int rowNum) throws SQLException { User user = new User(); @@ -71,20 +70,6 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { } } - @Transactional - @Override - public String getSignUpHashByJID(final String jid) { - List list = getJdbcTemplate().queryForList( - "SELECT loginhash FROM jids WHERE jid = ? AND user_id IS NULL", String.class, jid); - - if (list.isEmpty()) { - String hash = UUID.randomUUID().toString(); - getJdbcTemplate().update("INSERT INTO jids(jid, loginhash) VALUES (?, ?)", jid, hash); - return hash; - } - return list.get(0); - } - @Transactional @Override public String getSignUpHashByTelegramID(final Long telegramId, final String username) { @@ -105,40 +90,34 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional @Override public Optional createUser(final String username, final String password) throws UsernameTakenException { - KeyHolder holder = new GeneratedKeyHolder(); + var userInsert = new SimpleJdbcInsert(getJdbcTemplate()) + .withTableName("users") + .usingColumns("nick", "passw") + .usingGeneratedKeyColumns("id"); + var params = new MapSqlParameterSource(); + params.addValue("nick", username); + params.addValue("passw", password); try { - getJdbcTemplate().update( - con -> { - PreparedStatement stmt = con.prepareStatement( - "INSERT INTO users(nick,passw) VALUES (?,?)", - Statement.RETURN_GENERATED_KEYS); - stmt.setString(1, username); - stmt.setString(2, password); - return stmt; - }, - holder); + var uid = userInsert.executeAndReturnKey(params).intValue(); + if (uid > 0) { + getJdbcTemplate().update("INSERT INTO subscr_users(user_id, suser_id) VALUES (2, ?)", uid); + return getUserByUID(uid); + } } catch (DataIntegrityViolationException e) { throw new UsernameTakenException(); } - - int uid = holder.getKeys().size() > 1 ? ((Number)holder.getKeys().get("id")).intValue() : holder.getKey().intValue(); - - getJdbcTemplate().update("INSERT INTO useroptions(user_id) VALUES (?)", uid); - getJdbcTemplate().update("INSERT INTO subscr_users(user_id, suser_id) VALUES (2, ?)", uid); - - return getUserByUID(uid); + return Optional.empty(); } @Transactional(readOnly = true) @Override public Optional getUserByUID(final int uid) { - List list = getJdbcTemplate().query( - "SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, " + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + - "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + - "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + - "LEFT JOIN emails e ON e.user_id = u.id WHERE u.id = ?", new UserMapper(), uid); - + var list = getJdbcTemplate().query(""" + SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, + COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified + FROM users u LEFT JOIN facebook f ON f.user_id = u.id + LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id + LEFT JOIN emails e ON e.user_id = u.id WHERE u.id = ?""", new UserMapper(), uid); return list.isEmpty() ? Optional.empty() : Optional.of(list.get(0)); } @@ -147,14 +126,13 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Override public User getUserByName(final String username) { if (StringUtils.isNotBlank(username)) { - List list = getJdbcTemplate().query( - "SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, " + - "COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified " + - "FROM users u LEFT JOIN facebook f ON f.user_id = u.id " + - "LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id " + - "LEFT JOIN emails e ON e.user_id = u.id " + - "WHERE u.nick = ?", new UserMapper(), username); - + var list = getJdbcTemplate().query(""" + SELECT DISTINCT u.id, u.nick, u.passw, u.banned, u.last_seen, + COALESCE(f.fb_id, vk.vk_id, t.tg_id, e.user_id, 0) AS verified + FROM users u LEFT JOIN facebook f ON f.user_id = u.id + LEFT JOIN vk ON u.id = vk.user_id LEFT JOIN telegram t ON u.id = t.user_id + LEFT JOIN emails e ON e.user_id = u.id + WHERE u.nick = ?""", new UserMapper(), username); if (!list.isEmpty()) return list.get(0); } @@ -269,6 +247,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { } @Transactional(readOnly = true) + @Nonnull @Override public User getUserByHash(final String hash) { if (StringUtils.isNotBlank(hash)) { @@ -337,27 +316,6 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { getJdbcTemplate().update("UPDATE users SET passw = ? WHERE id = ?", newPassword, user.getUid()) > 0; } - @Transactional(readOnly = true) - @Override - public int getUserOptionInt(final int uid, final String option, final int defaultValue) { - if (StringUtils.isBlank(option)) - return defaultValue; - - List list = getJdbcTemplate().queryForList( - "SELECT " + option + " FROM useroptions WHERE user_id = ?", Integer.class, uid); - - return list.isEmpty() ? defaultValue : list.get(0); - } - - @Transactional - @Override - public int setUserOptionInt(final int uid, final String option, final int value) { - if (StringUtils.isBlank(option)) - return 0; - - return getJdbcTemplate().update("UPDATE useroptions SET " + option + "= ? WHERE user_id = ?", value, uid); - } - @Transactional(readOnly = true) @Override public User getUserInfo(final User user) { @@ -399,17 +357,6 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { } } - @Transactional(readOnly = true) - @Override - public boolean getCanMedia(final int uid) { - List list = getJdbcTemplate().queryForList( - "SELECT users.lastphoto - UNIX_TIMESTAMP() FROM users WHERE id = ?", - Integer.class, - uid); - - return !list.isEmpty() && list.get(0) < 3600; - } - @Transactional(readOnly = true) @Override public boolean isInWL(final int uid, final int check) { @@ -442,7 +389,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional(readOnly = true) @Override public boolean isReplyToBL(final User user, final Message reply) { - return getNamedParameterJdbcTemplate().queryForObject("WITH RECURSIVE banned(reply_id, user_id) AS (" + + var replies = getNamedParameterJdbcTemplate().queryForList("WITH RECURSIVE banned(reply_id, user_id) AS (" + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + @@ -459,7 +406,8 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { new MapSqlParameterSource("uid", user.getUid()) .addValue("mid", reply.getMid()) .addValue("rid", reply.getRid()), - Integer.class) > 0; + Integer.class); + return !replies.isEmpty() && replies.get(0) > 0; } @Transactional(readOnly = true) @@ -544,16 +492,6 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { uid); } - @Transactional(readOnly = true) - @Override - public Integer getUserRecommendations(User user) { - try { - return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM favorites WHERE user_id=?", Integer.class, user.getUid()); - } catch (EmptyResultDataAccessException e) { - return 0; - } - } - @Transactional(readOnly = true) @Override public List getUserBLUsers(final int uid) { @@ -838,11 +776,9 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional(readOnly = true) @Override public int getUIDbyVKID(long vkID) { - try { - return getJdbcTemplate().queryForObject("SELECT user_id FROM vk WHERE vk_id=? AND user_id IS NOT NULL", Integer.class, vkID); - } catch (EmptyResultDataAccessException e) { - return 0; - } + var users = getJdbcTemplate().queryForList(""" + SELECT user_id FROM vk WHERE vk_id=? AND user_id IS NOT NULL""", Integer.class, vkID); + return users.isEmpty() ? 0 : users.get(0); } @Transactional @@ -928,7 +864,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { public boolean canDeleteTelegramUser(User user) { return getEmails(user).size() > 0 || getFbCrossPostStatus(user.getUid()).isConnected() || getVkTokens(user.getUid()).isPresent(); } - private class TokenMapper implements RowMapper { + private static class TokenMapper implements RowMapper { @Override public ExternalToken mapRow(ResultSet rs, int rowNum) throws SQLException { -- cgit v1.2.3