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 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