From e20b54989f38d480f157dd36dd3b9d1fc4476813 Mon Sep 17 00:00:00 2001 From: Jacek Date: Wed, 29 Apr 2020 14:31:10 +0200 Subject: [PATCH] SONAR-13221 add support PK with many columns to drop builder --- .../v83/DropIdFromComponentsTable.java | 5 ++- .../v83/common/DropPrimaryKeyOnIdColumn.java | 2 +- ...aryKeyOnIdColumnOfManualMeasuresTable.java | 2 +- ...yKeyOnIdColumnOfProjectQProfilesTable.java | 2 +- ...pPrimaryKeyOnIdColumnOfSnapshotsTable.java | 2 +- .../v83/util/AddPrimaryKeyBuilder.java | 12 ++++-- .../v83/util/DropPrimaryKeySqlGenerator.java | 38 ++++++++++++------- .../util/DropPrimaryKeySqlGeneratorTest.java | 8 ++-- 8 files changed, 44 insertions(+), 27 deletions(-) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/DropIdFromComponentsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/DropIdFromComponentsTable.java index 37509a845aa..e76caf281cb 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/DropIdFromComponentsTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/DropIdFromComponentsTable.java @@ -32,13 +32,14 @@ public class DropIdFromComponentsTable extends DdlChange { private final DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator; - public DropIdFromComponentsTable(Database db, DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator) { + public DropIdFromComponentsTable(Database db, DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator) { super(db); this.dropPrimaryKeySqlGenerator = dropPrimaryKeySqlGenerator; } + @Override public void execute(Context context) throws SQLException { - context.execute(dropPrimaryKeySqlGenerator.generate(TABLE_NAME, COLUMN_NAME)); + context.execute(dropPrimaryKeySqlGenerator.generate(TABLE_NAME, COLUMN_NAME, true)); context.execute(new DropColumnsBuilder(getDialect(), TABLE_NAME, COLUMN_NAME).build()); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/common/DropPrimaryKeyOnIdColumn.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/common/DropPrimaryKeyOnIdColumn.java index 5d931633f24..396756e7505 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/common/DropPrimaryKeyOnIdColumn.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/common/DropPrimaryKeyOnIdColumn.java @@ -36,6 +36,6 @@ public abstract class DropPrimaryKeyOnIdColumn extends DdlChange { @Override public void execute(Context context) throws SQLException { - context.execute(dropPrimaryKeySqlGenerator.generate(tableName, "id")); + context.execute(dropPrimaryKeySqlGenerator.generate(tableName, "id", true)); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/manualmeasures/DropPrimaryKeyOnIdColumnOfManualMeasuresTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/manualmeasures/DropPrimaryKeyOnIdColumnOfManualMeasuresTable.java index 17b95db08ec..85f307727db 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/manualmeasures/DropPrimaryKeyOnIdColumnOfManualMeasuresTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/manualmeasures/DropPrimaryKeyOnIdColumnOfManualMeasuresTable.java @@ -35,7 +35,7 @@ public class DropPrimaryKeyOnIdColumnOfManualMeasuresTable extends DdlChange { @Override public void execute(Context context) throws SQLException { - context.execute(dropPrimaryKeySqlGenerator.generate("manual_measures", "id")); + context.execute(dropPrimaryKeySqlGenerator.generate("manual_measures", "id", true)); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/projectqprofiles/DropPrimaryKeyOnIdColumnOfProjectQProfilesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/projectqprofiles/DropPrimaryKeyOnIdColumnOfProjectQProfilesTable.java index ce2a784d6d5..c7955828d99 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/projectqprofiles/DropPrimaryKeyOnIdColumnOfProjectQProfilesTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/projectqprofiles/DropPrimaryKeyOnIdColumnOfProjectQProfilesTable.java @@ -35,7 +35,7 @@ public class DropPrimaryKeyOnIdColumnOfProjectQProfilesTable extends DdlChange { @Override public void execute(Context context) throws SQLException { - context.execute(dropPrimaryKeySqlGenerator.generate("project_qprofiles", "id")); + context.execute(dropPrimaryKeySqlGenerator.generate("project_qprofiles", "id", true)); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/snapshots/issues/DropPrimaryKeyOnIdColumnOfSnapshotsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/snapshots/issues/DropPrimaryKeyOnIdColumnOfSnapshotsTable.java index a4384573eed..9f8aa733ed6 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/snapshots/issues/DropPrimaryKeyOnIdColumnOfSnapshotsTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/snapshots/issues/DropPrimaryKeyOnIdColumnOfSnapshotsTable.java @@ -35,7 +35,7 @@ public class DropPrimaryKeyOnIdColumnOfSnapshotsTable extends DdlChange { @Override public void execute(Context context) throws SQLException { - context.execute(dropPrimaryKeySqlGenerator.generate("snapshots", "id")); + context.execute(dropPrimaryKeySqlGenerator.generate("snapshots", "id", true)); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/util/AddPrimaryKeyBuilder.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/util/AddPrimaryKeyBuilder.java index fad765114ba..bc2d0ebd0ab 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/util/AddPrimaryKeyBuilder.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/util/AddPrimaryKeyBuilder.java @@ -19,6 +19,9 @@ */ package org.sonar.server.platform.db.migration.version.v83.util; +import com.google.common.collect.Lists; +import java.util.List; + import static com.google.common.base.Preconditions.checkState; import static java.lang.String.format; import static org.sonar.server.platform.db.migration.def.Validations.validateTableName; @@ -27,16 +30,17 @@ import static org.sonar.server.platform.db.migration.sql.CreateTableBuilder.PRIM public class AddPrimaryKeyBuilder { private final String tableName; - private final String primaryKey; + private final List primaryKey; - public AddPrimaryKeyBuilder(String tableName, String column) { + public AddPrimaryKeyBuilder(String tableName, String column, String... moreColumns) { this.tableName = validateTableName(tableName); - this.primaryKey = column; + this.primaryKey = Lists.asList(column, moreColumns); } public String build() { checkState(primaryKey != null, "Primary key is missing"); - return format("ALTER TABLE %s ADD CONSTRAINT %s%s PRIMARY KEY (%s)", tableName, PRIMARY_KEY_PREFIX, tableName, primaryKey); + return format("ALTER TABLE %s ADD CONSTRAINT %s%s PRIMARY KEY (%s)", tableName, PRIMARY_KEY_PREFIX, tableName, + String.join(",", this.primaryKey)); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/util/DropPrimaryKeySqlGenerator.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/util/DropPrimaryKeySqlGenerator.java index 55649d09b1f..1d8771ecd94 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/util/DropPrimaryKeySqlGenerator.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/util/DropPrimaryKeySqlGenerator.java @@ -21,6 +21,7 @@ package org.sonar.server.platform.db.migration.version.v83.util; import java.sql.SQLException; +import java.util.ArrayList; import java.util.List; import org.sonar.db.Database; import org.sonar.db.dialect.Dialect; @@ -34,6 +35,7 @@ import static java.util.Arrays.asList; import static java.util.Collections.singletonList; public class DropPrimaryKeySqlGenerator { + private static final String GENERIC_DROP_CONSTRAINT_STATEMENT = "ALTER TABLE %s DROP CONSTRAINT %s"; private final Database db; private SqlHelper sqlHelper; @@ -43,7 +45,7 @@ public class DropPrimaryKeySqlGenerator { this.sqlHelper = sqlHelper; } - public List generate(String tableName, String columnName) throws SQLException { + public List generate(String tableName, String columnName, boolean isAutoGenerated) throws SQLException { Dialect dialect = db.getDialect(); switch (dialect.getId()) { case PostgreSql.ID: @@ -51,7 +53,7 @@ public class DropPrimaryKeySqlGenerator { case MsSql.ID: return generateForMsSql(tableName, sqlHelper.getMssqlConstraint(tableName)); case Oracle.ID: - return generateForOracle(tableName, sqlHelper.getOracleConstraint(tableName)); + return generateForOracle(tableName, sqlHelper.getOracleConstraint(tableName), isAutoGenerated); case H2.ID: return generateForH2(tableName, columnName, sqlHelper.getH2Constraint(tableName)); default: @@ -60,27 +62,37 @@ public class DropPrimaryKeySqlGenerator { } private List generateForPostgresSql(String tableName, String column, String constraintName) throws SQLException { + List statements = new ArrayList<>(); + statements.add(format("ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT", tableName, column)); + String sequence = sqlHelper.getPostgresSqlSequence(tableName, column); - return asList( - format("ALTER TABLE %s ALTER COLUMN %s DROP DEFAULT", tableName, column), - format("DROP SEQUENCE %s", sequence), - format("ALTER TABLE %s DROP CONSTRAINT %s", tableName, constraintName)); + if (sequence != null) { + statements.add(format("DROP SEQUENCE %s", sequence)); + } + + statements.add(format(GENERIC_DROP_CONSTRAINT_STATEMENT, tableName, constraintName)); + + return statements; } - private static List generateForOracle(String tableName, String constraintName) { - return asList( - format("DROP TRIGGER %s_IDT", tableName), - format("DROP SEQUENCE %s_SEQ", tableName), - format("ALTER TABLE %s DROP CONSTRAINT %s", tableName, constraintName)); + private static List generateForOracle(String tableName, String constraintName, boolean isAutoGenerated) { + List statements = new ArrayList<>(); + if (isAutoGenerated) { + statements.add(format("DROP TRIGGER %s_IDT", tableName)); + statements.add(format("DROP SEQUENCE %s_SEQ", tableName)); + } + + statements.add(format(GENERIC_DROP_CONSTRAINT_STATEMENT, tableName, constraintName)); + return statements; } private static List generateForMsSql(String tableName, String constraintName) { - return singletonList(format("ALTER TABLE %s DROP CONSTRAINT %s", tableName, constraintName)); + return singletonList(format(GENERIC_DROP_CONSTRAINT_STATEMENT, tableName, constraintName)); } private static List generateForH2(String tableName, String column, String constraintName) { return asList( - format("ALTER TABLE %s DROP CONSTRAINT %s", tableName, constraintName), + format(GENERIC_DROP_CONSTRAINT_STATEMENT, tableName, constraintName), format("ALTER TABLE %s ALTER COLUMN %s INTEGER NOT NULL", tableName, column)); } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/util/DropPrimaryKeySqlGeneratorTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/util/DropPrimaryKeySqlGeneratorTest.java index d293ab8ba46..747ffeeb63f 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/util/DropPrimaryKeySqlGeneratorTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/util/DropPrimaryKeySqlGeneratorTest.java @@ -54,7 +54,7 @@ public class DropPrimaryKeySqlGeneratorTest { when(sqlHelper.getPostgresSqlSequence(TABLE_NAME, "id")).thenReturn(TABLE_NAME + "_id_seq"); when(db.getDialect()).thenReturn(POSTGRESQL); - List sqls = underTest.generate(TABLE_NAME, PK_COLUMN); + List sqls = underTest.generate(TABLE_NAME, PK_COLUMN, true); assertThat(sqls).containsExactly("ALTER TABLE issues ALTER COLUMN id DROP DEFAULT", "DROP SEQUENCE issues_id_seq", @@ -66,7 +66,7 @@ public class DropPrimaryKeySqlGeneratorTest { when(sqlHelper.getMssqlConstraint(TABLE_NAME)).thenReturn(CONSTRAINT); when(db.getDialect()).thenReturn(MS_SQL); - List sqls = underTest.generate(TABLE_NAME, PK_COLUMN); + List sqls = underTest.generate(TABLE_NAME, PK_COLUMN, true); assertThat(sqls).containsExactly("ALTER TABLE issues DROP CONSTRAINT pk_id"); } @@ -76,7 +76,7 @@ public class DropPrimaryKeySqlGeneratorTest { when(sqlHelper.getOracleConstraint(TABLE_NAME)).thenReturn(CONSTRAINT); when(db.getDialect()).thenReturn(ORACLE); - List sqls = underTest.generate(TABLE_NAME, PK_COLUMN); + List sqls = underTest.generate(TABLE_NAME, PK_COLUMN, true); assertThat(sqls).containsExactly("DROP TRIGGER issues_IDT", "DROP SEQUENCE issues_SEQ", @@ -88,7 +88,7 @@ public class DropPrimaryKeySqlGeneratorTest { when(sqlHelper.getH2Constraint(TABLE_NAME)).thenReturn(CONSTRAINT); when(db.getDialect()).thenReturn(H2); - List sqls = underTest.generate(TABLE_NAME, PK_COLUMN); + List sqls = underTest.generate(TABLE_NAME, PK_COLUMN, true); assertThat(sqls).containsExactly("ALTER TABLE issues DROP CONSTRAINT pk_id", "ALTER TABLE issues ALTER COLUMN id INTEGER NOT NULL"); -- 2.39.5