From 43f5fc6c2776d3eb0aa5cd76cb160fb6e2d868f5 Mon Sep 17 00:00:00 2001 From: Jacek Date: Wed, 14 Jul 2021 15:03:49 +0200 Subject: [PATCH] SONAR-15131 Fix finding trigger name in Oracle DB --- .../MigrationConfigurationModule.java | 2 + .../sql/DropPrimaryKeySqlGenerator.java | 16 +++- .../db/migration/sql/OracleTriggerFinder.java | 59 +++++++++++++++ .../sql/DropPrimaryKeySqlGeneratorTest.java | 7 +- .../sql/OracleTriggerFinderTest.java | 75 +++++++++++++++++++ 5 files changed, 154 insertions(+), 5 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/OracleTriggerFinder.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/OracleTriggerFinderTest.java diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java index 29853fbbb5e..cd09559e6b5 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java @@ -24,6 +24,7 @@ import org.sonar.server.platform.db.migration.history.MigrationHistoryImpl; import org.sonar.server.platform.db.migration.history.MigrationHistoryMeddler; import org.sonar.server.platform.db.migration.sql.DbPrimaryKeyConstraintFinder; import org.sonar.server.platform.db.migration.sql.DropPrimaryKeySqlGenerator; +import org.sonar.server.platform.db.migration.sql.OracleTriggerFinder; import org.sonar.server.platform.db.migration.step.MigrationStepRegistryImpl; import org.sonar.server.platform.db.migration.step.MigrationStepsProvider; import org.sonar.server.platform.db.migration.version.v00.DbVersion00; @@ -65,6 +66,7 @@ public class MigrationConfigurationModule extends Module { MigrationHistoryMeddler.class, // Utility classes + OracleTriggerFinder.class, DbPrimaryKeyConstraintFinder.class, DropPrimaryKeySqlGenerator.class, NetworkInterfaceProvider.class); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DropPrimaryKeySqlGenerator.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DropPrimaryKeySqlGenerator.java index 6e42b47eb92..6d6a1a4c01b 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DropPrimaryKeySqlGenerator.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DropPrimaryKeySqlGenerator.java @@ -19,6 +19,7 @@ */ package org.sonar.server.platform.db.migration.sql; +import com.google.common.annotations.VisibleForTesting; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; @@ -41,10 +42,17 @@ public class DropPrimaryKeySqlGenerator { private final Database db; private final DbPrimaryKeyConstraintFinder dbConstraintFinder; + private final OracleTriggerFinder oracleTriggerFinder; - public DropPrimaryKeySqlGenerator(Database db, DbPrimaryKeyConstraintFinder dbConstraintFinder) { + public DropPrimaryKeySqlGenerator(Database db, DbPrimaryKeyConstraintFinder dbConstraintFinder, OracleTriggerFinder oracleTriggerFinder) { this.db = db; this.dbConstraintFinder = dbConstraintFinder; + this.oracleTriggerFinder = oracleTriggerFinder; + } + + @VisibleForTesting + public DropPrimaryKeySqlGenerator(Database db, DbPrimaryKeyConstraintFinder dbConstraintFinder) { + this(db, dbConstraintFinder, null); } public List generate(String tableName, String columnName, boolean isAutoGenerated) throws SQLException { @@ -85,10 +93,12 @@ public class DropPrimaryKeySqlGenerator { return statements; } - private static List generateForOracle(String tableName, String constraintName, boolean isAutoGenerated) { + private List generateForOracle(String tableName, String constraintName, boolean isAutoGenerated) + throws SQLException { List statements = new ArrayList<>(); if (isAutoGenerated) { - statements.add(format("DROP TRIGGER %s_IDT", tableName)); + Optional triggerNameOpt = oracleTriggerFinder.findTriggerName(tableName); + triggerNameOpt.ifPresent(s -> statements.add(format("DROP TRIGGER %s", s))); statements.add(format("DROP SEQUENCE %s_SEQ", tableName)); } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/OracleTriggerFinder.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/OracleTriggerFinder.java new file mode 100644 index 00000000000..96712941058 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/OracleTriggerFinder.java @@ -0,0 +1,59 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 SonarSource SA + * mailto:info 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.server.platform.db.migration.sql; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Optional; +import org.sonar.db.Database; + +import static java.lang.String.format; + +public class OracleTriggerFinder { + + private final Database db; + + public OracleTriggerFinder(Database db) { + this.db = db; + } + + public Optional findTriggerName(String tableName) throws SQLException { + String constraintQuery = getTriggerNameQueryBy(tableName); + return executeQuery(constraintQuery); + } + + private Optional executeQuery(String query) throws SQLException { + try (Connection connection = db.getDataSource().getConnection(); + PreparedStatement pstmt = connection + .prepareStatement(query); + ResultSet rs = pstmt.executeQuery()) { + if (rs.next()) { + return Optional.ofNullable(rs.getString(1)); + } + return Optional.empty(); + } + } + + private static String getTriggerNameQueryBy(String tableName) { + return format("SELECT trigger_name FROM user_triggers WHERE upper(table_name) = upper('%s')", tableName); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DropPrimaryKeySqlGeneratorTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DropPrimaryKeySqlGeneratorTest.java index e44495148c8..9c52483ac47 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DropPrimaryKeySqlGeneratorTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DropPrimaryKeySqlGeneratorTest.java @@ -48,8 +48,9 @@ public class DropPrimaryKeySqlGeneratorTest { private final Database db = mock(Database.class); private final DbPrimaryKeyConstraintFinder dbConstraintFinder = mock(DbPrimaryKeyConstraintFinder.class); + private final OracleTriggerFinder oracleTriggerFinder = mock(OracleTriggerFinder.class); - private final DropPrimaryKeySqlGenerator underTest = new DropPrimaryKeySqlGenerator(db, dbConstraintFinder); + private final DropPrimaryKeySqlGenerator underTest = new DropPrimaryKeySqlGenerator(db, dbConstraintFinder, oracleTriggerFinder); @Test public void generate_unknown_dialect() throws SQLException { @@ -100,11 +101,12 @@ public class DropPrimaryKeySqlGeneratorTest { @Test public void generate_for_oracle_autogenerated_true() throws SQLException { when(dbConstraintFinder.findConstraintName(TABLE_NAME)).thenReturn(Optional.of(CONSTRAINT)); + when(oracleTriggerFinder.findTriggerName(TABLE_NAME)).thenReturn(Optional.of("a_trigger")); when(db.getDialect()).thenReturn(ORACLE); List sqls = underTest.generate(TABLE_NAME, PK_COLUMN, true); - assertThat(sqls).containsExactly("DROP TRIGGER issues_IDT", + assertThat(sqls).containsExactly("DROP TRIGGER a_trigger", "DROP SEQUENCE issues_SEQ", "ALTER TABLE issues DROP CONSTRAINT pk_id DROP INDEX"); } @@ -112,6 +114,7 @@ public class DropPrimaryKeySqlGeneratorTest { @Test public void generate_for_oracle_autogenerated_false() throws SQLException { when(dbConstraintFinder.findConstraintName(TABLE_NAME)).thenReturn(Optional.of(CONSTRAINT)); + when(oracleTriggerFinder.findTriggerName(TABLE_NAME)).thenReturn(Optional.of("a_trigger")); when(db.getDialect()).thenReturn(ORACLE); List sqls = underTest.generate(TABLE_NAME, PK_COLUMN, false); diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/OracleTriggerFinderTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/OracleTriggerFinderTest.java new file mode 100644 index 00000000000..f6072b96a7f --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/OracleTriggerFinderTest.java @@ -0,0 +1,75 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 SonarSource SA + * mailto:info 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.server.platform.db.migration.sql; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.Optional; +import javax.sql.DataSource; +import org.junit.Test; +import org.sonar.db.Database; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.mockito.Mockito.mock; +import static org.mockito.Mockito.when; + +public class OracleTriggerFinderTest { + + private final Database mockDb = mock(Database.class); + private final OracleTriggerFinder underTest = new OracleTriggerFinder(mockDb); + + @Test + public void execute_query_to_get_trigger_name() throws SQLException { + DataSource mockDataSource = mock(DataSource.class); + when(mockDb.getDataSource()).thenReturn(mockDataSource); + Connection mockConnection = mock(Connection.class); + when(mockDataSource.getConnection()).thenReturn(mockConnection); + PreparedStatement mockPreparedStatement = mock(PreparedStatement.class); + when(mockConnection.prepareStatement("SELECT trigger_name FROM user_triggers WHERE upper(table_name) = upper('table_with_trg')")).thenReturn(mockPreparedStatement); + ResultSet mockResultSet = mock(ResultSet.class); + when(mockResultSet.next()).thenReturn(true); + when(mockResultSet.getString(1)).thenReturn("my_trigger"); + when(mockPreparedStatement.executeQuery()).thenReturn(mockResultSet); + + Optional foundTrigger = underTest.findTriggerName("table_with_trg"); + + assertThat(foundTrigger).hasValue("my_trigger"); + } + + @Test + public void execute_query_to_get_trigger_name_empty_result() throws SQLException { + DataSource mockDataSource = mock(DataSource.class); + when(mockDb.getDataSource()).thenReturn(mockDataSource); + Connection mockConnection = mock(Connection.class); + when(mockDataSource.getConnection()).thenReturn(mockConnection); + PreparedStatement mockPreparedStatement = mock(PreparedStatement.class); + when(mockConnection.prepareStatement("SELECT trigger_name FROM user_triggers WHERE upper(table_name) = upper('table_with_trg')")).thenReturn(mockPreparedStatement); + ResultSet mockResultSet = mock(ResultSet.class); + when(mockResultSet.next()).thenReturn(false); + when(mockPreparedStatement.executeQuery()).thenReturn(mockResultSet); + + Optional foundTrigger = underTest.findTriggerName("table_with_trg"); + + assertThat(foundTrigger).isEmpty(); + } + +} -- 2.39.5