From 6cd35dbc723a64720c08a331ede9e605df11b8b4 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 28 Jan 2023 19:58:01 +0300 Subject: CI: enable sqlserver job --- .../com/juick/service/MessagesServiceImpl.java | 33 ++++++------ .../java/com/juick/service/TagServiceImpl.java | 2 +- .../resources/db/migration/V1.39__tags_noindex.sql | 7 +++ src/main/resources/schema-h2.sql | 4 +- src/main/resources/schema-mysql.sql | 2 +- src/main/resources/schema-sqlite.sql | 4 +- src/main/resources/schema-sqlserver.sql | 13 ++--- .../java/com/juick/server/tests/ServerTests.java | 60 +++++++++------------- 8 files changed, 60 insertions(+), 65 deletions(-) create mode 100644 src/main/resources/db/migration/V1.39__tags_noindex.sql (limited to 'src') diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 74e5db77..8149f37f 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -780,7 +780,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List getMessages(int uid, final List mids) { if (CollectionUtils.isNotEmpty(mids)) { - var query = omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE " + """ + var query = (omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE ") + """ banned(message_id, reply_id) AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids) AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) @@ -822,10 +822,10 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id WHERE messages.message_id IN (:ids) GROUP BY - messages.message_id, rid, replyto, messages.user_id, users.nick, usr_banned, messages.ts, + messages.message_id, messages.user_id, users.nick, users.banned, messages.ts, messages.readonly, messages.privacy, messages.attach, messages.hidden, - messages_txt.repliesby, messages_txt.txt, q, messages.updated, to_uid, to_name, updated_at, - m_user_uri, msg_reply_uri, html, subscr_messages.last_read_rid"""; + messages_txt.repliesby, messages_txt.txt, messages.updated, messages.replies, updated_at, + subscr_messages.last_read_rid"""; List msgs = getNamedParameterJdbcTemplate().query(query, new MapSqlParameterSource("ids", mids) .addValue("uid", uid), new MessageMapper()); @@ -923,22 +923,23 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ Instant messageDeletingWindow = now.minus(3, ChronoUnit.DAYS); Optional message = getMessage(uid, mid); if (message.isPresent()) { + if (message.get().getUser().getUid() != uid) return false; Instant ts = message.get().getUpdatedAt(); SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid) .addValue("archive_uid", archiveUser.getUid()); if (ts.compareTo(messageDeletingWindow) >= 0) { - if (getNamedParameterJdbcTemplate() - .update("DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", parameters) > 0) { - - getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", - parameters); - getNamedParameterJdbcTemplate().update("DELETE FROM replies WHERE message_id = :mid", parameters); - getNamedParameterJdbcTemplate().update("DELETE FROM subscr_messages WHERE message_id = :mid", - parameters); - getNamedParameterJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id = :mid", - parameters); - - return true; + // TODO: cascade delete + getNamedParameterJdbcTemplate().update("DELETE FROM replies WHERE message_id = :mid", parameters); + getNamedParameterJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id = :mid", + parameters); + getNamedParameterJdbcTemplate().update("DELETE FROM subscr_messages WHERE message_id = :mid", + parameters); + getNamedParameterJdbcTemplate().update("DELETE FROM messages_properties WHERE message_id = :mid", + parameters); + if (getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", + parameters) > 0) { + return getNamedParameterJdbcTemplate() + .update("DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", parameters) > 0; } } else { return getNamedParameterJdbcTemplate().update( diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java index bbf21bb0..4657659b 100644 --- a/src/main/java/com/juick/service/TagServiceImpl.java +++ b/src/main/java/com/juick/service/TagServiceImpl.java @@ -166,7 +166,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Override public List getPopularTags() { return getJdbcTemplate().queryForList(""" - select name from tags where noindex=false + select name from tags where noindex=0 order by stat_messages desc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""", String.class); } diff --git a/src/main/resources/db/migration/V1.39__tags_noindex.sql b/src/main/resources/db/migration/V1.39__tags_noindex.sql new file mode 100644 index 00000000..af37bc66 --- /dev/null +++ b/src/main/resources/db/migration/V1.39__tags_noindex.sql @@ -0,0 +1,7 @@ +alter table tags alter noindex set default null; +alter table tags alter noindex type INTEGER USING CASE WHEN noindex THEN 1 ELSE 0 END; +alter table tags alter noindex set default 0; +alter table tags alter top set default null; +alter table tags alter top type INTEGER USING CASE WHEN top THEN 1 ELSE 0 END; +alter table tags alter top set default 0; +alter table tags rename top to notop \ No newline at end of file diff --git a/src/main/resources/schema-h2.sql b/src/main/resources/schema-h2.sql index 13799b9d..a02804d5 100644 --- a/src/main/resources/schema-h2.sql +++ b/src/main/resources/schema-h2.sql @@ -195,8 +195,8 @@ CREATE MEMORY TABLE "PUBLIC"."TAGS"( "TAG_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1 RESTART WITH 3) DEFAULT ON NULL NOT NULL, "SYNONYM_ID" INTEGER DEFAULT NULL, "NAME" VARCHAR_IGNORECASE(70) DEFAULT NULL, - "TOP" BOOLEAN DEFAULT FALSE NOT NULL, - "NOINDEX" BOOLEAN DEFAULT FALSE NOT NULL, + "NOTOP" SMALLINT DEFAULT '0' NOT NULL, + "NOINDEX" SMALLINT DEFAULT '0' NOT NULL, "STAT_MESSAGES" INTEGER DEFAULT '0' NOT NULL, "STAT_USERS" SMALLINT DEFAULT '0' NOT NULL ); diff --git a/src/main/resources/schema-mysql.sql b/src/main/resources/schema-mysql.sql index 2f3f9bfa..5b22bb35 100644 --- a/src/main/resources/schema-mysql.sql +++ b/src/main/resources/schema-mysql.sql @@ -515,7 +515,7 @@ CREATE TABLE `tags` ( `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `synonym_id` int(10) unsigned DEFAULT NULL, `name` char(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, - `top` tinyint(1) unsigned NOT NULL DEFAULT 0, + `notop` tinyint(1) unsigned NOT NULL DEFAULT 0, `noindex` tinyint(1) unsigned NOT NULL DEFAULT 0, `stat_messages` int(10) unsigned NOT NULL DEFAULT 0, `stat_users` smallint(5) unsigned NOT NULL DEFAULT 0, diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql index 3c31381d..b624d67b 100644 --- a/src/main/resources/schema-sqlite.sql +++ b/src/main/resources/schema-sqlite.sql @@ -201,8 +201,8 @@ CREATE TABLE tags ( tag_id INTEGER PRIMARY KEY NOT NULL, synonym_id bigint, name character varying(70) COLLATE NOCASE, - top boolean DEFAULT false NOT NULL, - noindex boolean DEFAULT false NOT NULL, + notop smallint DEFAULT 0 NOT NULL, + noindex smallint DEFAULT 0 NOT NULL, stat_messages bigint DEFAULT (0) NOT NULL, stat_users smallint DEFAULT (0) NOT NULL, FOREIGN KEY (synonym_id) REFERENCES tags(tag_id) diff --git a/src/main/resources/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql index 5fbe9569..71539577 100644 --- a/src/main/resources/schema-sqlserver.sql +++ b/src/main/resources/schema-sqlserver.sql @@ -9,6 +9,7 @@ CREATE TABLE users ( lastphoto bigint DEFAULT (0) NOT NULL, karma smallint DEFAULT (0) NOT NULL, last_seen datetimeoffset, + UNIQUE(nick), PRIMARY KEY (id) ); CREATE TABLE user_services ( @@ -30,7 +31,7 @@ CREATE TABLE tags ( tag_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL, synonym_id integer, name character varying(70), - [top] bit DEFAULT 0 NOT NULL, + notop bit DEFAULT 0 NOT NULL, noindex bit DEFAULT 0 NOT NULL, stat_messages bigint DEFAULT (0) NOT NULL, stat_users smallint DEFAULT (0) NOT NULL, @@ -120,7 +121,7 @@ CREATE TABLE meon ( CREATE TABLE messages ( message_id INTEGER IDENTITY(1,1) PRIMARY KEY NOT NULL, user_id bigint NOT NULL, - lang TEXT DEFAULT '__' NOT NULL, + lang varchar(max) DEFAULT '__' NOT NULL, ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, replies smallint DEFAULT (0) NOT NULL, maxreplyid smallint DEFAULT (0) NOT NULL, @@ -143,7 +144,7 @@ CREATE TABLE messages_access ( FOREIGN KEY (message_id) REFERENCES messages(message_id) ); CREATE TABLE messages_properties ( - message_id INTEGER PRIMARY KEY NOT NULL, + message_id INTEGER NOT NULL, reply_id smallint NOT NULL, property_key character varying(255) NOT NULL, property_value character varying(255) NOT NULL, @@ -181,7 +182,7 @@ CREATE TABLE pm ( user_id bigint NOT NULL, user_id_to bigint NOT NULL, ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, - txt text NOT NULL, + txt varchar(max) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (user_id_to) REFERENCES users(id) ); @@ -192,7 +193,7 @@ CREATE TABLE replies ( replyto smallint DEFAULT (0) NOT NULL, ts datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, attach VARCHAR(10) CHECK (attach IN ('jpg', 'mp4', 'png')), - txt text NOT NULL, + txt varchar(max) NOT NULL, updated_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, user_uri character varying(255) DEFAULT NULL, reply_uri character varying(255) DEFAULT NULL, @@ -270,7 +271,7 @@ CREATE TABLE usersinfo ( url character varying(128), gender character varying(32), bday character varying(32), - descr text, + descr varchar(max), UNIQUE(user_id), FOREIGN KEY (user_id) REFERENCES users(id) ); diff --git a/src/test/java/com/juick/server/tests/ServerTests.java b/src/test/java/com/juick/server/tests/ServerTests.java index ea1e6100..1cd51f7f 100644 --- a/src/test/java/com/juick/server/tests/ServerTests.java +++ b/src/test/java/com/juick/server/tests/ServerTests.java @@ -197,8 +197,6 @@ public class ServerTests { @Inject private ActivityPubService activityPubService; @Inject - private SignatureService signatureService; - @Inject private ActivityPubManager activityPubManager; @Inject private ProfileUriBuilder profileUriBuilder; @@ -208,19 +206,8 @@ public class ServerTests { private String baseUri; @Value("${web_domain:localhost}") private String webDomain; - @Inject - private OkHttpClient apClient; - @Value("classpath:snapshots/activity/testuser.json") private Resource testuserResponse; - @Value("classpath:snapshots/activity/testapp.json") - private Resource testappResponse; - @Value("classpath:snapshots/activity/testfollow.json") - private Resource testfollowRequest; - @Value("classpath:snapshots/activity/testdelete.json") - private Resource testDeleteRequest; - @Value("classpath:snapshots/activity/test_suspended_user.json") - private Resource testSuspendedUserResponse; @Value("classpath:snapshots/email/subscription.html") private Resource testSubscriptionHtmlEmail; @Value("classpath:snapshots/email/private.html") @@ -249,30 +236,20 @@ public class ServerTests { private Resource invisiblePixel; @Value("classpath:sape.xml") private Resource sapeOutput; - @Value("classpath:flag.json") - private Resource flagPayload; @Value("classpath:friendica_update.json") private Resource friendicaUpdate; @Inject AppleClientSecretGenerator clientSecretGenerator; @Inject private Remark remarkConverter; - - @Inject - private KeystoreManager testKeystoreManager; - @Inject private ApplicationEventPublisher applicationEventPublisher; - @Inject private Users usersController; @Inject private User serviceUser; @Inject private User archiveUser; - @Inject - private ConversionService conversionService; - private static User ugnich, freefd; static String ugnichName, ugnichPassword, freefdName, freefdPassword; URI emptyUri = URI.create(StringUtils.EMPTY); @@ -382,6 +359,7 @@ public class ServerTests { @Test public void messageTests() { + deleteMessages(); User user = userService.createUser("mmmme", "secret").orElseThrow(IllegalStateException::new); assertEquals("mmmme", user.getName()); int mid = messagesService.createMessage(user.getUid(), "yo", null, Set.of()); @@ -898,8 +876,8 @@ public class ServerTests { .getUsersSubscribedToComments(messagesService.getMessage(mid).get(), messagesService.getReply(mid, rid)) .size()); - assertNotEquals("should NOT be deleted", String.format("Message %s deleted", mid), - commandsManager.processCommand(readerUser, "D #" + mid, emptyUri).getText()); + var deletion = commandsManager.processCommand(readerUser, "D #" + mid, emptyUri).getText(); + assertThat(deletion, is("This is not your message")); assertEquals("Message deleted", commandsManager.processCommand(user, "D #" + mid, emptyUri).getText()); assertEquals("Message not found", commandsManager.processCommand(user, "#" + mid, emptyUri).getText()); @@ -1039,11 +1017,18 @@ public class ServerTests { assertThat(messagesService.getMessage(mid1).get().getRecommendations().size(), is(1)); } + void deleteMessages() { + var ids = messagesService.getAll(0, 0); + var msgs = messagesService.getMessages(0, ids); + var results = msgs.stream().map(i -> messagesService.deleteMessage(i.getUser().getUid(), i.getMid())); + assertThat(results.allMatch(i -> i), is(true)); + } + @Test @Order(5) public void lastReadTests() throws Exception { jdbcTemplate.execute("DELETE FROM bl_users"); - jdbcTemplate.execute("DELETE FROM messages"); + deleteMessages(); assertThat(userService.isInBLAny(ugnich.getUid(), freefd.getUid()), is(false)); int mid = messagesService.createMessage(ugnich.getUid(), "to be watched", null, Set.of()); subscriptionService.subscribeMessage(messagesService.getMessage(mid).get(), ugnich); @@ -1616,8 +1601,9 @@ public class ServerTests { } @Test + @Order(5) public void bannedUserShouldNotBeVisibleToOthers() { - jdbcTemplate.execute("DELETE FROM messages"); + deleteMessages(); User casualUser = userService.createUser("user", "secret").orElseThrow(IllegalStateException::new); User bannedUser = userService.createUser("banned", "banned").orElseThrow(IllegalStateException::new); jdbcTemplate.update("UPDATE users SET banned=1 WHERE id=?", bannedUser.getUid()); @@ -1661,7 +1647,7 @@ public class ServerTests { .andExpect(jsonPath("$.icon.url", is(String.format(baseUri + "av-96-%s.png", hash)))) .andExpect(jsonPath("$.publicKey.publicKeyPem", is(keystoreManager.getPublicKeyPem()))); - jdbcTemplate.execute("DELETE FROM messages"); + deleteMessages(); List mids = IteratorUtils.toList(IntStream.rangeClosed(1, 30) .mapToObj( i -> messagesService.createMessage(ugnich.getUid(), @@ -1700,8 +1686,7 @@ public class ServerTests { return display == null || !display.getValue().equals("none"); }).count(); assertThat(visibleItems, equalTo(14L)); - jdbcTemplate.execute("DELETE FROM messages"); - jdbcTemplate.execute("DELETE FROM replies"); + deleteMessages(); } @Test @@ -1793,6 +1778,7 @@ public class ServerTests { } @Test + @Order(20) public void discussionsShouldBePageableByTimestamp() throws Exception { String msgText = "Привет, я снова Угнич"; int mid = messagesService.createMessage(ugnich.getUid(), msgText, null, Set.of()); @@ -1887,9 +1873,7 @@ public class ServerTests { @Test public void notificationsTests() throws Exception { - jdbcTemplate.execute("DELETE FROM messages"); - jdbcTemplate.execute("DELETE FROM replies"); - jdbcTemplate.execute("DELETE FROM subscr_messages"); + deleteMessages(); jdbcTemplate.execute("DELETE FROM bl_users"); MvcResult loginResult = mockMvc .perform(post("/login").with(csrf()).param("username", freefdName).param("password", @@ -2024,6 +2008,7 @@ public class ServerTests { @Test public void activitySerialization() throws Exception { + deleteMessages(); Message msgNoTags = commandsManager.processCommand(ugnich, "people", emptyUri).getNewMessage().get(); String json = jsonMapper.writeValueAsString(Context.build(activityPubManager.makeNote(msgNoTags))); Message msg = commandsManager.processCommand(ugnich, "*NSFW *shit happens", emptyUri).getNewMessage() @@ -2032,7 +2017,7 @@ public class ServerTests { assertThat(note.isSensitive(), is(true)); json = jsonMapper.writeValueAsString(Context.build(note)); Note replyNote = new Note(); - replyNote.setId("http://localhost:8080/n/2-1"); + replyNote.setId("http://localhost:8080/n/" + msg.getMid() + "-1"); replyNote.setInReplyTo(profileUriBuilder.messageUri(msg)); replyNote.setAttributedTo("http://localhost:8080/u/freefd"); replyNote.setTo(Collections.singletonList(profileUriBuilder.personUri(ugnich))); @@ -2052,9 +2037,9 @@ public class ServerTests { json = jsonMapper.writeValueAsString(Context.build( activityPubManager.makeNote( messagesService.getReply(replyToExt.getMid(), replyToExt.getRid())))); - mockMvc.perform(get("/n/2-0")).andExpect(status().isOk()); + mockMvc.perform(get("/n/" + msg.getMid() + "-0")).andExpect(status().isOk()); mockMvc.perform(get("/n/2222-0")).andExpect(status().isNotFound()); - mockMvc.perform(get("/n/2-14")).andExpect(status().isNotFound()); + mockMvc.perform(get("/n/"+ msg.getMid() + "-14")).andExpect(status().isNotFound()); } @Test @@ -2159,8 +2144,9 @@ public class ServerTests { } @Test + @Order(6) public void ActivityPubLikesShouldNotMirrorMessage() throws Exception { - jdbcTemplate.execute("DELETE FROM messages"); + deleteMessages(); Message msg = commandsManager.processCommand(ugnich, "YO", emptyUri).getNewMessage().get(); messagesService.likeMessage(msg.getMid(), freefd.getUid(), Reaction.LIKE); messagesService.likeMessage(msg.getMid(), 0, Reaction.LIKE, "http://localhost:8080/u/test"); -- cgit v1.2.3