diff options
21 files changed, 265 insertions, 112 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); diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index 100f7da4449..f60e01c4f7d 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -33,7 +33,7 @@ CREATE TABLE "ACTIVE_RULES"( "RULE_UUID" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "ACTIVE_RULES" ADD CONSTRAINT "PK_ACTIVE_RULES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PROFILE_RULE_UUIDS" ON "ACTIVE_RULES"("PROFILE_UUID" NULLS FIRST, "RULE_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PROFILE_RULE_UUIDS" ON "ACTIVE_RULES"("PROFILE_UUID" NULLS FIRST, "RULE_UUID" NULLS FIRST); CREATE TABLE "ALM_PATS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -44,7 +44,7 @@ CREATE TABLE "ALM_PATS"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "ALM_PATS" ADD CONSTRAINT "PK_ALM_PATS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_ALM_PATS" ON "ALM_PATS"("USER_UUID" NULLS FIRST, "ALM_SETTING_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_ALM_PATS" ON "ALM_PATS"("USER_UUID" NULLS FIRST, "ALM_SETTING_UUID" NULLS FIRST); CREATE TABLE "ALM_SETTINGS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -61,7 +61,7 @@ CREATE TABLE "ALM_SETTINGS"( "WEBHOOK_SECRET" CHARACTER VARYING(160) ); ALTER TABLE "ALM_SETTINGS" ADD CONSTRAINT "PK_ALM_SETTINGS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_ALM_SETTINGS" ON "ALM_SETTINGS"("KEE" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_ALM_SETTINGS" ON "ALM_SETTINGS"("KEE" NULLS FIRST); CREATE TABLE "ANALYSIS_PROPERTIES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -99,7 +99,7 @@ CREATE TABLE "APP_BRANCH_PROJECT_BRANCH"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "APP_BRANCH_PROJECT_BRANCH" ADD CONSTRAINT "PK_APP_BRANCH_PROJECT_BRANCH" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_APP_BRANCH_PROJ" ON "APP_BRANCH_PROJECT_BRANCH"("APPLICATION_BRANCH_UUID" NULLS FIRST, "PROJECT_BRANCH_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_APP_BRANCH_PROJ" ON "APP_BRANCH_PROJECT_BRANCH"("APPLICATION_BRANCH_UUID" NULLS FIRST, "PROJECT_BRANCH_UUID" NULLS FIRST); CREATE INDEX "IDX_ABPB_APP_UUID" ON "APP_BRANCH_PROJECT_BRANCH"("APPLICATION_UUID" NULLS FIRST); CREATE INDEX "IDX_ABPB_APP_BRANCH_UUID" ON "APP_BRANCH_PROJECT_BRANCH"("APPLICATION_BRANCH_UUID" NULLS FIRST); CREATE INDEX "IDX_ABPB_PROJ_UUID" ON "APP_BRANCH_PROJECT_BRANCH"("PROJECT_UUID" NULLS FIRST); @@ -112,7 +112,7 @@ CREATE TABLE "APP_PROJECTS"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "APP_PROJECTS" ADD CONSTRAINT "PK_APP_PROJECTS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_APP_PROJECTS" ON "APP_PROJECTS"("APPLICATION_UUID" NULLS FIRST, "PROJECT_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_APP_PROJECTS" ON "APP_PROJECTS"("APPLICATION_UUID" NULLS FIRST, "PROJECT_UUID" NULLS FIRST); CREATE INDEX "IDX_APP_PROJ_APPLICATION_UUID" ON "APP_PROJECTS"("APPLICATION_UUID" NULLS FIRST); CREATE INDEX "IDX_APP_PROJ_PROJECT_UUID" ON "APP_PROJECTS"("PROJECT_UUID" NULLS FIRST); @@ -244,9 +244,9 @@ CREATE TABLE "COMPONENTS"( "CREATED_AT" TIMESTAMP ); CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "COMPONENTS_UUID" ON "COMPONENTS"("UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "COMPONENTS_UUID" ON "COMPONENTS"("UUID" NULLS FIRST); CREATE INDEX "COMPONENTS_BRANCH_UUID" ON "COMPONENTS"("BRANCH_UUID" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "COMPONENTS_KEE_BRANCH_UUID" ON "COMPONENTS"("KEE" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "COMPONENTS_KEE_BRANCH_UUID" ON "COMPONENTS"("KEE" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); CREATE TABLE "DEFAULT_QPROFILES"( "LANGUAGE" CHARACTER VARYING(20) NOT NULL, @@ -255,7 +255,7 @@ CREATE TABLE "DEFAULT_QPROFILES"( "UPDATED_AT" BIGINT NOT NULL ); ALTER TABLE "DEFAULT_QPROFILES" ADD CONSTRAINT "PK_DEFAULT_QPROFILES" PRIMARY KEY("LANGUAGE"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_DEFAULT_QPROFILES_UUID" ON "DEFAULT_QPROFILES"("QPROFILE_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_DEFAULT_QPROFILES_UUID" ON "DEFAULT_QPROFILES"("QPROFILE_UUID" NULLS FIRST); CREATE TABLE "DEPRECATED_RULE_KEYS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -265,7 +265,7 @@ CREATE TABLE "DEPRECATED_RULE_KEYS"( "RULE_UUID" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "DEPRECATED_RULE_KEYS" ADD CONSTRAINT "PK_DEPRECATED_RULE_KEYS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS"("OLD_REPOSITORY_KEY" NULLS FIRST, "OLD_RULE_KEY" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS"("OLD_REPOSITORY_KEY" NULLS FIRST, "OLD_RULE_KEY" NULLS FIRST); CREATE INDEX "RULE_UUID_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS"("RULE_UUID" NULLS FIRST); CREATE TABLE "DUPLICATIONS_INDEX"( @@ -305,7 +305,7 @@ CREATE TABLE "EVENT_COMPONENT_CHANGES"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "EVENT_COMPONENT_CHANGES" ADD CONSTRAINT "PK_EVENT_COMPONENT_CHANGES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "EVENT_COMPONENT_CHANGES_UNIQUE" ON "EVENT_COMPONENT_CHANGES"("EVENT_UUID" NULLS FIRST, "CHANGE_CATEGORY" NULLS FIRST, "COMPONENT_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "EVENT_COMPONENT_CHANGES_UNIQUE" ON "EVENT_COMPONENT_CHANGES"("EVENT_UUID" NULLS FIRST, "CHANGE_CATEGORY" NULLS FIRST, "COMPONENT_UUID" NULLS FIRST); CREATE INDEX "EVENT_CPNT_CHANGES_CPNT" ON "EVENT_COMPONENT_CHANGES"("EVENT_COMPONENT_UUID" NULLS FIRST); CREATE INDEX "EVENT_CPNT_CHANGES_ANALYSIS" ON "EVENT_COMPONENT_CHANGES"("EVENT_ANALYSIS_UUID" NULLS FIRST); @@ -330,7 +330,7 @@ CREATE TABLE "EXTERNAL_GROUPS"( "EXTERNAL_IDENTITY_PROVIDER" CHARACTER VARYING(100) NOT NULL ); ALTER TABLE "EXTERNAL_GROUPS" ADD CONSTRAINT "PK_EXTERNAL_GROUPS" PRIMARY KEY("GROUP_UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_EXT_GRP_EXT_ID_PROVIDER" ON "EXTERNAL_GROUPS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_GROUP_ID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_EXT_GRP_EXT_ID_PROVIDER" ON "EXTERNAL_GROUPS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_GROUP_ID" NULLS FIRST); CREATE TABLE "FILE_SOURCES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -347,7 +347,7 @@ CREATE TABLE "FILE_SOURCES"( "UPDATED_AT" BIGINT NOT NULL ); ALTER TABLE "FILE_SOURCES" ADD CONSTRAINT "PK_FILE_SOURCES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "FILE_SOURCES_FILE_UUID" ON "FILE_SOURCES"("FILE_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "FILE_SOURCES_FILE_UUID" ON "FILE_SOURCES"("FILE_UUID" NULLS FIRST); CREATE INDEX "FILE_SOURCES_PROJECT_UUID" ON "FILE_SOURCES"("PROJECT_UUID" NULLS FIRST); CREATE INDEX "FILE_SOURCES_UPDATED_AT" ON "FILE_SOURCES"("UPDATED_AT" NULLS FIRST); @@ -363,7 +363,7 @@ CREATE TABLE "GITHUB_PERMS_MAPPING"( "SONARQUBE_PERMISSION" CHARACTER VARYING(64) NOT NULL ); ALTER TABLE "GITHUB_PERMS_MAPPING" ADD CONSTRAINT "PK_GITHUB_PERMS_MAPPING" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_GITHUB_PERM_MAPPINGS" ON "GITHUB_PERMS_MAPPING"("GITHUB_ROLE" NULLS FIRST, "SONARQUBE_PERMISSION" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_GITHUB_PERM_MAPPINGS" ON "GITHUB_PERMS_MAPPING"("GITHUB_ROLE" NULLS FIRST, "SONARQUBE_PERMISSION" NULLS FIRST); CREATE TABLE "GROUP_ROLES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -372,7 +372,7 @@ CREATE TABLE "GROUP_ROLES"( "GROUP_UUID" CHARACTER VARYING(40) ); ALTER TABLE "GROUP_ROLES" ADD CONSTRAINT "PK_GROUP_ROLES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES"("GROUP_UUID" NULLS FIRST, "ENTITY_UUID" NULLS FIRST, "ROLE" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES"("GROUP_UUID" NULLS FIRST, "ENTITY_UUID" NULLS FIRST, "ROLE" NULLS FIRST); CREATE INDEX "GROUP_ROLES_ENTITY_UUID" ON "GROUP_ROLES"("ENTITY_UUID" NULLS FIRST); CREATE TABLE "GROUPS"( @@ -383,7 +383,7 @@ CREATE TABLE "GROUPS"( "UPDATED_AT" TIMESTAMP ); ALTER TABLE "GROUPS" ADD CONSTRAINT "PK_GROUPS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_GROUPS_NAME" ON "GROUPS"("NAME" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_GROUPS_NAME" ON "GROUPS"("NAME" NULLS FIRST); CREATE TABLE "GROUPS_USERS"( "GROUP_UUID" CHARACTER VARYING(40) NOT NULL, @@ -391,7 +391,7 @@ CREATE TABLE "GROUPS_USERS"( ); CREATE INDEX "INDEX_GROUPS_USERS_GROUP_UUID" ON "GROUPS_USERS"("GROUP_UUID" NULLS FIRST); CREATE INDEX "INDEX_GROUPS_USERS_USER_UUID" ON "GROUPS_USERS"("USER_UUID" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "GROUPS_USERS_UNIQUE" ON "GROUPS_USERS"("USER_UUID" NULLS FIRST, "GROUP_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "GROUPS_USERS_UNIQUE" ON "GROUPS_USERS"("USER_UUID" NULLS FIRST, "GROUP_UUID" NULLS FIRST); CREATE TABLE "INTERNAL_COMPONENT_PROPS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -402,7 +402,7 @@ CREATE TABLE "INTERNAL_COMPONENT_PROPS"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "INTERNAL_COMPONENT_PROPS" ADD CONSTRAINT "PK_INTERNAL_COMPONENT_PROPS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQUE_COMPONENT_UUID_KEE" ON "INTERNAL_COMPONENT_PROPS"("COMPONENT_UUID" NULLS FIRST, "KEE" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQUE_COMPONENT_UUID_KEE" ON "INTERNAL_COMPONENT_PROPS"("COMPONENT_UUID" NULLS FIRST, "KEE" NULLS FIRST); CREATE TABLE "INTERNAL_PROPERTIES"( "KEE" CHARACTER VARYING(40) NOT NULL, @@ -478,7 +478,7 @@ CREATE TABLE "ISSUES_IMPACTS"( "SEVERITY" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "ISSUES_IMPACTS" ADD CONSTRAINT "PK_ISSUES_IMPACTS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_ISS_KEY_SOF_QUAL" ON "ISSUES_IMPACTS"("ISSUE_KEY" NULLS FIRST, "SOFTWARE_QUALITY" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_ISS_KEY_SOF_QUAL" ON "ISSUES_IMPACTS"("ISSUE_KEY" NULLS FIRST, "SOFTWARE_QUALITY" NULLS FIRST); CREATE TABLE "LIVE_MEASURES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -494,7 +494,7 @@ CREATE TABLE "LIVE_MEASURES"( ); ALTER TABLE "LIVE_MEASURES" ADD CONSTRAINT "PK_LIVE_MEASURES" PRIMARY KEY("UUID"); CREATE INDEX "LIVE_MEASURES_PROJECT" ON "LIVE_MEASURES"("PROJECT_UUID" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "LIVE_MEASURES_COMPONENT" ON "LIVE_MEASURES"("COMPONENT_UUID" NULLS FIRST, "METRIC_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "LIVE_MEASURES_COMPONENT" ON "LIVE_MEASURES"("COMPONENT_UUID" NULLS FIRST, "METRIC_UUID" NULLS FIRST); CREATE TABLE "METRICS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -514,7 +514,7 @@ CREATE TABLE "METRICS"( "DECIMAL_SCALE" INTEGER ); ALTER TABLE "METRICS" ADD CONSTRAINT "PK_METRICS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "METRICS_UNIQUE_NAME" ON "METRICS"("NAME" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "METRICS_UNIQUE_NAME" ON "METRICS"("NAME" NULLS FIRST); CREATE TABLE "NEW_CODE_PERIODS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -527,7 +527,7 @@ CREATE TABLE "NEW_CODE_PERIODS"( "PREVIOUS_NON_COMPLIANT_VALUE" CHARACTER VARYING(255) ); ALTER TABLE "NEW_CODE_PERIODS" ADD CONSTRAINT "PK_NEW_CODE_PERIODS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_NEW_CODE_PERIODS" ON "NEW_CODE_PERIODS"("PROJECT_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_NEW_CODE_PERIODS" ON "NEW_CODE_PERIODS"("PROJECT_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); CREATE INDEX "IDX_NCP_TYPE" ON "NEW_CODE_PERIODS"("TYPE" NULLS FIRST); CREATE INDEX "IDX_NCP_VALUE" ON "NEW_CODE_PERIODS"("VALUE" NULLS FIRST); @@ -537,7 +537,7 @@ CREATE TABLE "NEW_CODE_REFERENCE_ISSUES"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "NEW_CODE_REFERENCE_ISSUES" ADD CONSTRAINT "PK_NEW_CODE_REFERENCE_ISSUES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_NEW_CODE_REFERENCE_ISSUES" ON "NEW_CODE_REFERENCE_ISSUES"("ISSUE_KEY" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_NEW_CODE_REFERENCE_ISSUES" ON "NEW_CODE_REFERENCE_ISSUES"("ISSUE_KEY" NULLS FIRST); CREATE TABLE "NOTIFICATIONS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -588,7 +588,7 @@ CREATE TABLE "PERM_TPL_CHARACTERISTICS"( "TEMPLATE_UUID" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "PERM_TPL_CHARACTERISTICS" ADD CONSTRAINT "PK_PERM_TPL_CHARACTERISTICS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PERM_TPL_CHARAC" ON "PERM_TPL_CHARACTERISTICS"("TEMPLATE_UUID" NULLS FIRST, "PERMISSION_KEY" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PERM_TPL_CHARAC" ON "PERM_TPL_CHARACTERISTICS"("TEMPLATE_UUID" NULLS FIRST, "PERMISSION_KEY" NULLS FIRST); CREATE TABLE "PERMISSION_TEMPLATES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -611,7 +611,7 @@ CREATE TABLE "PLUGINS"( "REMOVED" BOOLEAN DEFAULT FALSE NOT NULL ); ALTER TABLE "PLUGINS" ADD CONSTRAINT "PK_PLUGINS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "PLUGINS_KEY" ON "PLUGINS"("KEE" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "PLUGINS_KEY" ON "PLUGINS"("KEE" NULLS FIRST); CREATE TABLE "PORTFOLIO_PROJ_BRANCHES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -628,7 +628,7 @@ CREATE TABLE "PORTFOLIO_PROJECTS"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "PORTFOLIO_PROJECTS" ADD CONSTRAINT "PK_PORTFOLIO_PROJECTS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PORTFOLIO_PROJECTS" ON "PORTFOLIO_PROJECTS"("PORTFOLIO_UUID" NULLS FIRST, "PROJECT_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PORTFOLIO_PROJECTS" ON "PORTFOLIO_PROJECTS"("PORTFOLIO_UUID" NULLS FIRST, "PROJECT_UUID" NULLS FIRST); CREATE TABLE "PORTFOLIO_REFERENCES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -638,7 +638,7 @@ CREATE TABLE "PORTFOLIO_REFERENCES"( "BRANCH_UUID" CHARACTER VARYING(255) ); ALTER TABLE "PORTFOLIO_REFERENCES" ADD CONSTRAINT "PK_PORTFOLIO_REFERENCES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PORTFOLIO_REFERENCES" ON "PORTFOLIO_REFERENCES"("PORTFOLIO_UUID" NULLS FIRST, "REFERENCE_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PORTFOLIO_REFERENCES" ON "PORTFOLIO_REFERENCES"("PORTFOLIO_UUID" NULLS FIRST, "REFERENCE_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); CREATE TABLE "PORTFOLIOS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -655,7 +655,7 @@ CREATE TABLE "PORTFOLIOS"( "BRANCH_KEY" CHARACTER VARYING(255) ); ALTER TABLE "PORTFOLIOS" ADD CONSTRAINT "PK_PORTFOLIOS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PORTFOLIOS_KEE" ON "PORTFOLIOS"("KEE" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PORTFOLIOS_KEE" ON "PORTFOLIOS"("KEE" NULLS FIRST); CREATE TABLE "PROJECT_ALM_SETTINGS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -669,7 +669,7 @@ CREATE TABLE "PROJECT_ALM_SETTINGS"( "MONOREPO" BOOLEAN NOT NULL ); ALTER TABLE "PROJECT_ALM_SETTINGS" ADD CONSTRAINT "PK_PROJECT_ALM_SETTINGS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PROJECT_ALM_SETTINGS" ON "PROJECT_ALM_SETTINGS"("PROJECT_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PROJECT_ALM_SETTINGS" ON "PROJECT_ALM_SETTINGS"("PROJECT_UUID" NULLS FIRST); CREATE INDEX "PROJECT_ALM_SETTINGS_ALM" ON "PROJECT_ALM_SETTINGS"("ALM_SETTING_UUID" NULLS FIRST); CREATE INDEX "PROJECT_ALM_SETTINGS_SLUG" ON "PROJECT_ALM_SETTINGS"("ALM_SLUG" NULLS FIRST); @@ -681,7 +681,7 @@ CREATE TABLE "PROJECT_BADGE_TOKEN"( "UPDATED_AT" BIGINT NOT NULL ); ALTER TABLE "PROJECT_BADGE_TOKEN" ADD CONSTRAINT "PK_PROJECT_BADGE_TOKEN" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PROJECT_BADGE_TOKEN" ON "PROJECT_BADGE_TOKEN"("PROJECT_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PROJECT_BADGE_TOKEN" ON "PROJECT_BADGE_TOKEN"("PROJECT_UUID" NULLS FIRST); CREATE TABLE "PROJECT_BRANCHES"( "UUID" CHARACTER VARYING(50) NOT NULL, @@ -698,7 +698,7 @@ CREATE TABLE "PROJECT_BRANCHES"( "IS_MAIN" BOOLEAN NOT NULL ); ALTER TABLE "PROJECT_BRANCHES" ADD CONSTRAINT "PK_PROJECT_BRANCHES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PROJECT_BRANCHES" ON "PROJECT_BRANCHES"("BRANCH_TYPE" NULLS FIRST, "PROJECT_UUID" NULLS FIRST, "KEE" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PROJECT_BRANCHES" ON "PROJECT_BRANCHES"("BRANCH_TYPE" NULLS FIRST, "PROJECT_UUID" NULLS FIRST, "KEE" NULLS FIRST); CREATE INDEX "PROJECT_BRANCHES_PROJECT_UUID" ON "PROJECT_BRANCHES"("PROJECT_UUID" NULLS FIRST); CREATE TABLE "PROJECT_LINKS"( @@ -735,7 +735,7 @@ CREATE TABLE "PROJECT_QGATES"( "QUALITY_GATE_UUID" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "PROJECT_QGATES" ADD CONSTRAINT "PK_PROJECT_QGATES" PRIMARY KEY("PROJECT_UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PROJECT_QGATES" ON "PROJECT_QGATES"("PROJECT_UUID" NULLS FIRST, "QUALITY_GATE_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PROJECT_QGATES" ON "PROJECT_QGATES"("PROJECT_UUID" NULLS FIRST, "QUALITY_GATE_UUID" NULLS FIRST); CREATE TABLE "PROJECT_QPROFILES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -743,7 +743,7 @@ CREATE TABLE "PROJECT_QPROFILES"( "PROFILE_KEY" CHARACTER VARYING(50) NOT NULL ); ALTER TABLE "PROJECT_QPROFILES" ADD CONSTRAINT "PK_PROJECT_QPROFILES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PROJECT_QPROFILES" ON "PROJECT_QPROFILES"("PROJECT_UUID" NULLS FIRST, "PROFILE_KEY" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PROJECT_QPROFILES" ON "PROJECT_QPROFILES"("PROJECT_UUID" NULLS FIRST, "PROFILE_KEY" NULLS FIRST); CREATE TABLE "PROJECTS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -759,7 +759,7 @@ CREATE TABLE "PROJECTS"( "CREATION_METHOD" CHARACTER VARYING(50) NOT NULL ); ALTER TABLE "PROJECTS" ADD CONSTRAINT "PK_NEW_PROJECTS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_PROJECTS_KEE" ON "PROJECTS"("KEE" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PROJECTS_KEE" ON "PROJECTS"("KEE" NULLS FIRST); CREATE INDEX "IDX_QUALIFIER" ON "PROJECTS"("QUALIFIER" NULLS FIRST); CREATE TABLE "PROPERTIES"( @@ -825,7 +825,7 @@ CREATE TABLE "QPROFILE_EDIT_GROUPS"( ); ALTER TABLE "QPROFILE_EDIT_GROUPS" ADD CONSTRAINT "PK_QPROFILE_EDIT_GROUPS" PRIMARY KEY("UUID"); CREATE INDEX "QPROFILE_EDIT_GROUPS_QPROFILE" ON "QPROFILE_EDIT_GROUPS"("QPROFILE_UUID" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "QPROFILE_EDIT_GROUPS_UNIQUE" ON "QPROFILE_EDIT_GROUPS"("GROUP_UUID" NULLS FIRST, "QPROFILE_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "QPROFILE_EDIT_GROUPS_UNIQUE" ON "QPROFILE_EDIT_GROUPS"("GROUP_UUID" NULLS FIRST, "QPROFILE_UUID" NULLS FIRST); CREATE TABLE "QPROFILE_EDIT_USERS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -835,7 +835,7 @@ CREATE TABLE "QPROFILE_EDIT_USERS"( ); ALTER TABLE "QPROFILE_EDIT_USERS" ADD CONSTRAINT "PK_QPROFILE_EDIT_USERS" PRIMARY KEY("UUID"); CREATE INDEX "QPROFILE_EDIT_USERS_QPROFILE" ON "QPROFILE_EDIT_USERS"("QPROFILE_UUID" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "QPROFILE_EDIT_USERS_UNIQUE" ON "QPROFILE_EDIT_USERS"("USER_UUID" NULLS FIRST, "QPROFILE_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "QPROFILE_EDIT_USERS_UNIQUE" ON "QPROFILE_EDIT_USERS"("USER_UUID" NULLS FIRST, "QPROFILE_UUID" NULLS FIRST); CREATE TABLE "QUALITY_GATE_CONDITIONS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -864,7 +864,7 @@ CREATE TABLE "REPORT_SCHEDULES"( "LAST_SEND_TIME_IN_MS" BIGINT NOT NULL ); ALTER TABLE "REPORT_SCHEDULES" ADD CONSTRAINT "PK_REPORT_SCHEDULES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_REPORT_SCHEDULES" ON "REPORT_SCHEDULES"("PORTFOLIO_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_REPORT_SCHEDULES" ON "REPORT_SCHEDULES"("PORTFOLIO_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); CREATE TABLE "REPORT_SUBSCRIPTIONS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -873,7 +873,7 @@ CREATE TABLE "REPORT_SUBSCRIPTIONS"( "USER_UUID" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "REPORT_SUBSCRIPTIONS" ADD CONSTRAINT "PK_REPORT_SUBSCRIPTIONS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_REPORT_SUBSCRIPTIONS" ON "REPORT_SUBSCRIPTIONS"("PORTFOLIO_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST, "USER_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_REPORT_SUBSCRIPTIONS" ON "REPORT_SUBSCRIPTIONS"("PORTFOLIO_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST, "USER_UUID" NULLS FIRST); CREATE TABLE "RULE_CHANGES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -892,7 +892,7 @@ CREATE TABLE "RULE_DESC_SECTIONS"( "CONTEXT_DISPLAY_NAME" CHARACTER VARYING(50) ); ALTER TABLE "RULE_DESC_SECTIONS" ADD CONSTRAINT "PK_RULE_DESC_SECTIONS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_RULE_DESC_SECTIONS" ON "RULE_DESC_SECTIONS"("RULE_UUID" NULLS FIRST, "KEE" NULLS FIRST, "CONTEXT_KEY" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_RULE_DESC_SECTIONS" ON "RULE_DESC_SECTIONS"("RULE_UUID" NULLS FIRST, "KEE" NULLS FIRST, "CONTEXT_KEY" NULLS FIRST); CREATE TABLE "RULE_IMPACT_CHANGES"( "NEW_SOFTWARE_QUALITY" CHARACTER VARYING(40), @@ -952,7 +952,7 @@ CREATE TABLE "RULES"( "CLEAN_CODE_ATTRIBUTE" CHARACTER VARYING(40) ); ALTER TABLE "RULES" ADD CONSTRAINT "PK_RULES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "RULES_REPO_KEY" ON "RULES"("PLUGIN_RULE_KEY" NULLS FIRST, "PLUGIN_NAME" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "RULES_REPO_KEY" ON "RULES"("PLUGIN_RULE_KEY" NULLS FIRST, "PLUGIN_NAME" NULLS FIRST); CREATE TABLE "RULES_DEFAULT_IMPACTS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -961,7 +961,7 @@ CREATE TABLE "RULES_DEFAULT_IMPACTS"( "SEVERITY" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "RULES_DEFAULT_IMPACTS" ADD CONSTRAINT "PK_RULES_DEFAULT_IMPACTS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_RUL_UUID_SOF_QUAL" ON "RULES_DEFAULT_IMPACTS"("RULE_UUID" NULLS FIRST, "SOFTWARE_QUALITY" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_RUL_UUID_SOF_QUAL" ON "RULES_DEFAULT_IMPACTS"("RULE_UUID" NULLS FIRST, "SOFTWARE_QUALITY" NULLS FIRST); CREATE TABLE "RULES_PARAMETERS"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -973,7 +973,7 @@ CREATE TABLE "RULES_PARAMETERS"( ); ALTER TABLE "RULES_PARAMETERS" ADD CONSTRAINT "PK_RULES_PARAMETERS" PRIMARY KEY("UUID"); CREATE INDEX "RULES_PARAMETERS_RULE_UUID" ON "RULES_PARAMETERS"("RULE_UUID" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "RULES_PARAMETERS_UNIQUE" ON "RULES_PARAMETERS"("RULE_UUID" NULLS FIRST, "NAME" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "RULES_PARAMETERS_UNIQUE" ON "RULES_PARAMETERS"("RULE_UUID" NULLS FIRST, "NAME" NULLS FIRST); CREATE TABLE "RULES_PROFILES"( "UUID" CHARACTER VARYING(40) NOT NULL, @@ -993,7 +993,7 @@ CREATE TABLE "SAML_MESSAGE_IDS"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "SAML_MESSAGE_IDS" ADD CONSTRAINT "PK_SAML_MESSAGE_IDS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "SAML_MESSAGE_IDS_UNIQUE" ON "SAML_MESSAGE_IDS"("MESSAGE_ID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "SAML_MESSAGE_IDS_UNIQUE" ON "SAML_MESSAGE_IDS"("MESSAGE_ID" NULLS FIRST); CREATE TABLE "SCANNER_ANALYSIS_CACHE"( "BRANCH_UUID" CHARACTER VARYING(40) NOT NULL, @@ -1006,14 +1006,14 @@ CREATE TABLE "SCIM_GROUPS"( "GROUP_UUID" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "SCIM_GROUPS" ADD CONSTRAINT "PK_SCIM_GROUPS" PRIMARY KEY("SCIM_UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_SCIM_GROUP_UUID" ON "SCIM_GROUPS"("GROUP_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_SCIM_GROUP_UUID" ON "SCIM_GROUPS"("GROUP_UUID" NULLS FIRST); CREATE TABLE "SCIM_USERS"( "SCIM_UUID" CHARACTER VARYING(40) NOT NULL, "USER_UUID" CHARACTER VARYING(40) NOT NULL ); ALTER TABLE "SCIM_USERS" ADD CONSTRAINT "PK_SCIM_USERS" PRIMARY KEY("SCIM_UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_SCIM_USERS_USER_UUID" ON "SCIM_USERS"("USER_UUID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_SCIM_USERS_USER_UUID" ON "SCIM_USERS"("USER_UUID" NULLS FIRST); CREATE TABLE "SCM_ACCOUNTS"( "USER_UUID" CHARACTER VARYING(255) NOT NULL, @@ -1058,7 +1058,7 @@ CREATE TABLE "USER_DISMISSED_MESSAGES"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "USER_DISMISSED_MESSAGES" ADD CONSTRAINT "PK_USER_DISMISSED_MESSAGES" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_USER_DISMISSED_MESSAGES" ON "USER_DISMISSED_MESSAGES"("USER_UUID" NULLS FIRST, "PROJECT_UUID" NULLS FIRST, "MESSAGE_TYPE" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_USER_DISMISSED_MESSAGES" ON "USER_DISMISSED_MESSAGES"("USER_UUID" NULLS FIRST, "PROJECT_UUID" NULLS FIRST, "MESSAGE_TYPE" NULLS FIRST); CREATE INDEX "UDM_PROJECT_UUID" ON "USER_DISMISSED_MESSAGES"("PROJECT_UUID" NULLS FIRST); CREATE INDEX "UDM_MESSAGE_TYPE" ON "USER_DISMISSED_MESSAGES"("MESSAGE_TYPE" NULLS FIRST); @@ -1084,8 +1084,8 @@ CREATE TABLE "USER_TOKENS"( "PROJECT_UUID" CHARACTER VARYING(40) ); ALTER TABLE "USER_TOKENS" ADD CONSTRAINT "PK_USER_TOKENS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID" NULLS FIRST, "NAME" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID" NULLS FIRST, "NAME" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH" NULLS FIRST); CREATE TABLE "USERS"( "UUID" CHARACTER VARYING(255) NOT NULL, @@ -1109,10 +1109,10 @@ CREATE TABLE "USERS"( "LAST_SONARLINT_CONNECTION" BIGINT ); ALTER TABLE "USERS" ADD CONSTRAINT "PK_USERS" PRIMARY KEY("UUID"); -CREATE UNIQUE NULLS DISTINCT INDEX "USERS_LOGIN" ON "USERS"("LOGIN" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "USERS_LOGIN" ON "USERS"("LOGIN" NULLS FIRST); CREATE INDEX "USERS_UPDATED_AT" ON "USERS"("UPDATED_AT" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_EXTERNAL_ID" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_ID" NULLS FIRST); -CREATE UNIQUE NULLS DISTINCT INDEX "UNIQ_EXTERNAL_LOGIN" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_LOGIN" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_EXTERNAL_ID" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_ID" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_EXTERNAL_LOGIN" ON "USERS"("EXTERNAL_IDENTITY_PROVIDER" NULLS FIRST, "EXTERNAL_LOGIN" NULLS FIRST); CREATE INDEX "USERS_EMAIL" ON "USERS"("EMAIL" NULLS FIRST); CREATE TABLE "WEBHOOK_DELIVERIES"( diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/CreateIndexBuilder.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/CreateIndexBuilder.java index 3e325e86c69..6a37ec802b4 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/CreateIndexBuilder.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/CreateIndexBuilder.java @@ -21,6 +21,9 @@ package org.sonar.server.platform.db.migration.sql; import java.util.ArrayList; import java.util.List; +import java.util.stream.Collectors; +import org.sonar.db.dialect.Dialect; +import org.sonar.db.dialect.PostgreSql; import org.sonar.server.platform.db.migration.def.ColumnDef; import static com.google.common.base.Preconditions.checkArgument; @@ -31,11 +34,17 @@ import static org.sonar.server.platform.db.migration.def.Validations.validateTab public class CreateIndexBuilder { - private final List<String> columns = new ArrayList<>(); + private static final String COLUMN_CANNOT_BE_NULL = "Column cannot be null"; + private final List<NullableColumn> columns = new ArrayList<>(); + private final Dialect dialect; private String tableName; private String indexName; private boolean unique = false; + public CreateIndexBuilder(Dialect dialect) { + this.dialect = dialect; + } + /** * Required name of table on which index is created */ @@ -68,16 +77,27 @@ public class CreateIndexBuilder { * Other attributes are ignored. */ public CreateIndexBuilder addColumn(ColumnDef column) { - columns.add(requireNonNull(column, "Column cannot be null").getName()); + requireNonNull(column, COLUMN_CANNOT_BE_NULL); + columns.add(new NullableColumn(column.getName(), column.isNullable())); return this; } /** * Add a column to the scope of index. Order of calls to this * method is important and is kept as-is when creating the index. + * + * @deprecated use {@link CreateIndexBuilder#addColumn(String, boolean) instead} */ + @Deprecated(since = "10.3") public CreateIndexBuilder addColumn(String column) { - columns.add(requireNonNull(column, "Column cannot be null")); + requireNonNull(column, COLUMN_CANNOT_BE_NULL); + columns.add(new NullableColumn(column, false)); + return this; + } + + public CreateIndexBuilder addColumn(String column, boolean isNullable) { + requireNonNull(column, COLUMN_CANNOT_BE_NULL); + columns.add(new NullableColumn(column, isNullable)); return this; } @@ -88,18 +108,46 @@ public class CreateIndexBuilder { return singletonList(createSqlStatement()); } + /** + * + */ private String createSqlStatement() { StringBuilder sql = new StringBuilder("CREATE "); if (unique) { sql.append("UNIQUE "); + if (dialect.supportsNullNotDistinct() && !PostgreSql.ID.equals(dialect.getId())) { + sql.append("NULLS NOT DISTINCT "); + } } sql.append("INDEX "); sql.append(indexName); sql.append(" ON "); sql.append(tableName); sql.append(" ("); - sql.append(String.join(", ", columns)); + + /* + * Oldest versions of postgres don't support NULLS NOT DISTINCT, and their default behavior is NULLS DISTINCT. + * To make sure we apply the same constraints as other DB vendors, we use coalesce to default to empty string, to ensure unicity constraint. + * Other db vendors are not impacted since they fall back to NULLS NOT DISTINCT by default. + */ + if (unique && !dialect.supportsNullNotDistinct() && PostgreSql.ID.equals(dialect.getId())) { + sql.append(columns.stream() + .map(c -> c.isNullable() ? "COALESCE(%s, '')".formatted(c.name()) : c.name()) + .collect(Collectors.joining(", "))); + } else { + sql.append(columns.stream() + .map(NullableColumn::name) + .collect(Collectors.joining(", "))); + } + sql.append(")"); + + if (unique && dialect.supportsNullNotDistinct() && PostgreSql.ID.equals(dialect.getId())) { + sql.append(" NULLS NOT DISTINCT"); + } return sql.toString(); } + + private record NullableColumn(String name, boolean isNullable) { + } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/CreateIndexOnColumn.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/CreateIndexOnColumn.java index fc73320b3d8..db946809851 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/CreateIndexOnColumn.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/CreateIndexOnColumn.java @@ -42,7 +42,7 @@ public abstract class CreateIndexOnColumn extends DdlChange { public void execute(Context context) throws SQLException { try (Connection connection = getDatabase().getDataSource().getConnection()) { if (!DatabaseUtils.indexExistsIgnoreCase(table, newIndexName(), connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(table) .setName(newIndexName()) .addColumn(columnName) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/CreateIndexOnColumns.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/CreateIndexOnColumns.java index 0110cf09c35..4b28582f5d7 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/CreateIndexOnColumns.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/CreateIndexOnColumns.java @@ -44,7 +44,7 @@ public abstract class CreateIndexOnColumns extends DdlChange { public void execute(Context context) throws SQLException { try (Connection connection = getDatabase().getDataSource().getConnection()) { if (!DatabaseUtils.indexExistsIgnoreCase(table, newIndexName(), connection)) { - CreateIndexBuilder builder = new CreateIndexBuilder() + CreateIndexBuilder builder = new CreateIndexBuilder(getDialect()) .setTable(table) .setName(newIndexName()) .setUnique(unique); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java index df08eaa4a98..0691ef4ddd0 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java @@ -1607,8 +1607,8 @@ public class CreateInitialSchema extends DdlChange { .build()); } - private static void addIndex(Context context, String table, String index, boolean unique, ColumnDef firstColumn, ColumnDef... otherColumns) { - CreateIndexBuilder builder = new CreateIndexBuilder() + private void addIndex(Context context, String table, String index, boolean unique, ColumnDef firstColumn, ColumnDef... otherColumns) { + CreateIndexBuilder builder = new CreateIndexBuilder(getDialect()) .setTable(table) .setName(index) .setUnique(unique); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateUniqueIndexForScimGroupsUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateUniqueIndexForScimGroupsUuid.java index a70ee752126..e775f804d74 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateUniqueIndexForScimGroupsUuid.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v100/CreateUniqueIndexForScimGroupsUuid.java @@ -48,9 +48,9 @@ public class CreateUniqueIndexForScimGroupsUuid extends DdlChange { } } - private static void createUserUuidUniqueIndex(Context context, Connection connection) { + private void createUserUuidUniqueIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(TABLE_NAME) .setName(INDEX_NAME) .addColumn(COLUMN_NAME) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexForEmailOnUsersTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexForEmailOnUsersTable.java index d305a0d0dd9..e2dd7984398 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexForEmailOnUsersTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexForEmailOnUsersTable.java @@ -47,9 +47,9 @@ class CreateIndexForEmailOnUsersTable extends DdlChange { } } - private static void createIndex(Context context, Connection connection) { + private void createIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(TABLE_NAME) .setName(INDEX_NAME) .addColumn(COLUMN_NAME) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexForScmAccountOnScmAccountsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexForScmAccountOnScmAccountsTable.java index ef6e4f34840..ff5f0eae03e 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexForScmAccountOnScmAccountsTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexForScmAccountOnScmAccountsTable.java @@ -46,9 +46,9 @@ class CreateIndexForScmAccountOnScmAccountsTable extends DdlChange { } } - private static void createIndex(Context context, Connection connection) { + private void createIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(SCM_ACCOUNTS_TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(SCM_ACCOUNTS_TABLE_NAME) .setName(INDEX_NAME) .addColumn(SCM_ACCOUNT_COLUMN_NAME) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.java index 9ceda7fbcb2..8b69deea675 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.java @@ -47,9 +47,9 @@ public class CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable extends Ddl } } - private static void createIndex(Context context, Connection connection) { + private void createIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(TABLE_NAME) .setName(INDEX_NAME) .addColumn(EXTERNAL_IDENTITY_PROVIDER_COLUMN_NAME) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTable.java index b09a82f9140..e6af0edd465 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSchedulesTable.java @@ -52,9 +52,9 @@ public class CreateUniqueIndexForReportSchedulesTable extends DdlChange { } } - private static void createUserUuidUniqueIndex(Context context, Connection connection) { + private void createUserUuidUniqueIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(TABLE_NAME) .setName(INDEX_NAME) .addColumn(COLUMN_NAME_PORTFOLIO) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTable.java index 54b3b869853..945e6011a9e 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/CreateUniqueIndexForReportSubscriptionsTable.java @@ -30,7 +30,7 @@ import org.sonar.server.platform.db.migration.step.DdlChange; import static org.sonar.server.platform.db.migration.version.v101.AddReportSubscriptionsTable.TABLE_NAME; -public class CreateUniqueIndexForReportSubscriptionsTable extends DdlChange{ +public class CreateUniqueIndexForReportSubscriptionsTable extends DdlChange { @VisibleForTesting static final String COLUMN_NAME_PORTFOLIO = "portfolio_uuid"; @@ -55,9 +55,9 @@ public class CreateUniqueIndexForReportSubscriptionsTable extends DdlChange{ } } - private static void createUserUuidUniqueIndex(DdlChange.Context context, Connection connection) { + private void createUserUuidUniqueIndex(DdlChange.Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(TABLE_NAME) .setName(INDEX_NAME) .addColumn(COLUMN_NAME_PORTFOLIO) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateUniqueConstraintOnIssuesImpacts.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateUniqueConstraintOnIssuesImpacts.java index 1fd625244da..ee0d8ff0e69 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateUniqueConstraintOnIssuesImpacts.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateUniqueConstraintOnIssuesImpacts.java @@ -41,9 +41,9 @@ public class CreateUniqueConstraintOnIssuesImpacts extends DdlChange { } } - private static void createIndex(Context context, Connection connection) { + private void createIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(TABLE_NAME) .setName(INDEX_NAME) .addColumn("issue_key") diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateUniqueConstraintOnRulesDefaultImpacts.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateUniqueConstraintOnRulesDefaultImpacts.java index 368960eb312..659f837f236 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateUniqueConstraintOnRulesDefaultImpacts.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v102/CreateUniqueConstraintOnRulesDefaultImpacts.java @@ -41,9 +41,9 @@ public class CreateUniqueConstraintOnRulesDefaultImpacts extends DdlChange { } } - private static void createIndex(Context context, Connection connection) { + private void createIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(TABLE_NAME) .setName(INDEX_NAME) .addColumn("rule_uuid") diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/CreateIndexBuilderTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/CreateIndexBuilderTest.java index ec573c08718..a04f538fd92 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/CreateIndexBuilderTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/CreateIndexBuilderTest.java @@ -19,8 +19,14 @@ */ package org.sonar.server.platform.db.migration.sql; +import java.sql.DatabaseMetaData; +import java.sql.SQLException; import java.util.List; import org.junit.Test; +import org.mockito.Mockito; +import org.sonar.db.dialect.H2; +import org.sonar.db.dialect.Oracle; +import org.sonar.db.dialect.PostgreSql; import static org.assertj.core.api.Assertions.assertThat; import static org.assertj.core.api.Assertions.assertThatThrownBy; @@ -29,57 +35,106 @@ import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVar public class CreateIndexBuilderTest { @Test public void create_index_on_single_column() { - verifySql(new CreateIndexBuilder() - .setTable("issues") - .setName("issues_key") - .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(10).build()), + verifySql(new CreateIndexBuilder(new Oracle()) + .setTable("issues") + .setName("issues_key") + .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(10).build()), "CREATE INDEX issues_key ON issues (kee)"); } @Test public void create_unique_index_on_single_column() { - verifySql(new CreateIndexBuilder() - .setTable("issues") - .setName("issues_key") - .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(10).build()) - .setUnique(true), + verifySql(new CreateIndexBuilder(new Oracle()) + .setTable("issues") + .setName("issues_key") + .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(10).build()) + .setUnique(true), "CREATE UNIQUE INDEX issues_key ON issues (kee)"); } @Test public void create_index_on_multiple_columns() { - verifySql(new CreateIndexBuilder() - .setTable("rules") - .setName("rules_key") - .addColumn(newVarcharColumnDefBuilder().setColumnName("repository").setLimit(10).build()) - .addColumn(newVarcharColumnDefBuilder().setColumnName("rule_key").setLimit(50).build()), + verifySql(new CreateIndexBuilder(new Oracle()) + .setTable("rules") + .setName("rules_key") + .addColumn(newVarcharColumnDefBuilder().setColumnName("repository").setLimit(10).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("rule_key").setLimit(50).build()), "CREATE INDEX rules_key ON rules (repository, rule_key)"); } @Test public void create_unique_index_on_multiple_columns() { - verifySql(new CreateIndexBuilder() - .setTable("rules") - .setName("rules_key") - .addColumn(newVarcharColumnDefBuilder().setColumnName("repository").setLimit(10).build()) - .addColumn(newVarcharColumnDefBuilder().setColumnName("rule_key").setLimit(50).build()) - .setUnique(true), + verifySql(new CreateIndexBuilder(new Oracle()) + .setTable("rules") + .setName("rules_key") + .addColumn(newVarcharColumnDefBuilder().setColumnName("repository").setLimit(10).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("rule_key").setLimit(50).build()) + .setUnique(true), "CREATE UNIQUE INDEX rules_key ON rules (repository, rule_key)"); } @Test + public void build_whenDialectH2_shouldHaveNullsNotDistinctClause() { + verifySql(new CreateIndexBuilder(new H2()) + .setTable("rules") + .setName("rules_key") + .addColumn(newVarcharColumnDefBuilder().setColumnName("repository").setLimit(10).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("rule_key").setLimit(50).build()) + .setUnique(true), + "CREATE UNIQUE NULLS NOT DISTINCT INDEX rules_key ON rules (repository, rule_key)"); + } + + @Test + public void build_whenDialectPostgres15_shouldHaveNullsNotDistinctClause() throws SQLException { + PostgreSql postgreSql = new PostgreSql(); + getMetadataForDbVersion(15, 0); + postgreSql.init(getMetadataForDbVersion(15, 0)); + + verifySql(new CreateIndexBuilder(postgreSql) + .setTable("rules") + .setName("rules_key") + .addColumn(newVarcharColumnDefBuilder().setColumnName("repository").setLimit(10).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("rule_key").setLimit(50).build()) + .setUnique(true), + "CREATE UNIQUE INDEX rules_key ON rules (repository, rule_key) NULLS NOT DISTINCT"); + } + + @Test + public void build_whenDialectPostgres14OrLower_shouldHaveCoalesceConditionsOnNullableColumns() throws SQLException { + PostgreSql postgreSql = new PostgreSql(); + getMetadataForDbVersion(14, 0); + postgreSql.init(getMetadataForDbVersion(14, 0)); + + verifySql(new CreateIndexBuilder(postgreSql) + .setTable("rules") + .setName("rules_key") + .addColumn(newVarcharColumnDefBuilder().setColumnName("repository").setLimit(10).build()) + .addColumn(newVarcharColumnDefBuilder().setColumnName("rule_key").setLimit(50).setIsNullable(false).build()) + .setUnique(true), + "CREATE UNIQUE INDEX rules_key ON rules (COALESCE(repository, ''), rule_key)"); + } + + private static DatabaseMetaData getMetadataForDbVersion(int major, int minor) throws SQLException { + DatabaseMetaData databaseMetaData = Mockito.mock(DatabaseMetaData.class); + Mockito.when(databaseMetaData.getDatabaseMajorVersion()).thenReturn(major); + Mockito.when(databaseMetaData.getDatabaseMinorVersion()).thenReturn(minor); + return databaseMetaData; + } + + + @Test public void index_length_is_not_specified_on_big_varchar_columns() { - verifySql(new CreateIndexBuilder() - .setTable("issues") - .setName("issues_key") - .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(4000).build()), + verifySql(new CreateIndexBuilder(new Oracle()) + .setTable("issues") + .setName("issues_key") + .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(4000).build()), "CREATE INDEX issues_key ON issues (kee)"); } @Test public void throw_NPE_if_table_is_missing() { assertThatThrownBy(() -> { - new CreateIndexBuilder() + new CreateIndexBuilder(new Oracle()) .setName("issues_key") .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(10).build()) .build(); @@ -91,7 +146,7 @@ public class CreateIndexBuilderTest { @Test public void throw_NPE_if_index_name_is_missing() { assertThatThrownBy(() -> { - new CreateIndexBuilder() + new CreateIndexBuilder(new H2()) .setTable("issues") .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(10).build()) .build(); @@ -103,7 +158,7 @@ public class CreateIndexBuilderTest { @Test public void throw_IAE_if_columns_are_missing() { assertThatThrownBy(() -> { - new CreateIndexBuilder() + new CreateIndexBuilder(new H2()) .setTable("issues") .setName("issues_key") .build(); @@ -115,7 +170,7 @@ public class CreateIndexBuilderTest { @Test public void throw_IAE_if_table_name_is_not_valid() { assertThatThrownBy(() -> { - new CreateIndexBuilder() + new CreateIndexBuilder(new H2()) .setTable("(not valid)") .setName("issues_key") .addColumn(newVarcharColumnDefBuilder().setColumnName("kee").setLimit(10).build()) @@ -128,7 +183,7 @@ public class CreateIndexBuilderTest { @Test public void throw_NPE_when_adding_null_column() { assertThatThrownBy(() -> { - new CreateIndexBuilder() + new CreateIndexBuilder(new H2()) .setTable("issues") .setName("issues_key") .addColumn((String) null) diff --git a/server/sonar-webserver-webapi/src/it/java/org/sonar/server/permission/GroupPermissionChangerIT.java b/server/sonar-webserver-webapi/src/it/java/org/sonar/server/permission/GroupPermissionChangerIT.java index 0676f3f992f..e22fa26f0df 100644 --- a/server/sonar-webserver-webapi/src/it/java/org/sonar/server/permission/GroupPermissionChangerIT.java +++ b/server/sonar-webserver-webapi/src/it/java/org/sonar/server/permission/GroupPermissionChangerIT.java @@ -279,9 +279,9 @@ public class GroupPermissionChangerIT { public void do_nothing_when_adding_permission_that_already_exists() { db.users().insertPermissionOnGroup(group, ADMINISTER_QUALITY_GATES); - apply(new GroupPermissionChange(Operation.ADD, ADMINISTER_QUALITY_GATES.getKey(), null, group, permissionService)); + apply(new GroupPermissionChange(Operation.ADD, ADMINISTER_QUALITY_GATES.getKey(), null, group, permissionService), ADMINISTER_QUALITY_GATES.getKey()); - assertThat(db.users().selectGroupPermissions(group, null)).containsOnly(ADMINISTER_QUALITY_GATES.getKey()); + assertThat(db.users().selectGroupPermissions(group, null)).containsExactly(ADMINISTER_QUALITY_GATES.getKey()); } @Test @@ -295,7 +295,7 @@ public class GroupPermissionChangerIT { fail("a BadRequestException should have been thrown for permission " + perm); } catch (BadRequestException e) { assertThat(e).hasMessage("Invalid project permission '" + perm + - "'. Valid values are [" + StringUtils.join(permissionService.getAllProjectPermissions(), ", ") + "]"); + "'. Valid values are [" + StringUtils.join(permissionService.getAllProjectPermissions(), ", ") + "]"); } }); } @@ -311,7 +311,7 @@ public class GroupPermissionChangerIT { fail("a BadRequestException should have been thrown for permission " + perm); } catch (BadRequestException e) { assertThat(e).hasMessage("Invalid project permission '" + perm + - "'. Valid values are [" + StringUtils.join(permissionService.getAllProjectPermissions(), ", ") + "]"); + "'. Valid values are [" + StringUtils.join(permissionService.getAllProjectPermissions(), ", ") + "]"); } }); } |