aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/main/java/com/juick/service/UserServiceImpl.java10
-rw-r--r--src/main/resources/db/migration/V1.45__drop_users_subscr.sql1
-rw-r--r--src/main/resources/schema-h2.sql5
-rw-r--r--src/main/resources/schema-mysql.sql14
-rw-r--r--src/main/resources/schema-sqlite.sql5
-rw-r--r--src/main/resources/schema-sqlserver.sql4
6 files changed, 6 insertions, 33 deletions
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
@@ -565,20 +565,6 @@ CREATE TABLE `users` (
/*!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)