From 38eaab33b92cbf57d44495a29a60f21a830bb102 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Thu, 1 Sep 2016 17:38:05 +0200 Subject: [PATCH] SONAR-7676 refactor table PROPERTIES to boolean+varchar+clob columns --- .../main/webapp/WEB-INF/config/environment.rb | 8 + .../migrate/1312_create_table_properties_2.rb | 38 ++ .../1313_populate_table_properties_2.rb | 29 ++ .../db/migrate/1314_drop_table_properties.rb | 31 ++ ..._rename_table_properties2_to_properties.rb | 42 ++ .../org/sonar/db/version/DatabaseVersion.java | 2 +- .../sonar/db/version/MigrationStepModule.java | 2 + .../version/v61/PopulateTableProperties2.java | 84 ++++ .../org/sonar/db/version/rows-h2.sql | 4 + .../org/sonar/db/version/schema-h2.ddl | 9 +- .../db/version/MigrationStepModuleTest.java | 2 +- .../v61/PopulateTableProperties2Test.java | 406 ++++++++++++++++++ .../properties_and_properties_2_tables.sql | 20 + 13 files changed, 672 insertions(+), 5 deletions(-) create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1312_create_table_properties_2.rb create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1313_populate_table_properties_2.rb create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1314_drop_table_properties.rb create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1315_rename_table_properties2_to_properties.rb create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v61/PopulateTableProperties2.java create mode 100644 sonar-db/src/test/java/org/sonar/db/version/v61/PopulateTableProperties2Test.java create mode 100644 sonar-db/src/test/resources/org/sonar/db/version/v61/PopulateTableProperties2Test/properties_and_properties_2_tables.sql diff --git a/server/sonar-web/src/main/webapp/WEB-INF/config/environment.rb b/server/sonar-web/src/main/webapp/WEB-INF/config/environment.rb index c074b06be76..cd529e41506 100644 --- a/server/sonar-web/src/main/webapp/WEB-INF/config/environment.rb +++ b/server/sonar-web/src/main/webapp/WEB-INF/config/environment.rb @@ -274,6 +274,14 @@ class ActiveRecord::Migration Java::OrgSlf4j::LoggerFactory::getLogger('DbMigration').info(text) if verbose end + def self.drop_index_quietly(table, index) + begin + remove_index table, :name => index + rescue + #ignore + end + end + private diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1312_create_table_properties_2.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1312_create_table_properties_2.rb new file mode 100644 index 00000000000..dc8456c9924 --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1312_create_table_properties_2.rb @@ -0,0 +1,38 @@ +# +# 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.1 +# +class CreateTableProperties2 < ActiveRecord::Migration + + def self.up + create_table 'properties2' do |t| + t.column 'prop_key', :string, :limit => 512, :null => false + t.column :resource_id, :integer, :null => true + t.column :user_id, :integer, :null => true + t.column 'is_empty', :boolean, :null => false + t.column :text_value, :string, :limit => 4000, :null => true + t.column :clob_value, :text, :null => true + t.column 'created_at', :big_integer, :null => false + end + add_varchar_index :properties2, :prop_key, :name => 'properties2_key' + end +end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1313_populate_table_properties_2.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1313_populate_table_properties_2.rb new file mode 100644 index 00000000000..5e99a96909a --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1313_populate_table_properties_2.rb @@ -0,0 +1,29 @@ +# +# 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.1 +# +class PopulateTableProperties2 < ActiveRecord::Migration + + def self.up + execute_java_migration('org.sonar.db.version.v61.PopulateTableProperties2') + end +end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1314_drop_table_properties.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1314_drop_table_properties.rb new file mode 100644 index 00000000000..ddb50e42302 --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1314_drop_table_properties.rb @@ -0,0 +1,31 @@ +# +# 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.1 +# +class DropTableProperties < ActiveRecord::Migration + + def self.up + drop_index_quietly :properties, :properties_key + drop_table :properties + end + +end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1315_rename_table_properties2_to_properties.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1315_rename_table_properties2_to_properties.rb new file mode 100644 index 00000000000..bacb08a968a --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1315_rename_table_properties2_to_properties.rb @@ -0,0 +1,42 @@ +# +# 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.1 +# +class RenameTableProperties2ToProperties < ActiveRecord::Migration + + def self.up + drop_index_quietly :properties2, :properties2_key + rename_table_quietly :properties2, :properties + add_varchar_index :properties, :prop_key, :name => 'properties_key' + end + + private + + def self.rename_table_quietly(oldName, newName) + begin + rename_table oldName, newName + rescue + #ignore + end + 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 ec8736608ac..223a31f3da1 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_311; + public static final int LAST_VERSION = 1_315; /** * 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 deae1edc772..e1efe08001b 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 @@ -149,6 +149,7 @@ import org.sonar.db.version.v61.AddErrorColumnsToCeActivity; import org.sonar.db.version.v61.DeleteProjectDashboards; import org.sonar.db.version.v61.DeleteReportsFromCeQueue; import org.sonar.db.version.v61.DropIsGlobalFromDashboards; +import org.sonar.db.version.v61.PopulateTableProperties2; import org.sonar.db.version.v61.RemoveViewsDefinitionFromProperties; import org.sonar.db.version.v61.ShrinkModuleUuidPathOfProjects; @@ -320,6 +321,7 @@ public class MigrationStepModule extends Module { ShrinkModuleUuidPathOfProjects.class, AddBUuidPathToProjects.class, AddErrorColumnsToCeActivity.class, + PopulateTableProperties2.class, RemoveViewsDefinitionFromProperties.class); } } diff --git a/sonar-db/src/main/java/org/sonar/db/version/v61/PopulateTableProperties2.java b/sonar-db/src/main/java/org/sonar/db/version/v61/PopulateTableProperties2.java new file mode 100644 index 00000000000..58762c46dcf --- /dev/null +++ b/sonar-db/src/main/java/org/sonar/db/version/v61/PopulateTableProperties2.java @@ -0,0 +1,84 @@ +/* + * 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.v61; + +import java.sql.SQLException; +import org.sonar.api.utils.System2; +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 PopulateTableProperties2 extends BaseDataChange { + private final System2 system2; + + public PopulateTableProperties2(Database db, System2 system2) { + super(db); + this.system2 = system2; + } + + @Override + public void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("SELECT" + + " p.prop_key, p.resource_id, p.text_value, p.user_id" + + " from properties p" + + " left outer join properties2 p2" + + " on p2.prop_key=p.prop_key" + + " and (p2.resource_id=p.resource_id or (p2.resource_id is null and p.resource_id is null))" + + " and (p2.user_id=p.user_id or (p2.user_id is null and p.user_id is null))" + + " where" + + " p2.id is null" + + " order by p.id"); + massUpdate.update("insert into properties2" + + " (prop_key, resource_id, user_id, is_empty, text_value, clob_value, created_at)" + + " values " + + " (?, ?, ?, ?, ?, ?, ?)"); + massUpdate.rowPluralName("copy data from table properties into table properties2"); + massUpdate.execute(this::handle); + } + + private boolean handle(Select.Row row, SqlStatement update) throws SQLException { + String key = row.getString(1); + Integer resourceId = row.getNullableInt(2); + String value = row.getNullableString(3); + Integer userId = row.getNullableInt(4); + + update.setString(1, key); + update.setInt(2, resourceId); + update.setInt(3, userId); + if (value == null || value.isEmpty()) { + update.setBoolean(4, true); + update.setString(5, null); + update.setString(6, null); + } else if (value.length() > 4000) { + update.setBoolean(4, false); + update.setString(5, null); + update.setString(6, value); + } else { + update.setBoolean(4, false); + update.setString(5, value); + update.setString(6, null); + } + update.setLong(7, system2.now()); + 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 507c13be8f8..490f32a1030 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 @@ -496,6 +496,10 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1307'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1309'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1310'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1311'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1312'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1313'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1314'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1315'); 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/main/resources/org/sonar/db/version/schema-h2.ddl b/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl index a37f25ac7dc..ce442a99309 100644 --- a/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl +++ b/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl @@ -164,10 +164,13 @@ CREATE TABLE "QUALITY_GATE_CONDITIONS" ( CREATE TABLE "PROPERTIES" ( "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "PROP_KEY" VARCHAR(512), + "PROP_KEY" VARCHAR(512) NOT NULL, "RESOURCE_ID" INTEGER, - "TEXT_VALUE" CLOB(2147483647), - "USER_ID" INTEGER + "USER_ID" INTEGER, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" VARCHAR(4000), + "CLOB_VALUE" CLOB(2147483647), + "CREATED_AT" BIGINT ); CREATE TABLE "PROJECT_LINKS" ( 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 271e5209b59..d7acef90b52 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(133); + assertThat(container.size()).isEqualTo(134); } } diff --git a/sonar-db/src/test/java/org/sonar/db/version/v61/PopulateTableProperties2Test.java b/sonar-db/src/test/java/org/sonar/db/version/v61/PopulateTableProperties2Test.java new file mode 100644 index 00000000000..7bc68019971 --- /dev/null +++ b/sonar-db/src/test/java/org/sonar/db/version/v61/PopulateTableProperties2Test.java @@ -0,0 +1,406 @@ +/* + * 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.v61; + +import java.sql.SQLException; +import java.util.Map; +import java.util.Objects; +import javax.annotation.CheckForNull; +import javax.annotation.Nullable; +import org.assertj.core.api.AbstractAssert; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.db.DbSession; +import org.sonar.db.DbTester; + +import static java.lang.Boolean.FALSE; +import static java.lang.Boolean.TRUE; +import static java.lang.String.valueOf; +import static java.util.Objects.requireNonNull; +import static org.assertj.core.api.Assertions.assertThat; +import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.when; + +public class PopulateTableProperties2Test { + private static final String EMPTY_PROPERTY = ""; + private static final String VALUE_SMALL = "some small value"; + private static final String VALUE_SIZE_4000 = String.format("%1$4000.4000s", "*"); + private static final String VALUE_SIZE_4001 = VALUE_SIZE_4000 + "P"; + private static final long DATE_1 = 1_555_000L; + private static final long DATE_2 = 2_666_000L; + private static final long DATE_3 = 3_777_000L; + private static final long DATE_4 = 4_888_000L; + + private System2 system2 = mock(System2.class); + + @Rule + public DbTester dbTester = DbTester.createForSchema(system2, PopulateTableProperties2Test.class, + "properties_and_properties_2_tables.sql"); + + private PopulateTableProperties2 underTest = new PopulateTableProperties2(dbTester.database(), system2); + + @Test + public void migration_has_no_effect_on_empty_tables() throws SQLException { + underTest.execute(); + + assertThat(dbTester.countRowsOfTable("properties")).isEqualTo(0); + assertThat(dbTester.countRowsOfTable("properties2")).isEqualTo(0); + } + + @Test + public void migration_does_copy_again_properties_which_are_already_copied() throws SQLException { + insertProperty(1, VALUE_SMALL, null, null); + insertProperty(10, VALUE_SMALL, null, null); + insertProperty(2, VALUE_SMALL, null, 21); + insertProperty(20, VALUE_SMALL, null, 21); + insertProperty(3, VALUE_SMALL, 31, null); + insertProperty(30, VALUE_SMALL, 31, null); + + insertProperty2(1, VALUE_SMALL, null, null); + insertProperty2(2, VALUE_SMALL, null, 21); + insertProperty2(3, VALUE_SMALL, 31, null); + + assertThat(dbTester.countRowsOfTable("properties")).isEqualTo(6); + assertThat(dbTester.countRowsOfTable("properties2")).isEqualTo(3); + + underTest.execute(); + + assertThat(dbTester.countRowsOfTable("properties")).isEqualTo(6); + assertThat(dbTester.countRowsOfTable("properties2")).isEqualTo(6); + } + + @Test + public void migration_moves_global_properties() throws SQLException { + when(system2.now()).thenReturn(DATE_1, DATE_2, DATE_3, DATE_4); + + insertProperty(1, VALUE_SMALL, null, null); + insertProperty(2, EMPTY_PROPERTY, null, null); + insertProperty(3, VALUE_SIZE_4000, null, null); + insertProperty(4, VALUE_SIZE_4001, null, null); + + underTest.execute(); + + assertThat(dbTester.countRowsOfTable("properties")).isEqualTo(4); + assertThat(dbTester.countRowsOfTable("properties2")).isEqualTo(4); + + assertThatProperty2(1) + .hasNoResourceId() + .hasNoUserId() + .hasTextValue(VALUE_SMALL) + .hasCreatedAt(DATE_1); + assertThatProperty2(2) + .hasNoResourceId() + .hasNoUserId() + .isEmpty() + .hasCreatedAt(DATE_2); + assertThatProperty2(3) + .hasNoResourceId() + .hasNoUserId() + .hasTextValue(VALUE_SIZE_4000) + .hasCreatedAt(DATE_3); + assertThatProperty2(4) + .hasNoResourceId() + .hasNoUserId() + .hasClobValue(VALUE_SIZE_4001) + .hasCreatedAt(DATE_4); + } + + @Test + public void migration_moves_user_properties() throws SQLException { + when(system2.now()).thenReturn(DATE_1, DATE_2, DATE_3, DATE_4); + + insertProperty(1, VALUE_SMALL, null, 11); + insertProperty(2, EMPTY_PROPERTY, null, 12); + insertProperty(3, VALUE_SIZE_4000, null, 13); + insertProperty(4, VALUE_SIZE_4001, null, 14); + + underTest.execute(); + + assertThat(dbTester.countRowsOfTable("properties")).isEqualTo(4); + assertThat(dbTester.countRowsOfTable("properties2")).isEqualTo(4); + + assertThatProperty2(1) + .hasNoResourceId() + .hasUserId(11) + .hasTextValue(VALUE_SMALL) + .hasCreatedAt(DATE_1); + assertThatProperty2(2) + .hasNoResourceId() + .hasUserId(12) + .isEmpty() + .hasCreatedAt(DATE_2); + assertThatProperty2(3) + .hasNoResourceId() + .hasUserId(13) + .hasTextValue(VALUE_SIZE_4000) + .hasCreatedAt(DATE_3); + assertThatProperty2(4) + .hasNoResourceId() + .hasUserId(14) + .hasClobValue(VALUE_SIZE_4001) + .hasCreatedAt(DATE_4); + } + + @Test + public void migration_moves_component_properties() throws SQLException { + when(system2.now()).thenReturn(DATE_1, DATE_2, DATE_3, DATE_4); + + insertProperty(1, VALUE_SMALL, 11, null); + insertProperty(2, EMPTY_PROPERTY, 12, null); + insertProperty(3, VALUE_SIZE_4000, 13, null); + insertProperty(4, VALUE_SIZE_4001, 14, null); + + underTest.execute(); + + assertThat(dbTester.countRowsOfTable("properties")).isEqualTo(4); + assertThat(dbTester.countRowsOfTable("properties2")).isEqualTo(4); + + assertThatProperty2(1) + .hasResourceId(11) + .hasNoUserId() + .hasTextValue(VALUE_SMALL) + .hasCreatedAt(DATE_1); + assertThatProperty2(2) + .hasResourceId(12) + .hasNoUserId() + .isEmpty() + .hasCreatedAt(DATE_2); + assertThatProperty2(3) + .hasResourceId(13) + .hasNoUserId() + .hasTextValue(VALUE_SIZE_4000) + .hasCreatedAt(DATE_3); + assertThatProperty2(4) + .hasResourceId(14) + .hasNoUserId() + .hasClobValue(VALUE_SIZE_4001) + .hasCreatedAt(DATE_4); + } + + private void insertProperty(int idAndKey, String value, @Nullable Integer resourceId, @Nullable Integer userId) { + dbTester.executeInsert("PROPERTIES", + "id", valueOf(idAndKey), + "prop_key", valueOf(idAndKey), + "text_value", value, + "resource_id", resourceId == null ? null : valueOf(resourceId), + "user_id", userId == null ? null : valueOf(userId)); + dbTester.commit(); + } + + private void insertProperty2(int idAndKey, @Nullable String value, @Nullable Integer resourceId, @Nullable Integer userId) { + dbTester.executeInsert("PROPERTIES2", + "id", valueOf(idAndKey), + "prop_key", valueOf(idAndKey), + "resource_id", resourceId == null ? null : valueOf(resourceId), + "user_id", userId == null ? null : valueOf(userId), + "is_empty", valueOf(value == null || value.isEmpty()), + "text_value", value != null && value.length() <= 4000 ? value : null, + "clob_value", value != null && value.length() > 4000 ? value : null, + "created_at", valueOf(1_55555_555)); + dbTester.commit(); + } + + private Property2Assert assertThatProperty2(int key) { + return new Property2Assert(dbTester, dbTester.getSession(), valueOf(key)); + } + + private static class Property2Assert extends AbstractAssert { + + private Property2Assert(DbTester dbTester, DbSession dbSession, String internalPropertyKey) { + super(asInternalProperty(dbTester, dbSession, internalPropertyKey), Property2Assert.class); + } + + private static Property2 asInternalProperty(DbTester dbTester, DbSession dbSession, String key) { + Map row = dbTester.selectFirst( + dbSession, + "select" + + " user_id as \"userId\", resource_id as \"resourceId\", is_empty as \"isEmpty\", text_value as \"textValue\", clob_value as \"clobValue\", created_at as \"createdAt\"" + + " from properties2" + + " where prop_key='" + key + "'"); + return new Property2( + (Long) row.get("userId"), + (Long) row.get("resourceId"), + isEmpty(row), + (String) row.get("textValue"), + (String) row.get("clobValue"), + (Long) row.get("createdAt")); + } + + private static Boolean isEmpty(Map row) { + Object flag = row.get("isEmpty"); + if (flag instanceof Boolean) { + return (Boolean) flag; + } + if (flag instanceof Long) { + Long longBoolean = (Long) flag; + return longBoolean.equals(1L); + } + throw new IllegalArgumentException("Unsupported object type returned for column \"isEmpty\": " + flag.getClass()); + } + + public Property2Assert hasNoUserId() { + isNotNull(); + + if (actual.getUserId() != null) { + failWithMessage("Expected Property2 to have column USER_ID to be null but was <%s>", actual.getUserId()); + } + + return this; + } + + public Property2Assert hasUserId(long expected) { + isNotNull(); + + if (!Objects.equals(actual.getUserId(), expected)) { + failWithMessage("Expected Property2 to have column USER_ID to be <%s> but was <%s>", true, actual.getUserId()); + } + + return this; + } + + public Property2Assert hasNoResourceId() { + isNotNull(); + + if (actual.getResourceId() != null) { + failWithMessage("Expected Property2 to have column RESOURCE_ID to be null but was <%s>", actual.getResourceId()); + } + + return this; + } + + public Property2Assert hasResourceId(long expected) { + isNotNull(); + + if (!Objects.equals(actual.getResourceId(), expected)) { + failWithMessage("Expected Property2 to have column RESOURCE_ID to be <%s> but was <%s>", true, actual.getResourceId()); + } + + return this; + } + + public Property2Assert isEmpty() { + isNotNull(); + + if (!Objects.equals(actual.getEmpty(), TRUE)) { + failWithMessage("Expected Property2 to have column IS_EMPTY to be <%s> but was <%s>", true, actual.getEmpty()); + } + if (actual.getTextValue() != null) { + failWithMessage("Expected Property2 to have column TEXT_VALUE to be null but was <%s>", actual.getTextValue()); + } + if (actual.getClobValue() != null) { + failWithMessage("Expected Property2 to have column CLOB_VALUE to be null but was <%s>", actual.getClobValue()); + } + + return this; + } + + public Property2Assert hasTextValue(String expected) { + isNotNull(); + + if (!Objects.equals(actual.getTextValue(), requireNonNull(expected))) { + failWithMessage("Expected Property2 to have column TEXT_VALUE to be <%s> but was <%s>", expected, actual.getTextValue()); + } + if (actual.getClobValue() != null) { + failWithMessage("Expected Property2 to have column CLOB_VALUE to be null but was <%s>", actual.getClobValue()); + } + if (!Objects.equals(actual.getEmpty(), FALSE)) { + failWithMessage("Expected Property2 to have column IS_EMPTY to be <%s> but was <%s>", false, actual.getEmpty()); + } + + return this; + } + + public Property2Assert hasClobValue(String expected) { + isNotNull(); + + if (!Objects.equals(actual.getClobValue(), requireNonNull(expected))) { + failWithMessage("Expected Property2 to have column CLOB_VALUE to be <%s> but was <%s>", expected, actual.getClobValue()); + } + if (actual.getTextValue() != null) { + failWithMessage("Expected Property2 to have column TEXT_VALUE to be null but was <%s>", actual.getTextValue()); + } + if (!Objects.equals(actual.getEmpty(), FALSE)) { + failWithMessage("Expected Property2 to have column IS_EMPTY to be <%s> but was <%s>", false, actual.getEmpty()); + } + + return this; + } + + public Property2Assert hasCreatedAt(long expected) { + isNotNull(); + + if (!Objects.equals(actual.getCreatedAt(), expected)) { + failWithMessage("Expected Property2 to have column CREATED_AT to be <%s> but was <%s>", expected, actual.getCreatedAt()); + } + + return this; + } + + } + + private static final class Property2 { + private final Long userId; + private final Long resourceId; + private final Boolean empty; + private final String textValue; + private final String clobValue; + private final Long createdAt; + + private Property2(@Nullable Long userId, @Nullable Long resourceId, + @Nullable Boolean empty, @Nullable String textValue, @Nullable String clobValue, + @Nullable Long createdAt) { + this.userId = userId; + this.resourceId = resourceId; + this.empty = empty; + this.textValue = textValue; + this.clobValue = clobValue; + this.createdAt = createdAt; + } + + public Long getUserId() { + return userId; + } + + public Long getResourceId() { + return resourceId; + } + + @CheckForNull + public Boolean getEmpty() { + return empty; + } + + @CheckForNull + public String getTextValue() { + return textValue; + } + + @CheckForNull + public String getClobValue() { + return clobValue; + } + + @CheckForNull + public Long getCreatedAt() { + return createdAt; + } + } +} diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v61/PopulateTableProperties2Test/properties_and_properties_2_tables.sql b/sonar-db/src/test/resources/org/sonar/db/version/v61/PopulateTableProperties2Test/properties_and_properties_2_tables.sql new file mode 100644 index 00000000000..9781eda6197 --- /dev/null +++ b/sonar-db/src/test/resources/org/sonar/db/version/v61/PopulateTableProperties2Test/properties_and_properties_2_tables.sql @@ -0,0 +1,20 @@ +CREATE TABLE "PROPERTIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROP_KEY" VARCHAR(512), + "RESOURCE_ID" INTEGER, + "TEXT_VALUE" CLOB(2147483647), + "USER_ID" INTEGER +); +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY"); + +CREATE TABLE "PROPERTIES2" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROP_KEY" VARCHAR(512) NOT NULL, + "RESOURCE_ID" INTEGER, + "USER_ID" INTEGER, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" VARCHAR(4000), + "CLOB_VALUE" CLOB(2147483647), + "CREATED_AT" BIGINT +); +CREATE INDEX "PROPERTIES2_KEY" ON "PROPERTIES2" ("PROP_KEY"); -- 2.39.5