SONAR-19612 Change index constraint for postgresql 15- and add support for "NULLS NOT DISTINCT" for supported vendor

6 files changed, 54 insertions, 4 deletions

diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/dialect/AbstractDialect.java b/server/sonar-db-core/src/main/java/org/sonar/db/dialect/AbstractDialect.java
index e7332bd751d..aba98d457ae 100644
--- a/server/sonar-db-core/src/main/java/org/sonar/db/dialect/AbstractDialect.java
+++ b/server/sonar-db-core/src/main/java/org/sonar/db/dialect/AbstractDialect.java
@@ -82,6 +82,11 @@ abstract class AbstractDialect implements Dialect {
     return false;
   }
 
+  @Override
+  public boolean supportsNullNotDistinct() {
+    return false;
+  }
+
   Version checkDbVersion(DatabaseMetaData metaData, Version minSupported) throws SQLException {
     int major = metaData.getDatabaseMajorVersion();
     int minor = metaData.getDatabaseMinorVersion();
diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/dialect/Dialect.java b/server/sonar-db-core/src/main/java/org/sonar/db/dialect/Dialect.java
index d3289ab36c0..c92137a0134 100644
--- a/server/sonar-db-core/src/main/java/org/sonar/db/dialect/Dialect.java
+++ b/server/sonar-db-core/src/main/java/org/sonar/db/dialect/Dialect.java
@@ -63,11 +63,16 @@ public interface Dialect {
   boolean supportsUpsert();
 
   /**
+   * Indicates whether the dialect supports the NULLS NOT DISTINCT clause in unique indexes
+   */
+  boolean supportsNullNotDistinct();
+
+  /**
    * This method is called when connecting for the first
    * time to the database.
    *
    * @throws MessageException when validation error must be displayed to user
-   * @throws SQLException in case of error to run the validations
+   * @throws SQLException     in case of error to run the validations
    */
   void init(DatabaseMetaData metaData) throws SQLException;
 }
diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/dialect/H2.java b/server/sonar-db-core/src/main/java/org/sonar/db/dialect/H2.java
index 3e4d1e852f6..b0c5ab2866d 100644
--- a/server/sonar-db-core/src/main/java/org/sonar/db/dialect/H2.java
+++ b/server/sonar-db-core/src/main/java/org/sonar/db/dialect/H2.java
@@ -42,6 +42,11 @@ public class H2 extends AbstractDialect {
   }
 
   @Override
+  public boolean supportsNullNotDistinct() {
+    return true;
+  }
+
+  @Override
   public void init(DatabaseMetaData metaData) {
     LoggerFactory.getLogger(getClass()).warn("H2 database should be used for evaluation purpose only.");
   }
diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/dialect/PostgreSql.java b/server/sonar-db-core/src/main/java/org/sonar/db/dialect/PostgreSql.java
index 6cbdb52d68e..b03a9715ddb 100644
--- a/server/sonar-db-core/src/main/java/org/sonar/db/dialect/PostgreSql.java
+++ b/server/sonar-db-core/src/main/java/org/sonar/db/dialect/PostgreSql.java
@@ -33,9 +33,11 @@ public class PostgreSql extends AbstractDialect {
   static final List<String> INIT_STATEMENTS = List.of("SET standard_conforming_strings=on", "SET backslash_quote=off");
   private static final Version MIN_SUPPORTED_VERSION = Version.create(9, 3, 0);
   private static final Version MIN_UPSERT_VERSION = Version.create(9, 5, 0);
+  private static final Version MIN_NULL_NOT_DISTINCT_VERSION = Version.create(15, 0, 0);
 
   private boolean initialized = false;
   private boolean supportsUpsert = false;
+  private boolean supportsNullNotDistinct = false;
 
   public PostgreSql() {
     super(ID, "org.postgresql.Driver", "true", "false", "SELECT 1");
@@ -63,12 +65,19 @@ public class PostgreSql extends AbstractDialect {
   }
 
   @Override
+  public boolean supportsNullNotDistinct() {
+    checkState(initialized, "onInit() must be called before calling supportsNullNotDistinct()");
+    return supportsNullNotDistinct;
+  }
+
+  @Override
   public void init(DatabaseMetaData metaData) throws SQLException {
     checkState(!initialized, "onInit() must be called once");
 
     Version version = checkDbVersion(metaData, MIN_SUPPORTED_VERSION);
 
     supportsUpsert = version.compareTo(MIN_UPSERT_VERSION) >= 0;
+    supportsNullNotDistinct = version.compareTo(MIN_NULL_NOT_DISTINCT_VERSION) >= 0;
     if (!supportsUpsert) {
       LoggerFactory.getLogger(getClass()).warn("Upgrading PostgreSQL to {} or greater is recommended for better performances", MIN_UPSERT_VERSION);
     }
diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/dialect/H2Test.java b/server/sonar-db-core/src/test/java/org/sonar/db/dialect/H2Test.java
index 71ed8ac7693..94263d811ce 100644
--- a/server/sonar-db-core/src/test/java/org/sonar/db/dialect/H2Test.java
+++ b/server/sonar-db-core/src/test/java/org/sonar/db/dialect/H2Test.java
@@ -80,4 +80,9 @@ public class H2Test {
   public void supportsUpsert_returns_false() {
     assertThat(underTest.supportsUpsert()).isFalse();
   }
+
+  @Test
+  public void supportsNullNotDistinct_returns_true() {
+    assertThat(underTest.supportsNullNotDistinct()).isTrue();
+  }
 }
diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/dialect/PostgreSqlTest.java b/server/sonar-db-core/src/test/java/org/sonar/db/dialect/PostgreSqlTest.java
index ac6553a4173..36dc64e16d0 100644
--- a/server/sonar-db-core/src/test/java/org/sonar/db/dialect/PostgreSqlTest.java
+++ b/server/sonar-db-core/src/test/java/org/sonar/db/dialect/PostgreSqlTest.java
@@ -82,7 +82,7 @@ public class PostgreSqlTest {
   @Test
   public void postgresql_9_2_is_not_supported() throws Exception {
     assertThatThrownBy(() -> {
-      DatabaseMetaData metadata = newMetadata( 9, 2);
+      DatabaseMetaData metadata = newMetadata(9, 2);
       underTest.init(metadata);
     })
       .isInstanceOf(MessageException.class)
@@ -91,7 +91,7 @@ public class PostgreSqlTest {
 
   @Test
   public void postgresql_9_3_is_supported_without_upsert() throws Exception {
-    DatabaseMetaData metadata = newMetadata( 9, 3);
+    DatabaseMetaData metadata = newMetadata(9, 3);
     underTest.init(metadata);
 
     assertThat(underTest.supportsUpsert()).isFalse();
@@ -100,7 +100,7 @@ public class PostgreSqlTest {
 
   @Test
   public void postgresql_9_5_is_supported_with_upsert() throws Exception {
-    DatabaseMetaData metadata = newMetadata( 9, 5);
+    DatabaseMetaData metadata = newMetadata(9, 5);
     underTest.init(metadata);
 
     assertThat(underTest.supportsUpsert()).isTrue();
@@ -124,6 +124,27 @@ public class PostgreSqlTest {
       .hasMessage("onInit() must be called before calling supportsUpsert()");
   }
 
+  @Test
+  public void supportsNullNotDistinct_throws_ISE_if_not_initialized() {
+    assertThatThrownBy(() -> underTest.supportsNullNotDistinct())
+      .isInstanceOf(IllegalStateException.class)
+      .hasMessage("onInit() must be called before calling supportsNullNotDistinct()");
+  }
+
+  @Test
+  public void supportsNullNotDistinct_shouldReturnTrue_WhenPostgres15OrGreater() throws SQLException {
+    DatabaseMetaData metadata = newMetadata(15, 0);
+    underTest.init(metadata);
+    assertThat(underTest.supportsNullNotDistinct()).isTrue();
+  }
+
+  @Test
+  public void supportsNullNotDistinct_shouldReturnFalse_WhenPostgres14OrLesser() throws SQLException {
+    DatabaseMetaData metadata = newMetadata(14, 0);
+    underTest.init(metadata);
+    assertThat(underTest.supportsNullNotDistinct()).isFalse();
+  }
+
   private DatabaseMetaData newMetadata(int dbMajorVersion, int dbMinorVersion) throws SQLException {
     DatabaseMetaData metadata = mock(DatabaseMetaData.class, Mockito.RETURNS_DEEP_STUBS);
     when(metadata.getDatabaseMajorVersion()).thenReturn(dbMajorVersion);