From 9e1ac06ecd2f9998f5126401e4ee3d6af205e004 Mon Sep 17 00:00:00 2001 From: Julien Lancelot Date: Tue, 17 Mar 2015 11:27:18 +0100 Subject: [PATCH] Merge multiple issues column requests alter into one request --- .../db/migrations/AddColumnsBuilder.java | 152 ++++++++++++++++++ .../db/migrations/DatabaseMigrations.java | 4 +- .../sonar/server/db/migrations/DdlChange.java | 68 ++++++++ .../db/migrations/DropColumnsBuilder.java | 68 ++++++++ .../db/migrations/v51/AddIssuesColumns.java | 93 +++++++++++ .../db/migrations/v51/DropIssuesColumns.java | 59 +++++++ .../db/migrations/AddColumnsBuilderTest.java | 131 +++++++++++++++ .../db/migrations/DropColumnsBuilderTest.java | 59 +++++++ .../migrations/v51/AddIssuesColumnsTest.java | 54 +++++++ .../migrations/v51/DropIssuesColumnsTest.java | 51 ++++++ .../v51/AddIssuesColumnsTest/schema.sql | 26 +++ ...gs_column.rb => 750_add_issues_columns.rb} | 4 +- ...771_add_issues_component_uuids_indexes.rb} | 4 +- .../migrate/773_remove_issue_component_ids.rb | 41 ----- ...dates.rb => 774_feed_issues_long_dates.rb} | 2 + ...omponent_ids_and_creation_date_indexes.rb} | 9 +- .../db/migrate/779_drop_issues_columns.rb | 29 ++++ .../migrate/780_rename_issues_long_dates.rb | 6 +- .../org/sonar/core/persistence/rows-h2.sql | 2 +- .../org/sonar/core/persistence/DbTester.java | 49 +++++- 20 files changed, 848 insertions(+), 63 deletions(-) create mode 100644 server/sonar-server/src/main/java/org/sonar/server/db/migrations/AddColumnsBuilder.java create mode 100644 server/sonar-server/src/main/java/org/sonar/server/db/migrations/DdlChange.java create mode 100644 server/sonar-server/src/main/java/org/sonar/server/db/migrations/DropColumnsBuilder.java create mode 100644 server/sonar-server/src/main/java/org/sonar/server/db/migrations/v51/AddIssuesColumns.java create mode 100644 server/sonar-server/src/main/java/org/sonar/server/db/migrations/v51/DropIssuesColumns.java create mode 100644 server/sonar-server/src/test/java/org/sonar/server/db/migrations/AddColumnsBuilderTest.java create mode 100644 server/sonar-server/src/test/java/org/sonar/server/db/migrations/DropColumnsBuilderTest.java create mode 100644 server/sonar-server/src/test/java/org/sonar/server/db/migrations/v51/AddIssuesColumnsTest.java create mode 100644 server/sonar-server/src/test/java/org/sonar/server/db/migrations/v51/DropIssuesColumnsTest.java create mode 100644 server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v51/AddIssuesColumnsTest/schema.sql rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{750_add_issues_tags_column.rb => 750_add_issues_columns.rb} (87%) rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{771_add_issue_component_uuids.rb => 771_add_issues_component_uuids_indexes.rb} (83%) delete mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/773_remove_issue_component_ids.rb rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{779_feed_issues_long_dates.rb => 774_feed_issues_long_dates.rb} (99%) rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{778_add_issues_long_dates.rb => 778_remove_issues_component_ids_and_creation_date_indexes.rb} (76%) create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/779_drop_issues_columns.rb diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/AddColumnsBuilder.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/AddColumnsBuilder.java new file mode 100644 index 00000000000..8d884296c8f --- /dev/null +++ b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/AddColumnsBuilder.java @@ -0,0 +1,152 @@ +/* + * 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. + */ +package org.sonar.server.db.migrations; + +import com.google.common.base.CharMatcher; +import com.google.common.base.Preconditions; +import org.sonar.core.persistence.dialect.Dialect; +import org.sonar.core.persistence.dialect.MsSql; +import org.sonar.core.persistence.dialect.Oracle; +import org.sonar.core.persistence.dialect.PostgreSql; + +import javax.annotation.CheckForNull; +import javax.annotation.Nullable; + +import java.util.List; + +import static com.google.common.collect.Lists.newArrayList; + +public class AddColumnsBuilder { + + private final Dialect dialect; + private final String tableName; + private List columnDefs = newArrayList(); + + public AddColumnsBuilder(Dialect dialect, String tableName) { + this.tableName = tableName; + this.dialect = dialect; + } + + public AddColumnsBuilder addColumn(ColumnDef columnDef) { + columnDefs.add(columnDef); + return this; + } + + public String build() { + StringBuilder sql = new StringBuilder().append("ALTER TABLE ").append(tableName).append(" "); + switch (dialect.getId()) { + case PostgreSql.ID: + addColumns(sql, "ADD COLUMN "); + break; + case MsSql.ID: + sql.append("ADD "); + addColumns(sql, ""); + break; + default: + sql.append("ADD ("); + addColumns(sql, ""); + sql.append(")"); + } + return sql.toString(); + } + + private void addColumns(StringBuilder sql, String columnPrefix) { + for (int i = 0; i < columnDefs.size(); i++) { + sql.append(columnPrefix); + addColumn(sql, columnDefs.get(i)); + if (i < columnDefs.size() - 1) { + sql.append(", "); + } + } + } + + private void addColumn(StringBuilder sql, ColumnDef columnDef) { + sql.append(columnDef.getName()).append(" ").append(typeToSql(columnDef)); + Integer limit = columnDef.getLimit(); + if (limit != null) { + sql.append(" (").append(Integer.toString(limit)).append(")"); + } + sql.append(columnDef.isNullable() ? " NULL" : " NOT NULL"); + } + + private String typeToSql(ColumnDef columnDef) { + switch (columnDef.getType()) { + case STRING: + return "VARCHAR"; + case BIG_INTEGER: + if (dialect.getId().equals(Oracle.ID)) { + return "NUMBER (38)"; + } else { + return "BIGINT"; + } + default: + throw new IllegalArgumentException("Unsupported type : " + columnDef.getType()); + } + } + + public static class ColumnDef { + private String name; + private Type type; + private boolean isNullable; + private Integer limit; + + public enum Type { + STRING, BIG_INTEGER + } + + public ColumnDef setNullable(boolean isNullable) { + this.isNullable = isNullable; + return this; + } + + public ColumnDef setLimit(@Nullable Integer limit) { + this.limit = limit; + return this; + } + + public ColumnDef setName(String name) { + Preconditions.checkArgument(CharMatcher.JAVA_LOWER_CASE.or(CharMatcher.anyOf("_")).matchesAllOf(name), "Column name should only contains lowercase and _ characters"); + this.name = name; + return this; + } + + public ColumnDef setType(Type type) { + this.type = type; + return this; + } + + public boolean isNullable() { + return isNullable; + } + + @CheckForNull + public Integer getLimit() { + return limit; + } + + public String getName() { + return name; + } + + public Type getType() { + return type; + } + } +} diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DatabaseMigrations.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DatabaseMigrations.java index 6afedd0a1f6..8b26a3f5196 100644 --- a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DatabaseMigrations.java +++ b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DatabaseMigrations.java @@ -93,6 +93,8 @@ public interface DatabaseMigrations { FeedManualMeasuresLongDates.class, FeedEventsLongDates.class, AddNewCharacteristics.class, - RemovePermissionsOnModulesMigration.class + RemovePermissionsOnModulesMigration.class, + AddIssuesColumns.class, + DropIssuesColumns.class ); } diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DdlChange.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DdlChange.java new file mode 100644 index 00000000000..6dafe50ec1b --- /dev/null +++ b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DdlChange.java @@ -0,0 +1,68 @@ +/* + * 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. + */ +package org.sonar.server.db.migrations; + +import org.apache.commons.dbutils.DbUtils; +import org.sonar.core.persistence.Database; + +import java.sql.Connection; +import java.sql.SQLException; + +public abstract class DdlChange implements DatabaseMigration { + + private final Database db; + + public DdlChange(Database db) { + this.db = db; + } + + @Override + public final void execute() throws SQLException { + Connection writeConnection = null; + try { + writeConnection = db.getDataSource().getConnection(); + writeConnection.setAutoCommit(false); + Context context = new Context(writeConnection); + execute(context); + + } finally { + DbUtils.closeQuietly(writeConnection); + } + } + + public class Context { + private final Connection writeConnection; + + public Context(Connection writeConnection) { + this.writeConnection = writeConnection; + } + + public void execute(String sql) throws SQLException { + try { + UpsertImpl.create(writeConnection, sql).execute().commit(); + } catch (Exception e) { + throw new IllegalStateException(String.format("Fail to execute %s", sql), e); + } + } + } + + public abstract void execute(Context context) throws SQLException; + +} diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DropColumnsBuilder.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DropColumnsBuilder.java new file mode 100644 index 00000000000..ce61d645b16 --- /dev/null +++ b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/DropColumnsBuilder.java @@ -0,0 +1,68 @@ +/* + * 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. + */ +package org.sonar.server.db.migrations; + +import org.sonar.core.persistence.dialect.*; + +public class DropColumnsBuilder { + + private final Dialect dialect; + private final String tableName; + private final String[] columns; + + public DropColumnsBuilder(Dialect dialect, String tableName, String... columns) { + this.tableName = tableName; + this.dialect = dialect; + this.columns = columns; + } + + public String build() { + StringBuilder sql = new StringBuilder().append("ALTER TABLE ").append(tableName).append(" "); + switch (dialect.getId()) { + case PostgreSql.ID: + case MySql.ID: + dropColumns(sql, "DROP COLUMN "); + break; + case MsSql.ID: + sql.append("DROP COLUMN "); + dropColumns(sql, ""); + break; + case Oracle.ID: + sql.append("DROP ("); + dropColumns(sql, ""); + sql.append(")"); + break; + default: + throw new IllegalStateException(String.format("Unsupported database '%s'", dialect.getId())); + } + return sql.toString(); + } + + private void dropColumns(StringBuilder sql, String columnPrefix) { + for (int i = 0; i < columns.length; i++) { + sql.append(columnPrefix); + sql.append(columns[i]); + if (i < columns.length - 1) { + sql.append(", "); + } + } + } + +} diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v51/AddIssuesColumns.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v51/AddIssuesColumns.java new file mode 100644 index 00000000000..529c3436e37 --- /dev/null +++ b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v51/AddIssuesColumns.java @@ -0,0 +1,93 @@ +/* + * 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. + */ + +package org.sonar.server.db.migrations.v51; + +import org.sonar.core.persistence.Database; +import org.sonar.server.db.migrations.AddColumnsBuilder; +import org.sonar.server.db.migrations.DdlChange; + +import java.sql.SQLException; + +/** + * Add the following columns to the issues table : + * - issue_creation_date_ms + * - issue_update_date_ms + * - issue_close_date_ms + * - tags + * - component_uuid + * - project_uuid + */ +public class AddIssuesColumns extends DdlChange { + + private final Database db; + + public AddIssuesColumns(Database db) { + super(db); + this.db = db; + } + + @Override + public void execute(DdlChange.Context context) throws SQLException { + context.execute(generateSql()); + } + + private String generateSql() { + return new AddColumnsBuilder(db.getDialect(), "issues") + .addColumn( + new AddColumnsBuilder.ColumnDef() + .setName("issue_creation_date_ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true) + ) + .addColumn( + new AddColumnsBuilder.ColumnDef() + .setName("issue_update_date_ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true) + ) + .addColumn( + new AddColumnsBuilder.ColumnDef() + .setName("issue_close_date_ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true) + ) + .addColumn( + new AddColumnsBuilder.ColumnDef() + .setName("tags") + .setType(AddColumnsBuilder.ColumnDef.Type.STRING) + .setLimit(4000) + .setNullable(true)) + .addColumn( + new AddColumnsBuilder.ColumnDef() + .setName("component_uuid") + .setType(AddColumnsBuilder.ColumnDef.Type.STRING) + .setLimit(50) + .setNullable(true)) + .addColumn( + new AddColumnsBuilder.ColumnDef() + .setName("project_uuid") + .setType(AddColumnsBuilder.ColumnDef.Type.STRING) + .setLimit(50) + .setNullable(true)) + .build(); + } + +} diff --git a/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v51/DropIssuesColumns.java b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v51/DropIssuesColumns.java new file mode 100644 index 00000000000..3b04ac3dbe1 --- /dev/null +++ b/server/sonar-server/src/main/java/org/sonar/server/db/migrations/v51/DropIssuesColumns.java @@ -0,0 +1,59 @@ +/* + * 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. + */ + +package org.sonar.server.db.migrations.v51; + +import com.google.common.annotations.VisibleForTesting; +import org.sonar.core.persistence.Database; +import org.sonar.server.db.migrations.DdlChange; +import org.sonar.server.db.migrations.DropColumnsBuilder; + +import java.sql.SQLException; + +/** + * Drop the following columns from the issues table : + * - issue_creation_date + * - issue_update_date + * - issue_close_date + * - component_id + * - root_component_id + */ +public class DropIssuesColumns extends DdlChange { + + private final Database db; + + public DropIssuesColumns(Database db) { + super(db); + this.db = db; + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(generateSql()); + } + + @VisibleForTesting + String generateSql() { + return new DropColumnsBuilder(db.getDialect(), "issues", + "issue_creation_date", "issue_update_date", "issue_close_date", "component_id", "root_component_id") + .build(); + } + +} diff --git a/server/sonar-server/src/test/java/org/sonar/server/db/migrations/AddColumnsBuilderTest.java b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/AddColumnsBuilderTest.java new file mode 100644 index 00000000000..f5e2d4516fd --- /dev/null +++ b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/AddColumnsBuilderTest.java @@ -0,0 +1,131 @@ +/* + * 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. + */ +package org.sonar.server.db.migrations; + +import org.junit.Test; +import org.sonar.core.persistence.dialect.*; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.failBecauseExceptionWasNotThrown; + +public class AddColumnsBuilderTest { + + @Test + public void add_columns_on_h2() throws Exception { + assertThat(new AddColumnsBuilder(new H2(), "issues") + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("date_in_ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true)) + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("name") + .setType(AddColumnsBuilder.ColumnDef.Type.STRING) + .setNullable(false) + .setLimit(10)) + .build()).isEqualTo("ALTER TABLE issues ADD (date_in_ms BIGINT NULL, name VARCHAR (10) NOT NULL)"); + } + + @Test + public void add_columns_on_mysql() throws Exception { + assertThat(new AddColumnsBuilder(new MySql(), "issues") + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("date_in_ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true)) + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("name") + .setType(AddColumnsBuilder.ColumnDef.Type.STRING) + .setNullable(false) + .setLimit(10)) + .build()).isEqualTo("ALTER TABLE issues ADD (date_in_ms BIGINT NULL, name VARCHAR (10) NOT NULL)"); + } + + @Test + public void add_columns_on_oracle() throws Exception { + assertThat(new AddColumnsBuilder(new Oracle(), "issues") + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("date_in_ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true)) + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("name") + .setType(AddColumnsBuilder.ColumnDef.Type.STRING) + .setNullable(false) + .setLimit(10)) + .build()).isEqualTo("ALTER TABLE issues ADD (date_in_ms NUMBER (38) NULL, name VARCHAR (10) NOT NULL)"); + } + + @Test + public void add_columns_on_postgresql() throws Exception { + assertThat(new AddColumnsBuilder(new PostgreSql(), "issues") + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("date_in_ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true)) + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("name") + .setType(AddColumnsBuilder.ColumnDef.Type.STRING) + .setNullable(false) + .setLimit(10)) + .build()).isEqualTo("ALTER TABLE issues ADD COLUMN date_in_ms BIGINT NULL, ADD COLUMN name VARCHAR (10) NOT NULL"); + } + + @Test + public void add_columns_on_mssql() throws Exception { + assertThat(new AddColumnsBuilder(new MsSql(), "issues") + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("date_in_ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true)) + .addColumn(new AddColumnsBuilder.ColumnDef() + .setName("name") + .setType(AddColumnsBuilder.ColumnDef.Type.STRING) + .setNullable(false) + .setLimit(10)) + .build()).isEqualTo("ALTER TABLE issues ADD date_in_ms BIGINT NULL, name VARCHAR (10) NOT NULL"); + } + + @Test + public void fail_when_column_name_is_in_upper_case() throws Exception { + try { + new AddColumnsBuilder.ColumnDef() + .setName("DATE_IN_MS") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true); + failBecauseExceptionWasNotThrown(IllegalArgumentException.class); + } catch (IllegalArgumentException e) { + assertThat(e).hasMessage("Column name should only contains lowercase and _ characters"); + } + } + + @Test + public void fail_when_column_name_contains_invalid_character() throws Exception { + try { + new AddColumnsBuilder.ColumnDef() + .setName("date-in/ms") + .setType(AddColumnsBuilder.ColumnDef.Type.BIG_INTEGER) + .setNullable(true); + failBecauseExceptionWasNotThrown(IllegalArgumentException.class); + } catch (IllegalArgumentException e) { + assertThat(e).hasMessage("Column name should only contains lowercase and _ characters"); + } + } + +} diff --git a/server/sonar-server/src/test/java/org/sonar/server/db/migrations/DropColumnsBuilderTest.java b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/DropColumnsBuilderTest.java new file mode 100644 index 00000000000..316718d716c --- /dev/null +++ b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/DropColumnsBuilderTest.java @@ -0,0 +1,59 @@ +/* + * 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. + */ +package org.sonar.server.db.migrations; + +import org.junit.Test; +import org.sonar.core.persistence.dialect.*; + +import static org.assertj.core.api.Assertions.assertThat; + +public class DropColumnsBuilderTest { + + @Test + public void drop_columns_on_mysql() throws Exception { + assertThat(new DropColumnsBuilder(new MySql(), "issues", "date_in_ms", "name") + .build()).isEqualTo("ALTER TABLE issues DROP COLUMN date_in_ms, DROP COLUMN name"); + } + + @Test + public void drop_columns_on_oracle() throws Exception { + assertThat(new DropColumnsBuilder(new Oracle(), "issues", "date_in_ms", "name") + .build()).isEqualTo("ALTER TABLE issues DROP (date_in_ms, name)"); + } + + @Test + public void drop_columns_on_postgresql() throws Exception { + assertThat(new DropColumnsBuilder(new PostgreSql(), "issues", "date_in_ms", "name") + .build()).isEqualTo("ALTER TABLE issues DROP COLUMN date_in_ms, DROP COLUMN name"); + } + + @Test + public void drop_columns_on_mssql() throws Exception { + assertThat(new DropColumnsBuilder(new MsSql(), "issues", "date_in_ms", "name") + .build()).isEqualTo("ALTER TABLE issues DROP COLUMN date_in_ms, name"); + } + + @Test(expected = IllegalStateException.class) + public void fail_to_drop_columns_on_h2() throws Exception { + new DropColumnsBuilder(new H2(), "issues", "date_in_ms", "name") + .build(); + } + +} diff --git a/server/sonar-server/src/test/java/org/sonar/server/db/migrations/v51/AddIssuesColumnsTest.java b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/v51/AddIssuesColumnsTest.java new file mode 100644 index 00000000000..6559ca4eb03 --- /dev/null +++ b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/v51/AddIssuesColumnsTest.java @@ -0,0 +1,54 @@ +/* + * 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. + */ +package org.sonar.server.db.migrations.v51; + +import org.junit.Before; +import org.junit.ClassRule; +import org.junit.Test; +import org.sonar.core.persistence.DbTester; +import org.sonar.server.db.migrations.DatabaseMigration; + +import java.sql.Types; + +public class AddIssuesColumnsTest { + + @ClassRule + public static DbTester db = new DbTester().schema(AddIssuesColumnsTest.class, "schema.sql"); + + DatabaseMigration migration; + + @Before + public void setUp() throws Exception { + migration = new AddIssuesColumns(db.database()); + } + + @Test + public void update_columns() throws Exception { + migration.execute(); + + db.assertColumnDefinition("issues", "issue_creation_date_ms", Types.BIGINT, null); + db.assertColumnDefinition("issues", "issue_update_date_ms", Types.BIGINT, null); + db.assertColumnDefinition("issues", "issue_close_date_ms", Types.BIGINT, null); + db.assertColumnDefinition("issues", "tags", Types.VARCHAR, 4000); + db.assertColumnDefinition("issues", "component_uuid", Types.VARCHAR, 50); + db.assertColumnDefinition("issues", "project_uuid", Types.VARCHAR, 50); + } + +} diff --git a/server/sonar-server/src/test/java/org/sonar/server/db/migrations/v51/DropIssuesColumnsTest.java b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/v51/DropIssuesColumnsTest.java new file mode 100644 index 00000000000..0280ffe0af6 --- /dev/null +++ b/server/sonar-server/src/test/java/org/sonar/server/db/migrations/v51/DropIssuesColumnsTest.java @@ -0,0 +1,51 @@ +/* + * 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. + */ +package org.sonar.server.db.migrations.v51; + +import org.junit.Before; +import org.junit.Test; +import org.sonar.core.persistence.Database; +import org.sonar.core.persistence.dialect.PostgreSql; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.when; + +public class DropIssuesColumnsTest { + + DropIssuesColumns migration; + + Database database; + + @Before + public void setUp() throws Exception { + database = mock(Database.class); + migration = new DropIssuesColumns(database); + } + + @Test + public void generate_sql_on_postgresql() throws Exception { + when(database.getDialect()).thenReturn(new PostgreSql()); + assertThat(migration.generateSql()).isEqualTo( + "ALTER TABLE issues DROP COLUMN issue_creation_date, DROP COLUMN issue_update_date, DROP COLUMN issue_close_date, DROP COLUMN component_id, DROP COLUMN root_component_id" + ); + } + +} diff --git a/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v51/AddIssuesColumnsTest/schema.sql b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v51/AddIssuesColumnsTest/schema.sql new file mode 100644 index 00000000000..3e799c31508 --- /dev/null +++ b/server/sonar-server/src/test/resources/org/sonar/server/db/migrations/v51/AddIssuesColumnsTest/schema.sql @@ -0,0 +1,26 @@ +CREATE TABLE "ISSUES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(50) UNIQUE NOT NULL, + "COMPONENT_ID" INTEGER NOT NULL, + "ROOT_COMPONENT_ID" INTEGER, + "RULE_ID" INTEGER, + "SEVERITY" VARCHAR(10), + "MANUAL_SEVERITY" BOOLEAN NOT NULL, + "MESSAGE" VARCHAR(4000), + "LINE" INTEGER, + "EFFORT_TO_FIX" DOUBLE, + "TECHNICAL_DEBT" INTEGER, + "STATUS" VARCHAR(20), + "RESOLUTION" VARCHAR(20), + "CHECKSUM" VARCHAR(1000), + "REPORTER" VARCHAR(255), + "ASSIGNEE" VARCHAR(255), + "AUTHOR_LOGIN" VARCHAR(255), + "ACTION_PLAN_KEY" VARCHAR(50) NULL, + "ISSUE_ATTRIBUTES" VARCHAR(4000), + "ISSUE_CREATION_DATE" TIMESTAMP, + "ISSUE_CLOSE_DATE" TIMESTAMP, + "ISSUE_UPDATE_DATE" TIMESTAMP, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT +); diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/750_add_issues_tags_column.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/750_add_issues_columns.rb similarity index 87% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/750_add_issues_tags_column.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/750_add_issues_columns.rb index 21f2bff9a63..ff2f7786ad7 100644 --- a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/750_add_issues_tags_column.rb +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/750_add_issues_columns.rb @@ -22,10 +22,10 @@ # SonarQube 5.1 # SONAR-5896 # -class AddIssuesTagsColumn < ActiveRecord::Migration +class AddIssuesColumns < ActiveRecord::Migration def self.up - add_column 'issues', :tags, :string, :null => true, :limit => 4000 + execute_java_migration('org.sonar.server.db.migrations.v51.AddIssuesColumns') end end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/771_add_issue_component_uuids.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/771_add_issues_component_uuids_indexes.rb similarity index 83% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/771_add_issue_component_uuids.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/771_add_issues_component_uuids_indexes.rb index ec2c940231f..df7b9be01d5 100644 --- a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/771_add_issue_component_uuids.rb +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/771_add_issues_component_uuids_indexes.rb @@ -21,11 +21,9 @@ # # SonarQube 5.1 # -class AddIssueComponentUuids < ActiveRecord::Migration +class AddIssuesComponentUuidsIndexes < ActiveRecord::Migration def self.up - add_column 'issues', :component_uuid, :string, :limit => 50, :null => true - add_column 'issues', :project_uuid, :string, :limit => 50, :null => true add_index 'issues', 'component_uuid', :name => 'issues_component_uuid' add_index 'issues', 'project_uuid', :name => 'issues_project_uuid' end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/773_remove_issue_component_ids.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/773_remove_issue_component_ids.rb deleted file mode 100644 index 42d480edd01..00000000000 --- a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/773_remove_issue_component_ids.rb +++ /dev/null @@ -1,41 +0,0 @@ -# -# 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.1 -# -class RemoveIssueComponentIds < ActiveRecord::Migration - - def self.up - if dialect()=='sqlserver' - remove_index :issues, :name => 'issues_component_uuid' - remove_index :issues, :name => 'issues_project_uuid' - end - remove_index 'issues', :name => 'issues_component_id' - remove_index 'issues', :name => 'issues_root_component_id' - remove_column 'issues', 'component_id' - remove_column 'issues', 'root_component_id' - - if dialect()=='sqlserver' - add_index :issues, :component_uuid, :name => 'issues_component_uuid' - add_index :issues, :project_uuid, :name => 'issues_project_uuid' - end - end -end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/779_feed_issues_long_dates.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/774_feed_issues_long_dates.rb similarity index 99% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/779_feed_issues_long_dates.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/774_feed_issues_long_dates.rb index 33c6db66b87..ddbc6da29af 100644 --- a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/779_feed_issues_long_dates.rb +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/774_feed_issues_long_dates.rb @@ -22,8 +22,10 @@ # SonarQube 5.1 # class FeedIssuesLongDates < ActiveRecord::Migration + def self.up execute_java_migration('org.sonar.server.db.migrations.v51.FeedIssuesLongDates') end + end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/778_add_issues_long_dates.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/778_remove_issues_component_ids_and_creation_date_indexes.rb similarity index 76% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/778_add_issues_long_dates.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/778_remove_issues_component_ids_and_creation_date_indexes.rb index 55492e8b421..33a658871f8 100644 --- a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/778_add_issues_long_dates.rb +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/778_remove_issues_component_ids_and_creation_date_indexes.rb @@ -21,10 +21,11 @@ # # SonarQube 5.1 # -class AddIssuesLongDates < ActiveRecord::Migration +class RemoveIssuesComponentIdsAndCreationDateIndexes < ActiveRecord::Migration + def self.up - add_column 'issues', :issue_creation_date_ms, :big_integer, :null => true - add_column 'issues', :issue_update_date_ms, :big_integer, :null => true - add_column 'issues', :issue_close_date_ms, :big_integer, :null => true + remove_index 'issues', :name => 'issues_component_id' + remove_index 'issues', :name => 'issues_root_component_id' + remove_index 'issues', :name => 'issues_creation_date' end end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/779_drop_issues_columns.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/779_drop_issues_columns.rb new file mode 100644 index 00000000000..c1d92bb722a --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/779_drop_issues_columns.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 5.1 +# +class DropIssuesColumns < ActiveRecord::Migration + + def self.up + execute_java_migration('org.sonar.server.db.migrations.v51.DropIssuesColumns') + end +end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/780_rename_issues_long_dates.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/780_rename_issues_long_dates.rb index 2d53e7cb59f..b82ce092ac6 100644 --- a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/780_rename_issues_long_dates.rb +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/780_rename_issues_long_dates.rb @@ -22,15 +22,13 @@ # SonarQube 5.1 # class RenameIssuesLongDates < ActiveRecord::Migration + def self.up - remove_index 'issues', :name => 'issues_creation_date' - remove_column 'issues', 'issue_creation_date' - remove_column 'issues', 'issue_update_date' - remove_column 'issues', 'issue_close_date' rename_column 'issues', 'issue_creation_date_ms', 'issue_creation_date' rename_column 'issues', 'issue_update_date_ms', 'issue_update_date' rename_column 'issues', 'issue_close_date_ms', 'issue_close_date' add_index 'issues', 'issue_creation_date', :name => 'issues_creation_date' end + end diff --git a/sonar-core/src/main/resources/org/sonar/core/persistence/rows-h2.sql b/sonar-core/src/main/resources/org/sonar/core/persistence/rows-h2.sql index a410243e678..5a090866dec 100644 --- a/sonar-core/src/main/resources/org/sonar/core/persistence/rows-h2.sql +++ b/sonar-core/src/main/resources/org/sonar/core/persistence/rows-h2.sql @@ -300,7 +300,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('769'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('770'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('771'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('772'); -INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('773'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('774'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('775'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('776'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('777'); diff --git a/sonar-core/src/test/java/org/sonar/core/persistence/DbTester.java b/sonar-core/src/test/java/org/sonar/core/persistence/DbTester.java index 48470d92f36..9f827de7751 100644 --- a/sonar-core/src/test/java/org/sonar/core/persistence/DbTester.java +++ b/sonar-core/src/test/java/org/sonar/core/persistence/DbTester.java @@ -50,24 +50,23 @@ import org.sonar.core.cluster.NullQueue; import org.sonar.core.config.Logback; import org.sonar.core.persistence.dialect.Dialect; +import javax.annotation.CheckForNull; +import javax.annotation.Nullable; + import java.io.File; import java.io.IOException; import java.io.InputStream; import java.net.HttpURLConnection; import java.net.URI; import java.net.URISyntaxException; -import java.sql.Clob; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.ResultSetMetaData; -import java.sql.SQLException; +import java.sql.*; import java.util.List; import java.util.Map; import java.util.Properties; import static com.google.common.collect.Lists.newArrayList; import static com.google.common.collect.Maps.newHashMap; +import static org.assertj.core.api.Assertions.assertThat; import static org.junit.Assert.fail; /** @@ -230,7 +229,7 @@ public class DbTester extends ExternalResource { for (int i = 1; i <= colCount; i++) { Object value = resultSet.getObject(i); if (value instanceof Clob) { - value = IOUtils.toString(((Clob)value).getAsciiStream()); + value = IOUtils.toString(((Clob) value).getAsciiStream()); } columns.put(metaData.getColumnLabel(i), value); } @@ -311,6 +310,42 @@ public class DbTester extends ExternalResource { } } + public void assertColumnDefinition(String table, String column, int expectedType, @Nullable Integer expectedSize) { + try (Connection connection = openConnection(); + PreparedStatement stmt = connection.prepareStatement("select * from " + table); + ResultSet res = stmt.executeQuery()) { + Integer columnIndex = getColumnIndex(res, column); + if (columnIndex == null) { + fail("The column '" + column + "' does not exist"); + } + + assertThat(res.getMetaData().getColumnType(columnIndex)).isEqualTo(expectedType); + if (expectedSize != null) { + assertThat(res.getMetaData().getColumnDisplaySize(columnIndex)).isEqualTo(expectedSize); + } + + } catch (Exception e) { + throw new IllegalStateException("Fail to check column"); + } + } + + @CheckForNull + private Integer getColumnIndex(ResultSet res, String column) { + try { + ResultSetMetaData meta = res.getMetaData(); + int numCol = meta.getColumnCount(); + for (int i = 1; i < numCol + 1; i++) { + if (meta.getColumnLabel(i).toLowerCase().equals(column.toLowerCase())) { + return i; + } + } + return null; + + } catch (Exception e) { + throw new IllegalStateException("Fail to get column idnex"); + } + } + private IDataSet dbUnitDataSet(InputStream stream) { try { ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(stream)); -- 2.39.5