From 75a631d48bc3e3fea8d35cddbe0e2c639c252796 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Sun, 12 Feb 2023 05:10:54 +0300
Subject: Drop `users_subscr` table

---
 src/main/java/com/juick/service/UserServiceImpl.java       | 10 +++++-----
 .../resources/db/migration/V1.45__drop_users_subscr.sql    |  1 +
 src/main/resources/schema-h2.sql                           |  5 -----
 src/main/resources/schema-mysql.sql                        | 14 --------------
 src/main/resources/schema-sqlite.sql                       |  5 -----
 src/main/resources/schema-sqlserver.sql                    |  4 ----
 6 files changed, 6 insertions(+), 33 deletions(-)
 create mode 100644 src/main/resources/db/migration/V1.45__drop_users_subscr.sql

diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java
index 57fcd399..932be19c 100644
--- a/src/main/java/com/juick/service/UserServiceImpl.java
+++ b/src/main/java/com/juick/service/UserServiceImpl.java
@@ -439,11 +439,11 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
     @Transactional(readOnly = true)
     @Override
     public List<User> getUserReadLeastPopular(final int uid, final int cnt) {
-        return getJdbcTemplate().query(
-                "SELECT users.id,users.nick FROM (subscr_users " +
-                        "INNER JOIN users_subscr ON (subscr_users.suser_id=? " +
-                        "AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users " +
-                        "ON subscr_users.user_id=users.id ORDER BY cnt " + limit(cnt),
+        return getJdbcTemplate().query("""
+                        SELECT users.id,users.nick, (select count(*) from subscr_users where user_id=users.id) cnt
+                        FROM subscr_users INNER JOIN users
+                        ON subscr_users.user_id=users.id WHERE subscr_users.suser_id=? ORDER by cnt
+                        """ + limit(cnt),
                 (rs, num) -> {
                     User u = new User();
                     u.setUid(rs.getInt(1));
diff --git a/src/main/resources/db/migration/V1.45__drop_users_subscr.sql b/src/main/resources/db/migration/V1.45__drop_users_subscr.sql
new file mode 100644
index 00000000..ed60a4bb
--- /dev/null
+++ b/src/main/resources/db/migration/V1.45__drop_users_subscr.sql
@@ -0,0 +1 @@
+DROP TABLE users_subscr
diff --git a/src/main/resources/schema-h2.sql b/src/main/resources/schema-h2.sql
index 08a63839..ea979df1 100644
--- a/src/main/resources/schema-h2.sql
+++ b/src/main/resources/schema-h2.sql
@@ -216,11 +216,6 @@ CREATE MEMORY TABLE "PUBLIC"."TWITTER"(
     "CROSSPOST" TINYINT DEFAULT '1' NOT NULL
 );
 ALTER TABLE "PUBLIC"."TWITTER" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_F" PRIMARY KEY("USER_ID");
-CREATE MEMORY TABLE "PUBLIC"."USERS_SUBSCR"(
-    "USER_ID" INTEGER NOT NULL,
-    "CNT" SMALLINT DEFAULT '0' NOT NULL
-);
-ALTER TABLE "PUBLIC"."USERS_SUBSCR" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_BB" PRIMARY KEY("USER_ID");
 CREATE MEMORY TABLE "PUBLIC"."USERSINFO"(
     "USER_ID" INTEGER NOT NULL,
     "JID" CHARACTER(32) DEFAULT NULL,
diff --git a/src/main/resources/schema-mysql.sql b/src/main/resources/schema-mysql.sql
index 581b1c1c..e458203f 100644
--- a/src/main/resources/schema-mysql.sql
+++ b/src/main/resources/schema-mysql.sql
@@ -564,20 +564,6 @@ CREATE TABLE `users` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
---
--- Table structure for table `users_subscr`
---
-
-DROP TABLE IF EXISTS `users_subscr`;
-/*!40101 SET @saved_cs_client     = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `users_subscr` (
-  `user_id` int(10) unsigned NOT NULL,
-  `cnt` smallint(5) unsigned NOT NULL DEFAULT 0,
-  PRIMARY KEY (`user_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
-/*!40101 SET character_set_client = @saved_cs_client */;
-
 --
 -- Table structure for table `usersinfo`
 --
diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index ea9ffe36..0e298cdf 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -229,11 +229,6 @@ CREATE TABLE users (
     last_seen timestamp with time zone,
     UNIQUE(nick)
 );
-CREATE TABLE users_subscr (
-    user_id INTEGER NOT NULL,
-    cnt smallint DEFAULT (0) NOT NULL,
-    FOREIGN KEY (user_id) REFERENCES users(id)
-);
 CREATE TABLE usersinfo (
     user_id INTEGER NOT NULL,
     jid character varying(64),
diff --git a/src/main/resources/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql
index a8d04cdf..4a683189 100644
--- a/src/main/resources/schema-sqlserver.sql
+++ b/src/main/resources/schema-sqlserver.sql
@@ -207,10 +207,6 @@ CREATE TABLE subscr_users (
     FOREIGN KEY (user_id) REFERENCES users(id),
     FOREIGN KEY (suser_id) REFERENCES users(id)
 );
-CREATE TABLE users_subscr (
-    user_id bigint NOT NULL,
-    cnt INTEGER NOT NULL DEFAULT 0
-);
 CREATE TABLE tags_ignore (
     tag_id integer NOT NULL,
     FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
-- 
cgit v1.2.3