Drop unused `useroptions` table

1 files changed, 38 insertions, 102 deletions

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<String> adminUsers;
 
-    private class UserMapper implements RowMapper<User> {
+    private static class UserMapper implements RowMapper<User> {
         @Override
         public User mapRow(@Nonnull ResultSet rs, int rowNum) throws SQLException {
             User user = new User();
@@ -73,20 +72,6 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
 
     @Transactional
     @Override
-    public String getSignUpHashByJID(final String jid) {
-        List<String> 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) {
         List<String> list = getJdbcTemplate().queryForList(
                 "SELECT loginhash FROM telegram WHERE tg_id = ? AND user_id IS NULL",
@@ -105,40 +90,34 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Transactional
     @Override
     public Optional<User> 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<User> getUserByUID(final int uid) {
-        List<User> 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<User> 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)) {
@@ -339,27 +318,6 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
 
     @Transactional(readOnly = true)
     @Override
-    public int getUserOptionInt(final int uid, final String option, final int defaultValue) {
-        if (StringUtils.isBlank(option))
-            return defaultValue;
-
-        List<Integer> 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) {
         try {
             getJdbcTemplate().queryForObject(
@@ -401,17 +359,6 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
 
     @Transactional(readOnly = true)
     @Override
-    public boolean getCanMedia(final int uid) {
-        List<Integer> 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) {
         List<Integer> list = getJdbcTemplate().queryForList(
                 "SELECT 1 FROM wl_users WHERE user_id = ? AND wl_user_id = ?",
@@ -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)
@@ -546,16 +494,6 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
 
     @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<User> getUserBLUsers(final int uid) {
         return getJdbcTemplate().query("SELECT users.id,users.nick FROM users INNER JOIN bl_users " +
                         "ON(bl_users.bl_user_id=users.id) WHERE bl_users.user_id=? ORDER BY users.nick",
@@ -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<ExternalToken> {
+    private static class TokenMapper implements RowMapper<ExternalToken> {
 
         @Override
         public ExternalToken mapRow(ResultSet rs, int rowNum) throws SQLException {