From a68ecf037f70499825ae945ba20e47d5fefefb2c Mon Sep 17 00:00:00 2001 From: Julien Lancelot Date: Fri, 31 Mar 2017 17:22:59 +0200 Subject: [PATCH] SONAR-9019 Populate ORGANIZATIONS.DEFAULT_GROUP_ID column --- .../org/sonar/db/version/rows-h2.sql | 7 +- .../db/migration/version/v64/DbVersion64.java | 3 +- ...teColumnDefaultGroupIdOfOrganizations.java | 63 ++++++ .../version/v64/DbVersion64Test.java | 2 +- ...lumnDefaultGroupIdOfOrganizationsTest.java | 180 ++++++++++++++++++ .../initial.sql | 35 ++++ 6 files changed, 285 insertions(+), 5 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizations.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizationsTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizationsTest/initial.sql diff --git a/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql b/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql index fda9e9f6e99..24ba7f0fd59 100644 --- a/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql +++ b/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql @@ -558,17 +558,18 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1621'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1622'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1623'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1624'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1625'); INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, IS_ROOT, CREATED_AT, UPDATED_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', false, '1418215735482', '1418215735482'); ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; -INSERT INTO ORGANIZATIONS (UUID, KEE, NAME, GUARDED, CREATED_AT, UPDATED_AT) VALUES ('AVdqnciQUUs7Zd3KPvFD', 'default-organization', 'Default Organization', true, '1474962596482', '1474962596482'); -INSERT INTO INTERNAL_PROPERTIES (KEE, IS_EMPTY, TEXT_VALUE, CREATED_AT) VALUES ('organization.default', false, 'AVdqnciQUUs7Zd3KPvFD', '1474962596482'); - INSERT INTO GROUPS(ID, ORGANIZATION_UUID, NAME, DESCRIPTION, CREATED_AT, UPDATED_AT) VALUES (1, 'AVdqnciQUUs7Zd3KPvFD', 'sonar-administrators', 'System administrators', '2011-09-26 22:27:51.0', '2011-09-26 22:27:51.0'); INSERT INTO GROUPS(ID, ORGANIZATION_UUID, NAME, DESCRIPTION, CREATED_AT, UPDATED_AT) VALUES (2, 'AVdqnciQUUs7Zd3KPvFD', 'sonar-users', 'Any new users created will automatically join this group', '2011-09-26 22:27:51.0', '2011-09-26 22:27:51.0'); ALTER TABLE GROUPS ALTER COLUMN ID RESTART WITH 3; +INSERT INTO ORGANIZATIONS (UUID, KEE, NAME, GUARDED, DEFAULT_GROUP_ID, CREATED_AT, UPDATED_AT) VALUES ('AVdqnciQUUs7Zd3KPvFD', 'default-organization', 'Default Organization', true, 2, '1474962596482', '1474962596482'); +INSERT INTO INTERNAL_PROPERTIES (KEE, IS_EMPTY, TEXT_VALUE, CREATED_AT) VALUES ('organization.default', false, 'AVdqnciQUUs7Zd3KPvFD', '1474962596482'); + INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (1, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'admin'); INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (2, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'profileadmin'); INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (3, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'gateadmin'); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java index 04c60066855..2784cb84469 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java @@ -52,6 +52,7 @@ public class DbVersion64 implements DbVersion { .add(1621, "Set all users into 'sonar-users' group", SetAllUsersIntoSonarUsersGroup.class) .add(1622, "Create 'Members' group in each organization", CreateMembersGroupsInEachOrganization.class) .add(1623, "Set organization members into 'Members' group", SetOrganizationMembersIntoMembersGroup.class) - .add(1624, "Add ORGANIZATIONS.DEFAULT_GROUP_ID", AddDefaultGroupIdToOrganizations.class); + .add(1624, "Add ORGANIZATIONS.DEFAULT_GROUP_ID", AddDefaultGroupIdToOrganizations.class) + .add(1625, "Populate column ORGANIZATIONS.DEFAULT_GROUP_ID", PopulateColumnDefaultGroupIdOfOrganizations.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizations.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizations.java new file mode 100644 index 00000000000..cbf62b1624e --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizations.java @@ -0,0 +1,63 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v64; + +import java.sql.SQLException; +import org.sonar.api.utils.System2; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class PopulateColumnDefaultGroupIdOfOrganizations extends DataChange { + + private final System2 system2; + + public PopulateColumnDefaultGroupIdOfOrganizations(Database db, System2 system2) { + super(db); + this.system2 = system2; + } + + @Override + protected void execute(Context context) throws SQLException { + populateDefaultGroupId(context, isOrganizationEnabled(context) ? "Members" : "sonar-users"); + } + + private void populateDefaultGroupId(Context context, String groupName) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate().rowPluralName("organizations"); + massUpdate.select("SELECT o.uuid, g.id FROM organizations o " + + "INNER JOIN groups g ON g.organization_uuid=o.uuid AND g.name=? " + + "WHERE o.default_group_id IS NULL") + .setString(1, groupName); + massUpdate.update("UPDATE organizations SET default_group_id=?,updated_at=? WHERE uuid=?"); + massUpdate.execute((row, update) -> { + update.setLong(1, row.getLong(2)); + update.setLong(2, system2.now()); + update.setString(3, row.getString(1)); + return true; + }); + } + + private static boolean isOrganizationEnabled(Context context) throws SQLException { + Boolean result = context.prepareSelect("SELECT text_value FROM internal_properties WHERE kee=?") + .setString(1, "organization.enabled") + .get(row -> "true".equals(row.getString(1))); + return result != null ? result : false; + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java index 212e50f88b2..2d1c5c54130 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java @@ -35,7 +35,7 @@ public class DbVersion64Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 25); + verifyMigrationCount(underTest, 26); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizationsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizationsTest.java new file mode 100644 index 00000000000..54f49721f6b --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizationsTest.java @@ -0,0 +1,180 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v64; + +import java.util.Date; +import java.util.List; +import java.util.stream.Collectors; +import javax.annotation.Nullable; +import org.assertj.core.groups.Tuple; +import org.junit.Before; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.db.CoreDbTester; + +import static java.lang.String.format; +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.tuple; +import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.when; + +public class PopulateColumnDefaultGroupIdOfOrganizationsTest { + + private static final long PAST = 100_000_000_000L; + private static final long NOW = 500_000_000_000L; + + private static final String DEFAULT_ORGANIZATION_UUID = "def-org"; + private static final String ORGANIZATION_1 = "ORGANIZATION_1"; + private static final String ORGANIZATION_2 = "ORGANIZATION_2"; + private static final String SONAR_USERS_NAME = "sonar-users"; + private static final String MEMBERS_NAME = "Members"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(PopulateColumnDefaultGroupIdOfOrganizationsTest.class, "initial.sql"); + + private System2 system2 = mock(System2.class); + + private PopulateColumnDefaultGroupIdOfOrganizations underTest = new PopulateColumnDefaultGroupIdOfOrganizations(db.database(), system2); + + @Before + public void setUp() throws Exception { + when(system2.now()).thenReturn(NOW); + } + + @Test + public void set_sonar_users_group_id_on_default_organization_when_organization_disabled() throws Exception { + setupDefaultOrganization(); + long groupId = insertGroup(DEFAULT_ORGANIZATION_UUID, SONAR_USERS_NAME); + + underTest.execute(); + + checkOrganizations(tuple(DEFAULT_ORGANIZATION_UUID, groupId, NOW)); + } + + @Test + public void set_members_group_id_on_organizations_when_organization_enabled() throws Exception { + setupDefaultOrganization(); + enableOrganization(); + long group1 = insertGroup(ORGANIZATION_1, MEMBERS_NAME); + insertOrganization(ORGANIZATION_1, null); + long group2 = insertGroup(ORGANIZATION_2, MEMBERS_NAME); + insertOrganization(ORGANIZATION_2, null); + + underTest.execute(); + + checkOrganizations(tuple(ORGANIZATION_1, group1, NOW), tuple(ORGANIZATION_2, group2, NOW), tuple(DEFAULT_ORGANIZATION_UUID, null, PAST)); + } + + @Test + public void does_nothing_when_default_group_id_already_set() throws Exception { + setupDefaultOrganization(); + enableOrganization(); + long group1 = insertGroup(ORGANIZATION_1, MEMBERS_NAME); + insertOrganization(ORGANIZATION_1, group1); + long group2 = insertGroup(ORGANIZATION_2, MEMBERS_NAME); + insertOrganization(ORGANIZATION_2, group2); + + underTest.execute(); + + checkOrganizations(tuple(ORGANIZATION_1, group1, PAST), tuple(ORGANIZATION_2, group2, PAST), tuple(DEFAULT_ORGANIZATION_UUID, null, PAST)); + } + + @Test + public void set_members_group_id_on_organizations_only_when_not_already_et() throws Exception { + setupDefaultOrganization(); + enableOrganization(); + long group1 = insertGroup(ORGANIZATION_1, MEMBERS_NAME); + insertOrganization(ORGANIZATION_1, null); + long group2 = insertGroup(ORGANIZATION_2, MEMBERS_NAME); + insertOrganization(ORGANIZATION_2, group2); + + underTest.execute(); + + checkOrganizations(tuple(ORGANIZATION_1, group1, NOW), tuple(ORGANIZATION_2, group2, PAST), tuple(DEFAULT_ORGANIZATION_UUID, null, PAST)); + } + + @Test + public void migration_is_reentrant() throws Exception { + setupDefaultOrganization(); + enableOrganization(); + long group1 = insertGroup(ORGANIZATION_1, MEMBERS_NAME); + insertOrganization(ORGANIZATION_1, null); + long group2 = insertGroup(ORGANIZATION_2, MEMBERS_NAME); + insertOrganization(ORGANIZATION_2, null); + + underTest.execute(); + checkOrganizations(tuple(ORGANIZATION_1, group1, NOW), tuple(ORGANIZATION_2, group2, NOW), tuple(DEFAULT_ORGANIZATION_UUID, null, PAST)); + + underTest.execute(); + checkOrganizations(tuple(ORGANIZATION_1, group1, NOW), tuple(ORGANIZATION_2, group2, NOW), tuple(DEFAULT_ORGANIZATION_UUID, null, PAST)); + } + + private void checkOrganizations(Tuple... expectedTuples) { + List tuples = db.select("select o.uuid, o.default_group_id, o.updated_at from organizations o").stream() + .map(map -> new Tuple(map.get("UUID"), map.get("DEFAULT_GROUP_ID"), map.get("UPDATED_AT"))) + .collect(Collectors.toList()); + assertThat(tuples).containsOnly(expectedTuples); + } + + private void insertOrganization(String uuid, @Nullable Long defaultGroupId) { + db.executeInsert( + "ORGANIZATIONS", + "UUID", uuid, + "KEE", uuid, + "NAME", uuid, + "DEFAULT_GROUP_ID", defaultGroupId, + "GUARDED", "false", + "CREATED_AT", PAST, + "UPDATED_AT", PAST); + } + + private long insertGroup(String organization, String name) { + db.executeInsert( + "GROUPS", + "NAME", name, + "DESCRIPTION", name, + "ORGANIZATION_UUID", organization, + "CREATED_AT", new Date(PAST), + "UPDATED_AT", new Date(PAST)); + return (Long) db.selectFirst(format("select id from groups where name='%s' and organization_uuid='%s'", name, organization)).get("ID"); + } + + private void setupDefaultOrganization() { + db.executeInsert("ORGANIZATIONS", + "UUID", DEFAULT_ORGANIZATION_UUID, + "KEE", DEFAULT_ORGANIZATION_UUID, "NAME", + DEFAULT_ORGANIZATION_UUID, "GUARDED", false, + "CREATED_AT", PAST, + "UPDATED_AT", PAST); + db.executeInsert("INTERNAL_PROPERTIES", + "KEE", "organization.default", + "IS_EMPTY", "false", + "TEXT_VALUE", DEFAULT_ORGANIZATION_UUID); + } + + private void enableOrganization() { + db.executeInsert("INTERNAL_PROPERTIES", + "KEE", "organization.enabled", + "IS_EMPTY", "false", + "TEXT_VALUE", "true"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizationsTest/initial.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizationsTest/initial.sql new file mode 100644 index 00000000000..edee0316d50 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/PopulateColumnDefaultGroupIdOfOrganizationsTest/initial.sql @@ -0,0 +1,35 @@ +CREATE TABLE "ORGANIZATIONS" ( + "UUID" VARCHAR(40) NOT NULL PRIMARY KEY, + "KEE" VARCHAR(32) NOT NULL, + "NAME" VARCHAR(64) NOT NULL, + "DESCRIPTION" VARCHAR(256), + "URL" VARCHAR(256), + "AVATAR_URL" VARCHAR(256), + "GUARDED" BOOLEAN NOT NULL, + "USER_ID" INTEGER, + "DEFAULT_PERM_TEMPLATE_PROJECT" VARCHAR(40), + "DEFAULT_PERM_TEMPLATE_VIEW" VARCHAR(40), + "DEFAULT_GROUP_ID" INTEGER, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +CREATE UNIQUE INDEX "PK_ORGANIZATIONS" ON "ORGANIZATIONS" ("UUID"); +CREATE UNIQUE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE"); + +CREATE TABLE "GROUPS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "NAME" VARCHAR(500), + "DESCRIPTION" VARCHAR(200), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "INTERNAL_PROPERTIES" ( + "KEE" VARCHAR(50) NOT NULL PRIMARY KEY, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" VARCHAR(4000), + "CLOB_VALUE" CLOB, + "CREATED_AT" BIGINT +); +CREATE UNIQUE INDEX "UNIQ_INTERNAL_PROPERTIES" ON "INTERNAL_PROPERTIES" ("KEE"); -- 2.39.5