From 5ef424120ed694b1f69daeafaa1454455d13dee2 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 31 Jan 2024 16:50:01 +0300 Subject: CI: enable SQLite * SQLite JDBC driver does not support generated keys feature. So we implement `returningId` helper * only one connection should be used from connection pooler * changes made to prevent connection locking --- src/main/java/com/juick/ActivityPubManager.java | 20 ++------- .../java/com/juick/service/BaseJdbcService.java | 14 +++++++ .../com/juick/service/MessagesServiceImpl.java | 46 +++++++++++++-------- src/main/java/com/juick/service/TagService.java | 2 - .../java/com/juick/service/TagServiceImpl.java | 22 +++++----- .../java/com/juick/service/UserServiceImpl.java | 22 ++++++---- src/main/java/com/juick/util/ReentrantLockMap.java | 47 ---------------------- 7 files changed, 72 insertions(+), 101 deletions(-) delete mode 100644 src/main/java/com/juick/util/ReentrantLockMap.java (limited to 'src/main/java') diff --git a/src/main/java/com/juick/ActivityPubManager.java b/src/main/java/com/juick/ActivityPubManager.java index f234969b..f4b87b05 100644 --- a/src/main/java/com/juick/ActivityPubManager.java +++ b/src/main/java/com/juick/ActivityPubManager.java @@ -431,8 +431,6 @@ public class ActivityPubManager implements ActivityListener, NotificationListene } } - private static final ReentrantLockMap verificationLock = new ReentrantLockMap(); - public User verifyActor(String method, String path, Map headers) { String signatureString = headers.get("signature"); if (StringUtils.isNotEmpty(signatureString)) { @@ -445,20 +443,10 @@ public class ActivityPubManager implements ActivityListener, NotificationListene // local user key = keystoreManager.getPublicKey(); } else { - ReentrantLock lock = null; - try { - lock = verificationLock.getLock(keyId.toASCIIString()); - lock.lock(); - var context = activityPubService.get(keyId); - if (context.isPresent()) { - actor = (Actor) context.get(); - key = KeystoreManager.publicKeyOf(actor); - } - } finally { - if (lock != null) { - lock.unlock(); - verificationLock.retainLock(keyId.toASCIIString()); - } + var context = activityPubService.get(keyId); + if (context.isPresent()) { + actor = (Actor) context.get(); + key = KeystoreManager.publicKeyOf(actor); } } if (key != null) { diff --git a/src/main/java/com/juick/service/BaseJdbcService.java b/src/main/java/com/juick/service/BaseJdbcService.java index d16dadd2..b51cbdc7 100644 --- a/src/main/java/com/juick/service/BaseJdbcService.java +++ b/src/main/java/com/juick/service/BaseJdbcService.java @@ -52,6 +52,11 @@ public class BaseJdbcService { @Value("#{new Boolean('${spring.sql.init.platform}' == 'mysql')}") private boolean haveNoOffsetDateTime; + @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlserver')}") + private boolean haveOutput; + @Value("#{new Boolean('${spring.sql.init.platform}' == 'h2')}") + private boolean haveDeltaTables; + public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() { return namedParameterJdbcTemplate; } @@ -124,4 +129,13 @@ public class BaseJdbcService { } return Types.TIMESTAMP_WITH_TIMEZONE; } + public String returningId(String insertClause, String valuesClause, String idColumn) { + if (haveOutput) { + return insertClause + " OUTPUT inserted."+ idColumn + " " + valuesClause; + } + if (haveDeltaTables) { + return "SELECT " + idColumn + " FROM FINAL TABLE (" + insertClause + " " + valuesClause + ")"; + } + return insertClause + " " + valuesClause + " RETURNING " + idColumn; + } } diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 6874c7bc..1c504a2d 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -37,6 +37,7 @@ import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcInsert; +import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.lang.NonNull; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @@ -133,22 +134,29 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional @Override public int createMessage(final int uid, final String txt, final String attachment, @NonNull final Set tags) { - SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("messages") - .usingColumns("user_id", "attach", "ts", "readonly", "updated", "updated_at", "txt") - .usingGeneratedKeyColumns("message_id"); - var insertMap = new MapSqlParameterSource(); - insertMap.addValue("user_id", uid); var now = Instant.now(); - insertMap.addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); - insertMap.addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); - insertMap.addValue("updated_at", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); - insertMap.addValue("txt", StringUtils.defaultString(txt)); - if (StringUtils.isNotEmpty(attachment)) { - insertMap.addValue("attach", attachment); - } - insertMap.addValue("readonly", TagUtils.hasTag(tags, "readonly")); - int mid = simpleJdbcInsert.executeAndReturnKey(insertMap).intValue(); - if (mid > 0) { + var query = returningId(""" + INSERT INTO messages(user_id, attach, ts, readonly, updated, txt, updated_at)""", + "VALUES(?, ?, ?, ?, ?, ?, ?)", "message_id"); + Integer mid = getJdbcTemplate().execute((ConnectionCallback) con -> { + var nowObject = toDateTime(now.atOffset(ZoneOffset.UTC)); + var stmt = con.prepareStatement(query); + stmt.setInt(1, uid); + stmt.setString(2, attachment); + stmt.setObject(3, nowObject, dateTimeType()); + stmt.setBoolean(4, TagUtils.hasTag(tags, "readonly")); + stmt.setObject(5, nowObject, dateTimeType()); + stmt.setObject(6, StringUtils.defaultString(txt)); + stmt.setObject(7, nowObject, dateTimeType()); + + try (var resultSet = stmt.executeQuery()) { + if (resultSet.next()) { + return resultSet.getInt(1); + } + return 0; + } + }); + if (mid != null && mid > 0) { if (CollectionUtils.isNotEmpty(tags)) { var newTags = new ArrayList<>(tags); getJdbcTemplate().batchUpdate("INSERT INTO messages_tags(message_id, tag_id) VALUES (?, ?)", @@ -819,8 +827,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ new MapSqlParameterSource("ids", mids) .addValue("uid", uid), messageMapper); - msgs.forEach(m -> m.setTags(tagService.getMessageTags(m.getMid()).stream() - .map(TagStats::getTag).collect(Collectors.toSet()))); + + msgs.forEach(m -> { + var tags = tagService.getMessageTags(m.getMid()).stream() + .map(TagStats::getTag).collect(Collectors.toSet()); + m.setTags(tags); + }); Map> likes = updateReactionsFor(mids); msgs.forEach(i -> i.setReactions(likes.get(i.getMid()))); msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid()))); diff --git a/src/main/java/com/juick/service/TagService.java b/src/main/java/com/juick/service/TagService.java index ef937460..dcdd3a26 100644 --- a/src/main/java/com/juick/service/TagService.java +++ b/src/main/java/com/juick/service/TagService.java @@ -51,12 +51,10 @@ public interface TagService { List getTagStats(); - @CacheEvict(value = "message_tags", key = "#mid") Set updateTags(int mid, Collection newTags); Pair> fromString(String txt); - @Cacheable(value = "message_tags") List getMessageTags(int mid); boolean blacklistTag(User user, Tag tag); diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java index 90d0fc83..9f5c9356 100644 --- a/src/main/java/com/juick/service/TagServiceImpl.java +++ b/src/main/java/com/juick/service/TagServiceImpl.java @@ -24,6 +24,7 @@ import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.tuple.Pair; import org.springframework.jdbc.core.BatchPreparedStatementSetter; +import org.springframework.jdbc.core.PreparedStatementCallback; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.support.GeneratedKeyHolder; @@ -132,19 +133,16 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Transactional @Override public int createTag(final String name) { - KeyHolder holder = new GeneratedKeyHolder(); - getJdbcTemplate().update( - con -> { - PreparedStatement stmt = con.prepareStatement( - "INSERT INTO tags(name) VALUES (?)", - Statement.RETURN_GENERATED_KEYS); + return getJdbcTemplate().execute(returningId("INSERT INTO tags(name)", "VALUES (?)", "tag_id"), + (PreparedStatementCallback) stmt -> { stmt.setString(1, name); - return stmt; - }, - holder); - - return holder.getKeys().size() > 1 ? ((Number) holder.getKeys().get("tag_id")).intValue() - : holder.getKey().intValue(); + try (var resultSet = stmt.executeQuery()) { + if (resultSet.next()) { + return resultSet.getInt(1); + } + return 0; + } + }); } private class TagStatsMapper implements RowMapper { diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index 812a4e8b..0bbeea91 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -27,12 +27,16 @@ import org.apache.commons.lang3.tuple.Pair; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; +import org.springframework.dao.DataAccessException; import org.springframework.dao.DataIntegrityViolationException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.UncategorizedSQLException; +import org.springframework.jdbc.core.PreparedStatementCallback; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcInsert; +import org.springframework.jdbc.support.GeneratedKeyHolder; +import org.springframework.jdbc.support.KeyHolder; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @@ -93,21 +97,25 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional @Override public Optional createUser(final String username, final String password) throws UsernameTakenException { - var userInsert = new SimpleJdbcInsert(getJdbcTemplate()) - .withTableName("users") - .usingColumns("nick", "passw") - .usingGeneratedKeyColumns("id"); var params = new MapSqlParameterSource(); params.addValue("nick", username); params.addValue("passw", password); try { - var uid = userInsert.executeAndReturnKey(params).intValue(); + Integer uid = getNamedParameterJdbcTemplate().execute(returningId("INSERT INTO users(nick, passw)", "VALUES (:nick, :passw)", "id"), + params, ps -> { + try (var resultSet = ps.executeQuery()) { + if (resultSet.next()) { + return resultSet.getInt(1); + } + return 0; + } + }); if (uid > 0) { getJdbcTemplate().update("INSERT INTO subscr_users(user_id, suser_id) VALUES (2, ?)", uid); return getUserByUID(uid); } - } catch (DataIntegrityViolationException | UncategorizedSQLException e) { - throw new UsernameTakenException(); + } catch (Exception e) { + return Optional.empty(); } return Optional.empty(); } diff --git a/src/main/java/com/juick/util/ReentrantLockMap.java b/src/main/java/com/juick/util/ReentrantLockMap.java deleted file mode 100644 index 36aee218..00000000 --- a/src/main/java/com/juick/util/ReentrantLockMap.java +++ /dev/null @@ -1,47 +0,0 @@ -/* - * Copyright (C) 2008-2023, Juick - * - * This program is free software: you can redistribute it and/or modify - * it under the terms of the GNU Affero General Public License as - * published by the Free Software Foundation, either version 3 of the - * License, or (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU Affero General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see . - */ - -package com.juick.util; - -import java.util.concurrent.ConcurrentHashMap; -import java.util.concurrent.locks.ReentrantLock; - -public class ReentrantLockMap { - - ConcurrentHashMap mapStringLock; - - public ReentrantLockMap() { - mapStringLock = new ConcurrentHashMap<>(); - } - - public ReentrantLock getLock(String key) { - ReentrantLock initValue = createIntanceLock(); - ReentrantLock lock = mapStringLock.putIfAbsent(key, initValue); - if (lock == null) { - lock = initValue; - } - return lock; - } - - public void retainLock(String key) { - mapStringLock.remove(key); - } - - protected ReentrantLock createIntanceLock() { - return new ReentrantLock(); - } -} -- cgit v1.2.3