From 43ab5b022f80236d76d05cba6e1c3d57f41daff3 Mon Sep 17 00:00:00 2001 From: Zipeng WU Date: Mon, 19 Apr 2021 18:18:33 +0200 Subject: [PATCH] SONAR-13849 handle multi-schema during database upgrade & data migration --- .../sql/DbPrimaryKeyConstraintFinder.java | 17 ++++++++++------- .../sql/DbPrimaryKeyConstraintFinderTest.java | 11 +++++++++-- 2 files changed, 19 insertions(+), 9 deletions(-) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DbPrimaryKeyConstraintFinder.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DbPrimaryKeyConstraintFinder.java index 7c307dc5dec..833db36471f 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DbPrimaryKeyConstraintFinder.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DbPrimaryKeyConstraintFinder.java @@ -82,13 +82,16 @@ public class DbPrimaryKeyConstraintFinder { } } - private static String getPostgresSqlConstraintQuery(String tableName) { - return format("SELECT conname " + - "FROM pg_constraint " + - "WHERE conrelid = " + - " (SELECT oid " + - " FROM pg_class " + - " WHERE relname LIKE '%s')", tableName); + private String getPostgresSqlConstraintQuery(String tableName) { + try (Connection connection = db.getDataSource().getConnection()) { + return format("SELECT conname " + + "FROM pg_constraint c " + + "JOIN pg_namespace n on c.connamespace = n.oid " + + "JOIN pg_class cls on c.conrelid = cls.oid " + + "WHERE cls.relname = '%s' AND n.nspname = '%s'", tableName, connection.getSchema()); + } catch (SQLException throwables) { + throw new IllegalStateException("Can not get database connection"); + } } private static String getMssqlConstraintQuery(String tableName) { diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DbPrimaryKeyConstraintFinderTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DbPrimaryKeyConstraintFinderTest.java index 647a4a33a58..faf94bea5c9 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DbPrimaryKeyConstraintFinderTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DbPrimaryKeyConstraintFinderTest.java @@ -19,7 +19,9 @@ */ package org.sonar.server.platform.db.migration.sql; +import java.sql.Connection; import java.sql.SQLException; +import javax.sql.DataSource; import org.junit.Rule; import org.junit.Test; import org.sonar.db.CoreDbTester; @@ -71,11 +73,16 @@ public class DbPrimaryKeyConstraintFinderTest { } @Test - public void getDbVendorSpecificQuery_postgresql() { + public void getDbVendorSpecificQuery_postgresql() throws SQLException { + DataSource dataSource = mock(DataSource.class); + Connection connection = mock(Connection.class); + when(dataSource.getConnection()).thenReturn(connection); + when(connection.getSchema()).thenReturn("SonarQube"); when(dbMock.getDialect()).thenReturn(POSTGRESQL); + when(dbMock.getDataSource()).thenReturn(dataSource); assertThat(underTest.getDbVendorSpecificQuery("my_table")) - .isEqualTo("SELECT conname FROM pg_constraint WHERE conrelid = (SELECT oid FROM pg_class WHERE relname LIKE 'my_table')"); + .isEqualTo("SELECT conname FROM pg_constraint c JOIN pg_namespace n on c.connamespace = n.oid JOIN pg_class cls on c.conrelid = cls.oid WHERE cls.relname = 'my_table' AND n.nspname = 'SonarQube'"); } @Test -- 2.39.5