From 7f182cc29f9484dc6ac0000404e3b287b6fa3b99 Mon Sep 17 00:00:00 2001 From: Aurelien Poscia Date: Tue, 28 Mar 2023 16:21:58 +0200 Subject: [PATCH] SONAR-18728 use DB calls to replace UserIndex.getAtMostThreeActiveUsersForScmAccount --- .../issue/ScmAccountToUserLoaderIT.java | 15 +---- .../issue/ScmAccountToUserLoader.java | 40 ++++++------- .../it/java/org/sonar/db/user/UserDaoIT.java | 24 ++++++-- .../main/java/org/sonar/db/user/UserDao.java | 9 ++- .../java/org/sonar/db/user/UserMapper.java | 2 + .../org/sonar/db/user/UserMapper.xml | 10 ++++ server/sonar-db-dao/src/schema/schema-sq.ddl | 2 + .../java/org/sonar/db/user/UserTesting.java | 3 +- .../v100/CreateIndexForEmailOnUsersTable.java | 60 +++++++++++++++++++ ...eIndexForScmAccountOnScmAccountsTable.java | 59 ++++++++++++++++++ .../migration/version/v100/DbVersion100.java | 2 + ...rateScmAccountsFromUsersToScmAccounts.java | 2 +- .../CreateIndexForEmailOnUsersTableTest.java | 53 ++++++++++++++++ ...exForScmAccountOnScmAccountsTableTest.java | 53 ++++++++++++++++ ...ScmAccountsFromUsersToScmAccountsTest.java | 16 ++++- .../schema.sql | 27 +++++++++ .../schema.sql | 5 ++ .../sonar/server/user/ws/UpdateActionIT.java | 2 +- 18 files changed, 342 insertions(+), 42 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTableTest/schema.sql diff --git a/server/sonar-ce-task-projectanalysis/src/it/java/org/sonar/ce/task/projectanalysis/issue/ScmAccountToUserLoaderIT.java b/server/sonar-ce-task-projectanalysis/src/it/java/org/sonar/ce/task/projectanalysis/issue/ScmAccountToUserLoaderIT.java index 411a7082f38..128f9927f50 100644 --- a/server/sonar-ce-task-projectanalysis/src/it/java/org/sonar/ce/task/projectanalysis/issue/ScmAccountToUserLoaderIT.java +++ b/server/sonar-ce-task-projectanalysis/src/it/java/org/sonar/ce/task/projectanalysis/issue/ScmAccountToUserLoaderIT.java @@ -21,14 +21,11 @@ package org.sonar.ce.task.projectanalysis.issue; import org.junit.Rule; import org.junit.Test; -import org.sonar.api.utils.System2; import org.sonar.api.utils.log.LogTester; import org.sonar.api.utils.log.LoggerLevel; import org.sonar.db.DbTester; import org.sonar.db.user.UserDto; import org.sonar.server.es.EsTester; -import org.sonar.server.user.index.UserIndex; -import org.sonar.server.user.index.UserIndexer; import static java.util.Arrays.asList; import static java.util.Collections.emptyList; @@ -44,15 +41,12 @@ public class ScmAccountToUserLoaderIT { @Rule public LogTester logTester = new LogTester(); - private UserIndexer userIndexer = new UserIndexer(db.getDbClient(), es.client()); @Test public void load_login_for_scm_account() { UserDto user = db.users().insertUser(u -> u.setScmAccounts(asList("charlie", "jesuis@charlie.com"))); - userIndexer.indexAll(); - UserIndex index = new UserIndex(es.client(), System2.INSTANCE); - ScmAccountToUserLoader underTest = new ScmAccountToUserLoader(index); + ScmAccountToUserLoader underTest = new ScmAccountToUserLoader(db.getDbClient()); assertThat(underTest.load("missing")).isNull(); assertThat(underTest.load("jesuis@charlie.com")).isEqualTo(user.getUuid()); @@ -62,10 +56,8 @@ public class ScmAccountToUserLoaderIT { public void warn_if_multiple_users_share_the_same_scm_account() { db.users().insertUser(u -> u.setLogin("charlie").setScmAccounts(asList("charlie", "jesuis@charlie.com"))); db.users().insertUser(u -> u.setLogin("another.charlie").setScmAccounts(asList("charlie"))); - userIndexer.indexAll(); - UserIndex index = new UserIndex(es.client(), System2.INSTANCE); - ScmAccountToUserLoader underTest = new ScmAccountToUserLoader(index); + ScmAccountToUserLoader underTest = new ScmAccountToUserLoader(db.getDbClient()); assertThat(underTest.load("charlie")).isNull(); assertThat(logTester.logs(LoggerLevel.WARN)).contains("Multiple users share the SCM account 'charlie': another.charlie, charlie"); @@ -73,8 +65,7 @@ public class ScmAccountToUserLoaderIT { @Test public void load_by_multiple_scm_accounts_is_not_supported_yet() { - UserIndex index = new UserIndex(es.client(), System2.INSTANCE); - ScmAccountToUserLoader underTest = new ScmAccountToUserLoader(index); + ScmAccountToUserLoader underTest = new ScmAccountToUserLoader(db.getDbClient()); try { underTest.loadAll(emptyList()); fail(); diff --git a/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/issue/ScmAccountToUserLoader.java b/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/issue/ScmAccountToUserLoader.java index 1fdca7df9b6..fac4f079909 100644 --- a/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/issue/ScmAccountToUserLoader.java +++ b/server/sonar-ce-task-projectanalysis/src/main/java/org/sonar/ce/task/projectanalysis/issue/ScmAccountToUserLoader.java @@ -20,16 +20,15 @@ package org.sonar.ce.task.projectanalysis.issue; import com.google.common.base.Joiner; -import com.google.common.collect.Ordering; import java.util.Collection; import java.util.List; import java.util.Map; import org.sonar.api.utils.log.Logger; import org.sonar.api.utils.log.Loggers; import org.sonar.ce.task.projectanalysis.util.cache.CacheLoader; -import org.sonar.core.util.stream.MoreCollectors; -import org.sonar.server.user.index.UserDoc; -import org.sonar.server.user.index.UserIndex; +import org.sonar.db.DbClient; +import org.sonar.db.DbSession; +import org.sonar.db.user.UserDto; /** * Loads the association between a SCM account and a SQ user @@ -38,28 +37,29 @@ public class ScmAccountToUserLoader implements CacheLoader { private static final Logger LOGGER = Loggers.get(ScmAccountToUserLoader.class); - private final UserIndex index; + private final DbClient dbClient; - public ScmAccountToUserLoader(UserIndex index) { - this.index = index; + public ScmAccountToUserLoader(DbClient dbClient) { + this.dbClient = dbClient; } @Override public String load(String scmAccount) { - List users = index.getAtMostThreeActiveUsersForScmAccount(scmAccount); - if (users.size() == 1) { - return users.get(0).uuid(); + try (DbSession dbSession = dbClient.openSession(false)) { + List userUuids = dbClient.userDao().selectUserUuidByScmAccountOrLoginOrEmail(dbSession, scmAccount); + if (userUuids.size() == 1) { + return userUuids.iterator().next(); + } + if (!userUuids.isEmpty()) { + List userDtos = dbClient.userDao().selectByUuids(dbSession, userUuids); + Collection logins = userDtos.stream() + .map(UserDto::getLogin) + .sorted() + .toList(); + LOGGER.warn(String.format("Multiple users share the SCM account '%s': %s", scmAccount, Joiner.on(", ").join(logins))); + } + return null; } - if (!users.isEmpty()) { - // multiple users are associated to the same SCM account, for example - // the same email - Collection logins = users.stream() - .map(UserDoc::login) - .sorted(Ordering.natural()) - .collect(MoreCollectors.toList(users.size())); - LOGGER.warn(String.format("Multiple users share the SCM account '%s': %s", scmAccount, Joiner.on(", ").join(logins))); - } - return null; } @Override 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 @@ -90,6 +90,22 @@ public class UserDaoIT { assertThat(userDto.getSortedScmAccounts()).containsExactlyElementsOf(scmAccountsUser1); } + @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")); @@ -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 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) { @@ -174,6 +174,13 @@ public class UserDao implements Dao { return mapper(session).selectNullableByScmAccountOrLoginOrEmail(scmAccountOrLoginOrEmail); } + /** + * This method is optimized for the first analysis: we tried to keep performance optimal (<10ms) for projects with large number of contributors + */ + public List 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 selectNullableByScmAccountOrLoginOrEmail(@Param("scmAccount") String scmAccountOrLoginOrEmail); + List 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} + + 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)) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTable.java new file mode 100644 index 00000000000..b05c7638fab --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTable.java @@ -0,0 +1,60 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser 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 + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v100; + +import com.google.common.annotations.VisibleForTesting; +import java.sql.Connection; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class CreateIndexForEmailOnUsersTable extends DdlChange { + + @VisibleForTesting + static final String INDEX_NAME = "users_email"; + @VisibleForTesting + static final String TABLE_NAME = "users"; + @VisibleForTesting + static final String COLUMN_NAME = "email"; + + public CreateIndexForEmailOnUsersTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + createIndex(context, connection); + } + } + + private static void createIndex(Context context, Connection connection) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn(COLUMN_NAME) + .setUnique(false) + .build()); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTable.java new file mode 100644 index 00000000000..c5d2557c694 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTable.java @@ -0,0 +1,59 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser 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 + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v100; + +import com.google.common.annotations.VisibleForTesting; +import java.sql.Connection; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.version.v100.CreateScmAccountsTable.SCM_ACCOUNT_COLUMN_NAME; +import static org.sonar.server.platform.db.migration.version.v100.CreateScmAccountsTable.TABLE_NAME; + +public class CreateIndexForScmAccountOnScmAccountsTable extends DdlChange { + + @VisibleForTesting + static final String INDEX_NAME = "scm_accounts_scm_account"; + + public CreateIndexForScmAccountOnScmAccountsTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + createIndex(context, connection); + } + } + + private static void createIndex(Context context, Connection connection) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn(SCM_ACCOUNT_COLUMN_NAME) + .setUnique(false) + .build()); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/DbVersion100.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/DbVersion100.java index fb8b694cd97..01e9385c4a9 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/DbVersion100.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/DbVersion100.java @@ -60,6 +60,8 @@ public class DbVersion100 implements DbVersion { .add(10_0_016, "Populate ncloc in 'Projects' table", PopulateNclocForForProjects.class) .add(10_0_015, "Add 'scm_accounts' table", CreateScmAccountsTable.class) .add(10_0_016, "Migrate scm accounts from 'users' to 'scm_accounts' table", MigrateScmAccountsFromUsersToScmAccounts.class) + .add(10_0_017, "Add index on 'scm_accounts.scm_account'", CreateIndexForScmAccountOnScmAccountsTable.class) + .add(10_0_018, "Add index on 'users.email'", CreateIndexForEmailOnUsersTable.class) ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/MigrateScmAccountsFromUsersToScmAccounts.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/MigrateScmAccountsFromUsersToScmAccounts.java index 11e780128c9..455a4db18de 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/MigrateScmAccountsFromUsersToScmAccounts.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/MigrateScmAccountsFromUsersToScmAccounts.java @@ -45,7 +45,7 @@ public class MigrateScmAccountsFromUsersToScmAccounts extends DataChange { protected void execute(Context context) throws SQLException { MassRowSplitter massRowSplitter = context.prepareMassRowSplitter(); - massRowSplitter.select("select u.uuid, u.scm_accounts from users u where u.active=? and not exists (select 1 from scm_accounts sa where sa.user_uuid = u.uuid)") + massRowSplitter.select("select u.uuid, lower(u.scm_accounts) from users u where u.active=? and not exists (select 1 from scm_accounts sa where sa.user_uuid = u.uuid)") .setBoolean(1, true); massRowSplitter.insert("insert into scm_accounts (user_uuid, scm_account) values (?, ?)"); diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTableTest.java new file mode 100644 index 00000000000..02b3683bd14 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTableTest.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser 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 + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v100; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.sonar.server.platform.db.migration.version.v100.CreateIndexForEmailOnUsersTable.COLUMN_NAME; +import static org.sonar.server.platform.db.migration.version.v100.CreateIndexForEmailOnUsersTable.INDEX_NAME; +import static org.sonar.server.platform.db.migration.version.v100.CreateIndexForEmailOnUsersTable.TABLE_NAME; + +public class CreateIndexForEmailOnUsersTableTest { + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateIndexForEmailOnUsersTableTest.class, "schema.sql"); + + private final CreateIndexForEmailOnUsersTable createIndexForEmailOnUsersTable = new CreateIndexForEmailOnUsersTable(db.database()); + + @Test + public void migration_should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + createIndexForEmailOnUsersTable.execute(); + + db.assertIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + createIndexForEmailOnUsersTable.execute(); + createIndexForEmailOnUsersTable.execute(); + + db.assertIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTableTest.java new file mode 100644 index 00000000000..3a34cfb789c --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTableTest.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser 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 + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v100; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.sonar.server.platform.db.migration.version.v100.CreateIndexForScmAccountOnScmAccountsTable.INDEX_NAME; +import static org.sonar.server.platform.db.migration.version.v100.CreateScmAccountsTable.SCM_ACCOUNT_COLUMN_NAME; +import static org.sonar.server.platform.db.migration.version.v100.CreateScmAccountsTable.TABLE_NAME; + +public class CreateIndexForScmAccountOnScmAccountsTableTest { + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateIndexForScmAccountOnScmAccountsTableTest.class, "schema.sql"); + + private final CreateIndexForScmAccountOnScmAccountsTable createIndexForScmAccountOnScmAccountsTable = new CreateIndexForScmAccountOnScmAccountsTable(db.database()); + + @Test + public void migration_should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + createIndexForScmAccountOnScmAccountsTable.execute(); + + db.assertIndex(TABLE_NAME, INDEX_NAME, SCM_ACCOUNT_COLUMN_NAME); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + createIndexForScmAccountOnScmAccountsTable.execute(); + createIndexForScmAccountOnScmAccountsTable.execute(); + + db.assertIndex(TABLE_NAME, INDEX_NAME, SCM_ACCOUNT_COLUMN_NAME); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/MigrateScmAccountsFromUsersToScmAccountsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/MigrateScmAccountsFromUsersToScmAccountsTest.java index afa1f8d793b..bc5bfd92988 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/MigrateScmAccountsFromUsersToScmAccountsTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/MigrateScmAccountsFromUsersToScmAccountsTest.java @@ -21,6 +21,7 @@ package org.sonar.server.platform.db.migration.version.v100; import java.sql.SQLException; import java.util.HashMap; +import java.util.Locale; import java.util.Map; import java.util.Set; import javax.annotation.Nullable; @@ -40,8 +41,9 @@ import static org.sonar.server.platform.db.migration.version.v100.MigrateScmAcco public class MigrateScmAccountsFromUsersToScmAccountsTest { private static final UuidFactory UUID_FACTORY = UuidFactoryFast.getInstance(); - private static final String SCM_ACCOUNT1 = "scmAccount"; - private static final String SCM_ACCOUNT2 = "scmAccount2"; + private static final String SCM_ACCOUNT1 = "scmaccount"; + private static final String SCM_ACCOUNT2 = "scmaccount2"; + private static final String SCM_ACCOUNT_CAMELCASE = "scmAccount3"; @Rule public final CoreDbTester db = CoreDbTester.createForSchema(MigrateScmAccountsFromUsersToScmAccountsTest.class, "schema.sql"); @@ -108,6 +110,16 @@ public class MigrateScmAccountsFromUsersToScmAccountsTest { assertThat(scmAccounts).containsExactly(new ScmAccountRow(userUuid, SCM_ACCOUNT1)); } + @Test + public void execute_whenUserHasOneScmAccountWithMixedCase_insertsInScmAccountsInLowerCase() throws SQLException { + String userUuid = insertUserAndGetUuid(format("%s%s%s", SCM_ACCOUNTS_SEPARATOR_CHAR, SCM_ACCOUNT_CAMELCASE, SCM_ACCOUNTS_SEPARATOR_CHAR)); + + migrateScmAccountsFromUsersToScmAccounts.execute(); + + Set scmAccounts = findAllScmAccounts(); + assertThat(scmAccounts).containsExactly(new ScmAccountRow(userUuid, SCM_ACCOUNT_CAMELCASE.toLowerCase(Locale.ENGLISH))); + } + @Test public void execute_whenUserHasTwoScmAccount_insertsInScmAccounts() throws SQLException { String userUuid = insertUserAndGetUuid(format("%s%s%s%s%s", diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTableTest/schema.sql new file mode 100644 index 00000000000..f2af20405c8 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/CreateIndexForEmailOnUsersTableTest/schema.sql @@ -0,0 +1,27 @@ +CREATE TABLE "USERS"( + "UUID" CHARACTER VARYING(255) NOT NULL, + "LOGIN" CHARACTER VARYING(255) NOT NULL, + "NAME" CHARACTER VARYING(200), + "EMAIL" CHARACTER VARYING(100), + "CRYPTED_PASSWORD" CHARACTER VARYING(100), + "SALT" CHARACTER VARYING(40), + "HASH_METHOD" CHARACTER VARYING(10), + "ACTIVE" BOOLEAN DEFAULT TRUE, + "SCM_ACCOUNTS" CHARACTER VARYING(4000), + "EXTERNAL_LOGIN" CHARACTER VARYING(255) NOT NULL, + "EXTERNAL_IDENTITY_PROVIDER" CHARACTER VARYING(100) NOT NULL, + "EXTERNAL_ID" CHARACTER VARYING(255) NOT NULL, + "USER_LOCAL" BOOLEAN NOT NULL, + "HOMEPAGE_TYPE" CHARACTER VARYING(40), + "HOMEPAGE_PARAMETER" CHARACTER VARYING(40), + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "RESET_PASSWORD" BOOLEAN NOT NULL, + "LAST_SONARLINT_CONNECTION" BIGINT +); +ALTER TABLE "USERS" ADD CONSTRAINT "PK_USERS" PRIMARY KEY("UUID"); +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); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTableTest/schema.sql new file mode 100644 index 00000000000..7a113bbdda2 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/CreateIndexForScmAccountOnScmAccountsTableTest/schema.sql @@ -0,0 +1,5 @@ +CREATE TABLE "SCM_ACCOUNTS"( + "USER_UUID" CHARACTER VARYING(255) NOT NULL, + "SCM_ACCOUNT" CHARACTER VARYING(255) NOT NULL +); +ALTER TABLE "SCM_ACCOUNTS" ADD CONSTRAINT "PK_SCM_ACCOUNTS" PRIMARY KEY("USER_UUID", "SCM_ACCOUNT"); diff --git a/server/sonar-webserver-webapi/src/it/java/org/sonar/server/user/ws/UpdateActionIT.java b/server/sonar-webserver-webapi/src/it/java/org/sonar/server/user/ws/UpdateActionIT.java index 4f013f14a42..d3827636087 100644 --- a/server/sonar-webserver-webapi/src/it/java/org/sonar/server/user/ws/UpdateActionIT.java +++ b/server/sonar-webserver-webapi/src/it/java/org/sonar/server/user/ws/UpdateActionIT.java @@ -238,7 +238,7 @@ public class UpdateActionIT { .execute(); UserDto user = dbClient.userDao().selectByLogin(dbSession, "john"); - assertThat(user.getSortedScmAccounts()).containsExactly("Jon.1", "JON.2", "jon.3"); + assertThat(user.getSortedScmAccounts()).containsExactly("jon.1", "jon.2", "jon.3"); } @Test -- 2.39.5