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
---
 .../com/juick/service/MessagesServiceImpl.java     | 46 ++++++++++++++--------
 1 file changed, 29 insertions(+), 17 deletions(-)

(limited to 'src/main/java/com/juick/service/MessagesServiceImpl.java')

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<Tag> 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<Integer>) 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<Integer, Set<Reaction>> likes = updateReactionsFor(mids);
             msgs.forEach(i -> i.setReactions(likes.get(i.getMid())));
             msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid())));
-- 
cgit v1.2.3