SONAR-18728 use DB calls to replace UserIndex.getAtMostThreeActiveUsersForScmAccount

6 files changed, 44 insertions, 6 deletions

diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/user/UserDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/user/UserDaoIT.java
index f68c75e14ea..42f99253f63 100644
--- a/server/sonar-db-dao/src/it/java/org/sonar/db/user/UserDaoIT.java
+++ b/server/sonar-db-dao/src/it/java/org/sonar/db/user/UserDaoIT.java
@@ -91,6 +91,22 @@ public class UserDaoIT {
   }
 
   @Test
+  public void selectUserUuidByScmAccountOrLoginOrEmail_findsCorrectResults() {
+    String user1 = db.users().insertUser(user -> user.setLogin("user1").setEmail("toto@tata.com")).getUuid();
+    String user2 = db.users().insertUser(user -> user.setLogin("user2")).getUuid();
+    String user3 = db.users().insertUser(user -> user.setLogin("user3").setScmAccounts(List.of("scmuser3", "scmuser3bis"))).getUuid();
+    db.users().insertUser();
+    db.users().insertUser(user -> user.setLogin("inactive_user1").setActive(false));
+    db.users().insertUser(user -> user.setLogin("inactive_user2").setActive(false).setScmAccounts(List.of("inactive_user2")));
+
+    assertThat(underTest.selectUserUuidByScmAccountOrLoginOrEmail(session, "toto@tata.com")).containsExactly(user1);
+    assertThat(underTest.selectUserUuidByScmAccountOrLoginOrEmail(session, "user2")).containsExactly(user2);
+    assertThat(underTest.selectUserUuidByScmAccountOrLoginOrEmail(session, "scmuser3")).containsExactly(user3);
+    assertThat(underTest.selectUserUuidByScmAccountOrLoginOrEmail(session, "inactive_user1")).isEmpty();
+    assertThat(underTest.selectUserUuidByScmAccountOrLoginOrEmail(session, "inactive_user2")).isEmpty();
+  }
+
+  @Test
   public void selectUserByLogin_ignore_inactive() {
     db.users().insertUser(user -> user.setLogin("user1"));
     db.users().insertUser(user -> user.setLogin("user2"));
@@ -309,7 +325,7 @@ public class UserDaoIT {
       .setLogin("john")
       .setName("John")
       .setEmail("jo@hn.com")
-      .setScmAccounts(List.of("jo.hn", "john2", ""))
+      .setScmAccounts(List.of("jo.hn", "john2", "", "JoHn"))
       .setActive(true)
       .setResetPassword(true)
       .setSalt("1234")
@@ -334,7 +350,7 @@ public class UserDaoIT {
     assertThat(user.getEmail()).isEqualTo("jo@hn.com");
     assertThat(user.isActive()).isTrue();
     assertThat(user.isResetPassword()).isTrue();
-    assertThat(user.getSortedScmAccounts()).containsExactly("jo.hn", "john2");
+    assertThat(user.getSortedScmAccounts()).containsExactly("jo.hn", "john", "john2");
     assertThat(user.getSalt()).isEqualTo("1234");
     assertThat(user.getCryptedPassword()).isEqualTo("abcd");
     assertThat(user.getHashMethod()).isEqualTo("SHA1");
@@ -411,9 +427,9 @@ public class UserDaoIT {
   public void update_scmAccounts() {
     UserDto user = db.users().insertUser(u -> u.setScmAccounts(emptyList()));
 
-    underTest.update(db.getSession(), user.setScmAccounts(List.of("jo.hn", "john2", "johndoo", "")));
+    underTest.update(db.getSession(), user.setScmAccounts(List.of("jo.hn", "john2", "johndooUpper", "")));
     UserDto reloaded = Objects.requireNonNull(underTest.selectByUuid(db.getSession(), user.getUuid()));
-    assertThat(reloaded.getSortedScmAccounts()).containsExactly("jo.hn", "john2", "johndoo");
+    assertThat(reloaded.getSortedScmAccounts()).containsExactly("jo.hn", "john2", "johndooupper");
 
     underTest.update(db.getSession(), user.setScmAccounts(List.of("jo.hn", "john2")));
     reloaded = Objects.requireNonNull(underTest.selectByUuid(db.getSession(), user.getUuid()));
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserDao.java
index e42533ac9e2..c9c145aea33 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserDao.java
@@ -126,7 +126,7 @@ public class UserDao implements Dao {
   private static void insertScmAccounts(DbSession session, String userUuid, List<String> scmAccounts) {
     scmAccounts.stream()
       .filter(StringUtils::isNotBlank)
-      .forEach(scmAccount -> mapper(session).insertScmAccount(userUuid, scmAccount));
+      .forEach(scmAccount -> mapper(session).insertScmAccount(userUuid, scmAccount.toLowerCase(ENGLISH)));
   }
 
   public UserDto update(DbSession session, UserDto dto) {
@@ -175,6 +175,13 @@ public class UserDao implements Dao {
   }
 
   /**
+   * This method is optimized for the first analysis: we tried to keep performance optimal (<10ms) for projects with large number of contributors
+   */
+  public List<String> selectUserUuidByScmAccountOrLoginOrEmail(DbSession session, String scmAccountOrLoginOrEmail) {
+    return mapper(session).selectUserUuidByScmAccountOrLoginOrEmail(scmAccountOrLoginOrEmail);
+  }
+
+  /**
    * Search for an active user with the given emailCaseInsensitive exits in database
    * Select is case insensitive. Result for searching 'mail@emailCaseInsensitive.com' or 'Mail@Email.com' is the same
    */
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserMapper.java
index c0f6d6fcaee..496bba6727c 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserMapper.java
@@ -40,6 +40,8 @@ public interface UserMapper {
   @CheckForNull
   List<UserDto> selectNullableByScmAccountOrLoginOrEmail(@Param("scmAccount") String scmAccountOrLoginOrEmail);
 
+  List<String> selectUserUuidByScmAccountOrLoginOrEmail(@Param("scmAccount") String scmAccountOrLoginOrEmail);
+
   /**
    * Select user by login. Note that disabled users are ignored.
    */
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserMapper.xml
index 8bef5ef227e..4a9363a45d9 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserMapper.xml
@@ -364,4 +364,14 @@
       user_uuid = #{userUuid,jdbcType=VARCHAR}
     </delete>
 
+    <select id="selectUserUuidByScmAccountOrLoginOrEmail" parameterType="String" resultType="String">
+      select user_uuid as uuid from scm_accounts sa
+      left join users u on sa.user_uuid = u.uuid
+      where u.active=${_true} and sa.scm_account = lower(#{scmAccount,jdbcType=VARCHAR})
+      union
+      select uuid from
+      users u
+      where active=${_true} and (login=#{scmAccount,jdbcType=VARCHAR} or email=#{scmAccount,jdbcType=VARCHAR} )
+    </select>
+
 </mapper>
diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl
index b07d5318d37..1d4b4ae594c 100644
--- a/server/sonar-db-dao/src/schema/schema-sq.ddl
+++ b/server/sonar-db-dao/src/schema/schema-sq.ddl
@@ -934,6 +934,7 @@ CREATE TABLE "SCM_ACCOUNTS"(
     "SCM_ACCOUNT" CHARACTER VARYING(255) NOT NULL
 );
 ALTER TABLE "SCM_ACCOUNTS" ADD CONSTRAINT "PK_SCM_ACCOUNTS" PRIMARY KEY("USER_UUID", "SCM_ACCOUNT");
+CREATE INDEX "SCM_ACCOUNTS_SCM_ACCOUNT" ON "SCM_ACCOUNTS"("SCM_ACCOUNT" NULLS FIRST);
 
 CREATE TABLE "SESSION_TOKENS"(
     "UUID" CHARACTER VARYING(40) NOT NULL,
@@ -1027,6 +1028,7 @@ CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS"("LOGIN" NULLS FIRST);
 CREATE INDEX "USERS_UPDATED_AT" ON "USERS"("UPDATED_AT" NULLS FIRST);
 CREATE UNIQUE INDEX "UNIQ_EXTERNAL_ID" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_ID" NULLS FIRST);
 CREATE UNIQUE INDEX "UNIQ_EXTERNAL_LOGIN" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_LOGIN" NULLS FIRST);
+CREATE INDEX "USERS_EMAIL" ON "USERS"("EMAIL" NULLS FIRST);
 
 CREATE TABLE "WEBHOOK_DELIVERIES"(
     "UUID" CHARACTER VARYING(40) NOT NULL,
diff --git a/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/user/UserTesting.java b/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/user/UserTesting.java
index 63762e339b5..dabcfa1f263 100644
--- a/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/user/UserTesting.java
+++ b/server/sonar-db-dao/src/testFixtures/java/org/sonar/db/user/UserTesting.java
@@ -19,6 +19,7 @@
  */
 package org.sonar.db.user;
 
+import java.util.Locale;
 import javax.annotation.Nullable;
 
 import static java.util.Collections.emptyList;
@@ -37,7 +38,7 @@ public class UserTesting {
       .setLogin(randomAlphanumeric(30))
       .setName(randomAlphanumeric(30))
       .setEmail(randomAlphanumeric(30))
-      .setScmAccounts(singletonList(randomAlphanumeric(40)))
+      .setScmAccounts(singletonList(randomAlphanumeric(40).toLowerCase(Locale.ENGLISH)))
       .setExternalId(randomAlphanumeric(40))
       .setExternalLogin(randomAlphanumeric(40))
       .setExternalIdentityProvider(randomAlphanumeric(40))