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

5 files changed, 68 insertions, 38 deletions

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<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())));
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<TagStats> getTagStats();
 
-    @CacheEvict(value = "message_tags", key = "#mid")
     Set<Tag> updateTags(int mid, Collection<Tag> newTags);
 
     Pair<String, Set<Tag>> fromString(String txt);
 
-    @Cacheable(value = "message_tags")
     List<TagStats> 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<Integer>) 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<TagStats> {
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<User> 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();
     }