aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/main/java/com/juick/TopManager.java19
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java9
-rw-r--r--src/main/resources/data-h2.sql1
-rw-r--r--src/main/resources/data-mysql.sql2
-rw-r--r--src/main/resources/data-sqlite.sql21
-rw-r--r--src/main/resources/data-sqlserver.sql3
-rw-r--r--src/main/resources/db/migration/V1.42__drop_top_ignore.sql3
-rw-r--r--src/main/resources/schema-h2.sql11
-rw-r--r--src/main/resources/schema-mysql.sql38
-rw-r--r--src/main/resources/schema-sqlite.sql12
-rw-r--r--src/main/resources/schema-sqlserver.sql12
-rw-r--r--src/test/java/com/juick/server/tests/ServerTests.java9
12 files changed, 32 insertions, 108 deletions
diff --git a/src/main/java/com/juick/TopManager.java b/src/main/java/com/juick/TopManager.java
index d7917fbc..13e91b34 100644
--- a/src/main/java/com/juick/TopManager.java
+++ b/src/main/java/com/juick/TopManager.java
@@ -17,22 +17,21 @@
package com.juick;
-import com.juick.model.CommandResult;
-import com.juick.model.Message;
-import com.juick.model.Tag;
-import com.juick.model.User;
-import com.juick.service.MessagesService;
+import java.net.URI;
+import java.util.List;
+
+import javax.inject.Inject;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Scheduled;
-import javax.inject.Inject;
-
-import java.net.URI;
-import java.util.List;
-import java.util.stream.Collectors;
+import com.juick.model.CommandResult;
+import com.juick.model.Message;
+import com.juick.model.Tag;
+import com.juick.model.User;
+import com.juick.service.MessagesService;
public class TopManager {
private static final Logger logger = LoggerFactory.getLogger(TopManager.class);
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 3ddfcd00..eb1acd59 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -1044,27 +1044,26 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Override
public List<Integer> getPopularCandidates() {
var beforeTime = Instant.now().minus(2, ChronoUnit.HOURS);
- var nsfw = tagService.getTag("NSFW", true);
var sql = """
SELECT replies.message_id FROM replies
INNER JOIN messages ON replies.message_id = messages.message_id
LEFT JOIN favorites ON favorites.message_id = messages.message_id
LEFT JOIN messages_tags ON messages_tags.message_id = messages.message_id
- WHERE COALESCE(messages_tags.tag_id, 0) != 2
- AND COALESCE(messages_tags.tag_id, 0) != :nsfw_id AND replies.ts > :before
+ LEFT JOIN tags ON messages_tags.tag_id=tags.tag_id
+ WHERE COALESCE(tags.notop, 0)=0 AND replies.ts > :before
AND NOT EXISTS (SELECT 1 FROM favorites WHERE message_id = messages.message_id
AND user_id = 2) GROUP BY replies.message_id HAVING COUNT(DISTINCT(replies.user_id)) > 5
UNION ALL SELECT favorites.message_id FROM favorites
INNER JOIN messages ON messages.message_id = favorites.message_id
LEFT JOIN messages_tags ON messages_tags.message_id = messages.message_id
- WHERE COALESCE(messages_tags.tag_id, 0) != 2
+ LEFT JOIN tags ON messages_tags.tag_id=tags.tag_id
+ WHERE COALESCE(tags.notop, 0)=0
AND favorites.ts > :before
AND NOT EXISTS (SELECT 1 FROM favorites
WHERE message_id = messages.message_id AND user_id = 2)
GROUP BY favorites.message_id HAVING COUNT(DISTINCT favorites.user_id) > 2
""";
return getNamedParameterJdbcTemplate().queryForList(sql, new MapSqlParameterSource()
- .addValue("nsfw_id", nsfw.getId())
.addValue("before", toDateTime(beforeTime.atOffset(ZoneOffset.UTC)),
dateTimeType()),
Integer.class);
diff --git a/src/main/resources/data-h2.sql b/src/main/resources/data-h2.sql
index bb1203a4..818575e9 100644
--- a/src/main/resources/data-h2.sql
+++ b/src/main/resources/data-h2.sql
@@ -1,7 +1,6 @@
INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password');
INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password');
INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password');
-INSERT INTO tags(tag_id, name) VALUES(2, 'juick');
INSERT INTO reactions (like_id, description) VALUES (1, 'like');
INSERT INTO reactions (like_id, description) VALUES (2, 'love');
INSERT INTO reactions (like_id, description) VALUES (3, 'lol');
diff --git a/src/main/resources/data-mysql.sql b/src/main/resources/data-mysql.sql
index a5db173a..d7c3d0f1 100644
--- a/src/main/resources/data-mysql.sql
+++ b/src/main/resources/data-mysql.sql
@@ -1,8 +1,6 @@
INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password');
INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password');
INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password');
-INSERT INTO tags(tag_id, name) VALUES(2, 'juick');
-ALTER TABLE tags AUTO_INCREMENT = 10;
ALTER TABLE users AUTO_INCREMENT = 10;
INSERT INTO reactions (like_id, description) VALUES (1, 'like');
INSERT INTO reactions (like_id, description) VALUES (2, 'love');
diff --git a/src/main/resources/data-sqlite.sql b/src/main/resources/data-sqlite.sql
index d34cb832..ae7e61d7 100644
--- a/src/main/resources/data-sqlite.sql
+++ b/src/main/resources/data-sqlite.sql
@@ -1,11 +1,10 @@
-INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password');;
-INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password');;
-INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password');;
-INSERT INTO tags(tag_id, name) VALUES(2, 'juick');;
-INSERT INTO reactions (like_id, description) VALUES (1, 'like');;
-INSERT INTO reactions (like_id, description) VALUES (2, 'love');;
-INSERT INTO reactions (like_id, description) VALUES (3, 'lol');;
-INSERT INTO reactions (like_id, description) VALUES (4, 'hmm');;
-INSERT INTO reactions (like_id, description) VALUES (5, 'angry');;
-INSERT INTO reactions (like_id, description) VALUES (6, 'uhblya');;
-INSERT INTO reactions (like_id, description) VALUES (7, 'ugh') \ No newline at end of file
+INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password');
+INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password');
+INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password');
+INSERT INTO reactions (like_id, description) VALUES (1, 'like');
+INSERT INTO reactions (like_id, description) VALUES (2, 'love');
+INSERT INTO reactions (like_id, description) VALUES (3, 'lol');
+INSERT INTO reactions (like_id, description) VALUES (4, 'hmm');
+INSERT INTO reactions (like_id, description) VALUES (5, 'angry');
+INSERT INTO reactions (like_id, description) VALUES (6, 'uhblya');
+INSERT INTO reactions (like_id, description) VALUES (7, 'ugh')
diff --git a/src/main/resources/data-sqlserver.sql b/src/main/resources/data-sqlserver.sql
index f5844a52..d60c7505 100644
--- a/src/main/resources/data-sqlserver.sql
+++ b/src/main/resources/data-sqlserver.sql
@@ -3,9 +3,6 @@ INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password');
INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password');
INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password');
set identity_insert users OFF;
-set identity_insert tags ON;
-INSERT INTO tags(tag_id, name) VALUES(2, 'juick');
-set identity_insert tags OFF;
INSERT INTO reactions (like_id, description) VALUES (1, 'like');
INSERT INTO reactions (like_id, description) VALUES (2, 'love');
INSERT INTO reactions (like_id, description) VALUES (3, 'lol');
diff --git a/src/main/resources/db/migration/V1.42__drop_top_ignore.sql b/src/main/resources/db/migration/V1.42__drop_top_ignore.sql
new file mode 100644
index 00000000..5465e49b
--- /dev/null
+++ b/src/main/resources/db/migration/V1.42__drop_top_ignore.sql
@@ -0,0 +1,3 @@
+DROP TABLE top_ignore_tags;
+DROP TABLE top_ignore_messages;
+DROP TABLE top_ignore_users
diff --git a/src/main/resources/schema-h2.sql b/src/main/resources/schema-h2.sql
index c2ecd4b7..58ce9219 100644
--- a/src/main/resources/schema-h2.sql
+++ b/src/main/resources/schema-h2.sql
@@ -201,17 +201,6 @@ CREATE MEMORY TABLE "PUBLIC"."TELEGRAM"(
"TS" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
"LOGINHASH" CHARACTER(36) DEFAULT NULL
);
-CREATE MEMORY TABLE "PUBLIC"."TOP_IGNORE_MESSAGES"(
- "MESSAGE_ID" INTEGER NOT NULL
-);
-CREATE MEMORY TABLE "PUBLIC"."TOP_IGNORE_TAGS"(
- "TAG_ID" INTEGER NOT NULL
-);
-ALTER TABLE "PUBLIC"."TOP_IGNORE_TAGS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_7" PRIMARY KEY("TAG_ID");
-CREATE MEMORY TABLE "PUBLIC"."TOP_IGNORE_USERS"(
- "USER_ID" INTEGER NOT NULL
-);
-ALTER TABLE "PUBLIC"."TOP_IGNORE_USERS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_B2" PRIMARY KEY("USER_ID");
CREATE MEMORY TABLE "PUBLIC"."FACEBOOK"(
"USER_ID" INTEGER DEFAULT NULL,
"FB_ID" BIGINT,
diff --git a/src/main/resources/schema-mysql.sql b/src/main/resources/schema-mysql.sql
index 58192261..00261574 100644
--- a/src/main/resources/schema-mysql.sql
+++ b/src/main/resources/schema-mysql.sql
@@ -536,44 +536,6 @@ CREATE TABLE `telegram` (
/*!40101 SET character_set_client = @saved_cs_client */;
--
--- Table structure for table `top_ignore_messages`
---
-
-DROP TABLE IF EXISTS `top_ignore_messages`;
-/*!40101 SET @saved_cs_client = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `top_ignore_messages` (
- `message_id` int(10) unsigned NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
-/*!40101 SET character_set_client = @saved_cs_client */;
-
---
--- Table structure for table `top_ignore_tags`
---
-
-DROP TABLE IF EXISTS `top_ignore_tags`;
-/*!40101 SET @saved_cs_client = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `top_ignore_tags` (
- `tag_id` int(10) unsigned NOT NULL,
- PRIMARY KEY (`tag_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
-/*!40101 SET character_set_client = @saved_cs_client */;
-
---
--- Table structure for table `top_ignore_users`
---
-
-DROP TABLE IF EXISTS `top_ignore_users`;
-/*!40101 SET @saved_cs_client = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `top_ignore_users` (
- `user_id` int(10) unsigned NOT NULL,
- 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 `twitter`
--
diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql
index 9037aef0..bffd38d2 100644
--- a/src/main/resources/schema-sqlite.sql
+++ b/src/main/resources/schema-sqlite.sql
@@ -211,18 +211,6 @@ CREATE TABLE telegram (
loginhash character varying(36),
FOREIGN KEY (user_id) REFERENCES users(id)
);
-CREATE TABLE top_ignore_messages (
- message_id bigint NOT NULL,
- FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);
-CREATE TABLE top_ignore_tags (
- tag_id NOT NULL,
- FOREIGN KEY (tag_id) REFERENCES tag(tag_id)
-);
-CREATE TABLE top_ignore_users (
- user_id INTEGER NOT NULL,
- FOREIGN KEY (user_id) REFERENCES users(id)
-);
CREATE TABLE twitter (
user_id INTEGER NOT NULL,
access_token character varying(64) NOT NULL,
diff --git a/src/main/resources/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql
index aafc159d..c28c318e 100644
--- a/src/main/resources/schema-sqlserver.sql
+++ b/src/main/resources/schema-sqlserver.sql
@@ -227,18 +227,6 @@ CREATE TABLE telegram (
loginhash character varying(36),
FOREIGN KEY (user_id) REFERENCES users(id)
);
-CREATE TABLE top_ignore_messages (
- message_id integer NOT NULL,
- FOREIGN KEY (message_id) REFERENCES messages(message_id)
-);
-CREATE TABLE top_ignore_tags (
- tag_id integer NOT NULL,
- FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
-);
-CREATE TABLE top_ignore_users (
- user_id bigint NOT NULL,
- FOREIGN KEY (user_id) REFERENCES users(id)
-);
CREATE TABLE twitter (
user_id bigint NOT NULL,
access_token character varying(64) NOT NULL,
diff --git a/src/test/java/com/juick/server/tests/ServerTests.java b/src/test/java/com/juick/server/tests/ServerTests.java
index 3c37e846..414ad2ab 100644
--- a/src/test/java/com/juick/server/tests/ServerTests.java
+++ b/src/test/java/com/juick/server/tests/ServerTests.java
@@ -707,8 +707,9 @@ public class ServerTests {
List<Integer> topCandidates = messagesService.getPopularCandidates();
assertThat(topCandidates.size(), is(1));
assertThat(topCandidates.get(0), is(topmid));
- Tag juickTag = tagService.getTag("juick", false);
- assertThat(juickTag.getId(), is(2));
+ Tag juickTag = tagService.getTag("juick", true);
+ var rowsCount = jdbcTemplate.update("UPDATE tags SET notop=1 WHERE tag_id=?", juickTag.getId());
+ assertThat(rowsCount, is(1));
tagService.updateTags(topmid, Collections.singletonList(juickTag));
assertThat(messagesService.getPopularCandidates().isEmpty(), is(true));
tagService.updateTags(topmid, Collections.singletonList(juickTag));
@@ -722,7 +723,9 @@ public class ServerTests {
CommandResult resultUndo = commandsManager.commandRecommend(serviceUser, URI.create(""),
String.valueOf(topmid));
assertThat(messagesService.getPopularCandidates().isEmpty(), is(false));
- Tag nsfw = tagService.getTag("NSFW", true);
+ Tag nsfw = tagService.getTag("NOT_SAFE_FOR_WORK", true);
+ rowsCount = jdbcTemplate.update("UPDATE tags SET notop=1 WHERE tag_id=?", nsfw.getId());
+ assertThat(rowsCount, is(1));
tagService.updateTags(topmid, Collections.singletonList(nsfw));
assertThat(messagesService.getPopularCandidates().isEmpty(), is(true));
User recommender = userService.createUser("recommender2", "x").orElseThrow(IllegalStateException::new);
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-22 11:04:45 +0000