aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--.github/workflows/ci.yml3
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java33
-rw-r--r--src/main/java/com/juick/service/TagServiceImpl.java2
-rw-r--r--src/main/resources/db/migration/V1.39__tags_noindex.sql7
-rw-r--r--src/main/resources/schema-h2.sql4
-rw-r--r--src/main/resources/schema-mysql.sql2
-rw-r--r--src/main/resources/schema-sqlite.sql4
-rw-r--r--src/main/resources/schema-sqlserver.sql13
-rw-r--r--src/test/java/com/juick/server/tests/ServerTests.java60
9 files changed, 62 insertions, 66 deletions
diff --git a/.github/workflows/ci.yml b/.github/workflows/ci.yml
index 190fa9ba..c2a9187f 100644
--- a/.github/workflows/ci.yml
+++ b/.github/workflows/ci.yml
@@ -10,7 +10,7 @@ jobs:
runs-on: ubuntu-latest
strategy:
matrix:
- profile: [default, postgres, mysql]
+ profile: [default, postgres, mysql, sqlserver]
services:
postgres:
image: postgres
@@ -38,6 +38,7 @@ jobs:
ACCEPT_EULA: Y
MSSQL_SA_PASSWORD: Passw00rd!
MSSQL_PID: Express
+ MSSQL_COLLATION: Cyrillic_General_100_CI_AS_WS_SC_UTF8
steps:
- uses: actions/checkout@v2
- name: Set up JDK 17
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<Message> getMessages(int uid, final List<Integer> 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<Message> 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> 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<String> 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<Integer> 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");
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-22 13:29:59 +0000