From 26d47bfc36a0374c06569cf2a99fc9b8c9978c28 Mon Sep 17 00:00:00 2001 From: Simon Brandhof Date: Wed, 11 May 2016 17:14:09 +0200 Subject: [PATCH] SONAR-7630 Do not use MySQL TINYINT(1) for non-boolean columns --- .../platformlevel/PlatformLevel1.java | 2 - .../platformlevel/PlatformLevel2.java | 4 +- .../1150_fix_type_of_rule_type_on_mysql.rb | 31 +++++++ .../org/sonar/db/version/DatabaseVersion.java | 2 +- .../sonar/db/version/MigrationStepModule.java | 6 +- .../sonar/db/version/TinyIntColumnDef.java | 3 +- .../version/v56/FixTypeOfRuleTypeOnMysql.java | 54 +++++++++++ .../sonar/db/version/v56/package-info.java | 24 +++++ .../org/sonar/db/version/rows-h2.sql | 2 + .../db/version/MigrationStepModuleTest.java | 2 +- .../db/version/TinyIntColumnDefTest.java | 2 +- .../v56/FixTypeOfRuleTypeOnMysqlTest.java | 89 +++++++++++++++++++ 12 files changed, 213 insertions(+), 8 deletions(-) create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1150_fix_type_of_rule_type_on_mysql.rb create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v56/FixTypeOfRuleTypeOnMysql.java create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v56/package-info.java create mode 100644 sonar-db/src/test/java/org/sonar/db/version/v56/FixTypeOfRuleTypeOnMysqlTest.java diff --git a/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel1.java b/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel1.java index 6f2a5b1b667..94d41fd68d9 100644 --- a/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel1.java +++ b/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel1.java @@ -34,7 +34,6 @@ import org.sonar.db.DefaultDatabase; import org.sonar.db.purge.PurgeProfiler; import org.sonar.db.semaphore.SemaphoresImpl; import org.sonar.db.version.DatabaseVersion; -import org.sonar.db.version.MigrationStepModule; import org.sonar.server.app.ProcessCommandWrapperImpl; import org.sonar.server.app.RestartFlagHolderImpl; import org.sonar.server.db.EmbeddedDatabaseFactory; @@ -114,7 +113,6 @@ public class PlatformLevel1 extends PlatformLevel { org.sonar.core.properties.PropertiesDao.class); addAll(CorePropertyDefinitions.all()); addAll(CePropertyDefinitions.all()); - add(MigrationStepModule.class); } private void addExtraRootComponents() { diff --git a/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel2.java b/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel2.java index 496a3b049f8..996aa970dbe 100644 --- a/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel2.java +++ b/server/sonar-server/src/main/java/org/sonar/server/platform/platformlevel/PlatformLevel2.java @@ -25,6 +25,7 @@ import org.sonar.core.i18n.RuleI18nManager; import org.sonar.core.platform.PluginClassloaderFactory; import org.sonar.core.platform.PluginLoader; import org.sonar.db.charset.DatabaseCharsetChecker; +import org.sonar.db.version.MigrationStepModule; import org.sonar.server.db.CheckDatabaseCharsetAtStartup; import org.sonar.server.db.migrations.DatabaseMigrator; import org.sonar.server.db.migrations.PlatformDatabaseMigration; @@ -71,6 +72,7 @@ public class PlatformLevel2 extends PlatformLevel { // DB migration PlatformDatabaseMigrationExecutorServiceImpl.class, - PlatformDatabaseMigration.class); + PlatformDatabaseMigration.class, + MigrationStepModule.class); } } diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1150_fix_type_of_rule_type_on_mysql.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1150_fix_type_of_rule_type_on_mysql.rb new file mode 100644 index 00000000000..b892c080ea0 --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1150_fix_type_of_rule_type_on_mysql.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 5.6 +# SONAR-7630 +# +class FixTypeOfRuleTypeOnMysql < ActiveRecord::Migration + + def self.up + execute_java_migration('org.sonar.db.version.v56.FixTypeOfRuleTypeOnMysql') + 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 9ccbc32b410..b76fa141cef 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 = 1125; + public static final int LAST_VERSION = 1150; /** * 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 b91a331a198..5d462792e4b 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 @@ -81,6 +81,7 @@ import org.sonar.db.version.v55.FeedActiveRulesLongDateColumns; import org.sonar.db.version.v55.FeedIssueTypes; import org.sonar.db.version.v55.FeedRulesLongDateColumns; import org.sonar.db.version.v55.FeedRulesTypes; +import org.sonar.db.version.v56.FixTypeOfRuleTypeOnMysql; public class MigrationStepModule extends Module { @Override @@ -159,6 +160,9 @@ public class MigrationStepModule extends Module { FeedRulesTypes.class, DeleteMeasuresWithRuleId.class, DeleteManualIssues.class, - DeleteManualRules.class); + DeleteManualRules.class, + + // 5.6 + FixTypeOfRuleTypeOnMysql.class); } } diff --git a/sonar-db/src/main/java/org/sonar/db/version/TinyIntColumnDef.java b/sonar-db/src/main/java/org/sonar/db/version/TinyIntColumnDef.java index 013e3c6fbfe..0f1f8ac544a 100644 --- a/sonar-db/src/main/java/org/sonar/db/version/TinyIntColumnDef.java +++ b/sonar-db/src/main/java/org/sonar/db/version/TinyIntColumnDef.java @@ -46,7 +46,8 @@ public class TinyIntColumnDef extends AbstractColumnDef { case Oracle.ID: return "NUMBER(3)"; case MySql.ID: - return "TINYINT(1)"; + // do not use TINYINT(1) as it's considered as booleans by connector/J. + return "TINYINT(2)"; case MsSql.ID: case H2.ID: return "TINYINT"; diff --git a/sonar-db/src/main/java/org/sonar/db/version/v56/FixTypeOfRuleTypeOnMysql.java b/sonar-db/src/main/java/org/sonar/db/version/v56/FixTypeOfRuleTypeOnMysql.java new file mode 100644 index 00000000000..09820a00ee6 --- /dev/null +++ b/sonar-db/src/main/java/org/sonar/db/version/v56/FixTypeOfRuleTypeOnMysql.java @@ -0,0 +1,54 @@ +/* + * 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.v56; + +import java.sql.SQLException; +import org.sonar.api.platform.ServerUpgradeStatus; +import org.sonar.api.utils.log.Loggers; +import org.sonar.db.Database; +import org.sonar.db.dialect.MySql; +import org.sonar.db.version.DdlChange; + +public class FixTypeOfRuleTypeOnMysql extends DdlChange { + + private static final int SQ_5_5 = 1100; + private static final int SQ_5_6 = 1150; + + private final ServerUpgradeStatus dbVersion; + + public FixTypeOfRuleTypeOnMysql(Database db, ServerUpgradeStatus dbVersion) { + super(db); + this.dbVersion = dbVersion; + } + + @Override + public void execute(Context context) throws SQLException { + // In SQ 5.6, migration 1100 create columns with expected type TINYINT(2) + // In SQ 5.5, migration 1100 create columns with type TINYINT(1) instead of TINYINT(2) + // In SQ 5.4 and lower, the type TINYINT(1) was used only for boolean columns, so no problem + // As an optimization fix must be applied only for instances upgrading from 5.5.x + if (getDatabase().getDialect().getId().equals(MySql.ID) && + dbVersion.getInitialDbVersion() >= SQ_5_5 && dbVersion.getInitialDbVersion() < SQ_5_6) { + Loggers.get(getClass()).info("Changing TINYINT(1) to TINYINT(2)"); + context.execute("ALTER TABLE rules MODIFY COLUMN rule_type TINYINT (2)"); + context.execute("ALTER TABLE issues MODIFY COLUMN issue_type TINYINT (2)"); + } + } +} diff --git a/sonar-db/src/main/java/org/sonar/db/version/v56/package-info.java b/sonar-db/src/main/java/org/sonar/db/version/v56/package-info.java new file mode 100644 index 00000000000..c3a55a0eb13 --- /dev/null +++ b/sonar-db/src/main/java/org/sonar/db/version/v56/package-info.java @@ -0,0 +1,24 @@ +/* + * 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. + */ +@ParametersAreNonnullByDefault +package org.sonar.db.version.v56; + +import javax.annotation.ParametersAreNonnullByDefault; + 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 2378cba72de..34782850411 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 @@ -403,5 +403,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1123'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1124'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1125'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1150'); + INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT, REMEMBER_TOKEN, REMEMBER_TOKEN_EXPIRES_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '1418215735482', '1418215735482', null, null); 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 daaef18b788..20c4b0d3537 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(63); + assertThat(container.size()).isEqualTo(64); } } diff --git a/sonar-db/src/test/java/org/sonar/db/version/TinyIntColumnDefTest.java b/sonar-db/src/test/java/org/sonar/db/version/TinyIntColumnDefTest.java index da7b10c1b0d..406536629e9 100644 --- a/sonar-db/src/test/java/org/sonar/db/version/TinyIntColumnDefTest.java +++ b/sonar-db/src/test/java/org/sonar/db/version/TinyIntColumnDefTest.java @@ -59,7 +59,7 @@ public class TinyIntColumnDefTest { assertThat(def.generateSqlType(new H2())).isEqualTo("TINYINT"); assertThat(def.generateSqlType(new PostgreSql())).isEqualTo("SMALLINT"); assertThat(def.generateSqlType(new MsSql())).isEqualTo("TINYINT"); - assertThat(def.generateSqlType(new MySql())).isEqualTo("TINYINT(1)"); + assertThat(def.generateSqlType(new MySql())).isEqualTo("TINYINT(2)"); assertThat(def.generateSqlType(new Oracle())).isEqualTo("NUMBER(3)"); } diff --git a/sonar-db/src/test/java/org/sonar/db/version/v56/FixTypeOfRuleTypeOnMysqlTest.java b/sonar-db/src/test/java/org/sonar/db/version/v56/FixTypeOfRuleTypeOnMysqlTest.java new file mode 100644 index 00000000000..d7e4b0ecdae --- /dev/null +++ b/sonar-db/src/test/java/org/sonar/db/version/v56/FixTypeOfRuleTypeOnMysqlTest.java @@ -0,0 +1,89 @@ +/* + * 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.v56; + +import java.sql.SQLException; +import org.junit.Test; +import org.sonar.api.platform.ServerUpgradeStatus; +import org.sonar.db.Database; +import org.sonar.db.dialect.Dialect; +import org.sonar.db.dialect.MySql; +import org.sonar.db.dialect.Oracle; +import org.sonar.db.version.DdlChange; + +import static org.mockito.Mockito.RETURNS_DEEP_STUBS; +import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.verify; +import static org.mockito.Mockito.verifyZeroInteractions; +import static org.mockito.Mockito.when; + +public class FixTypeOfRuleTypeOnMysqlTest { + + private static final int FRESH_MIGRATION_VERSION = -1; + private static final int A_MIGRATION_VERSION_IN_5_5 = 1105; + private static final int A_MIGRATION_VERSION_BEFORE_5_5 = 200; + + Database db = mock(Database.class, RETURNS_DEEP_STUBS); + ServerUpgradeStatus dbVersion = mock(ServerUpgradeStatus.class); + DdlChange.Context context = mock(DdlChange.Context.class); + FixTypeOfRuleTypeOnMysql underTest = new FixTypeOfRuleTypeOnMysql(db, dbVersion); + + @Test + public void alter_columns_if_upgrading_mysql_from_version_5_5() throws SQLException { + prepare(A_MIGRATION_VERSION_IN_5_5, new MySql()); + + underTest.execute(context); + + verify(context).execute("ALTER TABLE rules MODIFY COLUMN rule_type TINYINT (2)"); + verify(context).execute("ALTER TABLE issues MODIFY COLUMN issue_type TINYINT (2)"); + } + + @Test + public void do_not_alter_columns_if_fresh_mysql_install() throws SQLException { + prepare(FRESH_MIGRATION_VERSION, new MySql()); + + underTest.execute(context); + + verifyZeroInteractions(context); + } + + @Test + public void do_not_alter_columns_if_upgrading_mysql_from_before_5_5() throws SQLException { + prepare(A_MIGRATION_VERSION_BEFORE_5_5, new MySql()); + + underTest.execute(context); + + verifyZeroInteractions(context); + } + + @Test + public void do_not_alter_columns_if_upgrading_from_5_5_but_not_mysql() throws SQLException { + prepare(A_MIGRATION_VERSION_IN_5_5, new Oracle()); + + underTest.execute(context); + + verifyZeroInteractions(context); + } + + private void prepare(int initialVersion, Dialect dialect) { + when(dbVersion.getInitialDbVersion()).thenReturn(initialVersion); + when(db.getDialect()).thenReturn(dialect); + } +} -- 2.39.5