From 4e367ff196a8eea4ae02b8c954c2d4b0633f4590 Mon Sep 17 00:00:00 2001 From: Eric Giffon Date: Wed, 15 Feb 2023 15:56:20 +0100 Subject: [PATCH] SONAR-8219 Make users.user_local column not nullable --- server/sonar-db-dao/src/schema/schema-sq.ddl | 2 +- .../migration/version/v100/DbVersion100.java | 4 +- ...MakeColumnUserLocalNotNullableInUsers.java | 47 +++++++++ .../v100/UpdateUserLocalValueInUsers.java | 45 +++++++++ ...ColumnUserLocalNotNullableInUsersTest.java | 52 ++++++++++ .../v100/UpdateUserLocalValueInUsersTest.java | 96 +++++++++++++++++++ .../schema.sql | 27 ++++++ .../schema.sql | 27 ++++++ 8 files changed, 297 insertions(+), 3 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsers.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsers.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsersTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsersTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsersTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsersTest/schema.sql diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index ad8bc63ac84..cdab891a083 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -999,7 +999,7 @@ CREATE TABLE "USERS"( "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, + "USER_LOCAL" BOOLEAN NOT NULL, "HOMEPAGE_TYPE" CHARACTER VARYING(40), "HOMEPAGE_PARAMETER" CHARACTER VARYING(40), "LAST_CONNECTION_DATE" BIGINT, 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 727d281b2b9..9d0015944c8 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 @@ -49,8 +49,8 @@ public class DbVersion100 implements DbVersion { .add(10_0_005, "Drop column 'b_module_uuid_path' in the 'Components' table", DropBModuleUuidPathInComponents.class) .add(10_0_006, "Drop index 'projects_root_uuid' in the 'Components' table", DropIndexProjectsRootUuidInComponents.class) .add(10_0_007, "Drop column 'root_uuid' in the 'Components' table", DropRootUuidInComponents.class) - - + .add(10_0_008, "Update value of 'user_local' in the 'users' table", UpdateUserLocalValueInUsers.class) + .add(10_0_009, "Make column 'user_local' not nullable in the 'users' table", MakeColumnUserLocalNotNullableInUsers.class) ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsers.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsers.java new file mode 100644 index 00000000000..f8cf19bce51 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsers.java @@ -0,0 +1,47 @@ +/* + * 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.sonar.db.Database; +import org.sonar.server.platform.db.migration.def.BooleanColumnDef; +import org.sonar.server.platform.db.migration.sql.AlterColumnsBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class MakeColumnUserLocalNotNullableInUsers extends DdlChange { + private static final String TABLE_NAME = "users"; + private static final String COLUMN_NAME = "user_local"; + + private static final BooleanColumnDef columnDefinition = BooleanColumnDef.newBooleanColumnDefBuilder() + .setColumnName(COLUMN_NAME) + .setIsNullable(false) + .build(); + + public MakeColumnUserLocalNotNullableInUsers(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new AlterColumnsBuilder(getDialect(), TABLE_NAME) + .updateColumn(columnDefinition) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsers.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsers.java new file mode 100644 index 00000000000..cdd1d1ae5d0 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsers.java @@ -0,0 +1,45 @@ +/* + * 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.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class UpdateUserLocalValueInUsers extends DataChange { + + public UpdateUserLocalValueInUsers(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select uuid from users where user_local is null"); + massUpdate.update("update users set user_local = ? where uuid = ?"); + massUpdate.execute((row, update) -> { + String uuid = row.getString(1); + update.setBoolean(1, true); + update.setString(2, uuid); + return true; + }); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsersTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsersTest.java new file mode 100644 index 00000000000..c6bd8d7f59f --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsersTest.java @@ -0,0 +1,52 @@ +/* + * 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 java.sql.Types.BOOLEAN; + +public class MakeColumnUserLocalNotNullableInUsersTest { + private static final String TABLE_NAME = "users"; + private static final String COLUMN_NAME = "user_local"; + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(MakeColumnUserLocalNotNullableInUsersTest.class, "schema.sql"); + + private final MakeColumnUserLocalNotNullableInUsers underTest = new MakeColumnUserLocalNotNullableInUsers(db.database()); + + @Test + public void user_local_column_is_not_null() throws SQLException { + db.assertColumnDefinition(TABLE_NAME, COLUMN_NAME, BOOLEAN, null, true); + underTest.execute(); + db.assertColumnDefinition(TABLE_NAME, COLUMN_NAME, BOOLEAN, null, false); + } + + @Test + public void migration_is_reentrant() throws SQLException { + db.assertColumnDefinition(TABLE_NAME, COLUMN_NAME, BOOLEAN, null, true); + underTest.execute(); + underTest.execute(); + db.assertColumnDefinition(TABLE_NAME, COLUMN_NAME, BOOLEAN, null, false); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsersTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsersTest.java new file mode 100644 index 00000000000..336f51b958d --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsersTest.java @@ -0,0 +1,96 @@ +/* + * 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 java.util.HashMap; +import java.util.Map; +import java.util.stream.Collectors; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.core.util.UuidFactory; +import org.sonar.core.util.UuidFactoryFast; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DataChange; + +import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic; +import static org.apache.commons.lang.RandomStringUtils.randomNumeric; +import static org.assertj.core.api.Assertions.assertThat; + +public class UpdateUserLocalValueInUsersTest { + + private final UuidFactory uuidFactory = UuidFactoryFast.getInstance(); + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(UpdateUserLocalValueInUsersTest.class, "schema.sql"); + + private final DataChange underTest = new UpdateUserLocalValueInUsers(db.database()); + + @Test + public void migration_updates_user_local_if_null() throws SQLException { + String userUuid1 = insertUser(true); + String userUuid2 = insertUser(false); + String userUuid3 = insertUser(null); + + underTest.execute(); + + assertUserLocalIsUpdatedCorrectly(userUuid1, true); + assertUserLocalIsUpdatedCorrectly(userUuid2, false); + assertUserLocalIsUpdatedCorrectly(userUuid3, true); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + String userUuid1 = insertUser(true); + String userUuid2 = insertUser(false); + String userUuid3 = insertUser(null); + + underTest.execute(); + // re-entrant + underTest.execute(); + + assertUserLocalIsUpdatedCorrectly(userUuid1, true); + assertUserLocalIsUpdatedCorrectly(userUuid2, false); + assertUserLocalIsUpdatedCorrectly(userUuid3, true); + } + + private void assertUserLocalIsUpdatedCorrectly(String userUuid, boolean expected) { + String selectSql = String.format("select USER_LOCAL from users where uuid='%s'", userUuid); + assertThat(db.select(selectSql).stream() + .map(row -> row.get("USER_LOCAL")) + .collect(Collectors.toList())) + .containsExactlyInAnyOrder(expected); + } + + private String insertUser(Boolean userLocal) { + Map map = new HashMap<>(); + String uuid = uuidFactory.create(); + map.put("UUID", uuid); + map.put("LOGIN", randomAlphabetic(20)); + map.put("EXTERNAL_LOGIN", randomAlphabetic(20)); + map.put("EXTERNAL_IDENTITY_PROVIDER", "sonarqube"); + map.put("EXTERNAL_ID", randomNumeric(5)); + map.put("CREATED_AT", System.currentTimeMillis()); + map.put("USER_LOCAL", userLocal); + map.put("RESET_PASSWORD", false); + db.executeInsert("users", map); + return uuid; + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsersTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsersTest/schema.sql new file mode 100644 index 00000000000..134319f2309 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/MakeColumnUserLocalNotNullableInUsersTest/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, + "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/UpdateUserLocalValueInUsersTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsersTest/schema.sql new file mode 100644 index 00000000000..134319f2309 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v100/UpdateUserLocalValueInUsersTest/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, + "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); -- 2.39.5