From 84c3c4c71a7fadb52b7214b9514f5227835c2cac Mon Sep 17 00:00:00 2001 From: Daniel Schwarz Date: Tue, 9 May 2017 16:44:53 +0200 Subject: SONAR-9148 fix partition size for large inputs in MySQL * reproduce issues with large inputs in MySQL * fix chunk size of #keepAuthorizedProjectIds * fix chunk size of #keepAuthorizedUsersForRoleAndProject --- .../src/main/java/org/sonar/db/DatabaseUtils.java | 66 ++++++++++++++++++---- .../test/java/org/sonar/db/DatabaseUtilsTest.java | 16 ++++++ .../org/sonar/db/permission/AuthorizationDao.java | 9 ++- .../sonar/db/permission/AuthorizationDaoTest.java | 30 ++++++++++ 4 files changed, 108 insertions(+), 13 deletions(-) diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java b/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java index 1b4cd4209cd..46a8c91b7d0 100644 --- a/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java +++ b/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java @@ -38,6 +38,7 @@ import java.util.HashSet; import java.util.List; import java.util.Set; import java.util.function.Consumer; +import java.util.function.IntFunction; import javax.annotation.CheckForNull; import javax.annotation.Nullable; import org.sonar.api.utils.log.Logger; @@ -55,6 +56,10 @@ public class DatabaseUtils { */ private static final String[] TABLE_TYPE = {"TABLE"}; + protected DatabaseUtils() { + throw new IllegalStateException("Utility class"); + } + public static void closeQuietly(@Nullable Connection connection) { if (connection != null) { try { @@ -95,20 +100,32 @@ public class DatabaseUtils { * and with MsSQL when there's more than 2000 parameters in a query */ public static > List executeLargeInputs(Collection input, Function, List> function) { - return executeLargeInputs(input, function, size -> size == 0 ? Collections.emptyList() : new ArrayList<>(size)); + return executeLargeInputs(input, function, i -> i); } - public static > Set executeLargeInputsIntoSet(Collection input, Function, Set> function) { - return executeLargeInputs(input, function, size -> size == 0 ? Collections.emptySet() : new HashSet<>(size)); + /** + * Partition by 1000 elements a list of input and execute a function on each part. + * + * The goal is to prevent issue with ORACLE when there's more than 1000 elements in a 'in ('X', 'Y', ...)' + * and with MsSQL when there's more than 2000 parameters in a query + */ + public static > List executeLargeInputs(Collection input, Function, List> function, + IntFunction partitionSizeManipulations) { + return executeLargeInputs(input, function, size -> size == 0 ? Collections.emptyList() : new ArrayList<>(size), partitionSizeManipulations); + } + + public static > Set executeLargeInputsIntoSet(Collection input, Function, Set> function, + IntFunction partitionSizeManipulations) { + return executeLargeInputs(input, function, size -> size == 0 ? Collections.emptySet() : new HashSet<>(size), partitionSizeManipulations); } private static , RESULT extends Collection> RESULT executeLargeInputs(Collection input, - Function, RESULT> function, java.util.function.Function outputInitializer) { + Function, RESULT> function, java.util.function.Function outputInitializer, IntFunction partitionSizeManipulations) { if (input.isEmpty()) { return outputInitializer.apply(0); } RESULT results = outputInitializer.apply(input.size()); - for (List partition : toUniqueAndSortedPartitions(input)) { + for (List partition : toUniqueAndSortedPartitions(input, partitionSizeManipulations)) { RESULT subResults = function.apply(partition); if (subResults != null) { results.addAll(subResults); @@ -124,7 +141,24 @@ public class DatabaseUtils { * and with MsSQL when there's more than 2000 parameters in a query */ public static > void executeLargeUpdates(Collection inputs, Consumer> consumer) { - Iterable> partitions = toUniqueAndSortedPartitions(inputs); + executeLargeUpdates(inputs, consumer, i -> i); + } + + /** + * Partition by 1000 elements a list of input and execute a consumer on each part. + * + * The goal is to prevent issue with ORACLE when there's more than 1000 elements in a 'in ('X', 'Y', ...)' + * and with MsSQL when there's more than 2000 parameters in a query + * + * @param inputs the whole list of elements to be partitioned + * @param consumer the mapper method to be executed, for example {@code mapper(dbSession)::selectByUuids} + * @param partitionSizeManipulations the function that computes the number of usages of a partition, for example + * {@code partitionSize -> partitionSize / 2} when the partition of elements + * in used twice in the SQL request. + */ + public static > void executeLargeUpdates(Collection inputs, Consumer> consumer, + IntFunction partitionSizeManipulations) { + Iterable> partitions = toUniqueAndSortedPartitions(inputs, partitionSizeManipulations); for (List partition : partitions) { consumer.accept(partition); } @@ -136,12 +170,16 @@ public class DatabaseUtils { * The goal is to prevent issue with ORACLE when there's more than 1000 elements in a 'in ('X', 'Y', ...)' * and with MsSQL when there's more than 2000 parameters in a query * + * @param inputs the whole list of elements to be partitioned * @param sqlCaller a {@link Function} which calls the SQL update/delete and returns the number of updated/deleted rows. - * + * @param partitionSizeManipulations the function that computes the number of usages of a partition, for example + * {@code partitionSize -> partitionSize / 2} when the partition of elements + * in used twice in the SQL request. * @return the total number of updated/deleted rows (computed as the sum of the values returned by {@code sqlCaller}). */ - public static > int executeLargeUpdates(Collection inputs, Function, Integer> sqlCaller) { - Iterable> partitions = toUniqueAndSortedPartitions(inputs); + public static > int executeLargeUpdates(Collection inputs, Function, Integer> sqlCaller, + IntFunction partitionSizeManipulations) { + Iterable> partitions = toUniqueAndSortedPartitions(inputs, partitionSizeManipulations); Integer res = 0; for (List partition : partitions) { res += sqlCaller.apply(partition); @@ -153,7 +191,15 @@ public class DatabaseUtils { * Ensure values {@code inputs} are unique (which avoids useless arguments) and sorted before creating the partition. */ public static > Iterable> toUniqueAndSortedPartitions(Collection inputs) { - return Iterables.partition(toUniqueAndSortedList(inputs), PARTITION_SIZE_FOR_ORACLE); + return toUniqueAndSortedPartitions(inputs, i -> i); + } + + /** + * Ensure values {@code inputs} are unique (which avoids useless arguments) and sorted before creating the partition. + */ + public static > Iterable> toUniqueAndSortedPartitions(Collection inputs, IntFunction partitionSizeManipulations) { + int partitionSize = partitionSizeManipulations.apply(PARTITION_SIZE_FOR_ORACLE); + return Iterables.partition(toUniqueAndSortedList(inputs), partitionSize); } /** diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java b/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java index c820f7996bc..88dcd5e5a2e 100644 --- a/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java +++ b/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java @@ -25,6 +25,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.util.ArrayList; import java.util.Collections; import java.util.HashSet; import java.util.List; @@ -280,6 +281,21 @@ public class DatabaseUtilsTest { assertThat(outputs).isEmpty(); } + @Test + public void executeLargeInputs_uses_specified_partition_size_manipulations() { + List> partitions = new ArrayList<>(); + List outputs = DatabaseUtils.executeLargeInputs( + asList(1, 2, 3), + partition -> { + partitions.add(partition); + return partition; + }, + i -> i / 500); + + assertThat(outputs).containsExactly(1,2,3); + assertThat(partitions).containsExactly(asList(1,2), asList(3)); + } + @Test public void executeLargeUpdates() { List inputs = newArrayList(); diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/AuthorizationDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/AuthorizationDao.java index 7f7639c1416..2e0c94c29af 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/AuthorizationDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/AuthorizationDao.java @@ -136,7 +136,8 @@ public class AuthorizationDao implements Dao { return mapper(dbSession).keepAuthorizedProjectIdsForAnonymous(permission, partition); } return mapper(dbSession).keepAuthorizedProjectIdsForUser(userId, permission, partition); - }); + }, + partitionSize -> partitionSize / 2); } public Set keepAuthorizedProjectUuids(DbSession dbSession, Collection projectUuids, @Nullable Integer userId, String permission) { @@ -147,7 +148,8 @@ public class AuthorizationDao implements Dao { return mapper(dbSession).keepAuthorizedProjectUuidsForAnonymous(permission, partition); } return mapper(dbSession).keepAuthorizedProjectUuidsForUser(userId, permission, partition); - }); + }, + partitionSize -> partitionSize / 2); } /** @@ -157,7 +159,8 @@ public class AuthorizationDao implements Dao { public Collection keepAuthorizedUsersForRoleAndProject(DbSession dbSession, Collection userIds, String role, long projectId) { return executeLargeInputs( userIds, - partitionOfIds -> mapper(dbSession).keepAuthorizedUsersForRoleAndProject(role, projectId, partitionOfIds)); + partitionOfIds -> mapper(dbSession).keepAuthorizedUsersForRoleAndProject(role, projectId, partitionOfIds), + partitionSize -> partitionSize / 3); } private static AuthorizationMapper mapper(DbSession dbSession) { diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/AuthorizationDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/AuthorizationDaoTest.java index 174199c28f2..9ffad60746c 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/AuthorizationDaoTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/AuthorizationDaoTest.java @@ -19,9 +19,12 @@ */ package org.sonar.db.permission; +import java.util.Collection; import java.util.Collections; +import java.util.List; import java.util.Random; import java.util.Set; +import java.util.stream.Collectors; import java.util.stream.IntStream; import org.junit.Before; import org.junit.Rule; @@ -479,6 +482,24 @@ public class AuthorizationDaoTest { .isEmpty(); } + @Test + public void keepAuthorizedProjectIds_should_be_able_to_handle_lots_of_projects() { + List projects = IntStream.range(0, 2000).mapToObj(i -> db.components().insertPublicProject(organization)).collect(Collectors.toList()); + + Collection ids = projects.stream().map(ComponentDto::getId).collect(Collectors.toSet()); + assertThat(underTest.keepAuthorizedProjectIds(dbSession, ids, null, UserRole.USER)) + .containsOnly(ids.toArray(new Long[0])); + } + + @Test + public void keepAuthorizedProjectUuids_should_be_able_to_handle_lots_of_projects() { + List projects = IntStream.range(0, 2000).mapToObj(i -> db.components().insertPublicProject(organization)).collect(Collectors.toList()); + + Collection uuids = projects.stream().map(ComponentDto::uuid).collect(Collectors.toSet()); + assertThat(underTest.keepAuthorizedProjectUuids(dbSession, uuids, null, UserRole.USER)) + .containsOnly(uuids.toArray(new String[0])); + } + @Test public void keepAuthorizedUsersForRoleAndProject_returns_empty_if_user_set_is_empty_on_public_project() { OrganizationDto organization = db.organizations().insert(); @@ -662,6 +683,15 @@ public class AuthorizationDaoTest { newHashSet(100, 101, 102), "user", PROJECT_ID)).isEmpty(); } + @Test + public void keepAuthorizedUsersForRoleAndProject_should_be_able_to_handle_lots_of_users() { + List users = IntStream.range(0, 2000).mapToObj(i -> db.users().insertUser()).collect(Collectors.toList()); + + assertThat(underTest.keepAuthorizedUsersForRoleAndProject(dbSession, + users.stream().map(UserDto::getId).collect(Collectors.toSet()), "user", PROJECT_ID)).isEmpty(); + } + + @Test public void countUsersWithGlobalPermissionExcludingGroupMember() { // u1 has the direct permission, u2 and u3 have the permission through their group -- cgit v1.2.3