From 9358faa2e70102c82ff103657e75688cf72b4af1 Mon Sep 17 00:00:00 2001 From: Belen Pruvost Date: Thu, 28 Oct 2021 16:42:30 +0200 Subject: [PATCH] SONAR-15587 - Add sonarlint_ad_seen boolean column to users table --- server/sonar-db-dao/src/schema/schema-sq.ddl | 3 +- .../AddSonarlintAdSeenColumnInUsersTable.java | 57 +++++++++++ .../db/migration/version/v92/DbVersion92.java | 4 +- .../v92/UpsertSonarlintAdSeenValue.java | 39 ++++++++ ...SonarlintAdSeenColumnInUsersTableTest.java | 54 +++++++++++ .../v92/UpsertSonarlintAdSeenValueTest.java | 95 +++++++++++++++++++ .../schema.sql | 26 +++++ .../UpsertSonarlintAdSeenValueTest/schema.sql | 27 ++++++ 8 files changed, 303 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValue.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValueTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValueTest/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 2d9f8ca32fd..7a9d8390bba 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -967,7 +967,8 @@ CREATE TABLE "USERS"( "CREATED_AT" BIGINT, "UPDATED_AT" BIGINT, "RESET_PASSWORD" BOOLEAN NOT NULL, - "LAST_SONARLINT_CONNECTION" BIGINT + "LAST_SONARLINT_CONNECTION" BIGINT, + "SONARLINT_AD_SEEN" BOOLEAN DEFAULT FALSE ); ALTER TABLE "USERS" ADD CONSTRAINT "PK_USERS" PRIMARY KEY("UUID"); CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS"("LOGIN"); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTable.java new file mode 100644 index 00000000000..1954b99c78d --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTable.java @@ -0,0 +1,57 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v92; + +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.def.BooleanColumnDef; +import org.sonar.server.platform.db.migration.sql.AddColumnsBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.BooleanColumnDef.newBooleanColumnDefBuilder; + +public class AddSonarlintAdSeenColumnInUsersTable extends DdlChange { + + private static final String TABLE_NAME = "users"; + private static final String COLUMN_NAME = "sonarlint_ad_seen"; + + private static final BooleanColumnDef columnDefinition = newBooleanColumnDefBuilder() + .setColumnName(COLUMN_NAME) + .setDefaultValue(false) + .build(); + + public AddSonarlintAdSeenColumnInUsersTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection c = getDatabase().getDataSource().getConnection()) { + if (!DatabaseUtils.tableColumnExists(c, TABLE_NAME, COLUMN_NAME)) { + context.execute(new AddColumnsBuilder(getDialect(), TABLE_NAME) + .addColumn(columnDefinition) + .build()); + } + } + } + +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/DbVersion92.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/DbVersion92.java index a9890d7b662..dc49c24d94c 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/DbVersion92.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/DbVersion92.java @@ -30,6 +30,8 @@ public class DbVersion92 implements DbVersion { .add(6102, "Migrate Bitbucket.org authentication plugin settings to built-in authentication settings", MigrateBibucketOrgPluginSettingsToBuiltInSettings.class) .add(6103, "Create column quick_fix_available in 'issues'", AddQuickFixAvailableColumnInIssuesTable.class) .add(6104, "Create qgate_user_permissions Table", CreateQGateUserPermissionsTable.class) - .add(6105, "Create qgate_group_permissions Table", CreateQGateGroupPermissionsTable.class); + .add(6105, "Create qgate_group_permissions Table", CreateQGateGroupPermissionsTable.class) + .add(6106, "Create column sonarlint_ad_seen in 'users'", AddSonarlintAdSeenColumnInUsersTable.class) + .add(6107, "Upsert value of sonarlint_ad_seen in 'users'", UpsertSonarlintAdSeenValue.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValue.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValue.java new file mode 100644 index 00000000000..cb1e72c3621 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValue.java @@ -0,0 +1,39 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v92; + +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.Upsert; + +public class UpsertSonarlintAdSeenValue extends DataChange { + + public UpsertSonarlintAdSeenValue(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + Upsert upsert = context.prepareUpsert("update users set sonarlint_ad_seen = true where last_sonarlint_connection is not null"); + upsert.execute(); + upsert.commit(); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTableTest.java new file mode 100644 index 00000000000..7b96d1ccc76 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTableTest.java @@ -0,0 +1,54 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v92; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static java.sql.Types.BOOLEAN; + +public class AddSonarlintAdSeenColumnInUsersTableTest { + + private final String TABLE = "users"; + private final String COLUMN = "sonarlint_ad_seen"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(AddSonarlintAdSeenColumnInUsersTableTest.class, "schema.sql"); + + private final DdlChange underTest = new AddSonarlintAdSeenColumnInUsersTable(db.database()); + + @Test + public void migration_should_add_column() throws SQLException { + underTest.execute(); + + db.assertColumnDefinition(TABLE, COLUMN, BOOLEAN, null, true); + } + + @Test + public void migration_is_reentrant() throws SQLException { + underTest.execute(); + underTest.execute(); + + db.assertColumnDefinition(TABLE, COLUMN, BOOLEAN, null, true); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValueTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValueTest.java new file mode 100644 index 00000000000..44bbdc6182e --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValueTest.java @@ -0,0 +1,95 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v92; + +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 UpsertSonarlintAdSeenValueTest { + + private final UuidFactory uuidFactory = UuidFactoryFast.getInstance(); + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(UpsertSonarlintAdSeenValueTest.class, "schema.sql"); + + private final DataChange underTest = new UpsertSonarlintAdSeenValue(db.database()); + + @Test + public void migration_populates_sonarlint_promotion_seen_based_on_last_sonarlint_connection() throws SQLException { + String userUuid1 = insertUserWithLastSonarlintConnection(true); + String userUuid2 = insertUserWithLastSonarlintConnection(false); + + underTest.execute(); + + assertSonarlintPromotionSeenIsUpsertedCorrectly(userUuid1, true); + assertSonarlintPromotionSeenIsUpsertedCorrectly(userUuid2, false); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + String userUuid1 = insertUserWithLastSonarlintConnection(true); + String userUuid2 = insertUserWithLastSonarlintConnection(false); + + underTest.execute(); + // re-entrant + underTest.execute(); + + assertSonarlintPromotionSeenIsUpsertedCorrectly(userUuid1, true); + assertSonarlintPromotionSeenIsUpsertedCorrectly(userUuid2, false); + } + + private void assertSonarlintPromotionSeenIsUpsertedCorrectly(String userUuid, boolean seen) { + String selectSql = String.format("select SONARLINT_AD_SEEN from users where uuid='%s'", userUuid); + assertThat(db.select(selectSql).stream().map(row -> row.get("SONARLINT_AD_SEEN")).collect(Collectors.toList())) + .containsExactlyInAnyOrder(seen); + } + + private String insertUserWithLastSonarlintConnection(boolean setLastSonarlintConnection) { + 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("IS_ROOT", false); + map.put("ONBOARDED", false); + map.put("CREATED_AT", System.currentTimeMillis()); + if (setLastSonarlintConnection) { + map.put("LAST_SONARLINT_CONNECTION", System.currentTimeMillis()); + } + 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/v92/AddSonarlintAdSeenColumnInUsersTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTableTest/schema.sql new file mode 100644 index 00000000000..016b663813d --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/AddSonarlintAdSeenColumnInUsersTableTest/schema.sql @@ -0,0 +1,26 @@ +CREATE TABLE "USERS"( + "UUID" VARCHAR(255) NOT NULL, + "LOGIN" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(200), + "EMAIL" VARCHAR(100), + "CRYPTED_PASSWORD" VARCHAR(100), + "SALT" VARCHAR(40), + "HASH_METHOD" VARCHAR(10), + "ACTIVE" BOOLEAN DEFAULT TRUE, + "SCM_ACCOUNTS" VARCHAR(4000), + "EXTERNAL_LOGIN" VARCHAR(255) NOT NULL, + "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100) NOT NULL, + "EXTERNAL_ID" VARCHAR(255) NOT NULL, + "IS_ROOT" BOOLEAN NOT NULL, + "USER_LOCAL" BOOLEAN, + "ONBOARDED" BOOLEAN NOT NULL, + "HOMEPAGE_TYPE" VARCHAR(40), + "HOMEPAGE_PARAMETER" VARCHAR(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"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValueTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValueTest/schema.sql new file mode 100644 index 00000000000..ba93d9755c9 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v92/UpsertSonarlintAdSeenValueTest/schema.sql @@ -0,0 +1,27 @@ +create TABLE "USERS"( + "UUID" VARCHAR(255) NOT NULL, + "LOGIN" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(200), + "EMAIL" VARCHAR(100), + "CRYPTED_PASSWORD" VARCHAR(100), + "SALT" VARCHAR(40), + "HASH_METHOD" VARCHAR(10), + "ACTIVE" BOOLEAN DEFAULT TRUE, + "SCM_ACCOUNTS" VARCHAR(4000), + "EXTERNAL_LOGIN" VARCHAR(255) NOT NULL, + "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100) NOT NULL, + "EXTERNAL_ID" VARCHAR(255) NOT NULL, + "IS_ROOT" BOOLEAN NOT NULL, + "USER_LOCAL" BOOLEAN, + "ONBOARDED" BOOLEAN NOT NULL, + "HOMEPAGE_TYPE" VARCHAR(40), + "HOMEPAGE_PARAMETER" VARCHAR(40), + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "RESET_PASSWORD" BOOLEAN NOT NULL, + "LAST_SONARLINT_CONNECTION" BIGINT, + "SONARLINT_AD_SEEN" BOOLEAN DEFAULT FALSE +); + +alter table "USERS" add CONSTRAINT "PK_USERS" PRIMARY KEY("UUID"); -- 2.39.5