From 88e71c8c976210624ddb296f24f2ddcbe53417b4 Mon Sep 17 00:00:00 2001 From: Jacek Date: Tue, 5 May 2020 09:50:09 +0200 Subject: [PATCH] SONAR-13221 drop id column of USERS table --- .../step/SendIssueNotificationsStepTest.java | 4 +- .../db/permission/UserPermissionDao.java | 20 ++-- .../db/permission/UserPermissionMapper.java | 8 +- .../db/qualityprofile/UserMembershipDto.java | 10 +- .../main/java/org/sonar/db/user/UserDao.java | 15 --- .../main/java/org/sonar/db/user/UserDto.java | 10 -- .../java/org/sonar/db/user/UserMapper.java | 5 - .../org/sonar/db/user/UserMembershipDto.java | 10 +- .../db/permission/UserPermissionMapper.xml | 18 ++-- .../QProfileEditUsersMapper.xml | 8 +- .../sonar/db/user/GroupMembershipMapper.xml | 4 +- .../org/sonar/db/user/UserMapper.xml | 20 +--- server/sonar-db-dao/src/schema/schema-sq.ddl | 4 +- .../db/organization/OrganizationDaoTest.java | 4 +- .../db/permission/UserPermissionDaoTest.java | 100 +++++++++--------- .../QProfileEditUsersDaoTest.java | 40 +++---- .../java/org/sonar/db/user/UserDaoTest.java | 32 ++---- .../sonar/db/property/PropertyTesting.java | 8 +- .../java/org/sonar/db/user/UserTesting.java | 1 - .../db/migration/version/v83/DbVersion83.java | 9 ++ ...AddPrimaryKeyOnUuidColumnOfUsersTable.java | 38 +++++++ .../v83/users/DropIdColumnOfUsersTable.java | 32 ++++++ .../DropPrimaryKeyOnIdColumnOfUsersTable.java | 33 ++++++ ...opUniqueIndexOnUuidColumnOfUsersTable.java | 53 ++++++++++ ...rimaryKeyOnUuidColumnOfUsersTableTest.java | 49 +++++++++ .../users/DropIdColumnOfUsersTableTest.java | 49 +++++++++ ...pPrimaryKeyOnIdColumnOfUsersTableTest.java | 54 ++++++++++ ...iqueIndexOnUuidColumnOfUsersTableTest.java | 50 +++++++++ .../schema.sql | 29 +++++ .../DropIdColumnOfUsersTableTest/schema.sql | 30 ++++++ .../schema.sql | 30 ++++++ .../schema.sql | 29 +++++ .../authentication/SafeModeUserSession.java | 6 -- .../org/sonar/server/permission/UserId.java | 10 +- .../org/sonar/server/user/DoPrivileged.java | 5 - .../sonar/server/user/ServerUserSession.java | 10 +- .../server/user/ThreadLocalUserSession.java | 12 +-- .../org/sonar/server/user/UserSession.java | 7 -- .../org/sonar/server/user/UserUpdater.java | 2 +- .../SafeModeUserSessionTest.java | 1 - .../user/ThreadLocalUserSessionTest.java | 3 - .../server/user/UserUpdaterCreateTest.java | 6 +- .../tester/AnonymousMockUserSession.java | 5 - .../sonar/server/tester/MockUserSession.java | 13 --- .../sonar/server/tester/UserSessionRule.java | 20 ++-- .../server/user/TestUserSessionFactory.java | 5 - .../permission/ws/PermissionWsSupport.java | 2 +- .../server/permission/ws/UsersAction.java | 8 +- .../qualityprofile/ws/SearchUsersAction.java | 6 +- .../organization/ws/DeleteActionTest.java | 4 +- .../ws/OrganizationDeleterTest.java | 4 +- .../ws/template/ApplyTemplateActionTest.java | 8 +- .../template/BulkApplyTemplateActionTest.java | 10 +- .../server/project/ws/DeleteActionTest.java | 2 +- 54 files changed, 654 insertions(+), 301 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/users/AddPrimaryKeyOnUuidColumnOfUsersTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/users/DropIdColumnOfUsersTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/users/DropPrimaryKeyOnIdColumnOfUsersTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/users/DropUniqueIndexOnUuidColumnOfUsersTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/users/AddPrimaryKeyOnUuidColumnOfUsersTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/users/DropIdColumnOfUsersTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/users/DropPrimaryKeyOnIdColumnOfUsersTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/users/DropUniqueIndexOnUuidColumnOfUsersTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/users/AddPrimaryKeyOnUuidColumnOfUsersTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/users/DropIdColumnOfUsersTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/users/DropPrimaryKeyOnIdColumnOfUsersTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/users/DropUniqueIndexOnUuidColumnOfUsersTableTest/schema.sql diff --git a/server/sonar-ce-task-projectanalysis/src/test/java/org/sonar/ce/task/projectanalysis/step/SendIssueNotificationsStepTest.java b/server/sonar-ce-task-projectanalysis/src/test/java/org/sonar/ce/task/projectanalysis/step/SendIssueNotificationsStepTest.java index 91ddd0ada48..49a094beb53 100644 --- a/server/sonar-ce-task-projectanalysis/src/test/java/org/sonar/ce/task/projectanalysis/step/SendIssueNotificationsStepTest.java +++ b/server/sonar-ce-task-projectanalysis/src/test/java/org/sonar/ce/task/projectanalysis/step/SendIssueNotificationsStepTest.java @@ -466,9 +466,9 @@ public class SendIssueNotificationsStepTest extends BaseStepTest { assertThat(assigneeCacheCaptor.getAllValues()) .filteredOn(t -> t != cache) .isEmpty(); - Tuple[] expected = stream(users).map(user -> tuple(user.getUuid(), user.getUuid(), user.getId(), user.getLogin())).toArray(Tuple[]::new); + Tuple[] expected = stream(users).map(user -> tuple(user.getUuid(), user.getUuid(), user.getUuid(), user.getLogin())).toArray(Tuple[]::new); assertThat(cache.entrySet()) - .extracting(Map.Entry::getKey, t -> t.getValue().getUuid(), t -> t.getValue().getId(), t -> t.getValue().getLogin()) + .extracting(Map.Entry::getKey, t -> t.getValue().getUuid(), t -> t.getValue().getUuid(), t -> t.getValue().getLogin()) .containsOnly(expected); } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java index 274edbc24a5..8d826b8d71d 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java @@ -40,25 +40,25 @@ public class UserPermissionDao implements Dao { * No sort is done. * * @param query non-null query including optional filters. - * @param userIds Filter on user ids, including disabled users. Must not be empty and maximum size is {@link DatabaseUtils#PARTITION_SIZE_FOR_ORACLE}. + * @param userUuids Filter on user ids, including disabled users. Must not be empty and maximum size is {@link DatabaseUtils#PARTITION_SIZE_FOR_ORACLE}. */ - public List selectUserPermissionsByQuery(DbSession dbSession, PermissionQuery query, Collection userIds) { - if (userIds.isEmpty()) { + public List selectUserPermissionsByQuery(DbSession dbSession, PermissionQuery query, Collection userUuids) { + if (userUuids.isEmpty()) { return emptyList(); } - checkArgument(userIds.size() <= DatabaseUtils.PARTITION_SIZE_FOR_ORACLE, "Maximum 1'000 users are accepted"); - return mapper(dbSession).selectUserPermissionsByQueryAndUserIds(query, userIds); + checkArgument(userUuids.size() <= DatabaseUtils.PARTITION_SIZE_FOR_ORACLE, "Maximum 1'000 users are accepted"); + return mapper(dbSession).selectUserPermissionsByQueryAndUserUuids(query, userUuids); } - public List selectUserIdsByQuery(DbSession dbSession, PermissionQuery query) { - return paginate(mapper(dbSession).selectUserIdsByQuery(query), query); + public List selectUserUuidsByQuery(DbSession dbSession, PermissionQuery query) { + return paginate(mapper(dbSession).selectUserUuidsByQuery(query), query); } - public List selectUserIdsByQueryAndScope(DbSession dbSession, PermissionQuery query) { - return paginate(mapper(dbSession).selectUserIdsByQueryAndScope(query), query); + public List selectUserUuidsByQueryAndScope(DbSession dbSession, PermissionQuery query) { + return paginate(mapper(dbSession).selectUserUuidsByQueryAndScope(query), query); } - private static List paginate(List results, PermissionQuery query) { + private static List paginate(List results, PermissionQuery query) { return results .stream() // Pagination is done in Java because it's too complex to use SQL pagination in Oracle and MsSQL with the distinct diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java index 648b6066ade..f891feb4ec2 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java @@ -26,17 +26,17 @@ import org.apache.ibatis.annotations.Param; public interface UserPermissionMapper { - List selectUserPermissionsByQueryAndUserIds(@Param("query") PermissionQuery query, @Param("userIds") Collection userIds); + List selectUserPermissionsByQueryAndUserUuids(@Param("query") PermissionQuery query, @Param("userUuids") Collection userUuids); - List selectUserIdsByQuery(@Param("query") PermissionQuery query); + List selectUserUuidsByQuery(@Param("query") PermissionQuery query); /** * Fetch user ids based on permission query and only in a specific scope (global permissions only, organization permissions only or project permissions only) */ - List selectUserIdsByQueryAndScope(@Param("query") PermissionQuery query); + List selectUserUuidsByQueryAndScope(@Param("query") PermissionQuery query); /** - * Count the number of distinct users returned by {@link #selectUserIdsByQuery(PermissionQuery)} + * Count the number of distinct users returned by {@link #selectUserUuidsByQuery(PermissionQuery)} * {@link PermissionQuery#getPageOffset()} and {@link PermissionQuery#getPageSize()} are ignored. */ int countUsersByQuery(@Param("query") PermissionQuery query); diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/UserMembershipDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/UserMembershipDto.java index aacd397eb84..939aa2e47b0 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/UserMembershipDto.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/UserMembershipDto.java @@ -21,16 +21,16 @@ package org.sonar.db.qualityprofile; public class UserMembershipDto { - private int userId; + private String userUuid; // Set by MyBatis private String uuid; - public int getUserId() { - return userId; + public String getUserUuid() { + return userUuid; } - public UserMembershipDto setUserId(int userId) { - this.userId = userId; + public UserMembershipDto setUserUuid(String userUuid) { + this.userUuid = userUuid; return this; } 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 6f93b86fade..f0c315632d9 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 @@ -53,26 +53,11 @@ public class UserDao implements Dao { this.uuidFactory = uuidFactory; } - @CheckForNull - public UserDto selectUserById(DbSession session, int userId) { - return mapper(session).selectUser(userId); - } - @CheckForNull public UserDto selectByUuid(DbSession session, String uuid) { return mapper(session).selectByUuid(uuid); } - /** - * Select users by ids, including disabled users. An empty list is returned - * if list of ids is empty, without any db round trips. - * - * Used by the Governance plugin - */ - public List selectByIds(DbSession session, Collection ids) { - return executeLargeInputs(ids, mapper(session)::selectByIds); - } - @CheckForNull public UserDto selectActiveUserByLogin(DbSession session, String login) { UserMapper mapper = mapper(session); diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserDto.java index d89bdee2aac..c81e64516bd 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserDto.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserDto.java @@ -35,7 +35,6 @@ public class UserDto { /** Technical unique identifier, can't be null */ private String uuid; - private Integer id; private String login; private String name; private String email; @@ -75,15 +74,6 @@ public class UserDto { return this; } - public Integer getId() { - return id; - } - - public UserDto setId(Integer id) { - this.id = id; - return this; - } - /** * Spaces were authorized before SQ 5.4. * For versions 5.4+ it's not possible to create a login with a space character. 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 16e197b67ea..6f3c802d8eb 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,9 +40,6 @@ public interface UserMapper { @CheckForNull List selectNullableByScmAccountOrLoginOrEmail(@Param("scmAccount") String scmAccountOrLoginOrEmail, @Param("likeScmAccount") String likeScmAccount); - @CheckForNull - UserDto selectUser(int userId); - /** * Select user by login. Note that disabled users are ignored. */ @@ -55,8 +52,6 @@ public interface UserMapper { List selectByUuids(List uuids); - List selectByIds(@Param("ids") List ids); - List selectByEmail(String email); @CheckForNull diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserMembershipDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserMembershipDto.java index ea421573722..c316bc97f23 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserMembershipDto.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserMembershipDto.java @@ -24,17 +24,17 @@ import javax.annotation.Nullable; public class UserMembershipDto { - private Long id; + private String uuid; private String groupUuid; private String login; private String name; - public Long getId() { - return id; + public String getUuid() { + return uuid; } - public UserMembershipDto setId(Long id) { - this.id = id; + public UserMembershipDto setUuid(String uuid) { + this.uuid = uuid; return this; } diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml index 9d242a62ebf..503efd0a984 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml @@ -3,7 +3,7 @@ - select u.uuid as userUuid, ur.organization_uuid as organizationUuid, @@ -11,25 +11,25 @@ ur.role as permission - u.id in #{userId,jdbcType=INTEGER} + u.uuid in #{userUuid,jdbcType=VARCHAR} - select - u.id, lower(u.name) + u.uuid, lower(u.name) - group by u.id, lower(u.name) + group by u.uuid, lower(u.name) order by case when (count(ur.role) > 0) then 1 else 2 end asc, lower(u.name) asc - select - u.id, lower(u.name) + u.uuid, lower(u.name) from users u left join user_roles ur on ur.user_uuid = u.uuid and ur.organization_uuid=#{query.organizationUuid,jdbcType=VARCHAR} @@ -45,12 +45,12 @@ - group by u.id, lower(u.name) + group by u.uuid, lower(u.name) order by case when (count(distinct ur.role) > 0) then 1 else 2 end asc, lower(u.name) asc select * from ( select row_number() over(order by u.name asc) as number, - u.id as userId, u.name as name, qeu.uuid as uuid + u.uuid as userUuid, u.name as name, qeu.uuid as uuid ) as query where @@ -45,7 +45,7 @@ - SELECT u.id as id, u.login as login, u.name as name, gu.group_uuid as groupUuid + SELECT u.uuid as uuid, u.login as login, u.name as name, gu.group_uuid as groupUuid ORDER BY u.name ASC 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 82fd6784cda..a1b5e6eca7b 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 @@ -4,7 +4,6 @@ - u.id as id, u.uuid as uuid, u.login as login, u.name as name, @@ -51,13 +50,6 @@ OR u.scm_accounts like #{likeScmAccount, jdbcType=VARCHAR} - - - -