From 55f378b984dbd0442f72d0c0f003cf5791c6062c Mon Sep 17 00:00:00 2001 From: Teryk Bellahsene Date: Mon, 27 Jun 2016 23:18:38 +0200 Subject: [PATCH] SONAR-7794 Populate DB column RULES_PROFILES.USER_UPDATED_AT --- ...ulate_user_updated_at_of_rules_profiles.rb | 28 +++++ .../org/sonar/db/version/DatabaseVersion.java | 2 +- .../sonar/db/version/MigrationStepModule.java | 2 + .../PopulateUserUpdatedAtOfRulesProfiles.java | 92 ++++++++++++++ .../org/sonar/db/version/rows-h2.sql | 2 + .../db/version/MigrationStepModuleTest.java | 2 +- ...ulateUserUpdatedAtOfRulesProfilesTest.java | 119 ++++++++++++++++++ .../schema.sql | 25 ++++ 8 files changed, 270 insertions(+), 2 deletions(-) create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1263_populate_user_updated_at_of_rules_profiles.rb create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfiles.java create mode 100644 sonar-db/src/test/java/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfilesTest.java create mode 100644 sonar-db/src/test/resources/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfilesTest/schema.sql diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1263_populate_user_updated_at_of_rules_profiles.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1263_populate_user_updated_at_of_rules_profiles.rb new file mode 100644 index 00000000000..44aea88e73d --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1263_populate_user_updated_at_of_rules_profiles.rb @@ -0,0 +1,28 @@ +# +# SonarQube, open source software quality management tool. +# Copyright (C) 2008-2014 SonarSource +# mailto:contact AT sonarsource DOT com +# +# SonarQube 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. +# +# SonarQube 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. +# +# +# SonarQube 6.0 +# SONAR-7794 +# +class PopulateUserUpdatedAtOfRulesProfiles < ActiveRecord::Migration + def self.up + execute_java_migration('org.sonar.db.version.v60.PopulateUserUpdatedAtOfRulesProfiles') + end +end diff --git a/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java b/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java index 857ced783b4..7146d737095 100644 --- a/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java +++ b/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java @@ -30,7 +30,7 @@ import org.sonar.db.MyBatis; public class DatabaseVersion { - public static final int LAST_VERSION = 1_262; + public static final int LAST_VERSION = 1_263; /** * The minimum supported version which can be upgraded. Lower diff --git a/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java b/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java index e975696a8a4..6912c7ca6a1 100644 --- a/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java +++ b/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java @@ -131,6 +131,7 @@ import org.sonar.db.version.v60.PopulateComponentUuidOfDuplicationsIndex; import org.sonar.db.version.v60.PopulateComponentUuidOfMeasures; import org.sonar.db.version.v60.PopulateLastUsedColumnOfRulesProfiles; import org.sonar.db.version.v60.PopulateProfileKeyOfActivities; +import org.sonar.db.version.v60.PopulateUserUpdatedAtOfRulesProfiles; import org.sonar.db.version.v60.PopulateUuidColumnOnSnapshots; import org.sonar.db.version.v60.PopulateUuidColumnsOfProjects; import org.sonar.db.version.v60.PopulateUuidColumnsOfResourceIndex; @@ -249,6 +250,7 @@ public class MigrationStepModule extends Module { PopulateProfileKeyOfActivities.class, MakeProfileKeyNotNullOnActivities.class, AddUserUpdatedAtToRulesProfiles.class, + PopulateUserUpdatedAtOfRulesProfiles.class, // SNAPSHOTS.UUID AddUuidColumnToSnapshots.class, diff --git a/sonar-db/src/main/java/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfiles.java b/sonar-db/src/main/java/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfiles.java new file mode 100644 index 00000000000..eddf95aecac --- /dev/null +++ b/sonar-db/src/main/java/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfiles.java @@ -0,0 +1,92 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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.db.version.v60; + +import com.google.common.base.Throwables; +import java.sql.SQLException; +import java.util.Date; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import javax.annotation.CheckForNull; +import org.sonar.db.Database; +import org.sonar.db.version.BaseDataChange; +import org.sonar.db.version.MassUpdate; +import org.sonar.db.version.Select; +import org.sonar.db.version.SqlStatement; + +public class PopulateUserUpdatedAtOfRulesProfiles extends BaseDataChange { + + private static final String SQL_SELECT_PROFILES_NOT_UPDATED = "select kee from rules_profiles where user_updated_at is null"; + + public PopulateUserUpdatedAtOfRulesProfiles(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + Map userUpdatedAtByProfileKeys = buildUserUpdatedAtMap(context); + populateUserUpdatedAtColumn(context, userUpdatedAtByProfileKeys); + } + + private static Map buildUserUpdatedAtMap(Context context) throws SQLException { + Map lastAnalysisDatesByQPKeys = new HashMap<>(); + List profileKeys = context.prepareSelect(SQL_SELECT_PROFILES_NOT_UPDATED).list(row -> row.getString(1)); + profileKeys.forEach(profileKey -> lastAnalysisDatesByQPKeys.put(profileKey, getUserUpdateAt(context, profileKey))); + + return lastAnalysisDatesByQPKeys; + } + + @CheckForNull + private static Long getUserUpdateAt(Context context, String profileKey) { + try { + return context.prepareSelect("select created_at as \"createdAt\" " + + "from activities " + + "where user_login is not null " + + " and profile_key=? " + + "order by created_at DESC ") + .setString(1, profileKey) + .get(row -> { + Date userUpdatedAt = row.getNullableDate(1); + return userUpdatedAt == null ? null : userUpdatedAt.getTime(); + }); + } catch (SQLException e) { + throw Throwables.propagate(e); + } + } + + private static void populateUserUpdatedAtColumn(Context context, Map userUpdatedAdByProfileKey) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select(SQL_SELECT_PROFILES_NOT_UPDATED); + massUpdate.update("update rules_profiles set user_updated_at=? where kee=?"); + massUpdate.rowPluralName("quality profiles"); + massUpdate.execute((row, update) -> handle(userUpdatedAdByProfileKey, row, update)); + } + + private static boolean handle(Map userUpdatedAtByProfileKey, Select.Row row, SqlStatement update) throws SQLException { + String profileKey = row.getString(1); + + update.setLong(1, userUpdatedAtByProfileKey.get(profileKey)); + update.setString(2, profileKey); + + return true; + } +} diff --git a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql index 0fd0413b98b..d6f8d91b246 100644 --- a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql +++ b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql @@ -468,6 +468,8 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1258'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1259'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1260'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1261'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1262'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1263'); INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '1418215735482', '1418215735482'); ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; diff --git a/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java b/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java index 8de343972a9..2d35e024f38 100644 --- a/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java +++ b/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java @@ -29,6 +29,6 @@ public class MigrationStepModuleTest { public void verify_count_of_added_MigrationStep_types() { ComponentContainer container = new ComponentContainer(); new MigrationStepModule().configure(container); - assertThat(container.size()).isEqualTo(118); + assertThat(container.size()).isEqualTo(119); } } diff --git a/sonar-db/src/test/java/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfilesTest.java b/sonar-db/src/test/java/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfilesTest.java new file mode 100644 index 00000000000..bf9da85b2b1 --- /dev/null +++ b/sonar-db/src/test/java/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfilesTest.java @@ -0,0 +1,119 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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.db.version.v60; + +import com.google.common.base.Throwables; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Timestamp; +import javax.annotation.CheckForNull; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +import static java.lang.String.valueOf; +import static org.assertj.core.api.Assertions.assertThat; + +public class PopulateUserUpdatedAtOfRulesProfilesTest { + private static final String TABLE_QUALITY_PROFILES = "rules_profiles"; + private static final String TABLE_ACTIVITIES = "activities"; + + @Rule + public DbTester db = DbTester.createForSchema(System2.INSTANCE, PopulateUserUpdatedAtOfRulesProfilesTest.class, "schema.sql"); + + PopulateUserUpdatedAtOfRulesProfiles underTest = new PopulateUserUpdatedAtOfRulesProfiles(db.database()); + + @Test + public void migration_has_no_effect_on_empty_tables() throws SQLException { + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_QUALITY_PROFILES)).isEqualTo(0); + assertThat(db.countRowsOfTable(TABLE_ACTIVITIES)).isEqualTo(0); + } + + @Test + public void migration_update_quality_profiles_user_updated_at() throws SQLException { + + insertQualityProfile(1, "first-quality-profile"); + insertActivity("first-quality-profile", "my-login", 1_000_000_00L); + insertActivity("first-quality-profile", null, 2_000_000_000L); + insertActivity("first-quality-profile", "my-login", 1_100_000_000L); + insertQualityProfile(2, "second-quality-profile"); + insertActivity("second-quality-profile", null, 1_000_000_00L); + insertQualityProfile(3, "third-quality-profile"); + insertQualityProfile(4, "fourth-quality-profile"); + insertActivity("fourth-quality-profile", "my-login", 1_000_000_00L); + + underTest.execute(); + + assertUserUpdatedAt("first-quality-profile", 1_100_000_000L); + assertNoUserUpdatedAtDate("second-quality-profile"); + assertNoUserUpdatedAtDate("third-quality-profile"); + assertUserUpdatedAt("fourth-quality-profile", 1_000_000_00L); + } + + @Test + public void migration_is_reentrant() throws SQLException { + insertQualityProfile(1, "first-quality-profile"); + insertActivity("first-quality-profile", "my-login", 1_000_000_000L); + + underTest.execute(); + assertUserUpdatedAt("first-quality-profile", 1_000_000_000L); + + underTest.execute(); + assertUserUpdatedAt("first-quality-profile", 1_000_000_000L); + } + + private void assertUserUpdatedAt(String qualityProfileKey, long expectedLastUsed) { + assertThat(selectUserUpdatedAt(qualityProfileKey)).isEqualTo(expectedLastUsed); + } + + private void assertNoUserUpdatedAtDate(String qualityProfileKey) { + assertThat(selectUserUpdatedAt(qualityProfileKey)).isNull(); + } + + @CheckForNull + private Long selectUserUpdatedAt(String qualityProfileKey) { + return (Long) db.selectFirst(String.format("select user_updated_at as \"userUpdatedAt\" from rules_profiles where kee ='%s'", qualityProfileKey)).get("userUpdatedAt"); + } + + private void insertActivity(String profileKey, @Nullable String login, @Nullable Long createdAt) { + final String sqlInsertActivity = "insert into activities (profile_key, user_login, created_at) values (?, ?, ?) "; + try (PreparedStatement ps = db.getSession().getConnection().prepareStatement(sqlInsertActivity)) { + ps.setString(1, profileKey); + ps.setString(2, login); + ps.setTimestamp(3, createdAt == null ? null : new Timestamp(createdAt)); + ps.executeUpdate(); + db.getSession().commit(); + } catch (SQLException e) { + throw Throwables.propagate(e); + } + } + + private void insertQualityProfile(long id, String key) { + db.executeInsert(TABLE_QUALITY_PROFILES, + "id", valueOf(id), + "name", key, + "kee", key); + } +} diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfilesTest/schema.sql b/sonar-db/src/test/resources/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfilesTest/schema.sql new file mode 100644 index 00000000000..fa7acc48f55 --- /dev/null +++ b/sonar-db/src/test/resources/org/sonar/db/version/v60/PopulateUserUpdatedAtOfRulesProfilesTest/schema.sql @@ -0,0 +1,25 @@ +CREATE TABLE "ACTIVITIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "LOG_KEY" VARCHAR(250), + "PROFILE_KEY" VARCHAR(255) NOT NULL, + "CREATED_AT" TIMESTAMP, + "USER_LOGIN" VARCHAR(255), + "LOG_TYPE" VARCHAR(250), + "LOG_ACTION" VARCHAR(250), + "LOG_MESSAGE" VARCHAR(250), + "DATA_FIELD" CLOB(2147483647) +); + +CREATE TABLE "RULES_PROFILES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "LANGUAGE" VARCHAR(20), + "KEE" VARCHAR(255) NOT NULL, + "PARENT_KEE" VARCHAR(255), + "RULES_UPDATED_AT" VARCHAR(100), + "IS_DEFAULT" BOOLEAN NOT NULL DEFAULT FALSE, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP, + "LAST_USED" BIGINT, + "USER_UPDATED_AT" BIGINT +); -- 2.39.5