From 6a401f73236a70f702b64646d8bdec7c5a90e15d Mon Sep 17 00:00:00 2001
From: Zipeng WU <zipeng.wu@sonarsource.com>
Date: Tue, 5 Jul 2022 10:00:38 +0200
Subject: SONAR-16567 Notify the token creator about expiring tokens via email

---
 .../main/java/org/sonar/db/user/UserTokenDao.java  | 12 ++++++++
 .../java/org/sonar/db/user/UserTokenMapper.java    |  3 ++
 .../org/sonar/db/user/UserTokenMapper.xml          |  8 ++++++
 .../java/org/sonar/db/user/UserTokenDaoTest.java   | 33 +++++++++++++++++++++-
 4 files changed, 55 insertions(+), 1 deletion(-)

(limited to 'server/sonar-db-dao')

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDao.java
index e1191ee266c..581db16e863 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDao.java
@@ -19,6 +19,8 @@
  */
 package org.sonar.db.user;
 
+import java.time.LocalDate;
+import java.time.ZoneOffset;
 import java.util.Collection;
 import java.util.HashMap;
 import java.util.List;
@@ -54,6 +56,16 @@ public class UserTokenDao implements Dao {
     auditPersister.updateUserToken(dbSession, new UserTokenNewValue(userTokenDto, userLogin));
   }
 
+  public List<UserTokenDto> selectTokensExpiredInDays(DbSession dbSession, long days){
+    long timestamp = LocalDate.now().plusDays(days).atStartOfDay(ZoneOffset.UTC).toInstant().toEpochMilli();
+    return mapper(dbSession).selectTokensExpiredOnDate(timestamp);
+  }
+
+  public List<UserTokenDto> selectExpiredTokens(DbSession dbSession){
+    long timestamp = LocalDate.now().atStartOfDay(ZoneOffset.UTC).toInstant().toEpochMilli();
+    return mapper(dbSession).selectTokensExpiredOnDate(timestamp);
+  }
+
   public void updateWithoutAudit(DbSession dbSession, UserTokenDto userTokenDto) {
     mapper(dbSession).update(userTokenDto);
   }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenMapper.java
index 435d445c35d..173085903e9 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenMapper.java
@@ -41,4 +41,7 @@ public interface UserTokenMapper {
   int deleteByProjectKey(@Param("projectKey") String projectKey);
 
   List<UserTokenCount> countTokensByUserUuids(@Param("userUuids") List<String> userUuids);
+
+  List<UserTokenDto> selectTokensExpiredOnDate(@Param("timestamp") long timestamp);
+
 }
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserTokenMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserTokenMapper.xml
index f99bfb6c5c7..3e52e4907e2 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserTokenMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserTokenMapper.xml
@@ -55,6 +55,14 @@
     WHERE t.token_hash=#{tokenHash, jdbcType=VARCHAR}
   </select>
 
+  <select id="selectTokensExpiredOnDate" parameterType="Long" resultType="UserToken">
+    SELECT
+    <include refid="userTokensColumns"/>
+    FROM user_tokens t
+    LEFT JOIN projects p on t.project_key = p.kee
+    WHERE t.expiration_date = #{timestamp, jdbcType=BIGINT}
+  </select>
+
   <select id="selectByUserUuidAndName" parameterType="map" resultType="UserToken">
     SELECT
     <include refid="userTokensColumns"/>
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/user/UserTokenDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/user/UserTokenDaoTest.java
index 4dac17ded9e..4058abe180e 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/user/UserTokenDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/user/UserTokenDaoTest.java
@@ -19,8 +19,10 @@
  */
 package org.sonar.db.user;
 
+import java.time.LocalDate;
+import java.time.ZoneOffset;
+import java.util.List;
 import java.util.Map;
-import org.apache.commons.lang.math.RandomUtils;
 import org.junit.Rule;
 import org.junit.Test;
 import org.sonar.api.utils.System2;
@@ -78,6 +80,35 @@ public class UserTokenDaoTest {
     assertThat(projectAnalysisTokenFromDb.getExpirationDate()).isNull();
   }
 
+  @Test
+  public void select_tokens_expired_in_7_days() {
+    String token1 = insertTokenExpiredInDays(0);
+    String token2 = insertTokenExpiredInDays(7);
+    String token3 = insertTokenExpiredInDays(14);
+    List<UserTokenDto> tokenList = underTest.selectTokensExpiredInDays(dbSession, 7);
+    assertThat(tokenList).hasSize(1)
+      .extracting(UserTokenDto::getTokenHash)
+      .containsOnly(token2);
+  }
+
+  @Test
+  public void select_expired_tokens() {
+    String token1 = insertTokenExpiredInDays(0);
+    String token2 = insertTokenExpiredInDays(7);
+    String token3 = insertTokenExpiredInDays(14);
+    List<UserTokenDto> tokenList = underTest.selectExpiredTokens(dbSession);
+    assertThat(tokenList).hasSize(1)
+      .extracting(UserTokenDto::getTokenHash)
+      .containsOnly(token1);
+  }
+
+  private String insertTokenExpiredInDays(long days){
+    long expirationDate = LocalDate.now().plusDays(days).atStartOfDay(ZoneOffset.UTC).toInstant().toEpochMilli();
+    UserTokenDto userToken = newUserToken().setExpirationDate(expirationDate);
+    underTest.insert(dbSession, userToken, "login");
+    return userToken.getTokenHash();
+  }
+
   @Test
   public void update_last_connection_date() {
     UserDto user1 = db.users().insertUser();
-- 
cgit v1.2.3